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(); } }