用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条数据之后把表清空

usemaster
go
createdatabasedbLogTest
go
usedbLogTest
go
createtablelog_test(idint,codechar(10),namevarchar(20),datedatetime,memovarchar(100))
insertintolog_testselect100,'id001','jinjazz',getdate(),'剪刀'
insertintolog_testselect65549,'id002','游客',getdate()-1,'这家伙很懒，没有设置昵称'
insertintolog_testselect-999,'id003','这家伙来自火星',getdate()-1000,'a'
deletefromlog_test
--usemaster
--go
--dropdatabasedbLogTest

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

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

usingSystem;
usingSystem.Collections.Generic;
usingSystem.Text;
namespaceConsoleApplication21
{
classProgram
{
///<summary>
///分析sql2005日志，找回被delete的数据，引用请保留以下信息
///作者：jinjazz(csdn的剪刀)
///作者blog：http://blog.csdn.net/jinjazz
///</summary>
///<paramname="args"></param>
staticvoidMain(string[]args)
{
using(System.Data.SqlClient.SqlConnectionconn=newSystem.Data.SqlClient.SqlConnection())
{
conn.ConnectionString="server=localhost;uid=sa;pwd=sqlgis;database=dbLogTest";
conn.Open();
using(System.Data.SqlClient.SqlCommandcommand=conn.CreateCommand())
{
//察看dbo.log_test对象的sql日志
command.CommandText=@"SELECTallocunitname,operation,[RowLogContents0]asr0,[RowLogContents1]asr1
from::fn_dblog(null,null)
whereallocunitnamelike'dbo.log_test%'and
operationin('LOP_INSERT_ROWS','LOP_DELETE_ROWS')";
System.Data.SqlClient.SqlDataReaderreader=command.ExecuteReader();
//根据表字段的顺序建立字段数组
Datacolumn[]columns=newDatacolumn[]
{
newDatacolumn("id",System.Data.SqlDbType.Int),
newDatacolumn("code",System.Data.SqlDbType.Char,10),
newDatacolumn("name",System.Data.SqlDbType.VarChar),
newDatacolumn("date",System.Data.SqlDbType.DateTime),
newDatacolumn("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(Datacolumncincolumns)
{
Console.WriteLine("{0}={1}",c.Name,c.Value);
}
Console.WriteLine();
}
catch
{
//to-do...
}
}
reader.Close();
}
conn.Close();
}
Console.WriteLine("************************日志分析完成");
Console.ReadLine();
}
//自定义的column结构
publicclassDatacolumn
{
publicstringName;
publicSystem.Data.SqlDbTypeDataType;
publicshortLength=-1;
publicobjectValue=null;
publicDatacolumn(stringname,System.Data.SqlDbTypetype)
{
Name=name;
DataType=type;
}
publicDatacolumn(stringname,System.Data.SqlDbTypetype,shortlength)
{
Name=name;
DataType=type;
Length=length;
}
}
///<summary>
///sql二进制结构翻译，这个比较关键，测试环境为sql2005，其他版本没有测过。
///</summary>
///<paramname="data"></param>
///<paramname="columns"></param>
staticvoidTranslateData(byte[]data,Datacolumn[]columns)
{
//我只根据示例写了Char,DateTime,Int三种定长度字段和varchar一种不定长字段，其余的有兴趣可以自己补充
//这里没有暂时没有考虑Null和空字符串两种情况，以后会补充。
//引用请保留以下信息：
//作者：jinjazz
//sql的数据行二进制结构参考我的blog
//http://blog.csdn.net/jinjazz/archive/2008/08/07/2783872.aspx
//行数据从第5个字节开始
shortindex=4;
//先取定长字段
foreach(Datacolumncincolumns)
{
switch(c.DataType)
{
caseSystem.Data.SqlDbType.Char:
//读取定长字符串，需要根据表结构指定长度
c.Value=System.Text.Encoding.Default.GetString(data,index,c.Length);
index+=c.Length;
break;
caseSystem.Data.SqlDbType.DateTime:
//读取datetime字段，sql为8字节保存
System.DateTimedate=newDateTime(1900,1,1);
//前四位1/300秒保存
intsecond=BitConverter.ToInt32(data,index);
date=date.AddSeconds(second/300);
index+=4;
//后四位1900-1-1的天数
intdays=BitConverter.ToInt32(data,index);
date=date.AddDays(days);
index+=4;
c.Value=date;
break;
caseSystem.Data.SqlDbType.Int:
//读取int字段,为4个字节保存
c.Value=BitConverter.ToInt32(data,index);
index+=4;
break;
default:
//忽略不定长字段和其他不支持以及不愿意考虑的字段
break;
}
}
//跳过三个字节
index+=3;
//取变长字段的数量,保存两个字节
shortvarColumnCount=BitConverter.ToInt16(data,index);
index+=2;
//接下来,每两个字节保存一个变长字段的结束位置,
//所以第一个变长字段的开始位置可以算出来
shortstartIndex=(short)(index+varColumnCount*2);
//第一个变长字段的结束位置也可以算出来
shortendIndex=BitConverter.ToInt16(data,index);
//循环变长字段列表读取数据
foreach(Datacolumncincolumns)
{
switch(c.DataType)
{
caseSystem.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-718:14:03
memo=剪刀
数据对象dbo.log_test的LOP_INSERT_ROWS操作：
id=65549
code=id002
name=游客
date=2008-8-618:14:03
memo=这家伙很懒，没有设置昵称
数据对象dbo.log_test的LOP_INSERT_ROWS操作：
id=-999
code=id003
name=这家伙来自火星
date=2005-11-1118:14:03
memo=a
数据对象dbo.log_test的LOP_DELETE_ROWS操作：
id=100
code=id001
name=jinjazz
date=2008-8-718:14:03
memo=剪刀
数据对象dbo.log_test的LOP_DELETE_ROWS操作：
id=65549
code=id002
name=游客
date=2008-8-618:14:03
memo=这家伙很懒，没有设置昵称
数据对象dbo.log_test的LOP_DELETE_ROWS操作：
id=-999
code=id003
name=这家伙来自火星
date=2005-11-1118:14:03
memo=a
************************日志分析完成

试验成功~~