public static List <FoodStuff> ListAllIngredients()
{
//displays all foodstuffs that ARE atomic items (that is, base ingredients)
//this is determined by the RecipeMaterials entries: items where the Makes and MadeOf fields are different
//are the ones we DO want this function to return.
List <FoodStuff> lstFoods = new List <FoodStuff>();
try
{
string query = "Select * from Foodstuff where FoodstuffID in (select Makes from RecipeMaterials where Makes = MadeOf)"; //Not equals for Access is <> not !=
OleDbCommand cmd = new OleDbCommand(query, conn);
DataConnection.OpenConnection();
OleDbDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
FoodStuff fs = new FoodStuff(reader.GetString(0),
reader.GetString(1));
lstFoods.Add(fs);
}
reader.Close();
}
catch (Exception ex)
{
//don't do anything, apparently can't post messageboxes out of this thing
}
finally
{
DataConnection.CloseConnection();
}
return(lstFoods);
}
public static string TestInsert(string inID, string inValue)
{
try
{
string query = "insert into Testing(testID, testvalue) values (?,?)";
OleDbCommand cmd = new OleDbCommand(query, conn);
//parameterized queries need their parameters added in the order they're used in the query
//first is the placeholder character in the query string above
//next is the data type, for string data varchar probably works for most purposes
//next is the size of the parameter, for strings this is the length property
//set it equal to the variable you want to use in the query.
cmd.Parameters.Add("?", OleDbType.VarChar, inID.Length).Value = inID;
cmd.Parameters.Add("?", OleDbType.VarChar, inValue.Length).Value = inValue;
DataConnection.OpenConnection();
int rowsAdded = cmd.ExecuteNonQuery(); //for insertions
return(rowsAdded.ToString() + " rows added.");
}
catch (Exception ex)
{
return(ex.ToString());
}
finally
{
DataConnection.CloseConnection();
}
}
public static List <Recipe> ListOfIngredients(string makesID = "")
{
//find all recipe elements that go into the foodstuff with this ID
//if no id given, returns contents of entire BillofMaterials table
List <Recipe> resultRMs = new List <Recipe>();
try
{
string query = "select * from RecipeMaterials";
if (makesID != "")
{
query += " where Makes like ?";
}
OleDbCommand cmd = new OleDbCommand(query, conn);
if (makesID != "")
{
cmd.Parameters.Add("?", OleDbType.VarChar).Value = makesID;
}
DataConnection.OpenConnection();
OleDbDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
string[] results = new string[4];
results[0] = reader.GetString(0);
results[1] = reader.GetString(1);
if (reader[2] == DBNull.Value)
{
results[2] = "";
}
else
{
results[2] = reader.GetString(2);
}
if (reader[3] == DBNull.Value)
{
results[3] = "";
}
else
{
results[3] = reader.GetString(3);
}
resultRMs.Add(new Recipe(results[0], results[1], results[2], results[3]));
}
reader.Close();
}
catch (Exception ex)
{ }
finally
{
DataConnection.CloseConnection();
}
return(resultRMs);
}
public static List <FoodStuff> ListAllFoodstuffs()
{
//displays all foodstuffs that are not atomic items (that is, base ingredients)
//this is determined by the RecipeMaterials entries: items where the Makes and MadeOf fields are identical
//are atomic items. These are the ones we DO NOT want this function to return.
List <FoodStuff> lstFoods = new List <FoodStuff>();
try
{
string query = "Select * from Foodstuff where FoodstuffID in (select Makes from RecipeMaterials where Makes <> MadeOf)"; //Not equals for Access is <> not !=
OleDbCommand cmd = new OleDbCommand(query, conn);
DataConnection.OpenConnection();
OleDbDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
List <Recipe> lstMats = DataConnection.ListOfIngredients(reader.GetString(0));
FoodStuff fs = new FoodStuff(reader.GetString(0),
reader.GetString(1),
reader.IsDBNull(2) ? "" : reader.GetString(2),
reader.IsDBNull(3) ? -1 : reader.GetInt32(3),
reader.IsDBNull(4) ? -1 : reader.GetInt32(4),
reader.IsDBNull(5) ? -1.0 : reader.GetDouble(5),
reader.IsDBNull(6) ? -1 : reader.GetInt32(6),
null,
lstMats);
//tokenize the tags from their long string stored in the database.
if (!(reader[7] is System.DBNull))
{
string[] strTagList = reader.GetString(7).Split(',');
if (strTagList.Length > 0)
{
foreach (string t in strTagList)
{
fs.AddTag(t);
}
}
}
lstFoods.Add(fs);
}
reader.Close();
}
catch (Exception ex)
{
//don't do anything, apparently can't post messageboxes out of this thing
ex.ToString();
}
finally
{
DataConnection.CloseConnection();
}
return(lstFoods);
}
public static string AddRecipeItem(Recipe r)
{
//insert a single recipe entry in the RecipeMaterials table
//since adding a bulk of Recipe items (such as from a complex recipe) would be most easily done by a
//compound insert which Access conveniently does not support, this just needs to be called multiple times for
//each of the recipes in the Foodstuff's list to add.
try
{
//refactoring to build in only those parameters we actually need.
OleDbCommand cmd = new OleDbCommand();
string query = "insert into RecipeMaterials(Makes, MadeOf";
cmd.Parameters.Add("?", OleDbType.VarChar).Value = r.Makes;
cmd.Parameters.Add("?", OleDbType.VarChar).Value = r.MadeOf;
int intParameterCounter = 2;
if (r.FractionAmount() != "")
{
query += " , Quantity";
cmd.Parameters.Add("?", OleDbType.VarChar).Value = r.FractionAmount();
intParameterCounter++;
}
if (r.Unit != "")
{
query += ", Unit";
cmd.Parameters.Add("?", OleDbType.VarChar).Value = r.Unit;
intParameterCounter++;
}
query += ") Values(?";
for (int i = 1; i < intParameterCounter; i++) //counter is one-based
{
query += ",?";
}
query += ")";
cmd.CommandText = query;
cmd.Connection = conn;
DataConnection.OpenConnection();
cmd.ExecuteNonQuery();
return("Item added.");
}
catch (Exception ex)
{
return(ex.ToString());
}
finally
{
DataConnection.CloseConnection();
}
}
/*
* ===============================================================================
* Test methods targeting the Test table in the database, remove prior to release
* ===============================================================================
*/
public static string TestConnection()
{
try
{
DataConnection.OpenConnection();
DataConnection.CloseConnection();
return("Connection seems okay...");
}
catch (Exception ex)
{
return("Error: " + ex.ToString());
}
}
public static int NumFoodstuffs()
{
//returns a count of the number of items in the Foodstuff table
try
{
string query = "Select Count(FoodstuffID) from Foodstuff";
OleDbCommand cmd = new OleDbCommand(query, conn);
DataConnection.OpenConnection();
OleDbDataReader reader = cmd.ExecuteReader();
reader.Read();
return(reader.GetInt32(0));
}
catch (Exception ex)
{ return(-1); }
finally
{ DataConnection.CloseConnection(); }
}
public static List <string> TestSelectAll()
{
//this isn't quite as neat as the equivalent code in VB.
string query = "Select * from Testing";
OleDbCommand cmd = new OleDbCommand(query, conn);
List <string> resultset = new List <string>();
DataConnection.OpenConnection();
OleDbDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
resultset.Add(reader.GetString(0) + " " + reader.GetString(1));
}
reader.Close();
DataConnection.CloseConnection();
return(resultset);
}
public static void DeleteRecipeMaterial(Recipe recToDel)
{
try
{
string query = "delete from RecipeMaterials where Makes = ? and MadeOf = ?";
OleDbCommand cmd = new OleDbCommand(query, conn);
cmd.Parameters.Add("?", OleDbType.VarChar).Value = recToDel.Makes;
cmd.Parameters.Add("?", OleDbType.VarChar).Value = recToDel.MadeOf;
DataConnection.OpenConnection();
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
}
finally
{
DataConnection.CloseConnection();
}
}
//functions to read from, update in, and add to the database
//
//========================================================
public static string AddFoodStuff(FoodStuff fs)
{
//insert a foodstuff entry in the Foodstuff table
//will use comma-delineated string for the tags
//now contains the ingredients itself, don't need to handle them separately
try
{
//this needs to be rewritten to only insert fields and parameters for the items that the user is inserting, so the
//database will auto-default to NULL for the rest.
string query = "insert into Foodstuff(FoodstuffID, Name"; //these two must always be there
DataConnection.OpenConnection();
OleDbDataReader rdItemCount = new OleDbCommand("Select Count(Name) from Foodstuff", conn).ExecuteReader();
rdItemCount.Read();
DataConnection.CloseConnection();
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = conn;
//if statements for each value to add, and its associated parameter
cmd.Parameters.Add("?", OleDbType.VarChar).Value = fs.ID;// +Convert.ToInt32(rdItemCount[0]).ToString("0000#");
cmd.Parameters.Add("?", OleDbType.VarChar).Value = fs.Name;
int intParameterCount = 2;
if (fs.Directions != "")
{
query += ", Directions";
cmd.Parameters.Add("?", OleDbType.VarChar).Value = fs.Directions;
intParameterCount++;
}
if (fs.PrepTime > -1) //use -1, items may still have 0 prep time
{
query += ", PrepTime";
cmd.Parameters.Add("?", OleDbType.Numeric).Value = fs.PrepTime;
intParameterCount++;
}
if (fs.CookTime > -1)
{
query += ", CookTime";
cmd.Parameters.Add("?", OleDbType.Numeric).Value = fs.CookTime;
intParameterCount++;
}
if (fs.Cost > 0.0) //unlikely anything is going to be free
{
query += ", Cost";
cmd.Parameters.Add("?", OleDbType.Numeric).Value = fs.Cost;
intParameterCount++;
}
if (fs.Servings > 0)
{
query += ", Servings";
cmd.Parameters.Add("?", OleDbType.Numeric).Value = fs.Servings;
intParameterCount++;
}
if (fs.GetTags() != "")
{
query += ", Tags";
//Comma-and-space delineated list of the tags.
//When tokenizing or splitting tags, use ", " for the demarcation.
cmd.Parameters.Add("?", OleDbType.VarChar).Value = fs.GetTags();
intParameterCount++;
}
query += ") Values(?";
for (int i = 0; i < intParameterCount - 1; i++)
{
query += ",?";
}
query += ")";
cmd.CommandText = query;
DataConnection.OpenConnection();
cmd.ExecuteNonQuery();
foreach (Recipe r in fs.ReturnIngredientsList())
{
AddRecipeItem(r);
}
return("Item plus ingredients added.");
}
catch (Exception ex)
{
return(ex.ToString());
}
finally
{
DataConnection.CloseConnection();
}
}
//========================================================
//
//public static string AddFoodStuff(FoodStuff fs, List<Recipe> ingredients)
//{
// //insert a foodstuff entry in the Foodstuff table
// //will use comma-delineated string for the tags
// try
// {
// //this needs to be rewritten to only insert fields and parameters for the items that the user is inserting, so the
// //database will auto-default to NULL for the rest.
// string query = "insert into Foodstuff(FoodstuffID, Name"; //these two must always be there
// DataConnection.OpenConnection();
// OleDbDataReader rdItemCount = new OleDbCommand("Select Count(Name) from Foodstuff", conn).ExecuteReader();
// rdItemCount.Read();
// DataConnection.CloseConnection();
// OleDbCommand cmd = new OleDbCommand();
// cmd.Connection = conn;
// //if statements for each value to add, and its associated parameter
// cmd.Parameters.Add("?", OleDbType.VarChar).Value = fs.ID;// +Convert.ToInt32(rdItemCount[0]).ToString("0000#");
// cmd.Parameters.Add("?", OleDbType.VarChar).Value = fs.Name;
// int intParameterCount = 2;
// if (fs.Directions != "")
// {
// query += ", Directions";
// cmd.Parameters.Add("?", OleDbType.VarChar).Value = fs.Directions;
// intParameterCount++;
// }
// if (fs.PrepTime > -1) //use -1, items may still have 0 prep time
// {
// query += ", PrepTime";
// cmd.Parameters.Add("?", OleDbType.Numeric).Value = fs.PrepTime;
// intParameterCount++;
// }
// if (fs.CookTime > -1)
// {
// query += ", CookTime";
// cmd.Parameters.Add("?", OleDbType.Numeric).Value = fs.CookTime;
// intParameterCount++;
// }
// if (fs.Cost > 0.0) //unlikely anything is going to be free
// {
// query += ", Cost";
// cmd.Parameters.Add("?", OleDbType.Numeric).Value = fs.Cost;
// intParameterCount++;
// }
// if (fs.Servings > 0)
// {
// query += ", Servings";
// cmd.Parameters.Add("?", OleDbType.Numeric).Value = fs.Servings;
// intParameterCount++;
// }
// if (fs.GetTags() != "")
// {
// query += ", Tags";
// //Comma-and-space delineated list of the tags.
// //When tokenizing or splitting tags, use ", " for the demarcation.
// cmd.Parameters.Add("?", OleDbType.VarChar).Value = fs.GetTags();
// intParameterCount++;
// }
// query += ") Values(?";
// for (int i = 0; i < intParameterCount-1; i++)
// {
// query += ",?";
// }
// query += ")";
// cmd.CommandText = query;
// DataConnection.OpenConnection();
// cmd.ExecuteNonQuery();
// foreach (Recipe r in ingredients)
// AddRecipeItem(r);
// return "Item plus ingredients added.";
// }
// catch (Exception ex)
// {
// return ex.ToString();
// }
// finally
// {
// DataConnection.CloseConnection();
// }
//}
//public static string AddFoodStuff(FoodStuff fs, Recipe r)
//{
// //same as other but for just adding single Recipe elements instead of from a list.
// //insert a foodstuff entry in the Foodstuff table
// //will use comma-delineated string for the tags
// try
// {
// //this needs to be rewritten to only insert fields and parameters for the items that the user is inserting, so the
// //database will auto-default to NULL for the rest.
// string query = "insert into Foodstuff(FoodstuffID, Name"; //these two must always be there
// OleDbDataReader rdItemCount = new OleDbCommand("Select Count(Name) from Foodstuff", conn).ExecuteReader();
// rdItemCount.Read();
// OleDbCommand cmd = new OleDbCommand();
// cmd.Connection = conn;
// //fs.ID += Convert.ToInt32(rdItemCount[0]).ToString("0000#");
// //if statements for each value to add, and its associated parameter
// cmd.Parameters.Add("?", OleDbType.VarChar).Value = fs.ID;// +Convert.ToInt32(rdItemCount[0]).ToString("0000#");
// cmd.Parameters.Add("?", OleDbType.VarChar).Value = fs.Name;
// int intParameterCount = 2;
// if (fs.Directions != "")
// {
// query += ", Directions";
// cmd.Parameters.Add("?", OleDbType.VarChar).Value = fs.Directions;
// intParameterCount++;
// }
// if (fs.PrepTime > -1) //use -1, items may still have 0 prep time
// {
// query += ", PrepTime";
// cmd.Parameters.Add("?", OleDbType.Numeric).Value = fs.PrepTime;
// intParameterCount++;
// }
// if (fs.CookTime > -1)
// {
// query += ", CookTime";
// cmd.Parameters.Add("?", OleDbType.Numeric).Value = fs.CookTime;
// intParameterCount++;
// }
// if (fs.Cost > 0.0) //unlikely anything is going to be free
// {
// query += ", Cost";
// cmd.Parameters.Add("?", OleDbType.Numeric).Value = fs.Cost;
// intParameterCount++;
// }
// if (fs.Servings > 0)
// {
// query += ", Servings";
// cmd.Parameters.Add("?", OleDbType.Numeric).Value = fs.Servings;
// intParameterCount++;
// }
// if (fs.GetTags() != "")
// {
// query += ", Tags";
// //Comma-and-space delineated list of the tags.
// //When tokenizing or splitting tags, use ", " for the demarcation.
// cmd.Parameters.Add("?", OleDbType.VarChar).Value = fs.GetTags();
// intParameterCount++;
// }
// query += ") Values(?";
// for (int i = 1; i < intParameterCount; i++) //counter is one-based
// {
// query += ",?";
// }
// query += ")";
// cmd.CommandText = query;
// AddRecipeItem(r);
// DataConnection.OpenConnection();
// cmd.ExecuteNonQuery();
// return "Item plus ingredients added.";
// }
// catch (Exception ex)
// {
// return ex.ToString();
// }
// finally
// {
// DataConnection.CloseConnection();
// }
//}
public static void UpdateFoodstuff(FoodStuff updatedFS)
{
//need some way of identifying the column without passing in the name itself which might change.
//an enum won't work since that's just a bit of code associated with an integer.
//TODO:
//verify the record exists, if it does then update the designated column with the new value.
//Potentially ugly hack to make this work is to just update every field regardless of whether they were
//altered or not, overwriting with the same values for unaltered fields.
//it might be slower but would save the headache of trying to pick and choose the fields we need to write to.
try
{
//this needs to be rewritten to only insert fields and parameters for the items that the user is inserting, so the
//database will auto-default to NULL for the rest.
string query = "update foodstuff set "; //these two must always be there
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = conn;
//if statements for each value to add, and its associated parameter
query += "Name = ? ";
cmd.Parameters.Add("?", OleDbType.VarChar).Value = updatedFS.Name;
int intParameterCount = 1;
if (updatedFS.Directions != "")
{
query += ", Directions = ?";
cmd.Parameters.Add("?", OleDbType.VarChar).Value = updatedFS.Directions;
intParameterCount++;
}
if (updatedFS.PrepTime > -1) //use -1, items may still have 0 prep time
{
query += ", PrepTime = ?";
cmd.Parameters.Add("?", OleDbType.Numeric).Value = updatedFS.PrepTime;
intParameterCount++;
}
if (updatedFS.CookTime > -1)
{
query += ", CookTime = ? ";
cmd.Parameters.Add("?", OleDbType.Numeric).Value = updatedFS.CookTime;
intParameterCount++;
}
if (updatedFS.Cost > 0.0) //unlikely anything is going to be free
{
query += ", Cost = ? ";
cmd.Parameters.Add("?", OleDbType.Numeric).Value = updatedFS.Cost;
intParameterCount++;
}
if (updatedFS.Servings > 0)
{
query += ", Servings = ?";
cmd.Parameters.Add("?", OleDbType.Numeric).Value = updatedFS.Servings;
intParameterCount++;
}
if (updatedFS.GetTags() != "")
{
query += ", Tags = ? ";
//Comma-and-space delineated list of the tags.
//When tokenizing or splitting tags, use ", " for the demarcation.
cmd.Parameters.Add("?", OleDbType.VarChar).Value = updatedFS.GetTags();
intParameterCount++;
}
query += "where FoodstuffID = ?";
cmd.Parameters.Add("?", OleDbType.VarChar).Value = updatedFS.ID;
cmd.CommandText = query;
DataConnection.OpenConnection();
cmd.ExecuteNonQuery();
//item in fs's list isn't in ingredient list, so make (another) list of things we need to remove
//and do the exact opposite for those ingredients we need to add new entries for.
List <Recipe> IngredientsToDelete = new List <Recipe>();
List <Recipe> IngredientsToAdd = new List <Recipe>();
foreach (Recipe old_r in GetFoodstuffWithID(updatedFS.ID).ReturnIngredientsList())
{
//needs to match at least one item
bool match = false;
foreach (Recipe new_r in updatedFS.ReturnIngredientsList())
{
if (old_r == new_r)
{
match = true;
}
}
if (!match)
{
IngredientsToDelete.Add(old_r);
}
}
foreach (Recipe new_r in updatedFS.ReturnIngredientsList())
{
bool match = false;
foreach (Recipe old_r in GetFoodstuffWithID(updatedFS.ID).ReturnIngredientsList())
{
if (new_r == old_r)
{
match = true;
}
}
if (!match)
{
IngredientsToAdd.Add(new_r);
}
}
if (IngredientsToDelete.Count > 0)
{
foreach (Recipe r in IngredientsToDelete)
{
DeleteRecipeMaterial(r);
}
}
if (IngredientsToAdd.Count > 0)
{
foreach (Recipe r in IngredientsToAdd)
{
AddRecipeItem(r);
}
}
}
catch (Exception ex)
{
//return ex.ToString();
}
finally
{
DataConnection.CloseConnection();
}
}