// retrieve supplier with given ID
public static List <Suppliers> GetSuppliers()
{
List <Suppliers> suppliers = new List <Suppliers>();
Suppliers pSpack = null;
// create connection
SqlConnection connection = TravelExpertsDB.GetConnection();
// create SELECT command
string query = "SELECT SupplierId, SupName " +
"FROM Suppliers ";
SqlCommand cmd = new SqlCommand(query, connection);
// run the SELECT query
try
{
connection.Open();
SqlDataReader reader = cmd.ExecuteReader();
// build supplier object to return
while (reader.Read()) // if there is a supplier with this ID
{
pSpack = new Suppliers();
pSpack.SuppliersId = (int)reader["SupplierId"];
pSpack.SupName = reader["SupName"].ToString();
suppliers.Add(pSpack);
}
reader.Close();
}
catch (Exception ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(suppliers);
}
public static List <Suppliers> GetSuppliers()
{
List <Suppliers> suppliers = new List <Suppliers>(); //empty list
Suppliers s; //just for reading variables expressed before the commands!
//create the connection
SqlConnection connection = TravelExpertsDB.GetConnection();
//create the command for SELECT query to get the states
string query = "SELECT SupplierId, SupName FROM Suppliers ";
SqlCommand cmd = new SqlCommand(query, connection);
try
{
//open the connection
connection.Open();
//run the command
SqlDataReader reader = cmd.ExecuteReader(); //built-in
//each state data returned, make state object and add to the list
while (reader.Read()) //while there still is data to read
{
s = new Suppliers();
s.SuppliersId = (int)reader["SupplierId"]; //[] indexer from chapter 13
s.SupName = reader["SupName"].ToString();
suppliers.Add(s);
}
reader.Close();
}
catch (Exception ex) //error
{
throw ex;
}
finally //executes always
{
connection.Close();
}
//return the list of Suppliers
return(suppliers);
}
// update suppliers
// return indicator of success
public static bool UpdateSuppliers(Suppliers oldPack, Suppliers newPack)
{
bool success = false; // did not update
// connection
SqlConnection connection = TravelExpertsDB.GetConnection();
// update command
string updateStatement =
"UPDATE Suppliers SET " +
"SupplierId = @NewSupplierId, " +
"SupName = @NewSupName " +
"WHERE SupplierId = @OldSupplierId " + // identifies packages
"AND SupName = @OldSupName"; // remaining - for otimistic concurrency
SqlCommand cmd = new SqlCommand(updateStatement, connection);
cmd.Parameters.AddWithValue("@NewSupplierId", newPack.SuppliersId);
cmd.Parameters.AddWithValue("@NewSupName", newPack.SupName);
cmd.Parameters.AddWithValue("@OldSupplierId", oldPack.SuppliersId);
cmd.Parameters.AddWithValue("@OldSupName", oldPack.SupName);
try
{
connection.Open();
int count = cmd.ExecuteNonQuery();
if (count > 0)
{
success = true; // updated
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(success);
}
// delete supplier
// return indicator of success
public static bool Delete(Suppliers pSpack)
{
bool success = false;
// create connection
SqlConnection connection = TravelExpertsDB.GetConnection();
// create DELETE command
string deleteStatement =
"DELETE FROM Suppliers " +
"WHERE SupplierId = @SupplierId " + // needed for identification
"AND SupName = @SupName"; // the rest - for optimistic concurrency
SqlCommand cmd = new SqlCommand(deleteStatement, connection);
cmd.Parameters.AddWithValue("@SupName", pSpack.SupName);
try
{
connection.Open();
// execute the command
int count = cmd.ExecuteNonQuery();
// check if successful
if (count > 0)
{
success = true;
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(success);
}
public static Suppliers GetSuppliers(string id)
{
Suppliers suppliers;
SqlConnection connection = TravelExpertsDB.GetConnection();
string query = "SELECT SupplierId," +
"SupName FROM Suppliers WHERE SupplierId =@Pid;";
SqlCommand cmd = new SqlCommand(query, connection);
suppliers = new Suppliers();
cmd.Parameters.AddWithValue("@Pid", int.Parse(id));
try
{
//open the connection
connection.Open();
//run the command
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow); //built-in
//each state data returned, make state object and add to the list
if (reader.Read())
{
suppliers.SuppliersId = (int)reader["SupplierId"];
suppliers.SupName = reader["SupName"].ToString();
}
}
catch (Exception ex) //error
{
throw ex;
}
finally //executes always
{
connection.Close();
}
return(suppliers);
}
public static bool DeleteSupplier(Suppliers supplier)
{
throw new NotImplementedException();
}
public static int AddSupplier(Suppliers supplier)
{
throw new NotImplementedException();
}
public static bool UpdateSupplier(Suppliers supplier, suppliers newSupplier)
{
throw new NotImplementedException();
}
public static bool UpdateSupplier(object modifysup, Suppliers newSupplier)
{
throw new NotImplementedException();
}
