public async Task <IList <Dictionary <string, object> > > ExecuteReaderAsync(ParameterizedSql sql)
{
using (var command = this.GenerateCommand(sql))
using (var reader = await command.ExecuteReaderAsync())
{
if (reader.HasRows == false)
{
return(default(IList <Dictionary <string, object> >));
}
var results = new List <Dictionary <string, object> >();
var columns = reader.GetColumns().ToList();
while (await reader.ReadAsync())
{
var item = new Dictionary <string, object>();
foreach (var(ordinal, columnName) in columns)
{
item.Add(columnName, reader.GetValue(ordinal));
}
results.Add(item);
}
return(results);
}
}
public Task <int> ExecuteNonQueryAsync(ParameterizedSql sql)
{
using (var command = this.GenerateCommand(sql))
{
return(command.ExecuteNonQueryAsync());
}
}
protected DbCommand GenerateCommand(ParameterizedSql parameterizedSql)
{
Guard.NotNull(parameterizedSql, nameof(parameterizedSql));
var command = this.Connection.CreateCommand();
command.CommandText = parameterizedSql;
if (this.Transaction != null)
{
command.Transaction = this.Transaction;
}
foreach (var currentParameter in parameterizedSql.Parameters.EmptyIfNull())
{
var parameter = command.CreateParameter();
parameter.ParameterName = currentParameter.ParameterName;
parameter.Value = currentParameter.Value;
command.Parameters.Add(parameter);
}
return(command);
}
public int ExecuteNonQuery(ParameterizedSql sql)
{
using (var command = this.GenerateCommand(sql))
{
return(command.ExecuteNonQuery());
}
}
public void CreateFromDbSet()
{
var psql = new ParameterizedSql <DefaultAccessEntity>(_db.DefaultAccessEntities, _logger);
Assert.True(psql.IsSelect);
Assert.False(psql.ReadOnly);
Assert.Empty(psql.ParameterValues);
}
public void CreateWithParam()
{
var param = "John Doe";
var psql = new ParameterizedSql <DefaultAccessEntity>(_db.DefaultAccessEntities.Where(x => x.Name == param), _logger);
Assert.True(psql.IsSelect);
Assert.False(psql.ReadOnly);
Assert.NotEmpty(psql.ParameterValues);
}
public IEnumerable <T> LoadObjects <T>(string sql, IDictionary <string, object> parameters) where T : new()
{
var command = new ParameterizedSql
{
Command = sql,
Parameters = parameters
};
return(Execute <T>(command, reader => this.Instantiate <T>(reader)));
}
public void ConvertToDelete()
{
var param = "John Doe";
var psql = new ParameterizedSql <DefaultAccessEntity>(_db.DefaultAccessEntities.Where(x => x.Name == param), _logger);
var del = psql.ToDelete();
Assert.True(psql.IsSelect);
Assert.False(del.IsSelect);
Assert.True(del.IsDelete);
}
public void ConvertToUpdate()
{
var p1 = "John Doe";
var p2 = "Jane Smith";
var psql = new ParameterizedSql <DefaultAccessEntity>(_db.DefaultAccessEntities.Where(x => x.Name == p1), _logger);
var upd = psql.ToUpdate(x => new DefaultAccessEntity()
{
Name = p2
});
Assert.True(psql.IsSelect);
Assert.False(upd.IsSelect);
Assert.True(upd.IsUpdate);
}
protected virtual DbCommand GenerateIDBCommand(ParameterizedSql sql)
{
var command = this.GenerateIDBCommand(sql.Sql);
foreach (var currentParameter in sql.Parameters)
{
var parameter = command.CreateParameter();
parameter.ParameterName = currentParameter.ParameterName;
parameter.Value = currentParameter.Value;
command.Parameters.Add(parameter);
}
return(command);
}
public ParameterizedSql BuildSqlQuery <T>(Expression <Func <T, bool> > expression)
{
var sqlScriptParts = new List <SqlScriptPart>();
this.ProcessExpression(expression.Body, sqlScriptParts);
var result = new ParameterizedSql
{
Sql = this.ToRawSql(sqlScriptParts),
Parameters = new List <SqlParameter>(sqlScriptParts
.OfType <SqlScriptParameter>()
.Select(f => new SqlParameter
{
Value = f.ParameterValue,
ParameterName = f.ParameterName
}))
};
return(result);
}
public virtual async Task <IList <T> > QueryAsync <T>(ParameterizedSql sql) where T : new()
{
var mapping = this.EntityMappingProvider.GetMapping <T>();
using (var command = this.GenerateIDBCommand(sql))
using (var reader = await command.ExecuteReaderAsync())
{
if (reader.HasRows == false)
{
return(default(IList <T>));
}
var results = new List <T>();
while (await reader.ReadAsync())
{
results.Add(reader.LoadWithEntityMapping(mapping));
}
return(results);
}
}
private IEnumerable <T> Execute <T>(ParameterizedSql command, Func <IDataReader, T> instantiate)
{
using (var connection = new SqlConnection(connectionString))
{
connection.Open();
using (var cmd = connection.CreateCommand())
{
cmd.CommandText = command.Command;
foreach (var pair in command.Parameters)
{
cmd.Parameters.AddWithValue(pair.Key, pair.Value);
}
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
yield return(instantiate(reader));
}
}
}
}
}
public IList <T> ExecuteReader <T>(ParameterizedSql sql) where T : new()
{
var mapping = this.EntityMappingProvider.GetMapping <T>();
using (var command = this.GenerateCommand(sql))
using (var reader = command.ExecuteReader())
{
if (reader.HasRows == false)
{
return(default(IList <T>));
}
var results = new List <T>();
while (reader.Read())
{
results.Add(reader.LoadWithEntityMapping(mapping));
}
return(results);
}
}
public virtual IList <T> Query <T>(ParameterizedSql parameterizedSql) where T : new()
{
Guard.NotNull(parameterizedSql, nameof(parameterizedSql));
var mapping = this.EntityMappingProvider.GetMapping <T>();
using (var command = this.GenerateIDBCommand(parameterizedSql))
using (var reader = command.ExecuteReader())
{
if (reader.HasRows == false)
{
return(default(IList <T>));
}
var results = new List <T>();
while (reader.Read())
{
results.Add(reader.LoadWithEntityMapping(mapping));
}
return(results);
}
}
public static string GetWithReplacedParameters(this ParameterizedSql self)
{
return(self.Parameters.Aggregate(self.Sql,
(current, currentParameter) => current.Replace(currentParameter.ParameterName,
currentParameter.Value is string?$"\"{currentParameter.Value}\"" : currentParameter.Value.ToString())));
}
