如何通过 SqlConnection 获取上次执行的 SQL 查询? [英] How to get last executed SQL query by SqlConnection?

问题描述

我的代码可以访问一个 SqlConnection 对象.我无法访问所有其他 ADO.NET 对象，例如 SqlCommand、SqlParameter 等.这些其他对象由 Dapper Extensions ORM 使用.

I have a SqlConnection object accessible to my code. All other ADO.NET objects like SqlCommand, SqlParameter etc are not accessible to me. These other objects are consumed by Dapper Extensions ORM.

我的应用程序使用 SqlConnection 对象和 Dapper Extensions 方法执行 SQL 查询.SQL 查询由 Dapper Extensions 自动生成；我无法访问生成的查询.我想记录这个 SQL 查询.

My application executes SQL queries using SqlConnection object and Dapper Extensions method. SQL query is auto generated by Dapper Extensions; generated query is not accessible to me. I want to log this SQL query.

我已经有了我的日志记录模块，我唯一需要的是连接对象执行的最后一个 SQL 查询.

I already have my logging module in place and the only thing I need is the last SQL query executed by connection object.

如何通过SqlConnection获取上次执行的SQL查询?

How to get last executed SQL query by SqlConnection?

如果我得到底层 SqlCommand，我可以使用下面的代码从中构建查询；不幸的是，我无法访问它.

If I get underlying SqlCommand, I can build the query from it using the code below; unfortunately, it is not accessible to me.

public string GetCommandLogString(IDbCommand command)
{
    string outputText;
    if(command.Parameters.Count == 0)
    {
        outputText = command.CommandText;
    }
    else
    {
        StringBuilder output = new StringBuilder();
        output.Append(command.CommandText);
        output.Append("; ");

        IDataParameter objIDataParameter;
        int parameterCount = command.Parameters.Count;
        for(int i = 0; i < parameterCount; i++)
        {
            objIDataParameter = (IDataParameter)command.Parameters[i];
            output.Append(string.Format("{0} = '{1}'", objIDataParameter.ParameterName, objIDataParameter.Value));

            if(i + 1 < parameterCount)
            {
                output.Append(", ");
            }
        }
        outputText = output.ToString();
    }
    return outputText;
}

推荐答案

我过去使用的一种方法，当我不想依赖任何外部工具时(或者缺少工具时，比如当使用 MS Access)是使用数据库连接和命令包装器".类，以便我可以将日志记录添加到它们的任何方法或属性中.

An approach that I've used in the past, when I didn't want to rely upon any external tools (or when the tools were lacking, like when working with MS Access) is to use database connection and command "wrapper" classes so that I can add logging to any of their methods or properties.

要使用它，您将要使用的任何连接传递给 WrappedDbConnection 的构造函数，如下所示:

To use it, you pass whatever connection you want to use into the WrappedDbConnection's constructor as below:

using (var conn = new WrappedDbConnection(GetMyConnection()))
{
    // Do work using Dapper here against "conn"
}

(注意:当调用 WrappedDbConnection 实例的 Dispose 方法时，该方法将被传递到底层连接，因此您不需要 using 用于 WrappedDbConnection 和一个单独的 using 用于您的连接 - 您只需要一个 using，如上所示).

(Note: When the WrappedDbConnection instance's Dispose method is called, that will be passed onto the underlying connection and so you don't need a using for the WrappedDbConnection and a separate using for your connection - you only need one using, as shown above).

下面定义了您需要的两个类.

The two classes that you need are defined below.

请注意，方法 ExecuteNonQuery、ExecuteReader、ExecuteReader 和 中有 Console.WriteLine 调用>ExecuteScalar，它将写出将要执行的查询.您可能需要更改此设置以满足您的要求以在完成后写出查询，或者您可能希望使用与 Console.Writeline 不同的输出，但这些应该足够简单要进行的更改.

Note that there are Console.WriteLine calls in the methods ExecuteNonQuery, ExecuteReader, ExecuteReader and ExecuteScalar that will write out what query is about to be executed. You may want to change this for your requirements to write out the query after it's completed or you might want to use a different output that Console.Writeline but those should be simple enough changes to make.

public class WrappedDbConnection : IDbConnection
{
    private readonly IDbConnection _conn;
    public WrappedDbConnection(IDbConnection connection)
    {
        if (connection == null)
            throw new ArgumentNullException(nameof(connection));

        _conn = connection;
    }

    public string ConnectionString
    {
        get { return _conn.ConnectionString; }
        set { _conn.ConnectionString = value; }
    }

    public int ConnectionTimeout
    {
        get { return _conn.ConnectionTimeout; }
    }

    public string Database
    {
        get { return _conn.Database; }
    }

    public ConnectionState State
    {
        get { return _conn.State; }
    }

    public IDbTransaction BeginTransaction()
    {
        return _conn.BeginTransaction();
    }

    public IDbTransaction BeginTransaction(IsolationLevel il)
    {
        return _conn.BeginTransaction(il);
    }

    public void ChangeDatabase(string databaseName)
    {
        _conn.ChangeDatabase(databaseName);
    }

    public void Close()
    {
        _conn.Close();
    }

    public IDbCommand CreateCommand()
    {
        return new WrappedDbCommand(_conn.CreateCommand());
    }

    public void Dispose()
    {
        _conn.Dispose();
    }

    public void Open()
    {
        _conn.Open();
    }
}

public class WrappedDbCommand : IDbCommand
{
    private readonly IDbCommand _cmd;
    public WrappedDbCommand(IDbCommand command)
    {
        if (command == null)
            throw new ArgumentNullException(nameof(command));

        _cmd = command;
    }

    public string CommandText
    {
        get { return _cmd.CommandText; }
        set { _cmd.CommandText = value; }
    }

    public int CommandTimeout
    {
        get { return _cmd.CommandTimeout; }
        set { _cmd.CommandTimeout = value; }
    }

    public CommandType CommandType
    {
        get { return _cmd.CommandType; }
        set { _cmd.CommandType = value; }
    }

    public IDbConnection Connection
    {
        get { return _cmd.Connection; }
        set { _cmd.Connection = value; }
    }

    public IDataParameterCollection Parameters
    {
        get { return _cmd.Parameters; }
    }

    public IDbTransaction Transaction
    {
        get { return _cmd.Transaction; }
        set { _cmd.Transaction = value; }
    }

    public UpdateRowSource UpdatedRowSource
    {
        get { return _cmd.UpdatedRowSource; }
        set { _cmd.UpdatedRowSource = value; }
    }

    public void Cancel()
    {
        _cmd.Cancel();
    }

    public IDbDataParameter CreateParameter()
    {
        return _cmd.CreateParameter();
    }

    public void Dispose()
    {
        _cmd.Dispose();
    }

    public int ExecuteNonQuery()
    {
        Console.WriteLine($"[ExecuteNonQuery] {_cmd.CommandText}");
        return _cmd.ExecuteNonQuery();
    }

    public IDataReader ExecuteReader()
    {
        Console.WriteLine($"[ExecuteReader] {_cmd.CommandText}");
        return _cmd.ExecuteReader();
    }

    public IDataReader ExecuteReader(CommandBehavior behavior)
    {
        Console.WriteLine($"[ExecuteReader({behavior})] {_cmd.CommandText}");
        return _cmd.ExecuteReader();
    }

    public object ExecuteScalar()
    {
        Console.WriteLine($"[ExecuteScalar] {_cmd.CommandText}");
        return _cmd.ExecuteScalar();
    }

    public void Prepare()
    {
        _cmd.Prepare();
    }
}

这篇关于如何通过 SqlConnection 获取上次执行的 SQL 查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！