public static List <Products_Supplier> GetProducts_Suppliers() { List <Products_Supplier> products_Suppliers = new List <Products_Supplier>(); // empty list object Products_Supplier ps; // for reading using (SqlConnection connection = TravelExpertConnection.GetConnection()) { string selectQuery = "SELECT ProductSupplierId, ProductId, ProdName, SupplierId, SupName" + " FROM Products_Suppliers" + " order BY SupplierId"; using (SqlCommand cmd = new SqlCommand(selectQuery, connection)) { connection.Open(); using (SqlDataReader dr = cmd.ExecuteReader()) { while (dr.Read()) // while there is data { ps = new Products_Supplier(); // object with no data initialized ps.ProductSupplierId = (int)dr["ProductSupplierId"]; ps.ProductId = (int)dr["ProductId"]; ps.SupplierId = (int)dr["SupplierId"]; ps.ProdName = (string)dr["ProdName"]; ps.SupName = (string)dr["SupName"]; products_Suppliers.Add(ps); } } // dr gets closed and recycled } // cmd gets recycled } // connection gets closed, and recycled return(products_Suppliers); }
public static Products_Supplier GetProductSupplierByID(int productID) { List <Products_Supplier> products_Supplier = new List <Products_Supplier>(); // empty list object //Products_Supplier products_Supplier = new Products_Supplier(); // empty list object Products_Supplier psi = null; using (SqlConnection connection = TravelExpertConnection.GetConnection()) { string query = "SELECT ProductId, ProductSupplierId, SupplierId FROM Products_Suppliers " + //"ORDER BY ProductSupplierId " + " Where ProductId = @ProductId"; using (SqlCommand cmd = new SqlCommand(query, connection)) { connection.Open(); cmd.Parameters.AddWithValue("@ProductId", productID); using (SqlDataReader dr = cmd.ExecuteReader()) { while (dr.Read()) // while there is data { psi = new Products_Supplier(); psi.ProductSupplierId = (int)dr["ProductSupplierId"]; psi.ProductId = (int)dr["ProductId"]; psi.SupplierId = (int)dr["SupplierId"]; products_Supplier.Add(psi); } } } } return(psi); }
public static List <int> GetSupplierIDs() { List <int> supplierIDs = new List <int>(); // make empty list of IDs int id; //int id = -1; using (SqlConnection con = TravelExpertConnection.GetConnection()) { string query = "SELECT SupplierId FROM Suppliers"; using (SqlCommand cmd = new SqlCommand(query, con)) { con.Open(); using (SqlDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { id = (int)reader["SuppplierId"]; supplierIDs.Add(id); } } } } return(supplierIDs); }
} // end of AddSuplier public static Suppliers GetSupplier(int supplierId) { Suppliers supplier = null; using (SqlConnection con = TravelExpertConnection.GetConnection()) { string query = "SELECT SupplierId, SupName " //SupplierId, + "FROM Suppliers " + "WHERE SupplierId = @SupplierId"; using (SqlCommand cmd = new SqlCommand(query, con)) { cmd.Parameters.AddWithValue("@SupplierId", supplierId); con.Open(); using (SqlDataReader reader = cmd.ExecuteReader (CommandBehavior.SingleRow)) { if (reader.Read()) // filling up the suppliers { supplier = new Suppliers(); supplier.SupplierId = (int)reader["SupplierId"]; supplier.SupName = reader["SupName"].ToString(); } // end of if object } // end of 3rd using } // end of 2nd using } // end of 1st using return(supplier); } // end of GetSupplier
} // end of query search public static List <Suppliers> GetAllSuppliers() { List <Suppliers> suppliers = new List <Suppliers>(); // empty list object Suppliers ps; // for reading using (SqlConnection connection = TravelExpertConnection.GetConnection()) { string selectQuery = "SELECT SupplierId, SupName FROM Suppliers " + "ORDER BY SupName"; using (SqlCommand cmd = new SqlCommand(selectQuery, connection)) { connection.Open(); using (SqlDataReader dr = cmd.ExecuteReader()) { while (dr.Read()) // while there is data { ps = new Suppliers(); // object with no data initialized ps.SupplierId = (int)dr["SupplierID"]; ps.SupName = (string)dr["SupName"]; suppliers.Add(ps); } } // dr gets closed and recycled } // cmd gets recycled } // connection gets closed, and recycled return(suppliers); }
public static List <Packages_Products_Supplier> GetPackages_Products_SupplierByID(int productSupplierId) { List <Packages_Products_Supplier> packages_Products_Supplier = new List <Packages_Products_Supplier>(); // empty list object Packages_Products_Supplier pps; // for reading using (SqlConnection connection = TravelExpertConnection.GetConnection()) { string selectQuery = "SELECT PackageId, ProductSupplierId " + "FROM [Packages_Products_Supplier]" + "WHERE ProductSupplierId = @ProductSupplierId"; using (SqlCommand cmd = new SqlCommand(selectQuery, connection)) // build an object an pass it a { connection.Open(); cmd.Parameters.AddWithValue("@ProductSupplierId", productSupplierId); using (SqlDataReader dr = cmd.ExecuteReader()) { while (dr.Read()) // while there is data { pps = new Packages_Products_Supplier(); // object with no data initialized pps.PackageId = (int)dr["PackageId"]; pps.ProductSupplierId = (int)dr["ProductSupplierId"]; packages_Products_Supplier.Add(pps); } } } } return(packages_Products_Supplier); }
} //End of Update //Add package public static int AddPackage(Packages pack) { int pkId = 0; using (SqlConnection con = TravelExpertConnection.GetConnection()) { string insertStatement = "INSERT INTO Packages " + "(PkgName, PkgStartDate, PkgEndDate, PkgDesc, PkgBasePrice, PkgAgencyCommission, Product) " + "OUTPUT inserted.PackageId " + // output auto-generated ID "VALUES DISTINCT (@PkgName, @PkgStartDate, @PkgEndDate, @PkgDesc, @PkgBasePrice, @PkgAgencyCommission, @Product) "; using (SqlCommand cmd = new SqlCommand(insertStatement, con)) { cmd.Parameters.AddWithValue("@PkgName", pack.PkgName); cmd.Parameters.AddWithValue("@PkgDesc", pack.PkgDesc); //Start date if (pack.PkgStartDate == null) { cmd.Parameters.AddWithValue("@PkgStartDate", DBNull.Value); } else { cmd.Parameters.AddWithValue("@PkgStartDate", pack.PkgStartDate); } //End date if (pack.PkgEndDate == null) { cmd.Parameters.AddWithValue("@PkgEndDate", DBNull.Value); } else { cmd.Parameters.AddWithValue("@PkgEndDate", pack.PkgEndDate); } //Product if (pack.Product == null) { cmd.Parameters.AddWithValue("@Product", DBNull.Value); } else { cmd.Parameters.AddWithValue("@Product", pack.Product); } cmd.Parameters.AddWithValue("@PkgBasePrice", pack.PkgBasePrice); cmd.Parameters.AddWithValue("@PkgAgencyCommission", pack.PkgAgencyCommission); con.Open(); pkId = (int)cmd.ExecuteScalar(); } //2nd using } //1st using return(pkId); } // end AddPackage
// Add Product public static int AddProduct(Products prod) { int prId = 0; using (SqlConnection con = TravelExpertConnection.GetConnection()) { string insertStatement = "INSERT INTO Products " + "(ProdName) " + "OUTPUT inserted.ProductId " + // output auto-generated ID "VALUES(@ProdName)"; using (SqlCommand cmd = new SqlCommand(insertStatement, con)) { cmd.Parameters.AddWithValue("@ProdName", prod.ProdName); con.Open(); prId = (int)cmd.ExecuteScalar(); } } return(prId); }
} // end of GetSupplier public static bool UpdateSuppliers(Suppliers oldSup, Suppliers newSup) { bool success = false; // if no new supplier using (SqlConnection con = TravelExpertConnection.GetConnection()) { string UpdateStatement = "UPDATE SUPPLIERS SET " + " SupName = @NewSupName " + " WHERE Suppliersid = @OldSuppliersid " + " And (SupName = @OldSupName OR " + " SupName IS NULL AND OldSupName IS NULL)"; using (SqlCommand cmd = new SqlCommand(UpdateStatement, con)) { if (newSup.SupName == null) { cmd.Parameters.AddWithValue("@NewSupName", DBNull.Value); } else { cmd.Parameters.AddWithValue("@NewSupName", (string)oldSup.SupName); cmd.Parameters.AddWithValue("@NewSuuplierID", oldSup.SupplierId); } if (oldSup.SupName == null) { cmd.Parameters.AddWithValue("@OldSupName", DBNull.Value); } else { cmd.Parameters.AddWithValue("@OldSupName", (string)oldSup.SupName); } con.Open(); int count = cmd.ExecuteNonQuery(); if (count > 0) { success = true; } } // end of object using object return(success); } // end of connection update Supplier } // end of query search
}//End of GetAllProducts //Update Product public static bool UpdateProduct(Products oldProd, Products newProd) { using (SqlConnection con = TravelExpertConnection.GetConnection()) { string updateStatement = "UPDATE Products " + "SET ProdName = @NewProdName " + "WHERE ProductId = @OldProductId " + "AND ProdName = @OldProdName "; //"ADD CONSTRAINT ProdNam UNIQUE (ProductId, ProdName)"; using (SqlCommand cmd = new SqlCommand(updateStatement, con)) { //******NEW Products data********* cmd.Parameters.AddWithValue("@NewProdName", newProd.ProdName); //******OLD Products data********* cmd.Parameters.AddWithValue("@OldProductId", oldProd.ProductId); cmd.Parameters.AddWithValue("@OldProdName", oldProd.ProdName); try { con.Open(); int count = cmd.ExecuteNonQuery(); if (count > 0) { return(true); } else { return(false); } } catch (SqlException ex) { throw ex; } finally { con.Close(); } } } }
//Getting all products public static List <Products> GetAllProducts() { List <Products> products = new List <Products>(); Products pr = null; SqlConnection con = TravelExpertConnection.GetConnection(); string selectStatement = "SELECT * " + "FROM Products " + "ORDER BY ProductId"; SqlCommand cmd = new SqlCommand(selectStatement, con); //con.Open(); //SqlDataReader reader = cmd.ExecuteReader(); //if (!reader.HasRows) //{ // MessageBox.Show("Product Name has to be unique") //} try { con.Open(); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) // while there is another record { pr = new Products(); pr.ProductId = (int)reader["ProductId"]; pr.ProdName = reader["ProdName"].ToString(); products.Add(pr); } } catch (SqlException ex) { throw ex; } finally { con.Close(); } return(products); }//End of GetAllProducts
public static int AddSupplier(Suppliers supplier) { int supID = -1; // -1 using (SqlConnection con = TravelExpertConnection.GetConnection()) { string insertStatement = "INSERT INTO Suppliers (SupName, ) " + "OUTPUT inserted.SupplierId " + "Values (@SupplierName)"; using (SqlCommand cmd = new SqlCommand(insertStatement, con)) { // add more codes cmd.CommandText = "spInsertSupplier"; //procedure for handle non-auto generate pk cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@SupplierName", supplier.SupName); con.Open(); supID = (int)cmd.ExecuteScalar(); } } return(supID); } // end of AddSuplier
public static List <int> GetProductSupplierIDs() { List <int> productSupplierIDs = new List <int>(); // empty list of IDs int id; // for reading using (SqlConnection connection = TravelExpertConnection.GetConnection()) { string query = "SELECT ProductSupplierId FROM Products_Suppliers"; // any exception not handled here is automatically thrown to the form // where the method was called using (SqlCommand cmd = new SqlCommand(query, connection)) { connection.Open(); SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); // close connection as soon as done with reading while (reader.Read()) { id = (int)reader["productSupplierId"]; productSupplierIDs.Add(id); } } // command object recycled } // connection object recycled return(productSupplierIDs); }
}//End of GetAllPackages //Update Package public static bool UpdatePackage(Packages oldPack, Packages newPack) { using (SqlConnection con = TravelExpertConnection.GetConnection()) { string updateStatement = "UPDATE Packages " + "SET PkgName = @NewPkgName, " + " PkgDesc = @NewPkgDesc, " + " PkgStartDate = @NewPkgStartDate, " + " PkgEndDate = @NewPkgEndDate, " + " PkgBasePrice = @NewPkgBasePrice, " + " PkgAgencyCommission = @NewPkgAgencyCommission, " + " Product = @NewProduct " + "WHERE PackageId = @OldPackageId " + "AND PkgName = @OldPkgName " + "AND PkgDesc = @OldPkgDesc " + "AND (PkgStartDate = @OldPkgStartDate OR " + //Nullable " PkgStartDate IS NULL AND @OldPkgStartDate IS NULL) " + "AND (PkgEndDate = @OldPkgEndDate OR " + //Nullable " PkgStartDate IS NULL AND @OldPkgStartDate IS NULL) " + "AND PkgBasePrice = @OldPkgBasePrice " + "AND PkgAgencyCommission = @OldPkgAgencyCommission " + "AND (Product = @OldProduct OR " + //Nullable " Product IS NULL AND @OldProduct IS NULL)"; using (SqlCommand cmd = new SqlCommand(updateStatement, con)) { //******NEW Packages data********* cmd.Parameters.AddWithValue("@NewPkgName", newPack.PkgName); cmd.Parameters.AddWithValue("@NewPkgDesc", newPack.PkgDesc); cmd.Parameters.AddWithValue("@NewPkgBasePrice", newPack.PkgBasePrice); cmd.Parameters.AddWithValue("@NewPkgAgencyCommission", newPack.PkgAgencyCommission); if (newPack.Product == null) { cmd.Parameters.AddWithValue("@NewProduct", DBNull.Value); } else { cmd.Parameters.AddWithValue("@NewProduct", newPack.Product); } //New Start date if (newPack.PkgStartDate == null) { cmd.Parameters.AddWithValue("@NewPkgStartDate", DBNull.Value); } else { cmd.Parameters.AddWithValue("@NewPkgStartDate", (DateTime)newPack.PkgStartDate); } //New End date if (newPack.PkgEndDate == null) { cmd.Parameters.AddWithValue("@NewPkgEndDate", DBNull.Value); } else { cmd.Parameters.AddWithValue("@NewPkgEndDate", (DateTime)newPack.PkgEndDate); } //******OLD Packages data********* cmd.Parameters.AddWithValue("@OldPackageId", oldPack.PackgeId); cmd.Parameters.AddWithValue("@OldPkgName", oldPack.PkgName); cmd.Parameters.AddWithValue("@OldPkgDesc", oldPack.PkgDesc); //old Start date if (oldPack.PkgStartDate == null) { cmd.Parameters.AddWithValue("@OldPkgStartDate", DBNull.Value); } else { cmd.Parameters.AddWithValue("@OldPkgStartDate", (DateTime)oldPack.PkgStartDate); } //old End date if (oldPack.PkgEndDate == null) { cmd.Parameters.AddWithValue("@OldPkgEndDate", DBNull.Value); } else { cmd.Parameters.AddWithValue("@OldPkgEndDate", (DateTime)oldPack.PkgEndDate); } cmd.Parameters.AddWithValue("@OldPkgBasePrice", oldPack.PkgBasePrice); cmd.Parameters.AddWithValue("@OldPkgAgencyCommission", oldPack.PkgAgencyCommission); if (oldPack.Product == null) { cmd.Parameters.AddWithValue("@OldProduct", DBNull.Value); } else { cmd.Parameters.AddWithValue("@OldProduct", oldPack.Product); } try { con.Open(); int count = cmd.ExecuteNonQuery(); if (count > 0) { return(true); } else { return(false); } } catch (SqlException ex) { throw ex; } finally { con.Close(); } } //2nd using } //1st "using" } //End of Update
//Getting all packages public static List <Packages> GetAllPackages() { List <Packages> packages = new List <Packages>(); Packages pk = null; SqlConnection con = TravelExpertConnection.GetConnection(); string selectStatement = "SELECT * " + "FROM Packages " + "ORDER BY PackageId"; SqlCommand cmd = new SqlCommand(selectStatement, con); try { con.Open(); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { pk = new Packages(); pk.PackgeId = (int)reader["PackageId"]; pk.PkgName = reader["PkgName"].ToString(); pk.PkgDesc = reader["PkgDesc"].ToString(); pk.PkgBasePrice = (decimal)reader["PkgBasePrice"]; pk.PkgAgencyCommission = (decimal)reader["PkgAgencyCommission"]; packages.Add(pk); //for null values //Added Product int prod = reader.GetOrdinal("Product"); if (reader.IsDBNull(prod)) { pk.Product = null; } else { pk.Product = reader["Product"].ToString(); } //Dates int dateStart = reader.GetOrdinal("PkgStartDate"); int dateEnd = reader.GetOrdinal("PkgEndDate"); if (reader.IsDBNull(dateStart)) { pk.PkgStartDate = null; } else if (reader.IsDBNull(dateEnd)) { pk.PkgEndDate = null; } else // it is not null { pk.PkgStartDate = Convert.ToDateTime(reader["PkgStartDate"]); pk.PkgEndDate = Convert.ToDateTime(reader["PkgEndDate"]); } } } catch (SqlException ex) { throw ex; } finally { con.Close(); } return(packages); }//End of GetAllPackages