建立一个sql日志表

 第一步建立一个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);