Post
Ask Question

CRUD Operation Using N-tier Architecture and Display in ASP.NET Gridview

Step 1:  Open Sqlserver,

a. Create database
 
create database sample.

b.Create table 
  1. create table tblEmp(Eid int identity(1,1) primary key,Ename nvarchar(50),Designation nvarchar(100),Eimagename varchar(50),Imagedata varbinary(max),Salary nvarchar(50),Email nvarchar(100),Mobileno nvarchar(10),Department nvarchar(100),ManagerPost nvarchar(100),Empno nvarchar(50))  
c. Create storedprocedure 
  1. create procedure spInsert_tblEmp(@Ename nvarchar(50), @Designation nvarchar(100), @Eimage varchar(50), @Imagedata varbinary(max), @Salary nvarchar(50), @Email nvarchar(100), @MobileNo nvarchar(10), @Department nvarchar(100), @ManagerPost nvarchar(100), @Empno nvarchar(50))  
  2. as  
  3. begin  
  4. insert into tblEmp(Ename, Designation, Eimagename, Imagedata, Salary, Email, Mobileno, Department, ManagerPost, Empno) values(@Ename, @Designation, @Eimage, @Imagedata, @Salary, @Email, @MobileNo, @Department, @ManagerPost, @Empno)  
  5. end  
  6. create procedure spUpdate_tblEmp(@Eid int, @Ename nvarchar(50), @Designation nvarchar(100), @Eimage varchar(50), @Imagedata varbinary(max), @Salary nvarchar(50), @Email nvarchar(100), @MobileNo nvarchar(10), @Department nvarchar(100), @ManagerPost nvarchar(100), @Empno nvarchar(50))  
  7. as  
  8. begin  
  9. Update tblEmp set Ename = @Ename, Designation = @Designation, Eimagename = @Eimage, Imagedata = @Imagedata, Salary = @Salary, Email = @Email, Mobileno = @MobileNo, Department = @Department, ManagerPost = @ManagerPost, Empno = @Empno where Eid = @Eid  
  10. end  
  11. create procedure spDelete_tblEmp(@Eid int)  
  12. as  
  13. begin  
  14. Delete from tblEmp where Eid = @Eid  
  15. end  
  16. create procedure spdisplay_tblEmp  
  17. as  
  18. begin  
  19. select * from tblemp  
  20. end  
  21. Create procedure spdisplay_tblEmpdata(@Eid int)  
  22. as  
  23. begin  
  24. select * from tblEmp where Eid = @Eid  
  25. end // This is just a sample script. Paste your real code (javascript or HTML) here.  
  26.   
  27. if ('this_is' == /an_example/)  
  28. {  
  29.     of_beautifier();  
  30. } else {  
  31.     var a = b ? (c % d) : e[f];  
  32. } 
Step 2: Open visual studio->select project->select Visual Studio solutions,

 
 
Step 3:

Right click on blank solution ->add->select two class library template ->name it as DAL(data access layer) and BAL(business access layer) ->show below folder structre,
 
 
 
Step 4:

In DAL class1.cs file automatically generates that file; delete and right click on DAL class library select new class file
name it as ConnectionFactory.cs. 
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Data;  
  4. using System.Data.SqlClient;  
  5. using System.Linq;  
  6. using System.Text;  
  7. using System.Threading.Tasks;  
  8. using System.Configuration;  
  9. namespace DAL  
  10. {  
  11.     public class ConnectionFactory  
  12.     {  
  13.         // SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConStr"].ConnectionString);  
  14.         SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=Sample;Integrated Security=True");  
  15.         public bool InsertEmpInfo(string cmdtext, SqlParameter[] p)  
  16.         {  
  17.             try  
  18.             {  
  19.                 SqlCommand cmd = new SqlCommand();  
  20.                 cmd.CommandType = CommandType.StoredProcedure;  
  21.                 cmd.CommandText = cmdtext;  
  22.                 cmd.Connection = con;  
  23.                 foreach(object obj in p)  
  24.                 {  
  25.                     cmd.Parameters.Add(obj);  
  26.                 }  
  27.                 con.Open();  
  28.                 int res = cmd.ExecuteNonQuery();  
  29.                 con.Close();  
  30.                 if (res != 0)  
  31.                 {  
  32.                     return false;  
  33.                 } else  
  34.                     return true;  
  35.             } catch  
  36.             {  
  37.                 throw;  
  38.             }  
  39.         }  
  40.         public DataSet DisplayEmpData(string cmdtext)  
  41.             {  
  42.             try  
  43.             {  
  44.                 con.Open();  
  45.                 SqlCommand cmd = new SqlCommand(cmdtext, con);  
  46.                 SqlDataAdapter da = new SqlDataAdapter(cmd);  
  47.                 cmd.CommandType = CommandType.StoredProcedure;  
  48.                 DataSet ds = new DataSet();  
  49.                 da.Fill(ds);  
  50.                 return ds;  
  51.             } catch  
  52.             {  
  53.                 throw;  
  54.             }  
  55.         }  
  56.         public DataSet DisplayEmpData1(string cmdtext, SqlParameter[] p)   
  57.             {  
  58.             try  
  59.             {  
  60.                 con.Open();  
  61.                 SqlCommand cmd = new SqlCommand(cmdtext, con);  
  62.                 SqlDataAdapter da = new SqlDataAdapter(cmd);  
  63.                 cmd.CommandType = CommandType.StoredProcedure;  
  64.                 foreach(object obj in p)  
  65.                 {  
  66.                     cmd.Parameters.Add(obj);  
  67.                 }  
  68.                 DataSet ds = new DataSet();  
  69.                 da.Fill(ds);  
  70.                 return ds;  
  71.             } catch  
  72.             {  
  73.                 throw;  
  74.             }  
  75.         }  
  76.     }  
  77. }  
  78. Build DAL library.  
