C# 将数据表传递给存储过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12320594/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Passing datatable to a stored procedure
提问by
I have a datatable created in C#.
我有一个用 C# 创建的数据表。
using (DataTable dt = new DataTable())
{
dt.Columns.Add("MetricId", typeof(int));
dt.Columns.Add("Descr", typeof(string));
dt.Columns.Add("EntryDE", typeof(int));
foreach (DataGridViewRow row in dgv.Rows)
{
dt.Rows.Add(row.Cells[1].Value, row.Cells[2].Value, row.Cells[0].Value);
}
// TODO: pass dt
}
And I have a stored procedure
我有一个存储过程
CREATE PROCEDURE [dbo].[Admin_Fill]
-- Add the parameters for the stored procedure here
@MetricId INT,
@Descr VARCHAR(100),
@EntryDE VARCHAR(20)
What I want is to pass the datatable to this stored procedure, how?
我想要的是将数据表传递给这个存储过程，如何？
采纳答案by AdaTheDev
You can use a Table Valued Parameter as of SQL Server 2008 / .NET 3.5....
从 SQL Server 2008 / .NET 3.5 开始，您可以使用表值参数......
Check out the guide on MSDN
Also, as there other options available, I have a comparisonof 3 approaches of passing multiple values (single field) to a sproc (TVP vs XML vs CSV)
此外，由于还有其他选项可用，我比较了将多个值（单个字段）传递给 sproc 的 3 种方法（TVP 与 XML 与 CSV）
回答by Robbie Dee
We generally squirt the data into XML documents and pass the data to the database as an NTEXT parameter.
我们通常将数据喷入 XML 文档，并将数据作为 NTEXT 参数传递给数据库。
回答by Tabish Sarwar
You need to define a Table Type that you want to pass in User-Defined Table Typesin your database.
Then you need to add the parameter in your Stored Procedure to pass it in like this:
@YourCustomTable AS [dbo].YourCustomTable Readonly,Then, when you have your rows setup, call it like this:
// Setup SP and Parameters command.CommandText = "YOUR STORED PROC NAME"; command.Parameters.Clear(); command.CommandType = CommandType.StoredProcedure; command.Parameters.AddWithValue("@someIdForParameter", someId); command.Parameters.AddWithValue("@YourCustomTable",dtCustomerFields).SqlDbType = SqlDbType.Structured; //Execute command.ExecuteNonQuery();
您需要定义要在数据库中的用户定义表类型中传递的表类型。
然后你需要在你的存储过程中添加参数来传递它，如下所示：
@YourCustomTable AS [dbo].YourCustomTable Readonly,然后，当你设置好行时，像这样调用它：
// Setup SP and Parameters command.CommandText = "YOUR STORED PROC NAME"; command.Parameters.Clear(); command.CommandType = CommandType.StoredProcedure; command.Parameters.AddWithValue("@someIdForParameter", someId); command.Parameters.AddWithValue("@YourCustomTable",dtCustomerFields).SqlDbType = SqlDbType.Structured; //Execute command.ExecuteNonQuery();
This should resolve your problem
这应该可以解决您的问题
