Data table as PARAMETER to Stored Procedure in SQL SERVER 2008

Here i will explain how to pass Data-table as parameter to a Stored Procedure in SQL Server 2008.

Create a table:

Create table ShoppingList
(ShoppingId INT IDENTITY(1,1) PRIMARY KEY,
Shoppingdate DATETIME DEFAULT GETDATE(),
ProductID INT,
Quantity INT,
ProductPrice MONEY)
 

Create the table data type:

CREATE TYPE dbo.TblShoppingList AS TABLE
(
Product INT,
Quantity INT,
ProductPrice MONEY
)
 

Create the Stored Procedure that has the table variable as the parameter:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE dbo.InsertShoppingList
(
    @ShoppingVAR dbo.TblShoppingList READONLY
)
AS
BEGIN
 SET NOCOUNT ON;
 INSERT INTO ShoppingList(ProductID, Quantity, ProductPrice)
 SELECT Product, Quantity, ProductPrice FROM @ShoppingVAR
 SET NOCOUNT OFF; 
END
GO

Here is the C# code for a console application:

static void Main(string[] args)
{
    DataSet dataset = new DataSet();
    DataTable dataTable = new DataTable();
    dataTable.Columns.Add("ProductId");

    dataTable.Columns.Add("Quantity");

    dataTable.Columns.Add("ProductPrice"); 

    var dr = dataTable.NewRow(); 

    dr["ProductId"] = 1; 

    dr["Quantity"] = 1;

    dr["ProductPrice"] = 1; 

    dataTable.Rows.Add(dr);
    using (SqlConnection conn = new SqlConnection(
      @"Data Source=.\SqlExpress;Initial Catalog=Shopping;Integrated Security=True;"))
    {
        conn.Open();       
        using (SqlCommand cmd = new SqlCommand("[dbo].[InsertShoppingList]"))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = conn;
            cmd.Parameters.AddWithValue("@ShoppingVAR", dataTable);
            using (SqlDataAdapter adp = new SqlDataAdapter(cmd))
            {
                adp.Fill(dataset);
            }
        }
    }
}