/// <summary>
/// Performs a SELECT operation on a single entity, using its keys
/// </summary>
public async Task <TEntity> SelectByIdAsync(IDbConnection connection, TEntity keyEntity,
AggregatedSqlStatementOptions <TEntity> statementOptions)
{
string statement;
string splitOnCondition;
SqlBuilder.ConstructFullJoinSelectStatement(
out statement,
out splitOnCondition,
ConstructJoinInstructions(statementOptions, _joinedEntitiesSqlBuilders),
whereClause: $"{SqlBuilder.ConstructKeysWhereClause(SqlBuilder.GetTableName())}");
var relationshipInstanceBuilder = new RelationshipEntityInstanceBuilder(_allEntityMappings);
var queriedEntityIdentities = await QueryAsync(connection,
statement,
splitOnCondition,
keyEntity,
false,
statementOptions.Transaction,
(int?)statementOptions.CommandTimeout?.TotalSeconds, relationshipInstanceBuilder);
// a problem in the Dapper library would cause this function to fail
// see https://github.com/StackExchange/dapper-dot-net/issues/596 for more info
return(FilterDuplicates(queriedEntityIdentities).SingleOrDefault());
}
public bool UpdateById(IDbConnection connection, TEntity keyEntity, AggregatedSqlStatementOptions <TEntity> statementOptions)
{
if (_sqlBuilder.RefreshOnUpdateProperties.Length > 0)
{
var updatedEntity = connection.Query <TEntity>(
_sqlBuilder.ConstructFullSingleUpdateStatement(),
keyEntity,
transaction:
statementOptions.Transaction,
commandTimeout: (int?)statementOptions.CommandTimeout?.TotalSeconds).FirstOrDefault();
if (updatedEntity != null)
{
// copy all the database generated props back onto our entity
this.CopyEntity(updatedEntity, keyEntity, _sqlBuilder.RefreshOnUpdateProperties);
}
return(updatedEntity != null);
}
return(connection.Execute(
_sqlBuilder.ConstructFullSingleUpdateStatement(),
keyEntity,
transaction:
statementOptions.Transaction,
commandTimeout: (int?)statementOptions.CommandTimeout?.TotalSeconds) > 0);
}
/// <summary>
/// Performs a common SELECT
/// </summary>
public async Task <IEnumerable <TEntity> > BatchSelectAsync(IDbConnection connection,
AggregatedSqlStatementOptions <TEntity> statementOptions)
{
//validation removed, up to the engine to fail
//Requires.Argument((statementOptions.LimitResults == null && statementOptions.SkipResults == null) || (statementOptions.OrderClause != null || statementOptions.RelationshipOptions.Values.Any(singleJoinOptions => singleJoinOptions.OrderClause != null)), nameof(statementOptions),
// "When using Top or Skip, you must provide an OrderBy clause.");
string statement;
string splitOnCondition;
SqlBuilder.ConstructFullJoinSelectStatement(
out statement,
out splitOnCondition,
ConstructJoinInstructions(statementOptions, _joinedEntitiesSqlBuilders),
whereClause: statementOptions.WhereClause,
orderClause: statementOptions.OrderClause,
skipRowsCount: statementOptions.SkipResults,
limitRowsCount: statementOptions.LimitResults);
var relationshipInstanceBuilder = new RelationshipEntityInstanceBuilder(_allEntityMappings);
var queriedEntityIdentities = await QueryAsync(connection,
statement,
splitOnCondition,
statementOptions.Parameters,
!statementOptions.ForceStreamResults,
statementOptions.Transaction,
(int?)statementOptions.CommandTimeout?.TotalSeconds,
relationshipInstanceBuilder);
// a problem in the Dapper library would cause this function to fail
// see https://github.com/StackExchange/dapper-dot-net/issues/596 for more info
return(FilterDuplicates(queriedEntityIdentities));
}
public async Task <bool> UpdateByIdAsync(IDbConnection connection, TEntity keyEntity,
AggregatedSqlStatementOptions <TEntity> statementOptions)
{
if (SqlBuilder.RefreshOnUpdateProperties.Length > 0)
{
var updatedEntity = (await connection.QueryAsync <TEntity>(
SqlBuilder.ConstructFullSingleUpdateStatement(),
keyEntity,
statementOptions.Transaction,
(int?)statementOptions.CommandTimeout?.TotalSeconds)).FirstOrDefault();
if (updatedEntity != null)
{
CopyEntity(updatedEntity, keyEntity, SqlBuilder.RefreshOnUpdateProperties);
}
return(updatedEntity != null);
}
return(await connection.ExecuteAsync(
SqlBuilder.ConstructFullSingleUpdateStatement(),
keyEntity,
statementOptions.Transaction,
(int?)statementOptions.CommandTimeout?.TotalSeconds) > 0);
}
/// <summary>
/// Performs a common SELECT
/// </summary>
public IEnumerable <TEntity> BatchSelect(IDbConnection connection, AggregatedSqlStatementOptions <TEntity> statementOptions)
{
//validation removed, up to the engine to fail
//Requires.Argument((statementOptions.LimitResults == null && statementOptions.SkipResults == null) || (statementOptions.OrderClause != null || statementOptions.RelationshipOptions.Values.Any(singleJoinOptions => singleJoinOptions.OrderClause != null)), nameof(statementOptions),
// "When using Top or Skip, you must provide an OrderBy clause.");
string statement;
string splitOnCondition;
this.SqlBuilder.ConstructFullJoinSelectStatement(
out statement,
out splitOnCondition,
this.ConstructJoinInstructions(statementOptions, _joinedEntitiesSqlBuilders),
whereClause: statementOptions.WhereClause,
orderClause: statementOptions.OrderClause,
skipRowsCount: statementOptions.SkipResults,
limitRowsCount: statementOptions.LimitResults);
var relationshipInstanceBuilder = new RelationshipEntityInstanceBuilder(_allEntityMappings);
var queriedEntityIdentities = this.Query(connection,
statement,
splitOnCondition,
statementOptions.Parameters,
!statementOptions.ForceStreamResults,
statementOptions.Transaction,
(int?)statementOptions.CommandTimeout?.TotalSeconds,
relationshipInstanceBuilder);
return(this.FilterDuplicates(queriedEntityIdentities));
}
public void Insert(IDbConnection connection, TEntity entity,
AggregatedSqlStatementOptions <TEntity> statementOptions)
{
if (SqlBuilder.RefreshOnInsertProperties.Length > 0)
{
var insertStatement = SqlBuilder.ConstructFullInsertStatement();
var insertedEntity =
connection.Query <TEntity>(
insertStatement,
entity,
statementOptions.Transaction,
commandTimeout: (int?)statementOptions.CommandTimeout?.TotalSeconds)
.FirstOrDefault();
// copy all the database generated props back onto our entity
CopyEntity(insertedEntity, entity, SqlBuilder.RefreshOnInsertProperties);
}
else
{
connection.Execute(
SqlBuilder.ConstructFullInsertStatement(),
entity,
statementOptions.Transaction,
(int?)statementOptions.CommandTimeout?.TotalSeconds);
}
}
public int BulkDelete(IDbConnection connection, AggregatedSqlStatementOptions <TEntity> statementOptions)
{
return(connection.Execute(
_sqlBuilder.ConstructFullBatchDeleteStatement(statementOptions.WhereClause),
statementOptions.Parameters,
transaction: statementOptions.Transaction,
commandTimeout: (int?)statementOptions.CommandTimeout?.TotalSeconds));
}
public int Count(IDbConnection connection, AggregatedSqlStatementOptions <TEntity> statementOptions)
{
return(connection.ExecuteScalar <int>(
SqlBuilder.ConstructFullCountStatement(statementOptions.WhereClause),
statementOptions.Parameters,
statementOptions.Transaction,
(int?)statementOptions.CommandTimeout?.TotalSeconds));
}
public bool DeleteById(IDbConnection connection, TEntity keyEntity, AggregatedSqlStatementOptions <TEntity> statementOptions)
{
return(connection.Execute(
_sqlBuilder.ConstructFullSingleDeleteStatement(),
keyEntity,
transaction: statementOptions.Transaction,
commandTimeout: (int?)statementOptions.CommandTimeout?.TotalSeconds) > 0);
}
public Task <int> BulkUpdateAsync(IDbConnection connection, TEntity entity, AggregatedSqlStatementOptions <TEntity> statementOptions)
{
return(connection.ExecuteAsync(
_sqlBuilder.ConstructFullBatchUpdateStatement(statementOptions.WhereClause),
entity,
transaction: statementOptions.Transaction,
commandTimeout: (int?)statementOptions.CommandTimeout?.TotalSeconds));
}
public Task <int> CountAsync(IDbConnection connection, AggregatedSqlStatementOptions <TEntity> statementOptions)
{
return(connection.ExecuteScalarAsync <int>(
_sqlBuilder.ConstructFullCountStatement(statementOptions.WhereClause),
statementOptions.Parameters,
transaction: statementOptions.Transaction,
commandTimeout: (int?)statementOptions.CommandTimeout?.TotalSeconds));
}
public TEntity SelectById(IDbConnection connection, TEntity keyEntity, AggregatedSqlStatementOptions <TEntity> statementOptions)
{
return(connection.Query <TEntity>(
_sqlBuilder.ConstructFullSingleSelectStatement(),
keyEntity,
transaction: statementOptions.Transaction,
commandTimeout: (int?)statementOptions.CommandTimeout?.TotalSeconds).SingleOrDefault());
}
public async Task <TEntity> SelectByIdAsync(IDbConnection connection, TEntity keyEntity,
AggregatedSqlStatementOptions <TEntity> statementOptions)
{
return((await connection.QueryAsync <TEntity>(
SqlBuilder.ConstructFullSingleSelectStatement(),
keyEntity,
statementOptions.Transaction,
(int?)statementOptions.CommandTimeout?.TotalSeconds)).SingleOrDefault());
}
public async Task <bool> DeleteByIdAsync(IDbConnection connection, TEntity keyEntity,
AggregatedSqlStatementOptions <TEntity> statementoptions)
{
return(await connection.ExecuteAsync(
SqlBuilder.ConstructFullSingleDeleteStatement(),
keyEntity,
statementoptions.Transaction,
(int?)statementoptions.CommandTimeout?.TotalSeconds) > 0);
}
protected IEnumerable <StatementSqlBuilderJoinInstruction> ConstructJoinInstructions(
AggregatedSqlStatementOptions <TEntity> statementOptions,
params GenericStatementSqlBuilder[] joinedEntitySqlBuilders)
{
foreach (var joinedEntitySqlBuilder in joinedEntitySqlBuilders)
{
var joinedEntityOptions =
statementOptions.RelationshipOptions[joinedEntitySqlBuilder.EntityMapping.EntityType];
yield return
(new StatementSqlBuilderJoinInstruction(joinedEntitySqlBuilder, joinedEntityOptions.JoinType,
joinedEntityOptions.WhereClause, joinedEntityOptions.OrderClause));
}
}
public Task <IEnumerable <TEntity> > BatchSelectAsync(IDbConnection connection,
AggregatedSqlStatementOptions <TEntity> statementOptions)
{
return(connection.QueryAsync <TEntity>(
SqlBuilder.ConstructFullBatchSelectStatement(
statementOptions.WhereClause,
statementOptions.OrderClause,
statementOptions.SkipResults,
statementOptions.LimitResults),
statementOptions.Parameters,
statementOptions.Transaction,
(int?)statementOptions.CommandTimeout?.TotalSeconds));
}
public IEnumerable <TEntity> BatchSelect(IDbConnection connection,
AggregatedSqlStatementOptions <TEntity> statementOptions)
{
return(connection.Query <TEntity>(
SqlBuilder.ConstructFullBatchSelectStatement(
statementOptions.WhereClause,
statementOptions.OrderClause,
statementOptions.SkipResults,
statementOptions.LimitResults),
statementOptions.Parameters,
buffered: !statementOptions.ForceStreamResults,
transaction: statementOptions.Transaction,
commandTimeout: (int?)statementOptions.CommandTimeout?.TotalSeconds));
}
public IEnumerable <TEntity> BatchSelect(IDbConnection connection, AggregatedSqlStatementOptions <TEntity> statementOptions)
{
Requires.Argument(
(statementOptions.LimitResults == null && statementOptions.SkipResults == null) ||
(statementOptions.OrderClause != null), nameof(statementOptions), "When using Top or Skip, you must provide an OrderBy clause.");
return(connection.Query <TEntity>(
_sqlBuilder.ConstructFullBatchSelectStatement(
whereClause: statementOptions.WhereClause,
orderClause: statementOptions.OrderClause,
skipRowsCount: statementOptions.SkipResults,
limitRowsCount: statementOptions.LimitResults),
statementOptions.Parameters,
buffered: !statementOptions.ForceStreamResults,
transaction: statementOptions.Transaction,
commandTimeout: (int?)statementOptions.CommandTimeout?.TotalSeconds));
}
/// <summary>
/// Performs a COUNT on a range of items.
/// </summary>
public Task <int> CountAsync(IDbConnection connection, AggregatedSqlStatementOptions <TEntity> statementOptions)
{
string statement;
string splitOnCondition;
this.SqlBuilder.ConstructFullJoinSelectStatement(
out statement,
out splitOnCondition,
this.ConstructJoinInstructions(statementOptions, _joinedEntitiesSqlBuilders),
selectClause: this.SqlBuilder.ConstructCountSelectClause(),
whereClause: statementOptions.WhereClause);
return(connection.ExecuteScalarAsync <int>(
statement,
statementOptions.Parameters,
transaction: statementOptions.Transaction,
commandTimeout: (int?)statementOptions.CommandTimeout?.TotalSeconds));
}
/// <summary>
/// Performs a COUNT on a range of items.
/// </summary>
public int Count(IDbConnection connection, AggregatedSqlStatementOptions <TEntity> statementOptions)
{
string statement;
string splitOnCondition;
SqlBuilder.ConstructFullJoinSelectStatement(
out statement,
out splitOnCondition,
ConstructJoinInstructions(statementOptions, _joinedEntitiesSqlBuilders),
SqlBuilder.ConstructCountSelectClause(),
statementOptions.WhereClause);
return(connection.ExecuteScalar <int>(
statement,
statementOptions.Parameters,
statementOptions.Transaction,
(int?)statementOptions.CommandTimeout?.TotalSeconds));
}
/// <summary>
/// Performs a SELECT operation on a single entity, using its keys
/// </summary>
public TEntity SelectById(IDbConnection connection, TEntity keyEntity, AggregatedSqlStatementOptions <TEntity> statementOptions)
{
string statement;
string splitOnCondition;
this.SqlBuilder.ConstructFullJoinSelectStatement(
out statement,
out splitOnCondition,
this.ConstructJoinInstructions(statementOptions, _joinedEntitiesSqlBuilders),
whereClause: $"{this.SqlBuilder.ConstructKeysWhereClause(this.SqlBuilder.GetTableName())}");
var relationshipInstanceBuilder = new RelationshipEntityInstanceBuilder(_allEntityMappings);
return(this.Query(connection,
statement,
splitOnCondition,
keyEntity,
false,
statementOptions.Transaction,
(int?)statementOptions.CommandTimeout?.TotalSeconds, relationshipInstanceBuilder).SingleOrDefault());
}
public async Task InsertAsync(IDbConnection connection, TEntity entity, AggregatedSqlStatementOptions <TEntity> statementOptions)
{
if (_sqlBuilder.RefreshOnInsertProperties.Length > 0)
{
var insertedEntity =
(await
connection.QueryAsync <TEntity>(
_sqlBuilder.ConstructFullInsertStatement(),
entity,
transaction: statementOptions.Transaction,
commandTimeout: (int?)statementOptions.CommandTimeout?.TotalSeconds)).FirstOrDefault();
// copy all the database generated props back onto our entity
this.CopyEntity(insertedEntity, entity, _sqlBuilder.RefreshOnInsertProperties);
}
else
{
connection.ExecuteAsync(
_sqlBuilder.ConstructFullInsertStatement(),
entity,
transaction: statementOptions.Transaction,
commandTimeout: (int?)statementOptions.CommandTimeout?.TotalSeconds);
}
}
public Task <int> BulkDeleteAsync(IDbConnection connection, AggregatedSqlStatementOptions <TEntity> statementOptions)
{
return(_mainEntitySqlStatements.BulkDeleteAsync(connection, statementOptions));
}
public int BulkDelete(IDbConnection connection, AggregatedSqlStatementOptions <TEntity> statementOptions)
{
return(_mainEntitySqlStatements.BulkDelete(connection, statementOptions));
}
public Task <bool> DeleteByIdAsync(IDbConnection connection, TEntity keyEntity, AggregatedSqlStatementOptions <TEntity> statementoptions)
{
return(_mainEntitySqlStatements.DeleteByIdAsync(connection, keyEntity, statementoptions));
}
public bool DeleteById(IDbConnection connection, TEntity keyEntity, AggregatedSqlStatementOptions <TEntity> statementOptions)
{
return(_mainEntitySqlStatements.DeleteById(connection, keyEntity, statementOptions));
}
public Task InsertAsync(IDbConnection connection, TEntity entity, AggregatedSqlStatementOptions <TEntity> statementOptions)
{
return(_mainEntitySqlStatements.InsertAsync(connection, entity, statementOptions));
}
public void Insert(IDbConnection connection, TEntity entity, AggregatedSqlStatementOptions <TEntity> statementOptions)
{
_mainEntitySqlStatements.Insert(connection, entity, statementOptions);
}
public Task <IEnumerable <TEntity> > BatchSelectAsync(IDbConnection connection, AggregatedSqlStatementOptions <TEntity> statementOptions)
{
//validation removed, let to the engine to fail
//Requires.Argument(
// (statementOptions.LimitResults == null && statementOptions.SkipResults == null)
// || (statementOptions.OrderClause != null), nameof(statementOptions), "When using Top or Skip, you must provide an OrderBy clause.");
return(connection.QueryAsync <TEntity>(
_sqlBuilder.ConstructFullBatchSelectStatement(
whereClause: statementOptions.WhereClause,
orderClause: statementOptions.OrderClause,
skipRowsCount: statementOptions.SkipResults,
limitRowsCount: statementOptions.LimitResults),
statementOptions.Parameters,
transaction: statementOptions.Transaction,
commandTimeout: (int?)statementOptions.CommandTimeout?.TotalSeconds));
}
public void Insert(IDbConnection connection, TEntity entity, AggregatedSqlStatementOptions <TEntity> statementOptions)
{
//As much as it pains me to do so, I have to treat Oracle on a separate block since there is no
//way to insert data and retrieve it on the same statement, so I must use a table and select from it
//and drop it after select
if (!(_sqlBuilder is OracleBuilder))
{
if (_sqlBuilder.RefreshOnInsertProperties.Length > 0)
{
var insertedEntity =
connection.Query <TEntity>(
_sqlBuilder.ConstructFullInsertStatement(),
entity,
transaction: statementOptions.Transaction,
commandTimeout: (int?)statementOptions.CommandTimeout?.TotalSeconds).FirstOrDefault();
// copy all the database generated props back onto our entity
this.CopyEntity(insertedEntity, entity, _sqlBuilder.RefreshOnInsertProperties);
}
else
{
connection.Execute(
_sqlBuilder.ConstructFullInsertStatement(),
entity,
transaction: statementOptions.Transaction,
commandTimeout: (int?)statementOptions.CommandTimeout?.TotalSeconds);
}
}
else
{
if (_sqlBuilder.RefreshOnInsertProperties.Length > 0)
{
//To guarantee well create a table we can drop, and its unique to that transaction, I've added a output parameter to the query
var param = new DynamicParameters(entity);
param.Add("temp_table_fastcrud", dbType: DbType.String, direction: ParameterDirection.Output, size: 40);
connection.Execute(
_sqlBuilder.ConstructFullInsertStatement(),
param,
transaction: statementOptions.Transaction,
commandTimeout: (int?)statementOptions.CommandTimeout?.TotalSeconds);
var temp_table_fastcrud = param.Get <string>("temp_table_fastcrud");
var insertedEntity = connection.Query <TEntity>($"SELECT * FROM {temp_table_fastcrud}", null, statementOptions.Transaction).FirstOrDefault();
connection.Execute($"DROP TABLE {temp_table_fastcrud}", null, statementOptions.Transaction);
// copy all the database generated props back onto our entity
this.CopyEntity(insertedEntity, entity, _sqlBuilder.RefreshOnInsertProperties);
}
else
{
connection.Execute(
_sqlBuilder.ConstructFullInsertStatement(),
entity,
transaction: statementOptions.Transaction,
commandTimeout: (int?)statementOptions.CommandTimeout?.TotalSeconds);
}
}
}
