将多个参数化变量添加到 C# 中的数据库 [英] Adding multiple parameterized variables to a database in c#
问题描述
我想在这里做一些类似的事情:如何在不重复INSERT INTO dbo.Blah"的情况下插入多行声明的一部分?除了在一个查询中执行此操作(比几十个更快)之外,我还想将其参数化,因为输入来自网络.
I'm looking to do something simulair toward here: How do I insert multiple rows WITHOUT repeating the "INSERT INTO dbo.Blah" part of the statement? except that in addition towards doing this in one query (faster then several dozen) I also want to do this parameterized as the input comes from the web.
目前我有
foreach(string data in Scraper){
SqlConnection conn = new SqlConnection(WebConfigurationManager.AppSettings["ConnectionInfo"].ToString());
string query = "INSERT INTO DATABASE('web',@data)";
SqlCommand sqlCommand= new SqlCommand(query, conn);
sqlCommand.Parameters.AddWithValue("@data", data);
Command.executeNonQuery();
conn.close();
}
这有点迟钝(注意,真实的例子有更多的列,但这会让事情变得更加混乱).
Which is a bit slugish (note the real example has a lot more colums but that would make things more confusing).
推荐答案
由于您使用的是 c#
和 sql server 2008
,因此您可以使用表值参数插入多行到您的数据库.以下是有关如何执行此操作的简短说明:
Since you are using c#
and sql server 2008
, you can use a table valued parameter to insert multiple rows to your database.
Here is a short description on how to do this:
首先,您需要创建一个用户定义的表类型:
First, you need to create a user defined table type:
CREATE TYPE MyTableType AS TABLE
(
Col1 int,
Col2 varchar(20)
)
GO
然后,您需要创建一个将接受该表类型作为参数的存储过程
Then, you need to create a stored procedure that will accept this table type as a parameter
CREATE PROCEDURE MyProcedure
(
@MyTable dbo.MyTableType READONLY -- NOTE: table valued parameters must be Readonly!
)
AS
INSERT INTO MyTable (Col1, Col2)
SELECT Col1, Col2
FROM @MyTable
GO
最后,从你的 c# 代码中执行这个存储过程:
Finally, execute this stored procedure from your c# code:
DataTable dt = new DataTable();
dt.Columns.Add("Col1", typeof(int));
dt.Columns.Add("Col2", typeof(string));
// Fill your data table here
using (var con = new SqlConnection("ConnectionString"))
{
using(var cmd = new SqlCommand("MyProcedure", con))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@MyTable", SqlDbType.Structured).Value = dt;
con.Open();
cmd.ExecuteNonQuery();
}
}
这篇关于将多个参数化变量添加到 C# 中的数据库的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!