C# 程式碼
/// <summary>
/// 将传入字符串以GZip算法压缩后，返回Base64编码字符
/// </summary>
/// <param name="rawString">需要压缩的字符串</param>
/// <returns>压缩后的Base64编码的字符串</returns>
public static string GZip(string rawString)
{
return rawString; //off test
if (string.IsNullOrEmpty(rawString) || rawString.Length == 0)
{
return "";
}
else
{
byte[] rawData = System.Text.Encoding.UTF8.GetBytes(rawString.ToString());
byte[] zippedData = GZip_Compress(rawData);
return (string)(Convert.ToBase64String(zippedData));
}
}
/// <summary>
/// GZip压缩
/// </summary>
/// <param name="rawData"></param>
/// <returns></returns>
public static byte[] GZip_Compress(byte[] rawData)
{
MemoryStream ms = new MemoryStream();
GZipStream compressedzipStream = new GZipStream(ms, CompressionMode.Compress, true);
compressedzipStream.Write(rawData, 0, rawData.Length);
compressedzipStream.Close();
return ms.ToArray();
}
/// <summary>
/// 将传入的二进制字符串资料以GZip算法解压缩
/// </summary>
/// <param name="zippedString">经GZip压缩后的二进制字符串</param>
/// <returns>原始未压缩字符串</returns>
public static string UnGZip(string zippedString)
{
return zippedString; //off test
if (string.IsNullOrEmpty(zippedString) || zippedString.Length == 0)
{
return "";
}
else
{
byte[] zippedData = Convert.FromBase64String(zippedString.ToString());
return (string)(System.Text.Encoding.UTF8.GetString(UnGZip_Decompress(zippedData)));
}
}
/// <summary>
/// ZIP解压
/// </summary>
/// <param name="zippedData"></param>
/// <returns></returns>
public static byte[] UnGZip_Decompress(byte[] zippedData)
{
MemoryStream ms = new MemoryStream(zippedData);
GZipStream compressedzipStream = new GZipStream(ms, CompressionMode.Decompress);
MemoryStream outBuffer = new MemoryStream();
byte[] block = new byte[1024];
while (true)
{
int bytesRead = compressedzipStream.Read(block, 0, block.Length);
if (bytesRead <= 0)
break;
else
outBuffer.Write(block, 0, bytesRead);
}
compressedzipStream.Close();
return outBuffer.ToArray();
}
產生SQL共用函式
-----------------------1.set SQL Server可執行CLR-----------------------
sp_configure 'clr enabled', 1
GO
RECONFIGURE
go
-----------------------2.建立ASSEMBLY-----------------------
CREATE ASSEMBLY Net_SQLCom FROM '******\bin\Debug\SQLCom.dll'
WITH PERMISSION_SET = SAFE;
--drop ASSEMBLY Net_SQLCom
go
--chk 有沒有看到Net_SQLCom
SELECT * FROM sys.assemblies
go
-----------------------3.建立function-----------------------
--drop function GZip
--drop function UnGZip
CREATE FUNCTION [dbo].[GZip](@str [nvarchar](max) )
RETURNS [nvarchar](MAX) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [Net_SQLCom].[UserDefinedFunctions].[GZip]
GO
CREATE FUNCTION [dbo].[UnGZip](@str [nvarchar](max) )
RETURNS [nvarchar](MAX) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [Net_SQLCom].[UserDefinedFunctions].[UnGZip]
GO
--chk 有沒有看到function
SELECT * FROM sys.assembly_modules;
-----------------------4.Test-----------------------
select [dbo].[UnGZip]([dbo].[GZip]('setse' ))
注意資料庫型別
| char | 資料有固定長度，並且都為英文數字。 |
| nchar | 資料有固定長度，但不確定是否皆為英文數字。 |
| varchar | 資料沒有固定長度，並且都為英文數字。 |
| nvarchar | 資料沒有固定長度，且不確定是否皆為英文數字。 |
