.NET COM $ P $ XML的pssion存储在SQL Server数据库 [英] .NET compression of XML to store in SQL Server database

问题描述

目前我们的.NET应用程序在内存中，我们坚持到SQL Server数据库构造XML数据。所述的XElement对象是使用的ToString（），然后存储在DB中一个varchar（MAX）柱转换为字符串。我们dind't要使用SQL XML数据类型，因为我们不需要任何验证和SQL并不需要在任何阶段来查询XML。

Currently our .NET application constructs XML data in memory that we persist to a SQL Server database. The XElement object is converted to a string using ToString() and then stored in a varchar(MAX) column in the DB. We dind't want to use the SQL XML datatype as we didn't need any validation and SQL doesn't need to query the XML at any stage.

尽管这个实现工作得很好，我们想通过COM $ P $，以减少数据库的大小，然后进行存放pssing的XML和DECOM $ P $取回后pssing它。没有人有任何样本code为COM pressing一个的XElement对象（和DECOM pressing将是巨大的）？另外，我需要对数据库列的数据类型，以便我们能够充分利用这个COM pression利用什么样的变化？

Although this implementation works fine, we want to reduce the size of the database by compressing the XML before storing it, and decompressing it after retrieving it. Does anyone have any sample code for compressing an XElement object (and decompressing would be great too)? Also, what changes would I need to make to the data type of the database column so that we can fully take advantage of this compression?

我再次调查XML数据类型的SQL Server 2005提供和验证的开销它提供的是太高，我们考虑使用它。此外，虽然它确实COM preSS的XML一些，它没有尽可能多的COM pression作为.NET DeflateStream类。

I have investigated again the XML datatype SQL Server 2005 offers, and the validation overhead it offers is too high for us to consider using it. Also, although it does compress the XML somewhat, it doesn't as much compression as the .NET DeflateStream class.

我已经写我们使用磁盘的XML，然后保存comrpessed版本作为新的文件测试DeflateStream类。结果是巨大的，一个16KB的文件，下降到一个3KB的文件，因此它仅仅指刚获得这种情况下要在内存中工作并保存得到的数据到数据库。没有人有任何样本code做COM pression，我应该改变varcahr（MAX）科拉姆输入到也许VARBINARY？

I have tested the DeflateStream class by writing the XML we use to disk, and then saving the comrpessed version as a new file. The results are great, a 16kb file goes down to a 3kb file, so it's jsut a case of getting this to work in memory and saving the resulting data to the DB. Does anyone have any sample code to do the compression, and should I change the varcahr(MAX) colum to type to maybe varbinary?

在此先感谢

推荐答案

本文可以帮助你得到一个开始。

This article may help you get a start.

下面的代码片段可以融为一体preSS一个字符串，并返回一个base-64 codeD结果：

The following snippet can compress a string and return a base-64 coded result:

public static string Compress(string text)
{
 byte[] buffer = Encoding.UTF8.GetBytes(text);
 MemoryStream ms = new MemoryStream();
 using (GZipStream zip = new GZipStream(ms, CompressionMode.Compress, true))
 {
  zip.Write(buffer, 0, buffer.Length);
 }

 ms.Position = 0;
 MemoryStream outStream = new MemoryStream();

 byte[] compressed = new byte[ms.Length];
 ms.Read(compressed, 0, compressed.Length);

 byte[] gzBuffer = new byte[compressed.Length + 4];
 System.Buffer.BlockCopy(compressed, 0, gzBuffer, 4, compressed.Length);
 System.Buffer.BlockCopy(BitConverter.GetBytes(buffer.Length), 0, gzBuffer, 0, 4);
 return Convert.ToBase64String (gzBuffer);
}

编辑：顺便说一句，你可能需要存储XML为文本时使用CLOB格式，甚至因为VARCHAR处理有一个非常有限的长度 - 这是XML往往能很快超过

As an aside, you may want to use CLOB formats even when storing XML as text because varchars have a very limited length - which XML can often quickly exceed.

这篇关于.NET COM $ P $ XML的pssion存储在SQL Server数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！