我有以下代码:

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

如果有人想知道我为什么要这么做

用于记录(失败的)语句 可以将其复制粘贴到企业管理器中进行测试


当前回答

修改版本的Kon的答案,因为它只是部分工作与类似的命名参数。使用String Replace函数的缺点。除此之外,我完全信任他的解决方案。

private string GetActualQuery(SqlCommand sqlcmd)
{
    string query = sqlcmd.CommandText;
    string parameters = "";
    string[] strArray = System.Text.RegularExpressions.Regex.Split(query, " VALUES ");

    //Reconstructs the second half of the SQL Command
    parameters = "(";

    int count = 0;
    foreach (SqlParameter p in sqlcmd.Parameters)
    {
        if (count == (sqlcmd.Parameters.Count - 1))
        {
            parameters += p.Value.ToString();
        }
        else
        {
            parameters += p.Value.ToString() + ", ";
        }
        count++;
    }

    parameters += ")";

    //Returns the string recombined.
    return strArray[0] + " VALUES " + parameters;
}

其他回答

虽然不完美,这里有一些我为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);

这个方法现在对我很有效。也许它对某人有用。请原谅所有的冗余。

    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());
}

分析器无疑是你最好的选择。

由于涉及到准备+执行步骤,您可能需要从分析器复制一组语句。

这是我用来将存储过程的参数列表输出到调试控制台的:

string query = (from SqlParameter p in sqlCmd.Parameters where p != null where p.Value != null select string.Format("Param: {0} = {1},  ", p.ParameterName, p.Value.ToString())).Aggregate(sqlCmd.CommandText, (current, parameter) => current + parameter);
Debug.WriteLine(query);

这将生成一个类似于下面的控制台输出:

Customer.prGetCustomerDetails: @Offset = 1,  Param: @Fetch = 10,  Param: @CategoryLevel1ID = 3,  Param: @VehicleLineID = 9,  Param: @SalesCode1 = bce,  

我把这段代码直接放在我希望调试的任何过程的下面,它类似于sql分析器会话,但使用c#。