Deploy C # program DES encryption and decryption on SQL Server

First enable the SQL server2005 Service

 

 

 

 

Create a project:

 

Select the database to be deployed

 

Add a class

 

 

/*************************************** ***************************
* Copyright (c)
* Description: data encryption and decryption
* 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>
/// Encryption
/// </Summary>
/// <Param name = "data"> string to be encrypted </param>
/// <Returns> encrypted string </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>
/// Decrypt
/// </Summary>
/// <Param name = "data"> string to be decrypted </param>
/// <Returns> decrypted string </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 call:

Select DBO. encode (UID, 'aaaaaaa', 'bbbbbbbbbbbb') from tb_users TU