Passing multiple records to a Stored Procedure / Passing DataTable to StoredProcedure / table valued parameters in sql server 2008 / SqlDbType.Structured

As the Title Says we can pass Multiple rows to a Stored Procedure or DataTable to a Stored Procedure by using SqlDbType.Structured... 
This is also known as Table valued Parameter which is introduced in SQL Server 2008

1. Create a Table Name as EMPTable  as shown below

CREATE TABLE [dbo].[EMPTable](

            [empid] [int] IDENTITY(1001,1) NOT NULL PRIMARY KEY,

            [fname] [nvarchar](50) NULL,

            [lname] [nvarchar](50) NULL,

            [sal] [int] NULL

)

 2. Create TableValued Parameter as shown below. This is important because it plays the important  

      role, the entire datatable is sent into this TableValued Parameter.

CREATE TYPE [dbo].[EMP_Insert] AS TABLE(

            [fname] [nvarchar](50) NULL,

            [lname] [nvarchar](50) NULL,

            [sal] [int] NULL

)

3. Create a Stored Procedure as shown below

create proc [dbo].[EMP_Insert_TableValued]

(

            @MyTableParameter EMP_Insert READONLY

)

as

begin

            insert into EMPTable (fname,lname,sal) select * from @MyTableParameter

end

Note : 

•  DataType in the Table and Table Valued Parameter should be equal
• Column Positions should be equal in DataTable and Table Valued Parameter

Now coming to design part, Default.aspx page looks as shown...

<%@ Page Title="Home Page" Language="C#" MasterPageFile="~/Site.master" 

   AutoEventWireup="true"
    CodeBehind="Default.aspx.cs" Inherits="TableValuedParms._Default" %>
 
<asp:Content ID="HeaderContent" runat="server" ContentPlaceHolderID="HeadContent">
</asp:Content>
<asp:Content ID="BodyContent" runat="server" ContentPlaceHolderID="MainContent">
    <center>
        <table style="width: 25%;">
            <tr>
                <td align="right" width="50%">
                    First Name :
                </td>
                <td align="left">
                    <asp:TextBox ID="txtFName" runat="server"></asp:TextBox>
                </td>
            </tr>
            <tr>
                <td align="right" width="50%">
                    Last Name :
                </td>
                <td align="left">
                    <asp:TextBox ID="txtLName" runat="server"></asp:TextBox>
                </td>
            </tr>
            <tr>
                <td align="right" width="50%">
                    Salary :
                </td>
                <td align="left">
                    <asp:TextBox ID="txtSal" runat="server"></asp:TextBox>
                </td>
            </tr>
            <tr>
                <td align="right" width="50%">
                    &nbsp;
                </td>
                <td align="left">
                    &nbsp;
                    <asp:Button ID="btnSubmit" runat="server" 

                            OnClick="btnSubmit_Click" Text="Submit" />
                </td>
            </tr>
            <tr>
                <td align="right" width="50%">
                    &nbsp;
                </td>
                <td align="left">
                    &nbsp;&nbsp;
                </td>
            </tr>
            <tr>
                <td align="center" colspan="2">
                    <asp:GridView ID="GridView1" runat="server">
                    </asp:GridView>
                </td>
            </tr>
            <tr>
                <td colspan="2" align="center">
                    <asp:Button ID="btnInsert" runat="server" 

                     OnClick="btnInsert_Click" Text="Insert InTo Table" />
                </td>
            </tr>
            <tr>
                <td colspan="2" align="center">
                    <asp:Label ID="lblRes" runat="server" Text=" "></asp:Label>
                </td>
            </tr>
        </table>
    </center>
</asp:Content> 

Now The Code Behind looks as shown below....

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
 
namespace TableValuedParms
{
    public partial class _Default : System.Web.UI.Page
    {
        static DataTable dt;
        DataRow dr;
        DataRow dr1;
        private string Constring =  

             ConfigurationManager.ConnectionStrings["constring"].ConnectionString;
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                dt = new DataTable();
                dt.Columns.Add("fname",typeof(string));
                dt.Columns.Add("lname",typeof(string));
                dt.Columns.Add("sal",typeof(Int32));
            }
        }
 
        protected void btnSubmit_Click(object sender, EventArgs e)
        {
            dr1 = dt.NewRow();
            dr1["fname"] = txtFName.Text;
            dr1["lname"] = txtLName.Text;
            dr1["sal"] = txtSal.Text;
            dt.Rows.Add(dr1);
 
            GridView1.DataSource = dt;
            GridView1.DataBind();
        }
 
        protected void btnInsert_Click(object sender, EventArgs e)
        {
            using (SqlConnection con = new SqlConnection(Constring))
            {
                con.Open();
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = con;
                    cmd.CommandText = "EMP_Insert_TableValued";
                    cmd.CommandType = CommandType.StoredProcedure;
                    SqlParameter Prm = 

                        cmd.Parameters.AddWithValue("@MyTableParameter", dt);
                    Prm.SqlDbType = SqlDbType.Structured;
                    Prm.TypeName = "dbo.EMP_Insert";
                    int retVal = cmd.ExecuteNonQuery();
                    lblRes.Text = retVal.ToString() + " Rows Inserted.";
                }
            }
        }
    }
}

Download the code from HERE