Step 5:
In BAL class1.cs file automatically generates that file; delete and right click on BAL class library select new class file
name it as BusinessEntities.cs and BusinessLogic.cs.
 
BusinessEntities.cs  
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Text;  
  5. using System.Threading.Tasks;  
  6. namespace BAL.BE   
  7. {  
  8.     public class BusinessEntities   
  9.     {  
  10.         public int Eid  
  11.         {  
  12.             get;  
  13.             set;  
  14.         }  
  15.         public string Ename  
  16.         {  
  17.             get;  
  18.             set;  
  19.         }  
  20.         public string Designation  
  21.         {  
  22.             get;  
  23.             set;  
  24.         }  
  25.         public string Eimagename   
  26.         {  
  27.             get;  
  28.             set;  
  29.         }  
  30.         public byte[] Imagedata   
  31.         {  
  32.             get;  
  33.             set;  
  34.         }  
  35.         public string Salary  
  36.         {  
  37.             get;  
  38.             set;  
  39.         }  
  40.         public string Email  
  41.         {  
  42.             get;  
  43.             set;  
  44.         }  
  45.         public string MobileNo  
  46.         {  
  47.             get;  
  48.             set;  
  49.         }  
  50.         public string Department  
  51.         {  
  52.             get;  
  53.             set;  
  54.         }  
  55.         public string ManagerPost  
  56.         {  
  57.             get;  
  58.             set;  
  59.         }  
  60.         public string Empno  
  61.         {  
  62.             get;  
  63.             set;  
  64.         }  
  65.     }  
  66. }  
