***********特别注意用户信息存在session中在其他页面的取值方法
ThreeTies.Model.User u= Session["User"] as ThreeTies.Model.User;
labUserName=u.UserName;
labUserPwd=u.UserPwd;
1.通用类层的代码
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
namespace ThreeTies.DBUtility
{
/// <summary>
/// 封装常见的数据库访问方法
/// </summary>
public class DBHelp
{
//连接串
private static string _connStr="server=.;uid=sa;pwd=;database=ThreeTies";
//连接对象
private static SqlConnection sqlcon = null;
/// <summary>
/// 建立数据库连接
/// </summary>
private static void CreateConnection()
{
if (sqlcon == null)
{
sqlcon = new SqlConnection(_connStr);
sqlcon.Open();
}
else if (sqlcon.State == ConnectionState.Closed || sqlcon.State == ConnectionState.Broken)
{
sqlcon.Close();
sqlcon.Open();
}
}
/// <summary>
/// 根据查询语句，执行单向的Insert、Update、Delete
/// </summary>
/// <param name="strsql">查询语句</param>
/// <returns>受影响的行数</returns>
public static int ExecuteNoQuery(string strsql)
{
int i=-1;
try
{
CreateConnection();
SqlCommand sqlcmd = new SqlCommand(strsql, sqlcon);
i = sqlcmd.ExecuteNonQuery();
return i;
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
/// <summary>
/// 根据查询语句，执行单向的Insert、Update、Delete
/// </summary>
/// <param name="strsql">查询语句</param>
/// <param name="param">语句中的参数数组</param>
/// <returns>受影响的行数</returns>
public static int ExecuteNoQuery(string strsql, params SqlParameter[] param)
{
int i = -1;
try
{
CreateConnection();
SqlCommand sqlcmd = new SqlCommand(strsql, sqlcon);
sqlcmd.Parameters.AddRange(param);
i = sqlcmd.ExecuteNonQuery();
return i;
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
/// <summary>
/// 根据查询数据，执行双向的查询
/// </summary>
/// <param name="strsql">查询语句</param>
/// <returns>查询的结果-表集合</returns>
public static DataTable GetTable(string strsql)
{
try
{
CreateConnection();
SqlDataAdapter sda = new SqlDataAdapter(strsql, sqlcon);
DataSet ds = new DataSet();
sda.Fill(ds, "temp");
sqlcon.Close();
return ds.Tables["temp"];
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
/// <summary>
/// 根据查询数据，执行双向的查询
/// </summary>
/// <param name="strsql">查询语句</param>
/// <param name="param">查询参数</param>
/// <returns>查询的结果-表集合</returns>
public static DataTable GetTable(string strsql, params SqlParameter[] param)
{
try
{
CreateConnection();
SqlDataAdapter sda = new SqlDataAdapter(strsql, sqlcon);
sda.SelectCommand.Parameters.AddRange(param);
DataSet ds = new DataSet();
sda.Fill(ds, "temp");
sqlcon.Close();
return ds.Tables["temp"];
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
/// <summary>
/// 根据查询要求返回轻量级对象DataReader
/// </summary>
/// <param name="strsql">查询语句</param>
/// <returns>返回的对象</returns>
public static SqlDataReader GetReader(string strsql)
{
try
{
CreateConnection();
SqlCommand sqlcmd = new SqlCommand(strsql, sqlcon);
SqlDataReader sdr = sqlcmd.ExecuteReader(CommandBehavior.CloseConnection);
return sdr;
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
/// <summary>
/// 根据查询要求返回轻量级对象DataReader
/// </summary>
/// <param name="strsql">查询语句</param>
/// <param name="param">查询参数</param>
/// <returns>返回的对象</returns>
public static SqlDataReader GetReader(string strsql, params SqlParameter[] param)
{
try
{
CreateConnection();
SqlCommand sqlcmd = new SqlCommand(strsql, sqlcon);
sqlcmd.Parameters.AddRange(param);
SqlDataReader sdr = sqlcmd.ExecuteReader(CommandBehavior.CloseConnection);
return sdr;
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
}
}
2.数据防问层的代码
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ThreeTies.DBUtility;
using ThreeTies.Model;
using System.Data.SqlClient;
namespace ThreeTies.SqlServerDAL
{
/// <summary>
/// 角色的数据访问层
/// </summary>
public class RoleService
{
//该查询的特点：因为用了DataReader,所以在查询期间是不会释放连接的
public static Role GetRole(int roleID)
{
Role role = null;
//给查询语句定义的参数
SqlParameter[] param=new SqlParameter[]{
new SqlParameter("@RoleID",roleID)
};
//using语句能自动释放对象
using (SqlDataReader sdr = DBHelp.GetReader("select * from [Role] where RoleID=@RoleID", param))
{
if (sdr.Read())
{
role = new Role();
//role.RoleID = roleID;
role.RoleID = (int)sdr.GetValue(0);
role.RoleName = (string)sdr.GetValue(1);
}
sdr.Close();
}
return role;
}
}
}
using ThreeTies.Model;
using ThreeTies.DBUtility;
using System.Data.SqlClient;
using System.Data;
namespace ThreeTies.SqlServerDAL
{
//用户的基础服务类
public class UserService
{
//添加用户
public static int AddUser(User u)
{
try
{
SqlParameter[] param = new SqlParameter[]{
new SqlParameter("@UserName",u.UserName),
new SqlParameter("@UserPassword",u.UserPassword),
new SqlParameter("@RoleID",u.Role.RoleID)
};
int i = DBHelp.ExecuteNoQuery("insert into [User] values(@userName,@UserPassword,@RoleID)", param);
return i;
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
//根据用户的账号返回用户的信息
public static User GetUserByUserID(string userName)
{
User u=null;
try
{
SqlParameter[] param = new SqlParameter[]{
new SqlParameter("@UserName",userName)
};
DataTable dt = DBHelp.GetTable("select * from [User] where UserName=@UserName", param);
//如果用户存在
if (dt.Rows.Count > 0)
{
u = new User();
u.UserName = dt.Rows[0]["UserName"].ToString();
u.UserPassword = dt.Rows[0]["UserPassword"].ToString();
u.Role = new Role();
u.Role = RoleService.GetRole(int.Parse(dt.Rows[0]["RoleID"].ToString()));
}
return u;
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
}
}
3.业务逻辑层的代码
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ThreeTies.Model;
using ThreeTies.SqlServerDAL;
namespace ThreeTies.BLL
{
//用户管理类
public class UserManager
{
/// <summary>
/// 注册方法
/// </summary>
/// <param name="u">注册的用户信息</param>
/// <returns>注册是否成功</returns>
public static bool Register(string userName,string userPassword,int roleID)
{
int i = -1;
//封装用户信息
User u = new User();
u.UserName = userName;
u.UserPassword = StringMD5.GetMD5(userPassword);//加密处理
u.Role = RoleService.GetRole(roleID);
try
{
i = UserService.AddUser(u);
if (i > 0)
{
return true;
}
else
{
return false;
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
/// <summary>
/// 提供登陆的账号、密码实现验证,并返回该对象
/// </summary>
/// <param name="userName">账号</param>
/// <param name="userPassword">密码</param>
/// <param name="u">返回的用户信息</param>
/// <returns>登陆是否成功</returns>
public static bool Login(string userName,string userPassword,out User u)
{
User currentUser = UserService.GetUserByUserID(userName);
if (currentUser == null)
{
u = null;
return false;
}
else if (currentUser.UserPassword == StringMD5.GetMD5(userPassword))
{
u = currentUser;//当前用户信息返回
return true;
}
else
{
u = null;
return false;
}
}
}
}