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);
}
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);
}
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());
}
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));
}
}
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;
}
}
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;
}
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;
}
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;
}
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;
}
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;
}
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;
}
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;
}
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;
}
}
}
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;
}
}
}
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;
}
}
}
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;
}
}
}
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;
}
}
}
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;
}
}
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);
}
}
}
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)));
}
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)));
}
public abstract IJoinSourceQueryBuilder <TRecord> Join(IAliasedSelectSource source, JoinType joinType, CommandParameterValues parameterValues, Parameters parameters, ParameterDefaults parameterDefaults);
public CommandParameterValues(CommandParameterValues from)
: base(from, StringComparer.OrdinalIgnoreCase)
{
CommandType = from.CommandType;
}
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;
}
}