public static LineItemCollection Select(string WhereClause, string OrderByClause)
{
string Sql = string.Format("SELECT * from [lineItem] WHERE ({0}) ORDER BY {1}", WhereClause, OrderByClause);
//string ConnStr = @"Server=STUDENT05;Database=prs;Trusted_Connection=True;";
SqlCommand Cmd = CreateConnection(ConnStr, Sql, "Connection didn't open");
SqlDataReader Reader = Cmd.ExecuteReader();
//if (!Reader.HasRows) {
// throw new ApplicationException("Result set has no rows!");
//}
LineItemCollection lineItems = new LineItemCollection();
while (Reader.Read())
{
int id = Reader.GetInt32(Reader.GetOrdinal("Id"));
int purchaseRequestId = Reader.GetInt32(Reader.GetOrdinal("PurchaseRequestId"));
int productId = Reader.GetInt32(Reader.GetOrdinal("ProductId"));
int quantity = Reader.GetInt32(Reader.GetOrdinal("Quantity"));
LineItem lineItem = new LineItem();
lineItem.Id = id;
lineItem.PurchaseRequestId = purchaseRequestId;
lineItem.ProductId = productId;
lineItem.Quantity = quantity;
// get the PurchaseRequest
lineItem.PurchaseRequest = PurchaseRequest.Select(lineItem.PurchaseRequestId);
// get the Product
lineItem.Product = Product.Select(lineItem.ProductId);
lineItems.Add(lineItem);
}
Cmd.Connection.Close();
return(lineItems);
}
public static PurchaseRequest Select(int Id)
{
PurchaseRequestCollection purchaseRequests = PurchaseRequest.Select($"Id = {Id}", "Id");
PurchaseRequest purchaseRequest = (purchaseRequests.Count == 1) ? purchaseRequests[0] : null;
return(purchaseRequest);
}
//INSERT
public static bool Insert(LineItem lineItem)
{
string Sql = string.Format("insert into [LineItem] " +
"LineItemID, LineItemID, Quantity) " +
" values" +
"(@lineitemid, @lineitemid, @quantity)");
string ConnStr = @"Server=STUDENT05;Database=prs;Trusted_Connection=True;";
SqlConnection Conn = new SqlConnection(ConnStr);
Conn.Open();
if (Conn.State != System.Data.ConnectionState.Open)
{
throw new ApplicationException("Connection didn't open");
}
SqlCommand Cmd = new SqlCommand(Sql, Conn);
AddSqlInsertUpdateParameters(Cmd, lineItem);
int recsAffected = Cmd.ExecuteNonQuery();
if (recsAffected != 1)
{
throw new ApplicationException("Insert Failed!");
}
lineItem.PurchaseRequest = PurchaseRequest.Select(lineItem.PurchaseRequestID);
lineItem.Product = Product.Select(lineItem.ProductID);
return(recsAffected == 1);
}
public static bool Delete(int Id)
{
PurchaseRequest purchaseRequest = PurchaseRequest.Select(Id);
if (purchaseRequest == null)
{
return(false);
}
bool rc = PurchaseRequest.Delete(purchaseRequest);
return(rc);
}
public static bool Insert(LineItem lineItem)
{
string Sql = string.Format("insert into [lineItem] " +
" (PurchaseRequestId, ProductId, Quantity) " +
" values " +
" (@PurchaseRequestId, @ProductId, @Quantity)");
//string ConnStr =@"Server=STUDENT05;Database=prs;Trusted_Connection=True;";
SqlCommand Cmd = CreateConnection(ConnStr, Sql, "Connection didn't open");
AddSqlInsertUpdateParameters(Cmd, lineItem);
int recsAffected = ExecuteSqlInsUpdDelCommand(Cmd, "Insert Failed!");
// get the last id inserted
lineItem.Id = GetLastIdGenerated(ConnStr, "lineItem");
lineItem.PurchaseRequest = PurchaseRequest.Select(lineItem.PurchaseRequestId);
lineItem.Product = Product.Select(lineItem.ProductId);
Cmd.Connection.Close();
return(recsAffected == 1);
}