Cómo pasar los parámetros de valor de la tabla al procedimiento almacenado desde el código .net

Tengo una base de datos MS SQL Server 2005. En algunos procedimientos, tengo parámetros de tabla que paso a un proceso almacenado como un nvarchar (separados por comas) y los divido internamente en valores únicos. Lo agrego a la lista de parámetros del comando SQL de esta manera:

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

Tengo que migrar la base de datos a SQL Server 2008. Sé que hay parámetros de valores de tabla, y sé cómo usarlos en procedimientos almacenados. Pero no sé cómo pasar uno a la lista de parámetros en un comando SQL. ¿Alguien sabe la syntax correcta del procedimiento Parameters.Add ? ¿O hay otra forma de pasar este parámetro?

    DataTable DbDataReader DataTable , DbDataReader o IEnumerable se pueden usar para rellenar un parámetro con valores de tabla según el artículo de MSDN Parámetros con valores de tabla en SQL Server 2008 (ADO.NET) .

    El siguiente ejemplo ilustra el uso de DataTable o 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 # Code :

     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; } } 

    Además de la respuesta de Ryan, también necesitará establecer la DataColumn de DataColumn si está tratando con un table-valued parameter con table-valued parameter con varias columnas cuyos ordinales no están en orden alfabético.

    Como ejemplo, si tiene el siguiente valor de tabla que se usa como parámetro en SQL:

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

    Debería pedir sus columnas como tales en 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 

    Si no lo haces, obtendrás un error de análisis, no se pudo convertir nvarchar a 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; } 

    La forma más limpia de trabajar con eso. Suponiendo que su tabla es una lista de enteros llamada “dbo.tvp_Int” (Personalizar para su propio tipo de tabla)

    Crea este método de extensión …

     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; } } 

    Ahora puede agregar un parámetro valuado de tabla en una línea en cualquier lugar simplemente haciendo esto:

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