SubsDotNet: February 2014

Take One DDL for Select ID,One Label for Msg, OneTextBox for ID & Ten for Name,Age and DOB and Six Buttons for Add,Edit,Update,Clear,Delete and Save Operation.

1.While clicking Add Button- ID of Textbox generate automatically and then fill up all Textboxes and then do operation for Save,Clear Operations.

2.While selecting ID of DDL- Then do for Edit,Update,Delete,Clear Operations.

here done Button Validation through 'Enable' features of Button and use Multiple Table.

Ans:-

Default.aspx:-

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<title>Add Head with Families..</title>

</head>

<body>

<asp:ScriptManager ID="ScriptManager1" runat="server">

</asp:ScriptManager>

<div class="divH"><font color="white"> <marquee direction="right"><strong>Operations of All Family Members with Head</strong></marquee></font></div>

<asp:UpdatePanel ID="Pnl" runat="server">

<table>

<tr class='trHCls'><td align="center" colspan='3'>HEAD MEMBER : </td></tr>

<tr><td>Select/Edit : </td><td><asp:TextBox ID="txtHid" ReadOnly="true" runat="server"></asp:TextBox></td><td><asp:DropDownList ID="DdlHNameId" runat="server" AutoPostBack="true"

onselectedindexchanged="DdlHNameId_SelectedIndexChanged" Visible="False"></asp:DropDownList></td></tr>

<tr><td class='trDCls' align="center" colspan='3'>DEPENDENTS : </td></tr>

<tr><td align="left" colspan='3'>DAUGHTER : </td></tr>

<tr><td align="left" colspan='3'>Others/If Any:(Sorry Not Added this features Now..) </td></tr>

</table>

</ContentTemplate>

</asp:UpdatePanel>

</div>

<asp:Button ID="btnAdd" CssClass="btn" runat="server" Text="ADD" onclick="btnAdd_Click"

AccessKey="a" ToolTip="alt+a" />

<asp:Button ID="btnEditthroughDDL" CssClass="btn" runat="server" Text="EDIT"

onclick="btnEditThroughDDL_Click" AccessKey="e" ToolTip="alt+e" />

<asp:Button ID="btnUpdate" CssClass="btn" runat="server" Text="UPDATE"

onclick="btnUpdate_Click" AccessKey="u" ToolTip="alt+u" />

<asp:Button ID="btnDelete" CssClass="btn" runat="server" Text="DELETE"

onclick="btnDelete_Click" AccessKey="d" ToolTip="alt+d" />

<asp:Button ID="btnClear" CssClass="btn" runat="server" Text="CLEAR"

onclick="btnClear_Click" style="height: 26px" AccessKey="c"

ToolTip="alt+c" />

<asp:Button ID="btnSave" CssClass="btn" runat="server" Text="SAVE" onclick="btnSave_Click"

AccessKey="s" ToolTip="alt+s" />

</div>

<asp:Label ID="lblMsg" runat="server" Text="Message"></asp:Label>

</div>

<div class="divH">Copy right @ 2014 Acess Meditech Pvt. Ltd.</div>

</center>

</form>

</body>

</html>

Default.aspx.cs:-

using System;

using System.Configuration;

using System.Data;

using System.Linq;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.HtmlControls;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Xml.Linq;

using System.Data.SqlClient;

using System.Drawing;

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

