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);
}