Billing System
19. Creating Methods to Insert, Update and Delete in DAL for Product Module
In the previous episode, we have successfully created Business Logic Layer (BLL) for our Product Module where we created getters and setters.
In this episode, we will create Data Access Layer (DAL) for our Product Module where we will create methods to Select all the products from database, Insert new Product and also methods to Update & Delete Existing Products.
WATCH THE VIDEO FIRST.
The methods to SELECT, INSERT, UPDATE and DELETE are as follows
1. Method to SELECT all the Products from Database
public DataTable Select()
{
//Create Sql Connection to connect Databaes
SqlConnection conn = new SqlConnection(myconnstrng);
//DAtaTable to hold the data from database
DataTable dt = new DataTable();
try
{
//Writing the Query to Select all the products from database
String sql = "SELECT * FROM tbl_products";
//Creating SQL Command to Execute Query
SqlCommand cmd = new SqlCommand(sql, conn);
//SQL Data Adapter to hold the value from database temporarily
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
//Open DAtabase Connection
conn.Open();
adapter.Fill(dt);
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
return dt;
}
2. Method to INSERT New Product in Database
public bool Insert(productsBLL p)
{
//Creating Boolean Variable and set its default value to false
bool isSuccess = false;
//Sql Connection for DAtabase
SqlConnection conn = new SqlConnection(myconnstrng);
try
{
//SQL Query to insert product into database
String sql = "INSERT INTO tbl_products (name, category, description, rate, qty, added_date, added_by) VALUES (@name, @category, @description, @rate, @qty, @added_date, @added_by)";
//Creating SQL Command to pass the values
SqlCommand cmd = new SqlCommand(sql, conn);
//Passign the values through parameters
cmd.Parameters.AddWithValue("@name", p.name);
cmd.Parameters.AddWithValue("@category", p.category);
cmd.Parameters.AddWithValue("@description", p.description);
cmd.Parameters.AddWithValue("@rate", p.rate);
cmd.Parameters.AddWithValue("@qty", p.qty);
cmd.Parameters.AddWithValue("@added_date", p.added_date);
cmd.Parameters.AddWithValue("@added_by", p.added_by);
//Opening the Database connection
conn.Open();
int rows = cmd.ExecuteNonQuery();
//If the query is executed successfully then the value of rows will be greater than 0 else it will be less than 0
if(rows>0)
{
//Query Executed Successfully
isSuccess = true;
}
else
{
//FAiled to Execute Query
isSuccess = false;
}
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
return isSuccess;
}
3. Method to Update Existing Product in Database
public bool Update(productsBLL p)
{
//create a boolean variable and set its initial value to false
bool isSuccess = false;
//Create SQL Connection for DAtabase
SqlConnection conn = new SqlConnection(myconnstrng);
try
{
//SQL Query to Update Data in dAtabase
String sql = "UPDATE tbl_products SET [email protected], [email protected], [email protected], rate=@rate, [email protected]_date, [email protected]_by WHERE [email protected]";
//Create SQL Cmmand to pass the value to query
SqlCommand cmd = new SqlCommand(sql, conn);
//Passing the values using parameters and cmd
cmd.Parameters.AddWithValue("@name", p.name);
cmd.Parameters.AddWithValue("@category", p.category);
cmd.Parameters.AddWithValue("@description", p.description);
cmd.Parameters.AddWithValue("@rate", p.rate);
cmd.Parameters.AddWithValue("@qty", p.qty);
cmd.Parameters.AddWithValue("@added_date", p.added_date);
cmd.Parameters.AddWithValue("@added_by", p.added_by);
cmd.Parameters.AddWithValue("@id", p.id);
//Open the Database connection
conn.Open();
//Create Int Variable to check if the query is executed successfully or not
int rows = cmd.ExecuteNonQuery();
//if the query is executed successfully then the value of rows will be greater than 0 else it will be less than zero
if(rows>0)
{
//Query ExecutedSuccessfully
isSuccess = true;
}
else
{
//Failed to Execute Query
isSuccess = false;
}
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
return isSuccess;
}
4. Method to Delete Existing Product in Database
public bool Delete(productsBLL p)
{
//Create Boolean Variable and Set its default value to false
bool isSuccess = false;
//SQL Connection for DB connection
SqlConnection conn = new SqlConnection(myconnstrng);
try
{
//Write Query Product from DAtabase
String sql = "DELETE FROM tbl_products WHERE [email protected]";
//Sql Command to Pass the Value
SqlCommand cmd = new SqlCommand(sql, conn);
//Passing the values using cmd
cmd.Parameters.AddWithValue("@id", p.id);
//Open Database Connection
conn.Open();
int rows = cmd.ExecuteNonQuery();
//If the query is executed successfullly then the value of rows will be greated than 0 else it will be less than 0
if(rows>0)
{
//Query Executed Successfully
isSuccess = true;
}
else
{
//Failed to Execute Query
isSuccess = false;
}
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
return isSuccess;
}
Thank you so much for reading and watching my tutorial. If you want to see more posts like this, then don't forget to SUBSCRIBE.
If you want to START from the beginning of this course, then CLICK HERE.
Post a Comment
0 Comments