.NET压缩XML以存储在SQL Server数据库中 [英] .NET compression of XML to store in SQL Server database

问题描述

目前，我们的.NET应用程序在内存中构建XML数据，并将其保存到SQL Server数据库中。使用ToString（）将XElement对象转换为字符串，然后存储在DB中的varchar（MAX）列中。我们不想使用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.

实现工作正常，我们希望通过在存储之前压缩XML来减小数据库的大小，并在检索之后对其进行解压缩。有没有人有压缩XElement对象的任何示例代码（解压缩也将是伟大的）？此外，我需要对数据库列的数据类型进行哪些更改，以便我们可以充分利用此压缩？

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提供，并且它提供的验证开销太高，我们不能考虑使用它。此外，虽然它压缩XML有点，它不像.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.

我已经测试了DeflateStream类通过编写XML使用磁盘，然后将压缩版本保存为新文件。结果是伟大的，一个16kb的文件下降到一个3kb的文件，所以它是jsut的一种情况下得到这在内存中工作，并将结果数据保存到DB。有没有任何人有任何样本代码来做压缩，我应该更改varcahr（MAX）colum键入可能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?

提前感谢

推荐答案

本文可以帮助您开始。

以下代码段可以压缩字符串并返回base-64编码结果：

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);
}

编辑：另外，你可能想使用CLOB格式，即使存储XML作为文本，因为varchars具有非常有限的长度 - 哪个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压缩XML以存储在SQL Server数据库中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！