Hitesh Patel: DATA TABLE STORE DATA EXAMPLE DESIGN FILE & CODING / CS FILE

===========
Design page - DTExm.aspx
===========
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="DTExm.aspx.cs" Inherits="DTExm" %>

<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="asp" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<style type="text/css">
.auto-style1 {
width: 107px;
}
.auto-style2 {
width: 143px;
}
.auto-style3 {
width: 154px;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<asp:ToolkitScriptManager ID="ToolkitScriptManager1" runat="server"></asp:ToolkitScriptManager>
<div>
<table style="width: 100%;">
<tr>
<td class="auto-style1"> </td>
<td class="auto-style2">SALES BILL</td>
<td class="auto-style3"> </td>
<td> </td>
</tr>
<tr>
<td class="auto-style1">BILL NO. :</td>
<td class="auto-style2">
<asp:TextBox ID="txtBillNo" runat="server" AutoPostBack="True"
ontextchanged="txtBillNo_TextChanged"></asp:TextBox>
</td>
<td class="auto-style3">CUSTOMER NAME :</td>
<td>
<asp:TextBox ID="txtName" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td class="auto-style1">DATE :</td>
<td class="auto-style2">
<asp:TextBox ID="txtDate" runat="server"></asp:TextBox>
<asp:CalendarExtender ID="CalendarExtender1" runat="server" TargetControlID="txtDate">
</asp:CalendarExtender>
</td>
<td class="auto-style3"> </td>
<td> </td>
</tr>
<tr>
<td colspan="4">
<table>
<tr>
<td>Product</td>
<td>QTY</td>
<td>Amount</td>
<td rowspan="2">
<asp:Button ID="btnADD" runat="server" Text="ADD" OnClick="btnADD_Click" />
</td>
</tr>
<tr>
<td>
<asp:DropDownList ID="ddlfrmPrd" runat="server" Width="80px">
</asp:DropDownList>
</td>
<td>
<asp:TextBox ID="txtfrmQty" runat="server" Width="50px"></asp:TextBox>
</td>
<td>
<asp:TextBox ID="txtfrmAmt" runat="server" Width="80px"></asp:TextBox>
</td>
</tr>
</table>
</td>
</tr>
<tr>
<td colspan="4">
<asp:GridView ID="GrvSales" runat="server" AutoGenerateColumns="False" CellPadding="4" ForeColor="#333333" GridLines="None" OnPageIndexChanging="GrvSales_PageIndexChanging" OnRowCancelingEdit="GrvSales_RowCancelingEdit" OnRowDeleting="GrvSales_RowDeleting" OnRowEditing="GrvSales_RowEditing" OnRowUpdating="GrvSales_RowUpdating">
<AlternatingRowStyle BackColor="White" />
<Columns>
<asp:TemplateField HeaderText="Bill NO" Visible="false">
<ItemTemplate>
<asp:Label ID="lblBillno" runat="server" Text='<%# Eval("BILLNO") %>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:Label ID="lblEBillno" runat="server" Text='<%# Eval("BILLNO") %>'></asp:Label>
</EditItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Product">
<ItemTemplate>
<asp:Label ID="lblPRD" runat="server" Text='<%# Eval("PRD_CODE") %>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:Label ID="lblEPRD" runat="server" Text='<%# Eval("PRD_CODE") %>' Visible="false"></asp:Label>
<asp:DropDownList ID="ddlEPRD" runat="server"></asp:DropDownList>
</EditItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="QTY">
<ItemTemplate>
<asp:Label ID="lblQty" runat="server" Text='<%# Eval("QTY") %>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtEQty" runat="server" Text='<%# Eval("QTy") %>'></asp:TextBox>
</EditItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="AMOUNT">
<ItemTemplate>
<asp:Label ID="lblAmt" runat="server" Text='<%# Eval("AMT") %>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtEAmt" runat="server" Text='<%# Eval("AMT") %>'></asp:TextBox>
</EditItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="TOTAL">
<ItemTemplate>
<asp:Label ID="lbltot" runat="server" Text='<%# Eval("TOTAMT") %>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:Label ID="lblEtot" runat="server" Text='<%# Eval("TOTAMT") %>'></asp:Label>
</EditItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="COMMAND">
<ItemTemplate>
<asp:Button ID="btnEdit" runat="server" Text="EDIT" CommandName="Edit"></asp:Button>
<asp:Button ID="btnDelete" runat="server" Text="DELETE" CommandName="Delete"></asp:Button>
</ItemTemplate>
<EditItemTemplate>
<asp:Button ID="btnUpdate" runat="server" Text="UPDATE" CommandName="Update"></asp:Button>
<asp:Button ID="btnCancel" runat="server" Text="CANCEL" CommandName="Cancel"></asp:Button>
</EditItemTemplate>
</asp:TemplateField>
</Columns>
<EditRowStyle BackColor="#2461BF" />
<FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
<RowStyle BackColor="#EFF3FB" />
<SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
<SortedAscendingCellStyle BackColor="#F5F7FB" />
<SortedAscendingHeaderStyle BackColor="#6D95E1" />
<SortedDescendingCellStyle BackColor="#E9EBEF" />
<SortedDescendingHeaderStyle BackColor="#4870BE" />
</asp:GridView>
</td>
</tr>
<tr>
<td colspan="2">
<asp:Label ID="lblMsg" runat="server" ForeColor="Green"></asp:Label>
</td>
<td>
<asp:Button ID="btnSAVE" runat="server" Text="SAVE" OnClick="btnSAVE_Click"></asp:Button>
</td>
<td></td>
</tr>
</table>
</div>
</form>
</body>
</html>

=============
CODING FILE - DTExm.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;
public partial class DTExm : System.Web.UI.Page
{
DAL d = new DAL();
DataSet Ds = new DataSet();
DataTable tb = new DataTable();
string query;
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindTable();
BindDDL();
Bind();
}
}
private void BindTable()
{
DataTable ord = new DataTable("Table"); //the “Table” value must be same for
DataColumn c = new DataColumn(); // always
//BILLNO, PRD_CODE, QTY, AMT, TOTAMT
ord.Columns.Add(new DataColumn("BILLNO", Type.GetType("System.String")));
ord.Columns.Add(new DataColumn("PRD_CODE", Type.GetType("System.String")));
ord.Columns.Add(new DataColumn("QTY", Type.GetType("System.String")));
ord.Columns.Add(new DataColumn("AMT", Type.GetType("System.String")));
ord.Columns.Add(new DataColumn("TOTAMT", Type.GetType("System.String")));

Session["ss"] = ord;
}
protected void Bind()
{
try
{
lblMsg.Text = "";
tb = (DataTable)(Session["ss"]);
GrvSales.DataSource = (DataTable)(Session["ss"]);// storing in “tb” datatable variable and bind with
GrvSales.DataBind();
}
catch (Exception ex)
{
Page.ClientScript.RegisterStartupScript(GetType(), "", "<script language='javascript'> alert ('" + ex.ToString() + "'); </script>");
}

}
protected void BindDDL()
{
query = "Select * from PRODUCTMAST";
Ds = d.FETCH(query);
if (Ds.Tables[0].Rows.Count > 0)
{
ddlfrmPrd.DataSource = Ds;
ddlfrmPrd.DataTextField = "PRD_NAME";
ddlfrmPrd.DataValueField = "PRD_CODE";
ddlfrmPrd.DataBind();
ddlfrmPrd.Items.Insert(0, new ListItem("--Select--", "0"));
}
}
protected void cls()
{
txtBillNo.Text = "";
txtDate.Text = "";
txtName.Text = "";
txtfrmAmt.Text = "";
txtfrmQty.Text = "";
ddlfrmPrd.SelectedIndex = 0;
}

