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