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 + "";
}