//Retrieve values from database
public List <Lure> getLuresFromDatabase()
{
//Declare list of Lures, Query database
List <Lure> allLures = new List <Lure>();
string select = "SELECT * FROM LURES";
comm = new OleDbCommand(select, conn);
reader = comm.ExecuteReader(CommandBehavior.CloseConnection);
//Loop through database, read values
while (reader.Read())
{
string id = reader["LureID"].ToString();
string manufacturer = reader["Manufacturer"].ToString();
string name = reader["LureName"].ToString();
string type = reader["LureType"].ToString();
string length = reader["Length"].ToString();
string weight = reader["Weight"].ToString();
string color = reader["Color"].ToString();
string price = reader["Price"].ToString();
string description = reader["LureDescription"].ToString();
string picture = reader["PictureName"].ToString();
//Add properties of a lure, method called from Lure class
Lure aLure = new Lure(id, manufacturer, name, type, length, weight, color, price, description, picture);
allLures.Add(aLure);
}
//Close connection and return list of lures
reader.Close();
return(allLures);
}
private void btnAdd_Click(object sender, EventArgs e)
{
Lure newLure = new Lure();
theLures.Add(newLure);
lureBindingSource.MoveLast();
addingNew = true;
btnViewPhoto.Text = "Add Photo";
enableControls();
}
private void btnAdd_Click(object sender, EventArgs e)
{
Lure newLure = new Lure();
theLures.Add(newLure);
lureBindingSource.MoveLast();
addingNew = true;
btnViewPhoto.Text = "Add Photo";
enableControls();
}
//Edit lure
public void editLure(Lure aLure)
{
//Try Catch statement
try
{
//Open connection to database, execute command to delete lure with matching LureID
conn.Open();
string updateLure = "UPDATE LURES SET Manufacturer = ?, LureName = ?, LureType = ?, Length = ?, Weight = ?, Color = ?, Price = ?, LureDescription = ?, PictureName = ? WHERE LureID = ?";
comm = new OleDbCommand(updateLure, conn);
//Initialize new instance and pass in new parameter values
OleDbParameter manufacturerParam = new OleDbParameter("Manufacturer", aLure.LureManufacturer);
OleDbParameter nameParam = new OleDbParameter("LureName", aLure.LureName);
OleDbParameter typeParam = new OleDbParameter("LureType", aLure.LureType);
OleDbParameter lengthParam = new OleDbParameter("Length", aLure.LureLength);
OleDbParameter weightParam = new OleDbParameter("Weight", aLure.LureWeight);
OleDbParameter colorParam = new OleDbParameter("Color", aLure.LureColor);
OleDbParameter priceParam = new OleDbParameter("Price", aLure.LurePrice);
OleDbParameter descriptionParam = new OleDbParameter("LureDescription", aLure.LureDescription);
OleDbParameter pictureParam = new OleDbParameter("PictureName", aLure.PictureName);
OleDbParameter lureIDParam = new OleDbParameter("LureID", aLure.LureID);
//Add new parameters for edited lure
comm.Parameters.Add(manufacturerParam);
comm.Parameters.Add(nameParam);
comm.Parameters.Add(typeParam);
comm.Parameters.Add(lengthParam);
comm.Parameters.Add(weightParam);
comm.Parameters.Add(colorParam);
comm.Parameters.Add(priceParam);
comm.Parameters.Add(descriptionParam);
comm.Parameters.Add(pictureParam);
comm.Parameters.Add(lureIDParam);
comm.ExecuteNonQuery();
conn.Close();
}
catch (OleDbException ex)
{
//Error message when exception is caught
MessageBox.Show(ex.Message);
}
}
public void addNewLureToDatabase(Lure aLure)
{
//Try Catch statement
try
{
//Open connection to database, execute command to insert new lure values
conn.Open();
string insertLure = "INSERT INTO LURES (Manufacturer, LureName, LureType, Length, Weight, Color, Price, LureDescription, PictureName) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
comm = new OleDbCommand(insertLure, conn);
//Initialize new instance and pass in new parameter values
OleDbParameter manufacturerParam = new OleDbParameter("Manufacturer", aLure.LureManufacturer);
OleDbParameter nameParam = new OleDbParameter("LureName", aLure.LureName);
OleDbParameter typeParam = new OleDbParameter("LureType", aLure.LureType);
OleDbParameter lengthParam = new OleDbParameter("Length", aLure.LureLength);
OleDbParameter weightParam = new OleDbParameter("Weight", aLure.LureWeight);
OleDbParameter colorParam = new OleDbParameter("Color", aLure.LureColor);
OleDbParameter priceParam = new OleDbParameter("Price", aLure.LurePrice);
OleDbParameter descriptionParam = new OleDbParameter("LureDescription", aLure.LureDescription);
OleDbParameter pictureParam = new OleDbParameter("PictureName", aLure.PictureName);
//Add new parameters for lure
comm.Parameters.Add(manufacturerParam);
comm.Parameters.Add(nameParam);
comm.Parameters.Add(typeParam);
comm.Parameters.Add(lengthParam);
comm.Parameters.Add(weightParam);
comm.Parameters.Add(colorParam);
comm.Parameters.Add(priceParam);
comm.Parameters.Add(descriptionParam);
comm.Parameters.Add(pictureParam);
comm.ExecuteNonQuery();
}
catch (OleDbException ex)
{
//Error message when exception is caught
MessageBox.Show(ex.Message);
}
}
public void addNewLureToDatabase(Lure aLure)
{
//Try Catch statement
try
{
//Open connection to database, execute command to insert new lure values
conn.Open();
string insertLure = "INSERT INTO LURES (Manufacturer, LureName, LureType, Length, Weight, Color, Price, LureDescription, PictureName) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
comm = new OleDbCommand(insertLure, conn);
//Initialize new instance and pass in new parameter values
OleDbParameter manufacturerParam = new OleDbParameter("Manufacturer", aLure.LureManufacturer);
OleDbParameter nameParam = new OleDbParameter("LureName", aLure.LureName);
OleDbParameter typeParam = new OleDbParameter("LureType", aLure.LureType);
OleDbParameter lengthParam = new OleDbParameter("Length", aLure.LureLength);
OleDbParameter weightParam = new OleDbParameter("Weight", aLure.LureWeight);
OleDbParameter colorParam = new OleDbParameter("Color", aLure.LureColor);
OleDbParameter priceParam = new OleDbParameter("Price", aLure.LurePrice);
OleDbParameter descriptionParam = new OleDbParameter("LureDescription", aLure.LureDescription);
OleDbParameter pictureParam = new OleDbParameter("PictureName", aLure.PictureName);
//Add new parameters for lure
comm.Parameters.Add(manufacturerParam);
comm.Parameters.Add(nameParam);
comm.Parameters.Add(typeParam);
comm.Parameters.Add(lengthParam);
comm.Parameters.Add(weightParam);
comm.Parameters.Add(colorParam);
comm.Parameters.Add(priceParam);
comm.Parameters.Add(descriptionParam);
comm.Parameters.Add(pictureParam);
comm.ExecuteNonQuery();
}
catch (OleDbException ex)
{
//Error message when exception is caught
MessageBox.Show(ex.Message);
}
}
//Delete lure from database
public void deleteLureFromDatabase(Lure aLure)
{
//Try Catch statement
try
{
//Open connection to database, execute sql statement to delete lure with matching LureID value
conn.Open();
string del = "DELETE FROM LURES WHERE LureID = ?";
comm = new OleDbCommand(del, conn);
OleDbParameter paramID = new OleDbParameter("LureID", aLure.LureID);
comm.Parameters.Add(paramID);
comm.ExecuteNonQuery();
conn.Close();
}
catch (OleDbException ex)
{
//Error message when exception is caught
MessageBox.Show(ex.Message);
}
}
//Delete lure from database
public void deleteLureFromDatabase(Lure aLure)
{
//Try Catch statement
try
{
//Open connection to database, execute sql statement to delete lure with matching LureID value
conn.Open();
string del = "DELETE FROM LURES WHERE LureID = ?";
comm = new OleDbCommand(del, conn);
OleDbParameter paramID = new OleDbParameter("LureID", aLure.LureID);
comm.Parameters.Add(paramID);
comm.ExecuteNonQuery();
conn.Close();
}
catch (OleDbException ex)
{
//Error message when exception is caught
MessageBox.Show(ex.Message);
}
}
private void btnEdit_Click(object sender, EventArgs e)
{
aLure = new Lure(lureIDTextBox.Text, lureManufacturerTextBox.Text, lureNameTextBox.Text, lureTypeTextBox.Text, lureLengthTextBox.Text, lureWeightTextBox.Text, lureColorTextBox.Text, lurePriceTextBox.Text, lureDescriptionTextBox.Text, pictureNameTextBox.Text);
enableControls();
}
private void lureBindingSource_CurrentChanged(object sender, EventArgs e)
{
currentLure = (Lure)lureBindingSource.Current;
}
private void lureBindingSource_CurrentChanged(object sender, EventArgs e)
{
currentLure = (Lure)lureBindingSource.Current;
}
private void btnEdit_Click(object sender, EventArgs e)
{
aLure = new Lure(lureIDTextBox.Text, lureManufacturerTextBox.Text, lureNameTextBox.Text, lureTypeTextBox.Text, lureLengthTextBox.Text, lureWeightTextBox.Text, lureColorTextBox.Text, lurePriceTextBox.Text, lureDescriptionTextBox.Text, pictureNameTextBox.Text);
enableControls();
}
//Retrieve values from database
public List<Lure> getLuresFromDatabase()
{
//Declare list of Lures, Query database
List<Lure> allLures = new List<Lure>();
string select = "SELECT * FROM LURES";
comm = new OleDbCommand(select, conn);
reader = comm.ExecuteReader(CommandBehavior.CloseConnection);
//Loop through database, read values
while (reader.Read())
{
string id = reader["LureID"].ToString();
string manufacturer = reader["Manufacturer"].ToString();
string name = reader["LureName"].ToString();
string type = reader["LureType"].ToString();
string length = reader["Length"].ToString();
string weight = reader["Weight"].ToString();
string color = reader["Color"].ToString();
string price = reader["Price"].ToString();
string description = reader["LureDescription"].ToString();
string picture = reader["PictureName"].ToString();
//Add properties of a lure, method called from Lure class
Lure aLure = new Lure(id, manufacturer, name, type, length, weight, color, price, description, picture);
allLures.Add(aLure);
}
//Close connection and return list of lures
reader.Close();
return allLures;
}
//Edit lure
public void editLure(Lure aLure)
{
//Try Catch statement
try
{
//Open connection to database, execute command to delete lure with matching LureID
conn.Open();
string updateLure = "UPDATE LURES SET Manufacturer = ?, LureName = ?, LureType = ?, Length = ?, Weight = ?, Color = ?, Price = ?, LureDescription = ?, PictureName = ? WHERE LureID = ?";
comm = new OleDbCommand(updateLure, conn);
//Initialize new instance and pass in new parameter values
OleDbParameter manufacturerParam = new OleDbParameter("Manufacturer", aLure.LureManufacturer);
OleDbParameter nameParam = new OleDbParameter("LureName", aLure.LureName);
OleDbParameter typeParam = new OleDbParameter("LureType", aLure.LureType);
OleDbParameter lengthParam = new OleDbParameter("Length", aLure.LureLength);
OleDbParameter weightParam = new OleDbParameter("Weight", aLure.LureWeight);
OleDbParameter colorParam = new OleDbParameter("Color", aLure.LureColor);
OleDbParameter priceParam = new OleDbParameter("Price", aLure.LurePrice);
OleDbParameter descriptionParam = new OleDbParameter("LureDescription", aLure.LureDescription);
OleDbParameter pictureParam = new OleDbParameter("PictureName", aLure.PictureName);
OleDbParameter lureIDParam = new OleDbParameter("LureID", aLure.LureID);
//Add new parameters for edited lure
comm.Parameters.Add(manufacturerParam);
comm.Parameters.Add(nameParam);
comm.Parameters.Add(typeParam);
comm.Parameters.Add(lengthParam);
comm.Parameters.Add(weightParam);
comm.Parameters.Add(colorParam);
comm.Parameters.Add(priceParam);
comm.Parameters.Add(descriptionParam);
comm.Parameters.Add(pictureParam);
comm.Parameters.Add(lureIDParam);
comm.ExecuteNonQuery();
conn.Close();
}
catch (OleDbException ex)
{
//Error message when exception is caught
MessageBox.Show(ex.Message);
}
}
