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;
}