BusinessLogic.cs 
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Text;  
  5. using System.Threading.Tasks;  
  6. using DAL;  
  7. using System.Data.SqlClient;  
  8. using BAL.BE;  
  9. using System.Data;  
  10. namespace BAL.BL  
  11. {  
  12.     public class BusinessLogic  
  13.     {  
  14.         public bool EmpInfoinsert(BusinessEntities becat)  
  15.       {  
  16.             try  
  17.             {  
  18.                 ConnectionFactory cf = new ConnectionFactory();  
  19.                 SqlParameter[] sp = new SqlParameter[10];  
  20.                 sp[0] = new SqlParameter("@Ename", becat.Ename);  
  21.                 sp[1] = new SqlParameter("@Designation", becat.Designation);  
  22.                 sp[2] = new SqlParameter("@Eimage", becat.Eimagename);  
  23.                 sp[3] = new SqlParameter("@Imagedata", becat.Imagedata);  
  24.                 sp[4] = new SqlParameter("@Salary", becat.Salary);  
  25.                 sp[5] = new SqlParameter("@Email", becat.Email);  
  26.                 sp[6] = new SqlParameter("@MobileNo", becat.MobileNo);  
  27.                 sp[7] = new SqlParameter("@Department", becat.Department);  
  28.                 sp[8] = new SqlParameter("@ManagerPost", becat.ManagerPost);  
  29.                 sp[9] = new SqlParameter("@Empno", becat.Empno);  
  30.                 return cf.InsertEmpInfo("spInsert_tblEmp", sp);  
  31.             } catch  
  32.             {  
  33.                 throw;  
  34.             }  
  35.         }  
  36.         public bool EmpInfoupdate(BusinessEntities becat)  
  37.             {  
  38.             try {  
  39.                 ConnectionFactory cf = new ConnectionFactory();  
  40.                 SqlParameter[] sp = new SqlParameter[11];  
  41.                 sp[0] = new SqlParameter("@Eid", becat.Eid);  
  42.                 sp[1] = new SqlParameter("@Ename", becat.Ename);  
  43.                 sp[2] = new SqlParameter("@Designation", becat.Designation);  
  44.                 sp[3] = new SqlParameter("@Eimage", becat.Eimagename);  
  45.                 sp[4] = new SqlParameter("@Imagedata", becat.Imagedata);  
  46.                 sp[5] = new SqlParameter("@Salary", becat.Salary);  
  47.                 sp[6] = new SqlParameter("@Email", becat.Email);  
  48.                 sp[7] = new SqlParameter("@MobileNo", becat.MobileNo);  
  49.                 sp[8] = new SqlParameter("@Department", becat.Department);  
  50.                 sp[9] = new SqlParameter("@ManagerPost", becat.ManagerPost);  
  51.                 sp[10] = new SqlParameter("@Empno", becat.Empno);  
  52.                 return cf.InsertEmpInfo("spUpdate_tblEmp", sp);  
  53.             } catch  
  54.             {  
  55.                 throw;  
  56.             }  
  57.         }  
  58.         public bool EmpInfoDelete(int Eid)   
  59.             {  
  60.             try  
  61.             {  
  62.                 ConnectionFactory cf = new ConnectionFactory();  
  63.                 SqlParameter[] sp = new SqlParameter[1];  
  64.                 sp[0] = new SqlParameter("@Eid", Eid);  
  65.                 return cf.InsertEmpInfo("spDelete_tblEmp", sp);  
  66.             } catch  
  67.             {  
  68.                 throw;  
  69.             }  
  70.         }  
  71.         public DataSet DisplayEmp()   
  72.             {  
  73.             ConnectionFactory cf = new ConnectionFactory();  
  74.             DataSet ds = cf.DisplayEmpData("spdisplay_tblEmp");  
  75.             return ds;  
  76.         }  
  77.         public DataSet DisplayEmpInfo(int Eid)  
  78.         {  
  79.             try  
  80.             {  
  81.                 ConnectionFactory cf = new ConnectionFactory();  
  82.                 SqlParameter[] sp = new SqlParameter[1];  
  83.                 sp[0] = new SqlParameter("@Eid", Eid);  
  84.                 DataSet ds = cf.DisplayEmpData1("spdisplay_tblEmpdata", sp);  
  85.                 return ds;  
  86.             } catch  
  87.             {  
  88.                 throw;  
  89.             }  
  90.         }  
  91.     }  
  92. }  
Build the BAL library.
 
Step 6: Right click on blank solution select website name it as "DemoRegister ".
 
