让MySoft.Data也能有Discuz!NT的数据库查询分析工具

当我们用Debug模式运行Discuz!NT项目的时候，我们会发现在页面的最下方有一个数据查询分析工具，非常的方便。当我们运行一个页面的时候，对sql的操作可以一目了然。当然，有些还是无法显示在页面上的，比如多个跳转的时候，最终显示的就只有一个页面的sql。不过也挺方便了。

如图：

这个数据库查询分析工具，只有在Debug模式下才会显示，Release模式不会显示。

这样的做法有两个好处，一是方便调试，第二是每当发布站点的时候，一定是Release模式的，可以确保站点在运行效率上有保证（很多时候我都是Debug的，囧）。

到底Disucuz!NT是怎么做的，从页面上分析可以知道，是直接在</body>的前面添加这些调试信息上去的

 

这些信息，在数据访问的DbHelper直接通过CommandText得到sql，DbParameter得到参数。当执行SQL时，通过DbHelper这个类里面的QueryDetail类进行html的拼装，最终成为这个样子了。在这里我就简单说下，不做详细的分析了，有兴趣的同学可以直接看Discuz!NT项目的pagebase类和dbhelper类，全部都是在这里实现的。

最关键的就是，这么好的一个功能怎么搬到MySoft.Data框架上去用，Discuz!NT每次输出HTML时是通过模板进行页面显示的，所以，在数据访问阶段得到的sql直接嵌进去就可以了。但是，我们这些普通的页面输出就不能按照这样的做法了。

经过测试，只有每当得到这些数据库分析的HTML时就在httpmodule类中得到即将输出的页面得到HTML再进行嵌进去，这里如果不嵌入到原有的html中，而是直接加到网页的最末尾，会导致页面的JS报错，css错位等。所以，必须把这些html加入到</body>标签的前面。

原理就是这样，具体看下面代码实现吧：

首先，在MySoft.Data的DbHelper类中进行代码的改装：DbHelper.cs

using System;
using System.Data;
using System.Data.Common;
using System.Collections;

namespace MySoft.Data
{
    public delegate string DecryptEventHandler(string connectionString);

    public sealed class DbHelper
    {
        private DecryptEventHandler decryptProxy;
        private DbProviderFactory dbFactory;
        private string dbConnectionString;

        public DbHelper(string connectionString, DbProviderFactory dbFactory)
        {
            this.dbConnectionString = connectionString;
            this.dbFactory = dbFactory;
        }

        public void SetDecryptHandler(DecryptEventHandler decryptProxy)
        {
            this.decryptProxy = decryptProxy;
        }

        #region 创建供外部调用的对象

        public DbConnection CreateConnection()
        {
            DbConnection dbconn = dbFactory.CreateConnection();
            if (decryptProxy != null)
            {
                dbConnectionString = decryptProxy(dbConnectionString);
                decryptProxy = null;
            }
            dbconn.ConnectionString = dbConnectionString;
            return dbconn;
        }

        /// <summary>
        /// 创建命令
        /// </summary>
        /// <returns></returns>
        public DbCommand CreateCommand()
        {
            return dbFactory.CreateCommand();
        }

        /// <summary>
        /// 创建参数
        /// </summary>
        /// <returns></returns>
        public DbParameter CreateParameter()
        {
            return dbFactory.CreateParameter();
        }

        #endregion

        public DbCommand CreateStoredProcCommand(string procName)
        {
            DbCommand dbCommand = dbFactory.CreateCommand();
            dbCommand.CommandType = CommandType.StoredProcedure;
            dbCommand.CommandText = procName;
            return dbCommand;
        }

        public DbCommand CreateSqlStringCommand(string cmdText)
        {
            DbCommand dbCommand = dbFactory.CreateCommand();
            dbCommand.CommandType = CommandType.Text;
            dbCommand.CommandText = cmdText;
            return dbCommand;
        }

        #region 增加参数

        public void AddParameterCollection(DbCommand cmd, DbParameterCollection dbParameterCollection)
        {
            foreach (DbParameter dbParameter in dbParameterCollection)
            {
                cmd.Parameters.Add(dbParameter);
            }
        }

