In Part 1 of this article Series I have created database
structure also inserted some Record and create MVC 4 web application and also
describe about what is Model, View, controller. Now in this article I am going
to describe about Data Layer and Business Layer and also about Model (how to
create model as entities). So let us create another project in my CRUDOPERATION
Solution. Because I want to keep
DATALAYER and BUSSINESSLAYER in Separate project. So Let use first of all
create DATALAYER.
Step 4
Right click on
Solution Explorer => Add =>New Item => Class Library =>Give
Meaningful name (here DATALAYER)
Step 5
Now Write some simple database Interaction method on DATALAYER
Class.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
namespace DATALAYER
{
public class MYDAL
{
SqlConnection con;
SqlCommand cmd;
SqlDataAdapter da;
DataSet ds;
public MYDAL()
{
con = new SqlConnection(ConfigurationManager.ConnectionStrings["Con"].ConnectionString);
}
public DataSet GetDetails(string query, SqlParameter spparmeter)
{
if (con.State == ConnectionState.Closed)
con.Open();
cmd = new SqlCommand(query, con);
cmd.CommandType = CommandType.StoredProcedure;
if (spparmeter != null)
{
cmd.Parameters.Add(spparmeter);
}
da = new SqlDataAdapter(cmd);
ds = new DataSet();
da.Fill(ds);
return ds;
}
public int DMLOPERATION(string query, Dictionary<string, string> dic)
{
if (con.State == ConnectionState.Closed)
con.Open();
cmd = new SqlCommand(query, con);
cmd.CommandType = CommandType.StoredProcedure;
foreach (var keyvalue in dic)
{
string key = keyvalue.Key;
string value = keyvalue.Value;
cmd.Parameters.AddWithValue(key, value);
}
return cmd.ExecuteNonQuery();
}
}
}
Step 6
Now here I have Create a Separate folder Name ( Entities ) into Business Layer project. Because I am going to create Model inside this entities folder.
Since In this
article I am going to Work on two table EmployeeDetails
and DepartmentDetails
So Let us Create first of all Two Model Within Entites folder.
Step 7
RightClick On
Entities folder => Add =>Class => Name (EmployeeDetails)
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.ComponentModel.DataAnnotations;
namespace BUSSINESSLAYER.Entites
{
public class EmployeeDetails
{
private DepartmentDetails department;
public EmployeeDetails()
{
department = new DepartmentDetails();
}
[Display(Name="Emp_Id")]
[Required]
public int id
{
get;
set;
}
[Display (Name="Emp_Name")]
[Required(ErrorMessage="Employee
Name is Required")]
public string Name
{
get;
set;
}
[Display(Name="Emp_salary")]
[Required(ErrorMessage="employee
salary is required")]
public int salary
{
get;
set;
}
[Display(Name="Emp_department")]
[Required(ErrorMessage="Employee
department is required")]
public DepartmentDetails employeeDepartMent
{
get {return department;}
set { department = value;}
}
}
}
Step 8
DepartmentDetails.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.ComponentModel.DataAnnotations;
namespace BUSSINESSLAYER.Entites
{
public class DepartmentDetails
{
[Display(Name = "Dept_Id")]
public int department_id
{
get;
set;
}
[Display(Name = "Dept_Name")]
[Required(ErrorMessage = "Department
Name is required")]
public string DepartMent_Name
{
get;
set;
}
[Display(Name = "Dept_Loc")]
[Required(ErrorMessage = "Department
Location is required")]
public string DepartMent_Location
{
get;
set;
}
}
}
Step 9
Now I am going to create another class library project Named
Business Layer. Where I have written some Method to perform operation on
database.
Create Some Method into
Business Layer project to access the database record.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using BUSSINESSLAYER.Entites;
using DATALAYER;
using System.Data;
using System.Data.SqlClient;
namespace BUSSINESSLAYER
{
public class MYBAL
{
public List<EmployeeDetails> Getemployee(string query, SqlParameter sqlparameter)
{
MYDAL mydal = new MYDAL();
DataSet ds = mydal.GetDetails(query, sqlparameter);
List<EmployeeDetails> listemployee = new List<EmployeeDetails>();
EmployeeDetails objemployee;
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
objemployee = new EmployeeDetails();
objemployee.id = Convert.ToInt32(ds.Tables[0].Rows[i]["Id"].ToString());
objemployee.Name =
ds.Tables[0].Rows[i]["Name"].ToString();
objemployee.salary = Convert.ToInt32(ds.Tables[0].Rows[i]["salary"].ToString());
objemployee.employeeDepartMent.department_id = Convert.ToInt32(ds.Tables[0].Rows[i]["department_id"].ToString());
objemployee.employeeDepartMent.DepartMent_Name = ds.Tables[0].Rows[i]["Department_Name"].ToString();
objemployee.employeeDepartMent.DepartMent_Location
= ds.Tables[0].Rows[i]["Department_Locatiom"].ToString();
listemployee.Add(objemployee);
}
return listemployee;
}
//-------------------------------------------------------------------------------------------
public EmployeeDetails GetSingleemployee(string query, SqlParameter sqlparameter)
{
MYDAL mydal = new MYDAL();
DataSet ds = mydal.GetDetails(query, sqlparameter);
EmployeeDetails objemployee = new EmployeeDetails();
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
objemployee.id = Convert.ToInt32(ds.Tables[0].Rows[i]["Id"].ToString());
objemployee.Name =
ds.Tables[0].Rows[i]["Name"].ToString();
objemployee.salary = Convert.ToInt32(ds.Tables[0].Rows[i]["salary"].ToString());
objemployee.employeeDepartMent.department_id = Convert.ToInt32(ds.Tables[0].Rows[i]["department_id"]);
objemployee.employeeDepartMent.DepartMent_Name = ds.Tables[0].Rows[i]["Department_Name"].ToString();
objemployee.employeeDepartMent.DepartMent_Location = ds.Tables[0].Rows[i]["Department_Locatiom"].ToString();
}
return objemployee;
}
public List<DepartmentDetails> GetDepartment(string query, SqlParameter spparameter)
{
MYDAL obj = new MYDAL();
DataSet ds = obj.GetDetails(query, spparameter);
List<DepartmentDetails> listdepartment = new List<DepartmentDetails>();
DepartmentDetails objdepartment;
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
objdepartment = new DepartmentDetails();
objdepartment.department_id = Convert.ToInt32(ds.Tables[0].Rows[i]["department_id"].ToString());
objdepartment.DepartMent_Name =
ds.Tables[0].Rows[i]["Department_Name"].ToString();
listdepartment.Add(objdepartment);
}
return listdepartment;
}
public int AddNewEmployee(string query, EmployeeDetails employee)
{
MYDAL obj = new MYDAL();
Dictionary<string, string> dic = new Dictionary<string, string>();
dic.Add("@name",
employee.Name);
dic.Add("@salary",
employee.salary.ToString());
dic.Add("@department_id", employee.employeeDepartMent.department_id.ToString());
return obj.DMLOPERATION(query, dic);
}
public int UpdateEmployee(string query, EmployeeDetails employee)
{
MYDAL obj = new MYDAL();
Dictionary<string, string> dic = new Dictionary<string, string>();
dic.Add("@id",
employee.id.ToString());
dic.Add("@name",
employee.Name);
dic.Add("@salary",
employee.salary.ToString());
dic.Add("@departmentid",
employee.employeeDepartMent.department_id.ToString());
return obj.DMLOPERATION(query, dic);
}
public int DeleteEmployee(string query, EmployeeDetails employee)
{
MYDAL obj = new MYDAL();
Dictionary<string, string> dic = new Dictionary<string, string>();
dic.Add("@id",
employee.id.ToString());
return obj.DMLOPERATION(query, dic);
}
}
Author- Sayta Prakash
