public static PurchaseRequest Select(int Id)
{
PurchaseRequestCollection purchaseRequests = PurchaseRequest.Select($"Id = {Id}", "Id");
PurchaseRequest purchaseRequest = (purchaseRequests.Count == 1) ? purchaseRequests[0] : null;
return(purchaseRequest);
}
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);
}
static void Main(string[] args)
{
// Select
string whereClause = "LastName = 'Cobb'";
string orderByClause = "UserName desc";
UserCollection users = User.Select(whereClause, orderByClause);
User user = users[0];
user.FirstName = "Alex";
bool success = User.Update(user);
success = User.Delete(user);
User user2Insert = new User {
UserName = "******",
Password = "******",
FirstName = "Nbr 88",
LastName = "Cobb",
Phone = "513-555-1212",
Email = "*****@*****.**",
IsReviewer = false,
IsAdmin = false
};
success = User.Insert(user2Insert);
VendorCollection vendors = Vendor.Select("1 = 1", "Id");
Vendor vendor = new Vendor {
Code = "Krog0010",
Name = "Kroger",
Address = "123 Any Street",
City = "Cincinnati", State = "OH", Zip = "45201",
Email = "*****@*****.**", Phone = "513-555-1212",
IsRecommended = true
};
bool rc = Vendor.Insert(vendor);
vendor.Code = "KROG1111";
rc = Vendor.Update(vendor);
rc = Vendor.Delete(vendor);
ProductCollection products = Product.Select("1 = 1", "Id");
Product product = new Product {
VendorId = 2,
Name = "Widget1",
VendorPartNumber = "BWidget",
Price = 12345.67M,
Unit = "Each",
PhotoPath = "http://www.maxtrain.com"
};
rc = Product.Insert(product);
product.Price = 76543.21m;
rc = Product.Update(product);
rc = Product.Delete(product);
PurchaseRequestCollection purchaseRequests = PurchaseRequest.Select("1 = 1", "Id");
PurchaseRequest purchaseRequest = new PurchaseRequest();
purchaseRequest.UserId = 1;
purchaseRequest.Description = "My first purchase request";
purchaseRequest.Justification = "My birthday";
purchaseRequest.DateNeeded = DateTime.Now;
purchaseRequest.DeliveryMode = "USPS";
purchaseRequest.DocsAttached = false;
purchaseRequest.Status = "New";
//purchaseRequest.Total = 0.0M;
//purchaseRequest.SubmittedDate = DateTime.Now;
rc = PurchaseRequest.Insert(purchaseRequest);
purchaseRequest.DateNeeded = DateTime.Now.AddDays(1);
rc = PurchaseRequest.Update(purchaseRequest);
purchaseRequests = PurchaseRequest.Select("1 = 1", "Id");
rc = PurchaseRequest.Delete(purchaseRequest);
purchaseRequests = PurchaseRequest.Select("1 = 1", "Id");
//LineItemCollection lineitems = LineItem.Select("1 = 1", "Id");
//LineItem lineitem = new LineItem {
// PurchaseRequestId = 3,
// ProductId = 3,
// Quantity = 1
//};
//rc = LineItem.Insert(lineitem);
//PurchaseRequest NewPurchaseRequest = new PurchaseRequest {
// UserId = 2,
// Description = "First full add of PR",
// Justification = "I just want to"
//};
//rc = PurchaseRequest.Insert(NewPurchaseRequest);
//rc = NewPurchaseRequest.AddLineItem(1, 10);
//rc = NewPurchaseRequest.AddLineItem(2, 20);
//rc = NewPurchaseRequest.AddLineItem(3, 30);
PurchaseRequest NewPurchaseRequest = PurchaseRequest.Select(12);
//rc = NewPurchaseRequest.UpdateLineItem(5, 11);
int i = 0;
}