        public void AddInputParameter(DbCommand cmd, string parameterName, DbType dbType, int size, object value)
        {
            DbParameter dbParameter = cmd.CreateParameter();
            dbParameter.DbType = dbType;
            dbParameter.ParameterName = parameterName;
            dbParameter.Size = size;
            dbParameter.Value = value;
            dbParameter.Direction = ParameterDirection.Input;
            cmd.Parameters.Add(dbParameter);
        }

        public void AddInputParameter(DbCommand cmd, string parameterName, DbType dbType, object value)
        {
            DbParameter dbParameter = cmd.CreateParameter();
            dbParameter.DbType = dbType;
            dbParameter.ParameterName = parameterName;
            dbParameter.Value = value;
            dbParameter.Direction = ParameterDirection.Input;
            cmd.Parameters.Add(dbParameter);
        }

        public void AddOutputParameter(DbCommand cmd, string parameterName, DbType dbType, int size)
        {
            DbParameter dbParameter = cmd.CreateParameter();
            dbParameter.DbType = dbType;
            dbParameter.ParameterName = parameterName;
            dbParameter.Size = size;
            dbParameter.Direction = ParameterDirection.Output;
            cmd.Parameters.Add(dbParameter);
        }

        public void AddInputOutputParameter(DbCommand cmd, string parameterName, DbType dbType, object value, int size)
        {
            DbParameter dbParameter = cmd.CreateParameter();
            dbParameter.DbType = dbType;
            dbParameter.ParameterName = parameterName;
            dbParameter.Value = value;
            dbParameter.Size = size;
            dbParameter.Direction = ParameterDirection.InputOutput;
            cmd.Parameters.Add(dbParameter);
        }

        public void AddReturnValueParameter(DbCommand cmd, string parameterName, DbType dbType)
        {
            DbParameter dbParameter = cmd.CreateParameter();
            dbParameter.DbType = dbType;
            dbParameter.ParameterName = parameterName;
            dbParameter.Direction = ParameterDirection.ReturnValue;
            cmd.Parameters.Add(dbParameter);
        }

        public DbParameter GetParameter(DbCommand cmd, string parameterName)
        {
            return cmd.Parameters[parameterName];
        }

        #endregion

        #region 执行

        public DataSet ExecuteDataSet(DbCommand cmd)
        {
            cmd.Connection = CreateConnection();
            cmd.Connection.Open();
            try
            {
                using (DbDataAdapter dbDataAdapter = dbFactory.CreateDataAdapter())
                {
#if DEBUG
                    DateTime dt1 = DateTime.Now;
#endif
                    dbDataAdapter.SelectCommand = cmd;
                    DataSet ds = new DataSet();
                    dbDataAdapter.Fill(ds);
                    if (cmd.Connection.State != ConnectionState.Closed)
                    {
                        cmd.Connection.Close();
                        cmd.Connection.Dispose();
                    }
#if DEBUG
                    DateTime dt2 = DateTime.Now;

                    DbQueryDetailHelper.QueryDetail += DbQueryDetailHelper.GetQueryDetail(cmd.CommandText, dt1, dt2, cmd.Parameters);
#endif
                    return ds;
                }
            }
            catch
            {
                if (cmd.Connection.State != ConnectionState.Closed)
                {
                    cmd.Connection.Close();
                    cmd.Connection.Dispose();
                }
                throw;
            }
        }

        public DataTable ExecuteDataTable(DbCommand cmd)
        {
            cmd.Connection = CreateConnection();
            cmd.Connection.Open();
            try
            {
                using (DbDataAdapter dbDataAdapter = dbFactory.CreateDataAdapter())
                {
#if DEBUG
                    DateTime dt1 = DateTime.Now;
#endif
                    dbDataAdapter.SelectCommand = cmd;
                    DataTable dataTable = new DataTable();
                    dbDataAdapter.Fill(dataTable);
                    if (cmd.Connection.State != ConnectionState.Closed)
                    {
                        cmd.Connection.Close();
                        cmd.Connection.Dispose();
                    }
#if DEBUG
                    DateTime dt2 = DateTime.Now;

                    DbQueryDetailHelper.QueryDetail += DbQueryDetailHelper.GetQueryDetail(cmd.CommandText, dt1, dt2, cmd.Parameters);
#endif
                    return dataTable;
                }
            }
            catch
            {
                if (cmd.Connection.State != ConnectionState.Closed)
                {
                    cmd.Connection.Close();
                    cmd.Connection.Dispose();
                }
                throw;
            }
        }

