从参数命令到非参数命令，可以修改

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

To

Private sub Update( byval myID as Int32, byval myVal as String)
    Using cmd As SqlCommand = Connection.CreateCommand
        cmd.CommandText = "UPDATE someTable SET Value = '" & myVaL & "'" & _
                          " WHERE Id = " & myID  
        cmd.ExecuteNonQuery
    End Using
End sub

我也有这个问题，一些参数化查询或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);

如果只是检查结果查询中的参数是如何格式化的，大多数DBMS将允许从无开始查询字面量。因此:

Using cmd As SqlCommand = Connection.CreateCommand
    cmd.CommandText = "SELECT @Value"
    cmd.Parameters.AddWithValue("@Value", "myValue")
    Return cmd.ExecuteScalar
End Using

这样你就可以看到引号是否被加倍等等。

如果你要转换命令文本:

Private Function ConvToNonParm(ByRef Cmd As SqlClient.SqlCommand) As String
    For myCnt As Int16 = 1 To Cmd.Parameters.Count
        Dim myVal As String = Cmd.Parameters(myCnt - 1).Value
        Select Case Cmd.Parameters(myCnt - 1).SqlDbType
            Case SqlDbType.Char, SqlDbType.NChar, SqlDbType.VarChar, SqlDbType.NChar, SqlDbType.NVarChar 'and so on
                myVal = "'" & myVal & "'"
                'Case "others...."

            Case Else
                'please assing
        End Select
        Cmd.CommandText = Replace(Cmd.CommandText, Cmd.Parameters(myCnt - 1).ToString, myVal)
    Next
    Cmd.Parameters.Clear()
    Return Cmd.CommandText
End Function

现在你可以像下面这样获得非参数commandtext:

    myCmd.CommandText = "UPDATE someTable SET Value = @Value"
    myCmd.CommandText &= " WHERE Id = @Id"
    myCmd.Parameters.AddWithValue("@Id", 1234)
    myCmd.Parameters.AddWithValue("@Value", "myValue")

    myCmd.CommandText = ConvToNonParm(myCmd)

结果是“更新someTable SET值= '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'

为了记录日志，恐怕没有更好的方法了，只能自己构造字符串:

string query = cmd.CommandText;

foreach (SqlParameter p in cmd.Parameters)
{
    query = query.Replace(p.ParameterName, p.Value.ToString());
}