Pass Dictionary to Stored Procedure T-SQL

问题:

I have mvc application. In action I have Dictionary<string,int>. The Key is ID and Value is sortOrderNumber. I want to create stored procedure that will be get key(id) find this record in database and save orderNumber column by value from Dictionary. I want to call stored procedure once time and pass data to it, instead of calling many times for updating data.

Have you any ideas? Thanks!

回答1:

Using Table Valued parameters is really not that complex.

given this SQL:

CREATE TYPE MyTableType as TABLE (ID nvarchar(25),OrderNumber int) 


CREATE PROCEDURE MyTableProc (@myTable MyTableType READONLY)    
   AS
   BEGIN
    SELECT * from @myTable
   END

this will show how relatively easy it is, it just selects out the values you sent in for demo purposes. I am sure you can easily abstract this away in your case.

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<string, int>();
            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<string, int> 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;
        }
    }
}

Produces

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

回答2:

The accepted answer of using a TVP is generally correct, but needs some clarification based on the amount of data being passed in. Using a DataTable is fine (not to mention quick and easy) for smaller sets of data, but for larger sets it does not scale given that it duplicates the dataset by placing it in the DataTable simply for the means of passing it to SQL Server. So, for larger sets of data there is an option to stream the contents of any custom collection. The only real requirement is that you need to define the structure in terms of SqlDb types and iterate through the collection, both of which are fairly trivial steps.

A simplistic overview of the minimal structure is shown below, which is an adaptation of the answer I posted on How can I insert 10 million records in the shortest time possible?, which deals with importing data from a file and is hence slightly different as the data is not currently in memory. As you can see from the code below, this setup is not overly complicated yet highly flexible as well as efficient and scalable.

SQL object # 1: Define the 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 object # 2: Use the 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

C# code, Part 1: Define the iterator/sender

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

C# code, Part 2: Use the iterator/sender

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

回答3:

Stored procedures do not support arrays as inputs. Googling gives a couple of hacks using XML or comma separated strings, but those are hacks.

A more SQLish way to do this is to create a temporary table (named e.g. #Orders) and insert all the data into that one. Then you can call the sp, using the same open Sql Connection and insie the SP use the #Orders table to read the values.

Another solution is to use Table-Valued Parameters but that requires some more SQL to setup so I think it is probably easier to use the temp table approach.