protected void clsADD()
{
txtfrmAmt.Text = "";
txtfrmQty.Text = "";
ddlfrmPrd.SelectedIndex = 0;
}
protected void btnADD_Click(object sender, EventArgs e)
{
tb = (DataTable)(Session["ss"]);
//BILLNO, PRD_CODE, QTY, AMT, TOTAMT
DataRow r1 = tb.NewRow();
r1["BILLNO"] = txtBillNo.Text;
r1["PRD_CODE"] = ddlfrmPrd.SelectedValue;
r1["QTY"] = Convert.ToDouble(txtfrmQty.Text);
r1["AMT"] = Convert.ToDouble(txtfrmAmt.Text);
r1["TOTAMT"] = Convert.ToDouble(txtfrmQty.Text) * Convert.ToDouble(txtfrmAmt.Text);
tb.Rows.Add(r1);
tb.AcceptChanges();
Session["ss"] = tb;
Bind();
clsADD();
}
protected void btnSAVE_Click(object sender, EventArgs e)
{
//d.TbSAVE = (DataTable)(Session["ss"]);
query = "Insert into BILLDETAIL values (" + txtBillNo.Text + ",'" + txtName.Text + "','" + txtDate.Text + "') ";
d.IUD(query);

tb = (DataTable)(Session["ss"]);
for (int i = 0; i < tb.Rows.Count; i++)
{
query = "Insert into BILLQTYDETAIL values (" + tb.Rows[i]["BILLNO"] + ",'" + tb.Rows[i]["PRD_CODE"].ToString() + "'," + tb.Rows[i]["QTY"].ToString() + "," + tb.Rows[i]["AMT"].ToString() + "," + tb.Rows[i]["TOTAMT"].ToString() + ") ";
d.IUD(query);
}
cls();
tb = null;
Session["ss"] = null;
Bind();
lblMsg.Text = "Entry Done Successfully !";
txtBillNo.Focus();
}
protected void GrvSales_RowEditing(object sender, GridViewEditEventArgs e)
{
GrvSales.EditIndex = e.NewEditIndex;
Bind();

DropDownList ddlEPRD = GrvSales.Rows[e.NewEditIndex].FindControl("ddlEPRD") as DropDownList;
Label lblEPRD = GrvSales.Rows[e.NewEditIndex].FindControl("lblEPRD") as Label;

query = "Select * from PRODUCTMAST";
Ds = d.FETCH(query);
if (Ds.Tables[0].Rows.Count > 0)
{
ddlEPRD.DataSource = Ds;
ddlEPRD.DataTextField = "PRD_NAME";
ddlEPRD.DataValueField = "PRD_CODE";
ddlEPRD.DataBind();
ddlEPRD.Items.Insert(0, new ListItem("--Select--", "0"));
ddlEPRD.SelectedValue = lblEPRD.Text;
}
}
protected void GrvSales_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
tb = (DataTable)(Session["ss"]);
GridViewRow row1 = GrvSales.Rows[e.RowIndex];

