/// <summary>
/// Return the number of rows that match the supplied WhereConditions
/// </summary>
/// <param name="whereConditions">IEnumerable list of WhereCondtions to filter by</param>
/// <returns></returns>
protected virtual long GetCount(IEnumerable <WhereCondition> whereConditions)
{
Dictionary <string, object> parameters = new Dictionary <string, object>();
string query = $"SELECT COUNT(*) FROM {TableName} ";
if (whereConditions != null && whereConditions.Any())
{
List <string> whereStatements = new List <string>();
int index = 1;
foreach (WhereCondition whereCondition in whereConditions)
{
if (whereCondition.IsNullEqualsOrNotEquals())
{
whereStatements.Add(TableName + "." + whereCondition.ColumnName + " " + whereCondition.GetComparisonString() + " NULL");
}
else
{
whereStatements.Add(TableName + "." + whereCondition.ColumnName + " " + whereCondition.GetComparisonString() + " " + DbAdapter.GetParameterPlaceholder(whereCondition.ColumnName, index));
parameters.Add(DbAdapter.GetParameterName(whereCondition.ColumnName, index), whereCondition.GetParameterValue());
}
index++;
}
query += "WHERE " + string.Join(" AND ", whereStatements);
}
if (DbAdapter.DebugLogger != null)
{
DbAdapter.DebugLogger.LogInformation($"{GetType().Name} /Query/ {query} /Parameters/ {string.Join(", ", parameters.Select(x => x.Key + ":" + x.Value))}");
}
return(GetDbConnection().QuerySingle <long>(query, parameters, DbAdapter.DbTransaction));
}
/// <summary>
/// Updates the matching row in the database
/// </summary>
/// <param name="entity">Entity to update in the database</param>
public virtual void Update(T entity)
{
List <string> updateColumns = new List <string>();
List <string> primaryKeyColumns = new List <string>();
Dictionary <string, object> parameters = new Dictionary <string, object>();
int index = 1;
foreach (Map map in Mappings.StandardMaps)
{
updateColumns.Add(map.ColumnName + "=" + DbAdapter.GetParameterPlaceholder(map.ColumnName, index));
parameters.Add(DbAdapter.GetParameterName(map.ColumnName, index), map.GetValue(entity));
index++;
}
foreach (Map map in Mappings.PrimaryKeyMaps)
{
primaryKeyColumns.Add(map.ColumnName + "=" + DbAdapter.GetParameterPlaceholder(map.ColumnName, index));
parameters.Add(DbAdapter.GetParameterName(map.ColumnName, index), map.GetValue(entity));
index++;
}
string query = $"UPDATE {TableName} " +
$"SET {string.Join(",", updateColumns)} " +
$"WHERE {string.Join(" AND ", primaryKeyColumns)} ";
if (DbAdapter.DebugLogger != null)
{
DbAdapter.DebugLogger.LogInformation($"{GetType().Name} /Query/ {query} /Parameters/ {string.Join(", ", parameters.Select(x => x.Key + ":" + x.Value))}");
}
GetDbConnection().Execute(query, parameters, DbAdapter.DbTransaction);
}
/// <summary>
/// Add multiple values to the database. Inserts are done in batches. Cache does not support this method of inserting multiple entities and will instead add them separately.
/// </summary>
/// <param name="entities">Entities to add to the database</param>
public virtual void Add(IEnumerable <T> entities)
{
if (DbAdapter.ListInsertSupported)
{
List <string> columns = new List <string>();
foreach (Map map in Mappings.AllMaps.Where(x => x != Mappings.AutonumberMap))
{
columns.Add(map.ColumnName);
}
// Caculate the batch size based on the number of parameters that will be generated in the SQL statements.
// SQLite has a parameter limit of 999
int batchSize = 900 / columns.Count;
// Split entities into batches
foreach (List <T> batchGroup in entities.Select((x, i) => new { Value = x, Index = i }).GroupBy(x => x.Index / batchSize).Select(x => x.Select(y => y.Value).ToList()))
{
string query = $"INSERT INTO {TableName} ({string.Join(",", columns)}) VALUES ";
List <string> values = new List <string>();
Dictionary <string, object> parameters = new Dictionary <string, object>();
int counter = 1;
foreach (T entity in batchGroup)
{
List <string> vals = new List <string>();
int index = 1;
foreach (Map map in Mappings.AllMaps.Where(x => x != Mappings.AutonumberMap))
{
vals.Add(DbAdapter.GetParameterPlaceholder(map.ColumnName, index) + counter);
parameters.Add(DbAdapter.GetParameterName(map.ColumnName, index) + counter, map.GetValue(entity));
index++;
}
values.Add($"({string.Join(",", vals)})");
counter++;
}
query += string.Join(",", values);
if (DbAdapter.DebugLogger != null)
{
DbAdapter.DebugLogger.LogInformation($"{GetType().Name} /Query/ {query} /Parameters/ {string.Join(", ", parameters.Select(x => x.Key + ":" + x.Value))}");
}
GetDbConnection().Execute(query, parameters, DbAdapter.DbTransaction);
}
}
else
{
foreach (T entity in entities)
{
Add(entity);
}
}
}
/// <summary>
/// Adds the supplied entity to the database
/// </summary>
/// <param name="entity">Entity to add to the database</param>
public virtual void Add(T entity)
{
List <string> columns = new List <string>();
List <string> values = new List <string>();
Dictionary <string, object> parameters = new Dictionary <string, object>();
int index = 1;
foreach (Map map in Mappings.AllMaps.Where(x => x != Mappings.AutonumberMap))
{
columns.Add(map.ColumnName);
values.Add(DbAdapter.GetParameterPlaceholder(map.ColumnName, index));
parameters.Add(DbAdapter.GetParameterName(map.ColumnName, index), map.GetValue(entity));
index++;
}
string query = $"INSERT INTO {TableName} " +
$"({string.Join(",", columns)}) VALUES ({string.Join(",", values)})";
if (DbAdapter.DebugLogger != null)
{
DbAdapter.DebugLogger.LogInformation($"{GetType().Name} /Query/ {query} /Parameters/ {string.Join(", ", parameters.Select(x => x.Key + ":" + x.Value))}");
}
if (Mappings.AutonumberMap == null)
{
GetDbConnection().Execute(query, parameters, DbAdapter.DbTransaction);
}
else
{
long autonumberValue = 0;
if (DbAdapter.LastInsertIdInSeparateQuery)
{
GetDbConnection().Execute(query, parameters, DbAdapter.DbTransaction);
autonumberValue = GetDbConnection().QuerySingle <long>(DbAdapter.GetLastInsertIdStatement(), null, DbAdapter.DbTransaction);
}
else
{
query += ";" + DbAdapter.GetLastInsertIdStatement();
autonumberValue = GetDbConnection().QuerySingle <long>(query, parameters, DbAdapter.DbTransaction);
}
PropertyInfo prop = entity.GetType().GetProperty(Mappings.AutonumberMap.PropertyName);
Mappings.AutonumberMap.SetValue(entity, Convert.ChangeType(autonumberValue, prop.PropertyType));
}
}
/// <summary>
/// Returns the basic SELECT and FROM parts of a SQL statement for the datebase table
/// </summary>
/// <returns></returns>
protected virtual string GetBasicSelectText()
{
List <string> columns = new List <string>();
List <string> joins = new List <string>();
foreach (Map map in Mappings.AllMaps)
{
columns.Add(DbAdapter.GetSelectColumnCast(typeof(T), TableName, map));
}
string query = $"SELECT {string.Join(",", columns)} FROM {TableName} ";
if (joins.Any())
{
query += string.Join(" ", joins.Distinct()) + " ";
}
return(query);
}
/// <summary>
/// Get the current DbConnection or creates a new one
/// </summary>
/// <returns></returns>
protected IDbConnection GetDbConnection()
{
if (DbAdapter.IsDisposed)
{
throw new Exception("This DbAdapter has already been disposed. " +
"Make sure you are calling the DbAdapter and your Unit of Work class in a using statement.");
}
if (DbAdapter.DbConnection == null)
{
DbAdapter.DbConnection = DbAdapter.CreateNewDbConnection(ConnectionString);
}
if (DbAdapter.DbConnection.State != ConnectionState.Open)
{
DbAdapter.DbConnection.Open();
}
DbAdapter.ConfigureDbConnection();
return(DbAdapter.DbConnection);
}
/// <summary>
/// Return all rows matching the queryin FluentQueryBuilder
/// </summary>
/// <param name="builder">FluentQueryBuilder</param>
/// <returns></returns>
protected List <T> GetEntities(FluentQueryBuilder <T> builder)
{
QueryBuilder query = GetQueryBuilder();
List <string> whereStatements = new List <string>();
int index = 1;
foreach (WhereCondition whereCondition in builder.WhereConditions)
{
if (whereCondition.IsNullEqualsOrNotEquals())
{
whereStatements.Add(TableName + "." + whereCondition.ColumnName + " " + whereCondition.GetComparisonString() + " NULL");
}
else
{
whereStatements.Add(TableName + "." + whereCondition.ColumnName + " " + whereCondition.GetComparisonString() + " " + DbAdapter.GetParameterPlaceholder(whereCondition.ColumnName, index));
query.AddParameter(DbAdapter.GetParameterName(whereCondition.ColumnName, index), whereCondition.GetParameterValue());
}
index++;
}
query.SqlQuery += $"WHERE {string.Join(" AND ", whereStatements)} ";
if (builder.OrderStatements != null && builder.OrderStatements.Any())
{
List <string> orderBy = new List <string>();
foreach (OrderStatement orderStatement in builder.OrderStatements)
{
orderBy.Add(orderStatement.ColumnName + (orderStatement.IsAscending ? "" : " DESC"));
}
query.SqlQuery += $"ORDER BY {TableName}.{string.Join(",", orderBy)} ";
}
if (builder.Limit.HasValue || builder.Offset.HasValue)
{
query.SqlQuery += DbAdapter.GetLimitOffsetStatement(builder.Limit, builder.Offset, query);
}
return(GetEntities(query));
}