コード例 #1
0
        CommandParameterValues InstanceToParameters(object instance, DocumentMap mapping, string prefix = null)
        {
            var result = new CommandParameterValues
            {
                [$"{prefix}Id"] = mapping.IdColumn.ReaderWriter.Read(instance)
            };

            var mType = mapping.InstanceTypeResolver.GetTypeFromInstance(instance);

            result[$"{prefix}JSON"] = JsonConvert.SerializeObject(instance, mType, jsonSerializerSettings);

            foreach (var c in mappings.Get(mType).IndexedColumns)
            {
                var value = c.ReaderWriter.Read(instance);
                if (value != null && value != DBNull.Value && value is string && c.MaxLength > 0)
                {
                    var attemptedLength = ((string)value).Length;
                    if (attemptedLength > c.MaxLength)
                    {
                        throw new StringTooLongException(string.Format("An attempt was made to store {0} characters in the {1}.{2} column, which only allows {3} characters.", attemptedLength, mapping.TableName, c.ColumnName, c.MaxLength));
                    }
                }
                else if (value != null && value != DBNull.Value && value is DateTime && value.Equals(DateTime.MinValue))
                {
                    value = SqlDateTime.MinValue.Value;
                }

                result[$"{prefix}{c.ColumnName}"] = value;
            }
            return(result);
        }
コード例 #2
0
        public override IJoinSourceQueryBuilder <TRecord> Join(IAliasedSelectSource source, JoinType joinType, CommandParameterValues parameterValues, Parameters parameters, ParameterDefaults parameterDefaults)
        {
            if (clauses.Count == 0)
            {
                throw new InvalidOperationException("Must have at least one 'ON' clause per join");
            }

            intermediateJoins.Add(new Join(clauses.ToList(), joinSource, type));
            clauses    = new List <JoinClause>();
            joinSource = source;
            type       = joinType;

            var commandParameterValues    = new CommandParameterValues(ParamValues, parameterValues);
            var combinedParameters        = new Parameters(Params, parameters);
            var combinedParameterDefaults = new ParameterDefaults(ParamDefaults, parameterDefaults);

            ParamValues.Clear();
            ParamValues.AddRange(commandParameterValues);
            Params.Clear();
            Params.AddRange(combinedParameters);
            ParamDefaults.Clear();
            ParamDefaults.AddRange(combinedParameterDefaults);

            return(this);
        }
コード例 #3
0
ファイル: QueryBuilder.cs プロジェクト: onenorth/Nevermore
        public List <TRecord> ToList(int skip, int take)
        {
            const string rowNumberColumnName = "RowNum";
            var          minRowParameter     = new UniqueParameter(uniqueParameterNameGenerator, new Parameter("_minrow"));
            var          maxRowParameter     = new UniqueParameter(uniqueParameterNameGenerator, new Parameter("_maxrow"));

            var clonedSelectBuilder = selectBuilder.Clone();

            clonedSelectBuilder.AddDefaultColumnSelection();
            clonedSelectBuilder.AddRowNumberColumn(rowNumberColumnName, new List <Column>());

            var subqueryBuilder = CreateSubqueryBuilder(clonedSelectBuilder);

            subqueryBuilder.AddWhere(new UnaryWhereParameter(rowNumberColumnName, UnarySqlOperand.GreaterThanOrEqual, minRowParameter));
            subqueryBuilder.AddWhere(new UnaryWhereParameter(rowNumberColumnName, UnarySqlOperand.LessThanOrEqual, maxRowParameter));
            subqueryBuilder.AddOrder("RowNum", false);

            var parmeterValues = new CommandParameterValues(paramValues)
            {
                { minRowParameter.ParameterName, skip + 1 },
                { maxRowParameter.ParameterName, take + skip }
            };

            return(transaction.ExecuteReader <TRecord>(subqueryBuilder.GenerateSelect().GenerateSql(), parmeterValues).ToList());
        }
