Como passar parâmetros de valor da tabela para o procedimento armazenado do código .net

Eu tenho um database do MS SQL Server 2005. Em alguns procedimentos eu tenho parâmetros de tabela que eu passo para um proc armazenado como um nvarchar (separado por vírgulas) e divido internamente em valores únicos. Eu adicionei-o à lista de parâmetros do comando SQL como este:

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

Eu tenho que migrar o database para o SQL Server 2008. Eu sei que existem parâmetros de valor de tabela e sei como usá-los em stored procedures. Mas eu não sei como passar um para a lista de parâmetros em um comando SQL. Alguém sabe a syntax correta do procedimento Parameters.Add ? Ou existe outra maneira de passar esse parâmetro?

    DataTable objects DataTable , DbDataReader ou IEnumerable podem ser usados ​​para preencher um parâmetro com valor de tabela pelo artigo do MSDN Table-Valued Parameters no SQL Server 2008 (ADO.NET) .

    O exemplo a seguir ilustra usando um DataTable ou um IEnumerable :

    Código 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ódigo C # :

     private static void ExecuteProcedure(bool useDataTable, string connectionString, IEnumerable 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 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 CreateSqlDataRecords(IEnumerable 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; } } 

    Além da resposta de Ryan, você também precisará definir a propriedade Ordinal do DataColumn se estiver lidando com um table-valued parameter com table-valued parameter com várias colunas cujos ordinais não estão em ordem alfabética.

    Por exemplo, se você tiver o seguinte valor de tabela que é usado como um parâmetro no SQL:

     CREATE TYPE NodeFilter AS TABLE ( ID int not null Code nvarchar(10) not null, ); 

    Você precisaria encomendar suas colunas como tal em 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 

    Se você não conseguir fazer isso, você receberá um erro de análise, falha ao converter nvarchar em int.

    Genérico

      public static DataTable ToTableValuedParameter(this IEnumerable list, Func selector) { var tbl = new DataTable(); tbl.Columns.Add("Id", typeof(T)); foreach (var item in list) { tbl.Rows.Add(selector.Invoke(item)); } return tbl; } 

    A maneira mais limpa de trabalhar com isso. Assumindo que sua tabela é uma lista de números inteiros chamados “dbo.tvp_Int” (Personalizar para seu próprio tipo de tabela)

    Crie este método de extensão …

     public static void AddWithValue_Tvp_Int(this SqlParameterCollection paramCollection, string parameterName, List 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; } } 

    Agora você pode adicionar um parâmetro com valor de tabela em uma linha em qualquer lugar simplesmente fazendo isso:

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