public static int AddProductsSuppliers(Products_Suppliers prodsupp) { SqlConnection con = UnversalDBControls.GetConnection(); string insertStatement = "INSERT INTO Products_Suppliers (ProductId, SupplierId) " + "VALUES(@ProductId, @SupplierId)"; SqlCommand cmd = new SqlCommand(insertStatement, con); cmd.Parameters.AddWithValue("@ProductId", prodsupp.ProductId); cmd.Parameters.AddWithValue("@SupplierId", prodsupp.SupplierId); try { con.Open(); cmd.ExecuteNonQuery(); string selectQuery = "SELECT IDENT_CURRENT('Products_Suppliers') FROM Products_Suppliers"; SqlCommand selectCmd = new SqlCommand(selectQuery, con); int ProductSupplierId = Convert.ToInt32(selectCmd.ExecuteScalar()); return(ProductSupplierId); } catch (SqlException ex) { throw ex; } finally { con.Close(); } }
public static List <Products_Suppliers> GetProductsSuppliers() { List <Products_Suppliers> products_supplier = new List <Products_Suppliers>(); Products_Suppliers prodsupp; SqlConnection connection = UnversalDBControls.GetConnection(); string selectStatement = "SELECT ProductSupplierId, ps.ProductId, ps.SupplierId,p.ProdName,s.SupName " + "FROM Products_Suppliers ps, Products p, Suppliers s where p.ProductId = ps.ProductId and s.SupplierId = ps.SupplierId ORDER BY ProductSupplierId"; SqlCommand selectCommand = new SqlCommand(selectStatement, connection); try { connection.Open(); SqlDataReader dr = selectCommand.ExecuteReader(); while (dr.Read()) { prodsupp = new Products_Suppliers(); prodsupp.ProductSupplierId = (int)dr["ProductSupplierId"]; prodsupp.ProductId = (int)dr["ProductId"]; prodsupp.SupplierId = (int)dr["SupplierId"]; prodsupp.ProductName = dr["ProdName"].ToString(); prodsupp.SupplierName = dr["SupName"].ToString(); products_supplier.Add(prodsupp); } } catch (SqlException ex) { throw ex; } finally { connection.Close(); } return(products_supplier); }
public static List <Products_Suppliers> GetProductsSuppliersIdFromPackageId(int PackageId) { List <Products_Suppliers> products_supplier = new List <Products_Suppliers>(); Products_Suppliers prodsupp; SqlConnection connection = UnversalDBControls.GetConnection(); string selectStatement = "SELECT a.ProductSupplierId, a.ProductId, a.SupplierId " + "FROM Products_Suppliers a INNER JOIN Packages_Products_Suppliers b " + "ON a.ProductSupplierId = b.ProductSupplierId " + "WHERE b.PackageId = @PackageId"; SqlCommand selectCommand = new SqlCommand(selectStatement, connection); selectCommand.Parameters.AddWithValue("@PackageId", PackageId); try { connection.Open(); SqlDataReader dr = selectCommand.ExecuteReader(); while (dr.Read()) { prodsupp = new Products_Suppliers(); prodsupp.ProductSupplierId = (int)dr["ProductSupplierId"]; prodsupp.ProductId = (int)dr["ProductId"]; prodsupp.SupplierId = (int)dr["SupplierId"]; products_supplier.Add(prodsupp); } } catch (SqlException ex) { throw ex; } finally { connection.Close(); } return(products_supplier); }
// Creates a copy of the current Products_Suppliers public Products_Suppliers CopyCombo() { Products_Suppliers copy = new Products_Suppliers(); copy.ProductId = this.ProductId; copy.ProductName = this.ProductName; copy.ProductSupplierId = this.ProductSupplierId; copy.SupplierId = this.SupplierId; copy.SupplierName = this.SupplierName; return(copy); }
public static bool UpdateProductsSuppliers(Products_Suppliers oldProdSupp, Products_Suppliers newProdSupp) { SqlConnection con = UnversalDBControls.GetConnection(); string updateStatement = "SET IDENTITY_INSERT Products_Suppliers ON " + "UPDATE Products_Suppliers " + "SET ProductId = @NewProductId, " + "SupplierId = @NewSupplierId " + "WHERE ProductSupplierId = @OldProductSupplierId " + "AND ProductId = @OldProductId " + "AND SupplierId = @OldSupplierId " + "SET IDENTITY_INSERT Products_Suppliers OFF"; SqlCommand cmd = new SqlCommand(updateStatement, con); cmd.Parameters.AddWithValue("@NewProductId", newProdSupp.ProductId); cmd.Parameters.AddWithValue("@NewSupplierId", newProdSupp.SupplierId); cmd.Parameters.AddWithValue("@OldProductId", oldProdSupp.ProductId); cmd.Parameters.AddWithValue("@OldSupplierId", oldProdSupp.SupplierId); cmd.Parameters.AddWithValue("@OldProductSupplierId", oldProdSupp.ProductSupplierId); try { con.Open(); if (cmd.ExecuteNonQuery() > 0) { return(true); } else { return(false); } } catch (SqlException ex) { throw ex; } finally { con.Close(); } }