下面用C#2010定义一个函数，供SQL SERVER 2008使用：
下面是压缩函数
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 };
