用c#读取并分析sql2005日志

 

我们可以自己用开发工具来实现sql日志的读取，这个应用还是很酷的，具体思路

1、首先要了解一个没有公开的系统函数::fn_dblog，他可以读取sql日志，并返回二进制的行数据
2、然后要了解sql的二进制数据是如何存储的，这个可以参考我的blog文章
http://blog.csdn.net/jinjazz/archive/2008/08/07/2783872.aspx
3、用自己擅长的开发工具来分析数据，得到我们需要的信息

我用c#写了一个测试样例，分析了int，char，datetime和varchar的日志情况而且没有考虑null和空字符串的保存，希望感兴趣的朋友能和我一起交流打造属于自己的日志分析工具

详细的试验步骤以及代码如下：

1、首先建立sqlserver的测试环境，我用的sql2005，这个过程不能保证在之前的版本中运行
以下sql语句会建立一个dbLogTest数据库，并建立一张log_test表，然后插入3条数据之后把表清空

1.use master
2.go
3.create database dbLogTest
4.go
5.use  dbLogTest
6.go
7.create table log_test(id int ,code char(10),name varchar(20),date datetime,memo varchar(100))
8.insert into log_test select 100, 'id001','jinjazz',getdate(),'剪刀'
9.insert into log_test select 65549,'id002','游客',getdate()-1,'这家伙很懒，没有设置昵称'
10.insert into log_test select -999,'id003','这家伙来自火星',getdate()-1000,'a'
11.
12.delete from log_test
13.
14.--use master
15.--go
16.--drop database dbLogTest
17.

2、我们最终的目的是要找到被我们删掉的数据

3、分析日志的c#代码:我已经尽量详细的写了注释