        public DbDataReader ExecuteReader(DbCommand cmd)
        {
            cmd.Connection = CreateConnection();
            cmd.Connection.Open();
            try
            {
#if DEBUG
                DateTime dt1 = DateTime.Now;
#endif
                DbDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
#if DEBUG
                DateTime dt2 = DateTime.Now;

                DbQueryDetailHelper.QueryDetail += DbQueryDetailHelper.GetQueryDetail(cmd.CommandText, dt1, dt2, cmd.Parameters);
#endif
                return reader;
            }
            catch
            {
                if (cmd.Connection.State != ConnectionState.Closed)
                {
                    cmd.Connection.Close();
                    cmd.Connection.Dispose();
                }
                throw;
            }
        }

        public int ExecuteNonQuery(DbCommand cmd)
        {
            cmd.Connection = CreateConnection();
            cmd.Connection.Open();
            try
            {
#if DEBUG
                DateTime dt1 = DateTime.Now;
#endif
                int ret = cmd.ExecuteNonQuery();
                if (cmd.Connection.State != ConnectionState.Closed)
                {
                    cmd.Connection.Close();
                    cmd.Connection.Dispose();
                }
#if DEBUG
                DateTime dt2 = DateTime.Now;

                DbQueryDetailHelper.QueryDetail += DbQueryDetailHelper.GetQueryDetail(cmd.CommandText, dt1, dt2, cmd.Parameters);
#endif
                return ret;
            }
            catch
            {
                if (cmd.Connection.State != ConnectionState.Closed)
                {
                    cmd.Connection.Close();
                    cmd.Connection.Dispose();
                }
                throw;
            }
        }

        public object ExecuteScalar(DbCommand cmd)
        {
            cmd.Connection = CreateConnection();
            cmd.Connection.Open();
            try
            {
#if DEBUG
                DateTime dt1 = DateTime.Now;
#endif
                object ret = cmd.ExecuteScalar();
                if (cmd.Connection.State != ConnectionState.Closed)
                {
                    cmd.Connection.Close();
                    cmd.Connection.Dispose();
                }
                if (ret == DBNull.Value) ret = null;
#if DEBUG
                DateTime dt2 = DateTime.Now;

                DbQueryDetailHelper.QueryDetail += DbQueryDetailHelper.GetQueryDetail(cmd.CommandText, dt1, dt2, cmd.Parameters);
#endif
                return ret;
            }
            catch
            {
                if (cmd.Connection.State != ConnectionState.Closed)
                {
                    cmd.Connection.Close();
                    cmd.Connection.Dispose();
                }
                throw;
            }
        }

        #endregion

        #region 执行事务

        public DataSet ExecuteDataSet(DbCommand cmd, DbTrans t)
        {
            cmd.Connection = t.Connection;
            if (t.Transaction != null) cmd.Transaction = t.Transaction;
            try
            {
                using (DbDataAdapter dbDataAdapter = dbFactory.CreateDataAdapter())
                {
#if DEBUG
                    DateTime dt1 = DateTime.Now;
#endif
                    dbDataAdapter.SelectCommand = cmd;
                    DataSet ds = new DataSet();
                    dbDataAdapter.Fill(ds);
#if DEBUG
                    DateTime dt2 = DateTime.Now;

                    DbQueryDetailHelper.QueryDetail += DbQueryDetailHelper.GetQueryDetail(cmd.CommandText, dt1, dt2, cmd.Parameters);
#endif
                    return ds;
                }
            }
            catch
            {
                throw;
            }
        }

