public static List <SupplierContact> GetSupplierContact(int SupplierContactId)
{
List <SupplierContact> supplierContacts = new List <SupplierContact>();
SupplierContact nextSupplierContact;
SqlConnection connection = UnversalDBControls.GetConnection();
string selectstatement = "SELECT * FROM [SupplierContacts] where SupplierId = @SupplierId";
SqlCommand selectCommand = new SqlCommand(selectstatement, connection);
// selectCommand.Parameters.AddWithValue("@SupplierId", SupplierId);
try
{
connection.Open();
SqlDataReader reader = selectCommand.ExecuteReader();
while (reader.Read())
{
nextSupplierContact = new SupplierContact();
nextSupplierContact.SupplierContactId = Convert.ToInt32(reader["SupplierContactId"]);
nextSupplierContact.SupConFirstName = Convert.ToString(reader["SupConFirstName"]);
nextSupplierContact.SupConLastName = Convert.ToString(reader["SupConLastName"]);
nextSupplierContact.SupConCompany = Convert.ToString(reader["SupConCompany"]);
nextSupplierContact.SupConAddress = Convert.ToString(reader["SupConAddress"]);
nextSupplierContact.SupConCity = Convert.ToString(reader["SupConCity"]);
nextSupplierContact.SupConProvince = Convert.ToString(reader["SupConProvince"]);
nextSupplierContact.SupConPostal = Convert.ToString(reader["SupConPostal"]);
nextSupplierContact.SupConCountry = Convert.ToString(reader["SupConCountry"]);
nextSupplierContact.SupConBusPhone = Convert.ToString(reader["SupConBusPhone"]);
nextSupplierContact.SupConFax = Convert.ToString(reader["SupConFax"]);
nextSupplierContact.SupConEmail = Convert.ToString(reader["SupConEmail"]);
nextSupplierContact.SupConURL = Convert.ToString(reader["SupConURL"]);
nextSupplierContact.AffiliationId = Convert.ToString(reader["AffiliationId"]);
nextSupplierContact.SupplierId = Convert.ToInt32(reader["SupplierId"]);
supplierContacts.Add(nextSupplierContact);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(supplierContacts);
}
public static int AddSupplierContact(SupplierContact nextContact)
{
SqlConnection con = UnversalDBControls.GetConnection();
string insertStatement = "INSERT INTO SupplierContacts (SupplierContactId, SupConFirstName, SupConLastName, SupConCompany, SupConAddress, SupConCity, SupConProvince, SupConPostal, SupConCountry, SupConBusPhone, SupConEmail, SupConURL, AffiliationId, SupplierId) " +
"VALUES(@SupplierContactId, @SupConFirstName, @SupConLastName, @SupConCompany, @SupConAddress, @SupConCity, @SupConProvince, @SupConPostal, @SupConCountry, @SupConBusPhone, @SupConEmail, @SupConURL, @AffiliationId, @SupplierId)";
SqlCommand cmd = new SqlCommand(insertStatement, con);
cmd.Parameters.AddWithValue("@SupplierContactId", nextContact.SupplierContactId);
cmd.Parameters.AddWithValue("@SupConFirstName", nextContact.SupplierContactId);
cmd.Parameters.AddWithValue("@SupConLastName", nextContact.SupplierContactId);
cmd.Parameters.AddWithValue("@SupConCompany", nextContact.SupplierContactId);
cmd.Parameters.AddWithValue("@SupConAddress", nextContact.SupplierContactId);
cmd.Parameters.AddWithValue("@SupConCity", nextContact.SupplierContactId);
cmd.Parameters.AddWithValue("@SupConProvince", nextContact.SupplierContactId);
cmd.Parameters.AddWithValue("@SupConPostal", nextContact.SupplierContactId);
cmd.Parameters.AddWithValue("@SupConCountry", nextContact.SupplierContactId);
cmd.Parameters.AddWithValue("@SupConBusPhone", nextContact.SupplierContactId);
cmd.Parameters.AddWithValue("@SupConEmail", nextContact.SupplierContactId);
cmd.Parameters.AddWithValue("@SupConURL", nextContact.SupplierContactId);
cmd.Parameters.AddWithValue("@AffiliationId", nextContact.SupplierContactId);
cmd.Parameters.AddWithValue("@SupplierId", nextContact.SupplierContactId);
try
{
con.Open();
cmd.ExecuteNonQuery();
string selectQuery = "SELECT IDENT_CURRENT('SupplierContacts') FROM SupplierContacts";
SqlCommand selectCmd = new SqlCommand(selectQuery, con);
int SupplierContactID = Convert.ToInt32(selectCmd.ExecuteScalar());
return(SupplierContactID);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
con.Close();
}
}
public static bool UpdateSupplierContact(SupplierContact oldSup, SupplierContact newSup)
{
SqlConnection con = UnversalDBControls.GetConnection();
string updateStatement = "UPDATE SupplierContacts " +
"SET SupConFirstName = @NewSupConFirstName, " +
"SET SupConLastName = @NewSupConLastName, " +
"SET SupConCompany = @NewSupConCompany, " +
"SET SupConAddress = @NewSupConAddress, " +
"SET SupConCity = @NewSupConCity, " +
"SET SupConProvince = @NewSupConProvince " +
"SET SupConPostal = @NewSupConPostal " +
"SET SupConCountry = @NewSupConCountry " +
"SET SupConBusPhone = @NewSupConBusPhone " +
"SET SupConFax = @NewSupConFax " +
"SET SupConEmail = @NewSupConEmail " +
"SET SupConURL = @NewSupConURL " +
"SET AffiliationId = @NewAffiliationId " +
"WHERE SupplierId = @OldSupplierId " +
"AND SupConFirstName = @OldSupConFirstName " +
"AND SupConLastName = @OldSupConLastName " +
"AND SupConCompany = @OldSupConCompany " +
"AND SupConAddress = @OldSupConAddress " +
"AND SupConCity = @OldSupConCity " +
"AND SupConProvince = @OldSupConProvince " +
"AND SupConPostal = @OldSupConPostal " +
"AND SupConCountry = @OldSupConCountry " +
"AND SupConBusPhone = @OldSupConBusPhone " +
"AND SupConFax = @OldSupConFax " +
"AND SupConEmail = @OldSupConEmail " +
"AND SupConURL = @OldSupConURL " +
"AND AffiliationId = @OldAffiliationId";
SqlCommand cmd = new SqlCommand(updateStatement, con);
cmd.Parameters.AddWithValue("@NewSupConFirstName", newSup.SupConFirstName);
cmd.Parameters.AddWithValue("@NewSupConLastName", newSup.SupConLastName);
cmd.Parameters.AddWithValue("@NewSupConCompany", newSup.SupConCompany);
cmd.Parameters.AddWithValue("@NewSupConAddress", newSup.SupConAddress);
cmd.Parameters.AddWithValue("@NewSupConCity", newSup.SupConCity);
cmd.Parameters.AddWithValue("@NewSupConProvince", newSup.SupConProvince);
cmd.Parameters.AddWithValue("@NewSupConPostal", newSup.SupConPostal);
cmd.Parameters.AddWithValue("@NewSupConCountry", newSup.SupConCountry);
cmd.Parameters.AddWithValue("@NewSupConBusPhone", newSup.SupConBusPhone);
cmd.Parameters.AddWithValue("@NewSupConFax", newSup.SupConFax);
cmd.Parameters.AddWithValue("@NewSupConEmail", newSup.SupConEmail);
cmd.Parameters.AddWithValue("@NewSupConURL", newSup.SupConURL);
cmd.Parameters.AddWithValue("@NewAffiliationId", newSup.AffiliationId);
cmd.Parameters.AddWithValue("@OldSupplierId", oldSup.SupplierId);
cmd.Parameters.AddWithValue("@OldSupConFirstName", oldSup.SupConFirstName);
cmd.Parameters.AddWithValue("@OldSupConLastName", oldSup.SupConLastName);
cmd.Parameters.AddWithValue("@OldSupConCompany", oldSup.SupConCompany);
cmd.Parameters.AddWithValue("@OldSupConAddress", oldSup.SupConAddress);
cmd.Parameters.AddWithValue("@OldSupConCity", oldSup.SupConCity);
cmd.Parameters.AddWithValue("@OldSupConProvince", oldSup.SupConProvince);
cmd.Parameters.AddWithValue("@OldSupConPostal", oldSup.SupConPostal);
cmd.Parameters.AddWithValue("@OldSupConCountry", oldSup.SupConCountry);
cmd.Parameters.AddWithValue("@OldSupConBusPhone", oldSup.SupConBusPhone);
cmd.Parameters.AddWithValue("@OldSupSupConFax", oldSup.SupConFax);
cmd.Parameters.AddWithValue("@OldSupConEmail", oldSup.SupConEmail);
cmd.Parameters.AddWithValue("@OldSupConURL", oldSup.SupConURL);
cmd.Parameters.AddWithValue("@OldAffiliationId", oldSup.AffiliationId);
try
{
con.Open();
if (cmd.ExecuteNonQuery() > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
con.Close();
}
}
