public int Delete(string table, string keyField, int keyValue)
{
MySqlConnection conn = DatabaseManager.GetConnection();
conn.Open();
try
{
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = conn;
cmd.CommandText = "DELETE FROM " + table + " WHERE " + keyField + " = :KEYVAL";
cmd.CommandType = CommandType.Text;
//cmd.Parameters.Add(":KEYVAL", keyValue);
MySqlParameter parameter = new MySqlParameter();
//parameter.DbType = DbType.Int32;
parameter.ParameterName = ":KEYVAL";
parameter.Value = keyValue;
cmd.Parameters.Add(parameter);
return(ExecuteNonQuery(cmd));// cmd.ExecuteNonQuery();
// Return the number of rows that were affected.
//return 0;
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
public int ExecuteSQL(String sqlQuery)
{
MySqlConnection conn = DatabaseManager.GetConnection();
// MySqlConnection conn =
conn.Open();
try
{
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = conn;
cmd.CommandText = sqlQuery;
cmd.CommandType = CommandType.Text;
if (_parameters != null && _parameters.Count > 0)
{
BindParameterList(cmd);
}
int rowsAffected = ExecuteNonQuery(cmd);// cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
_parameters.Clear();
return(rowsAffected);
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
public int Insert(string table)//, string keyField, string sequence)
{
MySqlConnection conn = DatabaseManager.GetConnection();
conn.Open();
try
{
//int identity = DatabaseManager.GetIdentity(sequence, conn);
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = conn;
cmd.CommandText = "INSERT INTO " + table + " " + BuildFieldListForInsert();// (keyField);
cmd.CommandType = CommandType.Text;
BindFieldList(cmd);
//cmd.ExecuteScalar();
ExecuteScalarCommand(cmd);
cmd.Parameters.Clear();
return((int)cmd.LastInsertedId);
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
public int UpdateWhere(string table, string keyField, int keyValue, string whereClause)
{
MySqlConnection conn = DatabaseManager.GetConnection();
conn.Open();
try
{
//"UPDATE settings SET id = :id, name = :name, value = :value WHERE id = :KEYVAL AND name=:name"
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = conn;
cmd.CommandText = "UPDATE " + table + " SET " + BuildFieldListForUpdate() + " WHERE " + keyField + " = :KEYVAL";
cmd.CommandType = CommandType.Text;
if (!String.IsNullOrEmpty(whereClause))
{
cmd.CommandText += " AND " + whereClause;
}
BindFieldList(cmd);
MySqlParameter parameter = new MySqlParameter();
parameter.DbType = DbType.Int32;
parameter.ParameterName = ":KEYVAL";
parameter.Value = keyValue;
cmd.Parameters.Add(parameter);
BindParameterList(cmd);
return(ExecuteNonQuery(cmd));// cmd.ExecuteNonQuery();
// Return the number of rows that were affected.
//return 0;
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
public void SetUpTable(string creationString)
{
MySqlConnection conn = DatabaseManager.GetConnection();
conn.Open();
try
{
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = conn;
cmd.CommandText = creationString;
cmd.CommandType = CommandType.Text;
cmd.ExecuteReader();
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
public int DeleteWhere(string table, string keyField, int keyValue, string whereClause)
{
MySqlConnection conn = DatabaseManager.GetConnection();
conn.Open();
try
{
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = conn;
cmd.CommandText = "DELETE FROM " + table + " WHERE " + keyField + " = :KEYVAL";
cmd.CommandType = CommandType.Text;
//cmd.Parameters.Add(":KEYVAL", keyValue);
MySqlParameter parameter = new MySqlParameter();
//parameter.DbType = DbType.Int32;
parameter.ParameterName = ":KEYVAL";
parameter.Value = keyValue;
cmd.Parameters.Add(parameter);
if (!String.IsNullOrEmpty(whereClause))
{
cmd.CommandText += " AND " + whereClause;
BindParameterList(cmd);
}
return(ExecuteNonQuery(cmd));// cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
public int DeleteWhere(string table, string whereClause)
{
MySqlConnection conn = DatabaseManager.GetConnection();
conn.Open();
try
{
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = conn;
cmd.CommandText = "DELETE FROM " + table + " WHERE " + whereClause;
cmd.CommandType = CommandType.Text;
BindParameterList(cmd);
return(ExecuteNonQuery(cmd));// cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
public DataView FindWhere(List <String> tables, List <DbJoin> joins = null)
{
string sqlString = "";
DataView dv = new DataView();
MySqlConnection conn = DatabaseManager.GetConnection();
MySqlDataAdapter listAdapter = new MySqlDataAdapter();
conn.Open();
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = conn;
string tableList = "";
foreach (string table in tables)
{
if (!String.IsNullOrEmpty(tableList))
{
tableList += ", ";
}
tableList += table;
}
sqlString = "SELECT " + BuildFieldValues() + " FROM " + tableList;
if (joins != null)
{
foreach (DbJoin join in joins)
{
switch (join.JoinType)
{
case JoinType.LeftJoin:
sqlString += " left join ";
break;
case JoinType.RightJoin:
sqlString += " right join ";
break;
case JoinType.FullJoin:
sqlString += " full join ";
break;
}
sqlString += join.Table + " on " + join.Criteria;
}
}
if (!String.IsNullOrEmpty(_whereClause))
{
sqlString += " WHERE " + _whereClause;
}
if (_parameters != null && _parameters.Count > 0)
{
BindParameterList(cmd);
}
if (_orderByFields != null && _orderByFields.Count > 0)
{
sqlString += " ORDER BY " + BuildOrderBy();
}
cmd.CommandText = sqlString;
cmd.CommandType = CommandType.Text;
try
{
listAdapter.SelectCommand = cmd;
DataTable dataTable = new DataTable();
listAdapter.Fill(dataTable);
dv = new DataView(dataTable);
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
return(dv);
}
public Dictionary <string, object> FindById(string table, string keyField, int keyValue)
{
MySqlConnection conn = DatabaseManager.GetConnection();
MySqlDataReader dr = null;
conn.Open();
try
{
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = conn;
cmd.CommandText = "SELECT " + BuildFieldValues() + " FROM " + table + " WHERE " + keyField + " = :" + keyField;
cmd.CommandType = CommandType.Text;
//cmd.Parameters.Add(":" + keyField, keyValue);
MySqlParameter parameter = new MySqlParameter();
parameter.DbType = DbType.Int32;
//sqlLiteParameter.DbType = (System.Data.DbType)parameter.Type;
parameter.ParameterName = "@" + keyField;
parameter.Value = keyValue;
cmd.Parameters.Add(parameter);
dr = cmd.ExecuteReader();
if (dr.Read())
{
Dictionary <String, object> vals = new Dictionary <string, object>();
int i = 0;
foreach (string s in _fields.Keys)
{
DbField f = (DbField)_fields[s];
switch (f.Type)
{
case DbType.Int32:
try { vals.Add(s, dr.GetDecimal(i)); }
catch { vals.Add(s, 0); }
break;
case DbType.String:
try { vals.Add(s, dr.GetString(i)); }
catch { vals.Add(s, ""); }
break;
//case DbTypes.Varchar:
// try { vals.Add(s, dr.GetString(i)); }
// catch { vals.Add(s, ""); }
// break;
case DbType.DateTime:
try { vals.Add(s, dr.GetDateTime(i)); }
catch { vals.Add(s, null); }
break;
case DbType.Binary:
try { vals.Add(s, dr.GetValue(i)); }
catch { vals.Add(s, null); }
break;
case DbType.Boolean:
try { vals.Add(s, dr.GetValue(i)); }
catch { vals.Add(s, ""); }
break;
case DbType.Double:
try { vals.Add(s, dr.GetValue(i)); }
catch { vals.Add(s, 0); }
break;
}
i++;
}
return(vals);
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (dr != null)
{
dr.Dispose();
}
conn.Close();
}
return(null);
}