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