通过字典＆LT;字符串，整数＆GT;存储过程的T-SQL [英] Pass Dictionary<string,int> to Stored Procedure T-SQL

问题描述

我MVC应用程序。在行动中我有词典＆LT;字符串，整数＆GT; 。在键是ID和值是sortOrderNumber。我想创建存储过程，将获得钥匙（ID）在数据库中找不到此记录，并通过值订单编号列>从字典。我想打电话给存储过程，一旦时间和传递数据给它，而不是调用多次更新数据。

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!

推荐答案

使用表值参数是真的不那么复杂。

Using Table Valued parameters is really not that complex.

鉴于这种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;
        }
    }
}

生成

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

这篇关于通过字典＆LT;字符串，整数＆GT;存储过程的T-SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！