如何从.net 代码传递表值参数到存储过程

我有一个 SQLServer2005数据库。在一些过程中，我将表参数作为 nvarchar(用逗号分隔)传递给存储过程，然后在内部分成单个值。我将它添加到 SQL 命令参数列表中，如下所示:

cmd.Parameters.Add("@Logins", SqlDbType.NVarchar).Value = "jim18,jenny1975,cosmo";

我必须将数据库迁移到 SQLServer2008。我知道存在表值参数，也知道如何在存储过程中使用它们。但是我不知道如何在 SQL 命令中将其传递给参数列表。

有人知道 Parameters.Add过程的正确语法吗? 或者有其他传递这个参数的方法吗？

302080 次浏览
小开

DataTable、 DbDataReader或 IEnumerable<SqlDataRecord>对象可用于填充 MSDN 文章 SQLServer2008中的表值参数(ADO.NET)中的表值参数。

下面的示例说明如何使用 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;
}
}
小开

继瑞安的回答，你还需要设置的 DataColumn的 Ordinal属性，如果你处理的是一个 table-valued parameter与 多个列的序号是 没有的字母顺序。

例如，如果您有以下表值，用作 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;


}
小开

最干净的工作方式。假设您的表是一个名为“ dbo.tvp _ Int”的整数列表(为您自己的表类型定制)

创建这个扩展方法..。

public static void AddWithValue_Tvp_Int(this SqlParameterCollection paramCollection, string parameterName, List<int> data)
{
if(paramCollection != null)
{
var p = paramCollection.Add(parameterName, SqlDbType.Structured);
p.TypeName = "dbo.tvp_Int";
DataTable _dt = new DataTable() {Columns = {"Value"}};
data.ForEach(value => _dt.Rows.Add(value));
p.Value = _dt;
}
}

现在您可以简单地在任何地方添加一个表值参数:

cmd.Parameters.AddWithValueFor_Tvp_Int("@IDValues", listOfIds);
小开

使用此代码可以根据类型创建合适的参数:

private SqlParameter GenerateTypedParameter(string name, object typedParameter)
{
DataTable dt = new DataTable();


var properties = typedParameter.GetType().GetProperties().ToList();
properties.ForEach(p =>
{
dt.Columns.Add(p.Name, Nullable.GetUnderlyingType(p.PropertyType) ?? p.PropertyType);
});
var row = dt.NewRow();
properties.ForEach(p => { row[p.Name] = (p.GetValue(typedParameter) ?? DBNull.Value); });
dt.Rows.Add(row);


return new SqlParameter
{
Direction = ParameterDirection.Input,
ParameterName = name,
Value = dt,
SqlDbType = SqlDbType.Structured
};
}
小开

如果有一个带参数的表值函数，例如:

CREATE FUNCTION [dbo].[MyFunc](@PRM1 int, @PRM2 int)
RETURNS TABLE
AS
RETURN
(
SELECT * FROM MyTable t
where t.column1 = @PRM1
and t.column2 = @PRM2
)

你可以这么说:

select * from MyFunc(1,1).

然后你可以像这样从 C # 调用它:

public async Task<ActionResult> MethodAsync(string connectionString, int? prm1, int? prm2)
{
List<MyModel> lst = new List<MyModel>();


using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.OpenAsync();


using (var command = connection.CreateCommand())
{
command.CommandText = $"select * from MyFunc({prm1},{prm2})";
using (var reader = await command.ExecuteReaderAsync())
{
if (reader.HasRows)
{
while (await reader.ReadAsync())
{
MyModel myModel = new MyModel();
myModel.Column1 = int.Parse(reader["column1"].ToString());
myModel.Column2 = int.Parse(reader["column2"].ToString());
lst.Add(myModel);
}
}
}
}
}
View(lst);
}

提交答案