/// <summary>
/// Populates SqlCommand 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(SqlCommand 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(Store, cmd.Parameters);
}
private static async Task <int> crudDelete(MsSqlDataStoreBase store, SqlConnection cnn, SqlTransaction trans, Doc doc, IDataStoreKey key)
{
var target = store.TargetName;
string tableName = store.AdjustObjectNameCasing(getTableName(doc.Schema, target));
using (var cmd = cnn.CreateCommand())
{
var pk = key ?? doc.GetDataStoreKey(target);
if (pk == null)
{
throw new MsSqlDataAccessException(StringConsts.KEY_UNAVAILABLE_ERROR);
}
var where = GeneratorUtils.KeyToWhere(pk, cmd.Parameters);
cmd.Transaction = trans;
if (!string.IsNullOrEmpty(where))
{
cmd.CommandText = string.Format("DELETE FROM [{0}] T1 WHERE {1}", tableName, where);
}
else
{
cmd.CommandText = string.Format("DELETE FROM [{0}] T1", tableName);
}
ConvertParameters(store, cmd.Parameters);
//dbg(cmd);
try
{
var affected = await cmd.ExecuteNonQueryAsync();
GeneratorUtils.LogCommand(store, "delete-ok", cmd, null);
return(affected);
}
catch (Exception error)
{
GeneratorUtils.LogCommand(store, "delete-error", cmd, error);
throw;
}
}//using command
}
private static async Task <int> crudUpdate(MsSqlDataStoreBase store, SqlConnection cnn, SqlTransaction trans, Doc doc, IDataStoreKey key, FieldFilterFunc filter)
{
var target = store.TargetName;
var values = new StringBuilder();
var vparams = new List <SqlParameter>();
var vpidx = 0;
foreach (var fld in doc.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;
}
fname = store.AdjustObjectNameCasing(fname);
if (fattr.StoreFlag != StoreFlag.LoadAndStore && fattr.StoreFlag != StoreFlag.OnlyStore)
{
continue;
}
if (filter != null)
{
if (!filter(doc, key, fld))
{
continue;
}
}
var converted = getDbFieldValue(doc, fld, fattr, store);
if (converted.value != null)
{
var pname = string.Format("@VAL{0}", vpidx);
values.AppendFormat(" [{0}] = {1},", fname, pname);
var par = new SqlParameter();
par.ParameterName = pname;
par.Value = converted.value;
if (converted.dbType.HasValue)
{
par.SqlDbType = converted.dbType.Value;
}
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 = store.AdjustObjectNameCasing(getTableName(doc.Schema, target));
using (var cmd = cnn.CreateCommand())
{
var sql = string.Empty;
var pk = key ?? doc.GetDataStoreKey(target);
if (pk == null)
{
throw new MsSqlDataAccessException(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 MsSqlDataAccessException(StringConsts.BROAD_UPDATE_ERROR);//20141008 DKh BROAD update
}
cmd.Transaction = trans;
cmd.CommandText = sql;
cmd.Parameters.AddRange(vparams.ToArray());
// ConvertParameters(store, cmd.Parameters);
//dbg(cmd);
try
{
var affected = await cmd.ExecuteNonQueryAsync();
GeneratorUtils.LogCommand(store, "update-ok", cmd, null);
return(affected);
}
catch (Exception error)
{
GeneratorUtils.LogCommand(store, "update-error", cmd, error);
throw;
}
}//using command
}