提问人：Tom 提问时间：9/29/2023 最后编辑：marc_sTom 更新时间：9/30/2023 访问量：62
使用 ADO.NET 从 C# 执行表值函数
Execute table-valued function from C# using ADO.NET
问：
我创建了一个表值函数，因为我想将值表返回到我的 .NET 应用程序。目前，代码的运行方式是返回受影响的行，这是方法所期望的。我想期望表作为返回类型。所以不知道该怎么办。ExecuteNonQuery
SQL格式：
CREATE FUNCTION [EMR].[fnGetMemberOrderDailySummary]
(@TVP typtblMember READONLY)
RETURNS TABLE
AS
RETURN (
SELECT *
FROM [EMR].[tblFactMemberOrderDailySummary]
WHERE Member IN (SELECT MemberMnemonic FROM @TVP)
)
.NET 代码：
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;
}
答：
-1赞
Jakobik Jakobicek
9/29/2023
#1
您使用的是 cmd.ExecuteNonQuery，请改用 reader
SqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
Console.WriteLine("{0}\t{1}", reader.GetInt32(0),
reader.GetString(1));
}
}
```
评论
2赞
Charlieface
9/30/2023
if (reader.HasRows)没有必要，会照顾到的while
2赞
Joel Coehoorn
9/30/2023
#2
如果要从数据库中获取表值函数，则不需要表值函数。相反，您可以直接运行 SQL，并使用 a 或 it 从 a 运行：DataTableFill()DataTableDataAdapterLoad()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;
如果不想将 SQL 直接放在 C# 代码中，则可以将其作为存储过程而不是表值函数来执行：
CREATE PROCEDURE [EMR].[GetMemberOrderDailySummary]
(@TVP typtblMember READONLY)
AS
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("[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;
无论哪种方式，我都倾向于编写 SQL 以使用 而不是：JOININ()
SELECT ds.*
FROM [EMR].[tblFactMemberOrderDailySummary] ds
INNER JOIN @TVP m on ds.Member = m.MemberMnemonic
评论