Как передать параметры табличного значения в хранимую процедуру из кода.net
У меня есть база данных MS SQL Server 2005. В нескольких процедурах у меня есть параметры таблицы, которые я передаю хранимому процессу в виде nvarchar (через запятую) и внутренне делю на отдельные значения. Я добавляю его в список параметров команды SQL следующим образом:
cmd.Parameters.Add("@Logins", SqlDbType.NVarchar).Value = "jim18,jenny1975,cosmo";
Я должен перенести базу данных в SQL Server 2008. Я знаю, что есть параметры табличных значений, и я знаю, как использовать их в хранимых процедурах. Но я не знаю, как передать его в список параметров в команде SQL. Кто-нибудь знает правильный синтаксис Parameters.Add процедура? Или есть другой способ передать этот параметр?
6 ответов
DataTable, DbDataReader, или же IEnumerable<SqlDataRecord> objects can be used to populate a table-valued parameter per the MSDN article Table-Valued Parameters in SQL Server 2008 (ADO.NET).
The following example illustrates using either a DataTable или IEnumerable<SqlDataRecord>:
SQL Code:
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# Code:
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"s 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).
Затем вы можете вызвать его из С# следующим образом:
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);
}