コード例 #4
0
        public IEnumerable <T> ExecuteReader <T>(string query, CommandParameterValues args, int?commandTimeoutSeconds = null)
        {
            var mapping = mappings.Get(typeof(T));

            using (var command = sqlCommandFactory.CreateCommand(connection, transaction, query, args, mapping, commandTimeoutSeconds))
            {
                AddCommandTrace(command.CommandText);
                return(Stream <T>(command, mapping));
            }
        }
コード例 #5
0
 public void AddRange(CommandParameterValues other)
 {
     foreach (var item in other)
     {
         if (ContainsKey(item.Key))
         {
             throw new Exception($"The parameter {item.Key} already exists");
         }
         this[item.Key] = item.Value;
     }
 }
コード例 #6
0
 public DeleteQueryBuilder(
     IUniqueParameterNameGenerator uniqueParameterNameGenerator,
     Action <Type, Where, CommandParameterValues, int?> executeDelete,
     IEnumerable <IWhereClause> whereClauses,
     CommandParameterValues parameterValues)
 {
     this.uniqueParameterNameGenerator = uniqueParameterNameGenerator;
     this.executeDelete   = executeDelete;
     this.whereClauses    = whereClauses;
     this.parameterValues = parameterValues;
 }
コード例 #7
0
 public SubquerySourceBuilder(ISelect select,
                              IRelationalTransaction relationalTransaction,
                              ITableAliasGenerator tableAliasGenerator,
                              IUniqueParameterNameGenerator uniqueParameterNameGenerator,
                              CommandParameterValues parameterValues,
                              Parameters parameters,
                              ParameterDefaults parameterDefaults)
     : base(relationalTransaction, tableAliasGenerator, uniqueParameterNameGenerator, parameterValues, parameters, parameterDefaults)
 {
     this.select = select;
 }
コード例 #8
0
 public TableSourceQueryBuilder(string tableOrViewName,
                                IRelationalTransaction relationalTransaction,
                                ITableAliasGenerator tableAliasGenerator,
                                IUniqueParameterNameGenerator uniqueParameterNameGenerator,
                                CommandParameterValues parameterValues,
                                Parameters parameters,
                                ParameterDefaults parameterDefaults)
     : base(relationalTransaction, tableAliasGenerator, uniqueParameterNameGenerator, parameterValues, parameters, parameterDefaults)
 {
     this.tableOrViewName = tableOrViewName;
 }
コード例 #9
0
 public DeleteQueryBuilder(IRelationalTransaction relationalTransaction,
                           IUniqueParameterNameGenerator uniqueParameterNameGenerator,
                           string tableName,
                           IEnumerable <IWhereClause> whereClauses,
                           CommandParameterValues parameterValues)
 {
     this.relationalTransaction        = relationalTransaction;
     this.uniqueParameterNameGenerator = uniqueParameterNameGenerator;
     this.tableName       = tableName;
     this.whereClauses    = whereClauses;
     this.parameterValues = parameterValues;
 }
コード例 #10
0
 protected SourceQueryBuilder(IRelationalTransaction relationalTransaction,
                              ITableAliasGenerator tableAliasGenerator,
                              IUniqueParameterNameGenerator uniqueParameterNameGenerator,
                              CommandParameterValues parameterValues,
                              Parameters parameters,
                              ParameterDefaults parameterDefaults)
 {
     RelationalTransaction        = relationalTransaction;
     TableAliasGenerator          = tableAliasGenerator;
     UniqueParameterNameGenerator = uniqueParameterNameGenerator;
     ParamValues   = parameterValues;
     Params        = parameters;
     ParamDefaults = parameterDefaults;
 }
コード例 #11
0
ファイル: QueryBuilder.cs プロジェクト: onenorth/Nevermore
 public QueryBuilder(TSelectBuilder selectBuilder,
                     IRelationalTransaction transaction,
                     ITableAliasGenerator tableAliasGenerator,
                     IUniqueParameterNameGenerator uniqueParameterNameGenerator,
                     CommandParameterValues paramValues,
                     Parameters @params,
                     ParameterDefaults paramDefaults)
 {
     this.selectBuilder                = selectBuilder;
     this.transaction                  = transaction;
     this.tableAliasGenerator          = tableAliasGenerator;
     this.uniqueParameterNameGenerator = uniqueParameterNameGenerator;
     this.paramValues                  = paramValues;
     this.@params       = @params;
     this.paramDefaults = paramDefaults;
 }
