public async Task <IEnumerable <C> > DeleteAsync(Expression <Func <C, object> >?where = null)
{
RegisteredClass registeredClass = _dBAutomator.RegisteredClasses.First(r => r.SomeClass.GetType() == typeof(C));
List <ExpressionModel <C> > expressions = new List <ExpressionModel <C> >();
BinaryExpression?binaryExpression = GetBinaryExpression(where);
GetExpressions(binaryExpression, expressions, registeredClass);
string sql = $"DELETE FROM \"{registeredClass.TableName}\"";
if (where != null)
{
sql = $"{sql} WHERE {where.GetWhereClause(expressions)}";
}
sql = $"{sql} RETURNING *;";
_logger.LogTrace(sql);
DynamicParameters p = GetDynamicParametersFromExpression(expressions);
using NpgsqlConnection connection = new NpgsqlConnection(_queryOptions.ConnectionString);
await connection.OpenAsync();
Stopwatch stopwatch = StopWatchStart();
var result = await connection.QueryAsync <C>(sql, p, _queryOptions.DbTransaction, _queryOptions.CommandTimeOut);
StopWatchEnd(stopwatch, "GetAsync()");
return(result);
}
public async Task <IEnumerable <C> > UpdateAsync(Expression <Func <C, object> > setCollection, Expression <Func <C, object> >?whereCollection = null)
{
RegisteredClass registeredClass = _dBAutomator.RegisteredClasses.First(r => r.SomeClass.GetType() == typeof(C));
List <ExpressionModel <C> > setExpressions = new List <ExpressionModel <C> >();
BinaryExpression?setBinaryExpression = GetBinaryExpression(setCollection);
GetExpressions(setBinaryExpression, setExpressions, registeredClass, "s_");
List <ExpressionModel <C> > whereExpressions = new List <ExpressionModel <C> >();
BinaryExpression?whereBinaryExpression = GetBinaryExpression(whereCollection);
GetExpressions(whereBinaryExpression, whereExpressions, registeredClass);
string sql = $"UPDATE \"{registeredClass.TableName}\" SET {setCollection.GetWhereClause(setExpressions)}";
if (whereCollection != null)
{
sql = $"{sql} WHERE {whereCollection.GetWhereClause(whereExpressions)}";
}
sql = $"{sql} RETURNING *;";
_logger.LogTrace(sql);
DynamicParameters p = GetDynamicParametersFromExpression(setExpressions);
GetDynamicParametersFromExpression(whereExpressions, p);
using NpgsqlConnection connection = new NpgsqlConnection(_queryOptions.ConnectionString);
connection.Open();
var stopWatch = StopWatchStart();
var result = await connection.QueryAsync <C>(sql, p, _queryOptions.DbTransaction, _queryOptions.CommandTimeOut);
StopWatchEnd(stopWatch, "UpdateAsync");
connection.Close();
return(result);
}
public async Task <IEnumerable <C> > GetAsync(Expression <Func <C, object> >?where = null, OrderByClause <C>?orderBy = null)
{
RegisteredClass registeredClass = _dBAutomator.RegisteredClasses.First(r => r.SomeClass.GetType() == typeof(C));
List <ExpressionModel <C> > expressions = new List <ExpressionModel <C> >();
BinaryExpression?binaryExpression = GetBinaryExpression(where);
GetExpressions(binaryExpression, expressions, registeredClass);
string sql = $"SELECT";
foreach (var property in registeredClass.RegisteredProperties)
{
sql = $"{sql} \"{property.ColumnName}\",";
}
sql = sql[0..^ 1];
public async Task <C> InsertAsync(C item)
{
if (item == null)
{
throw new ArgumentNullException(nameof(item), "The item cannot be null");
}
RegisteredClass registeredClass = _dBAutomator.RegisteredClasses.First(r => r.SomeClass.GetType() == item.GetType());
DynamicParameters p = GetDynamicParameters(item, registeredClass.RegisteredProperties);
string sql = $"INSERT INTO \"{registeredClass.TableName}\" (";
foreach (var property in registeredClass.RegisteredProperties.Where(p => !p.IsAutoIncrement))
{
sql = $"{sql}\"{property.ColumnName}\", ";
}
sql = sql[0..^ 2];
public async Task <int> DeleteAsync(C item)
{
if (item == null)
{
throw new NullReferenceException("The item cannot be null.");
}
RegisteredClass registeredClass = _dBAutomator.RegisteredClasses.First(r => r.SomeClass.GetType() == typeof(C));
DynamicParameters p = GetDynamicParameters(item, registeredClass.RegisteredProperties);
string sql = $"DELETE FROM \"{registeredClass.TableName}\" WHERE {GetWhereClause(item, registeredClass.RegisteredProperties)};";
_logger.LogTrace(sql);
if (item is IDBObject dBObject)
{
await dBObject.OnDeleteAsync(_dBAutomator);
}
using NpgsqlConnection connection = new NpgsqlConnection(_queryOptions.ConnectionString);
await connection.OpenAsync();
Stopwatch stopwatch = StopWatchStart();
var result = await connection.ExecuteAsync(sql, p, _queryOptions.DbTransaction, _queryOptions.CommandTimeOut);
StopWatchEnd(stopwatch, "GetAsync()");
if (item is IDBObject dBObject1)
{
await dBObject1.OnDeletedAsync(_dBAutomator);
}
return(result);
}
public async Task <C> UpdateAsync(C item)
{
if (item == null)
{
throw new NullReferenceException("The item cannot be null.");
}
RegisteredClass registeredClass = _dBAutomator.RegisteredClasses.First(r => r.SomeClass.GetType() == typeof(C));
DynamicParameters p = GetDynamicParameters(item, registeredClass.RegisteredProperties);
var keys = registeredClass.RegisteredProperties.Where(p => p.IsKey);
if (keys.Count() == 0)
{
throw new Exception("The registered class does not have a primary key attribute.");
}
string sql = $"UPDATE \"{registeredClass.TableName}\" SET {GetWhereClause(item, registeredClass.RegisteredProperties.Where(p => !p.IsAutoIncrement).ToList(), ",")} WHERE";
foreach (var key in keys)
{
sql = $"{sql} \"{key.ColumnName}\" =";
if (key.PropertyType == typeof(ulong))
{
sql = $"{sql} {Convert.ToInt64(item.GetType().GetProperty(key.PropertyName).GetValue(item, null))}";
}
else
{
sql = $"{sql} {item.GetType().GetProperty(key.PropertyName).GetValue(item, null)}";
}
sql = $"{sql} AND";
}
sql = sql[0..^ 3];
public ExpressionModel(RegisteredClass registeredClass, string parameterPrefix = "w_")
{
_registeredClass = registeredClass;
ParameterPrefix = parameterPrefix;
}
