Passing datatable to store procedure in SQL Server 2008

Here are good news for .net developer. Now we can pass the datatable directory to Store Procedure in SQL Server 2008

First you have to create type

CREATE TYPE  CategoryTableType AS TABLE
    ( CategoryID int, CategoryName nvarchar(50) )

Now create SP which accept the table value parameter. Here readonly word is required.

CREATE PROCEDURE usp_UpdateCategories
    (@tvpNewCategories dbo.CategoryTableType READONLY)

Now Pass datatable to store procedureusing (connection)
{
// Create a DataTable with the modified rows.
DataTable addedCategories =
  CategoriesDataTable.GetChanges(DataRowState.Added);

// Configure the SqlCommand and SqlParameter.
SqlCommand insertCommand = new SqlCommand(
    "usp_InsertCategories", connection);
insertCommand.CommandType = CommandType.StoredProcedure;
SqlParameter tvpParam = insertCommand.Parameters.AddWithValue(
    "@tvpNewCategories", addedCategories);
tvpParam.SqlDbType = SqlDbType.Structured;

// Execute the command.
insertCommand.ExecuteNonQuery();
}