SQL SERVER 2008中用C#定义压缩与解压缩函数

下面用C#2010定义一个函数，供SQL SERVER 2008使用：

下面是压缩函数

using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction(
        DataAccess=DataAccessKind.Read,
        FillRowMethodName="GetNextDepartment",
        TableDefinition="Name nvarchar(50),GroupName nvarchar(50)")]
    public static IEnumerable GetDepartments()
    {
        // 在此处放置代码
        using (SqlConnection conn = new SqlConnection("context connection=true"))
        {
            string sql = "Select Name,GroupName From HumanResources.Department";
            conn.Open();
            SqlCommand comm = new SqlCommand(sql, conn);
            SqlDataAdapter adaptor = new SqlDataAdapter(comm);
            DataSet dSet = new DataSet();
            adaptor.Fill(dSet);
            return (dSet.Tables[0].Rows);
        }
    }

    public static void GetNextDepartment(object row,
        out string name,
        out string groupName)
    {
        DataRow theRow = (DataRow)row;
        name=(string)theRow["Name"];
        groupName=(string)theRow["GroupName"];
    }
};

解压函数：

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.IO;
using System.IO.Compression;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlBytes BinaryDecompress(SqlBytes inputBinary)
    {
        // 在此处放置代码
        byte[] inputBytes = (byte[])inputBinary.Value;

        using (MemoryStream memStreamIn = new MemoryStream(inputBytes))
        {
            using (GZipStream s = new GZipStream(memStreamIn, CompressionMode.Decompress))
            {
                using (MemoryStream memStreamOut = new MemoryStream())
                {
                    for (int num = s.ReadByte(); num != -1; num = s.ReadByte())
                    {
                        memStreamOut.WriteByte((byte)num);
                    }
                    return (new SqlBytes(memStreamOut.ToArray()));
                }
            }
        }
    }
};