使用参数SQL Server插入多行 [英] Insert multiple rows with parameters Sql Server
问题描述
我试图弄清楚是否有一种方法可以在使用参数的情况下在Sql Server中执行多值插入,确切地说,是具有以下命令的
I am trying to figure out if there is a way to perform a multiple values insert in Sql Server while using parameters, to be precise, having a command like this:
com = new SqlCommand("insert into myTable values (@recID,@tagID)", con);
com.Parameters.Add("@recID", SqlDbType.Int).Value = recID;
com.Parameters.Add("@tagID", SqlDbType.Int).Value = tagID;
com.ExecuteNonQuery();
是否有一种方法可以考虑到每个值的参数可能不同,从而使用参数执行多个值的单次插入?(例如:tagID可能总是不同)
Is there a way to perform a multiple values single insert with parameters taking into account that parameters may be different for each value? (Example: tagID may be always different)
我一直在互联网上搜索,但到目前为止,还没有运气,在此先感谢您的问候.
I have been searching in Internet but no luck so far, thanks in advance, greetings.
推荐答案
You can use a table valued parameters : How to pass table value parameters to stored procedure from .net code
首先,在SQL Server中创建类型:
First, create the type, in SQL Server :
CREATE TYPE [dbo].[myTvpType] AS TABLE
(
[RecordID] int,
[TagID] int
)
以及用于插入数据的C#代码:
And the C# code to insert your data :
internal void InsertData(SqlConnection connection, Dictionary<int, int> valuesToInsert)
{
using (DataTable myTvpTable = CreateDataTable(valuesToInsert))
using (SqlCommand cmd = connection.CreateCommand())
{
cmd.CommandText = "INSERT INTO myTable SELECT RecordID, TagID FROM @myValues";
cmd.CommandType = CommandType.Text;
SqlParameter parameter = cmd.Parameters.AddWithValue("@myValues", myTvpTable);
parameter.SqlDbType = SqlDbType.Structured;
cmd.ExecuteNonQuery();
}
}
private DataTable CreateDataTable(Dictionary<int, int> valuesToInsert)
{
// Initialize the DataTable
DataTable myTvpTable = new DataTable();
myTvpTable.Columns.Add("RecordID", typeof(int));
myTvpTable.Columns.Add("TagID", typeof(int));
// Populate DataTable with data
foreach(key in valuesToInsert.Key)
{
DataRow row = myTvpTable.NewRow();
row["RecordID"] = valuesToInsert[key];
row["TagID"] = key;
}
}
这篇关于使用参数SQL Server插入多行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!