/// <summary>
/// Populates MySqlCommand with parameters from CRUD Query object
/// Note: this code was purposely made provider specific because other providers may treat some nuances differently
/// </summary>
public void PopulateParameters(MySqlCommand cmd, Query query)
{
foreach (var par in query.Where(p => p.HasValue))
{
cmd.Parameters.AddWithValue(par.Name, par.Value);
}
if (query.StoreKey != null)
{
var where = GeneratorUtils.KeyToWhere(query.StoreKey, cmd.Parameters);
cmd.CommandText += "\n WHERE \n {0}".Args(where);
}
CRUDGenerator.ConvertParameters(m_Store, cmd.Parameters);
}
public Cursor OpenCursor(ICRUDQueryExecutionContext context, Query query)
{
var ctx = (MySQLCRUDQueryExecutionContext)context;
var target = ctx.DataStore.TargetName;
Schema.FieldDef[] toLoad;
Schema schema = null;
MySqlDataReader reader = null;
var cmd = ctx.Connection.CreateCommand();
try
{
cmd.CommandText = m_Source.StatementSource;
PopulateParameters(cmd, query);
cmd.Transaction = ctx.Transaction;
try
{
reader = cmd.ExecuteReader();
GeneratorUtils.LogCommand(ctx.DataStore.LogLevel, "queryhandler-ok", cmd, null);
}
catch (Exception error)
{
GeneratorUtils.LogCommand(ctx.DataStore.LogLevel, "queryhandler-error", cmd, error);
throw;
}
schema = GetSchemaForQuery(target, query, reader, m_Source, out toLoad);
}
catch
{
if (reader != null)
{
reader.Dispose();
}
cmd.Dispose();
throw;
}
var enumerable = execEnumerable(ctx, cmd, reader, schema, toLoad, query);
return(new MySQLCursor(ctx, cmd, reader, enumerable));
}
public static void Delete(MySQLDataStoreBase store, MySqlConnection cnn, ModelBase instance, IDataStoreKey key, object[] extra)
{
var record = GeneratorUtils.AsSuitableRecordInstance(instance, true);
using (var cmd = cnn.CreateCommand())
{
var pk = key ?? record.DataStoreKey;
if (pk == null)
{
throw new MySQLDataAccessException(StringConsts.KEY_UNAVAILABLE_ERROR);
}
var where = GeneratorUtils.KeyToWhere(pk, cmd.Parameters);
if (!string.IsNullOrEmpty(where))
{
cmd.CommandText = string.Format("DELETE T1 FROM `{0}` T1 WHERE {1}", record.TableName, where);
}
else
{
cmd.CommandText = string.Format("DELETE T1 FROM `{0}` T1", record.TableName);
}
var affected = 0;
try
{
affected = cmd.ExecuteNonQuery();
GeneratorUtils.LogCommand(store.LogLevel, "rmdelete-ok", cmd, null);
}
catch (Exception error)
{
GeneratorUtils.LogCommand(store.LogLevel, "rmdelete-error", cmd, error);
throw;
}
if (affected == 0)
{
throw new MySQLDataAccessException(string.Format(StringConsts.NO_ROWS_AFFECTED_ERROR, "Delete"));
}
}//using command
}
private static int crudDelete(MySQLDataStoreBase store, MySqlConnection cnn, MySqlTransaction trans, Row row, IDataStoreKey key)
{
var target = store.TargetName;
string tableName = getTableName(row.Schema, target);
using (var cmd = cnn.CreateCommand())
{
var pk = key ?? row.GetDataStoreKey(target);
if (pk == null)
{
throw new MySQLDataAccessException(StringConsts.KEY_UNAVAILABLE_ERROR);
}
var where = GeneratorUtils.KeyToWhere(pk, cmd.Parameters);
cmd.Transaction = trans;
if (!string.IsNullOrEmpty(where))
{
cmd.CommandText = string.Format("DELETE T1 FROM `{0}` T1 WHERE {1}", tableName, where);
}
else
{
cmd.CommandText = string.Format("DELETE T1 FROM `{0}` T1", tableName);
}
ConvertParameters(store, cmd.Parameters);
try
{
var affected = cmd.ExecuteNonQuery();
GeneratorUtils.LogCommand(store.LogLevel, "delete-ok", cmd, null);
return(affected);
}
catch (Exception error)
{
GeneratorUtils.LogCommand(store.LogLevel, "delete-error", cmd, error);
throw;
}
}//using command
}
public Schema GetSchema(ICRUDQueryExecutionContext context, Query query)
{
var ctx = (MySQLCRUDQueryExecutionContext)context;
var target = ctx.DataStore.TargetName;
using (var cmd = ctx.Connection.CreateCommand())
{
cmd.CommandText = m_Source.StatementSource;
PopulateParameters(cmd, query);
cmd.Transaction = ctx.Transaction;
MySqlDataReader reader = null;
try
{
reader = cmd.ExecuteReader(CommandBehavior.SchemaOnly);
GeneratorUtils.LogCommand(ctx.DataStore.LogLevel, "queryhandler-ok", cmd, null);
}
catch (Exception error)
{
GeneratorUtils.LogCommand(ctx.DataStore.LogLevel, "queryhandler-error", cmd, error);
throw;
}
using (reader)
{
Schema.FieldDef[] toLoad;
return(GetSchemaForQuery(target, query, reader, m_Source, out toLoad));
} //using reader
} //using command
}
private static int crudUpsert(MySQLDataStoreBase store, MySqlConnection cnn, MySqlTransaction trans, Row row, FieldFilterFunc filter)
{
var target = store.TargetName;
var cnames = new StringBuilder();
var values = new StringBuilder();
var upserts = new StringBuilder();
var vparams = new List <MySqlParameter>();
var vpidx = 0;
foreach (var fld in row.Schema.FieldDefs)
{
var fattr = fld[target];
if (fattr == null)
{
continue;
}
if (fattr.StoreFlag != StoreFlag.LoadAndStore && fattr.StoreFlag != StoreFlag.OnlyStore)
{
continue;
}
if (filter != null)//20160210 Dkh+SPol
{
if (!filter(row, null, fld))
{
continue;
}
}
var fname = fld.GetBackendNameForTarget(target);
var fvalue = getFieldValue(row, fld.Order, store);
cnames.AppendFormat(" `{0}`,", fname);
if (fvalue != null)
{
var pname = string.Format("?VAL{0}", vpidx);
values.AppendFormat(" {0},", pname);
if (!fattr.Key)
{
upserts.AppendFormat(" `{0}` = {1},", fname, pname);
}
var par = new MySqlParameter();
par.ParameterName = pname;
par.Value = fvalue;
vparams.Add(par);
vpidx++;
}
else
{
values.Append(" NULL,");
upserts.AppendFormat(" `{0}` = NULL,", fname);
}
}//foreach
if (cnames.Length > 0 && upserts.Length > 0)
{
cnames.Remove(cnames.Length - 1, 1); // remove ","
upserts.Remove(upserts.Length - 1, 1); // remove ","
values.Remove(values.Length - 1, 1); // remove ","
}
else
{
return(0);//nothing to do
}
string tableName = getTableName(row.Schema, target);
using (var cmd = cnn.CreateCommand())
{
var sql =
@"INSERT INTO `{0}` ({1}) VALUES ({2}) ON DUPLICATE KEY UPDATE {3}".Args(tableName, cnames, values, upserts);
cmd.Transaction = trans;
cmd.CommandText = sql;
cmd.Parameters.AddRange(vparams.ToArray());
ConvertParameters(store, cmd.Parameters);
try
{
var affected = cmd.ExecuteNonQuery();
GeneratorUtils.LogCommand(store.LogLevel, "upsert-ok", cmd, null);
return(affected);
}
catch (Exception error)
{
GeneratorUtils.LogCommand(store.LogLevel, "upsert-error", cmd, error);
throw;
}
}//using command
}
private static int crudUpdate(MySQLDataStoreBase store, MySqlConnection cnn, MySqlTransaction trans, Row row, IDataStoreKey key, FieldFilterFunc filter)
{
var target = store.TargetName;
var values = new StringBuilder();
var vparams = new List <MySqlParameter>();
var vpidx = 0;
foreach (var fld in row.Schema.FieldDefs)
{
var fattr = fld[target];
if (fattr == null)
{
continue;
}
var fname = fld.GetBackendNameForTarget(target);
//20141008 DKh Skip update of key fields
//20160124 DKh add update of keys if IDataStoreKey is present
if (fattr.Key && !GeneratorUtils.HasFieldInNamedKey(fname, key))
{
continue;
}
if (fattr.StoreFlag != StoreFlag.LoadAndStore && fattr.StoreFlag != StoreFlag.OnlyStore)
{
continue;
}
if (filter != null)//20160210 Dkh+SPol
{
if (!filter(row, key, fld))
{
continue;
}
}
var fvalue = getFieldValue(row, fld.Order, store);
if (fvalue != null)
{
var pname = string.Format("?VAL{0}", vpidx);
values.AppendFormat(" `{0}` = {1},", fname, pname);
var par = new MySqlParameter();
par.ParameterName = pname;
par.Value = fvalue;
vparams.Add(par);
vpidx++;
}
else
{
values.AppendFormat(" `{0}` = NULL,", fname);
}
}//foreach
if (values.Length > 0)
{
values.Remove(values.Length - 1, 1);// remove ","
}
else
{
return(0);//nothing to do
}
string tableName = getTableName(row.Schema, target);
using (var cmd = cnn.CreateCommand())
{
var sql = string.Empty;
var pk = key ?? row.GetDataStoreKey(target);
if (pk == null)
{
throw new MySQLDataAccessException(StringConsts.KEY_UNAVAILABLE_ERROR);
}
var where = GeneratorUtils.KeyToWhere(pk, cmd.Parameters);
if (!string.IsNullOrEmpty(where))
{
sql = "UPDATE `{0}` T1 SET {1} WHERE {2}".Args(tableName, values, where);
}
else
{
throw new MySQLDataAccessException(StringConsts.BROAD_UPDATE_ERROR);//20141008 DKh BROAD update
}
cmd.Transaction = trans;
cmd.CommandText = sql;
cmd.Parameters.AddRange(vparams.ToArray());
ConvertParameters(store, cmd.Parameters);
try
{
var affected = cmd.ExecuteNonQuery();
GeneratorUtils.LogCommand(store.LogLevel, "update-ok", cmd, null);
return(affected);
}
catch (Exception error)
{
GeneratorUtils.LogCommand(store.LogLevel, "update-error", cmd, error);
throw;
}
}//using command
}
public RowsetBase Execute(ICRUDQueryExecutionContext context, Query query, bool oneRow = false)
{
var ctx = (MySQLCRUDQueryExecutionContext)context;
var target = ctx.DataStore.TargetName;
Rowset result = null;
using (var cmd = ctx.Connection.CreateCommand())
{
cmd.CommandText = m_Source.StatementSource;
PopulateParameters(cmd, query);
cmd.Transaction = ctx.Transaction;
MySqlDataReader reader = null;
try
{
reader = oneRow ? cmd.ExecuteReader(CommandBehavior.SingleRow) : cmd.ExecuteReader();
GeneratorUtils.LogCommand(ctx.DataStore.LogLevel, "queryhandler-ok", cmd, null);
}
catch (Exception error)
{
GeneratorUtils.LogCommand(ctx.DataStore.LogLevel, "queryhandler-error", cmd, error);
throw;
}
using (reader)
{
Schema.FieldDef[] toLoad;
Schema schema = getSchema(target, query, reader, out toLoad);
result = new Rowset(schema);
while (reader.Read())
{
var row = Row.MakeRow(schema, query.ResultRowType);
for (int i = 0; i < reader.FieldCount; i++)
{
var fdef = toLoad[i];
if (fdef == null)
{
continue;
}
var val = reader.GetValue(i);
if (fdef.NonNullableType == typeof(bool))
{
row[fdef.Order] = val.AsNullableBool();
}
else
{
row[fdef.Order] = val;
}
}
result.Add(row);
if (oneRow)
{
break;
}
}
} //using reader
} //using command
return(result);
}
/// <summary>
/// Auto generates select sql and params. If sqlStatement!=null then params are added to that statement
/// </summary>
public static void Load(MySQLDataStoreBase store, MySqlConnection cnn, string sqlStatement, ModelBase instance, IDataStoreKey key, object[] extra)
{
var autoSql = string.IsNullOrEmpty(sqlStatement);
var record = GeneratorUtils.AsSuitableRecordInstance(instance, autoSql);
var select = new StringBuilder();
if (autoSql)
{
foreach (var fld in record.Fields)
{
if (fld.StoreFlag == StoreFlag.LoadAndStore || fld.StoreFlag == StoreFlag.OnlyLoad)
{
select.AppendFormat(" T1.`{0}`,", fld.FieldName);
}
}
if (select.Length > 0)
{
select.Remove(select.Length - 1, 1);// remove ","
}
else
{
throw new MySQLDataAccessException(StringConsts.LOAD_NO_SELECT_COLUMNS_ERROR);
}
}
var pk = key ?? record.DataStoreKey;
if (pk == null)
{
throw new MySQLDataAccessException(StringConsts.KEY_UNAVAILABLE_ERROR);
}
using (var cmd = cnn.CreateCommand())
{
var where = GeneratorUtils.KeyToWhere(pk, cmd.Parameters);
if (autoSql)
{
cmd.CommandText = string.Format("SELECT {0} FROM `{1}` T1 WHERE {2}", select, record.TableName, where);
}
else
{
cmd.CommandText = string.Format(sqlStatement, where);
}
MySqlDataReader reader = null;
try
{
reader = cmd.ExecuteReader();
GeneratorUtils.LogCommand(store.LogLevel, "rmload-ok", cmd, null);
}
catch (Exception error)
{
GeneratorUtils.LogCommand(store.LogLevel, "rmload-error", cmd, error);
throw;
}
using (reader)
{
if (reader.Read())
{
reader.CopyFieldsToRecordFields(record);
}
else
{
throw new MySQLDataAccessException(string.Format(StringConsts.LOADING_ENTITY_NOT_FOUND_ERROR, pk));
}
} //using reader
} //using command
}
public static void Save(MySQLDataStoreBase store, MySqlConnection cnn, ModelBase instance, IDataStoreKey key, object[] extra)
{
var record = GeneratorUtils.AsSuitableRecordInstance(instance, true);
if (record.LastPostedChange != ChangeType.Created && record.LastPostedChange != ChangeType.Edited)
{
throw new MySQLDataAccessException(string.Format(StringConsts.MODEL_INVALID_STATE_ERROR, "Created || Edited", instance.LastPostedChange));
}
bool insert = instance.LastPostedChange == ChangeType.Created;
var cnames = new StringBuilder();
var values = new StringBuilder();
var vparams = new List <MySqlParameter>();
var vpidx = 0;
foreach (var fld in record.Fields)
{
if (fld.StoreFlag == StoreFlag.LoadAndStore || fld.StoreFlag == StoreFlag.OnlyStore)
{
if (
insert || fld.Modified
)
{
cnames.AppendFormat(" `{0}`,", fld.FieldName);
if (fld.HasValue)
{
var pname = string.Format("?VAL{0}", vpidx);
if (insert)
{
values.AppendFormat(" {0},", pname);
}
else
{
values.AppendFormat(" `{0}` = {1},", fld.FieldName, pname);
}
var par = new MySqlParameter();
par.ParameterName = pname;
par.Value = fld.ValueAsObject;
vparams.Add(par);
vpidx++;
}
else
{
if (insert)
{
values.Append(" NULL,");
}
else
{
values.AppendFormat(" `{0}` = NULL,", fld.FieldName);
}
}
}
}
}
if (cnames.Length > 0)
{
cnames.Remove(cnames.Length - 1, 1);// remove ","
if (values.Length > 0)
{
values.Remove(values.Length - 1, 1); // remove ","
}
}
else
{
return;//nothing has been modified
}
using (var cmd = cnn.CreateCommand())
{
var sql = string.Empty;
if (insert) //INSERT
{
sql =
string.Format("INSERT INTO `{0}` ({1}) VALUES ({2})", record.TableName, cnames, values);
}
else //UPDATE
{
var pk = key ?? record.DataStoreKey;
if (pk == null)
{
throw new MySQLDataAccessException(StringConsts.KEY_UNAVAILABLE_ERROR);
}
var where = GeneratorUtils.KeyToWhere(pk, cmd.Parameters);
if (!string.IsNullOrEmpty(where))
{
sql = string.Format("UPDATE `{0}` T1 SET {1} WHERE {2}", record.TableName, values, where);
}
else
{
sql = string.Format("UPDATE `{0}` T1 SET {1}", record.TableName, values);
}
}
cmd.CommandText = sql;
cmd.Parameters.AddRange(vparams.ToArray());
var affected = 0;
try
{
affected = cmd.ExecuteNonQuery();
GeneratorUtils.LogCommand(store.LogLevel, "rmsave-ok", cmd, null);
}
catch (Exception error)
{
GeneratorUtils.LogCommand(store.LogLevel, "rmsave-error", cmd, error);
throw;
}
if (affected == 0)
{
throw new MySQLDataAccessException(string.Format(StringConsts.NO_ROWS_AFFECTED_ERROR, instance.LastPostedChange == ChangeType.Created ? "Insert" : "Update"));
}
}//using command
}
