public static void CreateVeggie(string veggieName, int yummy, string description)
{
// 1. Declare variables and set initial values
DataLayer dataLayer = new DataLayer ();
DataField[] dataFields;
DataField NameField = new DataField ();
DataField YummyField = new DataField ();
DataField DescriptionField = new DataField ();
string sql = string.Empty;
// unselect any other players because the new player will also become the selected player.
MyDataLayer.DataLayer.ExecuteNonQuery (sql);
//3. Create data field objects for each field to be created.
NameField.FieldName = "VeggieName";
NameField.FieldType = "string";
NameField.FieldValue = '"' + veggieName + '"';
YummyField.FieldName = "Yummy";
YummyField.FieldType = "int";
YummyField.FieldValue = yummy.ToString ();
DescriptionField.FieldName = "Description";
DescriptionField.FieldType = "string";
DescriptionField.FieldValue = '"' + description + '"';
dataFields = new DataField[] { NameField, YummyField, DescriptionField };
// Insert Record into database.
dataLayer.InsertRecord ("tVeggies", dataFields);
RefreshVeggieList ();
}
public string InsertRecord(string tableName, DataField[] dataFields)
{
string sql = string.Empty;
string fieldNameList = string.Empty;
string fieldValueList = string.Empty;
string response = string.Empty;
SqliteCommand sqlCommand = new SqliteCommand ();
connection = GetConnection ();
try {
connection.Open ();
// Begin transaction for multiple updates (improves efficiency)
sqlCommand = new SqliteCommand ("BEGIN", connection);
sqlCommand.ExecuteNonQuery ();
// begin individual inserts. I will nest this later to accommodate batch updates.l
sql = "INSERT INTO " + tableName + " (";
sqlCommand = new SqliteCommand ();
sqlCommand.Connection = connection;
foreach (DataField dataField in dataFields) {
fieldNameList += dataField.FieldName + ", ";
fieldValueList += dataField.FieldValue + ", ";
sqlCommand.Parameters.AddWithValue ("@" + dataField.FieldName, dataField.FieldValue);
}
// remove extra commas at the end of the lists, and append to the sql statement
sql += fieldNameList.Substring (0, fieldNameList.Length - 2);
sql += ") VALUES (";
sql += fieldValueList.Substring (0, fieldValueList.Length - 2);
sql += ")";
Console.WriteLine (sql);
// load and run sql insert statement.
sqlCommand.CommandText = sql;
sqlCommand.ExecuteNonQuery ();
sqlCommand = new SqliteCommand ("END", connection);
sqlCommand.ExecuteNonQuery ();
connection.Close ();
response = "Success";
} catch (Exception ex) {
response = ">>> DataLayer.InsertRecord Error: " + ex.ToString ();
}
// End batch commmand and close connection to the database.
Console.WriteLine ("\n DataLayer.InsertRecord Response to be returned: " + response);
return response;
}
