public static void registerLoan(String mI, String dI, String i, String d) { try { OleDbConnection myConnection = DBConnectivity.GetConnection(); String myQuery = "INSERT INTO Loan (DVDID, MemberID, StartDate, ReturnDate) VALUES (@dI, @mI, @i, @d)"; OleDbCommand myCommand = new OleDbCommand(myQuery, myConnection); myCommand.Parameters.Add("@dI", OleDbType.VarChar).Value = dI; myCommand.Parameters.Add("@mI", OleDbType.VarChar).Value = mI; myCommand.Parameters.Add("@i", OleDbType.VarChar).Value = i; myCommand.Parameters.Add("@d", OleDbType.VarChar).Value = d; try { myConnection.Open(); myCommand.ExecuteNonQuery(); } catch (Exception ex) { Console.WriteLine("Exception in DBHandler", ex); } finally { myConnection.Close(); } } catch (Exception ex) { MessageBox.Show("Error connecting to database", ex.ToString()); } }
public static void returnLoan(String lI) { try { OleDbConnection myConnection = DBConnectivity.GetConnection(); String myQuery = "DELETE FROM Loan WHERE [LoanID] =" + lI + ""; OleDbCommand myCommand = new OleDbCommand(myQuery, myConnection); try { myConnection.Open(); myCommand.ExecuteNonQuery(); } catch (Exception ex) { Console.WriteLine("Exception in DBHandler", ex); } finally { myConnection.Close(); } } catch (Exception ex) { MessageBox.Show(ex.ToString()); } }
//Load Data into Combo Boxes public void loadMemberID() { try { OleDbConnection myConnection = DBConnectivity.GetConnection(); String myQuery = "SELECT MembershipID FROM Member ORDER BY MembershipID ASC"; OleDbDataAdapter dAdapter = new OleDbDataAdapter(myQuery, myConnection); DataSet ds = new DataSet(); dAdapter.Fill(ds); cboMemberID.DataSource = ds.Tables[0]; cboMemberID.ValueMember = "MembershipID"; cboLoanMemberID.DataSource = ds.Tables[0]; cboLoanMemberID.ValueMember = "MembershipID"; cboLoanCheckMemberID.DataSource = ds.Tables[0]; cboLoanCheckMemberID.ValueMember = "MembershipID"; cboReturnCheckMemberID.DataSource = ds.Tables[0]; cboReturnCheckMemberID.ValueMember = "MembershipID"; } catch (Exception ex) { MessageBox.Show(ex.ToString()); } }
private void cboLoanDVDID_SelectedIndexChanged(object sender, EventArgs e) { String cboSelectedItem = cboLoanDVDID.Text; OleDbConnection myConnection = DBConnectivity.GetConnection(); String myQuery = "SELECT DVDTitle FROM DVD WHERE DVDID=" + cboSelectedItem + ""; OleDbCommand myCommand = new OleDbCommand(myQuery, myConnection); try { myConnection.Open(); OleDbDataReader reader = myCommand.ExecuteReader(); while (reader.Read()) { txtLoanFilmTitle.Text = reader["DVDTitle"].ToString(); } reader.Close(); } catch (Exception ex) { Console.WriteLine(ex.ToString()); } finally { myConnection.Close(); } }
private void cboReturnCheckMemberID_SelectedIndexChanged(object sender, EventArgs e) { String cboSelectedItem = cboReturnCheckMemberID.Text; OleDbConnection myConnection = DBConnectivity.GetConnection(); String myQuery = "SELECT FirstName FROM Member WHERE MembershipID=" + cboSelectedItem + ""; OleDbCommand myCommand = new OleDbCommand(myQuery, myConnection); try { myConnection.Open(); OleDbDataReader reader = myCommand.ExecuteReader(); while (reader.Read()) { txtReturnMemberID.Text = reader["FirstName"].ToString(); } reader.Close(); } catch (Exception ex) { Console.WriteLine(ex.ToString()); } finally { myConnection.Close(); } }
public static OleDbDataAdapter checkInStock() { try { OleDbConnection myConnection = DBConnectivity.GetConnection(); String myQuery = "SELECT * FROM DVD WHERE Stock > 0 ORDER BY DVDID ASC"; OleDbCommand myCommand = new OleDbCommand(myQuery, myConnection); OleDbDataAdapter da = new OleDbDataAdapter(myCommand); try { myConnection.Open(); myCommand.ExecuteNonQuery(); myConnection.Close(); return(da); } catch (Exception ex) { Console.WriteLine("Exception in DBHandler", ex); myConnection.Close(); return(null); } } catch (Exception ex) { MessageBox.Show(ex.ToString()); return(null); } }
public static void deleteMember(string mI) { try { OleDbConnection myConnection = DBConnectivity.GetConnection(); String myQuery = "DELETE FROM Member WHERE [MembershipID] =" + mI + ""; OleDbCommand myCommand = new OleDbCommand(myQuery, myConnection); try { MessageBox.Show(myQuery); myConnection.Open(); myCommand.ExecuteNonQuery(); } catch (Exception ex) { Console.WriteLine("Exception in DBHandler", ex); } finally { myConnection.Close(); } } catch (Exception ex) { MessageBox.Show(ex.ToString()); } }
public static void updateMember(string mI, string ti, string fn, string ln, string a, string c, string p, string te, string ty) { OleDbConnection myConnection = DBConnectivity.GetConnection(); String myQuery = "UPDATE Member SET Title='" + ti + "', FirstName ='" + fn + "', Surname ='" + ln + "', Address ='" + a + "', County ='" + c + "', PostCode ='" + p + "', Telephone ='" + te + "', TypeOfMember ='" + ty + "' WHERE MembershipID=" + mI + ""; OleDbCommand myCommand = new OleDbCommand(myQuery, myConnection); try { myConnection.Open(); myCommand.ExecuteNonQuery(); } catch (Exception ex) { Console.WriteLine("Exception in DBHandler", ex); } finally { myConnection.Close(); } }
//Refresh Table public void refreshTable() { OleDbConnection myConnection = DBConnectivity.GetConnection(); DataTable dt = new DataTable(); switch (tabControl1.SelectedIndex) { case 0: { String myQuery = "SELECT * FROM DVD ORDER BY DVDID ASC"; OleDbCommand myCommand = new OleDbCommand(myQuery, myConnection); OleDbDataAdapter da = new OleDbDataAdapter(myCommand); da.Fill(dt); dgvLibraryTable.DataSource = dt; loadDVDID(); } break; case 1: { String myQuery = "SELECT * FROM Loan ORDER BY LoanID ASC"; OleDbCommand myCommand = new OleDbCommand(myQuery, myConnection); OleDbDataAdapter da = new OleDbDataAdapter(myCommand); da.Fill(dt); dgvLibraryTable.DataSource = dt; txtIssue.Text = (DateTime.Now.ToString("d")); txtDue.Text = (DateTime.Now.AddDays(3).ToString("d")); loadDVDID(); } break; case 2: { String myQuery = "SELECT * FROM Loan ORDER BY LoanID ASC"; OleDbCommand myCommand = new OleDbCommand(myQuery, myConnection); OleDbDataAdapter da = new OleDbDataAdapter(myCommand); da.Fill(dt); dgvLibraryTable.DataSource = dt; loadLoanID(); } break; case 3: { String myQuery = "SELECT * FROM Member ORDER BY MembershipID ASC"; OleDbCommand myCommand = new OleDbCommand(myQuery, myConnection); OleDbDataAdapter da = new OleDbDataAdapter(myCommand); da.Fill(dt); dgvLibraryTable.DataSource = dt; } break; } }
private void btnReturnCheck_Click(object sender, EventArgs e) { string checkStr = cboReturnCheckMemberID.SelectedValue.ToString(); OleDbConnection myConnection = DBConnectivity.GetConnection(); DataTable dt = new DataTable(); String myQuery = "SELECT * FROM Loan WHERE MemberID=" + checkStr + ""; OleDbCommand myCommand = new OleDbCommand(myQuery, myConnection); OleDbDataAdapter da = new OleDbDataAdapter(myCommand); da.Fill(dt); dgvLibraryTable.DataSource = dt; lblProgress.Text = "Table Updated..."; fillFilmTitle(checkStr); }
public void loadDVDID() { OleDbConnection myConnection = DBConnectivity.GetConnection(); switch (tabControl1.SelectedIndex) { case 0: case 2: { try { String myQuery = "SELECT DVDID FROM DVD ORDER BY DVDID ASC"; OleDbDataAdapter dAdapter = new OleDbDataAdapter(myQuery, myConnection); DataSet ds = new DataSet(); dAdapter.Fill(ds); cboCheckDVDID.DataSource = ds.Tables[0]; cboCheckDVDID.ValueMember = "DVDID"; } catch (Exception ex) { MessageBox.Show(ex.ToString()); } } break; case 1: { try { String myQuery = "SELECT DVDID FROM DVD WHERE Stock > 0 ORDER BY DVDID ASC"; OleDbDataAdapter dAdapter = new OleDbDataAdapter(myQuery, myConnection); DataSet ds = new DataSet(); dAdapter.Fill(ds); cboLoanDVDID.DataSource = ds.Tables[0]; cboLoanDVDID.ValueMember = "DVDID"; } catch (Exception ex) { MessageBox.Show(ex.ToString()); } break; } } }
public static void stockAdd(String a) { OleDbConnection myConnection = DBConnectivity.GetConnection(); String myQuery = "UPDATE DVD SET Stock = Stock + 1 WHERE DVDID=" + a + ""; OleDbCommand myCommand = new OleDbCommand(myQuery, myConnection); try { myConnection.Open(); myCommand.ExecuteNonQuery(); } catch (Exception ex) { Console.WriteLine(ex.ToString()); } finally { myConnection.Close(); } }
private void btnCheckFilm_Click(object sender, EventArgs e) { try { string checkStr = cboCheckDVDID.SelectedValue.ToString(); OleDbConnection myConnection = DBConnectivity.GetConnection(); DataTable dt = new DataTable(); String myQuery = "SELECT * FROM DVD WHERE DVDID=" + checkStr + ""; OleDbCommand myCommand = new OleDbCommand(myQuery, myConnection); OleDbDataAdapter da = new OleDbDataAdapter(myCommand); da.Fill(dt); dgvLibraryTable.DataSource = dt; lblProgress.Text = "Table Updated..."; fillFilmTitle(checkStr); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } }
public static void registerMember(string ti, string fn, string ln, string a, string c, string p, string te, string ty) { try { OleDbConnection myConnection = DBConnectivity.GetConnection(); String myQuery = "INSERT INTO Member(Title, FirstName, Surname, Address, County, PostCode, Telephone, TypeOfMember) VALUES (@ti, @fn, @ln, @a, @c, @p, @te, @t)"; OleDbCommand myCommand = new OleDbCommand(myQuery, myConnection); myCommand.Parameters.Add("@ti", OleDbType.VarChar).Value = ti; myCommand.Parameters.Add("@fn", OleDbType.VarChar).Value = fn; myCommand.Parameters.Add("@ln", OleDbType.VarChar).Value = ln; myCommand.Parameters.Add("@a", OleDbType.VarChar).Value = a; myCommand.Parameters.Add("@c", OleDbType.VarChar).Value = c; myCommand.Parameters.Add("@p", OleDbType.VarChar).Value = p; myCommand.Parameters.Add("@te", OleDbType.VarChar).Value = te; myCommand.Parameters.Add("@ty", OleDbType.VarChar).Value = ty; try { myConnection.Open(); myCommand.ExecuteNonQuery(); } catch (Exception ex) { Console.WriteLine("Exception in DBHandler", ex); } finally { myConnection.Close(); } } catch (Exception ex) { MessageBox.Show("Error connecting to database"); } }
//Aquire information to edit the Stock field in the database public void stock() { String DVDminus = cboLoanDVDID.Text; String DVDadd = cboReturnLoanID.Text; if (tabControl1.SelectedIndex == 1) { OleDbConnection myConnection = DBConnectivity.GetConnection(); String myQuery = "SELECT DVDID FROM DVD WHERE DVDID=" + DVDminus + ""; OleDbCommand myCommand = new OleDbCommand(myQuery, myConnection); int DVDID; String DVDIDStr; try { myConnection.Open(); OleDbDataReader reader = myCommand.ExecuteReader(); while (reader.Read()) { DVDID = Int32.Parse(reader["DVDID"].ToString()); DVDIDStr = DVDID.ToString(); Loan.stockMinus(DVDminus); } reader.Close(); } catch (Exception ex) { Console.WriteLine(ex.ToString()); } finally { myConnection.Close(); } } if (tabControl1.SelectedIndex == 2) { OleDbConnection myConnection = DBConnectivity.GetConnection(); String myQuery = "SELECT DVDID FROM Loan WHERE LoanID=" + DVDadd + ""; OleDbCommand myCommand = new OleDbCommand(myQuery, myConnection); String DVDIDStr; try { myConnection.Open(); OleDbDataReader reader = myCommand.ExecuteReader(); while (reader.Read()) { DVDIDStr = reader["DVDID"].ToString(); Loan.stockAdd(DVDIDStr); } reader.Close(); } catch (Exception ex) { Console.WriteLine(ex.ToString()); } finally { myConnection.Close(); } } refreshTable(); }
public void fillFilmTitle(string cS) { OleDbConnection myConnection = DBConnectivity.GetConnection(); DataTable dt = new DataTable(); String myQuery = "SELECT DVDTitle FROM DVD WHERE DVDID=" + cS + ""; }