我有以下代码:
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
如果有人想知道我为什么要这么做
用于记录(失败的)语句 可以将其复制粘贴到企业管理器中进行测试
当前回答
正如@pkExec和@Alok提到的，使用Replace在100%的情况下都不能工作。 这是我在我们的DAL中使用的解决方案，它使用RegExp只“匹配整个单词”并正确格式化数据类型。因此，生成的SQL可以直接在MySQL Workbench(或SQLSMS等…)中测试:)
(根据使用的DBMS替换MySQLHelper.EscapeString()函数)
Dim query As String = cmd.CommandText
query = query.Replace("SET", "SET" & vbNewLine)
query = query.Replace("WHERE", vbNewLine & "WHERE")
query = query.Replace("GROUP BY", vbNewLine & "GROUP BY")
query = query.Replace("ORDER BY", vbNewLine & "ORDER BY")
query = query.Replace("INNER JOIN", vbNewLine & "INNER JOIN")
query = query.Replace("LEFT JOIN", vbNewLine & "LEFT JOIN")
query = query.Replace("RIGHT JOIN", vbNewLine & "RIGHT JOIN")
If query.Contains("UNION ALL") Then
query = query.Replace("UNION ALL", vbNewLine & "UNION ALL" & vbNewLine)
ElseIf query.Contains("UNION DISTINCT") Then
query = query.Replace("UNION DISTINCT", vbNewLine & "UNION DISTINCT" & vbNewLine)
Else
query = query.Replace("UNION", vbNewLine & "UNION" & vbNewLine)
End If
For Each par In cmd.Parameters
If par.Value Is Nothing OrElse IsDBNull(par.Value) Then
query = RegularExpressions.Regex.Replace(query, par.ParameterName & "\b", "NULL")
ElseIf TypeOf par.Value Is Date Then
query = RegularExpressions.Regex.Replace(query, par.ParameterName & "\b", "'" & Format(par.Value, "yyyy-MM-dd HH:mm:ss") & "'")
ElseIf TypeOf par.Value Is TimeSpan Then
query = RegularExpressions.Regex.Replace(query, par.ParameterName & "\b", "'" & par.Value.ToString & "'")
ElseIf TypeOf par.Value Is Double Or TypeOf par.Value Is Decimal Or TypeOf par.Value Is Single Then
query = RegularExpressions.Regex.Replace(query, par.ParameterName & "\b", Replace(par.Value.ToString, ",", "."))
ElseIf TypeOf par.Value Is Integer Or TypeOf par.Value Is UInteger Or TypeOf par.Value Is Long Or TypeOf par.Value Is ULong Then
query = RegularExpressions.Regex.Replace(query, par.ParameterName & "\b", par.Value.ToString)
Else
query = RegularExpressions.Regex.Replace(query, par.ParameterName & "\b", "'" & MySqlHelper.EscapeString(CStr(par.Value)) & "'")
End If
Next
例子:
SELECT * FROM order WHERE order_status = @order_status AND order_date = @order_date
将生成:
SELECT * FROM order WHERE order_status = 'C' AND order_date = '2015-01-01 00:00:00'
其他回答
虽然不完美，这里有一些我为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);
我也有这个问题，一些参数化查询或sp的会给我一个SqlException(主要是字符串或二进制数据将被截断)，并且语句很难调试(据我所知，目前没有SQL分析器支持SQL Azure)
我在这里看到了很多类似的代码。我最终把我的解决方案放在一个Sql-Library项目中，以备将来使用。
生成器可以在这里使用: https://github.com/jeroenpot/SqlHelper/blob/master/Source/Mirabeau.MsSql.Library/SqlGenerator.cs
它支持两种CommandType。文本和命令类型。StoredProcedure
如果你安装了nuget-package，你可以用下面的语句生成它:
SqlDebugHelper.CreateExecutableSqlStatement(sql, parameters);
扩展Kon的代码来帮助调试存储过程:
private void ExtractSqlCommandForDebugging(SqlCommand cmd)
{
string sql = "exec " + cmd.CommandText;
bool first = true;
foreach (SqlParameter p in cmd.Parameters)
{
string value = ((p.Value == DBNull.Value) ? "null"
: (p.Value is string) ? "'" + p.Value + "'"
: p.Value.ToString());
if (first)
{
sql += string.Format(" {0}={1}", p.ParameterName, value);
first = false;
}
else
{
sql += string.Format("\n , {0}={1}", p.ParameterName, value);
}
}
sql += "\nGO";
Debug.WriteLine(sql);
}
在我的第一个测试用例中，它生成:
exec dbo.MyStoredProcName @SnailMail=False
, @Email=True
, @AcceptSnailMail=False
, @AcceptEmail=False
, @DistanceMiles=-1
, @DistanceLocationList=''
, @ExcludeDissatisfied=True
, @ExcludeCodeRed=True
, @MinAge=null
, @MaxAge=18
, @GenderTypeID=-1
, @NewThisYear=-1
, @RegisteredThisYear=-1
, @FormersTermGroupList=''
, @RegistrationStartDate=null
, @RegistrationEndDate=null
, @DivisionList='25'
, @LocationList='29,30'
, @OneOnOneOPL=-1
, @JumpStart=-1
, @SmallGroup=-1
, @PurchasedEAP=-1
, @RedeemedEAP=-1
, @ReturnPlanYes=False
, @MinNetPromoter=-1
, @MinSurveyScore=-1
, @VIPExclusionTypes='-2'
, @FieldSelectionMask=65011584
, @DisplayType=0
GO
你可能需要添加一些有条件的“..is…”类型的赋值，例如日期和时间。
为了记录日志，恐怕没有更好的方法了，只能自己构造字符串:
string query = cmd.CommandText;
foreach (SqlParameter p in cmd.Parameters)
{
query = query.Replace(p.ParameterName, p.Value.ToString());
}
分析器无疑是你最好的选择。
由于涉及到准备+执行步骤，您可能需要从分析器复制一组语句。
