Passing datatable to a stored procedure

Question

I have a datatable created in 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?

Solution 1

You can use a Table Valued Parameter as of 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)

Solution 2

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

Comments

[Admin]

Do I have to create a table type and define the structure in SQL Server?

[AdaTheDev]

Yes, need to create a new table type

[Reza M.A]

That's right if we create Table-Valued Parameter Types as @AdaTheDev linked. eg: CREATE TYPE dbo.CategoryTableType AS TABLE ( CategoryID int, CategoryName nvarchar(50) )

[DenverJT]

I had to set the parameter's TypeName property as well. That meant I couldn't use the AddWithValue method. But this answer got me on the right track.