public static List<Bid> getBids(int postID) { List<Bid> bids = new List<Bid>(); try { DataAccessLayer DAL = new DataAccessLayer(); DataTable dt = DAL.select(String.Format("postID = '{0}'", postID), "Bids"); foreach (DataRow row in dt.Rows) { int bidderID = Convert.ToInt32(row["BidderID"]); DataTable buyerDt = DAL.select(String.Format("profileID = '{0}'", bidderID), "UserProfile"); DataRow buyerRow = buyerDt.Rows[0]; int bidID = Convert.ToInt32(row["BidID"]); string bidder = Convert.ToString(buyerRow["Name"]); int bidPrice = Convert.ToInt32(row["BidPrice"]); bool viaEmail = Convert.ToBoolean(row["viaEmail"]); Bid bid = new Bid(postID, bidderID, bidder, bidPrice, viaEmail, bidID); bids.Add(bid); } } catch (Exception ex) { Console.Write("ERROR: An error occured in retrieving the bids --- " + ex.Message); } return bids; }
public static int AddUser(UserProfile elm) { int id = -1; try { DataAccessLayer DAL = new DataAccessLayer(); DataTable dt = DAL.select(String.Format("FacebookID = '{0}'", elm.FacebookID), "UserProfile"); if (dt == null ||dt.Rows.Count == 0) { Dictionary<string, string> Profile = new Dictionary<string, string>(); Profile.Add("FacebookID", elm.FacebookID); Profile.Add("Name", elm.Name); Profile.Add("Email", elm.Email); Profile.Add("FacebookProfileLink", elm.FacebookProfileLink); Profile.Add("Gender", elm.Gender); Profile.Add("IsActive", "1"); Profile.Add("IsDeleted", "0"); Profile.Add("CreatedDate", Convert.ToString(DateTime.Now)); Profile.Add("ModifiedDate", Convert.ToString(DateTime.Now)); id = DAL.insert(Profile, "UserProfile"); } } catch (Exception ex) { Console.Write("ERROR: An error occured in adding a new user --- " + ex.Message); } return id; }
public static IEnumerable<Textbook> getAllTextbooks() { List<Textbook> allTextbooks = new List<Textbook>(); try { DataAccessLayer DAL = new DataAccessLayer(); DataTable dt = DAL.select("", "TextBooks"); foreach (DataRow row in dt.Rows) { string isbn = Convert.ToString(row["ISBN"]); string title = Convert.ToString(row["BookTitle"]); string author = Convert.ToString(row["Author"]); string bookImageURL = Convert.ToString(row["BookImageURL"]); int courseID = Convert.ToInt32(row["CourseID"]); string courseName = CourseInfoHandler.getCourseName(courseID); int storePrice = Convert.ToInt32(row["StorePrice"]); Textbook textbook = new Textbook(isbn, title, author, courseName, bookImageURL, storePrice); allTextbooks.Add(textbook); } } catch (Exception ex) { Console.Write("ERROR: An error occured in retrieving all textbooks --- " + ex.Message); } return allTextbooks; }
public static UserProfile getUserProfile(int profileID) { UserProfile profile = null; try { DataAccessLayer DAL = new DataAccessLayer(); DataTable dt = DAL.select(String.Format("ProfileID = '{0}'", profileID), "UserProfile"); if (dt != null && dt.Rows.Count > 0) { DataRow row = dt.Rows[0]; string facebookID = Convert.ToString(row["FacebookID"]); string name = Convert.ToString(row["Name"]); string email = Convert.ToString(row["Email"]); string facebookProfileLink = Convert.ToString(row["FacebookProfileLink"]); string gender = Convert.ToString(row["Gender"]); profile = new UserProfile(name, facebookProfileLink, facebookID, gender, email, String.Empty); } } catch (Exception ex) { Console.Write("ERROR: An error occured in retrieving the user profile --- " + ex.Message); } return profile; }
public static int createBid(Bid bid) { int bidID = -1; DataAccessLayer DAL = new DataAccessLayer(); DataTable bidDupPrices = DAL.select(String.Format("BidPrice = '{0}' AND PostID = '{1}'", bid.BidPrice, bid.PostID), "Bids"); if (bidDupPrices.Rows.Count > 0) { return bidID; } Dictionary<string, string> Bid = new Dictionary<string, string>(); Bid.Add("BidPrice", Convert.ToString(bid.BidPrice)); Bid.Add("PostID", Convert.ToString(bid.PostID)); Bid.Add("BidderID", Convert.ToString(bid.BidderID)); Bid.Add("viaEmail", Convert.ToString(bid.BidviaEmail)); Bid.Add("IsActive", Convert.ToString(true)); Bid.Add("IsDeleted", Convert.ToString(false)); Bid.Add("CreatedDate", Convert.ToString(DateTime.Now)); Bid.Add("ModifiedDate", Convert.ToString(DateTime.Now)); bidID = DAL.insert(Bid, "Bids"); return bidID; }
public static Textbook getTextbook(int textbookID) { Textbook textbook = null; try { DataAccessLayer DAL = new DataAccessLayer(); DataTable dt = DAL.select(String.Format("TextBookID = '{0}'", textbookID), "TextBooks"); if (dt != null && dt.Rows.Count > 0) { DataRow row = dt.Rows[0]; string isbn = Convert.ToString(row["ISBN"]); string title = Convert.ToString(row["BookTitle"]); string author = Convert.ToString(row["Author"]); string bookImageURL = Convert.ToString(row["BookImageURL"]); int courseID = Convert.ToInt32(row["CourseID"]); string courseName = CourseInfoHandler.getCourseName(courseID); int storePrice = Convert.ToInt32(row["StorePrice"]); textbook = new Textbook(isbn, title, author, courseName, bookImageURL, storePrice); } } catch (Exception ex) { Console.Write("ERROR: An error occured in retrieving the textbook --- " + ex.Message); } return textbook; }
public static IEnumerable<MarketPost> getAllMarketPosts(bool isBuyPost) { List<MarketPost> allMarketPosts = new List<MarketPost>(); try { DataAccessLayer DAL = new DataAccessLayer(); DataTable dt = DAL.select(String.Format("IsBuy = '{0}'", isBuyPost), "Posts"); foreach (DataRow row in dt.Rows) { int textBookID = Convert.ToInt32(row["TextBookID"]); int profileID = Convert.ToInt32(row["ProfileID"]); int price = Convert.ToInt32(row["Price"]); int postID = Convert.ToInt32(row["PostID"]); bool viaEmail = Convert.ToBoolean(row["viaEmail"]); bool isNegotiable = Convert.ToBoolean(row["IsNegotiable"]); bool isBuy = Convert.ToBoolean(row["IsBuy"]); string condition = Convert.ToString(row["BookCondition"]); DateTime datePosted = Convert.ToDateTime(row["CreatedDate"]); UserProfile UserProfile = AccountHandler.getUserProfile(profileID); string postedBy = UserProfile.Name; string email = UserProfile.Email; Textbook textbook = TextbookHandler.getTextbook(textBookID); string title = textbook.Title; string course = textbook.CourseName; string isbn = textbook.ISBN; string author = textbook.Author; string bookImageURL = textbook.BookImageURL; List<Bid> bids = BidHandler.getBids(postID); MarketPost marketPost = new MarketPost( title, isBuy, course, condition, postedBy, datePosted, isbn, author, bookImageURL, price, bids, isNegotiable, email, viaEmail, profileID, postID); allMarketPosts.Add(marketPost); } } catch (Exception ex) { Console.Write("ERROR: An error occured in retrieving all market posts --- " + ex.Message); } return allMarketPosts; }
public static int[] getCourseIDs(string courseName) { List<int> courseIDs = new List<int>(); try { courseName = courseName.Replace(" ", String.Empty); string pattern = @"([A-Za-z]+)"; string[] result = Regex.Split(courseName, pattern); string coursePrefix = result[1]; StringBuilder courseNumber = new StringBuilder(); for (int i = 2; i < result.Length; i++) { courseNumber.Append(result[i]); } string whereClause = String.Empty; if (courseNumber.Equals(String.Empty)) { whereClause = String.Format("CourseName LIKE '%{0}%'", coursePrefix); } else { whereClause = String.Format("CourseName LIKE '%{0}%{1}%'", coursePrefix, courseNumber.ToString()); } DataAccessLayer DAL = new DataAccessLayer(); DataTable dt = DAL.select(whereClause, "CourseInfo", new string[] { "CourseID" }); foreach (DataRow row in dt.Rows) { int courseID = Convert.ToInt32(row["CourseID"]); courseIDs.Add(courseID); } } catch (Exception ex) { Console.Write("ERROR: An error occured in retrieving course IDs by course name --- " + ex.Message); } return courseIDs.ToArray(); }
public bool InsertStatus(DemoModel Status) { DataAccessLayer dal = new DataAccessLayer(); bool Success = true; try { /* Testing Insert */ Dictionary<string, string> dict = new Dictionary<string,string>(); dict.Add("StatusName", Status.StatusName); // no need to add statusID, as that column is an identity column dal.insert(dict, "TransactionStatus"); /* Testing Select */ DataTable dt = new DataTable(); dt = dal.select("StatusName = 'a'", "TransactionStatus"); foreach (DataRow row in dt.Rows) // Loop over the rows. { foreach (var col in row.ItemArray) { Debug.Write(col); } Debug.Write('\n'); } /* Testing Update */ Dictionary<string, string> d = new Dictionary<string, string>(); d.Add("StatusName", "'asma_rox'"); dal.update("TransactionStatus", "StatusName = 'asma_sux'", d); /* Manually go into SQL Server to see if the row is correctly updated */ /* Testing Delete */ dal.delete("StatusName = 'johnny'", "TransactionStatus"); /* now the db should not contain an entry with StatusName = 'johnny' */ } catch (Exception) { Success = false; } return Success; }
public static int getCourseID(string courseName) { int courseID = -1; try { DataAccessLayer DAL = new DataAccessLayer(); DataTable dt = DAL.select(String.Format("CourseName = '{0}'", courseName), "CourseInfo"); if (dt != null && dt.Rows.Count > 0) { courseID = Convert.ToInt32(dt.Rows[0]["CourseID"]); } } catch (Exception ex) { Console.Write("ERROR: An error occured in retrieving the course ID --- " + ex.Message); } return courseID; }
public static int getPostID(Post newPost) { int postID = -1; try { DataAccessLayer DAL = new DataAccessLayer(); DataTable dt = DAL.select(String.Format("ProfileID = '{0}' AND TextBookID = '{1}' AND IsBuy = '{2}'", newPost.ProfileID, newPost.TextBookID, newPost.IsBuy), "Posts"); if (dt != null && dt.Rows.Count > 0) { postID = Convert.ToInt32(dt.Rows[0]["PostID"]); } } catch (Exception ex) { Console.Write("ERROR: An error occured in retrieving the post ID --- " + ex.Message); } return postID; }
public static int getProfileID_Facebook(string facebookID) { int profileID = -1; try { DataAccessLayer DAL = new DataAccessLayer(); DataTable dt = DAL.select(String.Format("FacebookID = '{0}'", facebookID), "UserProfile"); if (dt != null && dt.Rows.Count > 0) { DataRow row = dt.Rows[0]; profileID = Convert.ToInt32(row["ProfileID"]); } } catch (Exception ex) { Console.Write("ERROR: An error occured in retrieving the user profile --- " + ex.Message); } return profileID; }
public static int[] getTextbookIDsByTitle(string title) { List<int> textbookIDs = new List<int>(); try { DataAccessLayer DAL = new DataAccessLayer(); DataTable dt = DAL.select(String.Format("BookTitle LIKE '%{0}%'", title), "TextBooks", new string[] { "TextBookID" }); foreach (DataRow row in dt.Rows) { int textbookID = Convert.ToInt32(row["TextBookID"]); textbookIDs.Add(textbookID); } } catch (Exception ex) { Console.Write("ERROR: An error occured in retrieving textbook IDs by book title --- " + ex.Message); } return textbookIDs.ToArray(); }
public static CourseInfo getCourseInfo(int courseID) { CourseInfo courseInfo = null; try { DataAccessLayer DAL = new DataAccessLayer(); DataTable dt = DAL.select(String.Format("CourseID = '{0}'", courseID), "CourseInfo"); if (dt != null && dt.Rows.Count > 0) { DataRow row = dt.Rows[0]; string courseName = Convert.ToString(row["CourseName"]); string description = Convert.ToString(row["Description"]); courseInfo = new CourseInfo(courseName, description); } } catch (Exception ex) { Console.Write("ERROR: An error occured in retrieving the course information --- " + ex.Message); } return courseInfo; }
public static MarketPost getMarketPost(int postID) { MarketPost marketPost = null; try { DataAccessLayer DAL = new DataAccessLayer(); DataTable dt = DAL.select(String.Format("PostID = '{0}'", postID), "Posts"); if (dt != null && dt.Rows.Count > 0) { DataRow row = dt.Rows[0]; int profileID = Convert.ToInt32(row["ProfileID"]); UserProfile UserProfile = AccountHandler.getUserProfile(profileID); string postedBy = UserProfile.Name; string email = UserProfile.Email; int textbookID = Convert.ToInt32(row["TextBookID"]); Textbook textbook = TextbookHandler.getTextbook(textbookID); string isbn = textbook.ISBN; string title = textbook.Title; string author = textbook.Author; string course = textbook.CourseName; string bookImageURL = textbook.BookImageURL; bool isBuy = Convert.ToBoolean(row["IsBuy"]); bool isNegotiable = Convert.ToBoolean(row["IsNegotiable"]); string condition = Convert.ToString(row["BookCondition"]); int price = Convert.ToInt32(row["Price"]); DateTime datePosted = Convert.ToDateTime(row["CreatedDate"]); List<Bid> bids = BidHandler.getBids(postID); bool viaEmail = Convert.ToBoolean(row["viaEmail"]); marketPost = new MarketPost( title, isBuy, course, condition, postedBy, datePosted, isbn, author, bookImageURL, price, bids, isNegotiable, email, viaEmail, profileID, postID); } } catch (Exception ex) { Console.Write("ERROR: An error occured in retrieving the market post --- " + ex.Message); } return marketPost; }
public static int getTextbookID(string course, string title) { int textbookID = -1; try { int courseID = CourseInfoHandler.getCourseID(course); DataAccessLayer DAL = new DataAccessLayer(); DataTable dt = DAL.select(String.Format("CourseID = '{0}' AND BookTitle = '{1}'", courseID, title), "TextBooks"); if (dt != null && dt.Rows.Count > 0) { textbookID = Convert.ToInt32(dt.Rows[0]["TextBookID"]); } } catch (Exception ex) { Console.Write("ERROR: An error occured in retrieving the textbook ID --- " + ex.Message); } return textbookID; }
public static int[] getTextbookIDsByCourse(string courseName) { List<int> textbookIDs = new List<int>(); try { int[] courseIDs = CourseInfoHandler.getCourseIDs(courseName); foreach (int courseID in courseIDs) { DataAccessLayer DAL = new DataAccessLayer(); DataTable dt = DAL.select(String.Format("CourseID = '{0}'", courseID), "TextBooks", new string[] { "TextBookID" }); foreach (DataRow row in dt.Rows) { int textbookID = Convert.ToInt32(row["TextBookID"]); textbookIDs.Add(textbookID); } } } catch (Exception ex) { Console.Write("ERROR: An error occured in retrieving textbook IDs by course name --- " + ex.Message); } return textbookIDs.ToArray(); }
public static int[] getTextbookIDsByISBN(string isbn) { List<int> textbookIDs = new List<int>(); try { // ISBN parsing (may or may not contain hyphens) isbn = isbn.Replace("-", String.Empty); DataAccessLayer DAL = new DataAccessLayer(); DataTable dt = DAL.select(String.Format("ISBN LIKE '%{0}%'", isbn), "TextBooks", new string[] { "TextBookID" }); foreach (DataRow row in dt.Rows) { int textbookID = Convert.ToInt32(row["TextBookID"]); textbookIDs.Add(textbookID); } } catch (Exception ex) { Console.Write("ERROR: An error occured in retrieving textbook IDs by ISBN --- " + ex.Message); } return textbookIDs.ToArray(); }
public static int insert(CourseInfo newCourseInfo) { int id = -1; try { DataAccessLayer DAL = new DataAccessLayer(); DataTable dt = DAL.select(String.Format("CourseName = '{0}'", newCourseInfo.CourseName), "CourseInfo"); if (dt == null || dt.Rows.Count == 0) { Dictionary<string, string> courseInfo = new Dictionary<string, string>(); courseInfo.Add("CourseName", newCourseInfo.CourseName); courseInfo.Add("Description", newCourseInfo.Description); courseInfo.Add("IsActive", "1"); courseInfo.Add("IsDeleted", "0"); courseInfo.Add("CreatedDate", Convert.ToString(DateTime.Now)); courseInfo.Add("ModifiedDate", Convert.ToString(DateTime.Now)); id = DAL.insert(courseInfo, "CourseInfo"); } } catch (Exception ex) { Console.Write("ERROR: An error occured in adding a new course --- " + ex.Message); } return id; }
public static int insert(Textbook newBook) { int id = -1; try { DataAccessLayer DAL = new DataAccessLayer(); DataTable dt = DAL.select(String.Format("ISBN = '{0}'", newBook.ISBN), "TextBooks"); if (dt == null || dt.Rows.Count == 0) { int courseID = CourseInfoHandler.getCourseID(newBook.CourseName); Dictionary<string, string> textbook = new Dictionary<string, string>(); textbook.Add("ISBN", newBook.ISBN); textbook.Add("BookTitle", newBook.Title); textbook.Add("Author", newBook.Author); textbook.Add("CourseID", courseID.ToString()); textbook.Add("BookImageURL", newBook.BookImageURL); textbook.Add("StorePrice", Convert.ToString(newBook.StorePrice)); textbook.Add("IsActive", "1"); textbook.Add("IsDeleted", "0"); textbook.Add("CreatedDate", Convert.ToString(DateTime.Now)); textbook.Add("ModifiedDate", Convert.ToString(DateTime.Now)); id = DAL.insert(textbook, "TextBooks"); } } catch (Exception ex) { Console.Write("ERROR: An error occured in adding a new textbook --- " + ex.Message); } return id; }
public static IEnumerable<Textbook> getTextbooksByCourse(string courseName) { List<Textbook> textbooks = new List<Textbook>(); try { int[] courseIDs = CourseInfoHandler.getCourseIDs(courseName); foreach (int courseID in courseIDs) { DataAccessLayer DAL = new DataAccessLayer(); DataTable dt = DAL.select(String.Format("CourseID = '{0}'", courseID), "TextBooks"); foreach (DataRow row in dt.Rows) { string isbn = Convert.ToString(row["ISBN"]); string title = Convert.ToString(row["BookTitle"]); string author = Convert.ToString(row["Author"]); string bookImageURL = Convert.ToString(row["BookImageURL"]); string full_courseName = CourseInfoHandler.getCourseName(courseID); int storePrice = Convert.ToInt32(row["StorePrice"]); Textbook textbook = new Textbook(isbn, title, author, full_courseName, bookImageURL, storePrice); textbooks.Add(textbook); } } } catch (Exception ex) { Console.Write("ERROR: An error occured in retrieving textbooks by course name --- " + ex.Message); } return textbooks; }