Three Tier Architecture -- Windows Application

Three Tier Architecture -- Windows Application

  Introduction:

                   This Article describes How to build C Sharp Application using 3-Architecture.

Three Tier Architecture:

      Application /Presentation Layer >>Business Logic Layer >> Data Access Layer.

First, we start with Data Access Layer.

Data Access Layer:

                    The following information used in the Data Access Layer:

Queries.

Connection String.

Class Name: DLLoginMaster.

Table Name: tbl_LoginMaster

Table Description:  This table contains login Master Data.

Table Fields,Description and Field Type:            

   Id – This is a Primary Key of this Table.Login Id Stores here.It is Auto Increment.[int]

  Username – Login User name stores here.[Varchar(50)]

  Password – Password of Username.Password stores here.[Varchar(50)].

  FirstName—First Name of the User. First Name stores here. .[Varchar(50)]

  LastName – Last Name of the User. Last Name stores here. .[Varchar(50)]

  Active – user may alive or not .[bit].

Now, we go to Coding Section .

Create One Class file under DataLayer Folder.before, we create three folders in the your Project.

1)ApplicationLayer.2)BusinessLayer.3)DataLayer.

Step by Step:

Call SQLClient namespace in the Code page.

TextBox1 : txtUserNamet TextBox2 : txtPasswordt TextBox3 : txtFirstNamet TextBox4 : txtLastName Check Box: ChkActive Button1:Add Button2:Update Button3:Delete DataGridView:DGVFillData

using System.Data.SqlClient; private SqlConnection Con; // Set your Server Name,DatabaseName,User Name and Password. private string ConStr = "Server=;Database=;User Id=;Password="; Copy the Code and Paste in your Class File public DLLoginMaster () { } public bool InsertintoTable(BLLoginMaster BLUserMas) { string query = "insert into tbl_LoginMaster(Username,Password,FirstName,LastName,Active) values('"+ BLUserMas.UserName +"','"+ BLUserMas.Password+"','"+ BLUserMas.FirstName +"','"+ BLUserMas.LastName+"','"+BLUserMas.Active +"')"; Con = new SqlConnection(ConStr); Con.Open(); SqlCommand cmd = new SqlCommand(query, Con); try { cmd.ExecuteNonQuery(); Con.Close(); result = true; } catch (Exception ex) { Program.WriteLog(ex.Message, ex.StackTrace); result = false; } finally { cmd.Dispose(); Con.Close(); Con.Dispose(); } return result; } public bool UpdateintoTable(BLLoginMaster BLUserMas) { string query = "update tbl_LoginMaster set Username='" + BLUserMas.UserName + "',Password='" + BLUserMas.Password + "',FirstName ='" + BLUserMas.FirstName + "',LastName='" + BLUserMas.LastName + "',Active='" + BLUserMas.Active + "' where id='" + BLUserMas.UserId + "' "; Con = new SqlConnection(ConStr); Con.Open(); SqlCommand cmd = new SqlCommand(query, Con); try { cmd.ExecuteNonQuery(); Con.Close(); result = true; } catch (Exception ex) { Program.WriteLog(ex.Message, ex.StackTrace); result = false; } finally { cmd.Dispose(); Con.Close(); Con.Dispose(); } return result; } public bool DeletefromTable(BLLoginMaster BLUserMas) { string query = "delete from tbl_LoginMaster where id='" + BLUserMas.UserId + "' "; Con = new SqlConnection(ConStr); Con.Open(); SqlCommand cmd = new SqlCommand(query, Con); try { cmd.ExecuteNonQuery(); Con.Close(); result = true; } catch (Exception ex) { Program.WriteLog(ex.Message, ex.StackTrace); result = false; } finally { cmd.Dispose(); Con.Close(); Con.Dispose(); } return result; } public DataSet FillData() { DataSet ds = null; try { string query = "Select distinct Username as [User Name],Password,FirstName as [First Name],LastName as [Last Name],Active from tbl_LoginMaster"; Con = new SqlConnection(ConStr); Con.Open(); SqlDataAdapter da = new SqlDataAdapter(query, Con); ds = new DataSet(); da.Fill(ds); Con.Close(); } catch (Exception e) { Program.WriteLog(e.Message, e.StackTrace); } return ds; } public string GetUserId(string str) { string query = "Select distinct id from tbl_LoginMaster where Password='" + str + "' "; Con = new SqlConnection(ConStr); Con.Open(); SqlCommand cmd = new SqlCommand(query, Con); try { SqlDataAdapter adapter = new SqlDataAdapter(cmd); DataTable dt = new DataTable(); adapter.Fill(dt); if (dt.Rows.Count > 0) { DataColumn col = dt.Columns["id"]; foreach (DataRow row in dt.Rows) { id = row[col].ToString(); } } Con.Close(); return id; } catch (Exception ex) { Program.WriteLog(ex.Message, ex.StackTrace); return id; } finally { cmd.Dispose(); Con.Close(); Con.Dispose(); } } bool returnvalue; public bool CheckPassowrd(string Passwrd) { string query = "Select distinct password from userMaster where password='" + Passwrd + "'"; Con = new SqlConnection(ConStr); Con.Open(); SqlCommand cmd = new SqlCommand(query, Con); SqlDataAdapter adapter = new SqlDataAdapter(cmd); DataTable dt = new DataTable(); adapter.Fill(dt); if (dt.Rows.Count > 0) { returnvalue = true; } Con.Close(); return returnvalue; }