        public DataTable ExecuteDataTable(DbCommand cmd, DbTrans t)
        {

            cmd.Connection = t.Connection;
            if (t.Transaction != null) cmd.Transaction = t.Transaction;
            try
            {

                using (DbDataAdapter dbDataAdapter = dbFactory.CreateDataAdapter())
                {
#if DEBUG
                    DateTime dt1 = DateTime.Now;
#endif
                    dbDataAdapter.SelectCommand = cmd;
                    DataTable dataTable = new DataTable();
                    dbDataAdapter.Fill(dataTable);
#if DEBUG
                    DateTime dt2 = DateTime.Now;

                    DbQueryDetailHelper.QueryDetail += DbQueryDetailHelper.GetQueryDetail(cmd.CommandText, dt1, dt2, cmd.Parameters);
#endif
                    return dataTable;
                }

            }
            catch
            {
                throw;
            }


        }

        public DbDataReader ExecuteReader(DbCommand cmd, DbTrans t)
        {
            cmd.Connection = t.Connection;
            if (t.Transaction != null) cmd.Transaction = t.Transaction;
            try
            {
#if DEBUG
                DateTime dt1 = DateTime.Now;
#endif
                DbDataReader reader = cmd.ExecuteReader();
#if DEBUG
                DateTime dt2 = DateTime.Now;

                DbQueryDetailHelper.QueryDetail += DbQueryDetailHelper.GetQueryDetail(cmd.CommandText, dt1, dt2, cmd.Parameters);
#endif
                return reader;
            }
            catch
            {
                throw;
            }
        }

        public int ExecuteNonQuery(DbCommand cmd, DbTrans t)
        {
            cmd.Connection = t.Connection;
            if (t.Transaction != null) cmd.Transaction = t.Transaction;
            try
            {
#if DEBUG
                DateTime dt1 = DateTime.Now;
#endif
                int ret = cmd.ExecuteNonQuery();
#if DEBUG
                DateTime dt2 = DateTime.Now;

                DbQueryDetailHelper.QueryDetail += DbQueryDetailHelper.GetQueryDetail(cmd.CommandText, dt1, dt2, cmd.Parameters);
#endif
                return ret;
            }
            catch
            {
                throw;
            }
        }

        public object ExecuteScalar(DbCommand cmd, DbTrans t)
        {
            cmd.Connection = t.Connection;
            if (t.Transaction != null) cmd.Transaction = t.Transaction;
            try
            {
#if DEBUG
                DateTime dt1 = DateTime.Now;
#endif
                object ret = cmd.ExecuteScalar();
                if (ret == DBNull.Value) ret = null;
#if DEBUG
                DateTime dt2 = DateTime.Now;

                DbQueryDetailHelper.QueryDetail += DbQueryDetailHelper.GetQueryDetail(cmd.CommandText, dt1, dt2, cmd.Parameters);
#endif
                return ret;
            }
            catch
            {
                throw;
            }
        }

        #endregion     
    }    
}

 在这个类中，加入了DEBUG的语句，目的是当调试的时候才运行代码，Relese的时候不会显示，这就是上面所说的只有Relese的版本不会显示调试信息的目的了。

其中GetQueryDetail这个类是直接返回HTML信息的，把SQL传入进去就会返回。

GetQueryDetail的具体实现在DbQueryDetailHelper这个类中：DbQueryDetailHelper.cs

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Common;
using System.IO;
using System.Text.RegularExpressions;

namespace MySoft.Data
{
    /// <summary>
    /// 数据库查询分析工具
    /// </summary>
    public class DbQueryDetailHelper
    {
        private static string m_querydetail = "";

