/// <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);