Step 1:- Create table
CREATE TABLE DATA
(
COLUMN1 VARCHAR(50),
COLUMN2 VARCHAR(50),
COLUMN3 VARCHAR(50),
COLUMN4 VARCHAR(50)
)
Step2:- Create user define table Type
CREATE TYPE TABLE_DATA
AS TABLE
(
COLUMN1 VARCHAR(50),
COLUMN2 VARCHAR(50),
COLUMN3 VARCHAR(50),
COLUMN4 VARCHAR(50)
)
Step3:- Create Insert Store Procedure
CREATE PROC SP_INSERT
@TBLDATA TABLE_DATA READONLY
AS
BEGIN
INSERT INTO DATA
SELECT * FROM @TBLDATA
END
Step4:- Default.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="Demo.Default" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Pass table-valued
parameter data to a stored procedure </title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Button ID="Button1" runat="server" Text="Insert" OnClick="Button1_Click" />
<asp:Label ID="Label1" runat="server" Text="Message" Visible="False"></asp:Label>
</div>
</form>
</body>
</html>
Step5:- Default.aspx.cs
using System;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Web.UI;
namespace Demo
{
public partial class Default : Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
}
}
protected DataTable GeTable()
{
DataTable dt
= new DataTable();
DataRow dr;
dt.Columns.Add(new DataColumn("Column1", typeof(string)));
dt.Columns.Add(new DataColumn("Column2", typeof(string)));
dt.Columns.Add(new DataColumn("Column3", typeof(string)));
dt.Columns.Add(new DataColumn("Column4", typeof(string)));
int totalRow
= 10;
for (int i
= 0; i < totalRow; i++)
{
dr
= dt.NewRow();
dr["Column1"] = "Row" +
i;
dr["Column2"] = "Row" +
i;
dr["Column3"] = "Row" +
i;
dr["Column4"] =
1000 / (i + 1);
dt.Rows.Add(dr);
}
return dt;
}
protected void Button1_Click(object sender, EventArgs e)
{
string strConnection
= "server=192.168.1.1; database=Test; uid=sa; pwd=admin";
SqlConnection connection
= new SqlConnection(strConnection);
SqlCommand sqlCommand
= new SqlCommand();
sqlCommand.CommandType
= CommandType.StoredProcedure;
sqlCommand.CommandText
= "SP_INSERT";
sqlCommand.Parameters.Add("@TBLDATA", SqlDbType.Structured).Value
= GeTable();
sqlCommand.Connection
= connection;
connection.Open();
int a
= sqlCommand.ExecuteNonQuery();
connection.Close();
if (a
> 0)
{
Label1.Text
= "Record Inserted Successfully";
Label1.ForeColor
= Color.Green;
Label1.Visible
= true;
}
else
{
Label1.Text
= "Record Not Inserted";
Label1.ForeColor
= Color.Red;
Label1.Visible
= true;
}
}
}
}
Step6:- Run & Click Insert Button
Step7:- Run the SQL Query
select * from DATA
Note:-
1. Table-valued parameters are read-only in Transact-SQL code.
2. You
cannot update the column values in the rows of a table-valued parameter and you
cannot insert or delete rows.
3. To modify
the data that is passed to a stored procedure or parameterized statement in
table-valued parameter, you must insert the data into a temporary table or into
a table variable.
4. You
cannot use ALTER TABLE statements to modify the design of table-valued
parameters.
No comments:
Post a Comment