select default page layout is  shown below, 
Source code for default.aspx.cs  
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Web;  
  5. using System.Web.UI;  
  6. using System.Web.UI.WebControls;  
  7. using DAL;  
  8. using BAL.BE;  
  9. using BAL.BL;  
  10. using System.Data;  
  11. using System.IO;  
  12. public partial class _Default: System.Web.UI.Page  
  13. {  
  14.     protected void Page_Load(object sender, EventArgs e)  
  15.     {  
  16.         BindGridViewData();  
  17.         txteid.Visible = false;  
  18.     }  
  19.     public void BindGridViewData()  
  20.         {  
  21.             BusinessLogic beobj = new BusinessLogic();  
  22.             DataSet ds = beobj.DisplayEmp();  
  23.             GridView1.DataSource = ds;  
  24.             GridView1.DataBind();  
  25.         }  
  26.         //insert button code  
  27.     protected void btnsubmit_Click(object sender, EventArgs e)  
  28.     {  
  29.             if (fileUpload1.HasFile)  
  30.             {  
  31.                 int length = fileUpload1.PostedFile.ContentLength;  
  32.                 byte[] imgbyte = new byte[length];  
  33.                 HttpPostedFile img = fileUpload1.PostedFile;  
  34.                 //set the binary data  
  35.                 img.InputStream.Read(imgbyte, 0, length);  
  36.                 string filename = Path.GetFileName(fileUpload1.PostedFile.FileName);  
  37.                 BusinessEntities be = new BusinessEntities();  
  38.                 BusinessLogic bl = new BusinessLogic();  
  39.                 be.Ename = txtename.Text;  
  40.                 be.Designation = txtdesg.Text;  
  41.                 //be.Eimage=txtimage.  
  42.                 be.Eimagename = filename;  
  43.                 be.Imagedata = imgbyte;  
  44.                 be.Salary = txtsalary.Text;  
  45.                 be.Email = txtEmail.Text;  
  46.                 be.MobileNo = txtmob.Text;  
  47.                 be.Department = txtdept.Text;  
  48.                 be.ManagerPost = txtpost.Text;  
  49.                 be.Empno = txtempno.Text;  
  50.                 bool result = bl.EmpInfoinsert(be);  
  51.                 lblmsg.Text = "Record saved successfully.....";  
  52.                 BindGridViewData();  
  53.                 txteid.Text = txtename.Text = txtdept.Text = txtdesg.Text = txtempno.Text = txtsalary.Text = txtpost.Text = txtmob.Text = txtEmail.Text = "";  
  54.             }  
  55.         }  
  56.         //update button code  
  57.     protected void btnupdate_Click(object sender, EventArgs e)  
  58.     {  
  59.             txteid.Visible = true;  
  60.             //BindGridViewData1();  
  61.             if (fileUpload1.HasFile)  
  62.             {  
  63.                 int length = fileUpload1.PostedFile.ContentLength;  
  64.                 byte[] imgbyte = new byte[length];  
  65.                 HttpPostedFile img = fileUpload1.PostedFile;  
  66.                 //set the binary data  
  67.                 img.InputStream.Read(imgbyte, 0, length);  
  68.                 string filename = Path.GetFileName(fileUpload1.PostedFile.FileName);  
  69.                 BusinessEntities be = new BusinessEntities();  
  70.                 BusinessLogic bl = new BusinessLogic();  
  71.                 be.Eid = int.Parse(txteid.Text);  
  72.                 be.Ename = txtename.Text;  
  73.                 be.Designation = txtdesg.Text;  
  74.                 //be.Eimage=txtimage.  
  75.                 be.Eimagename = filename;  
  76.                 be.Imagedata = imgbyte;  
  77.                 be.Salary = txtsalary.Text;  
  78.                 be.Email = txtEmail.Text;  
  79.                 be.MobileNo = txtmob.Text;  
  80.                 be.Department = txtdept.Text;  
  81.                 be.ManagerPost = txtpost.Text;  
  82.                 be.Empno = txtempno.Text;  
  83.                 bool result = bl.EmpInfoupdate(be);  
  84.                 lblmsg.Text = "Record updated successfully.....";  
  85.                 BindGridViewData();  
  86.                 txteid.Text = txtename.Text = txtdept.Text = txtdesg.Text = txtempno.Text = txtsalary.Text = txtpost.Text = txtmob.Text = txtEmail.Text = "";  
  87.                 txteid.Visible = false;  
  88.             }  
  89.         }  
  90.         //resete button code  
  91.     protected void btnresete_Click(object sender, EventArgs e)  
  92.     {  
  93.             txteid.Text = txtename.Text = txtdept.Text = txtdesg.Text = txtempno.Text = txtsalary.Text = txtpost.Text = txtmob.Text = txtEmail.Text = "";  
  94.         }  
  95.         //display on particular record in textbox code  
  96.     protected void Button1_Click(object sender, EventArgs e)   
  97.     {  
  98.             txteid.Visible = true;  
  99.             Button btn = (Button) sender;  
  100.             string CommandName = btn.CommandName;  
  101.             string CommandArgument = btn.CommandArgument;  
  102.             int abcd = int.Parse(CommandArgument);  
  103.             BindGridViewData1(abcd);  
  104.         }  
  105.         //display record code  
  106.     private void BindGridViewData1(int abcd)  
  107.     {  
  108.             BusinessLogic beobj = new BusinessLogic();  
  109.             DataSet ds = beobj.DisplayEmpInfo(abcd);  
  110.             int emid = (int) ds.Tables[0].Rows[0][0];  
  111.             txteid.Text = emid.ToString();  
  112.             txtename.Text = ds.Tables[0].Rows[0][1].ToString();  
  113.             txtdesg.Text = ds.Tables[0].Rows[0][2].ToString();  
  114.             txtsalary.Text = ds.Tables[0].Rows[0][5].ToString();  
  115.             txtEmail.Text = ds.Tables[0].Rows[0][6].ToString();  
  116.             txtmob.Text = ds.Tables[0].Rows[0][7].ToString();  
  117.             txtdept.Text = ds.Tables[0].Rows[0][8].ToString();  
  118.             txtpost.Text = ds.Tables[0].Rows[0][9].ToString();  
  119.             txtempno.Text = ds.Tables[0].Rows[0][10].ToString();  
  120.         }  
  121.         //In gridview delete button code  
  122.     protected void Button2_Click(object sender, EventArgs e)   
  123.     {  
  124.         txteid.Visible = true;  
  125.         Button btn = (Button) sender;  
  126.         string CommandName = btn.CommandName;  
  127.         string CommandArgument = btn.CommandArgument;  
  128.         int xyz = int.Parse(CommandArgument);  
  129.         BusinessEntities be = new BusinessEntities();  
  130.         BusinessLogic bl = new BusinessLogic();  
  131.         bool res = bl.EmpInfoDelete(xyz);  
  132.     }  
  133. }   
Build project and Run.