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