private int? Insert(string table, ColValues[] values)
{
int? sqlcode = null;
string query = "INSERT INTO " + table + " ( ";
query += values[0].Column;
for (int i = 1; i < values.GetLength(0); i++)
query += ", " + values[i].Column ;
query += " ) VALUES ( '" + values[0].Value + "' ";
for (int i = 1; i < values.GetLength(0); i++)
query += ", '" + values[i].Value + "' ";
query += ") ; ";
//open connection
if (this.OpenConnection(true) == true)
{
//create command and assign the query and connection from the constructor
MySqlCommand cmd = new MySqlCommand(query, connection);
//Execute command
sqlcode = cmd.ExecuteNonQuery();
//close connection
this.CloseConnection();
}
return sqlcode;
}
private int? Update(string table, ColValues[] values, string condition)
{
int? sqlcode = null;
string query = "UPDATE " + table + " SET ";
// create SQL statement for update
for (int i = 0; i < values.GetLength(0); i++)
{
query += values[i].Column + "= '" + values[i].Value + "'";
if (i != values.GetLength(0) - 1) query += " , ";
}
query += " WHERE " + condition;
//Open connection
if (this.OpenConnection(true) == true)
{
//create mysql command
MySqlCommand cmd = new MySqlCommand();
//Assign the query using CommandText
cmd.CommandText = query;
//Assign the connection using Connection
cmd.Connection = connection;
//Execute query
sqlcode = cmd.ExecuteNonQuery();
//close connection
this.CloseConnection();
}
return sqlcode;
}
public ActionResult PerformAction(SQLActionType actType, string table, ColValues[] values, string condition)
{
int? sqlcode = null;
// make sure that we are not going to use empty table
if (String.IsNullOrWhiteSpace(table))
return ActionResult.TableNullOrEmpty;
switch (actType)
{
case SQLActionType.Insert:
sqlcode = this.Insert(table, values);
break;
case SQLActionType.Update:
if (!String.IsNullOrWhiteSpace(condition))
sqlcode = this.Update(table, values, condition);
break;
case SQLActionType.Delete:
if (!String.IsNullOrWhiteSpace(condition))
sqlcode = this.Delete(table, condition);
break;
case SQLActionType.Backup:
// todo
break;
case SQLActionType.Restore:
// todo
break;
case SQLActionType.Count:
// todo
break;
}
return sqlcode.HasValue || sqlcode.Value >= 0 ? ActionResult.Ok : ActionResult.OtherError;
}
