This is also known as Table valued Parameter which is introduced in SQL Server 2008
1. Create a Table Name as EMPTable as shown below
CREATE TABLE [dbo].[EMPTable](
[empid]
[int] IDENTITY(1001,1) NOT NULL PRIMARY KEY,
[fname]
[nvarchar](50) NULL,
[lname]
[nvarchar](50) NULL,
[sal]
[int] NULL
)
2. Create TableValued Parameter as shown below. This is important because it plays the important
role, the entire datatable is sent into this TableValued Parameter.
CREATE TYPE [dbo].[EMP_Insert]
AS TABLE(
[fname]
[nvarchar](50)
NULL,
[lname]
[nvarchar](50)
NULL,
[sal]
[int] NULL
)
3. Create a Stored Procedure as shown below
create proc [dbo].[EMP_Insert_TableValued]
(
@MyTableParameter
EMP_Insert READONLY
)
as
begin
insert into EMPTable (fname,lname,sal) select * from @MyTableParameter
end
Note :
- DataType in the Table and Table Valued Parameter should be equal
- Column Positions should be equal in DataTable and Table Valued Parameter
Now coming to design part, Default.aspx page looks as shown...
<%@ Page Title="Home Page" Language="C#" MasterPageFile="~/Site.master"
AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="TableValuedParms._Default" %> <asp:Content ID="HeaderContent" runat="server" ContentPlaceHolderID="HeadContent"> </asp:Content> <asp:Content ID="BodyContent" runat="server" ContentPlaceHolderID="MainContent"> <center> <table style="width: 25%;"> <tr> <td align="right" width="50%"> First Name : </td> <td align="left"> <asp:TextBox ID="txtFName" runat="server"></asp:TextBox> </td> </tr> <tr> <td align="right" width="50%"> Last Name : </td> <td align="left"> <asp:TextBox ID="txtLName" runat="server"></asp:TextBox> </td> </tr> <tr> <td align="right" width="50%"> Salary : </td> <td align="left"> <asp:TextBox ID="txtSal" runat="server"></asp:TextBox> </td> </tr> <tr> <td align="right" width="50%"> </td> <td align="left"> <asp:Button ID="btnSubmit" runat="server"
OnClick="btnSubmit_Click" Text="Submit" /> </td> </tr> <tr> <td align="right" width="50%"> </td> <td align="left"> </td> </tr> <tr> <td align="center" colspan="2"> <asp:GridView ID="GridView1" runat="server"> </asp:GridView> </td> </tr> <tr> <td colspan="2" align="center"> <asp:Button ID="btnInsert" runat="server"
OnClick="btnInsert_Click" Text="Insert InTo Table" /> </td> </tr> <tr> <td colspan="2" align="center"> <asp:Label ID="lblRes" runat="server" Text=" "></asp:Label> </td> </tr> </table> </center> </asp:Content>
Now The Code Behind looks as shown below....
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.Configuration; using System.Data.SqlClient; namespace TableValuedParms { public partial class _Default : System.Web.UI.Page { static DataTable dt; DataRow dr; DataRow dr1; private string Constring =
ConfigurationManager.ConnectionStrings["constring"].ConnectionString; protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { dt = new DataTable(); dt.Columns.Add("fname",typeof(string)); dt.Columns.Add("lname",typeof(string)); dt.Columns.Add("sal",typeof(Int32)); } } protected void btnSubmit_Click(object sender, EventArgs e) { dr1 = dt.NewRow(); dr1["fname"] = txtFName.Text; dr1["lname"] = txtLName.Text; dr1["sal"] = txtSal.Text; dt.Rows.Add(dr1); GridView1.DataSource = dt; GridView1.DataBind(); } protected void btnInsert_Click(object sender, EventArgs e) { using (SqlConnection con = new SqlConnection(Constring)) { con.Open(); using (SqlCommand cmd = new SqlCommand()) { cmd.Connection = con; cmd.CommandText = "EMP_Insert_TableValued"; cmd.CommandType = CommandType.StoredProcedure; SqlParameter Prm =
cmd.Parameters.AddWithValue("@MyTableParameter", dt); Prm.SqlDbType = SqlDbType.Structured; Prm.TypeName = "dbo.EMP_Insert"; int retVal = cmd.ExecuteNonQuery(); lblRes.Text = retVal.ToString() + " Rows Inserted."; } } } } }
Download the code from HERE
No comments:
Post a Comment