Label lblEBillno = row1.FindControl("lblEBillno") as Label;
DropDownList ddlEPRD = row1.FindControl("ddlEPRD") as DropDownList;
TextBox txtEQty = row1.FindControl("txtEQty") as TextBox;
TextBox txtEAmt = row1.FindControl("txtEAmt") as TextBox;
Label lblEtot = row1.FindControl("lblEtot") as Label;

DataRow row = tb.Rows[e.RowIndex];
row["PRD_CODE"] = ddlEPRD.SelectedValue;
row["QTY"] = Convert.ToDouble(txtEQty.Text);
row["AMT"] = Convert.ToDouble(txtEAmt.Text);
row["TOTAMT"] = Convert.ToDouble(txtEQty.Text) * Convert.ToDouble(txtEAmt.Text);

GrvSales.EditIndex = -1;
Bind();
}
protected void GrvSales_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
tb = (DataTable)(Session["ss"]);
tb.Rows.RemoveAt(e.RowIndex); //deleting the records and decreasing the total
Bind(); //calling the tempdata form refresh the output
}
protected void GrvSales_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
GrvSales.EditIndex = -1;
Bind();
}
protected void GrvSales_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
GrvSales.PageIndex = e.NewPageIndex;
Bind();
}
protected void txtBillNo_TextChanged(object sender, EventArgs e)
{
ViewState["bno"] = txtBillNo.Text;
query = "Select * from BILLDETAIL where BILLNO = "+ txtBillNo.Text +"";
//Ds.Tables["BILLDETAIL"] = d.DTFETCH(query);
Ds = d.FETCH(query);
if (Ds.Tables[0].Rows.Count > 0)
{
txtDate.Text = Ds.Tables[0].Rows[0]["DATE"].ToString();
txtName.Text = Ds.Tables[0].Rows[0]["CUSTNAME"].ToString();

string query1 = "Select * from BILLQTYDETAIL where BILLNO = " + txtBillNo.Text + "";
//Ds.Tables["BILLDETAIL"] = d.DTFETCH(query1);
Ds.Clear();

DataSet Ds1 = new DataSet();
Ds1.Clear();
Ds1 = d.FETCH(query1);
if (Ds1.Tables[0].Rows.Count > 0)
{
GrvSales.DataSource = Ds1;
GrvSales.DataBind();
}
}
else
{
cls();
GrvSales.DataSource = null;
GrvSales.DataBind();
txtBillNo.Text = ViewState["bno"].ToString();
txtName.Focus();
}

//bd ,BILLQTYDETAIL bq where bd.BILLNO = bq.BILLNO
}
}

================
Class File -DAL.CS
================

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
/// <summary>
/// Summary description for DAL
/// </summary>
public class DAL
{
DataSet Ds = new DataSet();
DataTable Dt = new DataTable();
//SqlConnection con = new SqlConnection(@"Data Source=ADMIN-PC;Initial Catalog=INTERVIEW;User ID=sa;Password=sandy");
SqlConnection con = new SqlConnection(@"Data Source=VISION4-PC;Initial Catalog=INTERVIEW;User ID=sa;Password=vision");
public DAL()
{
//
// TODO: Add constructor logic here
//
}

public void IUD(string Str)
{
con.Open();
SqlCommand cmd = new SqlCommand(Str,con);
cmd.ExecuteNonQuery();
con.Close();
}

public DataSet FETCH(string Str)
{
con.Open();
SqlDataAdapter da = new SqlDataAdapter(Str, con);
da.Fill(Ds);
con.Close();
return Ds;
}

public DataTable DTFETCH(string Str)
{
con.Open();
SqlDataAdapter da = new SqlDataAdapter(Str, con);
da.Fill(Dt);
con.Close();
return Dt;
}

public int INTFETCH(string Str)
{
int n;
con.Open();
SqlCommand cmd = new SqlCommand(Str, con);
n = Convert.ToInt32(cmd.ExecuteScalar());
con.Close();
return n;
}
public String STRFETCH(string Str)
{
string inf;
con.Open();
SqlCommand cmd = new SqlCommand(Str, con);
inf = Convert.ToString(cmd.ExecuteScalar());
con.Close();
return inf;
}

}

Hitesh Patel

Thursday, November 13, 2014

DATA TABLE STORE DATA EXAMPLE DESIGN FILE & CODING / CS FILE

No comments:

Post a Comment

Demo for Repository Pattern in ASP.Net

Search This Blog