如何从.net代码传递表值参数到存储过程
我有一个MS SQL Server 2005数据库。 在一些过程中，我将表参数传递给一个存储过程作为nvarchar（用逗号分隔）并在内部划分为单个值。 我将它添加到SQL命令参数列表中，如下所示：
cmd.Parameters.Add("@Logins", SqlDbType.NVarchar).Value = "jim18,jenny1975,cosmo";
我必须将数据库迁移到SQL Server 2008.我知道有表值参数，并且我知道如何在存储过程中使用它们。 但我不知道如何将其传递给SQL命令中的参数列表。 有没有人知道Parameters.Add过程的正确语法？ 或者有另一种方法来传递这个参数？
DataTable ， DbDataReader或IEnumerable<SqlDataRecord>对象可用于填充SQL Server 2008（ADO.NET）中MSDN文章表值参数的表值参数。
以下示例说明如何使用DataTable或IEnumerable<SqlDataRecord> ：
SQL代码 ：
CREATE TABLE dbo.PageView
(
PageViewID BIGINT NOT NULL CONSTRAINT pkPageView PRIMARY KEY CLUSTERED,
PageViewCount BIGINT NOT NULL
);
CREATE TYPE dbo.PageViewTableType AS TABLE
(
PageViewID BIGINT NOT NULL
);
CREATE PROCEDURE dbo.procMergePageView
@Display dbo.PageViewTableType READONLY
AS
BEGIN
MERGE INTO dbo.PageView AS T
USING @Display AS S
ON T.PageViewID = S.PageViewID
WHEN MATCHED THEN UPDATE SET T.PageViewCount = T.PageViewCount + 1
WHEN NOT MATCHED THEN INSERT VALUES(S.PageViewID, 1);
END
C＃代码 ：
private static void ExecuteProcedure(bool useDataTable,
string connectionString,
IEnumerable<long> ids)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
using (SqlCommand command = connection.CreateCommand())
{
command.CommandText = "dbo.procMergePageView";
command.CommandType = CommandType.StoredProcedure;
SqlParameter parameter;
if (useDataTable) {
parameter = command.Parameters
.AddWithValue("@Display", CreateDataTable(ids));
}
else
{
parameter = command.Parameters
.AddWithValue("@Display", CreateSqlDataRecords(ids));
}
parameter.SqlDbType = SqlDbType.Structured;
parameter.TypeName = "dbo.PageViewTableType";
command.ExecuteNonQuery();
}
}
}
private static DataTable CreateDataTable(IEnumerable<long> ids)
{
DataTable table = new DataTable();
table.Columns.Add("ID", typeof(long));
foreach (long id in ids)
{
table.Rows.Add(id);
}
return table;
}
private static IEnumerable<SqlDataRecord> CreateSqlDataRecords(IEnumerable<long> ids)
{
SqlMetaData[] metaData = new SqlMetaData[1];
metaData[0] = new SqlMetaData("ID", SqlDbType.BigInt);
SqlDataRecord record = new SqlDataRecord(metaData);
foreach (long id in ids)
{
record.SetInt64(0, id);
yield return record;
}
}
除了Ryan的回答，如果你正在处理一个带有多列的table-valued parameter ，其序号不是按字母顺序排列，你还需要设置DataColumn的Ordinal属性。
举个例子，如果你有下面的表值作为SQL中的一个参数使用：
CREATE TYPE NodeFilter AS TABLE (
ID int not null
Code nvarchar(10) not null,
);
您需要在C＃中对您的列进行排序：
table.Columns["ID"].SetOrdinal(0);
// this also bumps Code to ordinal of 1
// if you have more than 2 cols then you would need to set more ordinals
如果你不这样做，你会得到一个解析错误，无法将nvarchar转换为int。
通用
public static DataTable ToTableValuedParameter<T, TProperty>(this IEnumerable<T> list, Func<T, TProperty> selector)
{
var tbl = new DataTable();
tbl.Columns.Add("Id", typeof(T));
foreach (var item in list)
{
tbl.Rows.Add(selector.Invoke(item));
}
return tbl;
}
上一篇: How to pass table value parameters to stored procedure from .net code