コード例 #12
0
 public JoinSourceQueryBuilder(IAliasedSelectSource originalSource,
                               JoinType joinType,
                               IAliasedSelectSource nextJoin,
                               IRelationalTransaction relationalTransaction,
                               ITableAliasGenerator tableAliasGenerator,
                               IUniqueParameterNameGenerator uniqueParameterNameGenerator,
                               CommandParameterValues parameterValues,
                               Parameters parameters,
                               ParameterDefaults parameterDefaults)
     : base(relationalTransaction, tableAliasGenerator, uniqueParameterNameGenerator, parameterValues, parameters, parameterDefaults)
 {
     this.originalSource = originalSource;
     clauses             = new List <JoinClause>();
     joinSource          = nextJoin;
     type = joinType;
 }
コード例 #13
0
 public IEnumerable <T> ExecuteReaderWithProjection <T>(string query, CommandParameterValues args, Func <IProjectionMapper, T> projectionMapper, int?commandTimeoutSeconds = null)
 {
     using (var command = sqlCommandFactory.CreateCommand(connection, transaction, query, args, commandTimeoutSeconds: commandTimeoutSeconds))
     {
         AddCommandTrace(command.CommandText);
         try
         {
             return(Stream(command, projectionMapper));
         }
         catch (SqlException ex)
         {
             throw WrapException(command, ex);
         }
         catch (Exception ex)
         {
             Log.DebugException($"Exception in relational transaction '{name}'", ex);
             throw;
         }
     }
 }
コード例 #14
0
 public T ExecuteScalar <T>(string query, CommandParameterValues args, int?commandTimeoutSeconds = null)
 {
     using (var command = sqlCommandFactory.CreateCommand(connection, transaction, query, args, commandTimeoutSeconds: commandTimeoutSeconds))
     {
         AddCommandTrace(command.CommandText);
         try
         {
             var result = command.ExecuteScalarWithRetry(GetRetryPolicy(RetriableOperation.Select));
             return((T)AmazingConverter.Convert(result, typeof(T)));
         }
         catch (SqlException ex)
         {
             throw WrapException(command, ex);
         }
         catch (Exception ex)
         {
             Log.DebugException($"Exception in relational transaction '{name}'", ex);
             throw;
         }
     }
 }
コード例 #15
0
 public int ExecuteNonQuery(string query, CommandParameterValues args, int?commandTimeoutSeconds = null)
 {
     using (new TimedSection(Log, ms => $"Executing non query took {ms}ms in transaction '{name}': {query}", 300))
         using (var command = sqlCommandFactory.CreateCommand(connection, transaction, query, args, commandTimeoutSeconds: commandTimeoutSeconds))
         {
             AddCommandTrace(command.CommandText);
             try
             {
                 return(command.ExecuteNonQueryWithRetry(GetRetryPolicy(RetriableOperation.Select)));
             }
             catch (SqlException ex)
             {
                 throw WrapException(command, ex);
             }
             catch (Exception ex)
             {
                 Log.DebugException($"Exception in relational transaction '{name}'", ex);
                 throw;
             }
         }
 }
コード例 #16
0
 public void ExecuteRawDeleteQuery(string query, CommandParameterValues args, int?commandTimeoutSeconds = null)
 {
     using (new TimedSection(Log, ms => $"Executing DELETE query took {ms}ms in transaction '{name}': {query}", 300))
         using (var command = sqlCommandFactory.CreateCommand(connection, transaction, query, args, commandTimeoutSeconds: commandTimeoutSeconds))
         {
             AddCommandTrace(command.CommandText);
             try
             {
                 // We can retry deletes because deleting something that doesn't exist will silently do nothing
                 command.ExecuteNonQueryWithRetry(GetRetryPolicy(RetriableOperation.Delete), "ExecuteDeleteQuery " + query);
             }
             catch (SqlException ex)
             {
                 throw WrapException(command, ex);
             }
             catch (Exception ex)
             {
                 Log.DebugException($"Exception in relational transaction '{name}'", ex);
                 throw;
             }
         }
 }
