我有以下代码:
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。以下内容很简短，可以满足我的需要。
下面生成的SQL可以在SSMS中复制/粘贴(它正确地将参数替换为值)。您可以添加更多类型，但这满足我在这种情况下使用的所有类型。
private static void LogSQL(SqlCommand cmd)
{
string query = cmd.CommandText;
foreach (SqlParameter prm in cmd.Parameters)
{
switch (prm.SqlDbType)
{
case SqlDbType.Bit:
int boolToInt = (bool)prm.Value ? 1 : 0;
query = query.Replace(prm.ParameterName, string.Format("{0}", (bool)prm.Value ? 1 : 0));
break;
case SqlDbType.Int:
query = query.Replace(prm.ParameterName, string.Format("{0}", prm.Value));
break;
case SqlDbType.VarChar:
query = query.Replace(prm.ParameterName, string.Format("'{0}'", prm.Value));
break;
default:
query = query.Replace(prm.ParameterName, string.Format("'{0}'", prm.Value));
break;
}
}
// the following is my how I write to my log - your use will vary
logger.Debug("{0}", query);
return;
}
现在我可以在执行之前记录SQL:
LogSQL(queryCmd)
queryCmd.ExecuteNonQuery()
其他回答
如果你正在使用SQL Server，你可以使用SQL Server Profiler(如果你有的话)来查看实际执行的命令字符串。这对于复制/粘贴测试很有用，但对于日志记录恐怕就没用了。
分析器无疑是你最好的选择。
由于涉及到准备+执行步骤，您可能需要从分析器复制一组语句。
虽然不完美，这里有一些我为TSQL做的东西-可以很容易地调整为其他口味…如果没有其他的话，它将为你自己的改进提供一个起点:)
这在数据类型和输出参数等方面做得很好，类似于在SSMS中使用“执行存储过程”。我们主要使用sp，所以“text”命令不考虑参数等
public static String ParameterValueForSQL(this SqlParameter sp)
{
String retval = "";
switch (sp.SqlDbType)
{
case SqlDbType.Char:
case SqlDbType.NChar:
case SqlDbType.NText:
case SqlDbType.NVarChar:
case SqlDbType.Text:
case SqlDbType.Time:
case SqlDbType.VarChar:
case SqlDbType.Xml:
case SqlDbType.Date:
case SqlDbType.DateTime:
case SqlDbType.DateTime2:
case SqlDbType.DateTimeOffset:
retval = "'" + sp.Value.ToString().Replace("'", "''") + "'";
break;
case SqlDbType.Bit:
retval = (sp.Value.ToBooleanOrDefault(false)) ? "1" : "0";
break;
default:
retval = sp.Value.ToString().Replace("'", "''");
break;
}
return retval;
}
public static String CommandAsSql(this SqlCommand sc)
{
StringBuilder sql = new StringBuilder();
Boolean FirstParam = true;
sql.AppendLine("use " + sc.Connection.Database + ";");
switch (sc.CommandType)
{
case CommandType.StoredProcedure:
sql.AppendLine("declare @return_value int;");
foreach (SqlParameter sp in sc.Parameters)
{
if ((sp.Direction == ParameterDirection.InputOutput) || (sp.Direction == ParameterDirection.Output))
{
sql.Append("declare " + sp.ParameterName + "\t" + sp.SqlDbType.ToString() + "\t= ");
sql.AppendLine(((sp.Direction == ParameterDirection.Output) ? "null" : sp.ParameterValueForSQL()) + ";");
}
}
sql.AppendLine("exec [" + sc.CommandText + "]");
foreach (SqlParameter sp in sc.Parameters)
{
if (sp.Direction != ParameterDirection.ReturnValue)
{
sql.Append((FirstParam) ? "\t" : "\t, ");
if (FirstParam) FirstParam = false;
if (sp.Direction == ParameterDirection.Input)
sql.AppendLine(sp.ParameterName + " = " + sp.ParameterValueForSQL());
else
sql.AppendLine(sp.ParameterName + " = " + sp.ParameterName + " output");
}
}
sql.AppendLine(";");
sql.AppendLine("select 'Return Value' = convert(varchar, @return_value);");
foreach (SqlParameter sp in sc.Parameters)
{
if ((sp.Direction == ParameterDirection.InputOutput) || (sp.Direction == ParameterDirection.Output))
{
sql.AppendLine("select '" + sp.ParameterName + "' = convert(varchar, " + sp.ParameterName + ");");
}
}
break;
case CommandType.Text:
sql.AppendLine(sc.CommandText);
break;
}
return sql.ToString();
}
这将产生沿着这些线的输出…
use dbMyDatabase;
declare @return_value int;
declare @OutTotalRows BigInt = null;
exec [spMyStoredProc]
@InEmployeeID = 1000686
, @InPageSize = 20
, @InPage = 1
, @OutTotalRows = @OutTotalRows output
;
select 'Return Value' = convert(varchar, @return_value);
select '@OutTotalRows' = convert(varchar, @OutTotalRows);
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;
}
晚回答，我知道，但我也想这样做，所以我可以记录SQL。以下内容很简短，可以满足我的需要。
下面生成的SQL可以在SSMS中复制/粘贴(它正确地将参数替换为值)。您可以添加更多类型，但这满足我在这种情况下使用的所有类型。
private static void LogSQL(SqlCommand cmd)
{
string query = cmd.CommandText;
foreach (SqlParameter prm in cmd.Parameters)
{
switch (prm.SqlDbType)
{
case SqlDbType.Bit:
int boolToInt = (bool)prm.Value ? 1 : 0;
query = query.Replace(prm.ParameterName, string.Format("{0}", (bool)prm.Value ? 1 : 0));
break;
case SqlDbType.Int:
query = query.Replace(prm.ParameterName, string.Format("{0}", prm.Value));
break;
case SqlDbType.VarChar:
query = query.Replace(prm.ParameterName, string.Format("'{0}'", prm.Value));
break;
default:
query = query.Replace(prm.ParameterName, string.Format("'{0}'", prm.Value));
break;
}
}
// the following is my how I write to my log - your use will vary
logger.Debug("{0}", query);
return;
}
现在我可以在执行之前记录SQL:
LogSQL(queryCmd)
queryCmd.ExecuteNonQuery()
