c#读取并分析sqlServer据库日志（转）

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

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

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

use master
go
create database dbLogTest
go
use  dbLogTest
go
create table log_test(id int ,code char(10),name varchar(20),date datetime,memo varchar(100))
insert into log_test select 100, ´id001´,´jinjazz´,getdate(),´剪刀´
insert into log_test select 65549,´id002´,´游客´,getdate()-1,´这家伙很懒，没有设置昵称´
insert into log_test select -999,´id003´,´这家伙来自火星´,getdate()-1000,´a´

delete from log_test

－－use master 
－－go
－－drop database dbLogTest

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

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

using System;
using System.Collections.Generic;
using System.Text;

namespace ConsoleApplication21
{
    class Program
    { 

        static void Main(string[] args)
        {
            using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection())
            {
                conn.ConnectionString = "server=localhost;uid=sa;pwd=sqlgis;database=dbLogTest";
                conn.Open();
                using (System.Data.SqlClient.SqlCommand command = conn.CreateCommand())
                {
                    //察看dbo.log_test对象的sql日志
                    command.CommandText = @"SELECT allocunitname,operation,[RowLog Contents 0] as r0,[RowLog Contents 1]as r1 
                                from::fn_dblog (null, null)   
                                where allocunitname like ´dbo.log_test%´and
                                operation in(´LOP_INSERT_ROWS´,´LOP_DELETE_ROWS´)";

                    System.Data.SqlClient.SqlDataReader reader = command.ExecuteReader();
                    //根据表字段的顺序建立字段数组
                    Datacolumn[] columns = new Datacolumn[]
                        {
                            new Datacolumn("id", System.Data.SqlDbType.Int),
                            new Datacolumn("code", System.Data.SqlDbType.Char,10),
                            new Datacolumn("name", System.Data.SqlDbType.VarChar),
                            new Datacolumn("date", System.Data.SqlDbType.DateTime),
                            new Datacolumn("memo", System.Data.SqlDbType.VarChar)
                        };
                    //循环读取日志
                    while (reader.Read())
                    {
                        byte[] data = (byte[])reader["r0"];
                        
                        try
                        {
                            //把二进制数据结构转换为明文
                            TranslateData(data, columns);
                            Console.WriteLine("数据对象{1}的{0}操作：", reader["operation"], reader["allocunitname"]);
                            foreach (Datacolumn c in columns)
                            {
                                Console.WriteLine("{0} = {1}", c.Name, c.Value);
                            }
                            Console.WriteLine();
                        }
                        catch
                        {
                            //to-do...
                        }
                        
                    }
                    reader.Close();
                }
                conn.Close();
            }
            Console.WriteLine("************************日志分析完成");
            Console.ReadLine();
        }
        //自定义的column结构
        public class Datacolumn
        {
            public string Name;
            public System.Data.SqlDbType DataType;
            public short Length = -1;
            public object Value = null;
            public Datacolumn(string name, System.Data.SqlDbType type)
            {
                Name = name;
                DataType = type;
            }
            public Datacolumn(string name,System.Data.SqlDbType type,short length)
            {
                Name = name;
                DataType = type;
                Length = length;
            }
        }
        /// <summary>
        /// sql二进制结构翻译，这个比较关键，测试环境为sql2005，其他版本没有测过。
        /// </summary>
        /// <param name="data"></param>
        /// <param name="columns"></param>
        static void TranslateData(byte[] data, Datacolumn[] columns)
        {
            //我只根据示例写了Char,DateTime,Int三种定长度字段和varchar一种不定长字段，其余的有兴趣可以自己补充
            //这里没有暂时没有考虑Null和空字符串两种情况，以后会补充。

            //引用请保留以下信息：
            //作者：jinjazz 
            //sql的数据行二进制结构参考我的blog
            //http://blog.csdn.net/jinjazz/archive/2008/08/07/2783872.aspx
            //行数据从第5个字节开始
            short index = 4;
            //先取定长字段
            foreach (Datacolumn c in columns)
            {
                switch (c.DataType)
                {
                    case System.Data.SqlDbType.Char:
                        //读取定长字符串，需要根据表结构指定长度
                        c.Value = System.Text.Encoding.Default.GetString(data,index,c.Length);
                        index += c.Length;
                        break;
                    case System.Data.SqlDbType.DateTime:
                        //读取datetime字段，sql为8字节保存
                        System.DateTime date = new DateTime(1900, 1, 1);
                        //前四位1/300秒保存
                        int second = BitConverter.ToInt32(data, index);
                        date = date.AddSeconds(second/300);
                        index += 4;
                        //后四位1900-1-1的天数
                        int days = BitConverter.ToInt32(data, index);
                        date=date.AddDays(days);
                        index += 4;
                        c.Value = date;
                        break;
                    case System.Data.SqlDbType.Int:
                        //读取int字段,为4个字节保存
                        c.Value = BitConverter.ToInt32(data, index);
                        index += 4;
                        break;
                   default:
                       //忽略不定长字段和其他不支持以及不愿意考虑的字段
                        break;
                }
            }
            //跳过三个字节
            index += 3;
            //取变长字段的数量,保存两个字节
            short varColumnCount = BitConverter.ToInt16(data, index);
            index += 2;
            //接下来,每两个字节保存一个变长字段的结束位置,
            //所以第一个变长字段的开始位置可以算出来
            short startIndex =(short)( index + varColumnCount * 2);
            //第一个变长字段的结束位置也可以算出来
            short endIndex = BitConverter.ToInt16(data, index);
            //循环变长字段列表读取数据
            foreach (Datacolumn c in columns)
            {
                switch (c.DataType)
                {
                    case System.Data.SqlDbType.VarChar:
                        //根据开始和结束位置，可以算出来每个变长字段的值
                        c.Value =System.Text.Encoding.Default.GetString(data, startIndex, endIndex - startIndex);
                        //下一个变长字段的开始位置
                        startIndex = endIndex;
                        //获取下一个变长字段的结束位置
                        index += 2;
                        endIndex = BitConverter.ToInt16(data, index);
                        break;
                    default:
                        //忽略定长字段和其他不支持以及不愿意考虑的字段
                        break;
                }
            }
            //获取完毕
        }
    }
}

4、更改你的sql连接字符串后运行以上代码，会看到如下输出信息：

数据对象dbo.log_test的LOP_INSERT_ROWS操作：
id = 100
code = id001
name = jinjazz
date = 2008-8-7 18:14:03
memo = 剪刀

数据对象dbo.log_test的LOP_INSERT_ROWS操作：
id = 65549
code = id002
name = 游客
date = 2008-8-6 18:14:03
memo = 这家伙很懒，没有设置昵称

数据对象dbo.log_test的LOP_INSERT_ROWS操作：
id = -999
code = id003
name = 这家伙来自火星
date = 2005-11-11 18:14:03
memo = a

数据对象dbo.log_test的LOP_DELETE_ROWS操作：
id = 100
code = id001
name = jinjazz
date = 2008-8-7 18:14:03
memo = 剪刀

数据对象dbo.log_test的LOP_DELETE_ROWS操作：
id = 65549
code = id002
name = 游客
date = 2008-8-6 18:14:03
memo = 这家伙很懒，没有设置昵称

数据对象dbo.log_test的LOP_DELETE_ROWS操作：
id = -999
code = id003
name = 这家伙来自火星
date = 2005-11-11 18:14:03
memo = a

************************日志分析完成