Pase el diccionario al procedimiento almacenado T-SQL

Tengo una aplicación mvc. En acción, tengo Dictionary . La Key es ID y el Value es sortOrderNumber. Quiero crear el procedimiento almacenado que será get key (id) encontrar este registro en la base de datos y guardar orderNumber column por value de Dictionary. Deseo llamar al procedimiento almacenado una vez y pasarle datos, en lugar de llamar muchas veces para actualizar los datos.

¿Tienes alguna idea? ¡Gracias!

El uso de parámetros de valores de tabla realmente no es tan complejo.

dado este SQL:

 CREATE TYPE MyTableType as TABLE (ID nvarchar(25),OrderNumber int) CREATE PROCEDURE MyTableProc (@myTable MyTableType READONLY) AS BEGIN SELECT * from @myTable END 

esto mostrará cuán relativamente fácil es, simplemente selecciona los valores que envió para propósitos de demostración. Estoy seguro de que puede abstraer fácilmente esto en su caso.

 using System; using System.Collections.Generic; using System.Data; using System.Data.SqlClient; namespace TVPSample { class Program { static void Main(string[] args) { //setup some data var dict = new Dictionary(); for (int x = 0; x < 10; x++) { dict.Add(x.ToString(),x+100); } //convert to DataTable var dt = ConvertToDataTable(dict); using (SqlConnection conn = new SqlConnection("[Your Connection String here]")) { conn.Open(); using (SqlCommand comm = new SqlCommand("MyTableProc",conn)) { comm.CommandType=CommandType.StoredProcedure; var param = comm.Parameters.AddWithValue("myTable", dt); //this is the most important part: param.SqlDbType = SqlDbType.Structured; var reader = comm.ExecuteReader(); //or NonQuery, etc. while (reader.Read()) { Console.WriteLine("{0} {1}", reader["ID"], reader["OrderNumber"]); } } } } //I am sure there is a more elegant way of doing this. private static DataTable ConvertToDataTable(Dictionary dict) { var dt = new DataTable(); dt.Columns.Add("ID",typeof(string)); dt.Columns.Add("OrderNumber", typeof(Int32)); foreach (var pair in dict) { var row = dt.NewRow(); row["ID"] = pair.Key; row["OrderNumber"] = pair.Value; dt.Rows.Add(row); } return dt; } } } 

Produce

 0 100 1 101 2 102 3 103 4 104 5 105 6 106 7 107 8 108 9 109 

La respuesta aceptada de usar un TVP generalmente es correcta, pero necesita algunas aclaraciones basadas en la cantidad de datos que se pasan. Usar un DataTable está bien (sin mencionar que es rápido y fácil) para conjuntos de datos más pequeños, pero para conjuntos más grandes lo hace no escalar dado que duplica el conjunto de datos colocándolo en el DataTable simplemente para pasarlo a SQL Server. Por lo tanto, para conjuntos de datos más grandes, hay una opción para transmitir los contenidos de cualquier colección personalizada. El único requisito real es que necesite definir la estructura en términos de tipos SqlDb e iterar a través de la colección, los cuales son pasos bastante triviales.

A continuación se muestra una descripción simplista de la estructura mínima, que es una adaptación de la respuesta que publiqué en ¿Cómo puedo insertar 10 millones de registros en el menor tiempo posible? , que se ocupa de importar datos de un archivo y, por lo tanto, es ligeramente diferente ya que los datos no están actualmente en la memoria. Como se puede ver en el siguiente código, esta configuración no es demasiado complicada pero muy flexible, eficiente y escalable.

Objeto SQL n.º 1: definir la estructura

 -- First: You need a User-Defined Table Type CREATE TYPE dbo.IDsAndOrderNumbers AS TABLE ( ID NVARCHAR(4000) NOT NULL, SortOrderNumber INT NOT NULL ); GO 

Objeto SQL # 2: Usa la estructura

 -- Second: Use the UDTT as an input param to an import proc. -- Hence "Tabled-Valued Parameter" (TVP) CREATE PROCEDURE dbo.ImportData ( @ImportTable dbo.IDsAndOrderNumbers READONLY ) AS SET NOCOUNT ON; -- maybe clear out the table first? TRUNCATE TABLE SchemaName.TableName; INSERT INTO SchemaName.TableName (ID, SortOrderNumber) SELECT tmp.ID, tmp.SortOrderNumber FROM @ImportTable tmp; -- OR -- some other T-SQL -- optional return data SELECT @NumUpdates AS [RowsUpdated], @NumInserts AS [RowsInserted]; GO 

C # code, Parte 1: Definir el iterador / remitente

 using System.Collections; using System.Data; using System.Data.SqlClient; using System.IO; using Microsoft.SqlServer.Server; private static IEnumerable SendRows(Dictionary RowData) { SqlMetaData[] _TvpSchema = new SqlMetaData[] { new SqlMetaData("ID", SqlDbType.NVarChar, 4000), new SqlMetaData("SortOrderNumber", SqlDbType.Int) }; SqlDataRecord _DataRecord = new SqlDataRecord(_TvpSchema); StreamReader _FileReader = null; // read a row, send a row foreach (KeyValuePair _CurrentRow in RowData) { // You shouldn't need to call "_DataRecord = new SqlDataRecord" as // SQL Server already received the row when "yield return" was called. // Unlike BCP and BULK INSERT, you have the option here to create an // object, do manipulation(s) / validation(s) on the object, then pass // the object to the DB or discard via "continue" if invalid. _DataRecord.SetString(0, _CurrentRow.ID); _DataRecord.SetInt32(1, _CurrentRow.sortOrderNumber); yield return _DataRecord; } } 

Código C #, Parte 2: Usar el iterador / remitente

 public static void LoadData(Dictionary MyCollection) { SqlConnection _Connection = new SqlConnection("{connection string}"); SqlCommand _Command = new SqlCommand("ImportData", _Connection); SqlDataReader _Reader = null; // only needed if getting data back from proc call SqlParameter _TVParam = new SqlParameter(); _TVParam.ParameterName = "@ImportTable"; // _TVParam.TypeName = "IDsAndOrderNumbers"; //optional for CommandType.StoredProcedure _TVParam.SqlDbType = SqlDbType.Structured; _TVParam.Value = SendRows(MyCollection); // method return value is streamed data _Command.Parameters.Add(_TVParam); _Command.CommandType = CommandType.StoredProcedure; try { _Connection.Open(); // Either send the data and move on with life: _Command.ExecuteNonQuery(); // OR, to get data back from a SELECT or OUTPUT clause: SqlDataReader _Reader = _Command.ExecuteReader(); { Do something with _Reader: If using INSERT or MERGE in the Stored Proc, use an OUTPUT clause to return INSERTED.[RowNum], INSERTED.[ID] (where [RowNum] is an IDENTITY), then fill a new Dictionary(ID, RowNumber) from _Reader.GetString(0) and _Reader.GetInt32(1). Return that instead of void. } } finally { _Reader.Dispose(); // optional; needed if getting data back from proc call _Command.Dispose(); _Connection.Dispose(); } } 

Los procedimientos almacenados no admiten matrices como entradas. Googlear da un par de hacks usando XML o cadenas separadas por comas, pero esos son hacks.

Una manera más SQLish de hacer esto es crear una tabla temporal (llamada por ejemplo #Orders ) e insertar todos los datos en esa. Luego puede llamar a la sp, usando la misma conexión Sql abierta e insie el SP usa la tabla #Orders para leer los valores.

Otra solución es usar Parámetros con valores de tabla, pero eso requiere más SQL para configurar, por lo que creo que probablemente sea más fácil usar el enfoque de tabla temporal.