从一个SqlCommand对象生成的SQL语句？ [英] Get the generated SQL statement from a SqlCommand object?

问题描述

我有以下的code：

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说明书作为一个字符串，它应该是这样的：

I wonder if there is any way to get the final SQL statment as a String, which should look like this:

UPDATE someTable SET Value = "myValue" WHERE Id = 1234

如果有人想知道为什么我会做这样的：

If anyone wonders why I would do this:

• 用于记录（失败）语句


• 具有复制和放大器的可能性;粘贴到企业管理器用于测试目的

推荐答案

虽然不完美，这里的东西我敲了东西TSQL - 可以很容易地调整了其他口味......如果不出意外，它会给你一个起点为自己的改进：）

Whilst not perfect, here's something I knocked up something for TSQL - could be easily tweaked for other flavours... If nothing else it will give you a start point for your own improvements :)

这确实对数据类型和输出参数等类似使用OK工作执行存储过程，在SSMS。我们主要使用的SP这样的文字命令不考虑的参数等。

This does an OK job on data types and output parameters etc similar to using "execute stored procedure" in SSMS. We mostly used SPs so the "text" command doesn't account for parameters etc

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

这沿着这些线路产生的输出...

this generates output along these lines...

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

这篇关于从一个SqlCommand对象生成的SQL语句？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！