Three
Tier Architecture -- Windows Application
Introduction:
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.
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.