Suppose, My requirement is something like that I have
to pass a collection of records to Data base for processing. Here in SQL Server
2008 we make a custom data type TABLE to pass a collection of rows to a Stored
Procedure.
Below is my SQL server Data Table:
Image 1.
Script
of 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 -> Programmability -> Types -> User-Defined table Types
Image 2.
Image 3.
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 :
Image 4.
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 Come to Application Here I am going to read records from
a XML file and showing these records in a Grid View. In Grid View I gave a
check box option user can select any records and can insert collection of
records into DB.
Below is My Employee.xml
Image 5.
<?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>
</Employees>
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();
}
}
}
}
Before Running, Records in
my Data Table:
Image 6.
Now Run the application. Select Records and click on Insert
button.
Image 7.
Now see records in Table.
Image 8.