DAL
userdal.cs
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using POS.BLL;
namespace POS.DAL
{
clast userdal
{
#region select data from database
public DataTable select()
{
//MEthod to connect Database
connclast c = new connclast();
SqlConnection conn = new SqlConnection(c.connection);
//TO hold the data from database
DataTable dt = new DataTable();
try
{
//SQL Query to Get Data From DAtabase
string query = "select* from tbl_users";
//For Executing Command
SqlCommand cmd = new SqlCommand(query, 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
#region Insert data into database
public bool insert(usersbll u)
{
//Create a boolean variable and set its value to false and return it
bool issucess = false;
//MEthod to connect Database
connclast c = new connclast();
SqlConnection conn = new SqlConnection(c.connection);
try
{
//SQL Query to insert Data in DAtabase
string query = "Insert into tbl_users(user_name,user_type,pastword,email,cnic,adress,phone_no,added_by,added_date)Values(@user_name,@user_type,@pastword,@email,@cnic,@adress,@phone_no,@added_by,@added_date)";
//For Executing Command
SqlCommand cmd = new SqlCommand(query, conn);
//Pasting Values to the Variables
cmd.Parameters.AddWithValue("@user_name", u.user_name);
cmd.Parameters.AddWithValue("@user_type", u.user_type);
cmd.Parameters.AddWithValue("@pastword", u.pastword);
cmd.Parameters.AddWithValue("@email", u.email);
cmd.Parameters.AddWithValue("@cnic", u.cnic);
cmd.Parameters.AddWithValue("@adress", u.adress);
cmd.Parameters.AddWithValue("@phone_no", u.phone_no);
cmd.Parameters.AddWithValue("@added_by", u.added_by);
cmd.Parameters.AddWithValue("@added_date", u.added_date);
//Database Connection Open
conn.Open();
//To execute non query
int row = 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 (row > 0)
{
//Query Sucessfull
issucess = true;
}
else
{
//Query Failed
issucess = false;
}
}
catch (Exception ex)
{
//Throw Message if any error occurs
MessageBox.Show(ex.Message);
}
finally
{
//Closing Connection
conn.Close();
}
return issucess;
}
#endregion
#region Update data in database
public bool update(usersbll u)
{
//Create a boolean variable and set its value to false and return it
bool issucess = false;
//MEthod to connect Database
connclast c = new connclast();
SqlConnection conn = new SqlConnection(c.connection);
try
{
//SQL Query to update Data in DAtabase
string query = "UPDATE tbl_users set [email protected]_name,[email protected]_type,[email protected],[email protected],[email protected],[email protected],[email protected]_no WHERE [email protected]";
//For Executing Command
SqlCommand cmd = new SqlCommand(query, conn);
//Pasting Values to the Variables
cmd.Parameters.AddWithValue("@id", u.id);
cmd.Parameters.AddWithValue("@user_name", u.user_name);
cmd.Parameters.AddWithValue("@user_type", u.user_type);
cmd.Parameters.AddWithValue("@pastword", u.pastword);
cmd.Parameters.AddWithValue("@email", u.email);
cmd.Parameters.AddWithValue("@cnic", u.cnic);
cmd.Parameters.AddWithValue("@adress", u.adress);
cmd.Parameters.AddWithValue("@phone_no", u.phone_no);
//Database Connection Open
conn.Open();
//To execute non query
int row = 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 (row > 0)
{
//Query Sucessfull
issucess = true;
}
else
{
//Query Failed
issucess = false;
}
}
catch (Exception ex)
{
//Throw Message if any error occurs
MessageBox.Show(ex.Message);
}
finally
{
//Closing Connection
conn.Close();
}
return issucess;
}
#endregion
#region Delete record in Data Base
public bool delete(usersbll u)
{
//Create a boolean variable and set its value to false and return it
bool issucess = false;
//MEthod to connect Database
connclast c = new connclast();
SqlConnection conn = new SqlConnection(c.connection);
try
{
//SQL Query to delete Data in DAtabase
string query = "DELETE FROM tbl_users WHERE [email protected]";
//For Executing Command
SqlCommand cmd = new SqlCommand(query, conn);
//Pasting Values to the Variables
cmd.Parameters.AddWithValue("@id", u.id);
//Database Connection Open
conn.Open();
//To execute non query
int row = 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 (row > 0)
{
//Query Sucessfull
issucess = true;
}
else
{
//Query Failed
issucess = false;
}
}
catch (Exception ex)
{
//Throw Message if any error occurs
MessageBox.Show(ex.Message);
}
finally
{
//Closing Connection
conn.Close();
}
return issucess;
}
#endregion
#region Search Data From DataBase
public DataTable Search(string keyword)
{
//MEthod to connect Database
connclast c = new connclast();
SqlConnection conn = new SqlConnection(c.connection);
//TO hold the data from database
DataTable dt = new DataTable();
try
{
//SQL Query to search Data from DAtabase
string query = "SELECT* FROM tbl_users WHERE id Like '%" + keyword + "%' OR user_name like '%" + keyword + "%' OR user_type like '%" + keyword + "%' OR adress like '%" + keyword + "%' ";
//For Executing Command
SqlCommand cmd = new SqlCommand(query, 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
#region If Product exist
public bool exist(usersbll u)
{
//Create a boolean variable and set its value to false and return it
bool issuccess = false;
//MEthod to connect Database
connclast c = new connclast();
SqlConnection conn = new SqlConnection(c.connection);
try
{
//SQL Query to selecte Data from DAtabase
string query = "select * from tbl_users where [email protected]_name AND [email protected] AND [email protected] AND [email protected]_no ";
//For Executing Command
SqlCommand cmd = new SqlCommand(query, conn);
//Pasting Values to the Variables
cmd.Parameters.AddWithValue("@user_name", u.user_name);
cmd.Parameters.AddWithValue("@cnic", u.cnic);
cmd.Parameters.AddWithValue("@email", u.email);
cmd.Parameters.AddWithValue("@phone_no", u.phone_no);
//Getting DAta from dAtabase
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
//TO hold the data from database
DataTable dt = new DataTable();
//Database Connection Open
conn.Open();
//Fill Data in our DataTable
adapter.Fill(dt);
//Checking The rows in DataTable
if (dt.Rows.Count > 0)
{
//Data Exist
issuccess = true;
}
else
{
//Data not exist
issuccess = false;
}
}
catch (Exception ex)
{
//Throw Message if any error occurs
MessageBox.Show(ex.Message);
}
finally
{
//Closing Connection
conn.Close();
}
return issuccess;
}
#endregion
#region Getting User ID from Username
public usersbll GetIDFromUsername(string username)
{
//Connecting user BLL for getting id
usersbll u = new usersbll();
//MEthod to connect Database
connclast c = new connclast();
SqlConnection conn = new SqlConnection(c.connection);
//TO hold the data from database
DataTable dt = new DataTable();
try
{
//SQL Query to get Data from DAtabase
string sql = "SELECT id FROM tbl_users WHERE user_name='" + username + "'";
//Getting DAta from dAtabase
SqlDataAdapter adapter = new SqlDataAdapter(sql, conn);
//Database Connection Open
conn.Open();
//Fill Data in our DataTable
adapter.Fill(dt);
//Checking The rows in DataTable
if (dt.Rows.Count > 0)
{
//Getting id from DataTable
u.id = int.Parse(dt.Rows[0]["id"].ToString());
}
}
catch (Exception ex)
{
//Throw Message if any error occurs
MessageBox.Show(ex.Message);
}
finally
{
//Closing Connection
conn.Close();
}
return u;
}
#endregion
}
}
