mithilesh kumar singh: How to Perform CRUD operation with 3-tier using gridview and dynamic bind of drop down list in grid view when click edit button in asp .net

Hi. Today in this article, I explain about how to perform crud operation using more than one table and also bind drop drown list when clicking edit button inside the grid row and whatever value is blinded with column of grid view so when clicking the edit button then same value should be displayed first time in grid view drop-down list. We will perform whole functionality in 3 Tier architecture. So now let us see step by step

Step 1

We will create tow tables in SQL

Now create a stored procedure

Create

create procedure [dbo].[USP_BindDesignationDetails]

begin

select a.Desg_code, a.Desg_Name, a.Qualification, a.Experience,b.Dept_Name,b.Dept_code from Designation_Master a,Department_Master b where

a.FK_Dept_code = b.Dept_code

end

Select

CREATE procedure [dbo].[USP_BindDesignationDetails]

begin

select a.Desg_code, a.Desg_Name, a.Qualification, a.Experience,b.Dept_Name,b.Dept_code from Designation_Master a,Department_Master b where

a.FK_Dept_code = b.Dept_code

end

Update

Create proc [dbo].[USP_UpdateDegination]

(

@Desg_code varchar(20),

@Desg_Name varchar(50),

@Qualification varchar(50),

@Experience varchar(5),

@FK_Dept_code varchar(10)

)

begin

update Designation_Master set

Desg_Name = @Desg_Name,

Qualification=@Qualification,

Experience=@Experience,

FK_Dept_code=@FK_Dept_code where

Desg_code=@Desg_code

end

Delete

CREATE proc [dbo].[USP_DeleteDesignation]

(

@Desg_code varchar(20)

)

begin

Delete Designation_Master where

Desg_code=@Desg_code

end

Step 2

Open visual studio and add a web form and also add two more projects i.e Bussiness Layer and DataAccess Layer

Right click of solution explorer >> Add new project>> Class Library >> Add

Similarly, you can add both projects and if need Entity Layer then you can add the same way

Let us see our architecture below

Step 3

First open the DataAccess Layer and add a class

Conection.cs

public class Connection

