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

pdkcd3nj 于 2023-10-15 发布在 C#

关注(0)|答案(2)|浏览(120)

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;
}

sql-server

来源：https://stackoverflow.com/questions/77203219/execute-table-valued-function-from-c-sharp-using-ado-net

2条答案

按热度按时间

oug3syen1#

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

赞(0）回复(0）举报 2023-10-15

unftdfkk2#

You are using cmd.ExecuteNonQuery, use reader instead

SqlDataReader reader = command.ExecuteReader();

        if (reader.HasRows)
        {
            while (reader.Read())
            {
                Console.WriteLine("{0}\t{1}", reader.GetInt32(0),
                    reader.GetString(1));
            }
        }
    ```

赞(0）回复(0）举报 2023-10-15

我来回答

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

2条答案

相关问题

热门标签

最新问答