// retrieve object with given ID
public static SupplierContacts GetValue(int objID)
{
SupplierContacts obj = null;
// create connection
SqlConnection connection = TravelExperts.GetConection();
// create SELECT command
string query =
"SELECT SupplierContactId, SupConFirstName, SupConLastName, SupConCompany, SupConAddress, SupConCity, SupConProv, SupConPostal, SupConCountry, SupConBusPhone, SupConFax, SupConEmail, SupConURL, AffiliationId, SupplierId " +
"FROM SupplierContacts " +
"WHERE SupplierContactId = @SupplierContactId ";
SqlCommand cmd = new SqlCommand(query, connection);
// suply perameter value
cmd.Parameters.AddWithValue("@SupplierContactId", objID);
// run the SELECT query
try
{
// open the conection
connection.Open();
// run the command
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
// build object object to return
if (reader.Read()) // if there is a object with this ID
{
obj = new SupplierContacts();
obj.SupplierContactId = Convert.ToInt32(reader["SupplierContactId"]);
obj.SupConFirstName = reader["SupConFirstName"].ToString();
obj.SupConLastName = reader["SupConLastName"].ToString();
obj.SupConCompany = reader["SupConCompany"].ToString();
obj.SupConAddress = reader["SupConAddress"].ToString();
obj.SupConCity = reader["SupConCity"].ToString();
obj.SupConProv = reader["SupConProv"].ToString();
obj.SupConPostal = reader["SupConPostal"].ToString();
obj.SupConCountry = reader["SupConCountry"].ToString();
obj.SupConBusPhone = reader["SupConBusPhone"].ToString();
obj.SupConFax = reader["SupConFax"].ToString();
obj.SupConEmail = reader["SupConEmail"].ToString();
obj.SupConURL = reader["SupConURL"].ToString();
obj.AffiliationId = reader["AffiliationId"].ToString();
obj.SupplierId = Convert.ToInt32(reader["SupplierId"]);
}
reader.Close();
}
catch (Exception ex)
{
throw ex;
}
finally // executes always
{
connection.Close();
}
return(obj);
}
// insert new row to table
// return new object
public static int Add(SupplierContacts obj)
{
int custID = 0;
// create connection
SqlConnection connection = TravelExperts.GetConection();
// create INSERT command
// CustomerID is IDENTITY so no value provided
string insertStatment =
"INSERT INTO SupplierContacts(SupplierContactId, SupConFirstName, SupConLastName, SupConCompany, SupConAddress, SupConCity, SupConProv, SupConPostal, SupConCountry, SupConBusPhone, SupConFax, SupConEmail, SupConURL, AffiliationId, SupplierId) " +
"OUTPUT inserted.[SupplierContactId] " +
"VALUES(@SupplierContactId, @SupConFirstName, @SupConLastName, @SupConCompany, @SupConAddress, @SupConCity, @SupConProv, @SupConPostal, @SupConCountry, @SupConBusPhone, @SupConFax, @SupConEmail, @SupConURL, @AffiliationId, @SupplierId) ";
SqlCommand cmd = new SqlCommand(insertStatment, connection);
// suply perameter value
cmd.Parameters.AddWithValue("@SupplierContactId", obj.SupplierContactId);
cmd.Parameters.AddWithValue("@SupConFirstName", obj.SupConFirstName);
cmd.Parameters.AddWithValue("@SupConLastName", obj.SupConLastName);
cmd.Parameters.AddWithValue("@SupConCompany", obj.SupConCompany);
cmd.Parameters.AddWithValue("@SupConAddress", obj.SupConAddress);
cmd.Parameters.AddWithValue("@SupConCity", obj.SupConCity);
cmd.Parameters.AddWithValue("@SupConProv", obj.SupConProv);
cmd.Parameters.AddWithValue("@SupConPostal", obj.SupConPostal);
cmd.Parameters.AddWithValue("@SupConCountry", obj.SupConCountry);
cmd.Parameters.AddWithValue("@SupConBusPhone", obj.SupConBusPhone);
cmd.Parameters.AddWithValue("@SupConFax", obj.SupConFax);
cmd.Parameters.AddWithValue("@SupConEmail", obj.SupConEmail);
cmd.Parameters.AddWithValue("@SupConURL", obj.SupConURL);
cmd.Parameters.AddWithValue("@AffiliationId", obj.AffiliationId);
cmd.Parameters.AddWithValue("@SupplierId", obj.SupplierId);
// execute the INSERT command
try
{
// open the conection
connection.Open();
// execute insert command
custID = (int)cmd.ExecuteScalar();
}
catch (Exception ex)
{
throw ex;
}
finally // executes always
{
connection.Close();
}
// retrieve generated customer nID to return
return(custID);
}
// retrieve all objects
public static List <SupplierContacts> GetAll()
{
// create SELECT command
string query =
"SELECT SupplierContactId, SupConFirstName, SupConLastName, SupConCompany, SupConAddress, SupConCity, SupConProv, SupConPostal, SupConCountry, SupConBusPhone, SupConFax, SupConEmail, SupConURL, AffiliationId, SupplierId " +
"FROM SupplierContacts ";
SqlConnection connection = TravelExperts.GetConection();
List <SupplierContacts> dataList = new List <SupplierContacts>(); // epmty list
SupplierContacts data; // for reading
// create connection
SqlCommand cmd = new SqlCommand(query, connection);
// open the conection
connection.Open();
// run the command
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
// build object list to return
while (reader.Read()) // if there is a object with this ID
{
data = new SupplierContacts();
data.SupplierContactId = Convert.ToInt32(reader["SupplierContactId"]);
data.SupConFirstName = reader["SupConFirstName"].ToString();
data.SupConLastName = reader["SupConLastName"].ToString();
data.SupConCompany = reader["SupConCompany"].ToString();
data.SupConAddress = reader["SupConAddress"].ToString();
data.SupConCity = reader["SupConCity"].ToString();
data.SupConProv = reader["SupConProv"].ToString();
data.SupConPostal = reader["SupConPostal"].ToString();
data.SupConCountry = reader["SupConCountry"].ToString();
data.SupConBusPhone = reader["SupConBusPhone"].ToString();
data.SupConFax = reader["SupConFax"].ToString();
data.SupConEmail = reader["SupConEmail"].ToString();
data.SupConURL = reader["SupConURL"].ToString();
data.AffiliationId = reader["AffiliationId"].ToString();
data.SupplierId = Convert.ToInt32(reader["SupplierId"]);
dataList.Add(data);
}
return(dataList);
}
// makes identival copy of Customer
public SupplierContacts Clone()
{
SupplierContacts copy = new SupplierContacts();
copy.SupplierContactId = this.SupplierContactId;
copy.SupConFirstName = this.SupConFirstName;
copy.SupConLastName = this.SupConLastName;
copy.SupConCompany = this.SupConCompany;
copy.SupConAddress = this.SupConAddress;
copy.SupConCity = this.SupConCity;
copy.SupConProv = this.SupConProv;
copy.SupConPostal = this.SupConPostal;
copy.SupConCountry = this.SupConCountry;
copy.SupConBusPhone = this.SupConBusPhone;
copy.SupConFax = this.SupConFax;
copy.SupConEmail = this.SupConEmail;
copy.SupConURL = this.SupConURL;
copy.AffiliationId = this.AffiliationId;
copy.SupplierId = this.SupplierId;
return(copy);
}
// Update object
// return indicator of success
public static bool Update(SupplierContacts oldObj, SupplierContacts newObj)
{
bool success = false; // did not update
// create connection
SqlConnection connection = TravelExperts.GetConection();
// create UPDATE command
string updateStatment =
"UPDATE SupplierContacts SET " +
"SupplierContactId = @NewSupplierContactId, " +
"SupConFirstName = @NewSupConFirstName, " +
"SupConLastName = @NewSupConLastName, " +
"SupConCompany = @NewSupConCompany, " +
"SupConAddress = @NewSupConAddress, " +
"SupConCity = @NewSupConCity, " +
"SupConProv = @NewSupConProv, " +
"SupConPostal = @NewSupConPostal, " +
"SupConCountry = @NewSupConCountry, " +
"SupConBusPhone = @NewSupConBusPhone, " +
"SupConFax = @NewSupConFax, " +
"SupConEmail = @NewSupConEmail, " +
"SupConURL = @NewSupConURL, " +
"AffiliationId = @NewAffiliationId, " +
"SupplierId = @NewSupplierId " +
"WHERE SupplierContactId = @OldSupplierContactId " + // identifies
"AND SupConFirstName = @OldSupConFirstName " + // the rest - for optimistic concurrency
"AND SupConLastName = @OldSupConLastName " +
"AND SupConCompany = @OldSupConCompany " +
"AND SupConAddress = @OldSupConAddress " +
"AND SupConCity = @OldSupConCity " +
"AND SupConProv = @OldSupConProv " +
"AND SupConPostal = @OldSupConPostal " +
"AND SupConCountry = @OldSupConCountry " +
"AND SupConBusPhone = @OldSupConBusPhone " +
"AND SupConFax = @OldSupConFax " +
"AND SupConEmail = @OldSupConEmail " +
"AND SupConURL = @OldSupConURL " +
"AND AffiliationId = @OldAffiliationId " +
"AND SupplierId = @OldSupplierId ";
SqlCommand cmd = new SqlCommand(updateStatment, connection);
// suply perameter value
// New object Values
cmd.Parameters.AddWithValue("@NewSupplierContactId", newObj.SupplierContactId);
cmd.Parameters.AddWithValue("@NewSupConFirstName", newObj.SupConFirstName);
cmd.Parameters.AddWithValue("@NewSupConLastName", newObj.SupConLastName);
cmd.Parameters.AddWithValue("@NewSupConCompany", newObj.SupConCompany);
cmd.Parameters.AddWithValue("@NewSupConAddress", newObj.SupConAddress);
cmd.Parameters.AddWithValue("@NewSupConCity", newObj.SupConCity);
cmd.Parameters.AddWithValue("@NewSupConProv", newObj.SupConProv);
cmd.Parameters.AddWithValue("@NewSupConPostal", newObj.SupConPostal);
cmd.Parameters.AddWithValue("@NewSupConCountry", newObj.SupConCountry);
cmd.Parameters.AddWithValue("@NewSupConBusPhone", newObj.SupConBusPhone);
cmd.Parameters.AddWithValue("@NewSupConFax", newObj.SupConFax);
cmd.Parameters.AddWithValue("@NewSupConEmail", newObj.SupConEmail);
cmd.Parameters.AddWithValue("@NewSupConURL", newObj.SupConURL);
cmd.Parameters.AddWithValue("@NewAffiliationId", newObj.AffiliationId);
cmd.Parameters.AddWithValue("@NewSupplierId", newObj.SupplierId);
// ID
cmd.Parameters.AddWithValue("@OldSupplierContactId", oldObj.SupplierContactId);
// Old object Values
cmd.Parameters.AddWithValue("@OldSupConFirstName", oldObj.SupConFirstName);
cmd.Parameters.AddWithValue("@OldSupConLastName", oldObj.SupConLastName);
cmd.Parameters.AddWithValue("@OldSupConCompany", oldObj.SupConCompany);
cmd.Parameters.AddWithValue("@OldSupConAddress", oldObj.SupConAddress);
cmd.Parameters.AddWithValue("@OldSupConCity", oldObj.SupConCity);
cmd.Parameters.AddWithValue("@OldSupConProv", oldObj.SupConProv);
cmd.Parameters.AddWithValue("@OldSupConPostal", oldObj.SupConPostal);
cmd.Parameters.AddWithValue("@OldSupConCountry", oldObj.SupConCountry);
cmd.Parameters.AddWithValue("@OldSupConBusPhone", oldObj.SupConBusPhone);
cmd.Parameters.AddWithValue("@OldSupConFax", oldObj.SupConFax);
cmd.Parameters.AddWithValue("@OldSupConEmail", oldObj.SupConEmail);
cmd.Parameters.AddWithValue("@OldSupConURL", oldObj.SupConURL);
cmd.Parameters.AddWithValue("@OldAffiliationId", oldObj.AffiliationId);
cmd.Parameters.AddWithValue("@OldSupplierId", oldObj.SupplierId);
// execute the UPDATE command
try
{
// open the conection
connection.Open();
// execute the command
int count = cmd.ExecuteNonQuery();
// check if successful
if (count > 0)
{
success = true; // updated
}
}
catch (Exception ex)
{
throw ex;
}
finally // executes always
{
connection.Close();
}
// retrieve generated object ID to return
return(success);
}
// Delete object
// return indicator of success
public static bool Delete(SupplierContacts obj)
{
bool success = false;
// create connection
SqlConnection connection = TravelExperts.GetConection();
// create DELETE command
string deleteStatment =
"DELETE FROM SupplierContacts " +
"WHERE SupplierContactId = @SupplierContactId " + // needed for identification of object
"AND SupConFirstName = @SupConFirstName " + // the rest - for optimistic concurrency
"AND SupConLastName = @SupConLastName " +
"AND SupConCompany = @SupConCompany " +
"AND SupConAddress = @SupConAddress " +
"AND SupConCity = @SupConCity " +
"AND SupConProv = @SupConProv " +
"AND SupConPostal = @SupConPostal " +
"AND SupConCountry = @SupConCountry " +
"AND SupConBusPhone = @SupConBusPhone " +
"AND SupConFax = @SupConFax " +
"AND SupConEmail = @SupConEmail " +
"AND SupConURL = @SupConURL " +
"AND AffiliationId = @AffiliationId " +
"AND SupplierId = @SupplierId ";
SqlCommand cmd = new SqlCommand(deleteStatment, connection);
// suply perameter value
cmd.Parameters.AddWithValue("@SupplierContactId", obj.SupplierContactId);
cmd.Parameters.AddWithValue("@SupConFirstName", obj.SupConFirstName);
cmd.Parameters.AddWithValue("@SupConLastName", obj.SupConLastName);
cmd.Parameters.AddWithValue("@SupConCompany", obj.SupConCompany);
cmd.Parameters.AddWithValue("@SupConAddress", obj.SupConAddress);
cmd.Parameters.AddWithValue("@SupConCity", obj.SupConCity);
cmd.Parameters.AddWithValue("@SupConProv", obj.SupConProv);
cmd.Parameters.AddWithValue("@SupConPostal", obj.SupConPostal);
cmd.Parameters.AddWithValue("@SupConCountry", obj.SupConCountry);
cmd.Parameters.AddWithValue("@SupConBusPhone", obj.SupConBusPhone);
cmd.Parameters.AddWithValue("@SupConFax", obj.SupConFax);
cmd.Parameters.AddWithValue("@SupConEmail", obj.SupConEmail);
cmd.Parameters.AddWithValue("@SupConURL", obj.SupConURL);
cmd.Parameters.AddWithValue("@AffiliationId", obj.AffiliationId);
cmd.Parameters.AddWithValue("@SupplierId", obj.SupplierId);
// execute the command
try
{
// open the conection
connection.Open();
// execute the command
int count = cmd.ExecuteNonQuery();
// check if successful
if (count > 0)
{
success = true;
}
}
catch (Exception ex)
{
throw ex;
}
finally // executes always
{
connection.Close();
}
// retrieve generated customer nID to return
return(success);
}
