我有以下代码:

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

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

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


当前回答

如果你的数据库是Oracle, sql文本包含动态变量,如:1,:2,…然后你可以使用:

string query = cmd.CommandText;
int i = 1;
foreach (OracleParameter p in cmd.Parameters)
  {
    query = query.Replace(":"+i.ToString(),((p.Value==null)?"":p.Value.ToString()));
    i++;
  }

其他回答

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

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#。

如果你正在使用SQL Server,你可以使用SQL Server Profiler(如果你有的话)来查看实际执行的命令字符串。这对于复制/粘贴测试很有用,但对于日志记录恐怕就没用了。

扩展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…”类型的赋值,例如日期和时间。

这个方法是我自己写的。我用了布鲁诺·拉尼克的代码。也许它对某人有用。

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

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

    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