压缩前表大小:1057.688MB
压缩后表大小:63.289MB
vs2005为数据新建一个数据库工程。
Code
1using System;
2using System.Data;
3using System.Data.SqlClient;
4using System.Data.SqlTypes;
5using Microsoft.SqlServer.Server;
6using System.IO;
7using System.IO.Compression;
8using System.Text;
9
10public partial class Gzip
11{
12
13 [Microsoft.SqlServer.Server.SqlFunction]
14 public static SqlChars GzipToString(SqlBytes gBytes)
15 {
16 byte[] bytes = gBytes.Value;
17 bytes = Decompress(bytes);
18 string str = Encoding.GetEncoding(936).GetString(bytes);
19 SqlChars sqlchars = new SqlChars(str);
20 return (sqlchars);
21 }
22
23 [Microsoft.SqlServer.Server.SqlFunction]
24 public static SqlBytes StringToGzip(SqlChars chars)
25 {
26
27
28 byte[] bytes = Encoding.GetEncoding(936).GetBytes(chars.Buffer);
29 bytes = Compress(bytes);
30 SqlBytes gBytes = new SqlBytes(bytes);
31
32 return (gBytes);
33 }
34
35
36 采用.net系统自带Gzip压缩类进行流压缩#region 采用.net系统自带Gzip压缩类进行流压缩
37 /**//// <summary>
38 /// 压缩数据
39 /// </summary>
40 /// <param name="data"></param>
41 /// <returns></returns>
42 public static byte[] Compress(byte[] data)
43 {
44 byte[] bData;
45 MemoryStream ms = new MemoryStream();
46 GZipStream stream = new GZipStream(ms, CompressionMode.Compress, true);
47 stream.Write(data, 0, data.Length);
48 stream.Close();
49 stream.Dispose();
50 //必须把stream流关闭才能返回ms流数据,不然数据会不完整
51 //并且解压缩方法stream.Read(buffer, 0, buffer.Length)时会返回0
52 bData = ms.ToArray();
53 ms.Close();
54 ms.Dispose();
55 return bData;
56 }
57
58 /**//// <summary>
59 /// 解压数据
60 /// </summary>
61 /// <param name="data"></param>
62 /// <returns></returns>
63 public static byte[] Decompress(byte[] data)
64 {
65 byte[] bData;
66 MemoryStream ms = new MemoryStream();
67 ms.Write(data, 0, data.Length);
68 ms.Position = 0;
69 GZipStream stream = new GZipStream(ms, CompressionMode.Decompress, true);
70 byte[] buffer = new byte[1024];
71 MemoryStream temp = new MemoryStream();
72 int read = stream.Read(buffer, 0, buffer.Length);
73 while (read > 0)
74 {
75 temp.Write(buffer, 0, read);
76 read = stream.Read(buffer, 0, buffer.Length);
77 }
78 //必须把stream流关闭才能返回ms流数据,不然数据会不完整
79 stream.Close();
80 stream.Dispose();
81 ms.Close();
82 ms.Dispose();
83 bData = temp.ToArray();
84 temp.Close();
85 temp.Dispose();
86 return bData;
87
88 }
89 #endregion
90
91
92}
93
94
给数据库增加一个varbinary(MAX) 字段,把压缩以后的转移过来以后删除原来的字段。
然后使用这两个clr函数
select:
SELECT top 10 dbo.GzipToString([content1]) FROM [content_02]
insert:
insert into [content_02] ([content1]) values(dbo.StringToGzip('123abc'))
原文:http://hi.baidu.com/shujuzhuanhuan/blog/item/5d7991479fe83f3687947382.html
转载应指明出处!