        public static string QueryDetail
        {
            get { return m_querydetail; }
            set { m_querydetail = value; }
        }
        public static string GetQueryDetail(string commandText, DateTime dtStart, DateTime dtEnd, DbParameterCollection cmdParams)
        {
            string tr = "<tr style=\"background: rgb(255, 255, 255) none repeat scroll 0%; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;\">";
            string colums = "";
            string dbtypes = "";
            string values = "";
            string paramdetails = "";
            if (cmdParams != null && cmdParams.Count > 0)
            {
                foreach (DbParameter param in cmdParams)
                {
                    if (param == null)
                    {
                        continue;
                    }

                    colums += "<td>" + param.ParameterName + "</td>";
                    dbtypes += "<td>" + param.DbType.ToString() + "</td>";
                    values += "<td>" + param.Value.ToString() + "</td>";
                }
                paramdetails = string.Format("<table width=\"100%\" cellspacing=\"1\" cellpadding=\"0\" style=\"background: rgb(255, 255, 255) none repeat scroll 0%; margin-top: 5px; font-size: 12px; display: block; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;\">{0}{1}</tr>{0}{2}</tr>{0}{3}</tr></table>", tr, colums, dbtypes, values);
            }
            string outputstring = string.Format("<center><div style=\"border: 1px solid black; background:#FFF; margin: 2px; padding: 1em; text-align: left;  96%; clear: both;\"><div style=\"font-size: 12px; float: right;  100px; margin-bottom: 5px;\"><b>TIME:</b> {0}</div><span style=\"font-size: 12px;\">{1}{2}</span></div><br /></center>", dtEnd.Subtract(dtStart).TotalMilliseconds / 1000, commandText, paramdetails);

            //写入文件操作
            string debugsql = "";
            debugsql += "\r\nTIME:" + (dtEnd.Subtract(dtStart).TotalMilliseconds / 1000);
            debugsql += "\r\nSQL:" + commandText;
            if (cmdParams != null && cmdParams.Count > 0)
            {
                foreach (DbParameter param in cmdParams)
                {
                    if (param == null)
                    {
                        continue;
                    }

                    debugsql += "\r\nParameterName:" + param.ParameterName;
                    debugsql += " DbType:" + param.DbType.ToString();
                    debugsql += " Value:" + param.Value.ToString();
                }                
            }
            WriteDdbguSQLFile(debugsql);

            return outputstring;
        }

        private static void WriteDdbguSQLFile(string debugsql)
        {
            string logText = "\r\n-----------------------------------------------------------------------------------------";
            logText += "\r\n发生时间：" + DateTime.Now.ToString();
            logText += debugsql;
            logText += "\r\n-----------------------------------------------------------------------------------------\r\n";
            //日志物理路径
            string mapPath = AppDomain.CurrentDomain.BaseDirectory + "/DdbugSQLLog/";


            StreamWriter writer = null;
            try
            {
                //写入日志 
                string year = DateTime.Now.Year.ToString();
                string month = DateTime.Now.Month.ToString();
                string day = DateTime.Now.Day.ToString();
                string path = string.Empty;

                //得到文件夹完全路径
                string pathMonth = mapPath;

                //判断文件是否存在
                if (!Directory.Exists(pathMonth))//判断文件夹是否存在
                {
                    //创建月份文件夹
                    Directory.CreateDirectory(pathMonth);
                }

                //得到日志文件的名称
                string filename = DateTime.Now.ToString("yyyy-MM-dd") + ".log";

                //得到日志文件的完整路径
                path = pathMonth + "/" + filename;

                FileInfo file = new FileInfo(path);
                writer = new StreamWriter(file.FullName, true);//文件不在则创建，true表示追加
                writer.WriteLine(logText);
            }
            finally
            {
                if (writer != null)
                {
                    writer.Close();
                }
            }
        }
    }
}

得到HTML之后就是输出了，那么这个操作我就放在了DbQueryDetailHttpModule的类中，这是一个IHttpModule的类，目的是得到最后页面输出的HTML，把调试SQL信息嵌入到其中去:DbQueryDetailHttpModule.cs

using System;
using System.Web;
using System.IO;
using System.Text;
using System.Reflection;
using System.Diagnostics;
using System.Text.RegularExpressions;

namespace MySoft.Data
{
    /// <summary>
    /// Web在Debug模式下的HttpModule，用于显示数据库查询分析
    /// </summary>
    public class DbQueryDetailHttpModule : IHttpModule
    {
        public void Dispose()
        {
            // Do Nothing.
        }