Here , We declare BLLoginMaster.Why we Declare BLLoginMaster?.Passing Data from BLLoginMaster to DLLoginMaster. Business Layer : The following information used in the Business Layer: Create Methods/Functions Create Properties. Class Name: BLLoginMaster

using System.Data; using System.Windows.Forms; private static int _UserId; private static string _UserName; private static string _Password; private static string _Firstname; private static string _LastName; private static bool _Active; private DLLoginMaster DLUser; /// Property User Name /// public int UserId { get { return _UserId; } set { _UserId = value; } } /// /// Property User Name /// public string UserName { get { return _UserName; } set { _UserName = value; } } /// /// Property Password /// public string Password { get { return _Password; } set { _Password = value; } } /// /// Property FirstName /// public string FirstName { get { return _Firstname; } set { _Firstname = value; } } /// /// Property LastName /// public string LastName { get { return _LastName; } set { _LastName = value; } } /// /// Property Active /// public bool Active { get { return _Active; } set { _Active = value; } } public bool SaveRecord() { bool Output; DLUser = new DLLoginMaster (); Output = DLUser.InsertintoTable(this); return Output; } public bool UpdateRecord() { bool Output; DLUser = new DLLoginMaster (); Output = DLUser.UpdateintoTable(this); return Output; } public bool DeleteRecord() { bool Output; DLUser = new DLLoginMaster (); Output = DLUser.DeletefromTable(this); return Output; } public DataSet FillData() { DataSet ds = null; DLUser = new DLLoginMaster (); ds = DLUser.FillData(); return ds; } public string GetUserId(string str) { string Rid; DLUser = new DLLoginMaster (); Rid = DLUser.GetUserId(str); return Rid; } public bool CheckPassword(TextBox txt) { bool Output; DLUser = new DLLoginMaster (); Output = DLUser.CheckPassowrd(txt.Text); if (Output) { Output = true; } else { Output = false; } return Output; } Application Layer: Drag and Drop Items: No of TextBoxes = 4 No of Check Box =1 No Of Button =3 No of DataGridview =1

using .BusinessLayer; private BLLoginMaster BLUserMas; bool SaveStatus; private void FillData() { BLUserMas = new BLLoginMaster (); DGFillData.DataSource = BLUserMas.FillData().Tables[0]; } private void btn_Add_Click(object sender, EventArgs e) { InsertIntoTable(); } private void btn_Update_Click(object sender, EventArgs e) { UpdateIntoTable(); } private void btn_Delete_Click(object sender, EventArgs e) { DeleteFromTable(); } private void InsertIntoTable() { try { BLUserMas = new BLLoginMaster (); BLUserMas.UserName = txtUsername.Text.ToString(); BLUserMas.Password = txtPassword.Text.ToString(); BLUserMas.FirstName = txtFirstname.Text.ToString(); BLUserMas.LastName = txtLastname.Text.ToString(); if (chkActive.Checked) { BLUserMas.Active = true; } else { BLUserMas.Active = false; } if (txtPassword.Text != "") { BLUserMas = new BLLoginMaster (); bool Result = BLUserMas.CheckPassword(txtPassword); if (Result) { MessageBox.Show("Password Already Exists"); } else { SaveStatus = BLUserMas.SaveRecord(); } } if (SaveStatus) { MessageBox.Show("Saved Successfully"); FillData(); } } catch (Exception ex) { Program.WriteLog(ex.Message, ex.StackTrace); } } private void UpdateIntoTable() { try { BLUserMas = new BLLoginMaster (); BLUserMas.UserName = txtUsername.Text.ToString(); BLUserMas.Password = txtPassword.Text.ToString(); BLUserMas.FirstName = txtFirstname.Text.ToString(); BLUserMas.LastName = txtLastname.Text.ToString(); if (chkActive.Checked) { BLUserMas.Active = true; } else { BLUserMas.Active = false; } int Index = DGFillData.CurrentRow.Index; string userId = BLUserMas.GetUserId(Convert.ToString(DGFillData.Rows[Index].Cells[1].Value)); BLUserMas.UserId = Convert.ToInt32(userId); if (chkActive.Checked) { BLUserMas.Active = true; } else { BLUserMas.Active = false; } bool Status = BLUserMas.UpdateRecord(); if (Status) { MessageBox.Show("Updated Successfully", "Gantec"); FillData(); } } catch (Exception ex) { Program.WriteLog(ex.Message, ex.StackTrace); } } private void DeleteFromTable() { try { BLUserMas = new BLLoginMaster (); int Index = DGFillData.CurrentRow.Index; BLUserMas.UserId = Convert.ToInt32(DGFillData.Rows[Index].Cells[0].Value); bool Status = BLUserMas.DeleteRecord(); if (Status) { MessageBox.Show("Deleted Successfully"); FillData(); } } catch (Exception ex) { Program.WriteLog(ex.Message, ex.StackTrace); } }

Conclusion: Thanks for Reading this Article.