/// <summary>
/// Get DbProviderFactory from query criteria.
/// </summary>
/// <param name="criteria">The query criteria.</param>
/// <returns></returns>
public DbProviderFactory GetDbProviderFactory(QueryCriteria criteria)
{
if (criteria == null)
{
throw new ArgumentNullException("criteria");
}
var connStr = GetConnectionString(criteria.ConnectionStringName);
var cmdBuilder = GetQueryCommandBuilder(connStr.ProviderName);
return(cmdBuilder.GetDbProviderFactory());
}
public QueryCriteria Delete(params Condition[] conditions)
{
var criteria = new QueryCriteria(_tableName, _connectionStringName,
_readOnly, _predefinedColumns).Delete();
if (conditions != null && conditions.Length > 0)
{
foreach (var condition in conditions)
{
criteria.And(condition);
}
}
return(criteria);
}
public ICollection <TRecord> ExecuteMany(QueryCriteria criteria)
{
using (var cmd = _cmdFactory.CreateCommand(criteria, false))
{
using (var conn = cmd.Connection)
{
conn.Open();
using (var rdr = cmd.ExecuteReader())
{
var many = _mapperMany(rdr);
return(many);
}
}
}
}
private static string BuildDeleteCacheableSql(QueryCriteria criteria)
{
var sb = new StringBuilder();
sb.Append("DELETE FROM ");
sb.Append(criteria.TableName.ToDatabaseObjectName());
if (criteria.Conditions.Count > 0)
{
sb.Append(" ");
sb.Append("WHERE ");
AppendConditions(criteria, sb);
}
return(sb.ToString());
}
/// <summary>
/// Appends the sort bys.
/// </summary>
/// <param name="criteria">The criteria.</param>
/// <param name="sb">The sb.</param>
protected static void AppendSortBys(QueryCriteria criteria, StringBuilder sb)
{
var separate = "";
var en = criteria.SortBys.GetEnumerator();
while (en.MoveNext())
{
sb.Append(separate);
sb.Append(en.Current.Key.ToExpressionCacheableSql());
if (en.Current.Value)
{
sb.Append(" DESC");
}
separate = ", ";
}
}
/// <summary>
/// Appends the conditions.
/// </summary>
/// <param name="criteria">The criteria.</param>
/// <param name="sb">The sb.</param>
protected static void AppendConditions(QueryCriteria criteria, StringBuilder sb)
{
for (var i = 0; i < criteria.Conditions.Count; ++i)
{
if (i > 0)
{
if (criteria.ConditionAndOrs[i] == ConditionAndOr.And)
{
sb.Append(" AND ");
}
else
{
sb.Append(" OR ");
}
}
sb.Append(criteria.Conditions[i].ToConditionCacheableSql());
}
}
/// <summary>
/// Create a DbCommand from a query criteria.
/// </summary>
/// <param name="criteria">The query criteria.</param>
/// <param name="isCountCommand">if a count command is expected to create.</param>
/// <param name="returnAutoGeneratedInsertId">if the auto generated id is expected to return for a insert command.</param>
/// <returns></returns>
public DbCommand CreateCommand(QueryCriteria criteria, bool isCountCommand, bool returnAutoGeneratedInsertId)
{
if (criteria == null)
{
throw new ArgumentNullException("criteria");
}
var connStr = GetConnectionString(criteria.ConnectionStringName);
if (connStr == null)
{
throw new ConfigurationErrorsException(string.Format("Could not find speficied connectionstring key from configuration store: {0}.", criteria.ConnectionStringName));
}
var cmdBuilder = GetQueryCommandBuilder(connStr.ProviderName);
DbCommand cmd;
if (criteria.QueryType == QueryType.Sproc)
{
cmd = cmdBuilder.GetDbProviderFactory().CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = criteria.TableName;
}
else
{
cmd = cmdBuilder.BuildCommand(criteria, isCountCommand, returnAutoGeneratedInsertId);
}
cmd.Connection = cmdBuilder.GetDbProviderFactory().CreateConnection();
cmd.Connection.ConnectionString = connStr.ConnectionString;
if (criteria.QueryType == QueryType.Sproc)
{
var sprocCmd = new SprocDbCommand(cmd, cmdBuilder);
foreach (var parameterCondition in criteria.SprocParameterConditions)
{
sprocCmd.AddParameter(parameterCondition);
}
return(sprocCmd);
}
return(cmd);
}
public TRecord ExecuteOne(QueryCriteria criteria)
{
using (var cmd = _cmdFactory.CreateCommand(criteria, false))
{
using (var conn = cmd.Connection)
{
conn.Open();
using (var rdr = cmd.ExecuteReader())
{
if (rdr.Read())
{
var one = _mapperOne(rdr);
return(one);
}
}
}
}
return(default(TRecord));
}
/// <summary>
/// Builds the command parameters.
/// </summary>
/// <param name="criteria">The criteria.</param>
/// <param name="parameterCollection">The parameter collection.</param>
/// <param name="setParameterValueOnly">if set to <c>true</c> [set parameter value only].</param>
protected void BuildCommandParameters(QueryCriteria criteria, DbParameterCollection parameterCollection, bool setParameterValueOnly)
{
if (criteria == null)
{
return;
}
foreach (var column in criteria.ResultColumns)
{
AddExpressionParameters(column, parameterCollection, setParameterValueOnly);
}
foreach (var assignment in criteria.Assignments)
{
AddAssignmentParameters(assignment, parameterCollection, setParameterValueOnly);
}
foreach (var condition in criteria.Conditions)
{
AddConditionParameters(condition, parameterCollection, setParameterValueOnly);
}
}
/// <summary>
/// Builds the command.
/// </summary>
/// <param name="criteria">The criteria.</param>
/// <param name="isCountCommand">if the command to create is a count command.</param>
/// <param name="returnAutoGeneratedInsertId">if return auto generated insert id, applicable only for insert.</param>
/// <returns></returns>
public DbCommand BuildCommand(QueryCriteria criteria, bool isCountCommand, bool returnAutoGeneratedInsertId)
{
if (criteria == null)
{
throw new ArgumentNullException("criteria");
}
DbCommand cmd;
var cacheableSql = BuildCacheableSql(criteria, isCountCommand, returnAutoGeneratedInsertId);
if (!_cachedCommands.ContainsKey(cacheableSql))
{
lock (_cachedCommandsLock)
{
if (!_cachedCommands.ContainsKey(cacheableSql))
{
cmd = GetDbProviderFactory().CreateCommand();
cmd.CommandType = CommandType.Text;
BuildCommandParameters(criteria, cmd.Parameters, false);
cmd.CommandText = BuildCommandText(cacheableSql, cmd.Parameters);
var cachedCmd = CloneCommand(cmd);
foreach (DbParameter p in cachedCmd.Parameters)
{
p.Value = null;
}
_cachedCommands.Add(cacheableSql, cachedCmd);
return(cmd);
}
}
}
cmd = CloneCommand(_cachedCommands[cacheableSql]);
BuildCommandParameters(criteria, cmd.Parameters, true);
return(cmd);
}
/// <summary>
/// Clones this instance.
/// </summary>
/// <returns></returns>
public QueryCriteria Clone()
{
var clone = new QueryCriteria();
clone._assignments.AddRange(_assignments);
clone._sprocParameterConditions.AddRange(_sprocParameterConditions);
clone._conditionAndOrs.AddRange(_conditionAndOrs);
clone._conditions.AddRange(_conditions);
clone._connectionStringName = _connectionStringName;
clone._isDistinct = _isDistinct;
clone._maxResults = _maxResults;
clone._predefinedColumns.AddRange(_predefinedColumns);
clone._queryType = _queryType;
clone._readOnly = _readOnly;
clone._resultColumns.AddRange(_resultColumns);
clone._skipResults = _skipResults;
foreach (var item in _sortBys)
{
clone._sortBys.Add(item.Key, item.Value);
}
clone._tableName = _tableName;
return(clone);
}
private string BuildInsertCacheableSql(QueryCriteria criteria, bool returnAutoGeneratedInsertId)
{
var columnValues = GetColumnValues(criteria.Assignments);
var sb = new StringBuilder();
sb.Append("INSERT INTO ");
sb.Append(criteria.TableName.ToDatabaseObjectName());
sb.Append(" (");
var separate = "";
foreach (var columnValue in columnValues)
{
sb.Append(separate);
sb.Append(columnValue.Key);
separate = ", ";
}
sb.Append(") VALUES (");
separate = "";
foreach (var columnValue in columnValues)
{
sb.Append(separate);
sb.Append(ReferenceEquals(columnValue.Value, null)
? "NULL"
: columnValue.Value.ToExpressionCacheableSql());
separate = ", ";
}
sb.Append(")");
if (returnAutoGeneratedInsertId)
{
sb.Append(BuildReturnAutoGeneratedInsertIdSql());
}
return(sb.ToString());
}
/// <summary> /// Builds the no paging cacheable SQL. /// </summary> /// <param name="criteria">The criteria.</param> /// <param name="isCountCommand">if set to <c>true</c> [is count command].</param> /// <returns></returns> protected abstract string BuildNoPagingCacheableSql(QueryCriteria criteria, bool isCountCommand);
/// <summary>
/// Create a DbCommand from a query criteria.
/// </summary>
/// <param name="criteria">The query criteria.</param>
/// <returns></returns>
public DbCommand CreateCommand(QueryCriteria criteria)
{
return(CreateCommand(criteria, false, false));
}
/// <summary> /// Execute a custom count criteria created through ActiveRecord.Q.Select() /// </summary> /// <param name="criteria">The count criteria</param> /// <returns></returns> public abstract int Count(QueryCriteria criteria);
/// <summary> /// Execute a custom delete criteria created through ActiveRecord.Q.Delete() /// </summary> /// <param name="criteria">The delete criteria</param> /// <returns></returns> public abstract int Delete(QueryCriteria criteria);
/// <summary> /// Execute a custom update criteria created through ActiveRecord.Q.Update() /// </summary> /// <param name="criteria">The update criteria to execute</param> /// <returns>How many rows updated in database</returns> public abstract int Save(QueryCriteria criteria);
/// <summary>
/// Create a DbCommand from a query criteria.
/// </summary>
/// <param name="criteria">The query criteria.</param>
/// <param name="isCountCommand">if a count command is expected to create.</param>
/// <returns></returns>
public DbCommand CreateCommand(QueryCriteria criteria, bool isCountCommand)
{
return(CreateCommand(criteria, isCountCommand, false));
}
/// <summary> /// Builds the paging cacheable SQL. /// </summary> /// <param name="criteria">The criteria.</param> /// <returns></returns> protected abstract string BuildPagingCacheableSql(QueryCriteria criteria);