{

public SqlConnection con = null;

public Conection()

{

con = new SqlConnection(ConfigurationManager.ConnectionStrings["SqlCon"].ConnectionString);

}

Now-Again add a class

DA_DepartmentDesignation.cs

public DataSet BindDes()

{

SqlCommand cmd = new SqlCommand("USP_BindDesignationDetails", sqlCon.con);

cmd.CommandType = CommandType.StoredProcedure;

if (sqlCon.con.State == ConnectionState.Closed)

{

sqlCon.con.Open();

}

SqlDataAdapter da = new SqlDataAdapter(cmd);

DataSet ds = new DataSet();

da.Fill(ds);

return ds;

}

public int InsertDesg(string DesgName, string Qualification, string Experience, string DeptCode)

{

SqlCommand cmd = new SqlCommand("USP_InsertDesignation", sqlCon.con);

cmd.CommandType = CommandType.StoredProcedure;

if (sqlCon.con.State == ConnectionState.Closed)

{

sqlCon.con.Open();

}

cmd.Parameters.AddWithValue("@Desg_Name", DesgName.Trim());

cmd.Parameters.AddWithValue("@Qualification", Qualification.Trim());

cmd.Parameters.AddWithValue("@Experience", Experience.Trim());

cmd.Parameters.AddWithValue("@Dept_code", DeptCode.Trim());

return cmd.ExecuteNonQuery();

}

public string UpdateDesignationDetails(string desiCode,string deptCode, string desName, string Qualification, string exp)

{

string strMsg = string.Empty;

SqlCommand cmd = new SqlCommand("USP_UpdateDegination", sqlCon.con);

cmd.CommandType = CommandType.StoredProcedure;

if (sqlCon.con.State == ConnectionState.Closed)

{

sqlCon.con.Open();

}

cmd.Parameters.AddWithValue("@Desg_code", desiCode);

cmd.Parameters.AddWithValue("@Desg_Name", desName);

cmd.Parameters.AddWithValue("@Qualification", Qualification);

cmd.Parameters.AddWithValue("@Experience", exp);

cmd.Parameters.AddWithValue("@FK_Dept_code", deptCode);

int i = cmd.ExecuteNonQuery();

if (i > 0)

{

strMsg = "Designation Update Successfull";

}

else

{

strMsg = "Designation Upadte faild";

}

return strMsg;

}

public string DeleteDesignationDetails(string DesCode)

{

string strMsg = string.Empty;

SqlCommand cmd = new SqlCommand("USP_DeleteDesignation", sqlCon.con);

cmd.CommandType = CommandType.StoredProcedure;

if (sqlCon.con.State == ConnectionState.Closed)

{

sqlCon.con.Open();

}

cmd.Parameters.AddWithValue("@Desg_code", DesCode);

int i = cmd.ExecuteNonQuery();

if (i > 0)

{

strMsg = "Designation Delete Successfull";

}

else

{

strMsg = "Designation Delete faild";

}

return strMsg;

}

Step 4

Now we will build the DataAccess Leyer and then go the Business Layer and add dll of DataAccess Layer in Business Layer and then add a class in Business Layer

public DataSet DegDetails()

{

return objDept.BindDes();

}

public int AddDesignation(string DesgName, string Qualification, string Experience, string DeptCode)

{

return objDept.InsertDesg(DesgName, Qualification, Experience, DeptCode);

}

public string UpdateDesignation(string desCode,string deptCode, string desName, string Qualification , string exp)

{

return objDept.UpdateDesignationDetails(desCode,deptCode, desName, Qualification, exp);

}

public string DeleteDesignation(string desCode)

{

return objDept.DeleteDesignationDetails(desCode);

}

Step5

Now build Business Layer and then go to the presentation Layer means our web form where we will design view and then first add all of the business layers in the presentation layer

Now right-click and add a web form and write below code

<asp:Panel runat = "server" class="card" ID="PanelDesignation">

<h4 class="card-title"><i class="fa fa-table m-r-10"></i>Designation List</h4>

</div>

<asp:Panel runat = "server" ID="desig">

<asp:Label ID = "lblDepartment1" runat="server" CssClass="label-control">Department</asp:Label>

<asp:DropDownList ID = "ddlDesgDepartment" runat="server" TabIndex="6" class="selectpicker" CssClass="form-control">

<asp:ListItem>All Departments</asp:ListItem>

</asp:DropDownList>

</div>

<asp:Label ID = "lblDesignation" runat="server" CssClass="label-control">Designation</asp:Label>

<asp:TextBox ID = "txtDesgDesignation" runat="server" TabIndex="7" CssClass="form-control"></asp:TextBox>

</div>

<asp:Label ID = "lblQualifation" runat="server" CssClass="label-control">Qualifation</asp:Label>

<asp:TextBox ID = "txtDesgQualification" runat="server" TabIndex="8" CssClass="form-control"></asp:TextBox>

</div>

<asp:Label ID = "lblExperience" runat="server" CssClass="label-control">Experience</asp:Label>

<asp:TextBox ID = "txtDesgExperience" runat="server" TabIndex="9" CssClass="form-control"></asp:TextBox>

</div>

</div>

< asp:Label ID = "lblDesignetaionMSG" Font-Bold="true" Font-Size="Medium" runat="server" CssClass="label-control"></asp:Label>

</div>

<hr />

<asp:Button ID = "btnSave" runat="server" Text="Save" OnClick="btnSave_Click" class="btn btn-info" TabIndex="10" OnClientClick="return validatedeptdesilist2();" />

<asp:Button ID = "btnClear" runat="server" Text="Clear" class="btn btn-info" TabIndex="11" />

</div>

</asp:Panel>

<asp:GridView ID = "gridDesginationList" runat="server" AutoGenerateColumns="False" Width="100%" AllowPaging="True"

OnPageIndexChanging="gridDesginationList_PageIndexChanging" OnRowCancelingEdit="gridDesginationList_RowCancelingEdit"

OnRowUpdating="gridDesginationList_RowUpdating" OnRowDeleting="gridDesginationList_RowDeleting" OnRowDataBound="gridDesginationList_RowDataBound"

OnRowEditing="gridDesginationList_RowEditing" CellPadding="5" DataKeyNames="Desg_code"

CssClass="grid" RowStyle-CssClass="rows">

<asp:TemplateField HeaderText = "Desg code" SortExpression="Desg_code">

<asp:TextBox ID = "txtDesgCode" runat="server" CssClass="form-control" Text='<%# Bind("Desg_code") %>'></asp:TextBox>

</EditItemTemplate>

<asp:Label ID = "lblDesgCode" runat="server" Text='<%# Bind("Desg_code") %>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText = "Department" SortExpression="Dept_Name">

<%-- <EditItemTemplate>

<asp:TextBox ID = "txtDeptName" runat="server" CssClass="form-control" Text='<%# Bind("Dept_Name") %>'></asp:TextBox>

</EditItemTemplate>--%>

<asp:DropDownList ID = "ddlDeptName" runat="server" Font-Size="Small" Width="160px">

</asp:DropDownList>

</EditItemTemplate>

<asp:Label ID = "lblDeptName" runat="server" Text='<%# Bind("Dept_Name") %>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<%-- <asp:TemplateField HeaderText = "Department" SortExpression="Dept_Name">

<asp:TextBox ID = "txtDeptName" runat="server" CssClass="form-control" Text='<%# Bind("Dept_code") %>'></asp:TextBox>

</EditItemTemplate>

<asp:Label ID = "lblDeptName" runat="server" Text='<%# Bind("Dept_code") %>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>--%>

<asp:TemplateField HeaderText = "Designation" SortExpression="Desg_Name">

<asp:TextBox ID = "txtDesName" runat="server" CssClass="form-control" Text='<%# Bind("Desg_Name") %>'></asp:TextBox>

</EditItemTemplate>

<asp:Label ID = "lblDesName" runat="server" Text='<%# Bind("Desg_Name") %>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText = "Qualification" SortExpression="Qualification_Req">

<asp:TextBox ID = "txtQualification" runat="server" CssClass="form-control" Text='<%# Bind("Qualification") %>'></asp:TextBox>

</EditItemTemplate>

<asp:Label ID = "lblQualification" runat="server" Text='<%# Bind("Qualification") %>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText = "Experience" SortExpression="Experience_Req">

<asp:TextBox ID = "txtExperience" runat="server" CssClass="form-control" Text='<%# Bind("Experience") %>'></asp:TextBox>

</EditItemTemplate>

<asp:Label ID = "lblExperience" runat="server" Text='<%# Bind("Experience") %>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:CommandField ShowEditButton = "true" ItemStyle-HorizontalAlign="Center" HeaderStyle-HorizontalAlign="Center" ShowCancelButton="true" ShowDeleteButton="true" HeaderText="Edit / Delete"

EditText="<i class='fa fa-edit btn btn-info'> Edit</i>"

DeleteText="<i class='fa fa-trash btn btn-danger'> Delete</i>" UpdateText="<i class='fa fa-edit btn btn-info'> Update</i>" CancelText="<i class='fa fa-trash btn btn-danger'> Cancel</i>" />

</Columns>

< EditRowStyle BackColor="#ffffff" />

< SortedAscendingHeaderStyle BackColor="#246B61" />

< SortedDescendingHeaderStyle BackColor="#15524A" />

< asp:Label ID = "lblDesiMgs" Font-Bold="true" Font-Size="Medium" runat="server" CssClass="label-control"></asp:Label>

</div>

</asp:Panel>

Now right click and select view code and write below code

if(!IsPostBack)

{

bindDepartment();

}

protected void gridDesginationList_RowDataBound(object sender, GridViewRowEventArgs e)

{

if (e.Row.RowType == DataControlRowType.DataRow && gridDesginationList.EditIndex == e.Row.RowIndex)

{

DropDownList ddlDept = (DropDownList)e.Row.FindControl("ddlDeptName");

DataSet ds = new DataSet();

ds = objDept.DeptDetails();

ddlDept.DataSource = ds.Tables[0];

ddlDept.DataTextField = "Dept_Name";

ddlDept.DataValueField = "Dept_code";

ddlDept.DataBind();

string selectedDept = DataBinder.Eval(e.Row.DataItem, "Dept_Name").ToString();

string selectedDeptCode = DataBinder.Eval(e.Row.DataItem, "Dept_code").ToString();

ddlDept.Items.Insert(0, new ListItem(selectedDept, selectedDeptCode, true));

}

protected void gridDesginationList_PageIndexChanging(object sender, GridViewPageEventArgs e)

{

gridDesginationList.PageIndex = e.NewPageIndex;

bindDeptDesgGrid();

}

protected void gridDesginationList_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)

{

gridDesginationList.EditIndex = -1;

bindDeptDesgGrid();

}

protected void gridDesginationList_RowUpdating(object sender, GridViewUpdateEventArgs e)

{

DropDownList DeptName = gridDesginationList.Rows[e.RowIndex].FindControl("ddlDeptName") as DropDownList;

TextBox txtDegiName = gridDesginationList.Rows[e.RowIndex].FindControl("txtDesName") as TextBox;

TextBox txtQualification = gridDesginationList.Rows[e.RowIndex].FindControl("txtQualification") as TextBox;

TextBox txtExperience = gridDesginationList.Rows[e.RowIndex].FindControl("txtExperience") as TextBox;

TextBox txtDesigCode = gridDesginationList.Rows[e.RowIndex].FindControl("txtDesgCode") as TextBox;

string str1 = DeptName.SelectedItem.Value;

string str2 = txtDegiName.Text.Trim();

string str3 = txtQualification.Text.Trim();

string str4 = txtExperience.Text.Trim();

string str5 = txtDesigCode.Text;

string str = objDept.UpdateDesignation(str5.ToString(), str1.ToString(), str2.ToString(),str3.ToString(), str4.ToString());

lblDesiMgs.Text = str;

lblDesiMgs.ForeColor = System.Drawing.Color.Green;

gridDesginationList.EditIndex = -1;

bindDeptDesgGrid();

}

protected void gridDesginationList_RowDeleting(object sender, GridViewDeleteEventArgs e)

{

Label lblDesigCode = gridDesginationList.Rows[e.RowIndex].FindControl("lblDesgCode") as Label;

string str1 = lblDesigCode.Text;

string str = objDept.DeleteDesignation(str1.ToString());

lblDesiMgs.Text = str;

lblDesiMgs.ForeColor = System.Drawing.Color.Green;

bindDeptDesgGrid();

}

protected void gridDesginationList_RowEditing(object sender, GridViewEditEventArgs e)

{

gridDesginationList.EditIndex = e.NewEditIndex;

bindDeptDesgGrid();

}

protected void btnSave_Click(object sender, EventArgs e)

{

string deptCode = ddlDesgDepartment.SelectedItem.Value;

string desiName = txtDesgDesignation.Text;

string qualification = txtDesgQualification.Text;

string experience = txtDesgExperience.Text;

int result = objDept.AddDesignation(desiName, qualification, experience, deptCode);

if (result > 0)

{

lblDesignetaionMSG.Text = "Designation insert successfull !!!";

lblDesignetaionMSG.ForeColor = System.Drawing.Color.Green;

bindDeptDesgGrid();

}

else

{

lblDesignetaionMSG.Text = "Designation insert faild !!!";

lblDesignetaionMSG.ForeColor = System.Drawing.Color.Red;

}

Step6

Now we will run the project and see the result and check step by step

So first check insert

Click Save

Now check Update

Click Update

Now Check Delete Functionality

Tuesday, September 18, 2018

How to Perform CRUD operation with 3-tier using gridview and dynamic bind of drop down list in grid view when click edit button in asp .net

No comments:

C# Corner status