/// <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>
/// Format the SQL command in HTML (coloring, ...)
/// </summary>
/// <param name="command">Command to format in HTML</param>
/// <returns></returns>
protected virtual string GetQueryFormattedAsHtml(DatabaseCommand command)
{
string output = this.GetQueryFormattedAsText(command);
// Transform special characters
output = output.Replace("&", "&");
output = output.Replace("\"", """);
output = output.Replace("<", "<");
output = output.Replace(">", ">");
output = output.Replace(Environment.NewLine, "<br/>");
// Comments clorized
output = Regex.Replace(output,
@"^--(?<comment>[^\r\n]*)(?<post>\r\n|$)",
@"<span style=""color: Olive; font-weight: bold;"">--${comment}</span>${post}",
RegexOptions.IgnoreCase | RegexOptions.Multiline
);
// Keywords clorized
output = Regex.Replace(output,
@"(?<=(\[|\b))(?<keyword>(SELECT|FROM|WHERE|ORDER|INNER|JOIN|OUTER|LEFT|RIGHT|CROSS" +
@"|DISTINCT|DECLARE|SET|EXEC|NOT|IN|IS|NULL|BETWEEN|GROUP|BY|ASC|DESC|OVER|AS|ON" +
@"|AND|OR|TOP|GO|CASE|WHEN|ELSE|THEN|IF|BEGIN|END|LIKE))\b",
@"<span style=""color: #33f; font-weight: bold;"">${keyword}</span>",
RegexOptions.IgnoreCase
);
// Other keywords clorized
output = Regex.Replace(output,
@"(\b(?<keyword>ROW_NUMBER|COUNT|CONVERT|COALESCE|CAST)(?<post>\())",
@"<span style=""color: #3f6; font-weight: bold;"">${keyword}</span>${post}",
RegexOptions.IgnoreCase
);
// Parameters (@xxx)
output = Regex.Replace(output,
@"(?<param>\@[\w\d_]+)",
@"<span style=""color: #993; font-weight: bold;"">${param}</span>",
RegexOptions.IgnoreCase
);
// Numerics
output = Regex.Replace(output,
@"(?<arg>(\b|-)[\d.]+\b)",
@"<span style=""color: #FF3F00;"">${arg}</span>",
RegexOptions.IgnoreCase
);
// Strings, Dates
output = Regex.Replace(output,
@"(?<arg>'([^']|'')*')",
@"<span style=""color: #FF3F00;"">${arg}</span>",
RegexOptions.IgnoreCase
);
return(output);
}
/// <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);
}
/// <summary>
/// Creates a new instance of FluentQuery
/// </summary>
/// <param name="databaseCommand"></param>
protected internal FluentQuery(DatabaseCommand databaseCommand)
{
_databaseCommand = databaseCommand;
}
/// <summary>
/// Initializes a new instance of CommandTextFormatted
/// </summary>
/// <param name="command"></param>
internal CommandTextFormatted(DatabaseCommand command)
{
_dbCommand = command;
}