public static string PaginateQuery(string innerSql, int skip, int take, CommandParameters parameters, string tableName, string orderBy, string innerColumnSelector = "[Id]") { parameters.Add("_minrow", skip + 1); parameters.Add("_maxrow", take + skip); return($@"SELECT * FROM dbo.[{tableName}] WHERE {innerColumnSelector} IN ( SELECT {innerColumnSelector} FROM ( SELECT RESULT.{innerColumnSelector}, Row_Number() over (ORDER BY {orderBy}) as RowNum FROM ({innerSql}) RESULT ) RS WHERE RowNum >= @_minrow And RowNum <= @_maxrow )"); }
public IEnumerable <T> ExecuteReaderWithProjection <T>(string query, CommandParameters 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; } } }
public T ExecuteScalar <T>(string query, CommandParameters 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; } } }
public void ExecuteNonQuery(string query, CommandParameters 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 { command.ExecuteNonQueryWithRetry(GetRetryPolicy(RetriableOperation.Select)); } catch (SqlException ex) { throw WrapException(command, ex); } catch (Exception ex) { Log.DebugException($"Exception in relational transaction '{name}'", ex); throw; } } }
public void ExecuteRawDeleteQuery(string query, CommandParameters 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; } } }
public void ExecuteReader(string query, CommandParameters 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; } } }
public IDbCommand CreateCommand(IDbConnection connection, IDbTransaction transaction, string statement, CommandParameters 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; } }
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 CommandParameters(); 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); } } }
public CommandParameters(CommandParameters from) : base(from, StringComparer.OrdinalIgnoreCase) { CommandType = from.CommandType; }