将数据表传递给存储过程

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?

我想要的是将数据表传递给这个存储过程，怎么样？

3 个解决方案

#1

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

查看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的三种方法（TVP vs XML vs CSV）

#2

You need to define a Table Type that you want to pass in User-Defined Table Types in 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();

This should resolve your problem

这应该可以解决您的问题

#3

-1

We generally squirt the data into XML documents and pass the data to the database as an NTEXT parameter.

我们通常将数据喷射到XML文档中，并将数据作为NTEXT参数传递给数据库。

#1