public virtual IEnumerable <CommandPart> CreatePersistParts(SqlPersistTask task, string parameterNamePrefix)
{
ArgumentValidator.EnsureArgumentNotNull(task, "task");
ArgumentValidator.EnsureArgumentNotNullOrEmpty(parameterNamePrefix, "parameterNamePrefix");
var result = new List <CommandPart>();
int parameterIndex = 0;
foreach (var request in task.RequestSequence)
{
var tuple = task.Tuple;
var compilationResult = request.GetCompiledStatement();
var configuration = new SqlPostCompilerConfiguration();
var part = new CommandPart();
foreach (var binding in request.ParameterBindings)
{
var parameterValue = GetParameterValue(task, binding);
if (binding.BindingType == PersistParameterBindingType.VersionFilter && IsHandledLikeNull(parameterValue))
{
configuration.AlternativeBranches.Add(binding);
}
else
{
var parameterName = GetParameterName(parameterNamePrefix, ref parameterIndex);
configuration.PlaceholderValues.Add(binding, Driver.BuildParameterReference(parameterName));
AddParameter(part, binding, parameterName, parameterValue);
}
}
part.Statement = compilationResult.GetCommandText(configuration);
result.Add(part);
}
return(result);
}
public void PlaceholderTest()
{
var placeholderId = new object();
var select = SqlDml.Select(SqlDml.Placeholder(placeholderId));
var result = sqlConnection.Driver.Compile(select);
using (var command = sqlConnection.CreateCommand()) {
var configuration = new SqlPostCompilerConfiguration {
PlaceholderValues = { { placeholderId, "@xxx" } }
};
command.CommandText = result.GetCommandText(configuration);
var parameter = command.CreateParameter();
parameter.ParameterName = "xxx";
parameter.DbType = DbType.Int32;
parameter.Value = (int)'x';
command.Parameters.Add(parameter);
Assert.AreEqual((int)'x', command.ExecuteScalar());
}
using (var command = sqlConnection.CreateCommand()) {
var configuration = new SqlPostCompilerConfiguration {
PlaceholderValues = { { placeholderId, "@yyy" } }
};
command.CommandText = result.GetCommandText(configuration);
var parameter = command.CreateParameter();
parameter.ParameterName = "yyy";
parameter.DbType = DbType.Int32;
parameter.Value = (int)'y';
command.Parameters.Add(parameter);
Assert.AreEqual((int)'y', command.ExecuteScalar());
}
}
public void MultiColumnDynamicFilterTest()
{
var dynamicFilterId = new object();
var tableRef = SqlDml.TableRef(Catalog.Schemas["Person"].Tables["Contact"]);
var select = SqlDml.Select(tableRef);
select.Columns.Add(SqlDml.Count());
var filter = SqlDml.DynamicFilter(dynamicFilterId);
select.Where = filter;
filter.Expressions.Add(tableRef["FirstName"]);
filter.Expressions.Add(tableRef["LastName"]);
var result = sqlConnection.Driver.Compile(select);
using (var command = sqlConnection.CreateCommand()) {
var values = new List <string[]> {
new[] { "'Gustavo'", "'Achong'" }, new[] { "'Catherine'", "'Abel'" }
};
var configuration = new SqlPostCompilerConfiguration {
DynamicFilterValues = { { dynamicFilterId, values } }
};
command.CommandText = result.GetCommandText(configuration);
int count = Convert.ToInt32(command.ExecuteScalar());
Assert.AreEqual(2, count);
}
}
public void VariantTest()
{
var variantId = new object();
var select = SqlDml.Select(SqlDml.TableRef(Catalog.Schemas["Person"].Tables["Contact"]));
select.Limit = 1;
select.Columns.Add(SqlDml.Variant(variantId, 1, 2), "value");
var result = sqlConnection.Driver.Compile(select);
using (var command = sqlConnection.CreateCommand()) {
command.CommandText = result.GetCommandText();
using (var reader = command.ExecuteReader()) {
Assert.IsTrue(reader.Read());
Assert.AreEqual(1, reader.GetInt32(0));
}
var configuration = new SqlPostCompilerConfiguration {
AlternativeBranches = { variantId }
};
command.CommandText = result.GetCommandText(configuration);
using (var reader = command.ExecuteReader()) {
Assert.IsTrue(reader.Read());
Assert.AreEqual(2, reader.GetInt32(0));
}
}
}
public virtual CommandPart CreateQueryPart(IQueryRequest request, string parameterNamePrefix, ParameterContext parameterContext)
{
ArgumentValidator.EnsureArgumentNotNull(request, "request");
ArgumentValidator.EnsureArgumentNotNullOrEmpty(parameterNamePrefix, "parameterNamePrefix");
int parameterIndex = 0;
var compilationResult = request.GetCompiledStatement();
var configuration = new SqlPostCompilerConfiguration();
var result = new CommandPart();
using (parameterContext.ActivateSafely()) {
foreach (var binding in request.ParameterBindings)
{
object parameterValue = GetParameterValue(binding);
switch (binding.BindingType)
{
case QueryParameterBindingType.Regular:
break;
case QueryParameterBindingType.SmartNull:
// replacing "x = @p" with "x is null" when @p = null (or empty string in case of Oracle)
if (IsHandledLikeNull(parameterValue))
{
configuration.AlternativeBranches.Add(binding);
continue;
}
break;
case QueryParameterBindingType.BooleanConstant:
// expanding true/false parameters to constants to help query optimizer with branching
if ((bool)parameterValue)
{
configuration.AlternativeBranches.Add(binding);
}
continue;
case QueryParameterBindingType.LimitOffset:
// not parameter, just inlined constant
configuration.PlaceholderValues.Add(binding, parameterValue.ToString());
continue;
case QueryParameterBindingType.NonZeroLimitOffset:
// Like "LimitOffset" but we handle zero value specially
// We replace value with 1 and activate special branch that evaluates "where" part to "false"
var stringValue = parameterValue.ToString();
if (stringValue == "0")
{
configuration.PlaceholderValues.Add(binding, "1");
configuration.AlternativeBranches.Add(binding);
}
else
{
configuration.PlaceholderValues.Add(binding, stringValue);
}
continue;
case QueryParameterBindingType.RowFilter:
var filterData = (List <Tuple>)parameterValue;
var rowTypeMapping = ((QueryRowFilterParameterBinding)binding).RowTypeMapping;
if (filterData == null)
{
configuration.AlternativeBranches.Add(binding);
continue;
}
var commonPrefix = GetParameterName(parameterNamePrefix, ref parameterIndex);
var filterValues = new List <string[]>();
for (int tupleIndex = 0; tupleIndex < filterData.Count; tupleIndex++)
{
var tuple = filterData[tupleIndex];
var parameterReferences = new string[tuple.Count];
for (int fieldIndex = 0; fieldIndex < tuple.Count; fieldIndex++)
{
var name = string.Format(RowFilterParameterNameFormat, commonPrefix, tupleIndex, fieldIndex);
var value = tuple.GetValueOrDefault(fieldIndex);
parameterReferences[fieldIndex] = Driver.BuildParameterReference(name);
AddRegularParameter(result, rowTypeMapping[fieldIndex], name, value);
}
filterValues.Add(parameterReferences);
}
configuration.DynamicFilterValues.Add(binding, filterValues);
continue;
default:
throw new ArgumentOutOfRangeException("binding.BindingType");
}
// regular case -> just adding the parameter
string parameterName = GetParameterName(parameterNamePrefix, ref parameterIndex);
configuration.PlaceholderValues.Add(binding, Driver.BuildParameterReference(parameterName));
AddParameter(result, binding, parameterName, parameterValue);
}
}
result.Statement = compilationResult.GetCommandText(configuration);
return(result);
}
