使用C#编写字符串加密的存储过程!
在VS2005中新建SQL Server项目,然后编写代码如下:
1
using System;
2
using System.Data;
3
using System.Data.SqlClient;
4
using System.Data.SqlTypes;
5
using Microsoft.SqlServer.Server;
6
using System.Security.Cryptography;
7
using System.Text;
8
using System.IO;
9
10
public partial class StoredProcedures
11
{
12
[Microsoft.SqlServer.Server.SqlProcedure(Name="WriteHashedPassword")]
13
public static void WriteHashedPassword(string UserName,string Password)
14
{
15
try
16
{
17
using (SqlConnection cnn = new SqlConnection("Context Connection=true"))
18
{
19
cnn.Open();
20
using (SqlCommand sqlCmd=new SqlCommand())
21
{
22
SHA1Managed sh1 = new SHA1Managed();
23
UnicodeEncoding uEncode = new UnicodeEncoding();
24
25
byte[] txtBytes = uEncode.GetBytes(Password);
26
byte[] hashedPassword = sh1.ComputeHash(txtBytes);
27
string strHash = Convert.ToBase64String(hashedPassword);
28
29
string dm1 = "Insert tbl_User(username) VALUES(@HashAsString)";
30
sqlCmd.Connection = cnn;
31
sqlCmd.CommandText = dm1;
32
sqlCmd.Parameters.Add(new SqlParameter("@HashAsString", SqlDbType.NVarChar, 50));
33
sqlCmd.Parameters[0].Value = strHash;
34
sqlCmd.ExecuteNonQuery();
35
}
36
cnn.Close();
37
}
38
}
39
catch (Exception ex)
40
{
41
FileStream fs = new FileStream(@"c:\YukonCLR.log", FileMode.OpenOrCreate, FileAccess.Write);
42
StreamWriter sw = new StreamWriter(fs);
43
sw.WriteLine(ex.Message);
44
sw.Close();
45
}
46
}
47
}
using System;2
using System.Data;3
using System.Data.SqlClient;4
using System.Data.SqlTypes;5
using Microsoft.SqlServer.Server;6
using System.Security.Cryptography;7
using System.Text;8
using System.IO;9

10
public partial class StoredProcedures11
{12
[Microsoft.SqlServer.Server.SqlProcedure(Name="WriteHashedPassword")]13
public static void WriteHashedPassword(string UserName,string Password)14
{15
try16
{17
using (SqlConnection cnn = new SqlConnection("Context Connection=true"))18
{19
cnn.Open();20
using (SqlCommand sqlCmd=new SqlCommand())21
{22
SHA1Managed sh1 = new SHA1Managed();23
UnicodeEncoding uEncode = new UnicodeEncoding();24

25
byte[] txtBytes = uEncode.GetBytes(Password);26
byte[] hashedPassword = sh1.ComputeHash(txtBytes);27
string strHash = Convert.ToBase64String(hashedPassword);28

29
string dm1 = "Insert tbl_User(username) VALUES(@HashAsString)";30
sqlCmd.Connection = cnn;31
sqlCmd.CommandText = dm1;32
sqlCmd.Parameters.Add(new SqlParameter("@HashAsString", SqlDbType.NVarChar, 50));33
sqlCmd.Parameters[0].Value = strHash;34
sqlCmd.ExecuteNonQuery();35
}36
cnn.Close();37
}38
}39
catch (Exception ex)40
{41
FileStream fs = new FileStream(@"c:\YukonCLR.log", FileMode.OpenOrCreate, FileAccess.Write);42
StreamWriter sw = new StreamWriter(fs);43
sw.WriteLine(ex.Message);44
sw.Close();45
}46
}47
}SqlConnection cnn = new SqlConnection("Context Connection=true")),这句与写客户端的ADO.Net程序不同,此处应该算是固定写法吧,是取得上下文的连接,即当前调用此存储过程的连接.
关于调试:
调试用两种方式:
(1)在项目中有一个Test.sql,在里面按TSQL的写法调用存储过程: exec WriteHashedPassword '2','333',即可.
(2)在VS菜单里面的"调试"--"附加到进程",在里面附加到SQLServer进程,然后以各种方式调用存储过程即可,比如在Sql Server Management Studio里面调用存储过程,在VS里面调用,编程调用等等.
关于部署:
使用.Net的调试时会自动进行部署,还可以使用TSQL手动将程序储部署到SQLServer,应该还可以使用安装包的方式吧?
使用.Net编写存储过程是Sql Server2005的新特性,感觉那真是一个爽啊.哈哈,以后可以不用TSQL编写了....
一点说明:为什么在标题中要嵌入英文?原因是为了能够让国外的网友能查询到这篇文章。平常在Google上查资料的时候,经常参考国外网友的博客,帮助我解决了很多问题,所以我也想让他们能够参考我写的内容。当然文中我不可能全部译为英文,所以我尽量把代码粘全,靠代码说话吧。



浙公网安备 33010602011771号