Pass Dictionarylt;string,intgt; to Stored Procedure T-SQL(通过字典string,int到存储过程 T-SQL)
问题描述
我有 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
这篇关于通过字典<string,int>到存储过程 T-SQL的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持编程学习网!
本文标题为:通过字典<string,int>到存储过程 T-SQL


- 如何使用 pip 安装 Python MySQLdb 模块? 2021-01-01
- SQL 临时表问题 2022-01-01
- 使用 Oracle PL/SQL developer 生成测试数据 2021-01-01
- 以一个值为轴心,但将一行上的数据按另一行分组? 2022-01-01
- 更改自动增量起始编号? 2021-01-01
- 如何将 Byte[] 插入 SQL Server VARBINARY 列 2021-01-01
- 如何将 SonarQube 6.7 从 MySQL 迁移到 postgresql 2022-01-01
- 在SQL中,如何为每个组选择前2行 2021-01-01
- 导入具有可变标题的 Excel 文件 2021-01-01
- 远程 mySQL 连接抛出“无法使用旧的不安全身份验证连接到 MySQL 4.1+"来自 XAMPP 的错误 2022-01-01