public static PurchaseRequestCollection Select(string WhereClause, string OrderByClause) { string Sql = string.Format("SELECT * from [purchaseRequest] WHERE {0} ORDER BY {1}", WhereClause, OrderByClause); 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); SqlDataReader Reader = Cmd.ExecuteReader(); if (!Reader.HasRows) { throw new ApplicationException("Result set has no rows!"); } PurchaseRequestCollection purchaseRequests = new PurchaseRequestCollection(); while (Reader.Read()) { int id = Reader.GetInt32(Reader.GetOrdinal("Id")); int userid = Reader.GetInt32(Reader.GetOrdinal("UserId")); string user = Reader.GetString(Reader.GetOrdinal("User")); string description = Reader.GetString(Reader.GetOrdinal("Description")); string justification = Reader.GetString(Reader.GetOrdinal("Justification")); DateTime dateneeded = Reader.GetDateTime(Reader.GetOrdinal("DateNeeded")); string deliverymode = Reader.GetString(Reader.GetOrdinal("DeliveryMode")); bool docsattached = Reader.GetBoolean(Reader.GetOrdinal("DocsAttached")); string status = Reader.GetString(Reader.GetOrdinal("Status")); decimal total = Reader.GetDecimal(Reader.GetOrdinal("Total")); DateTime submitteddate = Reader.GetDateTime(Reader.GetOrdinal("SubmittedDate")); PurchaseRequest purchaseRequest = new PurchaseRequest(); purchaseRequest.Id = id; purchaseRequest.UserId = userid; purchaseRequest.Description = description; purchaseRequest.Justification = justification; purchaseRequest.DateNeeded = dateneeded; purchaseRequest.DeliveryMode = deliverymode; purchaseRequest.DocsAttached = docsattached; purchaseRequest.Status = status; purchaseRequest.Total = total; purchaseRequest.SubmittedDate = submitteddate; purchaseRequest.User = User.Select(purchaseRequest.UserId); purchaseRequests.Add(purchaseRequest); } return(purchaseRequests); }
public static PurchaseRequestCollection Select(string WhereClause, string OrderByClause) { string Sql = string.Format("SELECT * from [purchaseRequest] WHERE ({0}) ORDER BY {1}", WhereClause, OrderByClause); //string ConnStr = @"Server=DSI-WORKSTATION\SQLEXPRESS;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!"); //} PurchaseRequestCollection purchaseRequests = new PurchaseRequestCollection(); while (Reader.Read()) //this while loop is where the data is pulled from the database and put in the purchase request class instance { int id = Reader.GetInt32(Reader.GetOrdinal("Id")); int userId = Reader.GetInt32(Reader.GetOrdinal("UserId")); string description = Reader.GetString(Reader.GetOrdinal("Description")); string justification = Reader.GetString(Reader.GetOrdinal("Justification")); DateTime dateNeeded = Reader.GetDateTime(Reader.GetOrdinal("DateNeeded")); string deliveryMode = Reader.GetString(Reader.GetOrdinal("DeliveryMode")); bool docsAttached = Reader.GetBoolean(Reader.GetOrdinal("DocsAttached")); string status = Reader.GetString(Reader.GetOrdinal("Status")); decimal total = Reader.GetDecimal(Reader.GetOrdinal("Total")); DateTime submittedDate = Reader.GetDateTime(Reader.GetOrdinal("SubmittedDate")); PurchaseRequest purchaseRequest = new PurchaseRequest(); purchaseRequest.Id = id; purchaseRequest.UserId = userId; purchaseRequest.Description = description; purchaseRequest.Justification = justification; purchaseRequest.DateNeeded = dateNeeded; purchaseRequest.DeliveryMode = deliveryMode; purchaseRequest.DocsAttached = docsAttached; purchaseRequest.Status = status; purchaseRequest.Total = total; purchaseRequest.SubmittedDate = submittedDate; // get the user purchaseRequest.User = User.Select(purchaseRequest.UserId); //get the line items purchaseRequest.LineItems = GetLineItems(purchaseRequest.Id); purchaseRequests.Add(purchaseRequest); } Cmd.Connection.Close(); return(purchaseRequests); }