用户管理系统的数据访问层由User实现,该类继承于接口IUser,并实现了接口IUser的所有方法。
public class User:IUser
... {
private static readonly string GETUSERS="SELECT * FROM Users";
private static readonly string GETSINGLEUSER="SELECT * FROM Users WHERE UserID=";
private static readonly string ADDUSER="INSERT INTO Users(UserName,Password,Email,IsAdmin)VALUES";
private static readonly string UPDATEUSER="UPDATE Users SET Email=";
private static readonly string UPDATEUSERADMIN="UPDATE Users SET IsAdmin=";
private static readonly string UPDATEUSERPASSWORD="UPDATE Users SET Password=";
private static readonly string DETELEUSER="DELETE Users WHERE UserID=";
private static readonly string GETUSERLOGINBYSQL="SELECT UserID FROM Users WHERE UserName=";
}
// 根据用户名和密码,通过SQL语句从数据库中获取用户的登录信息,如ID值。
public SqlDataReader GetUserLoginBySQL( string sUserName, string sPassword)
... {
SqlConnection myConnection =new SqlConnection(ConfigurationManager.ConnectionStrings
["SQLCONNECTIONSTRING"].ConnectionString);
string cmdText=GETUSERLOGINBYSQL+"'"+sUserName.ToString()+"'"+"AND Password="+"'"+sPassword.ToString()+"'";
SqlCommand myCommand=new SqlCommand(cmdText,myConnection);
SqlDataReader dr=null;
try
...{
myConnection.Open();
dr=myCommand.ExecuteReader(CommandBehavior.CloseConnection);
}
catch(SqlException ex)
...{
throw new Exception(ex.Message,ex);
}
return dr;
}
// 根据用户名和密码,通过存储过程从数据库中获取用户的登录信息
public SqlDataReader GetUserLoginByPro( string sUserName, string sPassword)
... {
SqlConnection myConnection =new SqlConnection(ConfigurationManager.ConnectionStrings
["SQLCONNECTIONSTRING"].ConnectionString);
SqlCommand myCommand=new SqlCommand("Pr_GetUserLogin",myConnection);
myCommand.CommandType=CommandType.StoredProcedure;
SqlParameter pUserName=new SqlParameter("@UserName",SqlDbType.VarChar,32);
pUserName.Value=sUserName;
myCommand.Parameters.Add(pUserName);
SqlParameter pPassword=new SqlParameter("@Password",SqlDbType.VarChar,255);
pPassword.Value=sPassword;
myCommand.Parameters.Add(pPassword);
SqlDataReader dr=null;
try
...{
myConnection.Open();
dr=myCommand.ExecuteReader(CommandBehavior.CloseConnection);
}
catch(SqlException ex)
...{
throw new Exception(ex.Message,ex);
}
return dr;
}
// 获取系统中所有用户信息
public SqlDataReader GetUsers()
... {
SqlConnection myConnection =new SqlConnection(ConfigurationManager.ConnectionStrings
["SQLCONNECTIONSTRING"].ConnectionString);
SqlCommand myCommand=new SqlCommand(GETUSERS,myConnection);
SqlDataReader dr=null;
try
...{
myConnection.Open();
dr=myCommand.ExecuteReader(CommandBehavior.CloseConnection);
}
catch(SqlException ex)
...{
throw new Exception(ex.Message,ex);
}
return dr;
}
// 根据用户ID获取系统中单个用户的信息
public SqlDataReader GetSingleUser( int nUserID)
... {
SqlConnection myConnection =new SqlConnection(ConfigurationManager.ConnectionStrings
["SQLCONNECTIONSTRING"].ConnectionString);
string cmdText=GETSINGLEUSER+"'"+nUserID.ToString()+"'";
SqlCommand myCommand=new SqlCommand(cmdText,myConnection);
SqlDataReader dr=null;
try
...{
myConnection.Open();
dr=myCommand.ExecuteReader(CommandBehavior.CloseConnection);
}
catch(SqlException ex)
...{
throw new Exception(ex.Message,ex);
}
return dr;
}
// 添加新用户到数据库
public int AddUser( string sUserName, string sPassword, string sEmail)
... {
SqlConnection myConnection =new SqlConnection(ConfigurationManager.ConnectionStrings
["SQLCONNECTIONSTRING"].ConnectionString);
string cmdText=ADDUSER+"("+"'"+sUserName.ToString()+"',"+"'"+sPassword+"',"+"'"+sEmail+"',"+"'0"+")";
SqlCommand myCommand=new SqlCommand(cmdText,myConnection);
int nResult=-1;
try
...{
myConnection.Open();
nResult=myCommand.ExecuteNonQuery();
}
catch(SqlException ex)
...{
throw new Exception(ex.Message,ex);
}
finally
...{
myConnection.Close();
}
return nResult;
}
// 修改给定ID值的电子邮件,更新用户
public int UpdateUser( int nUserID, string sEmail)
... {
SqlConnection myConnection =new SqlConnection(ConfigurationManager.ConnectionStrings
["SQLCONNECTIONSTRING"].ConnectionString);
string cmdText=UPDATEUSER+"'"+sEmail+"'"+"WHERE UserID="+"'"+nUserID.ToString()+"'";
SqlCommand myCommand=new SqlCommand(cmdText,myConnection);
int nResult=-1;
try
...{
myConnection.Open();
nResult=myCommand.ExecuteNonQuery();
}
catch(SqlException ex)
...{
throw new Exception(ex.Message,ex);
}
finally
...{
myConnection.Close();
}
return nResult;
}
// 修改给定ID值的用户登录密码
public int UpdateUserPwd( int nUserID, string sPassword)
... {
SqlConnection myConnection =new SqlConnection(ConfigurationManager.ConnectionStrings
["SQLCONNECTIONSTRING"].ConnectionString);
string cmdText=UPDATEUSERPASSWORD+"'"+sPassword+"'"+"WHERE UserID="+"'"+nUserID.ToString()+"'";
SqlCommand myCommand=new SqlCommand(cmdText,myConnection);
int nResult=-1;
try
...{
myConnection.Open();
nResult=myCommand.ExecuteNonQuery();
}
catch(SqlException ex)
...{
throw new Exception(ex.Message,ex);
}
finally
...{
myConnection.Close();
}
return nResult;
}
// 修改给定ID值用户的管理员权限,并返回操作所影响的记录行数
public int UpdateUserAdmin( int nUserID, bool blsAdmin)
... {
SqlConnection myConnection =new SqlConnection(ConfigurationManager.ConnectionStrings
["SQLCONNECTIONSTRING"].ConnectionString);
string cmdText=UPDATEUSERADMIN+"'"+(blsAdmin?1:0).ToString()+"'"+"WHERE UserID="+"'"+nUserID.ToString()+"'";
SqlCommand myCommand=new SqlCommand(cmdText,myConnection);
int nResult=-1;
try
...{
myConnection.Open();
nResult=myCommand.ExecuteNonQuery();
}
catch(SqlException ex)
...{
throw new Exception(ex.Message,ex);
}
finally
...{
myConnection.Close();
}
return nResult;
}
// 删除给定ID值的用户
public int DeleteUser( int nUserID)
... {
SqlConnection myConnection =new SqlConnection(ConfigurationManager.ConnectionStrings
["SQLCONNECTIONSTRING"].ConnectionString);
string cmdText=DELETEUSER+"'"+nUserID.ToString()+"'";
SqlCommand myCommand=new SqlCommand(cmdText,myConnection);
int nResult=-1;
try
...{
myConnection.Open();
nResult=myCommand.ExecuteNonQuery();
}
catch(SqlException ex)
...{
throw new Exception(ex.Message,ex);
}
finally
...{
myConnection.Close();
}
return nResult;
}
// 加密函数,网络用户管理系统使用函数Encrypt(string password)对用户密码进行加密之后再保存到数据库中
// 函数Encrypt(string password)对加密用户输入的密码,加密为MD5,返回一个字符串数组。
public static string Encrypt( string password)
... {
//获取Byte数组
Byte[] clearBytes=new UnicodeEncoding().GetBytes(password);
//获取Hash值
Byte[] hashedBytes=((HashAlgorithm)CryptoConfig.CreateFromName("MD5")).ComputeHash(clearBytes);
//获取加密后的信息
return BitConverter.ToString(hashedBytes);
}