我有以下代码:
Using cmd As SqlCommand = Connection.CreateCommand
cmd.CommandText = "UPDATE someTable SET Value = @Value"
cmd.CommandText &= " WHERE Id = @Id"
cmd.Parameters.AddWithValue("@Id", 1234)
cmd.Parameters.AddWithValue("@Value", "myValue")
cmd.ExecuteNonQuery
End Using
我想知道是否有任何方法来获得最终的SQL语句作为字符串，它应该看起来像这样:
UPDATE someTable SET Value = "myValue" WHERE Id = 1234
如果有人想知道我为什么要这么做
用于记录(失败的)语句 可以将其复制粘贴到企业管理器中进行测试
当前回答
如果你正在使用SQL Server，你可以使用SQL Server Profiler(如果你有的话)来查看实际执行的命令字符串。这对于复制/粘贴测试很有用，但对于日志记录恐怕就没用了。
其他回答
我需要一个类似于字符串转换器的命令来允许更详细的日志记录，所以我写了这个命令。它将生成在新会话中重新执行命令所需的文本，包括输出参数和结构化参数。它经过了轻微的检验，但买者自负。
例子:
SqlCommand cmd = new SqlCommand("GetEntity", con);
cmd.Parameters.AddWithValue("@foobar", 1);
cmd.Parameters.Add(new SqlParameter(){
ParameterName = "@outParam",
Direction = ParameterDirection.Output,
SqlDbType = System.Data.SqlDbType.Int
});
cmd.Parameters.Add(new SqlParameter(){
Direction = ParameterDirection.ReturnValue
});
cmd.CommandType = CommandType.StoredProcedure;
会产生:
-- BEGIN COMMAND
DECLARE @foobar INT = 1;
DECLARE @outParam INT = NULL;
DECLARE @returnValue INT;
-- END PARAMS
EXEC @returnValue = GetEntity @foobar = @foobar, @outParam = @outParam OUTPUT
-- RESULTS
SELECT 1 as Executed, @returnValue as ReturnValue, @outParam as [@outParam];
-- END COMMAND
实现:
public class SqlCommandDumper
{
public static string GetCommandText(SqlCommand sqc)
{
StringBuilder sbCommandText = new StringBuilder();
sbCommandText.AppendLine("-- BEGIN COMMAND");
// params
for (int i = 0; i < sqc.Parameters.Count; i++)
logParameterToSqlBatch(sqc.Parameters[i], sbCommandText);
sbCommandText.AppendLine("-- END PARAMS");
// command
if (sqc.CommandType == CommandType.StoredProcedure)
{
sbCommandText.Append("EXEC ");
bool hasReturnValue = false;
for (int i = 0; i < sqc.Parameters.Count; i++)
{
if (sqc.Parameters[i].Direction == ParameterDirection.ReturnValue)
hasReturnValue = true;
}
if (hasReturnValue)
{
sbCommandText.Append("@returnValue = ");
}
sbCommandText.Append(sqc.CommandText);
bool hasPrev = false;
for (int i = 0; i < sqc.Parameters.Count; i++)
{
var cParam = sqc.Parameters[i];
if (cParam.Direction != ParameterDirection.ReturnValue)
{
if (hasPrev)
sbCommandText.Append(",");
sbCommandText.Append(" ");
sbCommandText.Append(cParam.ParameterName);
sbCommandText.Append(" = ");
sbCommandText.Append(cParam.ParameterName);
if (cParam.Direction.HasFlag(ParameterDirection.Output))
sbCommandText.Append(" OUTPUT");
hasPrev = true;
}
}
}
else
{
sbCommandText.AppendLine(sqc.CommandText);
}
sbCommandText.AppendLine("-- RESULTS");
sbCommandText.Append("SELECT 1 as Executed");
for (int i = 0; i < sqc.Parameters.Count; i++)
{
var cParam = sqc.Parameters[i];
if (cParam.Direction == ParameterDirection.ReturnValue)
{
sbCommandText.Append(", @returnValue as ReturnValue");
}
else if (cParam.Direction.HasFlag(ParameterDirection.Output))
{
sbCommandText.Append(", ");
sbCommandText.Append(cParam.ParameterName);
sbCommandText.Append(" as [");
sbCommandText.Append(cParam.ParameterName);
sbCommandText.Append(']');
}
}
sbCommandText.AppendLine(";");
sbCommandText.AppendLine("-- END COMMAND");
return sbCommandText.ToString();
}
private static void logParameterToSqlBatch(SqlParameter param, StringBuilder sbCommandText)
{
sbCommandText.Append("DECLARE ");
if (param.Direction == ParameterDirection.ReturnValue)
{
sbCommandText.AppendLine("@returnValue INT;");
}
else
{
sbCommandText.Append(param.ParameterName);
sbCommandText.Append(' ');
if (param.SqlDbType != SqlDbType.Structured)
{
logParameterType(param, sbCommandText);
sbCommandText.Append(" = ");
logQuotedParameterValue(param.Value, sbCommandText);
sbCommandText.AppendLine(";");
}
else
{
logStructuredParameter(param, sbCommandText);
}
}
}
private static void logStructuredParameter(SqlParameter param, StringBuilder sbCommandText)
{
sbCommandText.AppendLine(" {List Type};");
var dataTable = (DataTable)param.Value;
for (int rowNo = 0; rowNo < dataTable.Rows.Count; rowNo++)
{
sbCommandText.Append("INSERT INTO ");
sbCommandText.Append(param.ParameterName);
sbCommandText.Append(" VALUES (");
bool hasPrev = false;
for (int colNo = 0; colNo < dataTable.Columns.Count; colNo++)
{
if (hasPrev)
{
sbCommandText.Append(", ");
}
logQuotedParameterValue(dataTable.Rows[rowNo].ItemArray[colNo], sbCommandText);
hasPrev = true;
}
sbCommandText.AppendLine(");");
}
}
const string DATETIME_FORMAT_ROUNDTRIP = "o";
private static void logQuotedParameterValue(object value, StringBuilder sbCommandText)
{
try
{
if (value == null)
{
sbCommandText.Append("NULL");
}
else
{
value = unboxNullable(value);
if (value is string
|| value is char
|| value is char[]
|| value is System.Xml.Linq.XElement
|| value is System.Xml.Linq.XDocument)
{
sbCommandText.Append("N'");
sbCommandText.Append(value.ToString().Replace("'", "''"));
sbCommandText.Append('\'');
}
else if (value is bool)
{
// True -> 1, False -> 0
sbCommandText.Append(Convert.ToInt32(value));
}
else if (value is sbyte
|| value is byte
|| value is short
|| value is ushort
|| value is int
|| value is uint
|| value is long
|| value is ulong
|| value is float
|| value is double
|| value is decimal)
{
sbCommandText.Append(value.ToString());
}
else if (value is DateTime)
{
// SQL Server only supports ISO8601 with 3 digit precision on datetime,
// datetime2 (>= SQL Server 2008) parses the .net format, and will
// implicitly cast down to datetime.
// Alternatively, use the format string "yyyy'-'MM'-'dd'T'HH':'mm':'ss'.'fffK"
// to match SQL server parsing
sbCommandText.Append("CAST('");
sbCommandText.Append(((DateTime)value).ToString(DATETIME_FORMAT_ROUNDTRIP));
sbCommandText.Append("' as datetime2)");
}
else if (value is DateTimeOffset)
{
sbCommandText.Append('\'');
sbCommandText.Append(((DateTimeOffset)value).ToString(DATETIME_FORMAT_ROUNDTRIP));
sbCommandText.Append('\'');
}
else if (value is Guid)
{
sbCommandText.Append('\'');
sbCommandText.Append(((Guid)value).ToString());
sbCommandText.Append('\'');
}
else if (value is byte[])
{
var data = (byte[])value;
if (data.Length == 0)
{
sbCommandText.Append("NULL");
}
else
{
sbCommandText.Append("0x");
for (int i = 0; i < data.Length; i++)
{
sbCommandText.Append(data[i].ToString("x"));
}
}
}
else
{
sbCommandText.Append("/* UNKNOWN DATATYPE: ");
sbCommandText.Append(value.GetType().ToString());
sbCommandText.Append(" *" + "/ N'");
sbCommandText.Append(value.ToString());
sbCommandText.Append('\'');
}
}
}
catch (Exception ex)
{
sbCommandText.AppendLine("/* Exception occurred while converting parameter: ");
sbCommandText.AppendLine(ex.ToString());
sbCommandText.AppendLine("*/");
}
}
private static object unboxNullable(object value)
{
var typeOriginal = value.GetType();
if (typeOriginal.IsGenericType
&& typeOriginal.GetGenericTypeDefinition() == typeof(Nullable<>))
{
// generic value, unboxing needed
return typeOriginal.InvokeMember("GetValueOrDefault",
System.Reflection.BindingFlags.Public |
System.Reflection.BindingFlags.Instance |
System.Reflection.BindingFlags.InvokeMethod,
null, value, null);
}
else
{
return value;
}
}
private static void logParameterType(SqlParameter param, StringBuilder sbCommandText)
{
switch (param.SqlDbType)
{
// variable length
case SqlDbType.Char:
case SqlDbType.NChar:
case SqlDbType.Binary:
{
sbCommandText.Append(param.SqlDbType.ToString().ToUpper());
sbCommandText.Append('(');
sbCommandText.Append(param.Size);
sbCommandText.Append(')');
}
break;
case SqlDbType.VarBinary:
case SqlDbType.Image:
{
sbCommandText.Append("VARBINARY");
sbCommandText.Append("(MAX /* Specified as ");
sbCommandText.Append(param.Size);
sbCommandText.Append(" */)");
}
break;
case SqlDbType.VarChar:
case SqlDbType.Text:
{
sbCommandText.Append("VARCHAR");
sbCommandText.Append("(MAX /* Specified as ");
sbCommandText.Append(param.Size);
sbCommandText.Append(" */)");
}
break;
case SqlDbType.NVarChar:
case SqlDbType.NText:
{
sbCommandText.Append("NVARCHAR");
sbCommandText.Append("(MAX /* Specified as ");
sbCommandText.Append(param.Size);
sbCommandText.Append(" */)");
}
break;
// fixed length
case SqlDbType.Bit:
case SqlDbType.TinyInt:
case SqlDbType.SmallInt:
case SqlDbType.Int:
case SqlDbType.BigInt:
case SqlDbType.SmallMoney:
case SqlDbType.Money:
case SqlDbType.Decimal:
case SqlDbType.Real:
case SqlDbType.Float:
case SqlDbType.Date:
case SqlDbType.DateTime:
case SqlDbType.DateTime2:
case SqlDbType.DateTimeOffset:
case SqlDbType.UniqueIdentifier:
{
sbCommandText.Append(param.SqlDbType.ToString().ToUpper());
}
break;
// Unknown
case SqlDbType.Timestamp:
default:
{
sbCommandText.Append("/* UNKNOWN DATATYPE: ");
sbCommandText.Append(param.SqlDbType.ToString().ToUpper());
sbCommandText.Append(" *" + "/ ");
sbCommandText.Append(param.SqlDbType.ToString().ToUpper());
}
break;
}
}
}
sql命令查询将通过exec sp_executesql执行，所以这里有另一种方法来获取语句作为字符串(SqlCommand扩展方法):
public static string ToSqlStatement(this SqlCommand cmd)
{
return $@"EXECUTE sp_executesql N'{cmd.CommandText.Replace("'", "''")}'{cmd.Parameters.ToSqlParameters()}";
}
private static string ToSqlParameters(this SqlParameterCollection col)
{
if (col.Count == 0)
return string.Empty;
var parameters = new List<string>();
var parameterValues = new List<string>();
foreach (SqlParameter param in col)
{
parameters.Add($"{param.ParameterName}{param.ToSqlParameterType()}");
parameterValues.Add($"{param.ParameterName} = {param.ToSqlParameterValue()}");
}
return $",N\'{string.Join(",", parameters)}\',{string.Join(",", parameterValues)}";
}
private static object ToSqlParameterType(this SqlParameter param)
{
var paramDbType = param.SqlDbType.ToString().ToLower();
if (param.Precision != 0 && param.Scale != 0)
return $"{paramDbType}({param.Precision},{param.Scale})";
if (param.Precision != 0)
return $"{paramDbType}({param.Precision})";
switch (param.SqlDbType)
{
case SqlDbType.VarChar:
case SqlDbType.NVarChar:
string s = param.SqlValue?.ToString() ?? string.Empty;
return paramDbType + (s.Length > 0 ? $"({s.Length})" : string.Empty);
default:
return paramDbType;
}
}
private static string ToSqlParameterValue(this SqlParameter param)
{
switch (param.SqlDbType)
{
case SqlDbType.Char:
case SqlDbType.Date:
case SqlDbType.DateTime:
case SqlDbType.DateTime2:
case SqlDbType.DateTimeOffset:
case SqlDbType.NChar:
case SqlDbType.NText:
case SqlDbType.NVarChar:
case SqlDbType.Text:
case SqlDbType.Time:
case SqlDbType.VarChar:
case SqlDbType.Xml:
return $"\'{param.SqlValue.ToString().Replace("'", "''")}\'";
case SqlDbType.Bit:
return param.SqlValue.ToBooleanOrDefault() ? "1" : "0";
default:
return param.SqlValue.ToString().Replace("'", "''");
}
}
public static bool ToBooleanOrDefault(this object o, bool defaultValue = false)
{
if (o == null)
return defaultValue;
string value = o.ToString().ToLower();
switch (value)
{
case "yes":
case "true":
case "ok":
case "y":
return true;
case "no":
case "false":
case "n":
return false;
default:
bool b;
if (bool.TryParse(o.ToString(), out b))
return b;
break;
}
return defaultValue;
}
也需要覆盖非存储过程，所以我用以下逻辑扩充了CommandAsSql库(见上面@Flapper的回答下的评论):
private static void CommandAsSql_Text(this SqlCommand command, System.Text.StringBuilder sql)
{
string query = command.CommandText;
foreach (SqlParameter p in command.Parameters)
query = Regex.Replace(query, "\\B" + p.ParameterName + "\\b", p.ParameterValueForSQL()); //the first one is \B, the 2nd one is \b, since ParameterName starts with @ which is a non-word character in RegEx (see https://stackoverflow.com/a/2544661)
sql.AppendLine(query);
}
pull请求在: https://github.com/jphellemons/CommandAsSql/pull/3/commits/527d696dc6055c5bcf858b9700b83dc863f04896
Regex的想法是基于@stambikk和EvZ上面的评论，以及https://stackoverflow.com/a/2544661/903783的“更新:”部分提到了“消极的事后判断”。在正则表达式的开头使用\B而不是\B来进行单词边界检测是因为p.parameterName总是以“@”开头，而“@”不是单词字符。
注意ParameterValueForSQL()是CommandAsSql库中定义的一个扩展方法，用于处理单引号字符串参数值等问题。
如果只是检查结果查询中的参数是如何格式化的，大多数DBMS将允许从无开始查询字面量。因此:
Using cmd As SqlCommand = Connection.CreateCommand
cmd.CommandText = "SELECT @Value"
cmd.Parameters.AddWithValue("@Value", "myValue")
Return cmd.ExecuteScalar
End Using
这样你就可以看到引号是否被加倍等等。
我的解决方案:
public static class DbHelper
{
public static string ToString(this DbParameterCollection parameters, string sqlQuery)
{
return parameters.Cast<DbParameter>().Aggregate(sqlQuery, (current, p) => current.Replace(p.ParameterName, p.Value.ToString()));
}
}
