// retrieve product info with given product ID public static Products GetProductById(int productId) { Products product = null; //create the conneciton using (SqlConnection connection = TravelExpertsDB.GetConnection()) { // create select command string query = "SELECT ProductId, ProdName " + "FROM Products " + "WHERE ProductId=@ProductId"; // any exception not handled here is automaticlly thrown to the form // where the method was called using (SqlCommand cmd = new SqlCommand(query, connection)) { //supply parameter value cmd.Parameters.AddWithValue("@ProductId", productId); //open the connection connection.Open(); //run the command SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); // close connection as soon as done with reading //build product object to return if (reader.Read()) // if there is a product with this ID { product = new Products(); product.ProductId = (int)reader["ProductId"]; product.ProdName = reader["ProdName"].ToString(); } } // command object recycled } // connection object recyled return(product); }
public static List <Products> ProductDetail(int i) { List <Products> product = new List <Products>(); using (SqlConnection connection = TravelExpertsDB.GetConnection()) { string query = "SELECT ProductId, ProdName " + "FROM Products " + "WHERE ProductId = " + i; using (SqlCommand cmd = new SqlCommand(query, connection)) { connection.Open(); SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); while (reader.Read()) { Products prod = new Products(); prod.ProductId = (int)reader["ProductID"]; prod.ProdName = reader["ProdName"].ToString(); product.Add(prod); } return(product); } } }
/// Get products /// @Rohit public static Products GetProducts(int ProductID) // get product name based on selected productID { Products product = null; using (SqlConnection connection = TravelExpertsDB.GetConnection()) // connection made { //SQL query with parameter selection to extract one product data string query = "SELECT * FROM products WHERE productid = @productID"; // sql query to retrieve data using (SqlCommand cmd = new SqlCommand(query, connection)) { cmd.Parameters.AddWithValue("@productID", ProductID); connection.Open(); using (SqlDataReader reader = cmd.ExecuteReader()) { if (reader.Read()) { product = new Products(); product.ProductID = (int)reader["productID"]; product.ProductName = reader["prodName"].ToString(); } } } } return(product); // return the product name }// closes the connection
public static List <Products> GetProducts() { List <Products> products = new List <Products>(); // empty list of Products //create the connection using (SqlConnection connection = TravelExpertsDB.GetConnection()) { // create select command string query = "SELECT ProductId, ProdName " + "FROM Products"; // any exception not handled here is automaticlly 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()) { Products prod = new Products(); // for reading prod.ProductId = (int)reader["ProductId"]; prod.ProdName = reader["ProdName"].ToString(); products.Add(prod); } } // command object recycled } // connection object recyled return(products); }
// Add supplier db method public void AddSupplier(int supplierId, string supplierName) { SqlConnection con = TravelExpertsDB.GetConnection(); try { // Insert into Supplier Query string addSupplierQuery = @"INSERT INTO Suppliers (SupplierId, SupName) VALUES(@SupplierID, @SupplierName)"; // SQL command SqlCommand sqlSupplierCommand = new SqlCommand(addSupplierQuery, con); con.Open(); sqlSupplierCommand.Parameters.AddWithValue("@SupplierID", supplierId); sqlSupplierCommand.Parameters.AddWithValue("@SupplierName", supplierName); sqlSupplierCommand.ExecuteScalar(); } catch (Exception ex) { throw ex; } finally { con.Close(); } }
// Edit supplier db method public void EditSupplier(int supplierId, string supplierName) { SqlConnection con = TravelExpertsDB.GetConnection(); try { // Update Supplier Query string updateQuery = @"UPDATE Suppliers SET SupplierId = @SupplierID, SupName = @SupplierName WHERE SupplierId = @SupplierID"; // SQL command SqlCommand sqlCommand = new SqlCommand(updateQuery, con); con.Open(); sqlCommand.Parameters.AddWithValue("@SupplierID", supplierId); sqlCommand.Parameters.AddWithValue("@SupplierName", supplierName); sqlCommand.ExecuteScalar(); } catch (Exception ex) { throw ex; } finally { con.Close(); } }
public static List <Supplier> GetSupplier(ListView tableView) { List <Supplier> suppliers = new List <Supplier>(); //sql connection using (SqlConnection con = TravelExpertsDB.GetConnection()) { // Supplier Query string selectSuppliersQuery = @"SELECT SupplierId, SupName FROM Suppliers ORDER BY SupplierId"; // SQL command using (SqlCommand cmd = new SqlCommand(selectSuppliersQuery, con)) { con.Open(); SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); tableView.View = View.Details; tableView.FullRowSelect = true; //Data Table Columns tableView.Columns.Add("SupplierID").Width = 100; tableView.Columns.Add("SupplierName").Width = 300; while (reader.Read()) { // variables var item = new ListViewItem(); item.SubItems[0].Text = reader[0].ToString(); item.SubItems.Add(reader["SupName"].ToString()); tableView.Items.Add(item); } } } return(suppliers); }
//public static List<Supplier> GetProduct(ListView tableView, int suppId) //{ // List<Supplier>products = new List<Supplier>(); // //sql connection // using (SqlConnection con = TravelExpertsDB.GetConnection()) // { // string productQuery = @"SELECT Products.ProductId, Products.ProdName " + // "FROM Products " + // "INNER JOIN Products_suppliers ON Products_suppliers.ProductId = Products.ProductId " + // "INNER JOIN Suppliers ON Suppliers.SupplierId = Products_suppliers.SupplierId " + // "WHERE Suppliers.SupplierId = @SupplierId"; // // SQL command // SqlCommand sqlCommand = new SqlCommand(productQuery, con); // try // { // con.Open(); // sqlCommand.Parameters.AddWithValue("@SupplierId", suppId); // //sqlCommand.Parameters.AddWithValue("@ProdName", supplierName); // sqlCommand.ExecuteScalar(); // SqlDataReader reader = sqlCommand.ExecuteReader(CommandBehavior.CloseConnection); // tableView.View = View.Details; // tableView.FullRowSelect = true; // //Data Table Columns // tableView.Columns.Add("SupplierId").Width = 70; // tableView.Columns.Add("ProdName").Width = 100; // while (reader.Read()) // { // // variables // var item = new ListViewItem(); // item.SubItems[0].Text = reader[0].ToString(); // item.SubItems.Add(reader["ProdName"].ToString()); // tableView.Items.Add(item); // } // } // catch (Exception ex) // { // throw ex; // } // finally // { // con.Close(); // } // } // return products; //} public static List <Supplier> GetProducts(ListBox listBoxPackage, int suppId) { List <Supplier> products = new List <Supplier>(); using (SqlConnection con = TravelExpertsDB.GetConnection()) { string productQuery = @"SELECT Products.ProductId, Products.ProdName " + "FROM Products " + "INNER JOIN Products_suppliers ON Products_suppliers.ProductId = Products.ProductId " + "INNER JOIN Suppliers ON Suppliers.SupplierId = Products_suppliers.SupplierId " + "WHERE Suppliers.SupplierId = @SupplierId"; using (SqlCommand sqlCommand = new SqlCommand(productQuery, con)) { sqlCommand.Parameters.AddWithValue("@SupplierId", suppId); con.Open(); if (listBoxPackage.ValueMember != null) { SqlDataAdapter adapter = new SqlDataAdapter(sqlCommand); DataTable prod = new DataTable(); adapter.Fill(prod); listBoxPackage.DisplayMember = "ProdName"; listBoxPackage.ValueMember = "ProductId"; listBoxPackage.DataSource = prod; } } } return(products); }
/// <summary> /// Public static class method for retrieving all ProductId's from database /// </summary> /// <returns>int list of ProductId's</returns> public static List <int> GetProductIds() { //int list variable to store returned productIds List <int> productIds = new List <int>(); int productId; //stores each retrieved id //Sql connection block to connect to TravelExpertsDB; closes connection at end of block using (SqlConnection connection = TravelExpertsDB.GetConnection()) { string query = "SELECT ProductId " + "FROM Products " + "ORDER BY ProductId"; //sql command block; disposes command at end of block using (SqlCommand cmd = new SqlCommand(query, connection)) { connection.Open(); SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);//new sqldatareader for accessing db while (reader.Read()) { productId = (int)reader["ProductId"]; productIds.Add(productId); // add to the list } } // cmd object recycled }// connection object recycled return(productIds); }
public static List <int> GetProductSupplierIds() { List <int> productSupplierIds = new List <int>(); // empty list of ProductSupplier Ids int id; // for reading //create the connection using (SqlConnection connection = TravelExpertsDB.GetConnection()) { // create select command string query = "SELECT ProductSupplierId FROM Products_Suppliers " + "ORDER BY ProductSupplierId"; // any exception not handled here is automaticlly 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 recyled return(productSupplierIds); }
/// <summary> /// Adds product supplier pair to DB /// </summary> /// <param name="productId">Product ID</param> /// <param name="supplierId">Supplier ID</param> /// <returns>Successful?</returns> public static bool addProductSupplier(int productId, int supplierId) { bool success = false;//bool success value returned to tell if query successful //Sql connection block to connect to TravelExpertsDB; closes connection at end of block using (SqlConnection connection = TravelExpertsDB.GetConnection()) { string query = "INSERT INTO Products_Suppliers " + "(ProductId,SupplierId) " + "VALUES (@ProductId, @SupplierId) ; "; // Adds all parameters to new SQL Command using (SqlCommand cmd = new SqlCommand(query, connection)) { connection.Open(); // Adds all parameters to new SQL Command cmd.Parameters.AddWithValue("@ProductId", productId); cmd.Parameters.AddWithValue("@SupplierId", supplierId); success = cmd.ExecuteNonQuery() > 0; // Success if rows have been deleted } // cmd object recycled }// connection object recycled return(success); }
/// <summary> /// Public static class method for editing products /// </summary> /// <param name="oldProduct">Product class object for getting old values</param> /// <param name="newProduct">Product class object for getting new values</param> /// <returns></returns> public static bool UpdateProduct(Product oldProduct, Product newProduct) { bool success; // Opens connection using (SqlConnection connection = TravelExpertsDB.GetConnection()) { string query = "UPDATE Products " + "SET " + "ProdName = @NewProductName " + "WHERE " + "ProductId = @OldProductId AND " + "ProdName = @OldProdName ; "; // Creates command and adds all proper parameters using (SqlCommand cmd = new SqlCommand(query, connection)) { connection.Open(); //Add parameters for sql query cmd.Parameters.AddWithValue("@NewProductName", newProduct.ProductName); cmd.Parameters.AddWithValue("@OldProductId", oldProduct.ProductId); cmd.Parameters.AddWithValue("@OldProdName", oldProduct.ProductName); success = cmd.ExecuteNonQuery() > 0; // Success if rows changed } // cmd object recycled }// connection object recycled return(success); }
// get all ProductSupplier Ids public static List <Products_Suppliers> ProductSuppliers(int i) { List <Products_Suppliers> productSupplierList = new List <Products_Suppliers>(); using (SqlConnection connection = TravelExpertsDB.GetConnection()) { string query = "SELECT ps.ProductSupplierId, p.ProductId, p.ProdName, s.SupplierId, s.SupName " + "FROM Products_Suppliers AS ps " + "INNER JOIN Products AS p on ps.ProductId = p.ProductId " + "INNER JOIN Suppliers AS s on ps.SupplierId = s.SupplierId " + "where p.ProductId = " + i + " ORDER BY SupName"; using (SqlCommand cmd = new SqlCommand(query, connection)) { connection.Open(); SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); while (reader.Read()) { Products_Suppliers productSupplierItem = new Products_Suppliers(); productSupplierItem.ProductSupplierId = (int)reader[0]; productSupplierItem.ProductId = (int)reader[1]; productSupplierItem.ProdName = reader[2].ToString(); productSupplierItem.SupplierId = (int)reader[3]; productSupplierItem.SupName = reader[4].ToString(); productSupplierList.Add(productSupplierItem); } return(productSupplierList); } } }
public static List <Product> GetEngagedProducts() { List <Product> engagedProducts = new List <Product>(); // empty list Product engProd; // object instance for reading using (SqlConnection connection = TravelExpertsDB.GetConnection()) { string selectQuery = "SELECT DISTINCT prod.ProductId, prod.ProdName " + "FROM Products AS prod " + "INNER JOIN Products_Suppliers AS prs ON prod.ProductId=prs.ProductId " + "INNER JOIN Packages_Products_Suppliers AS pps " + "ON prs.ProductSupplierId=pps.ProductSupplierId " + "WHERE pps.PackageId IS NOT NULL " + "ORDER BY prod.ProdName"; using (SqlCommand cmd = new SqlCommand(selectQuery, connection)) { connection.Open(); SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); while (reader.Read()) // if products for given ID exists { engProd = new Product(); engProd.ProductId = (int)reader["ProductId"]; engProd.ProdName = reader["ProdName"].ToString(); engagedProducts.Add(engProd); } } } return(engagedProducts); }
/// <summary> /// Gets a list of suppliers by product id /// </summary> /// <returns>Supplier list</returns> public static List <Supplier> getSuppliersByProductId(int id) { List <Supplier> suppliers = new List <Supplier>(); SqlConnection connection = TravelExpertsDB.GetConnection(); // Gets suppliers related to product id String query = "SELECT ps.SupplierId, SupName " + "FROM " + "Products_Suppliers ps " + "JOIN Suppliers s " + "ON ps.SupplierId = s.SupplierId " + "WHERE ps.ProductId = @id " + "ORDER BY ps.SupplierId "; using (SqlCommand command = new SqlCommand(query, connection)) { connection.Open(); command.Parameters.AddWithValue("@id", id); SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection); // Add all found suppliers to list while (reader.Read()) { suppliers.Add(new Supplier((int)reader["SupplierId"], reader["SupName"].ToString())); } connection.Close(); } return(suppliers); }
/// <summary> /// Generates a list of supplier names. /// </summary> /// <returns>List of supplier names.</returns> public static List <string> GetAllSuppliers() { List <string> suppliersList = new List <string>(); suppliersList.Add(""); using (SqlConnection connection = TravelExpertsDB.GetConnection()) { string query = "SELECT SupName " + "FROM Suppliers " + "ORDER BY SupName"; using (SqlCommand cmd = new SqlCommand(query, connection)) { connection.Open(); using (SqlDataReader dr = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection)) { while (dr.Read()) { string supplierName = (string)dr["SupName"]; suppliersList.Add(supplierName); } } } } return(suppliersList); }
/// <summary> /// Gets sorted list of all product suppliers in DB. Key is prodsupplier id. Value is formatted string describing the pair. /// </summary> /// <returns>sorted list</returns> public static SortedList <int, string> getProductsSuppliersIdAndString() { SortedList <int, string> productsSuppliers = new SortedList <int, string>(); SqlConnection connection = TravelExpertsDB.GetConnection(); String query = "SELECT ProductSupplierId, ProdName, SupName " + "FROM " + "Products_Suppliers ps " + "JOIN Products pr " + "ON ps.ProductId = pr.ProductId " + "JOIN Suppliers s " + "ON ps.SupplierId = s.SupplierId ; "; using (SqlCommand command = new SqlCommand(query, connection)) { connection.Open(); SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection); while (reader.Read()) { productsSuppliers.Add((int)reader["ProductSupplierId"], reader["ProdName"].ToString() + " from " + reader["SupName"].ToString()); } connection.Close(); } return(productsSuppliers); }
/// <summary> /// Gets a list of products by supplier id /// </summary> /// <returns>Product list</returns> public static List <Product> getProductsBySupplierId(int id) { List <Product> products = new List <Product>(); SqlConnection connection = TravelExpertsDB.GetConnection(); // Gets products related to supplier id String query = "SELECT ps.ProductId, ProdName " + "FROM " + "Products_Suppliers ps " + "JOIN Products pr " + "ON ps.ProductId = pr.ProductId " + "WHERE ps.SupplierId = @id " + "ORDER BY ps.ProductID ; "; using (SqlCommand command = new SqlCommand(query, connection)) { connection.Open(); command.Parameters.AddWithValue("@id", id); SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection); // Add all found products to list while (reader.Read()) { products.Add(new Product((int)reader["ProductId"], reader["ProdName"].ToString())); } connection.Close(); } return(products); }
/// <summary> /// Removes product supplier pair to DB /// </summary> /// <param name="productId">Product ID</param> /// <param name="supplierId">Supplier ID</param> /// <returns>Successful?</returns> public static bool removeProductSupplier(int productId, int supplierId) { bool success = false;//bool success value returned to tell if query successful //Sql connection block to connect to TravelExpertsDB; closes connection at end of block using (SqlConnection connection = TravelExpertsDB.GetConnection()) { string query = "DELETE FROM Products_Suppliers " + "WHERE " + "ProductId = @ProductId AND " + "SupplierId = @SupplierId ; "; //sql command block; disposes command at end of block using (SqlCommand cmd = new SqlCommand(query, connection)) { connection.Open(); //Adds all parameters to new SQL Command cmd.Parameters.AddWithValue("@ProductId", productId); cmd.Parameters.AddWithValue("@SupplierId", supplierId); success = cmd.ExecuteNonQuery() > 0; // Success if rows have been deleted } // cmd object recycled }// connection object recycled return(success); }
/// <summary> /// Public static class method for deleting products /// </summary> /// <param name="oldProduct">Product class object for products being deleted</param> /// <returns></returns> public static bool DeleteProduct(Product oldProduct) { bool success = false; // Opens connection using (SqlConnection connection = TravelExpertsDB.GetConnection()) { string query = "DELETE FROM Products " + "WHERE " + "ProductId = @OldProductId AND " + "ProdName = @OldProductName ; "; // Adds all parameters to new SQL Command using (SqlCommand cmd = new SqlCommand(query, connection)) { connection.Open(); //Add parameters for sql query cmd.Parameters.AddWithValue("@OldProductId", oldProduct.ProductId); cmd.Parameters.AddWithValue("@OldProductName", oldProduct.ProductName); success = cmd.ExecuteNonQuery() > 0; // Success if rows have been deleted } // cmd object recycled }// connection object recycled return(success); }
/// <summary> /// Delete's a record in Packages_Products_Suppliers /// </summary> /// <param name="packageId">Package ID of target record</param> /// <param name="prodName">Product name of target record</param> /// <param name="supName">Supplier name of target record</param> /// <returns>True if delete successful, false if not.</returns> public static bool DeletePPSWithPackageIdThenConfirm(int packageId, string prodName, string supName) { bool successfullyDeleted; using (SqlConnection connection = TravelExpertsDB.GetConnection()) { string deleteStatement = "DELETE FROM Packages_Products_Suppliers " + "WHERE PackageId = " + packageId.ToString() + "AND ProductId = (SELECT ProductId FROM Products WHERE ProdName = '" + prodName + "') " + "AND SupplierId = (SELECT SupplierId FROM Suppliers WHERE SupName = '" + supName + "')"; using (SqlCommand cmd = new SqlCommand(deleteStatement, connection)) { connection.Open(); if (cmd.ExecuteNonQuery() > 0) { successfullyDeleted = true; } else { successfullyDeleted = false; } } } return(successfullyDeleted); }
/// <summary> /// Generates a filtered list of product names. /// </summary> /// <returns>Filtered list of product names.</returns> public static List <string> GetFilteredProducts() { List <string> productsList = new List <string>(); productsList.Add(""); using (SqlConnection connection = TravelExpertsDB.GetConnection()) { string query = "SELECT ProdName " + "FROM Products " + "ORDER BY ProdName"; using (SqlCommand cmd = new SqlCommand(query, connection)) { connection.Open(); using (SqlDataReader dr = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection)) { while (dr.Read()) { string prodName = (string)dr["ProdName"]; productsList.Add(prodName); } } } } return(productsList); }
public static List <Product> GetProducts() { List <Product> products = new List <Product>(); // empty list Product prod; // object instance for reading using (SqlConnection connection = TravelExpertsDB.GetConnection()) { string selectQuery = "SELECT ProductId, ProdName " + "FROM Products"; using (SqlCommand cmd = new SqlCommand(selectQuery, connection)) { connection.Open(); SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); while (reader.Read()) // if products for given ID exists { prod = new Product(); prod.ProductId = (int)reader["ProductId"]; prod.ProdName = reader["ProdName"].ToString(); products.Add(prod); } } } return(products); }
// retrieve productSupplier info with given productSupplier Id public static Products_Suppliers GetProductSupplierById(int productSupplierId) { Products_Suppliers productSupplier = null; //create the conneciton using (SqlConnection connection = TravelExpertsDB.GetConnection()) { // create select command string query = "SELECT ProductSupplierId, ps.ProductId, ps.SupplierId, ProdName, SupName " + "FROM Products_Suppliers ps " + "JOIN Products p ON ps.ProductId=p.ProductId " + "JOIN Suppliers s ON ps.SupplierId=s.SupplierId " + "WHERE ProductSupplierId=@ProductSupplierId"; // any exception not handled here is automaticlly thrown to the form // where the method was called using (SqlCommand cmd = new SqlCommand(query, connection)) { //supply parameter value cmd.Parameters.AddWithValue("@ProductSupplierId", productSupplierId); //open the connection connection.Open(); //run the command SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); // close connection as soon as done with reading //build product object to return if (reader.Read()) // if there is a product with this ID { productSupplier = new Products_Suppliers(); productSupplier.ProductSupplierId = (int)reader["ProductSupplierId"]; int col_pro = reader.GetOrdinal("ProductId"); //column number of ProductId if (reader.IsDBNull(col_pro)) // if reader contains DBNull in this column { productSupplier.ProductId = null; // make it null in the object } else // it is not null { productSupplier.ProductId = (int)reader["ProductId"]; } int col_sup = reader.GetOrdinal("SupplierId"); //column number of SupplierId if (reader.IsDBNull(col_sup)) // if reader contains DBNull in this column { productSupplier.SupplierId = null; // make it null in the object } else // it is not null { productSupplier.SupplierId = (int)reader["SupplierId"]; } productSupplier.ProdName = reader["ProdName"].ToString(); productSupplier.SupName = reader["SupName"].ToString(); } } // command object recycled } // connection object recyled return(productSupplier); }
// retrieve package info with given package ID public static Packages GetPackageById(int packageId) { Packages package = null; //create the conneciton using (SqlConnection connection = TravelExpertsDB.GetConnection()) { // create select command string query = "SELECT PackageId, PkgName,PkgStartDate, PkgEndDate, " + "PkgDesc, PkgBasePrice, PkgAgencyCommission " + "FROM Packages" + "WHERE PackageId=@PackageId"; // any exception not handled here is automaticlly thrown to the form // where the method was called using (SqlCommand cmd = new SqlCommand(query, connection)) { //supply parameter value cmd.Parameters.AddWithValue("@PackageId", packageId); //open the connection connection.Open(); //run the command SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); // close connection as soon as done with reading //build product object to return if (reader.Read()) // if there is a product with this ID { package = new Packages(); package.PackageId = (int)reader["PackageId"]; package.PkgName = reader["PkgName"].ToString(); int col_st = reader.GetOrdinal("PkgStartDate"); //column number of Start Date if (reader.IsDBNull(col_st)) // if reader contains DBNull in this column { package.PkgStartDate = null; // make it null in the object } else // it is not null { package.PkgStartDate = Convert.ToDateTime(reader["PkgStartDate"]); } int col_en = reader.GetOrdinal("PkgEndDate"); //column number of End Date if (reader.IsDBNull(col_en)) // if reader contains DBNull in this column { package.PkgEndDate = null; // make it null in the object } else // it is not null { package.PkgEndDate = Convert.ToDateTime(reader["PkgStartDate"]); } package.PkgDesc = reader["PkgDesc"].ToString(); package.PkgBasePrice = (decimal)reader["PkgBasePrice"]; package.PkgAgencyCommission = (decimal)reader["PkgAgencyCommission"]; } } // command object recycled } // connection object recyled return(package); }
public static List <Packages> PackageDetail(int i) { List <Packages> package = new List <Packages>(); using (SqlConnection connection = TravelExpertsDB.GetConnection()) { string query = "SELECT PackageId, PkgName, PkgStartDate, PkgEndDate, PkgDesc, PkgBasePrice, PkgAgencyCommission " + "FROM Packages " + "WHERE PackageId = " + i; using (SqlCommand cmd = new SqlCommand(query, connection)) { connection.Open(); SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); while (reader.Read()) { Packages pkg = new Packages(); // for reading pkg.PackageId = (int)reader["PackageId"]; pkg.PkgName = reader["PkgName"].ToString(); int col_std = reader.GetOrdinal("PkgStartDate"); //column number of Start Date if (reader.IsDBNull(col_std)) // if reader contains DBNull in this column { pkg.PkgStartDate = null; // make it null in the object } else // it is not null { pkg.PkgStartDate = (DateTime)reader["PkgStartDate"]; } int col_end = reader.GetOrdinal("PkgEndDate"); //column number of End Date if (reader.IsDBNull(col_end)) // if reader contains DBNull in this column { pkg.PkgEndDate = null; // make it null in the object } else // it is not null { pkg.PkgEndDate = (DateTime)reader["PkgEndDate"]; } pkg.PkgDesc = reader["PkgDesc"].ToString(); pkg.PkgBasePrice = (Decimal)reader["PkgBasePrice"]; int col_comm = reader.GetOrdinal("PkgAgencyCommission"); //column number of End Date if (reader.IsDBNull(col_comm)) // if reader contains DBNull in this column { pkg.PkgAgencyCommission = null; // make it null in the object } else // it is not null { pkg.PkgAgencyCommission = (Decimal)reader["PkgAgencyCommission"]; } package.Add(pkg); } return(package); } } }
///Written by Raymond Edeamrere public static bool AddSupplier(Supplier newSupplier) { using (SqlConnection connection = TravelExpertsDB.GetConnection()) { connection.Open(); SqlCommand cmd = connection.CreateCommand(); SqlTransaction addNewPkgTran; int rowsAffected; string insert = "INSERT INTO Suppliers (SupplierId, SupName )" + "VALUES (@SupplierId, @SupName)"; // start transaction addNewPkgTran = connection.BeginTransaction(); // set up cmd properties cmd.Connection = connection; cmd.Transaction = addNewPkgTran; cmd.CommandText = insert; // add non-nullable parameters cmd.Parameters.AddWithValue("@SupplierId", newSupplier.SupplierId); // nullable parameters if (string.IsNullOrEmpty(newSupplier.SupName)) { cmd.Parameters.AddWithValue("@SupName", DBNull.Value); } else { cmd.Parameters.AddWithValue("@SupName", newSupplier.SupName); } try { // run query rowsAffected = cmd.ExecuteNonQuery(); // insert failed if (rowsAffected != 1) { addNewPkgTran.Rollback(); return(false); } // commit transaction addNewPkgTran.Commit(); return(true); } catch (Exception ex) { throw ex; } } }
// insert a new product supplier into Products_Suppliers table // return new product_supplier Id public static int AddProductSupplier(Products_Suppliers productSupplier) { int productSupplierId = 0; // create the connection using (SqlConnection connection = TravelExpertsDB.GetConnection()) { // create INSERT command // CustomerID is IDENTITY so no value provided string insertStatement = "INSERT INTO Products_Suppliers(ProductId, SupplierId) " + "OUTPUT inserted.ProductSupplierId " + "VALUES(@ProductId, @SupplierId)"; using (SqlCommand cmd = new SqlCommand(insertStatement, connection)) { //supply paramter value, this way can avoid sql injection problem if (productSupplier.ProductId == null) { cmd.Parameters.AddWithValue("@ProductId", DBNull.Value); } else { cmd.Parameters.AddWithValue("@ProductId", productSupplier.ProductId); } if (productSupplier.SupplierId == null) { cmd.Parameters.AddWithValue("@SupplierId", DBNull.Value); } else { cmd.Parameters.AddWithValue("@SupplierId", productSupplier.SupplierId); } // execute INSERT command try { // open the connection connection.Open(); // execute insert command and get inserted ID productSupplierId = (int)cmd.ExecuteScalar(); } catch (Exception ex) { throw ex; } finally // executes always { connection.Close(); } } } return(productSupplierId); }
/// <summary> /// Add a new package to DB /// </summary> /// <param name="newPkg">package to add</param> /// <returns>true if successful, false otherwise</returns> public static bool AddPackage(Package newPkg) { using (SqlConnection connection = TravelExpertsDB.GetConnection()) { connection.Open(); SqlCommand cmd = connection.CreateCommand(); SqlTransaction addNewPkgTran; int rowsAffected; string insert = "INSERT INTO Packages (PkgName, PkgStartDate, PkgEndDate, " + "PkgDesc, PkgBasePrice, PkgAgencyCommission) " + "VALUES (@PkgName, @NewPkgStartDate, @NewPkgEndDate, " + "@NewPkgDesc, @NewPkgBasePrice, @NewPkgAgencyCommission)"; // start transaction addNewPkgTran = connection.BeginTransaction(); // set up cmd properties cmd.Connection = connection; cmd.Transaction = addNewPkgTran; cmd.CommandText = insert; // add non-nullable parameters cmd.Parameters.AddWithValue("@PkgName", newPkg.PkgName); cmd.Parameters.AddWithValue("@NewPkgBasePrice", newPkg.PkgBasePrice); // for nullable properties, have to check for null ProcessNewPkgNullables(cmd, newPkg, "New"); try { // run query rowsAffected = cmd.ExecuteNonQuery(); // insert failed if (rowsAffected != 1) { addNewPkgTran.Rollback(); return(false); } // commit transaction addNewPkgTran.Commit(); return(true); } catch (Exception ex) { throw ex; } } // close and recycle connection }
// update order: current - before update, updated - new data public static bool UpdatePackage(Package current, Package updated) { bool success = false; // no success yet using (SqlConnection connection = TravelExpertsDB.GetConnection()) { string updateStatement = "UPDATE Packages SET " + "PkgName = @NewPkgName, " + "PkgStartDate = @NewPkgStartDate, " + "PkgEndDate = @NewPkgEndDate, " + "PkgDesc = @NewPkgDesc, " + "PkgBasePrice = @NewPkgBasePrice, " + "PkgAgencyCommission = @NewPkgAgencyCommission " + "WHERE PackageId = @OldPackageId " + " AND PkgName = @OldPkgName " + " AND PkgStartDate = @OldPkgStartDate " + " AND PkgEndDate = @OldPkgEndDate " + " AND PkgDesc = @OldPkgDesc " + " AND PkgBasePrice = @OldPkgBasePrice " + " AND PkgAgencyCommission = @OldPkgAgencyCommission"; using (SqlCommand cmd = new SqlCommand(updateStatement, connection)) { // provide values for parameters cmd.Parameters.AddWithValue("@NewPkgName", updated.PkgName); cmd.Parameters.AddWithValue("@NewPkgStartDate", updated.PkgStartDate); cmd.Parameters.AddWithValue("@NewPkgEndDate", updated.PkgEndDate); cmd.Parameters.AddWithValue("@NewPkgDesc", updated.PkgDesc); cmd.Parameters.AddWithValue("@NewPkgBasePrice", updated.PkgBasePrice); cmd.Parameters.AddWithValue("@NewPkgAgencyCommission", updated.PkgAgencyCommission); cmd.Parameters.AddWithValue("@OldPackageId", current.PackageId); cmd.Parameters.AddWithValue("@OldPkgName", current.PkgName); cmd.Parameters.AddWithValue("@OldPkgStartDate", current.PkgStartDate); cmd.Parameters.AddWithValue("@OldPkgEndDate", current.PkgEndDate); cmd.Parameters.AddWithValue("@OldPkgDesc", current.PkgDesc); cmd.Parameters.AddWithValue("@OldPkgBasePrice", current.PkgBasePrice); cmd.Parameters.AddWithValue("@OldPkgAgencyCommission", current.PkgAgencyCommission); // open connection connection.Open(); // execute UPDATE command int count = cmd.ExecuteNonQuery(); if (count > 0) { success = true; } } // command object recycled } // connection closed and recycled return(success); }