Passing DataTable to stored procedure as an argument

Question

I have a data table created in C#.

DataTable dt = new DataTable();
dt.Columns.Add("Name", typeof(string));
dt.Columns.Add("Age", typeof(int));

dt.Rows.Add("James", 23);
dt.Rows.Add("Smith", 40);
dt.Rows.Add("Paul", 20);

I want to pass this to the following stored procedure.

CREATE PROCEDURE  SomeName(@data DATATABLE)
AS
BEGIN
    INSERT INTO SOMETABLE(Column2,Column3)
    VALUES(......);
END

My question is : How do we insert those 3 tuples to the SQL table ? do we need to access the column values with the dot operator ? or is there any other way of doing this?

StuartLC · Answer 1 · 2019-03-08T06:55:03.540

20

You can change the stored procedure to accept a table valued parameter as an input. First however, you will need to create a user defined table TYPE which matches the structure of the C# DataTable:

CREATE TYPE dbo.PersonType AS TABLE
(
    Name NVARCHAR(50), -- match the length of SomeTable.Column1
    Age INT
);

Adjust your SPROC:

CREATE PROCEDURE dbo.InsertPerson
    @Person dbo.PersonType READONLY
AS
BEGIN
  INSERT INTO SomeTable(Column1, Column2) 
     SELECT p.Name, p.Age
     FROM @Person p;
END

In C#, when you bind the datatable to the PROC parameter, you need to specify the parameter as:

parameter.SqlDbType = SqlDbType.Structured;
parameter.TypeName = "dbo.PersonType";

See also the example here Passing a Table-Valued Parameter to a Stored Procedure

edited Mar 08 '19 at 06:55

answered Apr 08 '15 at 06:46

StuartLC

104,537
17
209
285

Thank you for your answer. I am referring to MS SQL Server 2012. This type creation in it is not possible. Can you provide an alternative pls ? – nidarshani fernando Apr 08 '15 at 06:54
1

TVPs were made available in [SQL 2008 already](https://technet.microsoft.com/en-us/library/bb522526(v=sql.105).aspx) – StuartLC Apr 08 '15 at 06:58
when I tried creating the Type, it generates the error saying "syntax error near AS". can't figure out why it is happening – nidarshani fernando Apr 08 '15 at 07:09
Can you do a quick `SELECT @@VERSION`. I'm guessing you are <= 2005. – StuartLC Apr 08 '15 at 07:13
That's sad. If you can't upgrade, google for techniques of using Xml to pass collections and graphs down to stored procedures. You'll then serialize the DataSet to Xml in your data access layer, and then parse the @Xml in your proc. It really is messy, however. – StuartLC Apr 08 '15 at 07:29

score 2 · Answer 2 · answered Apr 08 '15 at 06:53

First you need to create a Userdefined type of table that resembles your actual table. See the example below,

CREATE TYPE SomeType AS TABLE 
(
   C1 int, 
   C2 VARCHAR(50)
)
After this you need to create a stored procedure that takes this table type   as parameter.


CREATE PROCEDURE SomeUSP
  @tabType SomeType READONLY
AS
BEGIN
   SET NOCOUNT ON;

   INSERT INTO Yourtable(C1,C2)
   SELECT C1,C2 FROM @tabType 
END

That's it...Job done :)

netDevMark · Answer 3 · 2023-05-13T22:13:37.430

In SQL Server, you can pass a DataTable from .NET to a stored procedure using a feature called table-valued parameters. Here's an example of how you might do that:

Define a table type in SQL Server. You need to create a user-defined table type in SQL Server that matches the structure of your DataTable:

CREATE TYPE dbo.MyTableType AS TABLE
(
    Name NVARCHAR(50),
    Age INT
);

Note the READONLY keyword, which is required for table-valued parameters.

Pass the DataTable to the stored procedure from .NET. You can use ADO.NET to call the stored procedure and pass the DataTable as a parameter:

using (SqlConnection conn = new SqlConnection(connectionString))
{
    conn.Open();

    using (SqlCommand cmd = new SqlCommand("SomeName", conn))
    {
        cmd.CommandType = CommandType.StoredProcedure;

        SqlParameter param = cmd.Parameters.AddWithValue("@data", dt);
        param.SqlDbType = SqlDbType.Structured;
        param.TypeName = "dbo.MyTableType";

        cmd.ExecuteNonQuery();
    }
}

Note that Entity Framework Core (as of version 5) does not natively support table-valued parameters, so you would need to use ADO.NET for this. EF Core does allow you to execute raw SQL commands, so you could potentially use that to call the stored procedure, but you'd still need to create the SqlParameter using ADO.NET.

Also, be aware that this feature is specific to SQL Server. Other database systems might not support table-valued parameters, or might support them in a different way.

Passing DataTable to stored procedure as an argument

3 Answers3

Linked