位置:海鸟网 > IT > ASP.NET >

用日志记录LINQ中的所有增删改的SQL语句的方法

我们知道LINQ中的增删改都要调用SubmitChanges方法,我们记录所有SQL的方式就是重写(override)DataContext中的SubmitChanges方法,为了避免每次修改dbml文件时影响我们自己写的内容,我们要先写一个DataContext的分布类,在这个类中重写SubmitChanges方法。
代码如下
Code


public partial class DataClasses1DataContext
    {
        public override void SubmitChanges(System.Data.Linq.ConflictMode failureMode)
        {
            //记录日志(每天一个文件,记录所有更改sql,日志会存在第一个盘的log文件夹下)
            string directory = Path.Combine(Directory.GetLogicalDrives().First(), "log");
            Directory.CreateDirectory(directory);
            string logFile = Path.Combine(directory,
                "log" + DateTime.Now.ToLongDateString() + ".txt");
            using (StreamWriter w = File.AppendText(logFile))
            {
              
                w.WriteLine("发生时间:{0}", DateTime.Now.ToString());
                w.WriteLine("日志内容为:");
                this.Log = w;
                try
                {
                    base.SubmitChanges(failureMode);
                }
                catch (Exception e)
                {
                    w.WriteLine("异常:" + e.Message + e.StackTrace);
                    w.WriteLine("--------------------------------------------------------------");

                    throw;
                }
                finally
                {
                    this.Log = null;
                }
                w.WriteLine("--------------------------------------------------------------");

            }


        }
    }

如果想把sql语句全部记录到数据库的代码如下:

Code

public partial class DataClasses1DataContext
    {
        StringBuilder sb = new StringBuilder();
            using (StringWriter sw = new StringWriter(sb))
            {

                sw.WriteLine("发生时间:{0}", DateTime.Now.ToString());
                sw.WriteLine("日志内容为:");
                this.Log = sw;
                try
                {
                    base.SubmitChanges(failureMode);
                    string sqlStr = "insert into logTable(Content)values( '"+sb.ToString()+"')";
                    //SqlConnection con=
                    using (SqlConnection con=new SqlConnection(this.Connection.ConnectionString))
                    {
                        con.Open();
                        SqlCommand cmd = new SqlCommand(sqlStr, con);
                        cmd.ExecuteNonQuery();
                    }

                }
                catch (Exception e)
                {
                    //记录日志(每天一个文件,记录所有更改sql,日志会存在第一个盘的log文件夹下)

                    string directory = Path.Combine(Directory.GetLogicalDrives().First(), "log");
                    Directory.CreateDirectory(directory);
                    string logFile = Path.Combine(directory,
                        "log" + DateTime.Now.ToLongDateString() + ".txt");
                    using (StreamWriter w = File.AppendText(logFile))
                    {
                        w.WriteLine("发生时间:{0}", DateTime.Now.ToString());
                        w.WriteLine("日志内容为:");
                        w.WriteLine(e.Message);

                    }
                }
                finally
                {
                    this.Log = null;
                }
    }

源码下载