`
touchinsert
  • 浏览: 1280058 次
  • 性别: Icon_minigender_1
  • 来自: 北京
文章分类
社区版块
存档分类
最新评论

用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

我们可以自己用开发工具来实现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

分享到:
评论

相关推荐

Global site tag (gtag.js) - Google Analytics