/// <summary>
/// Builds the WHERE clause from the given expression object.
/// </summary>
/// <param name="expression">The expression object.</param>
/// <returns>The <c>Apworks.Storage.Builders.WhereClauseBuildResult</c> instance
/// which contains the build result.</returns>
public WhereClauseBuildResult BuildWhereClause(Expression <Func <TTableObject, bool> > expression)
{
this.sb.Clear();
this.parameterValues.Clear();
this.Visit(expression.Body);
WhereClauseBuildResult result = new WhereClauseBuildResult
{
ParameterValues = parameterValues,
WhereClause = sb.ToString()
};
return(result);
}
public async Task <IEnumerable <TTableObject> > SelectAsync <TTableObject>(Specification <TTableObject> specification) where TTableObject : class, new()
{
try
{
using (var connection = this.CreateDatabaseConnection())
{
await connection.OpenAsync();
var sql = $"SELECT {this.GetAllFieldNames<TTableObject>()} FROM {this.GetTableName<TTableObject>()}";
WhereClauseBuildResult whereClauseBuildResult = null;
if (specification != null)
{
var whereClauseBuilder = this.CreateWhereClauseBuilder <TTableObject>();
whereClauseBuildResult = whereClauseBuilder.BuildWhereClause(specification);
sql += $" WHERE {whereClauseBuildResult.WhereClause}";
}
using (var command = this.CreateCommand(sql, connection))
{
if (whereClauseBuildResult != null)
{
command.Parameters.Clear();
var parameters = this.GetSelectCriteriaDbParameterList <TTableObject>(whereClauseBuildResult.ParameterValues);
foreach (var param in parameters)
{
command.Parameters.Add(param);
}
}
var reader = await command.ExecuteReaderAsync();
List <TTableObject> result = new List <TTableObject>();
while (await reader.ReadAsync())
{
result.Add(this.CreateFromReader <TTableObject>(reader));
}
reader.Close();
return(result);
}
}
}
catch (Exception ex)
{
log.Error("Failed to select the data.", ex);
throw;
}
}
private async Task DoUpdateAsync <TTableObject>(DbConnection connection, DbTransaction transaction, UpdateCriteria <TTableObject> updateCriteria, Specification <TTableObject> specification) where TTableObject : class, new()
{
try
{
var whereClauseBuilder = this.CreateWhereClauseBuilder <TTableObject>();
var sql = $"UPDATE {GetTableName<TTableObject>()} SET {GetUpdateCriteriaParameterNameList<TTableObject>(whereClauseBuilder, updateCriteria)}";
WhereClauseBuildResult whereClauseBuildResult = null;
if (specification != null)
{
whereClauseBuildResult = whereClauseBuilder.BuildWhereClause(specification);
sql = $"{sql} WHERE {whereClauseBuildResult.WhereClause}";
}
using (var command = this.CreateCommand(sql, connection))
{
if (transaction != null)
{
command.Transaction = transaction;
}
command.Parameters.Clear();
var updateParameters = GetUpdateCriteriaParameterList <TTableObject>(updateCriteria);
foreach (var parameter in updateParameters)
{
command.Parameters.Add(parameter);
}
if (whereClauseBuildResult != null)
{
foreach (var kvp in whereClauseBuildResult.ParameterValues)
{
var parameter = this.CreateParameter();
parameter.ParameterName = kvp.Key;
parameter.Value = kvp.Value;
command.Parameters.Add(parameter);
}
}
await command.ExecuteNonQueryAsync();
}
}
catch (Exception ex)
{
log.Error("Failed to update.", ex);
}
}