Email: Password:       Forgot Password 
    .netCodeSG
A Saarsha Group Online Community for dot net codes group like C#, Asp.NET, VB.NET, Sharepoint, JavaScript, JQuery, Ajax, SQL, WCF, WPF.
 
TECHNOLOGIES:
 

Table Value Parameter in ASP Dot Net And Sql Server

Article:
Viewed:  826 
Posted On:  28/03/2015 03:47:21 
How we can pass Table value Parameter in ASP.net? How we can process on a collection of records in asp.net c# with SQL Server ? 

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:

1.png

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

2.png

Image 2.

3.png

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 :

4.png

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

5.png

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:

6.png

Image 6.

 

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

 

7.png 

Image 7.

 

Now see records in Table.

 8.png

 

Image 8.

  Comment:
         HOME   |   Submit Article   |   Contact Us   |   About Us   |   Terms & Condition   |   Advertise With us