SQL Server 部署C#程式 DES加密及解密

首先開啟SQL Server2005服務

 

 

 

 

建立一個項目：

 

選擇需要部署的資料庫

 

新增一個類

 

 

/******************************************************************
 * Copyright(c)  
 * Description   : 資料加密及解密類
 * CreateDate    : 2009-05-22
 * Creater       : Oliver.Dong
 * LastChangeDate:
 * LastChanger   :
 * Version Info  : 1.0
 ********************************************************************/

using System;
using System.Collections.Generic;
using System.Text;
using System.Security.Cryptography;
using System.IO;

 

public partial class DesEncrypt
{
    /// <summary>
    /// 加密
    /// </summary>
    /// <param name="data">需加密的字串</param>
    /// <returns>加密後的字串</returns>
    [Microsoft.SqlServer.Server.SqlFunction()]
    public static string Encode(string data, string key_64, string iv_64)
    {
        byte[] byKey = System.Text.ASCIIEncoding.ASCII.GetBytes(key_64);
        byte[] byIV = System.Text.ASCIIEncoding.ASCII.GetBytes(iv_64);

        DESCryptoServiceProvider cryptoProvider = new DESCryptoServiceProvider();
        int i = cryptoProvider.KeySize;
        MemoryStream ms = new MemoryStream();
        CryptoStream cst = new CryptoStream(ms, cryptoProvider.CreateEncryptor(byKey, byIV), CryptoStreamMode.Write);

        StreamWriter sw = new StreamWriter(cst);
        sw.Write(data);
        sw.Flush();
        cst.FlushFinalBlock();
        sw.Flush();
        return Convert.ToBase64String(ms.GetBuffer(), 0, (int)ms.Length);
    }

    /// <summary>
    /// 解密
    /// </summary>
    /// <param name="data">需解密的字串</param>
    /// <returns>解密後的字串</returns>
    [Microsoft.SqlServer.Server.SqlFunction()]
    public static string Decode(string data, string key_64, string iv_64)
    {
        if (data.Length <= 0)
        {
            return "";
        }

        byte[] byKey = System.Text.ASCIIEncoding.ASCII.GetBytes(key_64);
        byte[] byIV = System.Text.ASCIIEncoding.ASCII.GetBytes(iv_64);
        byte[] byEnc;
        try
        {
            byEnc = Convert.FromBase64String(data);
        }
        catch
        {
            return null;
        }
        DESCryptoServiceProvider cryptoProvider = new DESCryptoServiceProvider();
        MemoryStream ms = new MemoryStream(byEnc);
        CryptoStream cst = new CryptoStream(ms, cryptoProvider.CreateDecryptor(byKey, byIV), CryptoStreamMode.Read);
        StreamReader sr = new StreamReader(cst);
        return sr.ReadToEnd();
    }
}

 

 

SQL 中調用：

SELECT dbo.Encode(uid,'AAAAAAAA','BBBBBBBB') FROM tb_users tu