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

问题描述

实际上，我的情况与这里提到的有所不同.我问了其他问题.但是由于我在那儿找不到解决方案，所以我决定更改方法.

Actually, my scenario is bit different than mentioned here. I have asked other question. But as I am not getting solution there, I decided to change the approach.

我有一个我的代码可以访问的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;
}

推荐答案

在过去，当我不想依赖任何外部工具时(或者缺少工具时，例如当使用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 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 -

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

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

(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"和"ExecuteScalar"中有Console.WriteLine调用，这些调用将写出将要执行的查询.您可能需要更改此要求，以在查询 完成后写出查询，或者您可能想使用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屋！