Pass Table as a Parameter to Sql Procedure.
Pass Table as a Parameter to Sql Procedure.
In this article I am Using 3 tier architecture for passing Table as parameter from 1 layer to
other ,and finally to Stored procedure.
Step 1 : Create Database :
(A)Table for data insertion.
CREATE TABLE [dbo].[SETTINGS](
[Id] [int] IDENTITY(1,1) NOT NULL,
[BusinessId] [int] NULL,
[Year] [int] NULL,
[Month] [int] NULL,
[BudgetAmt] [decimal](6, 2) NULL,
[FiscalForm] [datetime] NULL,
[FiscalTo] [datetime] NULL,
[AustFinYear]
[bit] NULL,
)
(B) User defined table type which will be used as a table valued parameter in the stored procedure.
CREATE TYPE dbo.SETTINGS_type
AS TABLE(
BusinessId int,
Year int,
Month int,
BudgetAmt decimal(6, 2),
FiscalForm
datetime,
FiscalTo datetime,
AustFinYear bit
)
(C) And Finally a Stored procedure for
insertion.
CREATE PROCEDURE table_insert
(
@TVP dbo.SETTINGS_type READONLY
)
As
BEGIN
SET NOCOUNT ON
INSERT INTO SETTINGS
(BusinessId,Year,Month,BudgetAmt,FiscalForm,FiscalTo,AustFinYear)
select * from @tvp
end
Designing Part on .aspx
<table width="100%" cellpadding="2" border="0"
cellspacing="0">
<tr>
<td style="border:1px solid gray;border-right:0;">
<asp:TextBox ID="txt_jan"
runat="server" placeholder="January" onkeypress="return only_num(event)"></asp:TextBox>
</td>
<td style="border:1px solid gray;border-right:0;">
<asp:TextBox ID="txt_fab"
runat="server" placeholder="February" onkeypress="return only_num(event)"></asp:TextBox>
</td>
<td style="border:1px solid
gray;">
<asp:TextBox ID="txt_march"
runat="server" placeholder="March" onkeypress="return only_num(event)"></asp:TextBox>
</td>
</tr>
<tr>
<td style="border:1px solid gray;border-right:0;">
<asp:TextBox ID="txt_april"
runat="server" placeholder="April" onkeypress="return only_num(event)"></asp:TextBox>
</td>
<td style="border:1px solid gray;border-right:0;">
<asp:TextBox ID="txt_may"
runat="server"
placeholder="May"
onkeypress="return
only_num(event)"> </asp:TextBox>
</td>
<td style="border:1px solid
gray;">
<asp:TextBox ID="txt_june"
runat="server" placeholder="June" onkeypress="return only_num(event)"></asp:TextBox>
</td>
</tr>
<tr>
<td style="border:1px solid gray;border-right:0;">
<asp:TextBox ID="txt_july"
runat="server" placeholder="July" onkeypress="return only_num(event)"></asp:TextBox>
</td>
<td style="border:1px solid gray;border-right:0;">
<asp:TextBox ID="txt_aug"
runat="server" placeholder="August" onkeypress="return only_num(event)"></asp:TextBox>
</td>
<td style="border:1px solid
gray;">
<asp:TextBox ID="txt_sep"
runat="server"
placeholder="September"
onkeypress="return
only_num(event)"></asp:TextBox>
</td>
</tr>
<tr>
<td style="border:1px solid gray;border-right:0;">
<asp:TextBox ID="txt_oct"
runat="server" placeholder="October" onkeypress="return only_num(event)"></asp:TextBox>
</td>
<td style="border:1px solid gray;border-right:0;">
<asp:TextBox ID="txt_nov"
runat="server" placeholder="November" onkeypress="return only_num(event)"></asp:TextBox>
</td>
<td style="border:1px solid
gray;">
<asp:TextBox ID="txt_dec"
runat="server" placeholder="December" onkeypress="return only_num(event)"></asp:TextBox>
</td>
</tr>
</table>
SUBMIT
onclick="btn_submit_Click"
/>
It will Look like :
Step 2 : Data Access
Layer.
Its class name DACreateUser
Function in DA.
public int
table_isnert(DataTable dt)
{
int i;
SqlParameter param = new
SqlParameter();
param.ParameterName = "TVP";
param.SqlDbType = SqlDbType.Structured;
param.Value = dt;
param.Direction = ParameterDirection.Input;
SqlCommand sqlCmd = new
SqlCommand("dbo.table_insert");
con.Open();
sqlCmd.Connection = con;
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.Parameters.Add(param);
i= sqlCmd.ExecuteNonQuery();
return i;
}
Step 3 : Business Access Layer.
Its class name BOCreateUser
Function in BA.
public int
table_isnert(DataTable dt)
{
DACreateUser objDACreateUser = new
DACreateUser();
int i;
i = objDACreateUser.table_isnert(dt);
return i;
}
Step 4 : Prsentation Layer.
Its class name same as .aspx
On Submit Click :
//Table Creation
DataTable dt_budget = new DataTable();
dt_budget.Columns.Add("business_id");
dt_budget.Columns.Add("year");
dt_budget.Columns.Add("month");
dt_budget.Columns.Add("budgwt_amount");
dt_budget.Columns.Add("fiscal_from");
dt_budget.Columns.Add("fiscal_to");
dt_budget.Columns.Add("aust_year");
///END
///Value
Assignment to Table
if (txt_jan.Text != null
|| txt_jan.Text == "")
{
}
else
{
dt_budget.Rows.Add(bussiness_id, year, 1, txt_jan.Text, txt_b_from.Text,
txt_b_to.Text, chk_aut_f_year.Checked);
}
if (txt_fab.Text == null
|| txt_fab.Text == "")
{
}
else
{
dt_budget.Rows.Add(bussiness_id, year,2, txt_fab.Text, txt_b_from.Text,
txt_b_to.Text, chk_aut_f_year.Checked);
}
if (txt_march.Text == null
|| txt_march.Text == "")
{
}
else
{
dt_budget.Rows.Add(bussiness_id, year, 3, txt_march.Text,
txt_b_from.Text, txt_b_to.Text, chk_aut_f_year.Checked);
}
if (txt_april.Text == null
|| txt_april.Text == "")
{
}
else
{
dt_budget.Rows.Add(bussiness_id, year, 4, txt_april.Text,
txt_b_from.Text, txt_b_to.Text, chk_aut_f_year.Checked);
}
if (txt_may.Text == null
|| txt_may.Text == "")
{
}
else
{
dt_budget.Rows.Add(bussiness_id, year, 5, txt_may.Text, txt_b_from.Text,
txt_b_to.Text, chk_aut_f_year.Checked);
}
if (txt_june.Text == null
|| txt_june.Text == "")
{
}
else
{
dt_budget.Rows.Add(bussiness_id, year, 6, txt_june.Text,
txt_b_from.Text, txt_b_to.Text, chk_aut_f_year.Checked);
}
if (txt_july.Text == null
|| txt_july.Text == "")
{
}
else
{
dt_budget.Rows.Add(bussiness_id, year,7, txt_july.Text, txt_b_from.Text,
txt_b_to.Text, chk_aut_f_year.Checked);
}
if (txt_aug.Text == null
|| txt_aug.Text == "")
{
}
else
{
dt_budget.Rows.Add(bussiness_id, year,8, txt_aug.Text, txt_b_from.Text,
txt_b_to.Text, chk_aut_f_year.Checked);
}
if (txt_sep.Text == null
|| txt_sep.Text == "")
{
}
else
{
dt_budget.Rows.Add(bussiness_id, year,9, txt_sep.Text, txt_b_from.Text,
txt_b_to.Text, chk_aut_f_year.Checked);
}
if (txt_oct.Text == null
|| txt_oct.Text == "")
{
}
else
{
dt_budget.Rows.Add(bussiness_id, year,10, txt_oct.Text, txt_b_from.Text,
txt_b_to.Text, chk_aut_f_year.Checked);
}
if (txt_nov.Text == null
|| txt_nov.Text == "")
{
}
else
{
dt_budget.Rows.Add(bussiness_id, year,11, txt_nov.Text, txt_b_from.Text,
txt_b_to.Text, chk_aut_f_year.Checked);
}
if (txt_dec.Text == null
|| txt_dec.Text == "")
{
}
else
{
dt_budget.Rows.Add(bussiness_id, year, 12, txt_dec.Text,
txt_b_from.Text, txt_b_to.Text, chk_aut_f_year.Checked);
}
///END
/////Calling
BA class object
BOCreateUser obj_bo = new BOCreateUser();
int kl;
kl = obj_bo.table_isnert(dt_budget);
if (kl == 0)
{
Response.Write("failed");
}
else
{
Response.Write("done");
}
///END
If everything go
correct than it display “DONe” Else “Failed”.
Comments
Post a Comment