private void cboSearchBy_SelectedIndexChanged(object sender, EventArgs e) { switch (cboSearchBy.Text) { case "TITLE": oFilterBook = ePublicVariable.eVariable.FILTER_BOOK.BOOK_TITLE; break; case "AUTHOR": oFilterBook = ePublicVariable.eVariable.FILTER_BOOK.BOOK_AUTHOR; break; case "CATEGORY": oFilterBook = ePublicVariable.eVariable.FILTER_BOOK.BOOK_CATEGORY; break; default: oFilterBook = ePublicVariable.eVariable.FILTER_BOOK.BOOK_TITLE; break; } }
private void MAIN_Load(object sender, EventArgs e) { LoadRecords(); oFilterBook = eVariable.FILTER_BOOK.BOOK_TITLE; }
public DataTable GetBookRecords(eVariable.FILTER_BOOK oFilter, string sStatus, string sFindText) { try { osb.ConnectionString = sConnectionString; ddq = new DatabaseQuery.DBQuery(); ddq.ConnectionString = osb.ConnectionString; string sQuery; switch (oFilter) { case eVariable.FILTER_BOOK.BOOK_TITLE: sQuery = " SELECT B.ID AS [BOOK_ID], B.TITLE,B.[SUBJECT],B.CATEGORY,B.AUTHOR,B.PUBLISH_DATE,B.BOOK_PRICE,B.RENT_PRICE,B.LOCATION, " + " COUNT(*) - " + " ((SELECT COUNT(*) FROM TBL_BORROWERREQUEST WHERE BOOK_ID = B.ID AND [STATUS] = 'REQUEST') + (SELECT COUNT(*) FROM TBL_BORROWEDBOOKS WHERE BOOK_ID = B.ID AND [STATUS] = 'BORROWED')) AS [COPIES_AVAILABLE], " + " (SELECT COUNT(*) FROM TBL_BOOKS TB WHERE TB.ID = B.ID AND TB.STATUS = 'ACTIVE')[TOTAL_COPIES] " + " FROM TBL_BOOKS AS B WHERE B.TITLE LIKE '%" + sFindText + "%' AND B.[STATUS] = '" + sStatus + "' " + " GROUP BY ID, TITLE,[SUBJECT],CATEGORY,AUTHOR,PUBLISH_DATE,BOOK_PRICE,RENT_PRICE,LOCATION "; break; case eVariable.FILTER_BOOK.BOOK_CATEGORY: sQuery = " SELECT B.ID AS [BOOK_ID], B.TITLE,B.[SUBJECT],B.CATEGORY,B.AUTHOR,B.PUBLISH_DATE,B.BOOK_PRICE,B.RENT_PRICE,B.LOCATION, " + " COUNT(*) - " + " ((SELECT COUNT(*) FROM TBL_BORROWERREQUEST WHERE BOOK_ID = B.ID AND [STATUS] = 'REQUEST') + (SELECT COUNT(*) FROM TBL_BORROWEDBOOKS WHERE BOOK_ID = B.ID AND [STATUS] = 'BORROWED')) AS [COPIES_AVAILABLE], " + " (SELECT COUNT(*) FROM TBL_BOOKS TB WHERE TB.ID = B.ID AND TB.STATUS = 'ACTIVE')[TOTAL_COPIES] " + " FROM TBL_BOOKS AS B WHERE B.CATEGORY LIKE '%" + sFindText + "%' AND B.[STATUS] = '" + sStatus + "' " + " GROUP BY ID, TITLE,[SUBJECT],CATEGORY,AUTHOR,PUBLISH_DATE,BOOK_PRICE,RENT_PRICE,LOCATION "; break; case eVariable.FILTER_BOOK.BOOK_AUTHOR: sQuery = " SELECT B.ID AS [BOOK_ID], B.TITLE,B.[SUBJECT],B.CATEGORY,B.AUTHOR,B.PUBLISH_DATE,B.BOOK_PRICE,B.RENT_PRICE,B.LOCATION, " + " COUNT(*) - " + " ((SELECT COUNT(*) FROM TBL_BORROWERREQUEST WHERE BOOK_ID = B.ID AND [STATUS] = 'REQUEST') + (SELECT COUNT(*) FROM TBL_BORROWEDBOOKS WHERE BOOK_ID = B.ID AND [STATUS] = 'BORROWED')) AS [COPIES_AVAILABLE], " + " (SELECT COUNT(*) FROM TBL_BOOKS TB WHERE TB.ID = B.ID AND TB.STATUS = 'ACTIVE')[TOTAL_COPIES] " + " FROM TBL_BOOKS AS B WHERE B.AUTHOR LIKE '%" + sFindText + "%' AND B.[STATUS] = '" + sStatus + "' " + " GROUP BY ID, TITLE,[SUBJECT],CATEGORY,AUTHOR,PUBLISH_DATE,BOOK_PRICE,RENT_PRICE,LOCATION "; break; case eVariable.FILTER_BOOK.BOOK_INACTIVE: sQuery = " SELECT B.ID AS [BOOK_ID], B.TITLE,B.[SUBJECT],B.CATEGORY,B.AUTHOR,B.PUBLISH_DATE,B.BOOK_PRICE,B.RENT_PRICE,B.LOCATION, " + " COUNT(*) - " + " ((SELECT COUNT(*) FROM TBL_BORROWERREQUEST WHERE BOOK_ID = B.ID AND [STATUS] = 'REQUEST') + (SELECT COUNT(*) FROM TBL_BORROWEDBOOKS WHERE BOOK_ID = B.ID AND [STATUS] = 'BORROWED')) AS [COPIES_AVAILABLE], " + " (SELECT COUNT(*) FROM TBL_BOOKS TB WHERE TB.ID = B.ID AND TB.STATUS = 'ACTIVE')[TOTAL_COPIES] " + " FROM TBL_BOOKS AS B WHERE B.[STATUS] = 'INACTIVE' " + " GROUP BY ID, TITLE,[SUBJECT],CATEGORY,AUTHOR,PUBLISH_DATE,BOOK_PRICE,RENT_PRICE,LOCATION "; break; case eVariable.FILTER_BOOK.NONE: sQuery = " SELECT B.ID AS [BOOK_ID], B.TITLE,B.[SUBJECT],B.CATEGORY,B.AUTHOR,B.PUBLISH_DATE,B.BOOK_PRICE,B.RENT_PRICE,B.LOCATION, " + " COUNT(*) - " + " ((SELECT COUNT(*) FROM TBL_BORROWERREQUEST WHERE BOOK_ID = B.ID AND [STATUS] = 'REQUEST') + (SELECT COUNT(*) FROM TBL_BORROWEDBOOKS WHERE BOOK_ID = B.ID AND [STATUS] = 'BORROWED')) AS [COPIES_AVAILABLE], " + " (SELECT COUNT(*) FROM TBL_BOOKS TB WHERE TB.ID = B.ID AND TB.STATUS = 'ACTIVE')[TOTAL_COPIES] " + " FROM TBL_BOOKS AS B WHERE B.[STATUS] = 'ACTIVE' " + " GROUP BY ID, TITLE,[SUBJECT],CATEGORY,AUTHOR,PUBLISH_DATE,BOOK_PRICE,RENT_PRICE,LOCATION "; break; case eVariable.FILTER_BOOK.BOOK_DEFAULT: sQuery = "SELECT * FROM TBL_BOOKS WHERE ID = '" + sFindText + "'"; break; default: sQuery = " SELECT B.ID AS [BOOK_ID], B.TITLE,B.[SUBJECT],B.CATEGORY,B.AUTHOR,B.PUBLISH_DATE,B.BOOK_PRICE,B.RENT_PRICE,B.LOCATION, " + " COUNT(*) - " + " ((SELECT COUNT(*) FROM TBL_BORROWERREQUEST WHERE BOOK_ID = B.ID AND [STATUS] = 'REQUEST') + (SELECT COUNT(*) FROM TBL_BORROWEDBOOKS WHERE BOOK_ID = B.ID AND [STATUS] = 'BORROWED')) AS [COPIES_AVAILABLE], " + " (SELECT COUNT(*) FROM TBL_BOOKS TB WHERE TB.ID = B.ID AND TB.STATUS = 'ACTIVE')[TOTAL_COPIES] " + " FROM TBL_BOOKS AS B WHERE B.[STATUS] = 'ACTIVE' " + " GROUP BY ID, TITLE,[SUBJECT],CATEGORY,AUTHOR,PUBLISH_DATE,BOOK_PRICE,RENT_PRICE,LOCATION "; break; } ddq.CommandText = sQuery; ds = ddq.GetDataset(CommandType.Text); return(ds.Tables.Count > 0 ? ds.Tables[0] : null); } catch (Exception ex) { throw ex; } }