userdb.cs

ASP C#代码实例 适合初学人士学习使用

using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web.Security;
using System.Security.Cryptography;
using System.Text;

namespace Example_10_3
{
	/// <summary>
	/// Summary description for UserDB.
	/// </summary>
	public class UserDB
	{
		private readonly string SQLCONNECTIONSTRING = ConfigurationSettings.AppSettings["SQLCONNECTIONSTRING"].ToString();

		public SqlDataReader GetUsers()
		{
			//定义数据库的Connection and Command 
			SqlConnection myConnection = new SqlConnection(SQLCONNECTIONSTRING);
			SqlCommand myCommand = new SqlCommand("Pr_GetUsers",myConnection);

			//定义访问数据库的方式为存储过程
			myCommand.CommandType = CommandType.StoredProcedure;

			SqlDataReader dr = null;

			try
			{
				//打开数据库的连接
				myConnection.Open();
			}
			catch(Exception ex)
			{
				throw new Exception("数据库连接失败!",ex);
			}

			try
			{
				//执行数据库的存储过程（访问数据库）
				dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
			}
			catch(Exception ex)
			{
				throw new Exception(ex.Message,ex);
			}

			//返回 dr
			return dr;
		}

		public SqlDataReader GetSingleUser(int nUserID)
		{
			//定义数据库的Connection and Command 
			SqlConnection myConnection = new SqlConnection(SQLCONNECTIONSTRING);
			SqlCommand myCommand = new SqlCommand("Pr_GetSingleUser",myConnection);

			//定义访问数据库的方式为存储过程
			myCommand.CommandType = CommandType.StoredProcedure;

			//添加储存过程的参数
			SqlParameter parameterUserID = new SqlParameter("@UserID",SqlDbType.Int,4);
			parameterUserID.Value = nUserID;
			myCommand.Parameters.Add(parameterUserID);

			SqlDataReader dr = null;

			try
			{
				//打开数据库的连接
				myConnection.Open();
			}
			catch(Exception ex)
			{
				throw new Exception("数据库连接失败!",ex);
			}

			try
			{
				//执行数据库的存储过程（访问数据库）
				dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
			}
			catch(Exception ex)
			{
				throw new Exception(ex.Message,ex);
			}

			//返回 dr
			return dr;
		}

		public int AddUser(String sUserName,String sPassword,String sEmail)
		{
			//定义数据库的Connection and Command 
			SqlConnection myConnection = new SqlConnection(SQLCONNECTIONSTRING);
			SqlCommand myCommand = new SqlCommand("Pr_AddUser",myConnection);

			//定义访问数据库的方式为存储过程
			myCommand.CommandType = CommandType.StoredProcedure;

			//创建访问数据库的参数
			SqlParameter parameterUserName = new SqlParameter("@UserName",SqlDbType.VarChar,32);
			parameterUserName.Value = sUserName;
			myCommand.Parameters.Add(parameterUserName);

			SqlParameter parameterPassword = new SqlParameter("@Password",SqlDbType.VarChar,100);
			parameterPassword.Value = sPassword;
			myCommand.Parameters.Add(parameterPassword);

			SqlParameter parameterEmail = new SqlParameter("@Email",SqlDbType.VarChar,100);
			parameterEmail.Value = sEmail;
			myCommand.Parameters.Add(parameterEmail);

			SqlParameter parameterUserID = new SqlParameter("@UserID",SqlDbType.Int,4);
			parameterUserID.Direction = ParameterDirection.ReturnValue;
			myCommand.Parameters.Add(parameterUserID);

			try
			{
				//打开数据库的连接
				myConnection.Open();
			}
			catch(Exception ex)
			{
				throw new Exception("数据库连接失败!",ex);
			}

			try 
			{
				//执行数据库的存储过程（访问数据库）
				myCommand.ExecuteNonQuery();
			}
			catch(Exception ex)
			{
				throw new Exception(ex.Message,ex);
			}
			finally 
			{
				if (myConnection.State == ConnectionState.Open)
				{
					//关闭数据库的连接
					myConnection.Close();
				}
			}

			return (int)parameterUserID.Value;
		}

