Passing .Net data table as a User Defined Table to a Stored Procedure

When there is an updated data set that needs to be sent to a Stored Procedure for updating all records in the data set, what most do is, iterate the data set, access records one by one and call the stored procedure for each an every record. This is not a best practice and it affects to the performance of the application. Time it needs may not be noticed if the data set contains few records but it is a noticable when there are many records to be updated. What if we can send the entire data set with a single call and stored procedure can update all records with a single execution?

This is not something new but still do not use. SQL Server 2008 introduced User Defined Data Table that can be used for creating a new type similar to a Structure in programming languages. It can be used for accepting a data set to a stored procedure via a parameter and, since it is a table, data in the table can be accessed just like a normal table for manipulation.

Let's see how we can do it. Assume that I have a data set that contains updated recordset and need to pass it for a stored procedure. For that, first of all, we need to create a table type;

CREATE TYPE [dbo].[NoteSectionTagTable] AS TABLE
(
 NoteSectionId int not null
 , TagId int not null
)

Next is the stored procedure. See how it can be set. Note the Read Only keyword which is a must.

CREATE PROCEDURE [dbo].[UpdateNoteSectionTags]
 @NoteSectionTagTable NoteSectionTagTable READONLY
AS
BEGIN

 DELETE dbo.NoteSectionTags
 WHERE NoteSectionId IN (SELECT NoteSectionId FROM @NoteSectionTagTable);

 INSERT INTO dbo.NoteSectionTags
  (NoteSectionId, TagId)
 SELECT NoteSectionId, TagId
 FROM @NoteSectionTagTable;

 RETURN 0
END

Everything is ready in database level. Here is the way of creating a table table and passing it to the stored procedure using C#.Net.

// creating the table
System.Data.DataTable noteSectionTags = new System.Data.DataTable();

// adding columns
System.Data.DataColumn noteSectionIdColumn = new System.Data.DataColumn("NoteSectionId", typeof(int));
noteSectionTags.Columns.Add(noteSectionIdColumn);

System.Data.DataColumn tagIdColumn = new System.Data.DataColumn("TagId", typeof(int));
noteSectionTags.Columns.Add(tagIdColumn);

System.Data.DataRow noteSectionTag;

// adding rows
noteSectionTag = noteSectionTags.NewRow();
noteSectionTag["TagId"] = Convert.ToInt32(item.Value);
noteSectionTag["NoteSectionId"] = Convert.ToInt32(ViewState["NoteSectionId"]);
noteSectionTags.Rows.Add(noteSectionTag);

// we can add multiple rows like above

//creating sql command for calling the stored procedure
System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand("dbo.UpdateNoteSectionTags");
command.CommandType = CommandType.StoredProcedure;

//note the parameter data type
System.Data.SqlClient.SqlParameter parameterNoteSectionTagTable = new System.Data.SqlClient.SqlParameter("NoteSectionTagTable", SqlDbType.Structured);
parameterNoteSectionTagTable.Value = noteSectionTags;
command.Parameters.Add(parameterNoteSectionTagTable);

// getting the connection for the config
string connectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;

using (System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(connectionString))
{

    command.Connection = connection;
    connection.Open();
    command.ExecuteNonQuery();
    connection.Close();
}