This is the 6th episode of our tutorial series on "BILLING and INVENTORY MANAGEMENT SYSTEM in C# and MS SQL Server".
In the previous episode, we have successfully created Business Logic Layer (BLL) for User module, which contains classes with getters and setters.
In this episode, we will create Data Access Layer (DAL) for our user module based on userBLL. This DAL will contain methods to Create New User, Display all the Users, Update existing User and Delete User.
PLEASE WATCH THE VIDEO FIRST
Code to create this CRUD (Create, Read, Update and Delete) methods are as follows:1. Method to Create New User
#region Insert Data in Database
public bool Insert(userBLL u)
{
bool isSuccess = false;
SqlConnection conn = new SqlConnection(myconnstrng);
try
{
String sql = "INSERT INTO tbl_users (first_name, last_name, email, username, password, contact, address, gender, user_type, added_date, added_by) VALUES (@first_name, @last_name, @email, @username, @password, @contact, @address, @gender, @user_type, @added_date, @added_by)";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@first_name", u.first_name);
cmd.Parameters.AddWithValue("@last_name", u.last_name);
cmd.Parameters.AddWithValue("@email", u.email);
cmd.Parameters.AddWithValue("@username", u.username);
cmd.Parameters.AddWithValue("@password", u.password);
cmd.Parameters.AddWithValue("@contact", u.contact);
cmd.Parameters.AddWithValue("@address", u.address);
cmd.Parameters.AddWithValue("@gender", u.gender);
cmd.Parameters.AddWithValue("@user_type", u.user_type);
cmd.Parameters.AddWithValue("@added_date", u.added_date);
cmd.Parameters.AddWithValue("@added_by", u.added_by);
conn.Open();
int rows = cmd.ExecuteNonQuery();
//If the query is executed Successfully then the value to rows will be greater than 0 else it will be less than 0
if(rows>0)
{
//Query Sucessfull
isSuccess = true;
}
else
{
//Query Failed
isSuccess = false;
}
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
return isSuccess;
}
#endregion
2. Method to Display All the Users
#region Select Data from Database
public DataTable Select()
{
//Static MEthod to connect Database
SqlConnection conn = new SqlConnection(myconnstrng);
//TO hold the data from database
DataTable dt = new DataTable();
try
{
//SQL Query to Get Data From DAtabase
String sql = "SELECT * FROM tbl_users";
//For Executing Command
SqlCommand cmd = new SqlCommand(sql, conn);
//Getting DAta from dAtabase
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
//Database Connection Open
conn.Open();
//Fill Data in our DataTable
adapter.Fill(dt);
}
catch(Exception ex)
{
//Throw Message if any error occurs
MessageBox.Show(ex.Message);
}
finally
{
//Closing Connection
conn.Close();
}
//Return the value in DataTable
return dt;
}
#endregion
3. Method to Update Existing User
#region Update data in Database
public bool Update(userBLL u)
{
bool isSuccess = false;
SqlConnection conn = new SqlConnection(myconnstrng);
try
{
string sql = "UPDATE tbl_users SET first_name=@first_name, last_name=@last_name, email=@email, username=@username, password=@password, contact=@contact, address=@address, gender=@gender, user_type=@user_type, added_date=@added_date, added_by=@added_by WHERE id=@id";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@first_name", u.first_name);
cmd.Parameters.AddWithValue("@last_name", u.last_name);
cmd.Parameters.AddWithValue("@email", u.email);
cmd.Parameters.AddWithValue("@username", u.username);
cmd.Parameters.AddWithValue("@password", u.password);
cmd.Parameters.AddWithValue("@contact", u.contact);
cmd.Parameters.AddWithValue("@address", u.address);
cmd.Parameters.AddWithValue("@gender", u.gender);
cmd.Parameters.AddWithValue("@user_type", u.user_type);
cmd.Parameters.AddWithValue("@added_date", u.added_date);
cmd.Parameters.AddWithValue("@added_by", u.added_by);
cmd.Parameters.AddWithValue("@id", u.id);
conn.Open();
int rows = cmd.ExecuteNonQuery();
if(rows>0)
{
//Query Successfull
isSuccess = true;
}
else
{
//Query Failed
isSuccess = false;
}
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
return isSuccess;
}
#endregion
4. Method to Delete User
#region Delete Data from DAtabase
public bool Delete(userBLL u)
{
bool isSuccess = false;
SqlConnection conn = new SqlConnection(myconnstrng);
try
{
string sql = "DELETE FROM tbl_users WHERE id=@id";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@id", u.id);
conn.Open();
int rows = cmd.ExecuteNonQuery();
if(rows>0)
{
//Query Successfull
isSuccess = true;
}
else
{
//Query Failed
isSuccess = false;
}
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
return isSuccess;
}
#endregion
Thank you so much for reading my post till the end.
If you are new and is interested to join this course please click on the link below
Comments