public int borrw_book(int mem, int book, string acc_date, string ret_date) { int ret; if (alredy_borrowed(book)) { MessageBox.Show("BOOK Alredy Borrowed", "Error", MessageBoxButtons.OK, MessageBoxIcon.Information); ret = -1; } else if (pending_yes(mem)) { MessageBox.Show("User alredy Borrowed 2 Books", "Error", MessageBoxButtons.OK, MessageBoxIcon.Information); ret = -1; } else { string query = "INSERT INTO tbl_borrow(mem_id,book_id,borrow_date,ret_date,pending)VALUES( " + mem + ", " + book + ", '" + acc_date + "', '" + ret_date + "','YES');" + "UPDATE tbl_Books SET bkstatus ='Unavailable' WHERE book_ID = " + book + ";"; dt = new Classes.Database(); ret = dt.updateDatabase(query); } return(ret); }
/// <summary> /// Method send informations about IP adress into database on the server /// </summary> public async void Send_IP_Raport_Into_Server() { // Preare async method Task <string> task = new Task <string>(Classes.IP_Data.Get_Data_From_IP); // Start working async task.Start(); // Create instance of Database Connection Classes.Database conn = new Classes.Database(); // Wait for results var query = await task; // Send IP data into server conn.Insert(query); // Check error flag if (conn.Error_Flag) { // Set main error flag as true Error_Flag = true; // Set main error message Error_Message = conn.Error_Message; } }
/// <summary> /// Method check account exists in Admins_List /// </summary> /// <param name="Login"></param> /// <param name="Password"></param> /// <returns> /// If exists return true /// Else return flase /// </returns> public bool Check_Admin(string Login, string Password) { if (Admins_List.Exists(x => x.Login == Login && x.Password == Password)) { Classes.Session.Admin_Login = Login; Classes.Session.Admin_Password = Password; // Create instance of Database Connection Classes.Database conn = new Classes.Database(); // Wait for results var query = $"UPDATE admins SET last_logedin = NOW() WHERE login='******'"; // Send IP data into server conn.Update(query); // Check error flag if (conn.Error_Flag) { // Set main error flag as true Error_Flag = true; // Set main error message Error_Message = conn.Error_Message; } return(true); } else { return(false); } }
public int logIN(string username, string password) { try { int result = 0; string query = "select * from tbl_Librarian where username = '******';"; Classes.Database db = new Classes.Database(); DataTable tbl = db.retriveData(query); foreach (DataRow row in tbl.Rows) { if (row["libpassword"].ToString() == password) { if (row["libtype"].ToString() == "Admin ") { result = 1; } else { result = 2; } } } return(result); } catch (Exception ex) { throw ex; } }
/// <summary> /// Method add new admin into database /// </summary> /// <param name="Database"></param> private void Add_New_Admin(Classes.Database Database) { // Prepare query var query = $"INSERT INTO admins (login, password, last_logedin) VALUES ('{Login_Textbox.Text.ToString()}', '{Password1_Textbox.Text.ToString()}', NOW())"; // Add new rew into database Database.Insert(query); }
public DataTable fill_book(int mem, int book) { dt = new Classes.Database(); string query = "SELECT * FROM tbl_Borrow WHERE mem_id =" + mem + "AND book_id = " + book + ";"; return(dt.retriveData(query)); }
/// <summary> /// Method check if in our database already exists "login" /// </summary> /// <param name="Database"></param> /// <returns> Number of found objects </returns> private int Check_If_Exists(Classes.Database Database) { // Prepare query var query = $"SELECT COUNT(*) FROM admins WHERE login = '******'"; // Send ask // Return result return(Database.Count(query)); }
private void Create_Button_Click(object sender, EventArgs e) { Classes.Database Database = new Classes.Database(); bool flag = true; // Check if login already exists in database if (Check_If_Exists(Database) > 0) { MessageBox.Show("Nick reserved", "Nick error"); Login_Textbox.BackColor = System.Drawing.Color.Red; flag = false; } if (Login_Textbox.Text.Length < 6) { MessageBox.Show("Login must have at list 7 chars", "Password error"); Login_Textbox.BackColor = System.Drawing.Color.Red; flag = false; } // Check password length if (Password1_Textbox.Text.Length < 6) { MessageBox.Show("Password must have at list 7 chars", "Password error"); Password1_Textbox.BackColor = System.Drawing.Color.Red; Password2_Textbox.BackColor = System.Drawing.Color.Red; flag = false; } // Comparing passwords if (Password1_Textbox.Text != Password2_Textbox.Text) { MessageBox.Show("Different passwords", "Password error"); Password1_Textbox.BackColor = System.Drawing.Color.Red; Password2_Textbox.BackColor = System.Drawing.Color.Red; flag = false; } if (flag) { // Create admin account Add_New_Admin(Database); // Close Create_Admin form this.Close(); } }
public DataTable searchmem(int memid) { string query = @"select * from tbl_Member where mem_ID = " + memid; try { dt = new Classes.Database(); return(dt.retriveData(query)); } catch (Exception ex) { throw ex; } }
public int delete_member(int mem) { try { string query = "DELETE FROM tbl_Member WHERE mem_ID = " + mem; dt = new Classes.Database(); int ret = dt.updateDatabase(query); return(ret); } catch (Exception ex) { throw ex; } }
public DataTable findb_book(int mem) { try { // Db Connection dt = new Classes.Database(); string query = "SELECT tbl_borrow.book_id,book_name,fine_day FROM tbl_Borrow,tbl_Books WHERE mem_id =" + mem + " AND pending= 'YES' AND tbl_borrow.book_id = tbl_Books.book_ID;"; return(dt.retriveData(query)); } catch (Exception ex) { throw ex; } }
public DataTable searchLibrarian(string keywords) { Classes.Database dt = new Classes.Database(); string query = @"SELECT * From tbl_Librarian WHERE libFirstName = '" + keywords + "' OR libFirstName LIKE '%" + keywords + "%' " + "libLastName = '" + keywords + "' OR libLastName LIKE '%" + keywords + "%' ;"; try { dt = new Classes.Database(); return(dt.retriveData(query)); } catch (Exception ex) { throw ex; } }
public DataTable searchbk(int bkid) { string query = @"select * from tbl_Books where book_ID = " + bkid; // query to select by book id try { dt = new Classes.Database(); return(dt.retriveData(query)); // return the data as a data table } catch (Exception ex) { throw ex; } }
public int delete_book(int bkID) { try { string query = "DELETE FROM tbl_Books WHERE book_ID = " + bkID; // SQL querry to delete the book record dt = new Classes.Database(); int ret = dt.updateDatabase(query); return(ret); // returing the result of the query 1 == success ,other unsuccesfull } catch (Exception ex) { throw ex; } }
public int addMember(string FName, string LName, string line1, string city, string contact) { try { string query = "INSERT INTO tbl_Member(memFirstName,memLastName,memaddressLine1,memaddressLine2,memcontactNumber) VALUES(' " + FName + "' , '" + LName + "' , '" + line1 + "' , '" + city + "' , '" + contact + "' )"; dt = new Classes.Database(); Console.WriteLine(query); int ret = dt.updateDatabase(query); return(ret); } catch (Exception ex) { throw ex; } }
/// <summary> /// Method load list of packs from the server /// </summary> public void Load_Packs_List() { // Create instance of Database Connection Classes.Database conn = new Classes.Database(); // Load list of packs from server conn.Select_Packs(ref Packs_List); // Check error flag if (conn.Error_Flag) { // Set main error flag as true Error_Flag = true; // Set main error message Error_Message = conn.Error_Message; } }
public int addPayment(string p_type, float p_amt, string p_details, int libID, int memID, string curr_time) { try { dt = new Classes.Database(); string query = "INSERT INTO tbl_Payment (payType, paymentAmount, paymentDetails,lib_id,mem_id,date_time) VALUES('" + p_type + "' , " + p_amt + " , '" + p_details + "' , " + libID + " , " + memID + ", '" + curr_time + "' )"; MessageBox.Show(query); int ret = dt.updateDatabase(query); return(ret); } catch (Exception ex) { throw ex; } }
public int update_member(string FName, string LName, string line1, string city, string contact, int memid) { try { string query = "UPDATE tbl_Member SET memFirstName = '" + FName + "', memLastName = '" + LName + "' ,memaddressLine1 ='" + line1 + "', memaddressLine2 = '" + city + "', memcontactNumber = '" + contact + "' WHERE mem_ID =" + memid; dt = new Classes.Database(); Console.WriteLine(query); int ret = dt.updateDatabase(query); return(ret); } catch (Exception ex) { throw ex; } }
public DataTable bookSearch(string choice, string keywords) { //string query = "SELECT * From tbl_Books WHERE @combo = '@input' OR @combo like '%@input%' "; string query = @"SELECT book_name AS Name, auther AS Auther, genre AS Genre, bklocation AS Location, bkstatus AS Status" + " From tbl_Books WHERE " + choice + " = '" + keywords + "'" + "OR " + choice + " LIKE '%" + keywords + "%';"; // Query to get Book List When the user search for the Book try { dt = new Classes.Database(); return(dt.retriveData(query)); // Return the List of Books as a Data Table according to the keywords and by book name or auther } catch (Exception ex) { throw ex; } }
public int addLibrarian(string FName, string LName, string line1, string city, string contact, string uername, string password, string libtype) { try { string query = "INSERT INTO tbl_Librarian(libFirstName,libLastName,addressLine1,addressLine2,contactNumber,username,libpassword,libtype)VALUES(" + "'" + FName + "'" + "," + "'" + LName + "'" + "," + "'" + line1 + "'" + "," + "'" + city + "'" + "," + "'" + contact + "'" + "," + "'" + uername + "'" + "," + "'" + password + "'" + "," + "'" + libtype + "'" + ")"; Classes.Database db = new Classes.Database(); int ret = db.updateDatabase(query); return(ret); } catch (Exception ex) { throw ex; } }
public int updateBook(int bkID, string BookName, string Auther, string Genre, float Price, float FineDay, string Location, string Status) { try { // Db Connection dt = new Classes.Database(); string query = "UPDATE tbl_Books SET book_name ='" + BookName + "', auther = '" + Auther + "',genre ='" + Genre + "', price = " + Price + " ,fine_day =" + FineDay + ",bklocation = '" + Location + "', bkstatus ='" + Status + "' WHERE book_ID = " + bkID; // SQL Auerry to update Book Details int ret = dt.updateDatabase(query); // runnig the SQL query return(ret); // returing the result of the query 1 == success ,other unsuccesfull } catch (Exception ex) { throw ex; } }
// Real Function public int addBook(string BookName, string Auther, string Genre, float Price, float FineDay, string Location, string Status) { try { // Db Connection dt = new Classes.Database(); string query = "INSERT INTO tbl_Books(book_name,auther,genre,price,fine_day,bklocation,bkstatus)VALUES(" + "'" + BookName + "'" + "," + "'" + Auther + "'" + "," + "'" + Genre + "'" + "," + Price + "," + FineDay + "," + "'" + Location + "'" + "," + "'" + Status + "'" + ")"; // SQL Auerry to insert Book Details int ret = dt.updateDatabase(query); // runnig the SQL query return(ret); } catch (Exception ex) { throw ex; } }
public int ret_book(int mem, int book, string date, float fine) { try { // Db Connection string query = "UPDATE tbl_Books SET bkstatus ='Available' WHERE book_ID = " + book + "; " + "UPDATE tbl_borrow SET pending ='NO' ,fine = " + fine + " WHERE book_id = " + book + "AND mem_id =" + mem + ";"; DateTime today = DateTime.Today; dt = new Classes.Database(); int ret = dt.updateDatabase(query); return(ret); } catch (Exception ex) { throw ex; } }
/// <summary> /// Method create & send informations about PC adress into database on the server /// </summary> /// <param name="query"></param> /// <returns></returns> public void Send_PC_Raport_Into_Server() { // Create class with informations about user's PC Classes.PC_Data A = new Classes.PC_Data(); var query = A.Query; // Create instance of Database Connection Classes.Database conn = new Classes.Database(); // Load list of packs from server conn.Insert(query); // Check error flag if (conn.Error_Flag) { // Set main error flag as true Error_Flag = true; // Set main error message Error_Message = conn.Error_Message; } }
private Boolean pending_yes(int memid) { try { dt = new Classes.Database(); string query = "SELECT * FROM tbl_Borrow WHERE mem_id =" + memid + "AND pending = 'YES';"; Boolean pending; DataTable tbl = dt.retriveData(query); if (tbl.Rows.Count >= 2) { pending = true; } else { pending = false; } return(pending); } catch (Exception) { throw; } }
private Boolean alredy_borrowed(int bookid) { string query = @"select * from tbl_Books where book_ID = " + bookid + " AND bkstatus ='Unavailable'"; try { dt = new Classes.Database(); Boolean borrowed; DataTable tbl = dt.retriveData(query); if (tbl.Rows.Count == 1) { borrowed = true; } else { borrowed = false; } return(borrowed); } catch (Exception ex) { throw ex; } }
public void Init() { this.DatabaseList = new List <Classes.Database>(); DirectoryInfo cfg = new DirectoryInfo(@"Config\Datenbanken"); if (cfg.Exists) { foreach (FileInfo fi in cfg.GetFiles()) { if (fi.Extension != ".xml") { continue; } XmlDocument doc = new XmlDocument(); try { doc.Load(fi.FullName); } catch { Controllers.LogController.LogError($"Could not load file {fi.FullName}"); } Classes.Database db = new Classes.Database(); XmlElement root = doc.DocumentElement; if (!root.HasAttribute("key") | !root.HasAttribute("name")) { continue; } db.Name = root.GetAttribute("name"); db.KeyName = root.GetAttribute("key"); foreach (XmlNode node in root.SelectNodes("//filters//filter")) { if (db.Filter.Contains(node.InnerText) | node.InnerText == null) { continue; } db.Filter.Add(node.InnerText); if (node.Attributes["single"] != null && node.Attributes["single"].Value == "J") { db.SingeFilters.Add(node.InnerText); } if (node.Attributes["dependency"] != null && node.Attributes["dependency"].Value != "") { db.FilterDependencies.Add(new Tuple <string, string>(node.InnerText, node.Attributes["dependency"].Value)); } } this.DatabaseList.Add(db); } } this.actSite = 0; this.cps = 10; this.newSite = -1; this.database = this.DatabaseList.FirstOrDefault(); var b = SetPartialList(); }