private int SQLDelete(int id)
{
SqlCommand cmd;
try
{
con = AppConfig.GetConnection();
con.Open();
cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = "[DeleteCategories]"; // ten procedure
cmd.CommandType = CommandType.StoredProcedure;
// params
cmd.Parameters.Add("categoryid", SqlDbType.Int).Value = id;
// end params
int rs = cmd.ExecuteNonQuery();
if (rs > 0)
{
return(rs);
}
else
{
return(0);
}
}
catch (SqlException)
{
return(-1);
}
catch (Exception)
{
return(-1);
}
finally
{
con.Close();
}
}
private bool CheckConstrainforCategories(int id)
{
SqlCommand cmd;
SqlDataReader dr = null;
try
{
con = AppConfig.GetConnection();
con.Open();
cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = "[CheckFKCategoriesProducts]";
cmd.CommandType = CommandType.StoredProcedure;
// params
cmd.Parameters.Add("categoryid", SqlDbType.Int).Value = id;
dr = cmd.ExecuteReader();
if (!dr.HasRows)
{
return(true);
}
MessageBox.Show("Please make sure no constrain in Product table!!!");
// end params
}
catch (SqlException ex)
{
return(false);
}
catch (Exception es)
{
return(false);
}
finally
{
if (dr != null)
{
dr.Close();
}
con.Close();
}
return(false);
}
private void LoadCategories()
{
DataTable data = new DataTable();
cbCateFilter.DataSource = data;
SqlDataReader r = null;
SqlCommand cmd;
try
{
con = AppConfig.GetConnection();
con.Open();
cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = "[LoadCategoryForProduct]";
cmd.CommandType = CommandType.StoredProcedure;
r = cmd.ExecuteReader();
data.Load(r);
cbCateFilter.DisplayMember = "Name";
cbCateFilter.ValueMember = "ID";
}
catch (SqlException e)
{
Console.WriteLine("SqlException_FilterCategory" + e.Message);
}
catch (Exception e)
{
Console.WriteLine("Exception_FilterCategory" + e.Message);
}
finally
{
if (con != null)
{
con.Close();
}
if (r != null)
{
r.Close();
}
}
}
private void cbFilterCountry_SelectedIndexChanged(object sender, EventArgs e)
{
SqlCommand cmd;
SqlDataReader reader = null;
try
{
con = AppConfig.GetConnection();
con.Open();
cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = "[SearchByCountryCustomers]";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("country", SqlDbType.NVarChar).Value = cbFilterCountry.Text.ToString();
reader = cmd.ExecuteReader();
dataTbl.Clear();
dataTbl.Load(reader);
}
catch (SqlException ex)
{
MessageBox.Show("SqlException_Filter" + ex.Message);
return;
}
catch (Exception ex)
{
MessageBox.Show("Exception_Filter" + ex.Message);
return;
}
finally
{
if (reader != null)
{
reader.Close();
}
if (con != null)
{
con.Close();
}
}
}
private int SQLAdd(Shippers ship)
{
SqlCommand cmd;
try
{
con = AppConfig.GetConnection();
con.Open();
cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = "[InsertShippers]"; // ten procedure
cmd.CommandType = CommandType.StoredProcedure;
// params
cmd.Parameters.Add("companyname", SqlDbType.NVarChar).Value = ship.CompanyName;
cmd.Parameters.Add("phone", SqlDbType.NVarChar).Value = ship.Phone;
// end params
int rs = cmd.ExecuteNonQuery();
if (rs > 0)
{
return(rs);
}
else
{
return(0);
}
}
catch (SqlException)
{
return(-1);
}
catch (Exception)
{
return(-1);
}
finally
{
con.Close();
}
}
private int SQLAdd(Categories cate)
{
SqlCommand cmd;
try
{
con = AppConfig.GetConnection();
con.Open();
cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = "[InsertCategories]"; // ten procedure
cmd.CommandType = CommandType.StoredProcedure;
//@categoryname,@description
// params
cmd.Parameters.Add("categoryname", SqlDbType.NVarChar).Value = cate.CategoriesName;
cmd.Parameters.Add("description", SqlDbType.NVarChar).Value = cate.Description;
// end params
int rs = cmd.ExecuteNonQuery();
if (rs > 0)
{
return(rs);
}
else
{
return(0);
}
}
catch (SqlException)
{
return(-1);
}
catch (Exception)
{
return(-1);
}
finally
{
con.Close();
}
}
private void LoadAllManager(int id)
{
SqlCommand cmd;
SqlDataReader reader = null;
try
{
con = AppConfig.GetConnection();
con.Open();
cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = "[GetManagerEmployees]"; // ten procedure
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("empid", SqlDbType.Int).Value = id;
reader = cmd.ExecuteReader();
dataCbMng.Clear();
dataCbMng.Load(reader);
cbMngName.DisplayMember = "Name";
cbMngName.ValueMember = "ID";
}
catch (SqlException)
{
//
}
catch (Exception)
{
//
}
finally
{
if (reader != null)
{
reader.Close();
}
if (con != null)
{
con.Close();
}
}
}
private void LoadAllQuery()
{
SqlCommand cmd;
SqlDataReader reader = null;
try
{
con = AppConfig.GetConnection();
con.Open();
cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = "[getAllPro.Product]"; // ten procedure
cmd.CommandType = CommandType.StoredProcedure;
reader = cmd.ExecuteReader();
dataTbl.Clear();
dataTbl.Load(reader);
// Add param
//cmd.Parameters.Add("sss", SqlDbType.VarChar).Value = "ss";
}
catch (SqlException)
{
//
}
catch (Exception)
{
//
}
finally
{
if (reader != null)
{
reader.Close();
}
if (con != null)
{
con.Close();
}
}
}
private void LoadAllQuery()
{
SqlCommand cmd;
SqlDataReader reader = null;
try
{
con = AppConfig.GetConnection();
con.Open();
cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = "[getAllPro.Product]";
cmd.CommandType = CommandType.StoredProcedure;
reader = cmd.ExecuteReader();
dataTbl.Clear();
dataTbl.Load(reader);
}
catch (SqlException ex)
{
Console.WriteLine("SqlException_LoadAllQuery" + ex.Message);
return;
}
catch (Exception ex)
{
Console.WriteLine("Exception_LoadAllQuery" + ex.Message);
return;
}
finally
{
if (reader != null)
{
reader.Close();
}
if (con != null)
{
con.Close();
}
}
}
private void cbCateFilter_SelectedIndexChanged(object sender, EventArgs e)
{
SqlCommand cmd;
SqlDataReader reader = null;
try
{
con = AppConfig.GetConnection();
con.Open();
cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = "[SearchByCateProduct]";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("cateid", SqlDbType.Int).Value = int.Parse(cbCateFilter.SelectedValue.ToString().Trim());
reader = cmd.ExecuteReader();
dataTbl.Clear();
dataTbl.Load(reader);
}
catch (SqlException ex)
{
Console.WriteLine("SqlException_FilterCateClick" + ex.Message);
return;
}
catch (Exception ex)
{
Console.WriteLine("Exception_FilterCateClick" + ex.Message);
return;
}
finally
{
if (reader != null)
{
reader.Close();
}
if (con != null)
{
con.Close();
}
}
}
private int SQLDelete(int proid)
{
SqlCommand cmd;
try
{
con = AppConfig.GetConnection();
con.Open();
cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = "[DeleteOrderDetails]"; // ten procedure
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("ordid", SqlDbType.Int).Value = orderID;
cmd.Parameters.Add("proid", SqlDbType.Int).Value = proid;
int rs = cmd.ExecuteNonQuery();
if (rs > 0)
{
return(rs);
}
else
{
return(0);
}
}
catch (SqlException)
{
return(-1);
}
catch (Exception)
{
return(-1);
}
finally
{
con.Close();
}
}
public void SearchByPhone()
{
SqlCommand cmd;
SqlDataReader dr = null;
try
{
con = AppConfig.GetConnection();
con.Open();
cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = "[SearchByPhoneShipper]"; // ten procedure
cmd.CommandType = CommandType.StoredProcedure;
// params
cmd.Parameters.Add("phone", SqlDbType.NVarChar).Value = txtSearch.Text.ToString().Trim();
dr = cmd.ExecuteReader();
dataTbl.Clear();
dataTbl.Load(dr);
// end params
}
catch (SqlException ex)
{
Console.WriteLine("SqlException_SearchByPhone" + ex.Message);
return;
}
catch (Exception es)
{
Console.WriteLine("Exception_SearchByPhone" + es.Message);
return;
}
finally
{
if (dr != null)
{
dr.Close();
}
con.Close();
}
}
private void LoadAllQuery()
{
SqlCommand cmd;
SqlDataReader reader = null;
try
{
con = AppConfig.GetConnection();
con.Open();
cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = "[getAllSales.orderDetail]"; // ten procedure
cmd.CommandType = CommandType.StoredProcedure;
// Add param
cmd.Parameters.Add("orderid", SqlDbType.Int).Value = orderID;
reader = cmd.ExecuteReader();
dataTbl.Clear();
dataTbl.Load(reader);
}
catch (SqlException e)
{
Console.WriteLine("LoadAllQuery_SqlException: " + e.Message);
}
catch (Exception e)
{
Console.WriteLine("LoadAllQuery_Exception: " + e.Message);
}
finally
{
if (reader != null)
{
reader.Close();
}
if (con != null)
{
con.Close();
}
}
}
private void LoadAllQuery()
{
SqlCommand cmd;
SqlDataReader reader = null;
try
{
con = AppConfig.GetConnection();
con.Open();
cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = "[getAllEmployees]"; // ten procedure
cmd.CommandType = CommandType.StoredProcedure;
reader = cmd.ExecuteReader();
dataTbl.Clear();
dataTbl.Load(reader);
dgEmployees.Columns[COLUMN_MNGID].Visible = false;
}
catch (SqlException)
{
//
}
catch (Exception)
{
//
}
finally
{
if (reader != null)
{
reader.Close();
}
if (con != null)
{
con.Close();
}
}
}
private void cbFilterCountry_SelectedIndexChanged(object sender, EventArgs e)
{
SqlCommand cmd;
SqlDataReader dr = null;
try
{
con = AppConfig.GetConnection();
con.Open();
cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = "[SearchByCountrySuppliers]"; // ten procedure
cmd.CommandType = CommandType.StoredProcedure;
// params
cmd.Parameters.Add("country", SqlDbType.NVarChar).Value = cbFilterCountry.SelectedItem.ToString();
dr = cmd.ExecuteReader();
dataTbl.Clear();
dataTbl.Load(dr);
// end params
}
catch (SqlException ex)
{
Console.WriteLine("SqlException_FilterCountry" + ex.Message);
return;
}
catch (Exception es)
{
Console.WriteLine("Exception_FilterCountry" + es.Message);
return;
}
finally
{
if (dr != null)
{
dr.Close();
}
con.Close();
}
}
private bool CheckExistOrderDetails(int proid)
{
SqlCommand cmd;
SqlDataReader reader = null;
try
{
con = AppConfig.GetConnection();
con.Open();
cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = "[CheckExistOrderDetails]"; // ten procedure
cmd.CommandType = CommandType.StoredProcedure;
// Add param
cmd.Parameters.Add("ordid", SqlDbType.Int).Value = this.orderID;
cmd.Parameters.Add("proid", SqlDbType.Int).Value = proid;
reader = cmd.ExecuteReader();
if (reader.HasRows)
{
return(true);
}
return(false);
}
catch (SqlException)
{
return(true);
}
catch (Exception)
{
return(true);
}
finally
{
con.Close();
}
}
// check
public void searchByShippedDate()
{
//dataTbl = new DataTable();
//dgOrders.DataSource = dataTbl;
SqlCommand cmd;
SqlDataReader dr = null;
try
{
con = AppConfig.GetConnection();
con.Open();
cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = "[SearchOrderByShippedDate]";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("From", SqlDbType.DateTime).Value = dpkFrom.Value;
cmd.Parameters.Add("To", SqlDbType.DateTime).Value = dpkTo.Value;
dr = cmd.ExecuteReader();
dataTbl.Clear();
dataTbl.Load(dr);
}
catch (Exception)
{
MessageBox.Show("Something wrong in database loading!");
return;
}
finally
{
if (dr != null)
{
dr.Close();
}
if (con != null)
{
con.Close();
}
}
}
private bool DeleteEmConstraint(int id)
{
SqlCommand cmd;
SqlDataReader reader = null;
try
{
con = AppConfig.GetConnection();
con.Open();
cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "[CheckFKEmployees]";
cmd.Parameters.Add("empid", SqlDbType.Int).Value = id;
reader = cmd.ExecuteReader();
return(reader.HasRows);
}
catch (SqlException)
{
return(false);
}
catch (Exception)
{
return(false);
}
finally
{
if (reader != null)
{
reader.Close();
}
if (con != null)
{
con.Close();
}
}
}
private void LoadProductQueryForSearch()
{
SqlCommand cmd;
SqlDataReader reader = null;
try
{
con = AppConfig.GetConnection();
con.Open();
cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = "[LoadProductForOrderDetails]"; // ten procedure [ProductForOrderDetailCont]
cmd.CommandType = CommandType.StoredProcedure;
reader = cmd.ExecuteReader();
}
catch (SqlException)
{
//
}
catch (Exception)
{
//
}
finally
{
if (reader != null)
{
reader.Close();
}
if (con != null)
{
con.Close();
}
}
}
private void SQLLoadSupplier()
{
SqlCommand cmd;
IDataReader r = null;
try
{
con = AppConfig.GetConnection();
con.Open();
cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = "[getAllName.Supplier]"; // ten procedure
cmd.CommandType = CommandType.StoredProcedure;
r = cmd.ExecuteReader();
dataSupplier.Load(r);
cbbSupplierID.DisplayMember = "Supplier Name";
cbbSupplierID.ValueMember = "Supplier ID";
cbbSupplierID.SelectedIndex = -1;
cbbSupplierFilter.DisplayMember = "Supplier Name";
cbbSupplierFilter.ValueMember = "Supplier ID";
cbbSupplierFilter.SelectedIndex = -1;
}
catch (SqlException e)
{
Console.WriteLine("co loi Supplier!!!");
}
catch (Exception e)
{
Console.WriteLine("co loi Supplier!!!");
}
finally
{
con.Close();
}
}
private int SQLUpdate(EntityClass.Orders ord)
{
SqlCommand cmd;
try
{
con = AppConfig.GetConnection();
con.Open();
cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = "[UpdateOrder]";
cmd.CommandType = CommandType.StoredProcedure;
if (ckCus.Checked)
{
cmd.Parameters.Add("custid", SqlDbType.Int).Value = (object)DBNull.Value;
}
else
{
cmd.Parameters.Add("custid", SqlDbType.Int).Value = int.Parse(ord.CusID);
}
cmd.Parameters.Add("empid", SqlDbType.Int).Value = ord.EmpID;
cmd.Parameters.Add("orderdate", SqlDbType.DateTime).Value = Convert.ToDateTime(ord.OrderDate);
cmd.Parameters.Add("requireddate", SqlDbType.DateTime).Value = Convert.ToDateTime(ord.RequiredDate);
if (ckShipped.Checked)
{
cmd.Parameters.Add("shippeddate", SqlDbType.DateTime).Value = (object)DBNull.Value;
}
else
{
cmd.Parameters.Add("shippeddate", SqlDbType.DateTime).Value = Convert.ToDateTime(ord.ShippedDate);
}
cmd.Parameters.Add("shipperid", SqlDbType.Int).Value = ord.ShipperID;
cmd.Parameters.Add("freight", SqlDbType.Money).Value = Convert.ToDecimal(ord.Freight);
cmd.Parameters.Add("shipname", SqlDbType.NVarChar).Value = ord.ShipName;
cmd.Parameters.Add("shipaddress", SqlDbType.NVarChar).Value = ord.ShipAddress;
cmd.Parameters.Add("shipcity", SqlDbType.NVarChar).Value = ord.ShipCity;
cmd.Parameters.Add("shipregion", SqlDbType.NVarChar).Value = ord.ShipRegion != null ? ord.ShipRegion : (object)DBNull.Value;
cmd.Parameters.Add("shippostalcode", SqlDbType.NVarChar).Value = ord.ShipPostalCode != null ? ord.ShipPostalCode : (object)DBNull.Value;
cmd.Parameters.Add("shipcountry", SqlDbType.NVarChar).Value = ord.ShipCountry;
cmd.Parameters.Add("orderid", SqlDbType.NVarChar).Value = ord.ID;
int rs = cmd.ExecuteNonQuery();
if (rs > 0)
{
return(rs);
}
else
{
return(0);
}
}
catch (SqlException)
{
return(-1);
}
catch (Exception)
{
return(-1);
}
finally
{
con.Close();
}
}