我有以下代码:
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(如果你有的话)来查看实际执行的命令字符串。这对于复制/粘贴测试很有用，但对于日志记录恐怕就没用了。
其他回答
正如@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'
这个方法现在对我很有效。也许它对某人有用。请原谅所有的冗余。
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
为了记录日志，恐怕没有更好的方法了，只能自己构造字符串:
string query = cmd.CommandText;
foreach (SqlParameter p in cmd.Parameters)
{
query = query.Replace(p.ParameterName, p.Value.ToString());
}
为我的解决方案使用了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;
}
如果只是检查结果查询中的参数是如何格式化的，大多数DBMS将允许从无开始查询字面量。因此:
Using cmd As SqlCommand = Connection.CreateCommand
cmd.CommandText = "SELECT @Value"
cmd.Parameters.AddWithValue("@Value", "myValue")
Return cmd.ExecuteScalar
End Using
这样你就可以看到引号是否被加倍等等。
