Table Value Parameter in ASP.Net Using SQL Server

Rahul Saxena
Mar 28, 2015
4.9k
0
1
Article

TableValueParameter.zip

The following is my SQL Server Data Table:

The script of the table is:

CREATE TABLE [dbo].[Employee](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NULL,
[Country] [varchar](50) NULL,
[LastLogin] [smalldatetime] NULL,
[JoinedOn] [datetime] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Employee] ADD CONSTRAINT [DF_Employee_LastLogin] DEFAULT (getdate()) FOR [LastLogin]
GO
ALTER TABLE [dbo].[Employee] ADD CONSTRAINT [DF_Employee_JoinedOn] DEFAULT (getdate()) FOR [JoinedOn]
GO

Now expand your DB and select Programmability -> Types -> User-Defined table Types as in the following:

USE [TestDB]
GO
CREATE TYPE [dbo].[EmployeeType] AS TABLE(
[Name] [varchar](50) NULL,
[Country] [varchar](50) NULL,
[LastLogin] [datetime] NULL,
[JoinedOn] [datetime] NULL
)
GO

Now create a new Stored Procedure as in the following:

USE [TestDB]
GO
/****** Object: StoredProcedure [dbo].[ManageEmployee]
Script Date: 03/28/2015 16:58:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ManageEmployee]
@tblEmployee EmployeeType READONLY
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO Employee(Name, Country, JoinedOn, LastLogin)
SELECT Name, Country, JoinedOn, LastLogin FROM @tblEmployee
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:

<?xml version="1.0" encoding="utf-8" ?>
<Employees>
<Employee>
<Name>Mayank</Name>
<Country>India</Country>
<JoinedOn>2015-01-03</JoinedOn>
<LastLogin>2015-01-03</LastLogin>
</Employee>
<Employee>
<Name>Rakesh</Name>
<Country>USA</Country>
<JoinedOn>2015-01-03</JoinedOn>
<LastLogin>2015-01-03</LastLogin>
</Employee>
<Employee>
<Name>Abhishek</Name>
<Country>France</Country>
<JoinedOn>2015-01-03</JoinedOn>
<LastLogin>2015-01-03</LastLogin>
</Employee>
<Employee>
<Name>Saurabh</Name>
<Country>Dubai</Country>
<JoinedOn>2015-01-03</JoinedOn>
<LastLogin>2015-01-03</LastLogin>
</Employee>

Now my aspx is:

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Table Value Parameter</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="GridViewEmployee" runat="server" AutoGenerateColumns="false">
<Columns>
<asp:TemplateField>
<ItemTemplate>
<asp:CheckBox ID="CheckBox1" runat="server" />
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField DataField="Name" HeaderText="Name" ItemStyle-Width="150" />
<asp:BoundField DataField="Country" HeaderText="Country" ItemStyle-Width="150" />
<asp:BoundField DataField="JoinedOn" HeaderText="Joined On" ItemStyle-Width="150" />
<asp:BoundField DataField="LastLogin" HeaderText="Last Login" ItemStyle-Width="150" />
</Columns>
</asp:GridView>
<br />
<asp:Button ID="btninsert" Text="Insert Records" runat="server" OnClick="btninsert_Click" />
</div>
</form>
</body>
</html>

Now my aspx.cs is:

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.Data.SqlClient;
using System.Configuration;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!this.IsPostBack)
{
GetData();
}
}
private void GetData()
{
DataSet ds = new DataSet();
ds.ReadXml(Server.MapPath("~/Employee.xml"));
GridViewEmployee.DataSource = ds.Tables[0];
GridViewEmployee.DataBind();
}
protected void btninsert_Click(object sender, EventArgs e)
{
DataTable dt = new DataTable();
dt.Columns.AddRange(new DataColumn[4]
{
new DataColumn("Name", typeof(string)),
new DataColumn("Country",typeof(string)),
new DataColumn("JoinedOn", typeof(DateTime)),
new DataColumn("LastLogin", typeof(DateTime))
});
foreach (GridViewRow row in GridViewEmployee.Rows)
{
if ((row.FindControl("CheckBox1") as CheckBox).Checked)
{
string name = row.Cells[1].Text;
string country = row.Cells[2].Text;
DateTime joinedDate = DateTime.Parse(row.Cells[3].Text);
DateTime lastLogin = DateTime.Parse(row.Cells[4].Text);
dt.Rows.Add(name, country, joinedDate, lastLogin);
}
}
if (dt.Rows.Count > 0)
{
InsertRecordsToDB(dt);
}
}
protected void InsertRecordsToDB(DataTable dt)
{
using (SqlConnection con = new SqlConnection(@"Server=INDIA\MSSQLServer2k8;database=TestDB;UID=sa; pwd=india;"))
{
using (SqlCommand cmd = new SqlCommand("ManageEmployee"))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = con;
cmd.Parameters.AddWithValue("@tblEmployee", dt);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
}
}