Execute table-valued function from C# using ADO.NET

Question

I have created a table-valued function because I want to return table of values to my .NET application. At the moment the way the code runs is that it returns affected rows which is expected of ExecuteNonQuery method. I want to expecting table as return type. So not sure what to do.

SQL:

CREATE FUNCTION [EMR].[fnGetMemberOrderDailySummary]
    (@TVP typtblMember READONLY)
RETURNS TABLE 
AS
    RETURN (
        SELECT * 
        FROM [EMR].[tblFactMemberOrderDailySummary]
        WHERE Member IN (SELECT MemberMnemonic FROM @TVP)
       )

.NET code:

using (var conn = new SqlConnection(_EDWConnString))
{
    table = CreateDataTable(Member);

    var procName = "[EMR].[fnGetMemberOrderDailySummary]";
    var procParam1Name = "@typtblMember";
                                   
    var cmd = new SqlCommand(procName, conn) { CommandType = CommandType.StoredProcedure };

    var dtparam = cmd.Parameters.AddWithValue(procParam1Name, Member.AsDataTable());
    dtparam.SqlDbType = SqlDbType.Structured;

    _logger.Log(LogSeverity.Debug, $"Beneficial owner position records insert into EDW is started");

    var affectedRows = cmd.ExecuteNonQueryAsync();

    var isSuccessful = affectedRows != null;

    _logger.Log(LogSeverity.Debug, $"GetSampleData meeting expectations: [{isSuccessful}]");

    return table;
}

Answer 1

You don't need a table-value function if you want to get a DataTable from the database. Instead, you can run the SQL directly and Fill() the DataTable with a DataAdapter or Load() it from a DataReader:

var sql = @"
 SELECT * 
 FROM [EMR].[tblFactMemberOrderDailySummary]
 WHERE Member IN (SELECT MemberMnemonic FROM @TVP)
";

var isSuccessful = false;
var result = new DataTable();
using var conn = new SqlConnection(_EDWConnString);
using var cmd = new SqlCommand(sql, conn);
using var da = new SqlDataAdapter(cmd);

cmd.Parameters.Add("@TVP", SqlDbType.Structured).Value = Member.AsDataTable();

try 
{
    _logger.Log(LogSeverity.Debug, $"Beneficial owner position records insert into EDW is started");
    isSuccessful = (da.Fill(result) > 0);
}
finally 
{
    _logger.Log(LogSeverity.Debug, $"GetSampleData meeting expectations: [{isSuccessful}]");
}

return result;

If you don't want to have SQL directly in your C# code, you would do this as a stored procedure instead of table-value function:

CREATE PROCEDURE [EMR].[GetMemberOrderDailySummary]
    (@TVP typtblMember READONLY)
AS
    SELECT * 
    FROM [EMR].[tblFactMemberOrderDailySummary]
    WHERE Member IN (SELECT MemberMnemonic FROM @TVP)

and then:

var isSuccessful = false;
var result = new DataTable();
using var conn = new SqlConnection(_EDWConnString);
using var cmd = new SqlCommand("[EMR].[GetMemberOrderDailySummary]", conn);
using var da = new SqlDataAdapter(cmd);

cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@TVP", SqlDbType.Structured).Value = Member.AsDataTable();

try 
{
    _logger.Log(LogSeverity.Debug, $"Beneficial owner position records insert into EDW is started");
    isSuccessful = (da.Fill(result) > 0);
}
finally 
{
    _logger.Log(LogSeverity.Debug, $"GetSampleData meeting expectations: [{isSuccessful}]");
}

return result;

Either way, I'd tend to write the SQL to use a JOIN instead of IN():

SELECT ds.* 
FROM [EMR].[tblFactMemberOrderDailySummary] ds
INNER JOIN @TVP m on ds.Member = m.MemberMnemonic