public static Products GetByPk(int id)
{
var sqlcmd = new SqlCommand(SqlGetByPk, Connection.sqlConnection);
sqlcmd.Parameters.AddWithValue("@Id", id);
var reader = sqlcmd.ExecuteReader();
if (!reader.HasRows)
{
reader.Close();
return(null);
}
reader.Read();
var product = new Products();
LoadProductFromSql(product, reader);
reader.Close();
Vendors.Connection = Connection;
var vendor = Vendors.GetByPk(product.VendorID);
product.Vendor = vendor;
return(product);
}
public static List <Products> GetAll()
{
var sqlcmd = new SqlCommand(SqlGetAll, Connection.sqlConnection);
var myReader = sqlcmd.ExecuteReader(); // execute the myReader
var products = new List <Products>();
while (myReader.Read())
{
var product = new Products();
products.Add(product); // add empty product instance to collection. Can do now, so don't forget later.
LoadProductFromSql(product, myReader);
}
myReader.Close();
//because of data reading/ opening-a-connection issue ??? We have to do the tricky thing below.
Vendors.Connection = Connection; // so vendor can use Db
foreach (var prod in products)
{
var vendor = Vendors.GetByPk(prod.VendorId); // !!! <-- !!!! We're USING fucntions of the vendor class to read fjflfdsljfl)
// we'll put whole vender instance into the vendro property of the product" (?)
//point to a vendor id that doesn't exist? Bad!
//prod.VendorId is going to close inside its own method, so we
// don't have to worry about it
//If we had TWO data readers open (impossible) we wouldn't have to do all this stuff/nonsense
prod.Vendor = vendor;
}
return(products);
}
public static Products GetByPk(int id)
{
var sqlcmd = new SqlCommand(SqlGetByPk, Connection.sqlConnection);
sqlcmd.Parameters.AddWithValue("@Id", id);
var myReader = sqlcmd.ExecuteReader();
if (!myReader.HasRows)
{
myReader.Close();
return(null);
}
myReader.Read();
var product = new Products(); // note: here we are instantiating an instance inside its own class, not Program class
LoadProductFromSql(product, myReader);
myReader.Close();
// ...and we attach one more variable to product: The vendor pointed to by the product's VendorId.
// - essentially the same as JOIN VIEW JOIN VIEW JOIN VIEW
Vendors.Connection = Connection;
Vendors vendor = Vendors.GetByPk(product.VendorId); // VendorId, as found in product table, IS the PK field for vendor table
product.Vendor = vendor;
return(product);
}
public static List <Products> GetAll()
{
var sqlcmd = new SqlCommand(SqlGetAll, Connection.sqlConnection);
var reader = sqlcmd.ExecuteReader();
var products = new List <Products>();
while (reader.Read())
{
var product = new Products();
products.Add(product);
LoadProductFromSql(product, reader);
}
reader.Close();
Vendors.Connection = Connection;
foreach (var prod in products)
{
var vendor = Vendors.GetByPk(prod.VendorID); // adding a FK
prod.Vendor = vendor;
}
return(products);
}