public static bool AddSupplier(Supplier supplier) { SqlConnection connection = TravelExpertsDB.GetConnection(); string insertStatement = "INSERT Suppliers " + "(SupName, SupplierContactId, SupConFirstName, SupConLastName, SupConCompany, SupConAddress, SupConCity, SupConProv, SupConPostal, SupConCountry, SupConBusPhone, SupConFax, SupConEmail, SupConURL) " + "VALUES (@SupName, @SupplierContactId, @SupConFirstName, @SupConLastName, @SupConCompany, @SupConAddress, @SupConCity, @SupConProv, @SupConPostal, @SupConCountry, @SupConBusPhone, @SupConFax, @SupConEmail, @SupConURL)"; SqlCommand insertCommand = new SqlCommand(insertStatement, connection); insertCommand.Parameters.AddWithValue( "@SupName", supplier.SupName); insertCommand.Parameters.AddWithValue( "@SupplierContactId", supplier.SupplierContactId); insertCommand.Parameters.AddWithValue( "@SupConFirstName", supplier.SupConFirstName); insertCommand.Parameters.AddWithValue( "@SupConLastName", supplier.SupConLastName); insertCommand.Parameters.AddWithValue( "@SupConCompany", supplier.SupConCompany); insertCommand.Parameters.AddWithValue( "@SupConAddress", supplier.SupConAddress); insertCommand.Parameters.AddWithValue( "@SupConCity", supplier.SupConCity); insertCommand.Parameters.AddWithValue( "@SupConProv", supplier.SupConProv); insertCommand.Parameters.AddWithValue( "@SupConPostal", supplier.SupConPostal); insertCommand.Parameters.AddWithValue( "@SupConCountry", supplier.SupConCountry); insertCommand.Parameters.AddWithValue( "@SupConBusPhone", supplier.SupConBusPhone); insertCommand.Parameters.AddWithValue( "@SupConFax", supplier.SupConFax); insertCommand.Parameters.AddWithValue( "@SupConEmail", supplier.SupConEmail); insertCommand.Parameters.AddWithValue( "@SupConURL", supplier.SupConURL); try { connection.Open(); int count = insertCommand.ExecuteNonQuery(); // Inform user and return execution status if (count > 0) { // MessageBox.Show("Inserted record: " + package.PackageId); return true; } else return false; } catch (SqlException ex) { throw ex; } finally { connection.Close(); } }
//function to delete the SQL data public static bool DeleteSupplier(Supplier supplier) { SqlConnection connection = TravelExpertsDB.GetConnection(); //connect the SQL7 string deleteForeign = "DELETE FROM SupplierContacts " + "WHERE SupplierId = @SupplierId "; SqlCommand deleteForeignCommand = new SqlCommand(deleteForeign, connection); //call the SQL deleteForeign command to delete the dependent table deleteForeignCommand.Parameters.AddWithValue( "@SupplierId", supplier.SupplierId); string deleteStatement = "DELETE FROM Suppliers " + "WHERE SupplierId = @SupplierId "; SqlCommand deleteCommand = new SqlCommand(deleteStatement, connection); //call the SQL delete command to delete the data deleteCommand.Parameters.AddWithValue( "@SupplierId", supplier.SupplierId); try { connection.Open(); int count2 = deleteForeignCommand.ExecuteNonQuery(); int count = deleteCommand.ExecuteNonQuery(); if (count > 0 && count2 > 0) return true; else return false; } catch (SqlException ex) { throw ex; } finally { connection.Close(); } }
public static bool UpdateSupplier(Supplier supplier) { SqlConnection connection = TravelExpertsDB.GetConnection(); string updateStatement = "UPDATE Suppliers SET " + "SupName = @SupName, " + "SupplierContactId = @SupplierContactId, " + "SupConFirstName = @SupConFirstName, " + "SupConLastName = @SupConLastName, " + "SupConCompany = @SupConCompany, " + "SupConAddress = @SupConAddress " + "SupConCity = @SupConCity " + "SupConProv = @SupConProv " + "SupConPostal = @SupConPostal " + "SupConCountry = @SupConCountry " + "SupConBusPhone = @SupConBusPhone " + "SupConFax = @SupConFax " + "SupConEmail = @SupConEmail " + "SupConURL = @SupConURL " + "WHERE SupplierId = @SupplierId"; SqlCommand updateCommand = new SqlCommand(updateStatement, connection); updateCommand.Parameters.AddWithValue( "@SupName", supplier.SupName); updateCommand.Parameters.AddWithValue( "@SupplierContactId", supplier.SupplierContactId); updateCommand.Parameters.AddWithValue( "@SupConFirstName", supplier.SupConFirstName); updateCommand.Parameters.AddWithValue( "@SupConLastName", supplier.SupConLastName); updateCommand.Parameters.AddWithValue( "@SupConCompany", supplier.SupConCompany); updateCommand.Parameters.AddWithValue( "@SupConAddress", supplier.SupConAddress); updateCommand.Parameters.AddWithValue( "@SupConCity", supplier.SupConCity); updateCommand.Parameters.AddWithValue( "@SupConProv", supplier.SupConProv); updateCommand.Parameters.AddWithValue( "@SupConPostal", supplier.SupConPostal); updateCommand.Parameters.AddWithValue( "@SupConCountry", supplier.SupConCountry); updateCommand.Parameters.AddWithValue( "@SupConBusPhone", supplier.SupConBusPhone); updateCommand.Parameters.AddWithValue( "@SupConFax", supplier.SupConFax); updateCommand.Parameters.AddWithValue( "@SupConEmail", supplier.SupConEmail); updateCommand.Parameters.AddWithValue( "@SupConURL", supplier.SupConURL); try { connection.Open(); int count = updateCommand.ExecuteNonQuery(); if (count > 0) return true; else return false; } catch (SqlException ex) { throw ex; } finally { connection.Close(); } }
/*Chen Code End*/ /* Author: Pooja */ public static List<Supplier> GetSuppliers() { List<Supplier> suppliers = new List<Supplier>(); SqlConnection connection = TravelExpertsDB.GetConnection(); string query = "SELECT Suppliers.SupplierId, SupName " + "FROM Suppliers " + // " INNER JOIN SupplierContacts on SupplierContacts.SupplierId = Suppliers.SupplierId " + "WHERE Suppliers.SupplierId > -1 "; SqlCommand command = new SqlCommand(query, connection); try { // Open db connection and run query connection.Open(); SqlDataReader reader = command.ExecuteReader(); // Read dataset fill Packages list while (reader.Read()) { Supplier supplier = new Supplier(); supplier.SupplierId = (int)reader["SupplierId"]; supplier.SupName = reader["SupName"].ToString(); suppliers.Add(supplier); } return suppliers; } catch (SqlException ex) { throw ex; } finally { connection.Close(); } }
/*Chen Code Start*/ public static List<Supplier> GetSuppliers(string filter) { List<Supplier> suppliers = new List<Supplier>(); SqlConnection connection = TravelExpertsDB.GetConnection(); string query = "SELECT Suppliers.SupplierId, SupName, SupplierContactId, SupConFirstName, SupConLastName, " + " SupConCompany, SupConAddress, SupConCity, SupConProv, SupConPostal, " + " SupConCountry, SupConBusPhone, SupConFax, SupConEmail, SupConURL " + " FROM Suppliers " + " INNER JOIN SupplierContacts on SupplierContacts.SupplierId = Suppliers.SupplierId " + " WHERE Suppliers.SupplierId > -1 " + filter; SqlCommand command = new SqlCommand(query, connection); try { // Open db connection and run query connection.Open(); SqlDataReader reader = command.ExecuteReader(); // Read dataset fill Packages list while (reader.Read()) { Supplier supplier = new Supplier(); supplier.SupplierId = (int)reader["SupplierId"]; supplier.SupName = reader["SupName"].ToString(); supplier.SupplierContactId = (int)reader["SupplierContactId"]; supplier.SupConFirstName = reader["SupConFirstName"].ToString(); supplier.SupConLastName = reader["SupConLastName"].ToString(); supplier.SupConCompany = reader["SupConCompany"].ToString(); supplier.SupConAddress = reader["SupConAddress"].ToString(); supplier.SupConCity = reader["SupConCity"].ToString(); supplier.SupConProv = reader["SupConProv"].ToString(); supplier.SupConPostal = reader["SupConPostal"].ToString(); supplier.SupConCountry = reader["SupConCountry"].ToString(); supplier.SupConBusPhone = reader["SupConBusPhone"].ToString(); supplier.SupConFax = reader["SupConFax"].ToString(); supplier.SupConEmail = reader["SupConEmail"].ToString(); supplier.SupConURL = reader["SupConURL"].ToString(); suppliers.Add(supplier); } return suppliers; } catch (SqlException ex) { throw ex; } finally { connection.Close(); } }
/* Author: Pooja Jairath */ public static List<int> GetSupplierId() { List<int> supplierids = new List<int>(); SqlConnection connection = TravelExpertsDB.GetConnection(); string query = "SELECT SupplierId FROM Suppliers"; SqlCommand command = new SqlCommand(query, connection); try { // Open db connection and run query connection.Open(); SqlDataReader reader = command.ExecuteReader(); // Read dataset fill Packages list while (reader.Read()) { Supplier supplier = new Supplier(); supplier.SupplierId = (int)reader["SupplierId"]; supplierids.Add(supplier.SupplierId); } return supplierids; } catch (SqlException ex) { throw ex; } finally { connection.Close(); } }
public static Supplier GetSupplier(int supplierid) { SqlConnection connection = TravelExpertsDB.GetConnection(); string selectStatement = //"SELECT * from Suppliers WHERE SupplierId = @SupplierId "; "SELECT Suppliers.SupplierId, SupName, SupplierContactId, SupConFirstName, SupConLastName, " + " SupConCompany, SupConAddress, SupConCity, SupConProv, SupConPostal, " + " SupConCountry, SupConBusPhone, SupConFax, SupConEmail, SupConURL " + " FROM Suppliers " + " INNER JOIN SupplierContacts on SupplierContacts.SupplierId = Suppliers.SupplierId " + " WHERE Suppliers.SupplierId > -1 "; SqlCommand selectCommand = new SqlCommand(selectStatement, connection); selectCommand.Parameters.AddWithValue("@SupplierId", supplierid); try { connection.Open(); SqlDataReader suppReader = selectCommand.ExecuteReader(CommandBehavior.SingleRow); if (suppReader.Read()) { Supplier supplier = new Supplier(); supplier.SupplierId = (int)suppReader["SupplierId"]; supplier.SupName = suppReader["SupName"].ToString(); supplier.SupplierContactId = (int)suppReader["SupplierContactId"]; supplier.SupConFirstName = suppReader["SupConFirstName"].ToString(); supplier.SupConLastName = suppReader["SupConLastName"].ToString(); supplier.SupConCompany = suppReader["SupConCompany"].ToString(); supplier.SupConAddress = suppReader["SupConAddress"].ToString(); supplier.SupConCity = suppReader["SupConCity"].ToString(); supplier.SupConProv = suppReader["SupConProv"].ToString(); supplier.SupConPostal = suppReader["SupConPostal"].ToString(); supplier.SupConCountry = suppReader["SupConCountry"].ToString(); supplier.SupConBusPhone = suppReader["SupConBusPhone"].ToString(); supplier.SupConFax = suppReader["SupConFax"].ToString(); supplier.SupConEmail = suppReader["SupConEmail"].ToString(); supplier.SupConURL = suppReader["SupConURL"].ToString(); return supplier; } else { return null; } } catch (SqlException ex) { throw ex; } finally { connection.Close(); } }
// Author: Mark Poffenroth /// <summary> /// Gets list of Supplier objects for specfied Product Id /// </summary> /// <param name="packageid">Product Id used to get Suppliers</param> /// <returns>List of Supplier objects for specfied Product Id.</returns> public static List<Supplier> GetProductSuppliers(int productID) { List<Supplier> suppliers = new List<Supplier>(); // Define connection and query info SqlConnection connection = TravelExpertsDB.GetConnection(); string query = "SELECT s.SupplierId, s.SupName " + " FROM Suppliers s " + " INNER JOIN Products_Suppliers ps ON s.SupplierId = ps.SupplierId " + " WHERE ps.ProductId = @ProductId"; SqlCommand command = new SqlCommand(query, connection); command.Parameters.AddWithValue("@ProductId", productID); try { // Open db connection and run query connection.Open(); SqlDataReader reader = command.ExecuteReader(); // Read dataset fill Packages list while (reader.Read()) { Supplier supplier = new Supplier(); supplier.SupplierId = (int)reader["SupplierId"]; supplier.SupName = reader["SupName"].ToString(); suppliers.Add(supplier); } } catch (SqlException ex) { throw ex; } finally { connection.Close(); } return suppliers; }