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

 1 using System.Data.SqlClient;
 2 using System.Data.SqlTypes;
 3 using Microsoft.SqlServer.Server;
 4 using System.Collections;
 5 
 6 public partial class UserDefinedFunctions
 7 {
 8     [Microsoft.SqlServer.Server.SqlFunction(
 9         DataAccess=DataAccessKind.Read,
10         FillRowMethodName="GetNextDepartment",
11         TableDefinition="Name nvarchar(50),GroupName nvarchar(50)")]
12     public static IEnumerable GetDepartments()
13     {
14         // 在此处放置代码
15         using (SqlConnection conn = new SqlConnection("context connection=true"))
16         {
17             string sql = "Select Name,GroupName From HumanResources.Department";
18             conn.Open();
19             SqlCommand comm = new SqlCommand(sql, conn);
20             SqlDataAdapter adaptor = new SqlDataAdapter(comm);
21             DataSet dSet = new DataSet();
22             adaptor.Fill(dSet);
23             return (dSet.Tables[0].Rows);
24         }
25     }
26 
27     public static void GetNextDepartment(object row,
28         out string name,
29         out string groupName)
30     {
31         DataRow theRow = (DataRow)row;
32         name=(string)theRow["Name"];
33         groupName=(string)theRow["GroupName"];
34     }
35 };

 1 using System;
 2 using System.Data;
 3 using System.Data.SqlClient;
 4 using System.Data.SqlTypes;
 5 using System.IO;
 6 using System.IO.Compression;
 7 using Microsoft.SqlServer.Server;
 8 
 9 public partial class UserDefinedFunctions
10 {
11     [Microsoft.SqlServer.Server.SqlFunction]
12     public static SqlBytes BinaryDecompress(SqlBytes inputBinary)
13     {
14         // 在此处放置代码
15         byte[] inputBytes = (byte[])inputBinary.Value;
16 
17         using (MemoryStream memStreamIn = new MemoryStream(inputBytes))
18         {
19             using (GZipStream s = new GZipStream(memStreamIn, CompressionMode.Decompress))
20             {
21                 using (MemoryStream memStreamOut = new MemoryStream())
22                 {
23                     for (int num = s.ReadByte(); num != -1; num = s.ReadByte())
24                     {
25                         memStreamOut.WriteByte((byte)num);
26                     }
27                     return (new SqlBytes(memStreamOut.ToArray()));
28                 }
29             }
30         }
31     }
32 };