{

Class1 obj = new Class1();

SqlConnection cn;

SqlCommand cmd;

SqlDataAdapter da;

string conStr=null;

protected void Page_Load(object sender, EventArgs e)

{

if (!IsPostBack)

{

DdlVisibility();

AllBtnEnabled();

}

void DdlVisibility()

{

DdlHNameId.Visible = false;

}

void AllBtnEnabled()

{

obj.GetAllBtn(btnAdd, btnSave, btnEditthroughDDL, btnUpdate, btnDelete, btnClear,txtHName,txtHAge, txtHDob,txtWName, txtWAge,txtWDob,txtSName,txtSAge,txtSDob, txtDName, txtDAge, txtDDob); //here Pass the Button's ID

}

void AddBtnEnable()

{

obj.GetAddBtn(btnAdd, btnSave, btnEditthroughDDL, btnUpdate, btnDelete, btnClear, txtHName, txtHAge, txtHDob, txtWName, txtWAge, txtWDob, txtSName, txtSAge, txtSDob, txtDName, txtDAge, txtDDob);

}

void SaveBtnEnabled()

{

obj.GetSaveBtn(btnAdd, btnSave, btnEditthroughDDL, btnUpdate, btnDelete, btnClear);

}

void ClearBtnEnabled()

{

obj.GetClearBtn(btnAdd, btnSave, btnEditthroughDDL, btnUpdate, btnDelete, btnClear);

}

void EditBtnEnabled()

{

obj.GetEditBtn(btnAdd, btnSave, btnEditthroughDDL, btnUpdate, btnDelete, btnClear);

}

void UpdateBtnEnabled()

{

obj.GetUpdateBtn(btnAdd, btnSave, btnEditthroughDDL, btnUpdate, btnDelete, btnClear);

}

void DeleteBtnEnabled()

{

obj.GetDeleteBtn(btnAdd, btnSave, btnEditthroughDDL, btnUpdate, btnDelete, btnClear);

}

protected void btnAdd_Click(object sender, EventArgs e)

{

DdlVisibility();

AddBtnEnable();

txtHName.Focus();

cn = new SqlConnection(obj.GetConnection(conStr));

cmd = new SqlCommand();

cmd.Connection = cn;

cmd.CommandType = CommandType.StoredProcedure;

cmd.CommandText = "spGenerateHeadMemberId";

string str = "";

lblMsg.ForeColor = Color.Red;

try

{

cn.Open();

str = cmd.ExecuteScalar().ToString();

txtHid.Text = Convert.ToString(str);

}

catch (Exception ex)

{

lblMsg.Text = ex.Message;

}

finally

{

cn.Close();

}

protected void btnSave_Click(object sender, EventArgs e)

{

DdlVisibility();

SaveBtnEnabled();

cn = new SqlConnection(obj.GetConnection(conStr));

cmd = new SqlCommand();

cmd.Connection = cn;

cmd.CommandType = CommandType.StoredProcedure;

cmd.CommandText = "spInsertAllFamily";

cmd.Parameters.AddWithValue("@hid", txtHid.Text);

cmd.Parameters.AddWithValue("@hname", txtHName.Text);

cmd.Parameters.AddWithValue("@hage", txtHAge.Text);

cmd.Parameters.AddWithValue("@hdob", txtHDob.Text);

cmd.Parameters.AddWithValue("@wid", txtHid.Text);

cmd.Parameters.AddWithValue("@sid", txtHid.Text);

cmd.Parameters.AddWithValue("@did", txtHid.Text);

cmd.Parameters.AddWithValue("@wname", txtWName.Text);

cmd.Parameters.AddWithValue("@wage", txtWAge.Text);

cmd.Parameters.AddWithValue("@wdob", txtWDob.Text);

cmd.Parameters.AddWithValue("@sname", txtSName.Text);

cmd.Parameters.AddWithValue("@sage", txtSAge.Text);

cmd.Parameters.AddWithValue("@sdob", txtSDob.Text);

cmd.Parameters.AddWithValue("@dname", txtDName.Text);

cmd.Parameters.AddWithValue("@dage", txtDAge.Text);

cmd.Parameters.AddWithValue("@ddob", txtDDob.Text);

cmd.Parameters.AddWithValue("@smode", "0");

lblMsg.ForeColor = Color.Red;

int rowAffected = 0;

try

{

cn.Open();

rowAffected = cmd.ExecuteNonQuery();

if (rowAffected > 0)

{

lblMsg.Text = "Inserted..";

}

else

{

lblMsg.Text = "Not Inserted..";

}

catch (Exception ex)

{

lblMsg.Text = ex.Message;

}

finally

{

cn.Close();

}

protected void btnEditThroughDDL_Click(object sender, EventArgs e)

{

EditBtnEnabled();

DdlHNameId.Visible = true;

cn = new SqlConnection(obj.GetConnection(conStr));

cmd = new SqlCommand();

cmd.Connection = cn;

cmd.CommandType = CommandType.StoredProcedure;

cmd.CommandText = "spGetHeaderNameIdInDDl";

da = new SqlDataAdapter(cmd);

DataTable dt = new DataTable();

da.Fill(dt);

DdlHNameId.DataTextField = "hname";

DdlHNameId.DataValueField = "hid";

DdlHNameId.DataSource = dt;

DdlHNameId.DataBind();

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

}

protected void DdlHNameId_SelectedIndexChanged(object sender, EventArgs e)

{

cn = new SqlConnection(obj.GetConnection(conStr));

cmd = new SqlCommand();

cmd.Connection = cn;

cmd.CommandType = CommandType.StoredProcedure;

cmd.CommandText = "spGetDataAsPerChangeDDL";

cmd.Parameters.AddWithValue("@hid",DdlHNameId.SelectedValue.ToString());

da = new SqlDataAdapter(cmd);

DataSet ds = new DataSet();

DataTable dt=new DataTable();

da.Fill(dt);

if (dt.Rows.Count > 0)

{

//txtName.Text=ds.Tables["Emp"].Rows[0]["name"].ToString(); --it will use when we will take DataSet instead of DataTable

txtHid.Text = dt.Rows[0]["hid"].ToString();

txtHName.Text = dt.Rows[0]["hname"].ToString();

txtHAge.Text = dt.Rows[0]["hage"].ToString();

txtHDob.Text = dt.Rows[0]["hdob"].ToString();

txtWName.Text = dt.Rows[0]["wname"].ToString();

txtWAge.Text = dt.Rows[0]["wage"].ToString();

txtWDob.Text = dt.Rows[0]["wdob"].ToString();

txtSName.Text = dt.Rows[0]["sname"].ToString();

txtSAge.Text = dt.Rows[0]["sage"].ToString();

txtSDob.Text = dt.Rows[0]["sdob"].ToString();

txtDName.Text = dt.Rows[0]["dname"].ToString();

txtDAge.Text = dt.Rows[0]["dage"].ToString();

txtDDob.Text = dt.Rows[0]["ddob"].ToString();

}

protected void btnUpdate_Click(object sender, EventArgs e)

{

UpdateBtnEnabled();

cn = new SqlConnection(obj.GetConnection(conStr));

cmd = new SqlCommand();

cmd.Connection = cn;

cmd.CommandType = CommandType.StoredProcedure;

cmd.CommandText = "spUpdateAllFamilyRecord";

cmd.Parameters.AddWithValue("@hid", DdlHNameId.SelectedValue.ToString());

cmd.Parameters.AddWithValue("@hname", txtHName.Text);

cmd.Parameters.AddWithValue("@hage", txtHAge.Text);

cmd.Parameters.AddWithValue("@hdob", txtHDob.Text);

cmd.Parameters.AddWithValue("@wid", DdlHNameId.SelectedValue.ToString());

cmd.Parameters.AddWithValue("@wname", txtWName.Text);

cmd.Parameters.AddWithValue("@wage", txtWAge.Text);

cmd.Parameters.AddWithValue("@wdob", txtWDob.Text);

cmd.Parameters.AddWithValue("@sid", DdlHNameId.SelectedValue.ToString());

cmd.Parameters.AddWithValue("@sname", txtSName.Text);

cmd.Parameters.AddWithValue("@sage", txtSAge.Text);

cmd.Parameters.AddWithValue("@sdob", txtSDob.Text);

cmd.Parameters.AddWithValue("@did", DdlHNameId.SelectedValue.ToString());

cmd.Parameters.AddWithValue("@dname", txtDName.Text);

cmd.Parameters.AddWithValue("@dage", txtDAge.Text);

cmd.Parameters.AddWithValue("@ddob", txtDDob.Text);

lblMsg.ForeColor = Color.Red;

int rowAffected = 0;

try

{

cn.Open();

rowAffected = cmd.ExecuteNonQuery();

if (rowAffected > 0)

{

lblMsg.Text = "Updated..";

}

else

{

lblMsg.Text = "Not Updated..";

}

catch (Exception ex)

{

lblMsg.Text = ex.Message;

}

finally

{

cn.Close();

}

protected void btnDelete_Click(object sender, EventArgs e)

{

DeleteBtnEnabled();

cn = new SqlConnection(obj.GetConnection(conStr));

cmd = new SqlCommand();

cmd.Connection = cn;

cmd.CommandType = CommandType.StoredProcedure;

cmd.CommandText = "spDeleteAllFamilyRecord";

cmd.Parameters.AddWithValue("@hid", DdlHNameId.SelectedValue.ToString());

cmd.Parameters.AddWithValue("@wid", txtHid.Text);

cmd.Parameters.AddWithValue("@sid", txtHid.Text);

cmd.Parameters.AddWithValue("@did", txtHid.Text);

lblMsg.ForeColor = Color.Red;

int rowAffected = 0;

try

{

cn.Open();

rowAffected = cmd.ExecuteNonQuery();

if (rowAffected > 0)

{

lblMsg.Text = "Deleted..";

}

else

{

lblMsg.Text = "Not Deleted..";

}

catch (Exception ex)

{

lblMsg.Text = ex.Message;

}

finally

{

cn.Close();

}

protected void btnClear_Click(object sender, EventArgs e)

{

DdlVisibility();

ClearBtnEnabled();

txtHid.Text = string.Empty.ToString();

txtHName.Text=string.Empty.ToString();

txtHAge.Text=string.Empty.ToString();

txtHDob.Text = string.Empty.ToString();

txtWName.Text = string.Empty.ToString();

txtWAge.Text = string.Empty.ToString();

txtWDob.Text = string.Empty.ToString();

txtSName.Text = string.Empty.ToString();

txtSAge.Text = string.Empty.ToString();

txtSDob.Text = string.Empty.ToString();

txtDName.Text = string.Empty.ToString();

txtDAge.Text = string.Empty.ToString();

txtDDob.Text = string.Empty.ToString();

DdlHNameId.Items.Clear();

lblMsg.ForeColor = Color.Red;

lblMsg.Text = "Cleared..";

}

Get Connection Through Class file:-

using System;

using System.Data;

using System.Configuration;

using System.Linq;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.HtmlControls;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Xml.Linq;

using System.Data.SqlClient;

/// <summary>

/// Summary description for Class1

/// </summary>

public class Class1

{

public Class1()

{

// TODO: Add constructor logic here

}

public string GetConnection(string str)

{

str = "server=192.168.0.200;database=subsdb;user id=sa;pwd=amtpl@123";

return str;

}

public void GetAllBtn(Button bAdd, Button bSave, Button bEdit, Button bUpdate, Button bDelete, Button bClear, TextBox tbHName, TextBox tbHAge, TextBox tbHDob, TextBox tbWName, TextBox tbWAge, TextBox tbWDob, TextBox tbSName, TextBox tbSAge, TextBox tbSDob, TextBox tbDName, TextBox tbDAge, TextBox tbDDob)

{

bAdd.Enabled = false;

bSave.Enabled = false;

bEdit.Enabled = false;

bUpdate.Enabled = false;

bDelete.Enabled = false;

bClear.Enabled = true;

tbHName.Enabled = false;

tbHAge.Enabled = false;

tbHDob.Enabled = false;

tbWName.Enabled = false;

tbWAge.Enabled = false;

tbWDob.Enabled = false;

tbSName.Enabled = false;

tbSAge.Enabled = false;

tbSDob.Enabled = false;

tbDName.Enabled = false;

tbDAge.Enabled = false;

tbDDob.Enabled = false;

}

public void GetAddBtn(Button bAdd, Button bSave, Button bEdit, Button bUpdate, Button bDelete, Button bClear, TextBox tbHName, TextBox tbHAge, TextBox tbHDob, TextBox tbWName, TextBox tbWAge, TextBox tbWDob, TextBox tbSName, TextBox tbSAge, TextBox tbSDob, TextBox tbDName, TextBox tbDAge, TextBox tbDDob)

{

bAdd.Enabled = false;

bSave.Enabled = true;

bEdit.Enabled = false;

bUpdate.Enabled = false;

bDelete.Enabled = false;

bClear.Enabled = true;

tbHName.Enabled = true;

tbHAge.Enabled = true;

tbHDob.Enabled = true;

tbWName.Enabled = true;

tbWAge.Enabled = true;

tbWDob.Enabled = true;

tbSName.Enabled = true;

tbSAge.Enabled = true;

tbSDob.Enabled = true;

tbDName.Enabled = true;

tbDAge.Enabled = true;

tbDDob.Enabled = true;

}

public void GetSaveBtn(Button bAdd, Button bSave, Button bEdit, Button bUpdate, Button bDelete, Button bClear)

{

bSave.Enabled = false;

bAdd.Enabled = false;

bEdit.Enabled = false;

bClear.Enabled = true;

bUpdate.Enabled = false;

bDelete.Enabled = false;

}

public void GetClearBtn(Button bAdd, Button bSave, Button bEdit, Button bUpdate, Button bDelete, Button bClear) //here pass the variables of Button types

{

bClear.Enabled = false;

bAdd.Enabled = true;

bEdit.Enabled = true;

bUpdate.Enabled = false;

bDelete.Enabled = false;

bSave.Enabled = false;

}

public void GetEditBtn(Button bAdd, Button bSave, Button bEdit, Button bUpdate, Button bDelete, Button bClear)

{

bEdit.Enabled = false;

bUpdate.Enabled = true;

bDelete.Enabled = true;

bAdd.Enabled = false;

bSave.Enabled = false;

bClear.Enabled = true;

}

public void GetUpdateBtn(Button bAdd, Button bSave, Button bEdit, Button bUpdate, Button bDelete, Button bClear)

{

bDelete.Enabled = false;

bEdit.Enabled = false;

bAdd.Enabled = false;

bUpdate.Enabled = false;

bClear.Enabled = true;

bSave.Enabled = false;

}

public void GetDeleteBtn(Button bAdd, Button bSave, Button bEdit, Button bUpdate, Button bDelete, Button bClear)

{

bDelete.Enabled = false;

bEdit.Enabled = false;

bAdd.Enabled = false;

bUpdate.Enabled = false;

bClear.Enabled = true;

bSave.Enabled = false;

}

For Style write code in .CSS file:-

.trHCls{

background-color:#FFFF99;

}

.trDCls{

background-color:#FFFFCC;

}

.header{

background-color:#CCFFFF;

}

.btn{

background-color:#99FF99;

width:75px;

height:25px;

border:solid 2px #999999;

}

.divCls

{

background-color:Silver;

}

.divH{

background-color:#999999;

}

For Create Table and Stored Procedure:-

use subsdb

1.create table tblHeadofFamily(hid int constraint p_key0 primary key,hname varchar(30),hage int,hdob date,Wifeid int references tblWife(wid),Sonid int references tblSon(sid),Daughterid int references tblDaughter(did))

2.create table tblWife(wid int constraint p_key primary key,wname varchar(30),wage int,wdob date)

3.create table tblSon(sid int constraint p_key1 primary key,sname varchar(30),sage int,sdob date)

4.create table tblDaughter(did int constraint p_key2 primary key,dname varchar(30),dage int,ddob date)

create proc spGenerateHeadMemberId

begin

select ISNULL(MAX(hid),0)+1 as Hid from tblHeadofFamily

end

create proc spInsertAllFamily(@hid int,@hname varchar(30),@hage int,@hdob date,

@wid int,@wname varchar(30),@wage int,@wdob date,

@sid int,@sname varchar(30),@sage int,@sdob date,

@did int,@dname varchar(30),@dage int,@ddob date,@smode int)

begin

if @smode=0

begin

select @hid=ISNULL(max(hid),0)+1 from tblHeadofFamily

select @wid=@hid

select @sid=@hid

select @did=@hid

--insert into tblWife(wid,wname,wage,wdob)values(@wid,@wname,@wage,@wdob)

--insert into tblSon(sid,sname,sage,sdob)values(@sid,@sname,@sage,@sdob)

--insert into tblDaughter(did,dname,dage,ddob)values(@did,@dname,@dage,@ddob)

insert into tblHeadofFamily(hid,hname,hage,hdob,Wifeid,Sonid,Daughterid)values (@hid,@hname,@hage,@hdob,@wid,@sid,@did)

end

else

begin

update tblWife set wname=@wname,wage=@wage,wdob=@wdob where wid=@wid

update tblSon set sname=@sname,sage=@sage,sdob=@sdob where sid=@sid

update tblDaughter set dname=@dname,dage=@dage,ddob=@ddob where did=@did

update tblHeadofFamily set hname=@hname,hage=@hage,hdob=@hdob where hid=@hid

end

exec spInsertAllFamily 1,'ggb',27,'2014-02-17',1,'ggb',27,'2014-02-17',1,'ggb',27,'2014-02-17',1,'ggb',27,'2014-02-17',0

create proc spGetHeaderNameIdInDDl

begin

select hname+'-'+CAST(hid as varchar) as hname,hid from tblHeadofFamily

end

exec spGetHeaderNameIdInDDl

create proc spGetDataAsPerChangeDDL(@hid int)

begin

select h.hid,h.hname,h.hage,h.hdob,w.wname,w.wage,w.wdob,s.sname,s.sage,s.sdob,d.dname,d.dage,d.ddob from tblHeadofFamily h inner join tblWife w on w.wid=h.hid inner join tblSon s on s.sid=h.hid inner join tblDaughter d on d.did=h.hid

where hid=@hid

end

exec spGetDataAsPerChangeDDL 9

create proc spUpdateAllFamilyRecord(@hid int,@hname varchar(30),@hage int,@hdob date,

@wid int,@wname varchar(30),@wage int,@wdob date,

@sid int,@sname varchar(30),@sage int,@sdob date,

@did int,@dname varchar(30),@dage int,@ddob date )

begin

update tblWife set wname=@wname,wage=@wage,wdob=@wdob where wid=@wid

update tblSon set sname=@sname,sage=@sage,sdob=@sdob where sid=@sid

update tblDaughter set dname=@dname,dage=@dage,ddob=@ddob where did=@did

update tblHeadofFamily set hname=@hname,hage=@hage,hdob=@hdob where hid=@hid

end

create proc spDeleteAllFamilyRecord(@hid int,@wid int,@sid int,@did int)

begin

delete from tblHeadofFamily where hid=@hid

delete from tblWife where wid=@wid

delete from tblDaughter where did=@did

delete from tblSon where sid=@sid

end

exec spDeleteAllFamilyRecord 4,4,4,4

SubsDotNet

Friday 28 February 2014

A Small Application to Provide Validation on Button Level using C#.NET(using Multiple Tables)