In the previous article SQL- Passing table valued parameter in stored procedure, we explored table valued parameter. In this article we will find out how to call stored procedure with TVP parameter from .NET. This is long awaited feature of passing table or array to stored procedure.

I will use the "sp_testtvp_i" stored procedure which i have created in my last article SQL- Passing table valued parameter in stored procedure

Let's write code and see how we can do this.

Step 1: Create a table TestTVP with name and age as column.


CREATE TABLE TestTVP(id INT , name VARCHAR(50))


Step 2:  Creat table type TestTVPType

CREATE TYPE TestTVPType AS TABLE
(

      id INT,
      name VARCHAR(10)
)

 

Step 3: Create stored procedure sp_testtvp_i which accepts table type parameter.

CREATE PROCEDURE sp_testtvp_i(@testtvptype TestTVPType READONLY)
AS
      INSERT INTO TestTVP(id, name)
      SELECT id , name FROM @testtvptype;
GO 

Step 4:  Now we will call above stored procedure from .NET.

using (SqlConnection conn = new SqlConnection("Provide connection string"))
{
        DataTable dt = new DataTable();
        dt.Columns.Add(new DataColumn("age"));
        dt.Columns.Add(new DataColumn("name"));
        dt.Rows.Add(1, "John" );
        dt.Rows.Add(2, "Andy" );
        SqlCommand insertCmd = new SqlCommand("sp_testtvp_i", conn);
        insertCmd.CommandType = CommandType.StoredProcedure;
        SqlParameter tvpParam = insertCmd.Parameters.AddWithValue("@testtvptype", dt);
        tvpParam.SqlDbType = SqlDbType.Structured;
        conn.Open();
        insertCmd.ExecuteNonQuery();

}

In case of table valued parameter (TVP) the SqlDbType should be SqlDbType.Structured .

If you have any column in the table as identity one has to do SET  IDENTITY_INSERT for the same session.

Let's see how we can call stored procedure which inserts data to a table which has one column as Identity. Let's make id as identity.

  Step 1: 

ALTER TABLE TestTVP
DROP COLUMN id

ALTER TABLE TestTVP
ADD id INT not null IDENTITY(1,1)

 Step 2:  Now before calling stored procedure we need to set the SET  IDENTITY_INSERT ON

using (SqlConnection conn = new SqlConnection(""Provide connection string"))
{
        DataTable dt = new DataTable();
        dt.Columns.Add(new DataColumn("id"));       
         dt.Columns.Add(new DataColumn("name"));
        dt.Rows.Add(1, "John");       
         dt.Rows.Add(2, "Andy");
        SqlCommand insertCmd = new SqlCommand("SET IDENTITY_INSERT TestTVP ON", conn);
        conn.Open();       
         insertCmd.ExecuteNonQuery();

        insertCmd = new SqlCommand("sp_testtvp_i", conn);
        insertCmd.CommandType = CommandType.StoredProcedure;       
         SqlParameter tvpParam = insertCmd.Parameters.AddWithValue("@testtvptype", dt);
        tvpParam.SqlDbType = SqlDbType.Structured;
        insertCmd.ExecuteNonQuery();

}

SET IDENTITY_INSERT will be off once connection is closed. One can also set the IDENTITY_INSERT ON or OFF in the stored procedure.

This ends the article of inserting datatable to stored procedure.