コード例 #17
0
 public void ExecuteReader(string query, CommandParameterValues args, Action <IDataReader> readerCallback)
 {
     using (var command = sqlCommandFactory.CreateCommand(connection, transaction, query, args))
     {
         AddCommandTrace(command.CommandText);
         try
         {
             using (var result = command.ExecuteReaderWithRetry(GetRetryPolicy(RetriableOperation.Select)))
             {
                 readerCallback(result);
             }
         }
         catch (SqlException ex)
         {
             throw WrapException(command, ex);
         }
         catch (Exception ex)
         {
             Log.DebugException($"Exception in relational transaction '{name}'", ex);
             throw;
         }
     }
 }
コード例 #18
0
        public IDbCommand CreateCommand(IDbConnection connection, IDbTransaction transaction, string statement, CommandParameterValues args, DocumentMap mapping = null, int?commandTimeoutSeconds = null)
        {
            var command = connection.CreateCommand();

            try
            {
                command.CommandTimeout = commandTimeoutSeconds ?? DefaultCommandTimeoutSeconds;
                command.CommandText    = statement;
                command.Transaction    = transaction;
                args?.ContributeTo(command, mapping);
                return(command);
            }
            catch
            {
                command.Dispose();
                throw;
            }
        }
コード例 #19
0
        public void InsertMany <TDocument>(string tableName, IReadOnlyCollection <TDocument> instances, bool includeDefaultModelColumns = true, string tableHint = null) where TDocument : class, IId
        {
            if (!instances.Any())
            {
                return;
            }

            var mapping = mappings.Get(instances.First().GetType()); // All instances share the same mapping.

            var parameters      = new CommandParameterValues();
            var valueStatements = new List <string>();
            var instanceCount   = 0;

            foreach (var instance in instances)
            {
                var instancePrefix     = $"{instanceCount}__";
                var instanceParameters = InstanceToParameters(instance, mapping, instancePrefix);
                if (string.IsNullOrWhiteSpace(instance.Id))
                {
                    instanceParameters[$"{instancePrefix}Id"] = AllocateId(mapping);
                }

                parameters.AddRange(instanceParameters);

                var defaultIndexColumnPlaceholders = new string[] { };
                if (includeDefaultModelColumns)
                {
                    defaultIndexColumnPlaceholders = new[] { $"@{instancePrefix}Id", $"@{instancePrefix}JSON" }
                }
                ;

                valueStatements.Add($"({string.Join(", ", mapping.IndexedColumns.Select(c => $"@{instancePrefix}{c.ColumnName}").Union(defaultIndexColumnPlaceholders))})");

                instanceCount++;
            }

            var defaultIndexColumns = new string[] { };

            if (includeDefaultModelColumns)
            {
                defaultIndexColumns = new[] { "Id", "JSON" }
            }
            ;

            var statement = string.Format(
                "INSERT INTO dbo.[{0}] {1} ({2}) values {3}",
                tableName ?? mapping.TableName,
                tableHint ?? "",
                string.Join(", ", mapping.IndexedColumns.Select(c => c.ColumnName).Union(defaultIndexColumns)),
                string.Join(", ", valueStatements)
                );

            using (new TimedSection(Log, ms => $"Insert took {ms}ms in transaction '{name}': {statement}", 300))
                using (var command = sqlCommandFactory.CreateCommand(connection, transaction, statement, parameters, mapping))
                {
                    AddCommandTrace(command.CommandText);
                    try
                    {
                        command.ExecuteNonQueryWithRetry(GetRetryPolicy(RetriableOperation.Insert));
                        instanceCount = 0;
                        foreach (var instance in instances)
                        {
                            var instancePrefix = $"{instanceCount}__";

                            // Copy the assigned Id back onto the document
                            mapping.IdColumn.ReaderWriter.Write(instance, (string)parameters[$"{instancePrefix}Id"]);

                            relatedDocumentStore.PopulateRelatedDocuments(this, instance);
                            instanceCount++;
                        }
                    }
                    catch (SqlException ex)
                    {
                        DetectAndThrowIfKnownException(ex, mapping);
                        throw WrapException(command, ex);
                    }
                    catch (Exception ex)
                    {
                        Log.DebugException($"Exception in relational transaction '{name}'", ex);
                        throw;
                    }
                }
        }

        void AddCommandTrace(string commandText)
        {
            lock (commandTrace)
            {
                if (commandTrace.Count == 100)
                {
                    Log.DebugFormat("A possible N+1 or long running transaction detected, this is a diagnostic message only does not require end-user action.\r\nStarted: {0:s}\r\nStack: {1}\r\n\r\n{2}", CreatedTime, Environment.StackTrace, string.Join("\r\n", commandTrace));
                }

                if (commandTrace.Count <= 200)
                {
                    commandTrace.Add(DateTime.Now.ToString("s") + " " + commandText);
                }
            }
        }