		public void UpdateUserPassword(int nUserID,String sPassword)
		{
			//定义数据库的Connection and Command 
			SqlConnection myConnection = new SqlConnection(SQLCONNECTIONSTRING);
			SqlCommand myCommand = new SqlCommand("Pr_UpdateUserPassword",myConnection);

			//定义访问数据库的方式为存储过程
			myCommand.CommandType = CommandType.StoredProcedure;

			//创建访问数据库的参数
			SqlParameter parameterUserID = new SqlParameter("@UserID",SqlDbType.Int,4);
			parameterUserID.Value = nUserID;
			myCommand.Parameters.Add(parameterUserID);

			SqlParameter parameterPassword = new SqlParameter("@Password",SqlDbType.VarChar,100);
			parameterPassword.Value = sPassword;
			myCommand.Parameters.Add(parameterPassword);

			try
			{
				//打开数据库的连接
				myConnection.Open();
			}
			catch(Exception ex)
			{
				throw new Exception("数据库连接失败!",ex);
			}

			try 
			{
				//执行数据库的存储过程（访问数据库）
				myCommand.ExecuteNonQuery();
			}
			catch(Exception ex)
			{
				throw new Exception(ex.Message,ex);
			}
			finally 
			{
				if (myConnection.State == ConnectionState.Open)
				{
					//关闭数据库的连接
					myConnection.Close();
				}
			}
		}

		public void DeleteUser(int nUserID)
		{
			//定义数据库的Connection and Command 
			SqlConnection myConnection = new SqlConnection(SQLCONNECTIONSTRING);
			SqlCommand myCommand = new SqlCommand("Pr_DeleteUser",myConnection);

			//定义访问数据库的方式为存储过程
			myCommand.CommandType = CommandType.StoredProcedure;

			//创建访问数据库的参数
			SqlParameter parameterUserID = new SqlParameter("@UserID",SqlDbType.Int,4);
			parameterUserID.Value = nUserID;
			myCommand.Parameters.Add(parameterUserID);

			try
			{
				//打开数据库的连接
				myConnection.Open();
			}
			catch(Exception ec)
			{
				throw new Exception("数据库连接失败!",ec);
			}

			try
			{
				//执行数据库的存储过程（访问数据库）
				myCommand.ExecuteNonQuery();
			}
			catch(Exception er)
			{
				throw new Exception(er.Message,er);
			}	
			finally
			{
				if(myConnection.State == ConnectionState.Open)
				{
					//关闭数据库的连接
					myConnection.Close();
				}
			}
		}

		public SqlDataReader GetUserLogin(String sUserName,String sPassword)
		{
			//定义数据库的Connection and Command 
			SqlConnection myConnection = new SqlConnection(SQLCONNECTIONSTRING);
			SqlCommand myCommand = new SqlCommand("Pr_GetUserLogin",myConnection);

			//定义访问数据库的方式为存储过程
			myCommand.CommandType = CommandType.StoredProcedure;

			//创建访问数据库的参数
			SqlParameter parameterUserName = new SqlParameter("@UserName",SqlDbType.VarChar,32);
			parameterUserName.Value = sUserName;
			myCommand.Parameters.Add(parameterUserName);

			SqlParameter parameterPassword = new SqlParameter("@Password",SqlDbType.VarChar,100);
			parameterPassword.Value = sPassword;
			myCommand.Parameters.Add(parameterPassword);
			
			SqlDataReader dr = null;

			try
			{
				//打开数据库的连接
				myConnection.Open();
			}
			catch(Exception ex)
			{
				throw new Exception("数据库连接失败!",ex);
			}

			try
			{
				//执行数据库的存储过程（访问数据库）
				dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
			}
			catch(Exception ex)
			{
				throw new Exception(ex.Message,ex);
			}

			//返回 dr
			return dr;
		}		

		/// <summary>
		/// 用户加密函数
		/// </summary>
		public static String Encrypt(string password) 
		{
			Byte[] clearBytes = new UnicodeEncoding().GetBytes(password);
			Byte[] hashedBytes = ((HashAlgorithm) CryptoConfig.CreateFromName("MD5")).ComputeHash(clearBytes);

			return BitConverter.ToString(hashedBytes);
		}		
	}
}