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); }