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;
}