Pass Dictionary<string,int> to Stored Procedure T-SQL
asp.net-mvc, import, sql-server, t-sql
Solution
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
Problem
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!