// retrieve all objects
public static List <Products_Suppliers> GetAll()
{
// create SELECT command
string query =
"SELECT ProductSupplierId, ProductId, SupplierId " +
"FROM Products_Suppliers ";
SqlConnection connection = TravelExperts.GetConection();
List <Products_Suppliers> dataList = new List <Products_Suppliers>(); // epmty list
Products_Suppliers 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 Products_Suppliers();
data.ProductSupplierId = Convert.ToInt32(reader["ProductSupplierId"]);
data.ProductId = Convert.ToInt32(reader["ProductId"]);
data.SupplierId = Convert.ToInt32(reader["SupplierId"]);
dataList.Add(data);
}
return(dataList);
}
// makes identival copy of Customer
public Products_Suppliers Clone()
{
Products_Suppliers copy = new Products_Suppliers();
copy.ProductSupplierId = this.ProductSupplierId;
copy.ProductId = this.ProductId;
copy.SupplierId = this.SupplierId;
return(copy);
}
// Update object
// return indicator of success
public static bool Update(Products_Suppliers oldObj, Products_Suppliers newObj)
{
bool success = false; // did not update
// create connection
SqlConnection connection = TravelExperts.GetConection();
// create UPDATE command
string updateStatment =
"UPDATE Products_Suppliers SET " +
"ProductSupplierId = @NewProductSupplierId, " +
"ProductId = @NewProductId, " +
"SupplierId = @NewSupplierId " +
"WHERE ProductSupplierId = @OldProductSupplierId " + // identifies
"AND ProductId = @OldProductId " + // the rest - for optimistic concurrency
"AND SupplierId = @OldSupplierId ";
SqlCommand cmd = new SqlCommand(updateStatment, connection);
// suply perameter value
// New object Values
cmd.Parameters.AddWithValue("@NewProductSupplierId", newObj.ProductSupplierId);
cmd.Parameters.AddWithValue("@NewProductId", newObj.ProductId);
cmd.Parameters.AddWithValue("@NewSupplierId", newObj.SupplierId);
// ID
cmd.Parameters.AddWithValue("@OldProductSupplierId", oldObj.ProductSupplierId);
// Old object Values
cmd.Parameters.AddWithValue("@OldProductId", oldObj.ProductId);
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(Products_Suppliers obj)
{
bool success = false;
// create connection
SqlConnection connection = TravelExperts.GetConection();
// create DELETE command
string deleteStatment =
"DELETE FROM Products_Suppliers " +
"WHERE ProductSupplierId = @ProductSupplierId " + // needed for identification of object
"AND ProductId = @ProductId " + // the rest - for optimistic concurrency
"AND SupplierId = @SupplierId ";
SqlCommand cmd = new SqlCommand(deleteStatment, connection);
// suply perameter value
cmd.Parameters.AddWithValue("@ProductSupplierId", obj.ProductSupplierId);
cmd.Parameters.AddWithValue("@ProductId", obj.ProductId);
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);
}
// retrieve object with given ID
public static Products_Suppliers GetValue(int objID)
{
Products_Suppliers obj = null;
// create connection
SqlConnection connection = TravelExperts.GetConection();
// create SELECT command
string query =
"SELECT ProductSupplierId, ProductId, SupplierId " +
"FROM Products_Suppliers " +
"WHERE ProductSupplierId = @ProductSupplierId ";
SqlCommand cmd = new SqlCommand(query, connection);
// suply perameter value
cmd.Parameters.AddWithValue("@ProductSupplierId", 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 Products_Suppliers();
obj.ProductSupplierId = Convert.ToInt32(reader["ProductSupplierId"]);
obj.ProductId = Convert.ToInt32(reader["ProductId"]);
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(Products_Suppliers obj)
{
int custID = 0;
// create connection
SqlConnection connection = TravelExperts.GetConection();
// create INSERT command
// CustomerID is IDENTITY so no value provided
string insertStatment =
"INSERT INTO Products_Suppliers(ProductSupplierId, ProductId, SupplierId) " +
"OUTPUT inserted.[ProductSupplierId] " +
"VALUES(@ProductSupplierId, @ProductId, @SupplierId) ";
SqlCommand cmd = new SqlCommand(insertStatment, connection);
// suply perameter value
cmd.Parameters.AddWithValue("@ProductSupplierId", obj.ProductSupplierId);
cmd.Parameters.AddWithValue("@ProductId", obj.ProductId);
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);
}
