第一步建立一个sql日志表
CREATE TABLE [dbo].[my_sqllog]( [id] [bigint] IDENTITY(1,1) NOT NULL, [hit] [bigint] NULL, [sqltext] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [paramdetails] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [begintime] [datetime] NULL, [endtime] [datetime] NULL, [fromurl] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL, [ip] [varchar](20) COLLATE Chinese_PRC_CI_AS NULL, [lastelapsedtime] [bigint] NULL, CONSTRAINT [PK_my_sqllog] PRIMARY KEY CLUSTERED ( [id] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY]
记录sql语句、此sql语句被执行次数,参数及值,记录开始时间,结束时间,来自哪个页面,ip和此条语句执行时间
第二步在sqlhelper里写记录代码
public static int ExecuteSqlLog(CommandType commandType, string commandText, params DbParameter[] cmdParams) { #region 参数处理 string colums = ""; string dbtypes = ""; string values = ""; string paramdetails = ""; if (cmdParams != null && cmdParams.Length > 0) { foreach (DbParameter param in cmdParams) { if (param == null) { continue; } colums += param.ParameterName + " "; dbtypes += param.DbType + " "; values += param.Value + ";"; } paramdetails = string.Format(" {0},{1},{2}", colums, dbtypes, values); } string fromurl = ""; if (System.Web.HttpContext.Current!=null) { fromurl = System.Web.HttpContext.Current.Request.Url.ToString(); } // commandText = commandText.Replace("'","‘").Replace(";",";"); SqlParameter[] parameters = new SqlParameter[] { new SqlParameter("@hit",1), new SqlParameter("@sqltext",commandText), new SqlParameter("@paramdetails",paramdetails), new SqlParameter("@begintime",DateTime.Now), new SqlParameter("@endtime",DateTime.Now), new SqlParameter("@fromurl",fromurl), new SqlParameter("@ip",Web.PressRequest.GetIP()), new SqlParameter("@lastelapsedtime",0), }; #endregion using (DbConnection connection = Factory.CreateConnection()) { connection.ConnectionString = GetRealConnectionString(commandText);//ConnectionString; string sql = ""; // 执行DbCommand命令,并返回结果. int id = Utils.TypeConverter.ObjectToInt(ExecuteScalarLog(CommandType.Text, "select top 1 id from my_sqllog where sqltext=@sqltext", new SqlParameter("@sqltext", commandText))); if (id > 0) { sql = "update my_sqllog set hit=hit+1,ip=@ip,endtime=@endtime,fromurl=@fromurl where id=" + id; } else { sql = "insert into my_sqllog(hit,sqltext,paramdetails,begintime,endtime,fromurl,ip,lastelapsedtime) values(@hit,@sqltext,@paramdetails,@begintime,@endtime,@fromurl,@ip,@lastelapsedtime)"; } // 创建DbCommand命令,并进行预处理 DbCommand cmd = Factory.CreateCommand(); bool mustCloseConnection = false; PrepareCommand(cmd, connection, (DbTransaction)null, commandType, sql, parameters, out mustCloseConnection); // 执行DbCommand命令,并返回结果. int retval = cmd.ExecuteNonQuery(); // 清除参数,以便再次使用. cmd.Parameters.Clear(); if (mustCloseConnection) connection.Close(); return retval; } }
第三部在你的每个执行sql语句的方法里加入以下代码,不管是ExecuteScalar、ExecuteReader还是ExecuteNonQuery等等都加上
//执行sql之前进行日志记录操纵 int log = ExecuteSqlLog(CommandType.Text, commandText, commandParameters);