        public void Init(HttpApplication context)
        {
#if DEBUG
            context.BeginRequest += new EventHandler(Application_BeginRequest);
            context.EndRequest += new EventHandler(Application_EndRequest);
#endif
        }

        protected void Application_BeginRequest(object sender, EventArgs e)
        {
            HttpApplication app = sender as HttpApplication;
            if ((app.Request.RawUrl.Contains(".aspx")))//这里看情况加上你的条件,要不然所有的请求都会被处理,会造成其它文件类型的输出异常.
            {
                app.Response.Filter = new ResponseFilter(app.Response.Filter);
            }
        }

        protected void Application_EndRequest(object sender, EventArgs e)
        {
            DbQueryDetailHelper.QueryDetail = "";
        }

    }

    public class ResponseFilter : Stream
    {
        private Stream _StreamFilter;

        private ResponseFilter() { }

        public ResponseFilter(Stream stream)
        {
            _StreamFilter = stream;
        }

        public override bool CanRead
        {
            get { return true; }
        }

        public override bool CanSeek
        {
            get { return true; }
        }

        public override bool CanWrite
        {
            get { return true; }
        }

        public override void Flush()
        {
            _StreamFilter.Flush();
        }

        public override long Length
        {
            get
            {
                return _StreamFilter.Length;
            }
        }

        public override long Position
        {
            get
            {
                return _StreamFilter.Position;
            }
            set
            {
                _StreamFilter.Position = value;
            }
        }

        public override int Read(byte[] buffer, int offset, int count)
        {
            return _StreamFilter.Read(buffer, offset, count);
        }

        public override long Seek(long offset, SeekOrigin origin)
        {
            return _StreamFilter.Seek(offset, origin);
        }

        public override void SetLength(long value)
        {
            _StreamFilter.SetLength(value);
        }

        public override void Write(byte[] buffer, int offset, int count)
        {
            string Html = Encoding.UTF8.GetString(buffer);
            buffer = Encoding.UTF8.GetBytes(Html.Replace("</body>", "<div>注意: 以下为数据查询分析工具，正式站点使用请使用Release编译。</div>" + DbQueryDetailHelper.QueryDetail + "</body>"));
            _StreamFilter.Write(buffer, offset, buffer.Length);
        }
    }

}

 这里就不多说了，直接看代码吧，原理也是替换（这里我在网上找了很久，终于发现这样写可以，就直接用了，囧）。

一切搞定之后，重新编译MoSoft.Data类库，最后在Web.Config文件添加HttpModule配置节：

<add name="DbQueryDetailHttpModule" type="MySoft.Data.DbQueryDetailHttpModule,MySoft.Data"/>

就这样，整个功能就能全部实现了，最后看一下我们输出的页面：

 

这些显示，完全和原版的一模一样，所以，我成功了移植了这个功能。哈哈

需要注意的是，这个功能只能适合Web开发，不使用桌面应用的开发。

所以，我又进行再一次的改装，就是直接输入SQL信息到文件中，每一次的访问都记录在日志文件中，这样就能用于桌面的应用开发了，具体的实现也是在DbQueryDetailHelper类中（看前面的类）。

输入的格式如图：

到此为止，整个实现过程就是这样子。其实这里的实现方法，可以应用于其它的ORM，看个人的喜爱了。

最后附上单独的文件和改装后的MySoft.Data。

单独文件：（链接: https://pan.baidu.com/s/1gftKdF1 密码: hmee）

针对MySoft.Data的替换方法：1、替换MySoft.Data\Data\Session\DbHelper.cs  2、在MySoft.Data\Common添加DbQueryDetail文件夹内容

改装后的MySoft.Data文件（2.7.5）：（链接: https://pan.baidu.com/s/1o8ia54m 密码: 7d6t）

使用方法：引入类库，在Web.Config添加HttpModules节：<add name="DbQueryDetailHttpModule" type="MySoft.Data.DbQueryDetailHttpModule,MySoft.Data"/>即可，注意一定要添加到最后面。

正版的MySoft.Data地址为：MySoft.Data

完整的示例项目：（链接: https://pan.baidu.com/s/1jIh2Fv4 密码: i6n3）