当我们用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; width: 96%; clear: both;\"><div style=\"font-size: 12px; float: right; width: 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)