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