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