1.using System;
2.using System.Collections.Generic;
3.using System.Text;
4.
5.namespace ConsoleApplication21
6.{
7.    class Program
8.    {
9.        /// <summary>
10.        /// 分析sql2005日志，找回被delete的数据，引用请保留以下信息
11.        /// 作者：jinjazz (csdn的剪刀)
12.        /// 作者blog：http://blog.csdn.net/jinjazz
13.        /// </summary>
14.        /// <param name="args"></param>
15.        static void Main(string[] args)
16.        {
17.            using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection())
18.            {
19.                conn.ConnectionString = "server=localhost;uid=sa;pwd=sqlgis;database=dbLogTest";
20.                conn.Open();
21.                using (System.Data.SqlClient.SqlCommand command = conn.CreateCommand())
22.                {
23.                    //察看dbo.log_test对象的sql日志
24.                    command.CommandText = @"SELECT allocunitname,operation,[RowLog Contents 0] as r0,[RowLog Contents 1]as r1
25.                                from::fn_dblog (null, null)  
26.                                where allocunitname like 'dbo.log_test%'and
27.                                operation in('LOP_INSERT_ROWS','LOP_DELETE_ROWS')";
28.
29.                    System.Data.SqlClient.SqlDataReader reader = command.ExecuteReader();
30.                    //根据表字段的顺序建立字段数组
31.                    Datacolumn[] columns = new Datacolumn[]
32.                        {
33.                            new Datacolumn("id", System.Data.SqlDbType.Int),
34.                            new Datacolumn("code", System.Data.SqlDbType.Char,10),
35.                            new Datacolumn("name", System.Data.SqlDbType.VarChar),
36.                            new Datacolumn("date", System.Data.SqlDbType.DateTime),
37.                            new Datacolumn("memo", System.Data.SqlDbType.VarChar)
38.                        };
39.                    //循环读取日志
40.                    while (reader.Read())
41.                    {
42.                        byte[] data = (byte[])reader["r0"];
43.                       
44.                        try
45.                        {
46.                            //把二进制数据结构转换为明文
47.                            TranslateData(data, columns);
48.                            Console.WriteLine("数据对象{1}的{0}操作：", reader["operation"], reader["allocunitname"]);
49.                            foreach (Datacolumn c in columns)
50.                            {
51.                                Console.WriteLine("{0} = {1}", c.Name, c.Value);
52.                            }
53.                            Console.WriteLine();
54.                        }
55.                        catch
56.                        {
57.                            //to-do...
58.                        }
59.                       
60.                    }
61.                    reader.Close();
62.                }
63.                conn.Close();
64.            }
65.            Console.WriteLine("************************日志分析完成");
66.            Console.ReadLine();
67.        }
68.        //自定义的column结构
69.        public class Datacolumn
70.        {
71.            public string Name;
72.            public System.Data.SqlDbType DataType;
73.            public short Length = -1;
74.            public object Value = null;
75.            public Datacolumn(string name, System.Data.SqlDbType type)
76.            {
77.                Name = name;
78.                DataType = type;
79.            }
80.            public Datacolumn(string name,System.Data.SqlDbType type,short length)
81.            {
82.                Name = name;
83.                DataType = type;
84.                Length = length;
85.            }
86.        }
87.        /// <summary>
88.        /// sql二进制结构翻译，这个比较关键，测试环境为sql2005，其他版本没有测过。
89.        /// </summary>
90.        /// <param name="data"></param>
91.        /// <param name="columns"></param>
92.        static void TranslateData(byte[] data, Datacolumn[] columns)
93.        {
94.            //我只根据示例写了Char,DateTime,Int三种定长度字段和varchar一种不定长字段，其余的有兴趣可以自己补充
95.            //这里没有暂时没有考虑Null和空字符串两种情况，以后会补充。
96.
97.            //引用请保留以下信息：
98.            //作者：jinjazz
99.            //sql的数据行二进制结构参考我的blog
100.            //http://blog.csdn.net/jinjazz/archive/2008/08/07/2783872.aspx
101.            //行数据从第5个字节开始
102.            short index = 4;
103.            //先取定长字段
104.            foreach (Datacolumn c in columns)
105.            {
106.                switch (c.DataType)
107.                {
108.                    case System.Data.SqlDbType.Char:
109.                        //读取定长字符串，需要根据表结构指定长度
110.                        c.Value = System.Text.Encoding.Default.GetString(data,index,c.Length);
111.                        index += c.Length;
112.                        break;
113.                    case System.Data.SqlDbType.DateTime:
114.                        //读取datetime字段，sql为8字节保存
115.                        System.DateTime date = new DateTime(1900, 1, 1);
116.                        //前四位1/300秒保存
117.                        int second = BitConverter.ToInt32(data, index);
118.                        date = date.AddSeconds(second/300);
119.                        index += 4;
120.                        //后四位1900-1-1的天数
121.                        int days = BitConverter.ToInt32(data, index);
122.                        date=date.AddDays(days);
123.                        index += 4;
124.                        c.Value = date;
125.                        break;
126.                    case System.Data.SqlDbType.Int:
127.                        //读取int字段,为4个字节保存
128.                        c.Value = BitConverter.ToInt32(data, index);
129.                        index += 4;
130.                        break;
131.                   default:
132.                       //忽略不定长字段和其他不支持以及不愿意考虑的字段
133.                        break;
134.                }
135.            }
136.            //跳过三个字节
137.            index += 3;
138.            //取变长字段的数量,保存两个字节
139.            short varColumnCount = BitConverter.ToInt16(data, index);
140.            index += 2;
141.            //接下来,每两个字节保存一个变长字段的结束位置,
142.            //所以第一个变长字段的开始位置可以算出来
143.            short startIndex =(short)( index + varColumnCount * 2);
144.            //第一个变长字段的结束位置也可以算出来
145.            short endIndex = BitConverter.ToInt16(data, index);
146.            //循环变长字段列表读取数据
147.            foreach (Datacolumn c in columns)
148.            {
149.                switch (c.DataType)
150.                {
151.                    case System.Data.SqlDbType.VarChar:
152.                        //根据开始和结束位置，可以算出来每个变长字段的值
153.                        c.Value =System.Text.Encoding.Default.GetString(data, startIndex, endIndex - startIndex);
154.                        //下一个变长字段的开始位置
155.                        startIndex = endIndex;
156.                        //获取下一个变长字段的结束位置
157.                        index += 2;
158.                        endIndex = BitConverter.ToInt16(data, index);
159.                        break;
160.                    default:
161.                        //忽略定长字段和其他不支持以及不愿意考虑的字段
162.                        break;
163.                }
164.            }
165.            //获取完毕
166.        }
167.    }
168.}
169.
4、更改你的sql连接字符串后运行以上代码，会看到如下输出信息：

1.数据对象dbo.log_test的LOP_INSERT_ROWS操作：
2.id = 100
3.code = id001
4.name = jinjazz
5.date = 2008-8-7 18:14:03
6.memo = 剪刀
7.
8.数据对象dbo.log_test的LOP_INSERT_ROWS操作：
9.id = 65549
10.code = id002
11.name = 游客
12.date = 2008-8-6 18:14:03
13.memo = 这家伙很懒，没有设置昵称
14.
15.数据对象dbo.log_test的LOP_INSERT_ROWS操作：
16.id = -999
17.code = id003
18.name = 这家伙来自火星
19.date = 2005-11-11 18:14:03
20.memo = a
21.
22.数据对象dbo.log_test的LOP_DELETE_ROWS操作：
23.id = 100
24.code = id001
25.name = jinjazz
26.date = 2008-8-7 18:14:03
27.memo = 剪刀
28.
29.数据对象dbo.log_test的LOP_DELETE_ROWS操作：
30.id = 65549
31.code = id002
32.name = 游客
33.date = 2008-8-6 18:14:03
34.memo = 这家伙很懒，没有设置昵称
35.
36.数据对象dbo.log_test的LOP_DELETE_ROWS操作：
37.id = -999
38.code = id003
39.name = 这家伙来自火星
40.date = 2005-11-11 18:14:03
41.memo = a
42.
43.************************日志分析完成
44.

试验成功~~

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/jinjazz/archive/2008/08/07/2783909.aspx