Table Value Parameter in ASP.Net Using SQL Server

The following is my SQL Server Data Table:



The script of the table is:

  1. CREATE TABLE [dbo].[Employee](  
  2.     [ID] [int] IDENTITY(1,1) NOT NULL,  
  3.     [Name] [varchar](50) NULL,  
  4.     [Country] [varchar](50) NULL,  
  5.     [LastLogin] [smalldatetime] NULL,  
  6.     [JoinedOn] [datetime] NULL  
  7. ON [PRIMARY]  
  8.   
  9. GO  
  10.   
  11. SET ANSI_PADDING OFF  
  12. GO  
  13.   
  14. ALTER TABLE [dbo].[Employee] ADD  CONSTRAINT [DF_Employee_LastLogin]  DEFAULT (getdate()) FOR [LastLogin]  
  15. GO  
  16.   
  17. ALTER TABLE [dbo].[Employee] ADD  CONSTRAINT [DF_Employee_JoinedOn]  DEFAULT (getdate()) FOR [JoinedOn]  
  18. GO  
Now expand your DB and select Programmability -> Types -> User-Defined table Types as in the following:




  1. USE [TestDB]  
  2. GO  
  3.   
  4.  CREATE TYPE [dbo].[EmployeeType] AS TABLE(  
  5.     [Name] [varchar](50) NULL,  
  6.     [Country] [varchar](50) NULL,  
  7.     [LastLogin] [datetime] NULL,  
  8.     [JoinedOn] [datetime] NULL  
  9. )  
  10. GO  
Now create a new Stored Procedure as in the following:


  1. USE [TestDB]  
  2. GO  
  3. /****** Object:  StoredProcedure [dbo].[ManageEmployee]     
  4.  Script Date: 03/28/2015 16:58:22 ******/  
  5. SET ANSI_NULLS ON  
  6. GO  
  7. SET QUOTED_IDENTIFIER ON  
  8. GO  
  9.   
  10. ALTER PROCEDURE [dbo].[ManageEmployee]  
  11.       @tblEmployee EmployeeType READONLY  
  12. AS  
  13. BEGIN  
  14.       SET NOCOUNT ON;  
  15.        
  16.       INSERT INTO Employee(Name, Country, JoinedOn, LastLogin)  
  17.       SELECT Name, Country, JoinedOn, LastLogin FROM @tblEmployee  
  18. END  
Now for the application, Here I will read records from a XML file and show the records in a Grid View. In the Grid View I provided a check box option so the user can select records and insert a collection of records into the DB.

The following is My Employee.xml:


  1. <?xml version="1.0" encoding="utf-8" ?>  
  2.  <Employees>  
  3.   <Employee>      
  4.     <Name>Mayank</Name>  
  5.     <Country>India</Country>  
  6.     <JoinedOn>2015-01-03</JoinedOn>  
  7.     <LastLogin>2015-01-03</LastLogin>  
  8.   </Employee>  
  9.   <Employee>       
  10.     <Name>Rakesh</Name>  
  11.     <Country>USA</Country>  
  12.     <JoinedOn>2015-01-03</JoinedOn>  
  13.     <LastLogin>2015-01-03</LastLogin>  
  14.   </Employee>  
  15.   <Employee>       
  16.     <Name>Abhishek</Name>  
  17.     <Country>France</Country>  
  18.     <JoinedOn>2015-01-03</JoinedOn>  
  19.     <LastLogin>2015-01-03</LastLogin>  
  20.   </Employee>  
  21.   <Employee>      
  22.     <Name>Saurabh</Name>  
  23.     <Country>Dubai</Country>  
  24.     <JoinedOn>2015-01-03</JoinedOn>  
  25.     <LastLogin>2015-01-03</LastLogin>  
  26.   </Employee>  
Now my aspx is:
  1. <%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>  
  2.   
  3. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">  
  4. <html xmlns="http://www.w3.org/1999/xhtml">  
  5. <head runat="server">  
  6.     <title>Table Value Parameter</title>  
  7. </head>  
  8. <body>  
  9.     <form id="form1" runat="server">  
  10.     <div>  
  11.         <asp:GridView ID="GridViewEmployee" runat="server" AutoGenerateColumns="false">  
  12.             <Columns>  
  13.                 <asp:TemplateField>  
  14.                     <ItemTemplate>  
  15.                         <asp:CheckBox ID="CheckBox1" runat="server" />  
  16.                     </ItemTemplate>  
  17.                 </asp:TemplateField>  
  18.                 <asp:BoundField DataField="Name" HeaderText="Name" ItemStyle-Width="150" />  
  19.                 <asp:BoundField DataField="Country" HeaderText="Country" ItemStyle-Width="150" />  
  20.                 <asp:BoundField DataField="JoinedOn" HeaderText="Joined On" ItemStyle-Width="150" />  
  21.                 <asp:BoundField DataField="LastLogin" HeaderText="Last Login" ItemStyle-Width="150" />  
  22.             </Columns>  
  23.         </asp:GridView>  
  24.         <br />  
  25.         <asp:Button ID="btninsert" Text="Insert Records" runat="server" OnClick="btninsert_Click" />  
  26.     </div>  
  27.     </form>  
  28. </body>  
  29. </html>  
Now my aspx.cs is:
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Web;  
  5. using System.Web.UI;  
  6. using System.Web.UI.WebControls;  
  7. using System.Data;  
  8. using System.Data.SqlClient;  
  9. using System.Configuration;  
  10.   
  11. public partial class _Default : System.Web.UI.Page  
  12. {  
  13.     protected void Page_Load(object sender, EventArgs e)  
  14.     {  
  15.         if (!this.IsPostBack)  
  16.         {  
  17.             GetData();  
  18.         }  
  19.     }  
  20.   
  21.     private void GetData()  
  22.     {  
  23.         DataSet ds = new DataSet();  
  24.         ds.ReadXml(Server.MapPath("~/Employee.xml"));  
  25.         GridViewEmployee.DataSource = ds.Tables[0];  
  26.         GridViewEmployee.DataBind();  
  27.     }  
  28.     protected void btninsert_Click(object sender, EventArgs e)  
  29.     {  
  30.         DataTable dt = new DataTable();  
  31.         dt.Columns.AddRange(new DataColumn[4]  
  32.         {              
  33.             new DataColumn("Name"typeof(string)),  
  34.             new DataColumn("Country",typeof(string)),  
  35.             new DataColumn("JoinedOn"typeof(DateTime)),  
  36.             new DataColumn("LastLogin"typeof(DateTime))  
  37.         });  
  38.   
  39.         foreach (GridViewRow row in GridViewEmployee.Rows)  
  40.         {  
  41.             if ((row.FindControl("CheckBox1"as CheckBox).Checked)  
  42.             {  
  43.                 string name = row.Cells[1].Text;  
  44.                 string country = row.Cells[2].Text;  
  45.                 DateTime joinedDate = DateTime.Parse(row.Cells[3].Text);  
  46.                 DateTime lastLogin = DateTime.Parse(row.Cells[4].Text);  
  47.                 dt.Rows.Add(name, country, joinedDate, lastLogin);  
  48.             }  
  49.         }  
  50.         if (dt.Rows.Count > 0)  
  51.         {  
  52.             InsertRecordsToDB(dt);  
  53.         }  
  54.     }  
  55.   
  56.     protected void InsertRecordsToDB(DataTable dt)  
  57.     {  
  58.         using (SqlConnection con = new SqlConnection(@"Server=INDIA\MSSQLServer2k8;database=TestDB;UID=sa; pwd=india;"))  
  59.         {  
  60.             using (SqlCommand cmd = new SqlCommand("ManageEmployee"))  
  61.             {  
  62.                 cmd.CommandType = CommandType.StoredProcedure;  
  63.                 cmd.Connection = con;  
  64.                 cmd.Parameters.AddWithValue("@tblEmployee", dt);  
  65.                 con.Open();  
  66.                 cmd.ExecuteNonQuery();  
  67.                 con.Close();  
  68.             }  
  69.         }  
  70.     }  
  71. }  
Before running, the following are the records in my data table:



Now run the application. Select Records and click on the Insert button.



Now see the records in the table.