How to implement Employee - Dept in Ado.Net
Ask by hanmandlu vasani
Updated 22 Sep 2020
EMP—DEPT
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace DataAccesLayer
{
public class DataAccessLayer
{
SqlConnection cn = null;
SqlCommand cmd = null;
SqlDataAdapter da = null;
DataSet ds = null;
SqlDataReader dr = null;
public DataAccessLayer()
{
// Constructor Code here
}
#region Execute DataSet With No Parameter
public DataSet ExecuteDataSet(string spName)
{
try
{
cn = new SqlConnection(clsConnection.Getconnection());
cn.Open();
ds = new DataSet();
da = new SqlDataAdapter(spName, cn);
da.Fill(ds);
return ds;
}
catch (Exception ex)
{
throw new ArgumentException(ex.Message);
}
finally
{
cn.Close();
da.Dispose();
ds.Dispose();
}
}
#endregion
#region Execute Dataset With SQL Parameters
public DataSet ExecuteDataSet(string spName, params object[] parametersValues)
{
try
{
cn = new SqlConnection(clsConnection.Getconnection());
cn.Open();
cmd = new SqlCommand();
cmd.CommandText = spName;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = cn;
cmd.Parameters.AddRange(parametersValues);
using (da = new SqlDataAdapter(cmd))
{
ds = new DataSet();
da.Fill(ds);
cmd.Parameters.Clear();
return ds;
}
}
catch (Exception ex)
{
throw new ArgumentException(ex.Message);
}
finally
{
cn.Close();
cmd.Dispose();
da.Dispose();
ds.Dispose();
}
}
#endregion
#region Execute DataReader with No Parameter
public SqlDataReader ExecuteReader(string spName)
{
try
{
cn = new SqlConnection(clsConnection.Getconnection());
cn.Open();
cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = spName;
return cmd.ExecuteReader();
}
catch (Exception ex)
{
throw new ArgumentException(ex.Message);
}
finally
{
cn.Close();
cmd.Dispose();
}
}
#endregion
#region Execute DataReader with SQL Parameters
public SqlDataReader ExecuteDataReader(string spName, params object[] parametersValues)
{
try
{
cn = new SqlConnection(clsConnection.Getconnection());
cn.Open();
cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = spName;
cmd.Parameters.AddRange(parametersValues);
return cmd.ExecuteReader();
}
catch (Exception ex)
{
throw new ArgumentException(ex.Message);
}
finally
{
cn.Close();
cmd.Dispose();
}
}
#endregion
#region Execute NonQuery with SQL parameter
public int ExecuteNonQuery(string spName, params object[] parametersValues)
{
try
{
cn = new SqlConnection(clsConnection.Getconnection());
cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandText = spName;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddRange(parametersValues);
cn.Open();
return cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw new ArgumentException(ex.Message);
}
finally
{
cn.Close();
cmd.Dispose();
}
}
#endregion
#region Execute NonQuery without SQL parameter
public int ExecuteNonQuery(string spName)
{
try
{
cn = new SqlConnection(clsConnection.Getconnection());
cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandText = spName;
cmd.CommandType = CommandType.StoredProcedure;
cn.Open();
return cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw new ArgumentException(ex.Message);
}
finally
{
cn.Close();
cmd.Dispose();
}
}
#endregion
#region Execute Sclar with No Parameter
public int ExecuteSclar(string spName)
{
try
{
Int32 resultSet;
cn = new SqlConnection(clsConnection.Getconnection());
cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandText = spName;
cmd.CommandType = CommandType.StoredProcedure;
cn.Open();
resultSet = (Int32)cmd.ExecuteScalar();
return resultSet;
}
catch (Exception ex)
{
throw new ArgumentException(ex.Message);
}
finally
{
cmd.Dispose();
cn.Close();
}
}
#endregion
#region Execute Sclar with SQL Parameter
public int ExecuteSclar(string spName, params object[] parametersValues)
{
try
{
Int32 resultSet;
cn = new SqlConnection(clsConnection.Getconnection());
cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandText = spName;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddRange(parametersValues);
cn.Open();
resultSet = (Int32)cmd.ExecuteScalar();
return resultSet;
}
catch (Exception ex)
{
throw new ArgumentException(ex.Message);
}
}
#endregion
}
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
namespace DataAccesLayer
{
public class clsConnection
{
public clsConnection()
{
//Constructor
}
#region for get Connection (SQL)
public static string Getconnection()
{
try
{
return ConfigurationManager.ConnectionStrings["ConStr"].ConnectionString.ToString();
}
catch (Exception )
{
throw;
}
}
#endregion
}
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using BE;
using DataAccesLayer;
namespace BO
{
public class DeptNameBO
{
DataAccessLayer objdal = new DataAccessLayer();
DeptNameBE deptnamebe = new DeptNameBE();
DataSet ds = null;
SqlParameter[] parameter = null;
public DataSet GetDeptNames()
{
try
{
ds = new DataSet();
ds = objdal.ExecuteDataSet("Usp_GetDeptNames");
return ds;
}
catch (Exception ex)
{
return ds;
throw new ArgumentException(ex.Message);
}
}
public DataSet GetMgrNames()
{
try
{
ds = new DataSet();
ds = objdal.ExecuteDataSet("Usp_GetMgrNames");
return ds;
}
catch (Exception ex)
{
return ds;
throw new ArgumentException(ex.Message);
}
}
public int InsertDepartment(ref DeptNameBE deptnamebe)
{
try
{
parameter = new SqlParameter[4];
parameter[0] = new SqlParameter("@DeptID", deptnamebe.DeptID);
parameter[1] = new SqlParameter("@DeptName", deptnamebe.DepartmentName);
parameter[2] = new SqlParameter("@MgrID", deptnamebe.ManagerID);
parameter[3] = new SqlParameter("@Status", deptnamebe.IsActive);
// parameter[4] = new SqlParameter("@ModifiedDate", deptnamebe.Modifieddatetime);
objdal.ExecuteNonQuery("Usp_SetDepartments", parameter);
return 0;
}
catch (Exception ex)
{
return 0;
throw new ArgumentException(ex.Message);
}
}
public int DeleteDepartment(ref DeptNameBE deptnamebe)
{
try
{
parameter = new SqlParameter[2];
parameter[0] = new SqlParameter("@DeptID", deptnamebe.DeptID);
parameter[1] = new SqlParameter("@Status", deptnamebe.IsActive);
objdal.ExecuteNonQuery("Usp_SetDeptName", parameter);
return 0;
}
catch (Exception ex)
{
return 0;
throw new ArgumentException(ex.Message);
}
}
}
}
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 BE;
using BO;
namespace WebApplication1.UI
{
public partial class DepartmentMaster : System.Web.UI.Page
{
DeptNameBE deptbe = new DeptNameBE();
DeptNameBO deptbo = new DeptNameBO();
DataSet ds = new DataSet();
protected void Page_Load(object sender, EventArgs e)
{
try
{
if (!IsPostBack)
{
BindDeptNames();
BindMgrNames();
}
}
catch (Exception ex)
{
lblMsg.Text = ex.Message;
}
}
private void BindDeptNames()
{
try
{
ds = deptbo.GetDeptNames();
if (ds.Tables[0].Rows.Count > 0)
{
gdvDeptName.DataSource = ds;
gdvDeptName.DataBind();
ViewState["sort"] = ds;
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
ds.Dispose();
}
}
private void BindMgrNames()
{
try
{
ds = deptbo.GetMgrNames();
if (ds.Tables[0].Rows.Count > 0)
{
ddlMgrName.DataSource = ds;
ddlMgrName.DataTextField = "ManagerName";
ddlMgrName.DataValueField = "EmpID";
ddlMgrName.DataBind();
ddlMgrName.Items.Insert(0, new ListItem("--Select--", "0"));
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
ds.Dispose();
}
}
protected void btnSubmit_Click(object sender, EventArgs e)
{
lblMsg.Text = "";
try
{
if (hdnDeptID.Value == "")
{
deptbe.DepartmentName = txtDeptName.Text;
deptbe.ManagerID = Convert.ToInt32(ddlMgrName.SelectedValue);
deptbe.IsActive = Convert.ToInt32(chkActive.Checked);
//deptbe.Modifieddatetime = Convert.ToDateTime(txtModifiedDate.Text);
deptbo.InsertDepartment(ref deptbe);
lblMsg.Text = "Department Inserted Successfully";
}
if (hdnDeptID.Value != "")
{
deptbe.DeptID = Convert.ToInt32(hdnDeptID.Value);
deptbe.DepartmentName = txtDeptName.Text;
deptbe.ManagerID = Convert.ToInt32(ddlMgrName.SelectedValue);
deptbe.IsActive = Convert.ToInt32(chkActive.Checked);
//deptbe.Modifieddatetime = Convert.ToDateTime(txtModifiedDate.Text);
deptbo.InsertDepartment(ref deptbe);
lblMsg.Text = "Department Updated Successfully";
}
BindDeptNames();
hdnDeptID.Value = "";
}
catch (Exception ex)
{
lblMsg.Text = ex.Message;
}
}
protected void btnDelete_Click(object sender, EventArgs e)
{
lblMsg.Text = "";
try
{
deptbe.DeptID = Convert.ToInt32(hdnDeptID.Value);
deptbe.IsActive = Convert.ToInt32(chkActive.Checked);
deptbo.DeleteDepartment(ref deptbe);
lblMsg.Text = "Department Deleted Successfully";
BindDeptNames();
hdnDeptID.Value = "";
}
catch (Exception ex)
{
lblMsg.Text = ex.Message;
}
}
protected void gdvDeptName_SelectedIndexChanging(object sender, GridViewSelectEventArgs e)
{
}
protected void gdvDeptName_SelectedIndexChanged(object sender, EventArgs e)
{
}
protected void btnreset_Click(object sender, EventArgs e)
{
lblMsg.Text = "";
}
protected void gdvDeptName_Sorting(object sender, GridViewSortEventArgs e)
{
string sortExpression = e.SortExpression;
if (GridViewSortDirection == SortDirection.Ascending)
{
GridViewSortDirection = SortDirection.Descending;
SortGridView(sortExpression, DESCENDING);
}
else
{
GridViewSortDirection = SortDirection.Ascending;
SortGridView(sortExpression, ASCENDING);
}
}
private void SortGridView(string sortExpression, string direction)
{
// You can cache the DataTable for improving performance
//BindDeptNames();
ds = (DataSet )ViewState["sort"];
DataTable dt = ds.Tables[0];
DataView dv = new DataView(dt);
dv.Sort = sortExpression + direction;
gdvDeptName.DataSource = dv;
gdvDeptName.DataBind();
}
private const string ASCENDING = " ASC";
private const string DESCENDING = " DESC";
public SortDirection GridViewSortDirection
{
get
{
if (ViewState["sortDirection"] == null)
ViewState["sortDirection"] = SortDirection.Ascending;
return (SortDirection)ViewState["sortDirection"];
}
set
{
ViewState["sortDirection"] = value;
}
}
protected void gdvDeptName_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
gdvDeptName.PageIndex = e.NewPageIndex;
BindDeptNames();
}
}
}
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="DepartmentMaster.aspx.cs"
Inherits="WebApplication1.UI.DepartmentMaster" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<style type="text/css">
.buttonstyle
{
visibility: hidden;
}
</style>
<script language="javascript" type="text/javascript">
function BindingValues(DeptID, DepartmentName, IsActive, EmpID) {
//debugger;
document.getElementById("hdnDeptID").value = DeptID;
// document.getElementById("txtDeptID").value = DeptID;
document.getElementById("txtDeptName").value = DepartmentName;
var ddlmgrname = document.getElementById("ddlMgrName");
// var DRP = ManagerName.toUpperCase();
for (var count = 0; count < ddlmgrname.options.length; count++) {
if (ddlmgrname.options[count].value.toUpperCase() == EmpID) {
ddlmgrname.options[count].selected = true;
break;
}
}
if (IsActive == "Active") {
document.getElementById("chkActive").checked = true;
}
else {
document.getElementById("chkActive").checked = false;
}
document.getElementById('btnSubmit').value = "Update";
//var parts = Modifieddatetime.split(" ");
//var Date = parts[0];
//var minutes = parts[1];
//document.getElementById("txtModifiedDate").value = Modifieddatetime;
document.getElementById("btnDelete").style.visibility = "visible";
}
function Validations() {
if (document.getElementById("txtDeptName").value == "") {
alert("Please enter Department Name");
document.getElementById('txtDeptName').focus();
return false;
}
var DeptNamepat = /^[a-zA-Z0-9\s-]+$/;
if (document.getElementById('txtDeptName').value.search(DeptNamepat) == -1) {
alert('Enter txtDeptName AlphaNumerics only');
document.getElementById('txtDeptName').focus();
return false;
}
if (document.getElementById('ddlMgrName').selectedIndex == 0) {
alert('Please Select Manager Name')
document.getElementById("ddlMgrName").focus();
return false;
}
/* deptDate = document.getElementById('txtModifiedDate').value;
if (document.getElementById('btnSubmit').value == "Update" && deptDate == "") {
alert('Please Enter Date mm/dd/yyyy Format');
document.getElementById("txtModifiedDate").focus();
return false;
}*/
/* var Datepattern = "^(0?[1-9]|1[012])[-/]?(0?[1-9]|[12][0-9]|3[01])[-/]?(19[\\d]{2}|20[\\d]{2}|2100)$";
if (document.getElementById('btnSubmit').value == "Update") {
if (!deptDate.match(Datepattern)) {
alert('Date Must be in "YY/MM/DD" format');
document.getElementById('txtModifiedDate').focus();
return false;
}
}*/
}
function ClearFields() {
document.getElementById("ddlMgrName").options[0].selected = true;
//document.getElementById("txtDeptID").value = "";
document.getElementById("txtDeptName").value = "";
//document.getElementByID("lblMsg").innerHTML = "";
// document.getElementById("txtModifiedDate").value = "";
document.getElementById("chkActive").checked = false;
document.getElementById("hdnDeptID").value = "";
document.getElementById("btnSubmit").value = "Submit";
return true;
}
</script>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:ScriptManager ID="ScriptManager1" runat="server">
</asp:ScriptManager>
<asp:UpdatePanel runat="server" ID="UpdMsg">
<ContentTemplate>
<asp:Label runat="server" ID="lblMsg" Style="top: 35px; left: 307px; position: absolute;
height: 19px; width: 480px; text-align: center" ForeColor="#CC3300"></asp:Label>
</ContentTemplate>
</asp:UpdatePanel>
<div style="top: 73px; left: 497px; position: absolute; font-size: large; font-weight: bold;
color: #0000FF;">
<table>
<tr>
<td>
<asp:Label runat="server" ID="lblDeptName" Text="Department Master" Font-Bold="true"></asp:Label>
</td>
</tr>
</table>
</div>
<div align="right">
<table>
<tr>
<td>
<asp:HyperLink runat="server" ID="hlnkEmpMaster" Text="Employee Master"NavigateUrl="~/UI/EmployeeMaster.aspx"></asp:HyperLink>::
<asp:HyperLink runat="server" ID="hlnkSalMaster" Text="Salary Master"NavigateUrl="~/UI/SalaryMaster.aspx"></asp:HyperLink>
</td>
</tr>
</table>
</div>
<br />
<br />
<table border="1" style="border-color: #0000FF; top: 128px; left: 376px; position: absolute;
height: 56px; width: 35%;">
<%-- <tr>
<td>
DeptID
</td>
<td>
<asp:TextBox ID="txtDeptID" runat="server" Width="90%" ReadOnly="true"></asp:TextBox>
</td>
</tr>--%>
<tr>
<td>
Department Name
</td>
<td>
<asp:TextBox ID="txtDeptName" runat="server" Width="90%"></asp:TextBox>
</td>
</tr>
<tr>
<td>
Assign Manager
</td>
<td>
<asp:DropDownList ID="ddlMgrName" runat="server">
</asp:DropDownList>
</td>
</tr>
<tr>
<td>
Status
</td>
<td>
<asp:CheckBox ID="chkActive" runat="server" Text="Active" Checked="true" />
</td>
</tr>
<%-- <tr>
<td>
ModifiedDate
</td>
<td>
<asp:TextBox runat="server" ID="txtModifiedDate"></asp:TextBox>
</td>
</tr>--%>
<tr>
<td>
</td>
<td>
<asp:Button ID="btnSubmit" runat="server" Text="Submit" OnClick="btnSubmit_Click"
OnClientClick="return Validations();" />
<asp:Button ID="btnDelete" runat="server" Text="Delete" OnClick="btnDelete_Click"
CssClass="buttonstyle" />
<asp:Button runat="server" ID="btnreset" Text="Reset" OnClientClick="return ClearFields();"
OnClick="btnreset_Click" />
</td>
</tr>
<tr>
<td colspan="2" align="center">
<asp:HiddenField ID="hdnDeptID" runat="server" />
</td>
</tr>
</table>
<asp:UpdatePanel ID="UpddeptName" runat="server" UpdateMode="Conditional">
<ContentTemplate>
<asp:GridView ID="gdvDeptName" runat="server" CellPadding="4" Style="top: 314px;
left: 358px; position: absolute; height: 133px; width: 463px"AutoGenerateColumns="False"
ForeColor="#333333" GridLines="None"OnSelectedIndexChanging="gdvDeptName_SelectedIndexChanging"
OnSelectedIndexChanged="gdvDeptName_SelectedIndexChanged"OnSorting="gdvDeptName_Sorting"
AllowSorting="True" AllowPaging="true"OnPageIndexChanging="gdvDeptName_PageIndexChanging"
PageSize="5">
<EditRowStyle BackColor="#2461BF" />
<FooterStyle BackColor="#507CD1" ForeColor="White" Font-Bold="True" />
<HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<PagerStyle ForeColor="White" HorizontalAlign="Center" BackColor="#2461BF" />
<RowStyle BackColor="#EFF3FB" />
<SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
<SortedAscendingCellStyle BackColor="#F5F7FB" />
<SortedAscendingHeaderStyle BackColor="#6D95E1" />
<SortedDescendingCellStyle BackColor="#E9EBEF" />
<SortedDescendingHeaderStyle BackColor="#4870BE" />
<AlternatingRowStyle BackColor="White" />
<Columns>
<asp:BoundField HeaderText="DeptID" DataField="DeptID" ItemStyle-HorizontalAlign="Center"
SortExpression="DeptID" />
<asp:BoundField HeaderText="DepartmentName" DataField="DepartmentName" ItemStyle-HorizontalAlign="Center"
SortExpression="DepartmentName" />
<asp:BoundField HeaderText="Status" DataField="Status" ItemStyle-HorizontalAlign="Center" />
<%-- <asp:BoundField HeaderText="Modifieddatetime" DataField="Modifieddatetime" ItemStyle-HorizontalAlign="Center"
DataFormatString="{0:d}" />--%>
<%-- <asp:BoundField HeaderText="EmpID" DataField="EmpID" ItemStyle-HorizontalAlign="Center" />--%>
<asp:TemplateField HeaderText="Modify">
<ItemTemplate>
<img id="Img1" alt="Modify" class="modify" style="border: 0px;" height="15"src="../Images/modify_New.jpg"
onclick="return BindingValues('<%#DataBinder.Eval(Container.DataItem,"DeptID")%>',
'<%#DataBinder.Eval(Container.DataItem,"DepartmentName")%>',
'<%#DataBinder.Eval(Container.DataItem,"Status")%>',
<%-- '<%#DataBinder.Eval(Container.DataItem,"Modifieddatetime","{0:d}")%>'--%>
'<%#DataBinder.Eval(Container.DataItem,"EmpID")%>'
);" />
</ItemTemplate>
<HeaderStyle HorizontalAlign="Center" />
<ItemStyle HorizontalAlign="center" />
</asp:TemplateField>
</Columns>
</asp:GridView>
</ContentTemplate>
<Triggers>
<asp:AsyncPostBackTrigger ControlID="btnSubmit" EventName="Click"></asp:AsyncPostBackTrigger>
<asp:AsyncPostBackTrigger ControlID="btnDelete" EventName="Click"></asp:AsyncPostBackTrigger>
<asp:AsyncPostBackTrigger ControlID="gdvDeptName" EventName="Sorting"></asp:AsyncPostBackTrigger>
<asp:AsyncPostBackTrigger ControlID="gdvDeptName" EventName="PageIndexChanging">
</asp:AsyncPostBackTrigger>
</Triggers>
</asp:UpdatePanel>
</div>
</form>
</body>
</html>