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]
  9. GO
  11. SET ANSI_PADDING OFF
  12. GO
  14. ALTER TABLE [dbo].[Employee] ADD CONSTRAINT [DF_Employee_LastLogin] DEFAULT (getdate()) FOR [LastLogin]
  15. GO
  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
  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
  10. ALTER PROCEDURE [dbo].[ManageEmployee]
  11. @tblEmployee EmployeeType READONLY
  12. AS
  13. BEGIN
  14. SET NOCOUNT ON;
  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" %>
  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;
  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. }
  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. });
  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. }
  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.