コード例 #20
0
ファイル: QueryBuilder.cs プロジェクト: onenorth/Nevermore
        public IJoinSourceQueryBuilder <TRecord> Join(IAliasedSelectSource source, JoinType joinType, CommandParameterValues parameterValues, Parameters parameters, ParameterDefaults parameterDefaults)
        {
            var subquery = new SubquerySource(selectBuilder.GenerateSelectWithoutDefaultOrderBy(), tableAliasGenerator.GenerateTableAlias());

            return(new JoinSourceQueryBuilder <TRecord>(subquery,
                                                        joinType,
                                                        source,
                                                        transaction,
                                                        tableAliasGenerator,
                                                        uniqueParameterNameGenerator,
                                                        new CommandParameterValues(ParameterValues, parameterValues),
                                                        new Parameters(Parameters, parameters),
                                                        new ParameterDefaults(ParameterDefaults, parameterDefaults)));
        }
コード例 #21
0
 public override IJoinSourceQueryBuilder <TRecord> Join(IAliasedSelectSource source, JoinType joinType, CommandParameterValues parameterValues, Parameters parameters, ParameterDefaults parameterDefaults)
 {
     return(new JoinSourceQueryBuilder <TRecord>(AsSource(),
                                                 joinType,
                                                 source,
                                                 RelationalTransaction,
                                                 TableAliasGenerator,
                                                 UniqueParameterNameGenerator,
                                                 new CommandParameterValues(ParamValues, parameterValues),
                                                 new Parameters(Params, parameters),
                                                 new ParameterDefaults(ParamDefaults, parameterDefaults)));
 }
コード例 #22
0
 public abstract IJoinSourceQueryBuilder <TRecord> Join(IAliasedSelectSource source, JoinType joinType, CommandParameterValues parameterValues, Parameters parameters, ParameterDefaults parameterDefaults);
コード例 #23
0
 public CommandParameterValues(CommandParameterValues from)
     : base(from, StringComparer.OrdinalIgnoreCase)
 {
     CommandType = from.CommandType;
 }
コード例 #24
0
        public DbCommand CreateCommand(DbConnection connection, DbTransaction transaction, string statement, CommandParameterValues args, ITypeHandlerRegistry typeHandlers, DocumentMap mapping = null, TimeSpan?commandTimeout = null)
        {
            var command = connection.CreateCommand();

            try
            {
                command.CommandTimeout = (int)(commandTimeout ?? DefaultCommandTimeout).TotalSeconds;
                command.CommandText    = statement;
                command.Transaction    = transaction;
                args?.ContributeTo(command, typeHandlers, mapping);
                return(command);
            }
            catch
            {
                command.Dispose();
                throw;
            }
        }