/// <summary>
/// Format the SQL command with parameters as variables.
/// </summary>
/// <param name="command">Command to format</param>
/// <returns></returns>
protected virtual string GetQueryFormattedAsVariables(DatabaseCommand command)
{
string commentTags = command.GetTagsAsSqlComments();
string commandText = command.CommandText.Value;
// Sort by ParameterName DESC to replace @abcdef before @abc.
var declarations = new StringBuilder();
Convertor.DbTypeMap.Initialize(command.GetInternalCommand().Connection);
foreach (DbParameter param in command.Parameters.Cast <DbParameter>().OrderByDescending(i => i.ParameterName))
{
string paramName = param.ParameterName;
string prefix = Schema.DataParameter.GetPrefixParameter(command.GetInternalCommand());
string sqlType = Convertor.DbTypeMap.IsStringRepresentation(param.DbType)
? $"VARCHAR({(param.Size > 0 ? param.Size : 4000)})"
: Convertor.DbTypeMap.FirstMappedType(param.DbType).SqlTypeName;
if (!paramName.StartsWith(prefix))
{
paramName = prefix + paramName;
}
declarations.AppendLine($"DECLARE {paramName} AS {sqlType.ToUpper()} = {GetValueFormatted(param)}");
}
if (declarations.Length > 0)
{
declarations.AppendLine();
}
return($"{commentTags}{declarations}{commandText}");
}
/// <summary>
/// Returns the CommandText with all parameter values included.
/// </summary>
/// <param name="command">DBCommand to manage</param>
/// <returns></returns>
protected virtual string GetQueryFormattedAsText(DatabaseCommand command)
{
string commandText = command.GetCommandTextWithTags();
// Sort by ParameterName DESC to replace @abcdef before @abc.
foreach (DbParameter param in command.Parameters.Cast <DbParameter>().OrderByDescending(i => i.ParameterName))
{
string paramName = param.ParameterName;
string prefix = Schema.DataParameter.GetPrefixParameter(command.GetInternalCommand());
if (!paramName.StartsWith(prefix))
{
paramName = prefix + paramName;
}
commandText = Regex.Replace(commandText, paramName, GetValueFormatted(param), RegexOptions.IgnoreCase);
}
return(commandText);
}