用c#读取并分析sql2005日志

用过logExplorer的朋友都会被他强悍的功能吸引，我写过一篇详细的操作文档可以参考
http://blog.csdn.net/jinjazz/archive/2008/05/19/2459692.aspx

我们可以自己用开发工具来实现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'
  12. delete from log_test
  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;
  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')";
  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和空字符串两种情况，以后会补充。
  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. }
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 = 剪刀
  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 = 这家伙很懒，没有设置昵称
  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
  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 = 剪刀
  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 = 这家伙很懒，没有设置昵称
  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
  43. ************************日志分析完成

试验成功~~

相关文档：
SQL2005数据行的二进制结构
LogExplore的一个详细操作手册
【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/cl1024cl/p/6204884.html