将数据表传递给存储过程

时间:2021-10-31 10:12:31

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


15  

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


9  

  1. You need to define a Table Type that you want to pass in User-Defined Table Types in your database.

    您需要定义要在数据库中的用户定义表类型中传递的表类型。

  2. Then you need to add the parameter in your Stored Procedure to pass it in like this:

    然后,您需要在存储过程中添加参数以将其传递给:

    @YourCustomTable AS [dbo].YourCustomTable Readonly,
    
  3. 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


15  

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


9  

  1. You need to define a Table Type that you want to pass in User-Defined Table Types in your database.

    您需要定义要在数据库中的用户定义表类型中传递的表类型。

  2. Then you need to add the parameter in your Stored Procedure to pass it in like this:

    然后,您需要在存储过程中添加参数以将其传递给:

    @YourCustomTable AS [dbo].YourCustomTable Readonly,
    
  3. 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参数传递给数据库。