internal static void AppendTestValue(StringBuilder builder, ParameterArgument argument)
{
// table-valued test argument
if (argument.TestValue is View)
{
return;
}
// scalar test argument (is always a string)
string value = (string)argument.TestValue;
if (value.EqualsCS(Text.Null, false))
{
builder.Append(Text.Null);
}
else
{
// if CLR type is not given => original value has been passed as d.Null
if (argument.ArgType == null)
{
if (argument.DT.IsDefined())
{
builder.Append(value);
}
else
{
builder.Append(Mapping.BuildUnchecked(argument.Value));
}
}
else
{
builder.Append(value);
}
}
}
private void ClearParameters()
{
var i = 0;
foreach (var param in _connectable.Executable.Compilable.GetRoot().ExplicitParams)
{
var argument = _connectable.Executable.Arguments.Where(a => a.ParamName == param.Name).FirstOrDefault();
if (argument == null)
{
continue;
}
string defaultValue = Wall.Text.Null;
if (param.IsOptional && param.Default.Value != null)
{
defaultValue = Filter.TrimSingleQuotes(Mapping.BuildUnchecked(param.Default.Value));
}
if (param.IsDbType() || param.DT == DT.Udt)
{
_parameters.Rows[i].Cells[2].Value = defaultValue;
}
++i;
}
}
private static void AppendScalarColumn(StringBuilder sql, object value, Type clrType)
{
AppendColumn(sql,
Text.GenerateSql(100)
.Append(Text.Cast)
.Append(Text.LeftBracket)
.Append(Mapping.BuildUnchecked(value ?? Designer.Null))
.Append(Text._As_)
.Append(Mapping.ClrMapping[clrType].DefaultDataType.Build())
.Append(Text.RightBracket)
.ToString(),
Text.SingleColumnName);
}
private static void AppendColumnValue(StringBuilder sql, object value, int index, bool isSqlVariantFirstRow = false)
{
if (isSqlVariantFirstRow)
{
AppendColumnAsSqlVariant(sql,
Mapping.BuildUnchecked(value),
String.Format("{0}{1}", Text.ColumnShortName, index));
}
else
{
AppendColumn(sql,
Mapping.BuildUnchecked(value),
String.Format("{0}{1}", Text.ColumnShortName, index));
}
}
internal string BuildTop(BuildContext buildContext)
{
if (_top == null && _topPercent == null && _topVariable == null)
{
return(null);
}
buildContext.TryAddParamToConcatRoot(_topVariable);
StringBuilder sql = new StringBuilder(20)
.Append(Text.Top)
.Append(Text.LeftBracket);
if (_topVariable != null)
{
sql.Append(_topVariable.Name)
.Append(Text.RightBracket);
if (!_topVariable.DT.IsInteger())
{
sql.S().Append(Text.Percent);
}
}
else
{
if (_top != null)
{
sql.Append(_top)
.Append(Text.RightBracket);
}
else
{
sql.Append(Mapping.BuildUnchecked(_topPercent))
.Append(Text.RightBracket).S()
.Append(Text.Percent);
}
}
if (_isWithTies)
{
sql.S().Append(Text.WithTies);
}
return(sql.S().ToString());
}
private static string GetParamDisplayValue(ParameterArgument argument)
{
if (argument.DT.IsDataType())
{
if (argument.DT == DT.Datetime2)
{
return(Filter.TrimSingleQuotes(Mapping.BuildUnchecked((DateTime)argument.Value)));
}
else
{
return(Filter.TrimSingleQuotes(Mapping.BuildUnchecked(argument.Value)));
}
}
else if (argument.DT.IsNameType() || argument.DT.IsTable())
{
if (argument.IsBulk)
{
return(Wall.Text.NotAvailable);
}
else if (argument.DT.IsTable())
{
return(Wall.Text.ThreeDots);
}
else
{
return(argument.Value.ToString());
}
}
else if (argument.DT.IsInlinerOrConcatenator())
{
return(argument.Value.ToString());
}
else
{
return(Wall.Text.NotAvailable);
}
}
private string BuildConcatParams()
{
StringBuilder declaration = Text.GenerateSql(100);
StringBuilder assignment = Text.GenerateSql(100);
bool first = true;
ConcatRoot.AllParams
.Where(param => !param.DT.IsVTB()).ToList() // exclude table variables, temp tables and bulk tables
.ForEach(param =>
{
assignment.NewLine(Text.Comma);
if (!first)
{
declaration.NewLine(Text.Comma);
}
string declare;
string assign;
// parameterized value:
if (param.ParameterizedValue != null)
{
declare = Executable.GetParamDeclaration(param.ParameterizedValue, true, false);
var value = param.ParameterizedValue;
if (value.DataType != null)
{
assign = Mapping.Build(value.Value, value.DataType);
}
else
{
assign = Mapping.BuildUnchecked(value.Value);
}
}
// SQL variable:
else
{
declare = param.BuildDeclaration();
assign = param.Name; // assign the same outer variable
}
declaration
.Append(param.Name)
.Append(Text._As_)
.Append(declare);
assignment.Append(param.Name)
.Append(Text.Equal)
.Append(assign);
first = false;
});
return(Text.GenerateSql(200)
.NewLine(Text.Free.CommaNSingleQuote)
.Append(declaration)
.Append(Text.SingleQuote)
.Append(assignment)
.ToString());
}
private string BuildStoredProcArguments()
{
StringBuilder argumentBuilder = new StringBuilder();
int i = 0;
var root = _compilable.GetRoot();
root.AllParams
.ForEach(param =>
{
if (i++ == 0)
{
argumentBuilder.Append(Text.OneSpace);
}
else
{
argumentBuilder.Append(Text.Comma);
}
if (Admin.IsValueParameterizationOn)
{
argumentBuilder.Append(param.Name);
}
else // (unreachable code)
{
#pragma warning disable CS0162 // Unreachable code detected
if (!_inner)
#pragma warning restore CS0162 // Unreachable code detected
{
argumentBuilder.Append(param.Name);
}
else
{
object directArgument = Arguments
.Where(a => a.ParamName == param.Name)
.Select(a => a.Value)
.FirstOrDefault();
Type directArgumentType = directArgument == null ? null : directArgument.GetType();
if (Variable.Detect(directArgument))
{
argumentBuilder.Append((string)directArgument);
}
else
{
if (directArgument != null &&
(directArgumentType == typeof(View) || (directArgumentType == typeof(DataTable))))
{
argumentBuilder.Append(param.Name);
}
else
{
argumentBuilder.Append(Mapping.BuildUnchecked(directArgument));
}
}
}
}
if (param.IsOutput)
{
argumentBuilder.S().Append(Text.Output);
}
});
argumentBuilder.Terminate();
if (chainException != null)
{
TryThrow(Text.Method.Pass);
}
return(argumentBuilder.ToString());
}
internal void BuildParamAppendix(BuildArgs buildArgs)
{
_paramDeclaration = new StringBuilder();
_paramAssignment = new StringBuilder();
if (Admin.IsValueParameterizationOn && !buildArgs.IsTesting)
{
_rootCompilable.ImplicitParams.ForEach(param =>
{
_arguments.Add(param.ParameterizedValue);
});
}
// return value
_paramDeclaration.Append(Text.Reserved.ReturnValueInnerParam).S()
.Append(Text.Free.EnclosedInt).S().Append(Text.Output);
_paramAssignment.Append(Text.Reserved.ReturnValueInnerParam)
.Append(Text._Equal_)
.Append(Text.Reserved.ReturnValueOuterParam).S()
.Append(Text.Output);
// auto add arguments of optional params (if not passed)
_rootCompilable.ExplicitParams.Where(param => param.IsOptional).ToList()
.ForEach(param =>
{
if (!CheckArgumentByName(param.Name))
{
_arguments.Add(param.Default);
}
});
// exclude inline params, table variable, temp tables and bulk tables
bool paramSeparator = false;
_arguments.Where(argument => argument.DT.IsNotInliner() &&
!argument.DT.IsVTB())
.ToList()
.ForEach(argument =>
{
// param declaration
_paramDeclaration.NewLine(Text.Comma);
_paramDeclaration
.Append(argument.ParamName)
.Append(Text._As_)
.Append(GetParamDeclaration(argument, true, false));
string declarationOutput = String.Empty;
if (argument.IsParamOutput)
{
declarationOutput = Text.OneSpace + Text.Output;
}
_paramDeclaration.Append(declarationOutput);
// marks the beginning of client params
if (!paramSeparator && argument.ParamName != Text.Reserved.ReturnValueInnerParam)
{
_paramAssignment.NewLine()
.NewLine(Text.Free.Params);
paramSeparator = true;
}
_paramAssignment.NewLine(Text.Comma);
// argument is a variable:
// note:
// variables can only be passed in the inner call, otherwise they are treated as string values
if (argument.IsPassedVariable && _inner)
{
_paramAssignment.Append(argument.ParamName)
.Append(Text.Equal)
.Append(argument.Value);
}
// argument is a value:
else
{
_paramAssignment.Append(argument.ParamName)
.Append(Text.Equal);
if (argument.Original is View)
{
// note: inner table param has the same name as outer table param
_paramAssignment.Append(argument.ParamName);
}
else if (argument.IsOutput)
{
_paramAssignment.Append(argument.ParamName)
.Append(Text.Underscore)
.Append(Text.Output);
}
else
{
// the test value has the advantage over the regular value
if (argument.TestValue != null)
{
Testing.AppendTestValue(_paramAssignment, argument);
}
else
{
if (argument.DataType != null)
{
_paramAssignment.Append(Mapping.Build(argument.Value, argument.DataType));
}
else
{
_paramAssignment.Append(Mapping.BuildUnchecked(argument.Value));
}
}
}
}
string assignmentOutput = String.Empty;
if (argument.IsOutput)
{
assignmentOutput = Text.OneSpace + Text.Output;
}
_paramAssignment.Append(assignmentOutput);
});
if (chainException != null)
{
TryThrow(Text.Method.Pass);
}
}
internal static View ConvertCollection <T>(IEnumerable data)
{
var ctype = typeof(T);
var type = Common.TryGetSerializationItemType(ctype, data, Text.Method.ToView);
if (type == null)
{
throw new QueryTalkException("Common.TryGetSerializationItemType",
QueryTalkExceptionType.EmptyDynamicResult, String.Format("type = {0}", ctype), Text.Method.ToView);
}
Type clrType = null;
QueryTalkException exception;
List <ViewColumnInfo> columns = new List <ViewColumnInfo>();
bool isScalar = (Mapping.CheckClrCompliance(type, out clrType, out exception) == Mapping.ClrTypeMatch.ClrMatch);
bool isDataValue = type == typeof(Value);
if (isDataValue)
{
isScalar = true;
clrType = typeof(System.Object);
}
bool isEmpty = _CheckIfEmpty(data, isScalar);
var properties = type.GetReadableProperties();
List <IPropertyAccessor> getters = null;
int numberOfProperties = 0;
var sqlOuter = Text.GenerateSql(500);
var sqlEmpty = Text.GenerateSql(200)
.NewLineIndent(Text.Select).S();
// outer select:
if (isScalar)
{
_BuildScalarOuterSelect(type, clrType, columns, sqlOuter);
}
else
{
_BuildClassOuterSelect(type, ref clrType, ref exception, columns, isEmpty, properties, out getters,
out numberOfProperties, sqlOuter, sqlEmpty);
}
// inner select:
var sqlInner = Text.GenerateSql(500);
int rowCount = 0;
if (!isEmpty)
{
bool firstRow = true;
foreach (var row in data)
{
if (!isScalar && row == null)
{
continue;
}
_BuildSelect(sqlInner, firstRow);
if (!isScalar)
{
_BuildClassValues(columns, properties, getters, numberOfProperties, sqlInner, firstRow, row);
}
else
{
AppendColumn(sqlInner, Mapping.BuildUnchecked(row), Text.SingleColumnShortName);
}
firstRow = false;
++rowCount;
}
}
else
{
if (isScalar)
{
AppendColumn(sqlEmpty, Text.Null, Text.SingleColumnShortName);
}
sqlInner.Append(sqlEmpty.ToString());
}
return(Finalizer(type, sqlOuter, sqlInner, columns.ToArray(), rowCount, isEmpty));
}
// build the most outer SQL wrapper
internal string BuildOutputWrapper(string execSql)
{
var root = Executable.Compilable.GetRoot();
var outputArguments = ParameterArgument.GetOutputArguments(Executable.Arguments);
var sql = Text.GenerateSql(1000).Append(Text.Free.QueryTalkCode);
// after: drop temp tables
var sqlAfter = Text.GenerateSql(100);
sqlAfter.Append(DropTempTables());
if (root.IsEmbeddedTryCatch)
{
sqlAfter
.NewLine(Text.EndTry)
.NewLine(Text.BeginCatch)
.NewLine(Text.Free.RaiserrorS)
.NewLine(Text.EndCatch).Terminate();
}
sql.Append(Text.Declare).S().Append(Text.Reserved.ReturnValueOuterParam)
.Append(Text._As_).Append(Text.Free.EnclosedInt).Terminate().S()
.Append(Text.Set).S().Append(Text.Reserved.ReturnValueOuterParam).Append(Text._Equal_)
.Append(Text.Zero).Terminate();
// TRY outer wrapper
if (root.IsEmbeddedTryCatch)
{
sql.NewLine(Text.BeginTry);
}
// output arguments
string outputValues = String.Empty;
foreach (var argument in outputArguments)
{
// param in outer wrapper that holds the outer reference
string paramOuterName = String.Format("{0}{1}{2}", argument.ParamName, Text.Underscore, Text.Output);
// before
sql.NewLine(Text.Declare).S()
.Append(paramOuterName).Append(Text._As_)
.Append(Executable.GetParamDeclaration(argument, false, true))
.Terminate().S()
.Append(Text.Set).S().Append(paramOuterName).Append(Text._Equal_);
if (argument.TestValue != null)
{
Testing.AppendTestValue(sql, argument);
}
else
{
sql.Append(Mapping.BuildUnchecked(argument.Value));
}
sql.TerminateSingle();
// after: return output values
outputValues = Text.GenerateSql(100)
.NewLineIndent(Text.Comma)
.Append(paramOuterName)
.Append(Text._As_)
.Append(Filter.Delimit(paramOuterName))
.ToString();
}
// append last sql code: return value + output values
sqlAfter
.NewLine(Text.Select).S()
.Append(Text.Free.ReturnValue)
.Append(Text._As_)
.Append(Text.Reserved.ReturnValueColumnName)
.Append(outputValues);
TryThrow(Text.Method.Pass);
sql.NewLine(execSql)
.Append(sqlAfter.ToString())
.TerminateSingle();
return(sql.ToString());
}
