我有以下代码:
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
如果有人想知道我为什么要这么做
用于记录(失败的)语句 可以将其复制粘贴到企业管理器中进行测试
当前回答
也需要覆盖非存储过程，所以我用以下逻辑扩充了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库中定义的一个扩展方法，用于处理单引号字符串参数值等问题。
其他回答
这个方法现在对我很有效。也许它对某人有用。请原谅所有的冗余。
Public Shared Function SqlString(ByVal cmd As SqlCommand) As String
Dim sbRetVal As New System.Text.StringBuilder()
For Each item As SqlParameter In cmd.Parameters
Select Case item.DbType
Case DbType.String
sbRetVal.AppendFormat("DECLARE {0} AS VARCHAR(255)", item.ParameterName)
sbRetVal.AppendLine()
sbRetVal.AppendFormat("SET {0} = '{1}'", item.ParameterName, item.Value)
sbRetVal.AppendLine()
Case DbType.DateTime
sbRetVal.AppendFormat("DECLARE {0} AS DATETIME", item.ParameterName)
sbRetVal.AppendLine()
sbRetVal.AppendFormat("SET {0} = '{1}'", item.ParameterName, item.Value)
sbRetVal.AppendLine()
Case DbType.Guid
sbRetVal.AppendFormat("DECLARE {0} AS UNIQUEIDENTIFIER", item.ParameterName)
sbRetVal.AppendLine()
sbRetVal.AppendFormat("SET {0} = '{1}'", item.ParameterName, item.Value)
sbRetVal.AppendLine()
Case DbType.Int32
sbRetVal.AppendFormat("DECLARE {0} AS int", item.ParameterName)
sbRetVal.AppendLine()
sbRetVal.AppendFormat("SET {0} = {1}", item.ParameterName, item.Value)
sbRetVal.AppendLine()
Case Else
Stop
End Select
Next
sbRetVal.AppendLine("")
sbRetVal.AppendLine(cmd.CommandText)
Return sbRetVal.ToString()
End Function
虽然不完美，这里有一些我为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);
为我的解决方案使用了Flapper的部分代码，它返回整个SQL字符串，包括在MS SQL SMS中运行的参数值。
public string ParameterValueForSQL(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:
if (sp.Value == DBNull.Value)
{
retval = "NULL";
}
else
{
retval = "'" + sp.Value.ToString().Replace("'", "''") + "'";
}
break;
case SqlDbType.Bit:
if (sp.Value == DBNull.Value)
{
retval = "NULL";
}
else
{
retval = ((bool)sp.Value == false) ? "0" : "1";
}
break;
default:
if (sp.Value == DBNull.Value)
{
retval = "NULL";
}
else
{
retval = sp.Value.ToString().Replace("'", "''");
}
break;
}
return retval;
}
public string CommandAsSql(SqlCommand sc)
{
string sql = sc.CommandText;
sql = sql.Replace("\r\n", "").Replace("\r", "").Replace("\n", "");
sql = System.Text.RegularExpressions.Regex.Replace(sql, @"\s+", " ");
foreach (SqlParameter sp in sc.Parameters)
{
string spName = sp.ParameterName;
string spValue = ParameterValueForSQL(sp);
sql = sql.Replace(spName, spValue);
}
sql = sql.Replace("= NULL", "IS NULL");
sql = sql.Replace("!= NULL", "IS NOT NULL");
return sql;
}
分析器无疑是你最好的选择。
由于涉及到准备+执行步骤，您可能需要从分析器复制一组语句。
这个方法是我自己写的。我用了布鲁诺·拉尼克的代码。也许它对某人有用。
public static string getQueryFromCommand(SqlCommand cmd)
{
StringBuilder CommandTxt = new StringBuilder();
CommandTxt.Append("DECLARE ");
List<string> paramlst = new List<string>();
foreach (SqlParameter parms in cmd.Parameters)
{
paramlst.Add(parms.ParameterName);
CommandTxt.Append(parms.ParameterName + " AS ");
CommandTxt.Append(parms.SqlDbType.ToString());
CommandTxt.Append(",");
}
if (CommandTxt.ToString().Substring(CommandTxt.Length-1, 1) == ",")
CommandTxt.Remove(CommandTxt.Length-1, 1);
CommandTxt.AppendLine();
int rownr = 0;
foreach (SqlParameter parms in cmd.Parameters)
{
string val = String.Empty;
if (parms.DbType.Equals(DbType.String) || parms.DbType.Equals(DbType.DateTime))
val = "'" + Convert.ToString(parms.Value).Replace(@"\", @"\\").Replace("'", @"\'") + "'";
if (parms.DbType.Equals(DbType.Int16) || parms.DbType.Equals(DbType.Int32) || parms.DbType.Equals(DbType.Int64) || parms.DbType.Equals(DbType.Decimal) || parms.DbType.Equals(DbType.Double))
val = Convert.ToString(parms.Value);
CommandTxt.AppendLine();
CommandTxt.Append("SET " + paramlst[rownr].ToString() + " = " + val.ToString());
rownr += 1;
}
CommandTxt.AppendLine();
CommandTxt.AppendLine();
CommandTxt.Append(cmd.CommandText);
return CommandTxt.ToString();
}
