通过字典<string,int>到存储过程 T-SQL

2021-12-02 00:00:00 import tsql sql-server asp.net-mvc

我有 mvc 应用程序.实际上，我有 Dictionary.Key 是 ID，Value 是 sortOrderNumber.我想创建一个存储过程，它将获取 key(id) 在数据库中找到这条记录并通过 value 从字典中保存 orderNumber 列.我想调用一次存储过程并将数据传递给它，而不是多次调用更新数据.

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

相关文章