create table type in sql server and insert Update data using store procedure

Demo.aspx

<html>

<head>

<title>Pass Data Table</title>

$(document).ready(function () {

$('#<%=dropproduct.ClientID %>').change(function () {

var result = $('#<%=dropproduct.ClientID %>').val().split(',');

if ($('#<%=dropproduct.ClientID %>').val() == "---Select---") {

}

else {

$('#detail').html('');

var str = "";

str = str + "<div style=\"margin-top: -200px;border-radius: 0.5em;font-size: 14px;background-color: aliceblue;margin-bottom: 100px;width:400px \"><table style=\"width:70%\"><tr><td><span>Pcode:-</span></td><td><span>" + result[2] + "</span><td></tr><tr><td><span>Product Name:-</span></td><td><span>" + result[3] + "</span><td></tr><tr><td><span>Image:-</span></td><td><img src=\"" + result[1].replace("~/", "../") + "\" style=\"Width:100px;height:90px\" /><td></tr></table></div>"

$('#detail').append(str);

}

});

$('#<%=dropproduct.ClientID %>').change(function () {

var result = $('#<%=dropproduct.ClientID %>').val().split(',');

var id = 1;

$.ajax({

type: "POST",

url: "AddStock.aspx/Product",

contentType: "application/json; charset=utf-8",

data: "{pid:'" + result + "',mid:'" + id + "'}",

dataType: "json",

success: function (data) {

if (data.d == "No") {

$('#<%=lblstock.ClientID %>').text("0");

$('#<%=lblstock.ClientID %>').addClass("isa_success");

}

else {

$('#<%=lblstock.ClientID %>').addClass("isa_success");

$('#<%=lblstock.ClientID %>').html(data.d);

}

});

</script>

</head>

<body>

<table>

<tr>

<td>

Select Name

</td>

<td>

<asp:DropDownList ID="Dropname" runat="server">

</asp:DropDownList>

<asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ErrorMessage="Select Supplier"

ControlToValidate="Dropname" CssClass="isa_warning" Display="Dynamic" ValidationGroup="valid"

SetFocusOnError="True" InitialValue="---Select---"></asp:RequiredFieldValidator>

</td>

</tr>

<tr>

<td>

Bill No:

</td>

<td>

<asp:TextBox ID="txtbill" runat="server"></asp:TextBox>

<asp:RequiredFieldValidator ID="RequiredFieldValidator5" runat="server" ErrorMessage="Enter Bill No"

ControlToValidate="txtbill" CssClass="isa_warning" Display="Dynamic" ValidationGroup="valid"

SetFocusOnError="True"></asp:RequiredFieldValidator>

</td>

</tr>

<tr>

<td>

Select Item

</td>

<td>

<asp:DropDownList ID="dropcate" runat="server" AutoPostBack="True" OnSelectedIndexChanged="dropcate_SelectedIndexChanged">

</asp:DropDownList>

<asp:RequiredFieldValidator ID="RequiredFieldValidator8" runat="server" ErrorMessage="Select Product"

ControlToValidate="dropcate" CssClass="isa_warning" Display="Dynamic" ValidationGroup="valid"

SetFocusOnError="True" InitialValue="---Select---"></asp:RequiredFieldValidator>

</td>

</tr>

<tr>

<td>

Select Product

</td>

<td>

<asp:DropDownList ID="dropproduct" runat="server">

</asp:DropDownList>

<asp:Label ID="lblstock" runat="server"></asp:Label>

<asp:RequiredFieldValidator ID="RequiredFieldValidator4" runat="server" ErrorMessage="Select Product"

ControlToValidate="dropproduct" CssClass="isa_warning" Display="Dynamic" ValidationGroup="valid"

SetFocusOnError="True" InitialValue="---Select---"></asp:RequiredFieldValidator>

</td>

</tr>

<tr>

<td>

Qty

</td>

<td>

<asp:TextBox ID="txtqty" runat="server"></asp:TextBox>

<asp:FilteredTextBoxExtender ID="FilteredTextBoxExtender2" runat="server" TargetControlID="txtqty"

ValidChars="1234567890.">

</asp:FilteredTextBoxExtender>

<asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server" ErrorMessage="Insert Quntity"

ControlToValidate="txtqty" CssClass="isa_warning" Display="Dynamic" ValidationGroup="valid"

SetFocusOnError="True"></asp:RequiredFieldValidator>

</td>

</tr>

<tr>

<td>

Rate

</td>

<td>

<asp:TextBox ID="txtrate" runat="server"></asp:TextBox>

<asp:FilteredTextBoxExtender ID="FilteredTextBoxExtender1" runat="server" TargetControlID="txtrate"

ValidChars="1234567890.">

</asp:FilteredTextBoxExtender>

<asp:RequiredFieldValidator ID="RequiredFieldValidator3" runat="server" ErrorMessage="Insert Rate"

ControlToValidate="txtrate" CssClass="isa_warning" Display="Dynamic" ValidationGroup="valid"

SetFocusOnError="True"></asp:RequiredFieldValidator>

</td>

</tr>

<tr>

<td>

Tax1(%)

</td>

<td>

<asp:TextBox ID="txttax1" runat="server"></asp:TextBox>

<asp:FilteredTextBoxExtender ID="FilteredTextBoxExtender3" runat="server" TargetControlID="txttax1"

ValidChars="1234567890.">

</asp:FilteredTextBoxExtender>

<asp:RequiredFieldValidator ID="RequiredFieldValidator6" runat="server" ErrorMessage="Insert Tax"

ControlToValidate="txttax1" CssClass="isa_warning" Display="Dynamic" ValidationGroup="valid"

SetFocusOnError="True"></asp:RequiredFieldValidator>

</td>

</tr>

<tr>

<td>

Tax2(%)

</td>

<td>

<asp:TextBox ID="txttax2" runat="server"></asp:TextBox>

<asp:FilteredTextBoxExtender ID="FilteredTextBoxExtender4" runat="server" TargetControlID="txttax2"

ValidChars="1234567890.">

</asp:FilteredTextBoxExtender>

<asp:RequiredFieldValidator ID="RequiredFieldValidator7" runat="server" ErrorMessage="Insert Tax2"

ControlToValidate="txttax2" CssClass="isa_warning" Display="Dynamic" ValidationGroup="valid"

SetFocusOnError="True"></asp:RequiredFieldValidator>

</td>

</tr>

<tr>

<td>

<asp:Button ID="btnsub" runat="server" Text="Submit" OnClick="btnsub_Click" ValidationGroup="valid" />

</td>

<td>

<asp:Label ID="lblmsg" runat="server"></asp:Label>

</td>

</tr>

</table>

</div>

<br />

<asp:Panel ID="pnl" runat="server" Visible="false">

<asp:GridView ID="grd" runat="server" AutoGenerateColumns="false" DataKeyNames="id"

CssClass="mGrid" OnSelectedIndexChanging="grd_SelectedIndexChanging">

<asp:CommandField ShowSelectButton="true" SelectText="Delete" />

<asp:BoundField DataField="productname" HeaderText="Product name" />

<asp:BoundField DataField="pcode" HeaderText="Product Code" />

<asp:BoundField DataField="mrp" HeaderText="M.R.P" />

<asp:BoundField DataField="rate" HeaderText="Rate" />

<asp:BoundField DataField="qty" HeaderText="Qty." />

<asp:BoundField DataField="tax1" HeaderText="Tax1(%)" />

<asp:BoundField DataField="tax2" HeaderText="Tax2(%)" />

<asp:BoundField DataField="total" HeaderText="Total Amount" />

</Columns>

</asp:GridView>

<br />

Total:<asp:Label ID="lbltotal" runat="server" Text="0"></asp:Label>

<asp:Button runat="server" ID="btnfinal" Text="Generate Bill" Visible="false" OnClick="btnfinal_Click" /></center>

</asp:Panel>

</form>

<body>

</html>

Demo.aspx.cs

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 DAL;

using System.ComponentModel;

using System.Web.Services;

using BAL;

public partial class Admin_AddStock : System.Web.UI.Page

{

DataTable dt = new DataTable();

dalc odal = new dalc();

protected void Page_Load(object sender, EventArgs e)

{

if (!IsPostBack) // only initialize once when the page first loads

{

fill();

if (!IsPostBack)

{

DataTable ddt = new DataTable();

ddt.Columns.Add("id", typeof(int));

ddt.Columns.Add("pid", typeof(int));

ddt.Columns.Add("productname");

ddt.Columns.Add("pcode");

ddt.Columns.Add("mrp");

ddt.Columns.Add("qty", typeof(int));

ddt.Columns.Add("rate", typeof(decimal));

ddt.Columns.Add("total", typeof(decimal));

ddt.Columns.Add("tax1", typeof(float));

ddt.Columns.Add("tax2", typeof(float));

ddt.Columns.Add("bv", typeof(float));

ViewState["dt"] = ddt;

ViewState["ddt"] = ddt;

}

public void fill()

{

BAL_Product obal = new BAL_Product();

dt = obal.category("GetCategory");

dropcate.DataSource = dt;

dropcate.DataTextField = "name";

dropcate.DataValueField = "id";

dropcate.DataBind();

dropcate.Items.Insert(0, "---Select---");

dt = obal.category("GetSupplier");

Dropsupplier.DataSource = dt;

Dropsupplier.DataTextField = "firm";

Dropsupplier.DataValueField = "id";

Dropsupplier.DataBind();

Dropsupplier.Items.Insert(0, "---Select---");

dropproduct.Items.Insert(0, "---Select---");

}

[WebMethod]

public static string Product(string pid, string mid)

{

string[] aa = pid.ToString().Split(',');

dalc odal = new dalc();

DataTable dt = new DataTable();

dt = odal.selectbyquerydt("select isnull(sum(total-selling),0) from stockmaster where pid ='" + int.Parse(aa[0]) + "' and fid='" + int.Parse(mid) + "'");

if (dt.Rows.Count == 1)

{

return dt.Rows[0][0].ToString();

}

else

{

return "No";

}

protected void btnsub_Click(object sender, EventArgs e)

{

if (Convert.ToInt32(txtrate.Text) > 0 && Convert.ToInt32(txtqty.Text) > 0)

{

string[] aa = dropproduct.SelectedValue.ToString().Split(',');

BAL_Product obal = new BAL_Product();

dt = obal.product("productbyid", int.Parse(aa[0]));

if (dt.Rows.Count > 0)

{

int id = 0;

decimal qty = Convert.ToDecimal(txtqty.Text);

int pid = Convert.ToInt32(dt.Rows[0][0].ToString());

string mrp = dt.Rows[0]["mrp"].ToString();

string productname = dt.Rows[0]["pname"].ToString();

string pcode = dt.Rows[0]["pcode"].ToString();

decimal rate = Convert.ToDecimal(txtrate.Text);

decimal tax1 = Convert.ToDecimal(txttax1.Text);

decimal tax2 = Convert.ToDecimal(txttax2.Text);

decimal total = qty * (rate + ((rate * tax1) / 100) + ((rate * tax2) / 100));

dt = (DataTable)ViewState["dt"];

if (dt.Rows.Count > 0)

{ id = dt.Rows.Count; }

DataRow[] dr;

dr = dt.Select("pid=" + pid);

if (dr.Length == 0)

{

dt.Rows.Add(id + 1, pid, productname, pcode, mrp, qty, rate, total, tax1, tax2, 0.0);

decimal amount = 0;

amount = Convert.ToDecimal(lbltotal.Text) + total;

lbltotal.Text = Convert.ToString(amount);

lblmsg.Text = "";

lblmsg.CssClass = "";

}

else

{

lblmsg.Text = "Already Added";

lblmsg.CssClass = "isa_error";

}

grd.DataSource = dt;

grd.DataBind();

dropproduct.SelectedIndex = 0;

Dropsupplier.Enabled = false;

txtbill.Enabled = false;

txtqty.Text = "";

txtrate.Text = "";

txttax1.Text = "";

txttax2.Text = "";

ViewState["dt"] = dt;

ViewState["ddt"] = dt;

btnfinal.Visible = true;

pnl.Visible = true;

}

else

{

lblmsg.Text = "Rate Or Quntity Can't Be Zero";

lblmsg.CssClass = "isa_error";

}

protected void grd_SelectedIndexChanging(object sender, GridViewSelectEventArgs e)

{

grd.SelectedIndex = e.NewSelectedIndex;

int id = int.Parse(grd.DataKeys[grd.SelectedIndex].Value.ToString());

DataTable dt = new DataTable();

dt = (DataTable)ViewState["ddt"];

for (int i = 0; i < dt.Rows.Count; i++)

{

if (int.Parse(dt.Rows[i]["id"].ToString()) == id)

{

decimal total = Convert.ToDecimal(dt.Rows[i]["total"].ToString());

decimal amount = 0;

amount = Convert.ToDecimal(lbltotal.Text) - total;

lbltotal.Text = Convert.ToString(amount);

dt.Rows[i].Delete();

break;

}

if (dt.Rows.Count == 0)

{

pnl.Visible = false;

}

else

{

pnl.Visible = true;

}

grd.DataSource = dt;

grd.DataBind();

ViewState["dt"] = dt;

ViewState["ddt"] = dt;

}

protected void btnfinal_Click(object sender, EventArgs e)

{

BAL.Bal_Generatebill obal = new BAL.Bal_Generatebill();

dt = (DataTable)ViewState["dt"];

if (dt.Rows.Count > 0)

{

dt.Columns.Remove("id");

dt.Columns.Remove("pcode");

dt.Columns.Remove("productname");

dt.Columns.Remove("total");

dt.Columns.Remove("mrp");

DataTable dd = new DataTable();

dd = obal.Generatebill("SupplierInvoice", int.Parse(Dropsupplier.SelectedValue), txtbill.Text, dt);

string msg = dd.Rows[0]["msg"].ToString();

if (msg == "OK")

{

ViewState["dt"] = null;

int id = int.Parse(dd.Rows[0]["id"].ToString());

Response.Redirect("success.aspx?msg=" + id);

}

lblmsg.Text = msg;

}

else

{

lblmsg.Text = "Cart Is Empty";

}

protected void dropcate_SelectedIndexChanged(object sender, EventArgs e)

{

if (dropcate.SelectedIndex != 0)

{

BAL_Product obal = new BAL_Product();

dt = obal.product("Getproductbyid", int.Parse(dropcate.SelectedValue));

dropproduct.DataSource = dt;

dropproduct.DataTextField = "pname";

dropproduct.DataValueField = "id";

dropproduct.DataBind();

dropproduct.Items.Insert(0, "---Select---");

}

BAL_Product .cs

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Data;

using System.Data.SqlClient;

using DAL;

using BO;

namespace BAL

{

public class BAL_Product

{

dalc odal = new dalc();

DataTable dt = new DataTable();

BALGetpara oget = new BALGetpara();

public string IUD_categery(string mode, BO_Product obo)

{

SqlParameter[] para = new SqlParameter[5];

para[0] = oget.Getpara("@mode", mode);

para[1] = oget.Getpara("@categoryname", obo.Categoryname);

para[2] = oget.Getpara("@photo", obo.Photo);

para[3] = oget.Getpara("@isactive", obo.Isactive);

para[4] = oget.Getpara("@id", obo.Id);

dt = odal.selectadminsp("product", para);

return dt.Rows[0][0].ToString();

}

public string IUD_Product(string mode, BO_Product obo)

{

SqlParameter[] para = new SqlParameter[13];

para[0] = oget.Getpara("@mode", mode);

para[1] = oget.Getpara("@pname", obo.Productname);

para[2] = oget.Getpara("@pcode", obo.Pcode);

para[3] = oget.Getpara("@mrp", obo.Mrp);

para[4] = oget.Getpara("@dp", obo.Dp);

para[5] = oget.Getpara("@bv", obo.Bv);

para[6] = oget.Getpara("@tax1", obo.Tax1);

para[7] = oget.Getpara("@tax2", obo.Tax2);

para[8] = oget.Getpara("@isactive", obo.Isactive);

para[9] = oget.Getpara("@photo", obo.Photo);

para[10] = oget.Getpara("@cid", obo.Cid);

para[11] = oget.Getpara("@id", obo.Id);

para[12] = oget.Getpara("@desc", obo.Discription);

dt = odal.selectadminsp("product", para);

return dt.Rows[0][0].ToString();

}

public string supplier(BO_Product obo)

{

SqlParameter[] para = new SqlParameter[11];

para[0] = oget.Getpara("@mode", obo.Mode);

para[1] = oget.Getpara("@contact", obo.Contactperson);

para[2] = oget.Getpara("@firm", obo.Firm);

para[3] = oget.Getpara("@address", obo.Address);

para[4] = oget.Getpara("@state", obo.State);

para[5] = oget.Getpara("@city", obo.City);

para[6] = oget.Getpara("@mobile", obo.Mobileno);

para[7] = oget.Getpara("@email", obo.Email);

para[8] = oget.Getpara("@isactive", obo.Isactive);

para[9] = oget.Getpara("@pincode", obo.Pincode);

para[10] = oget.Getpara("@id", obo.Id);

dt = odal.selectadminsp("product", para);

return dt.Rows[0][0].ToString();

}

public DataTable category(string mode)

{

SqlParameter[] para = new SqlParameter[1];

para[0] = oget.Getpara("@mode", mode);

dt = odal.selectadminsp("product", para);

return dt;

}

public DataTable product(string mode, int cid)

{

SqlParameter[] para = new SqlParameter[2];

para[0] = oget.Getpara("@mode", mode);

para[1] = oget.Getpara("@cid", cid);

dt = odal.selectadminsp("product", para);

return dt;

}

Bal_Generatebill.cs

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Data;

using System.Data.SqlClient;

using BAL;

using DAL;

namespace BAL

{

public class Bal_Generatebill

{

BALGetpara obget = new BALGetpara();

dalc odal = new dalc();

DataTable dt = new DataTable();

public DataTable Generatebill(string mode, int gid, string billno, DataTable dt)

{

SqlParameter[] para = new SqlParameter[4];

para[0] = obget.Getpara("@mode", mode);

para[1] = obget.Getpara("@sid", gid);

para[2] = obget.Getpara("@dt", dt);

para[3] = obget.Getpara("@billno", billno);

dt = odal.selectadminsp("kinduser.generatebill", para);

return dt;

}

public DataTable memberGeneratebill(string mode, int gid, int mid, DataTable dt)

{

SqlParameter[] para = new SqlParameter[4];

para[0] = obget.Getpara("@mode", mode);

para[1] = obget.Getpara("@gid", gid);

para[2] = obget.Getpara("@dt", dt);

para[3] = obget.Getpara("@sid", mid);

dt = odal.selectadminsp("kinduser.generatebill", para);

return dt;

}

BALGetpara.cs

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Data;

using System.Data.SqlClient;

namespace BAL

{

public class BALGetpara

{

public SqlParameter Getpara(string ParaName, string ParaValue)

{

SqlParameter para = new SqlParameter();

para.SqlDbType = SqlDbType.VarChar;

para.SqlValue = ParaValue;

para.ParameterName = ParaName;

return para;

}

public SqlParameter Getpara(string paraname, DataTable paravalue)

{

SqlParameter para = new SqlParameter();

para.ParameterName = paraname.ToString();

para.Value = paravalue;

para.SqlDbType = SqlDbType.Structured;

return para;

}

public SqlParameter Getpara(string paraname, decimal paravalue)

{

SqlParameter para = new SqlParameter();

para.ParameterName = paraname.ToString();

para.Value = paravalue.ToString();

para.SqlDbType = SqlDbType.Decimal;

return para;

}

public SqlParameter Getpara(string ParaName, int Paravalue)

{

SqlParameter para = new SqlParameter();

para.SqlDbType = SqlDbType.Int;

para.SqlValue = Paravalue;

para.ParameterName = ParaName;

return para;

}

public SqlParameter Getpara(string ParaName, bool Paravalue)

{

SqlParameter para = new SqlParameter();

para.SqlDbType = SqlDbType.Bit;

para.SqlValue = Paravalue;

para.ParameterName = ParaName;

return para;

}

//public SqlParameter Getpara(string paraname, bool paravalue)

//{

// SqlParameter para = new SqlParameter();

// para.ParameterName = paraname.ToString();

// para.Value = paravalue.ToString();

// para.SqlDbType = SqlDbType.VarChar;

// return para;

//}

public SqlParameter Getpara(string paraname, DateTime paravalue)

{

SqlParameter para = new SqlParameter();

para.ParameterName = paraname.ToString();

para.Value = paravalue.ToString();

para.SqlDbType = SqlDbType.DateTime;

return para;

}

public SqlParameter Getpara(string paraname, float paravalue)

{

SqlParameter para = new SqlParameter();

para.ParameterName = paraname.ToString();

para.Value = paravalue.ToString();

para.SqlDbType = SqlDbType.Float;

return para;

}

public SqlParameter Getpara(string paraname, double paravalue)

{

SqlParameter para = new SqlParameter();

para.ParameterName = paraname.ToString();

para.Value = paravalue.ToString();

para.SqlDbType = SqlDbType.Float;

return para;

}

Now Write Sql Server Code

Here is create SQL Table Type

CREATE TYPE cartproduct AS TABLE(

[pid] [int] NOT NULL,

[rate] [decimal](18, 2) NOT NULL,

[qty] [int] NOT NULL

)

CREATE TYPE cart1 AS TABLE(

[pid] [int] NOT NULL,

[qty] [int] NOT NULL,

[rate] [decimal](18, 2) NOT NULL,

[tax1] [float] NULL,

[tax2] [float] NULL,

[bv] [float] NULL

)

and now write store procedure

dbo.Managestock

create PROCEDURE Managestock

@mode varchar(50)='',

@dt cartproduct readonly,

@mid int=0,

@msg VARCHAR(500) output

BEGIN

SET NOCOUNT ON;

if @mode='ADD'

begin

update stockmaster set total=total+T.qty from stockmaster as S inner join @dt as T on T.pid=S.pid and S.fid=@mid

insert into stockmaster(fid,pid,total,selling) select @mid,T.pid,T.qty,0 from @dt as T left join stockmaster as S on S.pid=T.pid and S.fid=@mid where S.fid is null

set @msg= 'OK'

end

else if @mode='SUB'

begin

if(select COUNT(S.fid) from @dt as T left join stockmaster as S on S.fid=@mid and S.pid=T.pid group by S.total,S.selling,T.qty having((ISNULL(S.total,0))-(ISNULL(S.selling,0)+T.qty))<0) is null

begin

update stockmaster set selling=selling+T.qty from stockmaster as S inner join @dt as T on S.fid=@mid and T.pid=S.pid

set @msg= 'OK'

end

else

begin

set @msg= 'You Have Not Enough Stock'

end

END

dbo.generatebill

Create PROCEDURE generatebill

@mode varchar(50)='',

@sid int=0,

@id int=0,

@dt as cart1 readonly,

@billno varchar(50)='',

@gid int=0

BEGIN

SET NOCOUNT ON;

declare @total decimal(18,2)=(select isnull(sum((T.qty*T.rate)+(T.qty*T.rate*T.tax1/100)+(T.qty*T.rate*T.tax2/100)),0) from @dt as T)

declare @msg varchar(max)=''

declare @temp as cartproduct

if @mode='SupplierInvoice'

begin

insert into supplier_generateinvoice (sid,totalamount,billno,ondate) values (@sid,@total,@billno,GETDATE())

set @id =(select SCOPE_IDENTITY())

insert into supplier_invoice(iid,pid,rate,qty,tax1,tax2)select @id,pid,Rate,qty,tax1,tax2 from @dt

insert into @temp(pid,qty,rate) select pid,qty,rate from @dt

exec kinduser.Managestock 'ADD',@temp,1,@msg output

select @msg as msg ,@id as id

end

else if @mode ='FranchiseBill'

begin

insert into @temp(pid,qty,rate) select T.pid,T.qty,T.rate from @dt as T

exec kinduser.Managestock 'SUB',@temp,1,@msg output

if @msg='OK'

begin

insert into franchise_generateinvoice (fid,totalamount,ondate)values(@sid,@total,GETDATE ())

set @id =(select SCOPE_IDENTITY())

insert into franchise_invoice (iid,pid,rate,qty) select @id,T.pid,T.rate,T.qty from @dt as T

exec kinduser.Managestock 'ADD',@temp,@sid,@msg output

end

select @msg as msg,@id as id

end

Search This Blog

Programming With Sangani