Passer le dictionnaire à la procédure stockée T-SQL

la réponse acceptée de l'utilisation d'un PVT est généralement correcte, mais nécessite une clarification basée sur la quantité de données transmises. Utiliser un DataTable est très bien (pour ne pas mentionner rapide et facile) pour des ensembles de données plus petits, mais pour les ensembles plus grands, il ne se met pas à l'échelle étant donné qu'il duplique l'ensemble de données en le plaçant dans le DataTable simplement pour les moyens de le passer à SQL Server. Ainsi, pour des ensembles plus grands de données il y a une option pour streamer le contenu de n'importe quelle collection personnalisée. Le seul réel l'exigence est que vous devez définir la structure en termes de types SqlDb et itérer à travers la collection, les deux qui sont des étapes assez triviales.

un aperçu simpliste de la structure minimale est montré ci-dessous, qui est une adaptation de la réponse que j'ai posté sur Comment puis-je insérer 10 millions d'enregistrements dans le plus court délai possible? , qui traite de l'importation de données à partir d'un fichier et est donc légèrement différent car les données ne sont pas actuellement mémoire. Comme vous pouvez le voir à partir du code ci-dessous, cette configuration n'est pas trop compliquée mais très flexible ainsi que efficace et évolutive.

SQL objet # 1: Définir la structure

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

SQL objet # 2: utiliser la structure

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

code C, Partie 1: Définir l'itérateur / expéditeur

using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using Microsoft.SqlServer.Server;

private static IEnumerable<SqlDataRecord> SendRows(Dictionary<string,int> 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<string,int> _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;
      }
}

code C, PARTIE 2: Utiliser l'itérateur / expéditeur

public static void LoadData(Dictionary<string,int> 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<string, int>(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();
   }
}

ne prennent pas en charge les tableaux en tant qu'entrées. Googling donne quelques hacks en utilisant XML ou des chaînes séparées par des virgules, Mais ce sont des hacks.

une façon plus SQLish de faire ceci est de créer une table temporaire (nommée par exemple #Orders ) et d'insérer toutes les données dans celle-ci. Ensuite, vous pouvez appeler le sp, en utilisant la même connexion Sql ouverte et dans le SP utiliser la table #Orders pour lire les valeurs.

un autre la solution est d'utiliser table-Valued Parameters mais qui nécessite un peu plus de SQL à la configuration donc je pense qu'il est probablement plus facile d'utiliser l'approche de la table de température.