public static int InsertProdSupplier(ProdSuppliers newps) { int InsertPS = 0; SqlConnection con = DBConnection.GetConnection(); string sql = "INSERT Products_Suppliers (ProductId,SupplierId) " + "VALUES (@npsPid, @npsSid)"; SqlCommand cmdInsert = new SqlCommand(sql, con); //get connection cmdInsert.Parameters.AddWithValue("@npsPid", newps.ProdId); //assign value with parameter cmdInsert.Parameters.AddWithValue("@npsSid", newps.SupplierId); //assign value with parameter try { con.Open(); //connection open InsertPS = cmdInsert.ExecuteNonQuery(); //execute query } catch (SqlException ex) { MessageBox.Show("The error is " + ex.Message, ex.GetType().ToString()); } finally { con.Close();//close connection } return(InsertPS); }
public static int DeleteProdSupplier(ProdSuppliers delps) { int DeletePS = 0; SqlConnection con = DBConnection.GetConnection(); string sql = "DELETE FROM Products_Suppliers " + "WHERE ProductId = @delPId " + "AND SupplierId = @delSId"; SqlCommand cmdDelete = new SqlCommand(sql, con); //get connection cmdDelete.Parameters.AddWithValue("@delPId", delps.ProdId); //assign value with parameter cmdDelete.Parameters.AddWithValue("@delSId", delps.SupplierId); //assign value with parameter try { con.Open(); //connection open DeletePS = cmdDelete.ExecuteNonQuery(); //execute query } catch (SqlException ex) { MessageBox.Show("The error is " + ex.Message, ex.GetType().ToString());//throw exception } finally { con.Close();//close connection } return(DeletePS); }
public static ProdSuppliers GetProductSupplierById(int pId, int sId) { ProdSuppliers ps = new ProdSuppliers(); SqlConnection con = DBConnection.GetConnection(); string sql = "SELECT ProductSupplierId, ProductId, SupplierId " + "FROM Products_Suppliers " + "WHERE ProductId = @pId and SupplierId =@sId"; SqlCommand cmdSelect = new SqlCommand(sql, con); cmdSelect.Parameters.AddWithValue("@pId", pId); cmdSelect.Parameters.AddWithValue("@sId", sId); try { con.Open(); SqlDataReader Reader = cmdSelect.ExecuteReader(); while (Reader.Read()) { ps.ProductSupplierId = Convert.ToInt32(Reader["ProductSupplierId"]); ps.ProdId = Convert.ToInt32(Reader["ProductId"]); ps.SupplierId = Convert.ToInt32(Reader["SupplierId"]); } } catch (SqlException ex) { MessageBox.Show("The error is " + ex.Message, ex.GetType().ToString()); } finally { con.Close(); } return(ps); }