public WhereClauseResult Build()
{
try
{
if (_tuples.Count == 0)
{
return(WhereClauseResult.Null);
}
var writer = new StringTextWriter();
foreach (var tuple in _tuples)
{
writer.Write(writer.Length > 0 ? $" {tuple.Key} {tuple.Value}" : tuple.Value);
}
var statement = writer.ToStringWithClear();
var dynParms = _dynParms;
return(new WhereClauseResult(statement, dynParms));
}
finally
{
Clear();
}
}
public OrderbyClauseResult Build()
{
try
{
var writer = new StringTextWriter();
// ReSharper disable once InvertIf
if (_tuples != null && _tuples.Any())
{
foreach (var tuple in _tuples)
{
if (writer.Length > 0)
{
writer.Write(", ");
}
writer.Write($"{tuple.Key} {tuple.Value}");
}
var orderbyClause = writer.ToStringWithClear();
return((OrderbyClauseResult)orderbyClause);
}
// (null)
return(OrderbyClauseResult.Null);
}
finally
{
Clear();
}
}
public CommandTextEventInfo ConstructSingleSql <TKey, TEntity>(IList <Expression <Func <TEntity, object> > > logicFields, IEnumerable <Expression <Func <TEntity, object> > > selectFields, Expression <Func <TEntity, object> > id, TKey value) where TKey : IEquatable <TKey> where TEntity : class, IEntity <TKey>
{
if (id == null)
{
throw new ArgumentNullException(nameof(id));
}
var writer = new StringTextWriter();
var dynParms = new DynamicParameters();
var entityType = typeof(TEntity);
var tableName = _dialectSettings.GetEscapeName(entityType);
var tp = entityType.GetRuntimeProperties().Where(p => p.PropertyType.IsSimpleType());
if (logicFields != null)
{
tp = tp.Where(p => !logicFields.Any(exp => string.Equals(p.Name, exp.GetMemberName(), StringComparison.Ordinal)));
}
if (selectFields != null)
{
tp = tp.Where(p => selectFields.Any(exp => string.Equals(p.Name, exp.GetMemberName(), StringComparison.Ordinal)));
}
var idParamName = _dialectSettings.GetParameterName(id.GetMemberName());
dynParms.Add(idParamName, value);
writer.Write($"SELECT TOP(1) {string.Join(", ", tp.Select(p => _dialectSettings.GetEscapeName(p)))} FROM {tableName} ");
writer.Write($" WHERE ({_dialectSettings.GetEscapeName(id)} = {idParamName}) ");
writer.Write(";");
return(new CommandTextEventInfo(writer.ToStringWithClear(), dynParms));
}
public CommandTextEventInfo ConstructTakeSql <TKey, TEntity>(IList <Expression <Func <TEntity, object> > > logicFields, int take, IEnumerable <Expression <Func <TEntity, object> > > selectFields, WhereClauseResult whereClause, OrderbyClauseResult orderbyClause) where TKey : IEquatable <TKey> where TEntity : class, IEntity <TKey>
{
var writer = new StringTextWriter();
var dynParms = new DynamicParameters();
var entityType = typeof(TEntity);
var tableName = _dialectSettings.GetEscapeName(entityType);
var tp = entityType.GetRuntimeProperties().Where(p => p.PropertyType.IsSimpleType());
if (logicFields != null)
{
tp = tp.Where(p => !logicFields.Any(exp => string.Equals(p.Name, exp.GetMemberName(), StringComparison.Ordinal)));
}
if (selectFields != null)
{
tp = tp.Where(p => selectFields.Any(exp => string.Equals(p.Name, exp.GetMemberName(), StringComparison.Ordinal)));
}
writer.Write($"SELECT TOP({take}) {string.Join(", ", tp.Select(p => _dialectSettings.GetEscapeName(p)))} FROM {tableName} ");
if (whereClause != null)
{
writer.Write($"WHERE ({whereClause}) ");
dynParms.AdddWhereClause(whereClause);
}
if (!orderbyClause.IsNull)
{
writer.Write($"ORDER BY {orderbyClause} ");
}
writer.Write(";");
return(new CommandTextEventInfo(writer.ToStringWithClear(), dynParms));
}
public async Task <TResult> ExecuteAsync <TResult>(Func <IStorageDbConnection, object[], IDbTransaction, CancellationToken, Task <TResult> > command, object[] args, IsolationLevel il, CancellationToken cancellationToken)
{
if (command == null)
{
throw new ArgumentNullException(nameof(command));
}
if (args == null)
{
throw new ArgumentNullException(nameof(args));
}
var writer = new StringTextWriter();
writer.WriteAa();
var conn = new StorageDbConnection(await CreateDbConnectionWithOpenAsync(cancellationToken), writer);
writer.WriteBb(Identity);
var tr = conn.BeginTransaction(il);
writer.WriteFf();
try
{
var result = await command(conn, args, tr, cancellationToken);
tr.Commit();
writer.WriteGg();
writer.WriteCc();
return(result);
}
catch (Exception e)
{
writer.WriteError(e.Message);
tr.Rollback();
writer.WriteHh();
throw new InvalidOperationException("Command failed to execute!", e);
}
finally
{
conn.Close();
conn.Dispose();
tr.Dispose();
writer.WriteDd(Identity);
writer.WriteEe();
var message = writer.ToStringWithClear();
Logger.Write(message);
}
}
public CommandTextEventInfo ConstructCountSql <TKey, TEntity>(Expression <Func <TEntity, object> > member, WhereClauseResult whereClause) where TKey : IEquatable <TKey> where TEntity : class, IEntity <TKey>
{
var writer = new StringTextWriter();
var dynParms = new DynamicParameters();
var entityType = typeof(TEntity);
var tableName = _dialectSettings.GetEscapeName(entityType);
writer.Write($"SELECT COUNT({(member == null ? "1" : _dialectSettings.GetEscapeName(member))}) AS _TotalNumberOfRecords FROM {tableName} ");
if (whereClause != null)
{
writer.Write($"WHERE ({whereClause}) ");
dynParms.AdddWhereClause(whereClause);
}
writer.Write(";");
return(new CommandTextEventInfo(writer.ToStringWithClear(), dynParms));
}
public CommandTextEventInfo ConstructUpdateSql <TKey, TEntity>(IList <Expression <Func <TEntity, object> > > logicFields, TEntity entityToUpdate, IEnumerable <Expression <Func <TEntity, object> > > updateFields, Expression <Func <TEntity, object> > id, TKey value) where TKey : IEquatable <TKey> where TEntity : class, IEntity <TKey>
{
if (entityToUpdate == null)
{
throw new ArgumentNullException(nameof(entityToUpdate));
}
if (id == null)
{
throw new ArgumentNullException(nameof(id));
}
var writer = new StringTextWriter();
var dynParms = new DynamicParameters();
var entityType = typeof(TEntity);
var tableName = _dialectSettings.GetEscapeName(entityType);
var tp = entityType.GetRuntimeProperties().Where(p => p.PropertyType.IsSimpleType());
if (logicFields != null)
{
tp = tp.Where(p => !logicFields.Any(exp => string.Equals(p.Name, exp.GetMemberName(), StringComparison.Ordinal)));
}
if (updateFields != null)
{
tp = tp.Where(p => updateFields.Any(exp => string.Equals(p.Name, exp.GetMemberName(), StringComparison.Ordinal)));
}
var properties = tp.ToList();
var kvps = new List <KeyValuePair <string, string> >(properties.Count);
foreach (var property in properties)
{
var paramName = _dialectSettings.GetParameterName(property);
dynParms.Add(paramName, property.GetValue(entityToUpdate));
kvps.Add(new KeyValuePair <string, string>(_dialectSettings.GetEscapeName(property), paramName));
}
var idParamName = _dialectSettings.GetParameterName(id.GetMemberName());
dynParms.Add(idParamName, value);
writer.Write($"UPDATE {tableName} SET {string.Join(", ", kvps.Select(kvp => $"{kvp.Key} = {kvp.Value}"))} WHERE ({_dialectSettings.GetEscapeName(id.GetMemberName())} = {idParamName}) ;");
return(new CommandTextEventInfo(writer.ToStringWithClear(), dynParms));
}
public CommandTextEventInfo ConstructDeleteSql <TKey, TEntity>(Expression <Func <TEntity, object> > id, TKey value) where TKey : IEquatable <TKey> where TEntity : class, IEntity <TKey>
{
if (id == null)
{
throw new ArgumentNullException(nameof(id));
}
var writer = new StringTextWriter();
var dynParms = new DynamicParameters();
var entityType = typeof(TEntity);
var tableName = _dialectSettings.GetEscapeName(entityType);
var idParamName = _dialectSettings.GetParameterName(id.GetMemberName());
dynParms.Add(idParamName, value);
writer.Write($"DELETE FROM {tableName} WHERE ({_dialectSettings.GetEscapeName(id)} = {idParamName}) ;");
return(new CommandTextEventInfo(writer.ToStringWithClear(), dynParms));
}
public CommandTextEventInfo ConstructInsertSql <TKey, TEntity>(IList <Expression <Func <TEntity, object> > > logicFields, TEntity entityToInsert, IEnumerable <Expression <Func <TEntity, object> > > ignoredFields, bool autoIncrement) where TKey : IEquatable <TKey> where TEntity : class, IEntity <TKey>
{
if (entityToInsert == null)
{
throw new ArgumentNullException(nameof(entityToInsert));
}
var writer = new StringTextWriter();
var dynParms = new DynamicParameters();
var entityType = typeof(TEntity);
var tableName = _dialectSettings.GetEscapeName(entityType);
var tp = entityType.GetRuntimeProperties().Where(p => p.PropertyType.IsSimpleType());
if (logicFields != null)
{
tp = tp.Where(p => !logicFields.Any(exp => string.Equals(p.Name, exp.GetMemberName(), StringComparison.Ordinal)));
}
if (ignoredFields != null)
{
tp = tp.Where(p => !ignoredFields.Any(exp => string.Equals(p.Name, exp.GetMemberName(), StringComparison.Ordinal)));
}
var properties = tp.ToList();
var kvps = new List <KeyValuePair <string, string> >(properties.Count);
foreach (var property in properties)
{
var paramName = _dialectSettings.GetParameterName(property);
dynParms.Add(paramName, property.GetValue(entityToInsert));
kvps.Add(new KeyValuePair <string, string>(_dialectSettings.GetEscapeName(property), paramName));
}
writer.Write($"INSERT INTO {tableName} ({string.Join(", ", kvps.Select(kvp => kvp.Key))}) VALUES ({string.Join(", ", kvps.Select(kvp => kvp.Value))}) ;");
// ReSharper disable once InvertIf
if (autoIncrement)
{
writer.Write(_dialectSettings.GetIdentitySql);
writer.Write(" ;");
}
return(new CommandTextEventInfo(writer.ToStringWithClear(), dynParms));
}
public CommandTextEventInfo ConstructExistsSql(Type entityType, WhereClauseResult whereClause)
{
var writer = new StringTextWriter();
var dynParms = new DynamicParameters();
var tableName = _dialectSettings.GetEscapeName(entityType);
writer.Write("SELECT ISNULL(");
writer.Write("(");
writer.Write($"SELECT TOP(1) 1 FROM {tableName}");
if (whereClause != null)
{
writer.Write($"WHERE ({whereClause}) ");
dynParms.AdddWhereClause(whereClause);
}
writer.Write("),0");
writer.Write(") AS _ExistsFlag ;");
return(new CommandTextEventInfo(writer.ToStringWithClear(), dynParms));
}
public async Task ExecuteAsync(Func <IStorageDbConnection, object[], CancellationToken, Task> command, object[] args, CancellationToken cancellationToken)
{
if (command == null)
{
throw new ArgumentNullException(nameof(command));
}
if (args == null)
{
throw new ArgumentNullException(nameof(args));
}
var writer = new StringTextWriter();
writer.WriteAa();
var conn = new StorageDbConnection(await CreateDbConnectionWithOpenAsync(cancellationToken), writer);
writer.WriteBb(Identity);
try
{
await command(conn, args, cancellationToken);
writer.WriteCc();
}
catch (Exception e)
{
writer.WriteError(e.Message);
throw new InvalidOperationException("Command failed to execute!", e);
}
finally
{
conn.Close();
conn.Dispose();
writer.WriteDd(Identity);
writer.WriteEe();
var message = writer.ToStringWithClear();
Logger.Write(message);
}
}
public CommandTextEventInfo ConstructDeleteSql(Type entityType, WhereClauseResult whereClause)
{
if (entityType == null)
{
throw new ArgumentNullException(nameof(entityType));
}
var writer = new StringTextWriter();
var dynParms = new DynamicParameters();
var tableName = _dialectSettings.GetEscapeName(entityType);
writer.Write($"DELETE FROM {tableName} ");
if (whereClause != null)
{
writer.Write($"WHERE ({whereClause}) ");
dynParms.AdddWhereClause(whereClause);
}
writer.Write(";");
return(new CommandTextEventInfo(writer.ToStringWithClear(), dynParms));
}
public CommandTextEventInfo ConstructPageSql <TKey, TEntity>(IList <Expression <Func <TEntity, object> > > logicFields, int skip, int take, IEnumerable <Expression <Func <TEntity, object> > > selectFields, WhereClauseResult whereClause, OrderbyClauseResult orderbyClause) where TKey : IEquatable <TKey> where TEntity : class, IEntity <TKey>
{
var writer = new StringTextWriter();
var dynParms = new DynamicParameters();
var entityType = typeof(TEntity);
var tableName = _dialectSettings.GetEscapeName(entityType);
var tp = entityType.GetRuntimeProperties().Where(p => p.PropertyType.IsSimpleType());
if (logicFields != null)
{
tp = tp.Where(p => !logicFields.Any(exp => string.Equals(p.Name, exp.GetMemberName(), StringComparison.Ordinal)));
}
if (selectFields != null)
{
tp = tp.Where(p => selectFields.Any(exp => string.Equals(p.Name, exp.GetMemberName(), StringComparison.Ordinal)));
}
// _TotalNumberOfRecords
writer.Write($"SELECT COUNT(1) AS _TotalNumberOfRecords FROM {tableName} ");
if (whereClause != null)
{
writer.Write($"WHERE ({whereClause})");
}
writer.Write("; ");
// PagedResult
var propeerties = tp.ToList();
writer.Write($"SELECT {string.Join(", ", propeerties.Select(p => $"PagedResult.{_dialectSettings.GetEscapeName(p)}"))} FROM (");
writer.Write($"SELECT TOP({skip + take}) ROW_NUMBER() OVER(ORDER BY {orderbyClause}) AS _PagedNumber, {string.Join(", ", propeerties.Select(p => _dialectSettings.GetEscapeName(p)))} FROM {tableName}");
if (whereClause != null)
{
writer.Write($" WHERE ({whereClause})");
dynParms.AdddWhereClause(whereClause);
}
writer.Write($") AS PagedResult WHERE (PagedResult._PagedNumber > {skip}) ");
writer.Write(";");
return(new CommandTextEventInfo(writer.ToStringWithClear(), dynParms));
}
public StorageDbConnection(IDbConnection conn, StringTextWriter textWriter)
{
_conn = conn ?? throw new ArgumentNullException(nameof(conn));
TextWriter = textWriter ?? throw new ArgumentNullException(nameof(textWriter));
}
