在C#中添加多个参数变量数据库 [英] Adding multiple parameterized variables to a database in c#

查看:559
本文介绍了在C#中添加多个参数变量数据库的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我在找做一些对这里simulair:<一href=\"https://stackoverflow.com/questions/2624713/how-do-i-insert-multiple-rows-without-repeating-the-insert-into-dbo-blah-part\">How做我插不重复多行&QUOT; INSERT INTO dbo.Blah&QUOT;声明的一部分?
不同之处在于,除了对在一个查询(快则几十个),这样做我也想这样做参数为输入来自网络。

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();
}

这是一个有点slugish(注意实际的例子有很多更colums但是这会使事情更加混乱)。

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#code执行此存储过程:

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 (SqlConnection con = new SqlConnection("ConnectionString"))
{
    SqlCommand cmd = new SqlCommand("MyProcedure", con);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add("@MyTable", SqlDbType.Structured).Value = dt;
    con.Open();
    cmd.ExecuteNonQuery();
    con.Close();
}

这篇关于在C#中添加多个参数变量数据库的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