http://www.cnblogs.com/dingjie08/archive/2009/03/05/1404103.html
asp.net数据库连接,三层架构方法调用 一、web.config配置 public class GetConn
{ public GetConn() { // // TODO: 在此处添加构造函数逻辑 // } private static SqlConnection conn = null; /**//// <summary> /// 返回数据库的连接 /// </summary> public SqlConnection Conn { get { if (conn == null)//如果为空,则创建,否则直接返回对象,单键模式,减少对象的重复创建。 { string url = ConfigurationSettings.AppSettings["ConnectionKey"]; conn = new SqlConnection(url); } return conn; } } } 三、数据库方法调用 public class User
{ public User() { // // TODO: 在此处添加构造函数逻辑 // } GetConn conn = new GetConn(); /**//// <summary> /// 根据条件获取用户信息 /// </summary> /// <param name="UserName"></param> /// <returns></returns> public DataTable SelectUsersDynamic(string WhereCondition, string OrderByExpression) { DataTable dt = new DataTable(); SqlCommand comm = new SqlCommand("SelectUsersDynamic", conn.Conn); comm.CommandType = CommandType.StoredProcedure; comm.Parameters.Add("@WhereCondition", SqlDbType.NVarChar, 500); comm.Parameters["@WhereCondition"].Value = WhereCondition; comm.Parameters["@WhereCondition"].Direction = System.Data.ParameterDirection.Input; comm.Parameters.Add("@OrderByExpression", SqlDbType.NVarChar, 250); comm.Parameters["@OrderByExpression"].Value = OrderByExpression; comm.Parameters["@OrderByExpression"].Direction = System.Data.ParameterDirection.Input; SqlDataAdapter adapter = new SqlDataAdapter(comm); adapter.Fill(dt); if (conn.Conn.State == ConnectionState.Open) conn.Conn.Close(); return dt; } /**//// <summary> /// 查询所有用户信息 /// </summary> /// <returns></returns> public DataTable SelectUsersAll() { DataTable dt = new DataTable(); SqlCommand comm = new SqlCommand("SelectUsersAll", conn.Conn); comm.CommandType = CommandType.StoredProcedure; SqlDataAdapter adapter = new SqlDataAdapter(comm); adapter.Fill(dt); if (conn.Conn.State == ConnectionState.Open) conn.Conn.Close(); return dt; } /**//// <summary> /// 删除用户 /// </summary> /// <param name="UserID"></param> /// <returns></returns> public int DeleteUser(int UserID) { SqlCommand comm = new SqlCommand("DeleteUser", conn.Conn); if (conn.Conn.State == ConnectionState.Closed) conn.Conn.Open(); comm.CommandType = CommandType.StoredProcedure; comm.Parameters.Add("@UserID", SqlDbType.Int, 4); comm.Parameters["@UserID"].Value = UserID; comm.Parameters["@UserID"].Direction = System.Data.ParameterDirection.Input; int counts = comm.ExecuteNonQuery(); if (conn.Conn.State == ConnectionState.Open) conn.Conn.Close(); return counts; } /**//// <summary> /// 修改用户信息 /// </summary> /// <param name="UserID"></param> /// <param name="IsSupplier"></param> /// <param name="RoleID"></param> /// <returns></returns> public int UpdateUser(int UserID,string UserName,int IsSupplier, int RoleID) { SqlCommand comm = new SqlCommand("UpdateUser", conn.Conn); if (conn.Conn.State == ConnectionState.Closed) conn.Conn.Open(); comm.CommandType = CommandType.StoredProcedure; comm.Parameters.Add("@UserID", SqlDbType.Int,4); comm.Parameters["@UserID"].Value = UserID; comm.Parameters["@UserID"].Direction = System.Data.ParameterDirection.Input; comm.Parameters.Add("@UserName", SqlDbType.VarChar,20); comm.Parameters["@UserName"].Value = UserName; comm.Parameters["@UserName"].Direction = System.Data.ParameterDirection.Input; comm.Parameters.Add("@IsSupplier", SqlDbType.Int,4); comm.Parameters["@IsSupplier"].Value = IsSupplier; comm.Parameters["@IsSupplier"].Direction = System.Data.ParameterDirection.Input; comm.Parameters.Add("@RoleID", SqlDbType.Int,4); comm.Parameters["@RoleID"].Value = RoleID; comm.Parameters["@RoleID"].Direction = System.Data.ParameterDirection.Input; int counts = comm.ExecuteNonQuery(); if (conn.Conn.State == ConnectionState.Open) conn.Conn.Close(); return counts; } /**//// <summary> /// 设置密码 /// </summary> /// <param name="UserName"></param> /// <param name="Password"></param> /// <returns></returns> public int UpdateUserPassword(string UserName, string Password) { SqlCommand comm = new SqlCommand("UpdateUserPassword", conn.Conn); if (conn.Conn.State == ConnectionState.Closed) conn.Conn.Open(); comm.CommandType = CommandType.StoredProcedure; comm.Parameters.Add("@UserName", SqlDbType.VarChar,20); comm.Parameters["@UserName"].Value = UserName; comm.Parameters["@UserName"].Direction = System.Data.ParameterDirection.Input; comm.Parameters.Add("@Password", SqlDbType.VarChar ,32); comm.Parameters["@Password"].Value = Password; comm.Parameters["@Password"].Direction = System.Data.ParameterDirection.Input; int counts = comm.ExecuteNonQuery(); if (conn.Conn.State == ConnectionState.Open) conn.Conn.Close(); return counts; } /**//// <summary> /// 新增用户 /// </summary> /// <param name="UserName"></param> /// <param name="Password"></param> /// <param name="IsSupplier"></param> /// <param name="RoleID"></param> /// <returns></returns> public int InsertUser(string UserName, string Password, int IsSupplier, int RoleID,string SupplierCode) { SqlCommand comm = new SqlCommand("InsertUser", conn.Conn); if (conn.Conn.State == ConnectionState.Closed) conn.Conn.Open(); comm.CommandType = CommandType.StoredProcedure; comm.Parameters.Add("@UserName", SqlDbType.NVarChar, 20); comm.Parameters["@UserName"].Value = UserName; comm.Parameters["@UserName"].Direction = System.Data.ParameterDirection.Input; comm.Parameters.Add("@Password", SqlDbType.NVarChar, 32); comm.Parameters["@Password"].Value = Password; comm.Parameters["@Password"].Direction = System.Data.ParameterDirection.Input; comm.Parameters.Add("@IsSupplier", SqlDbType.Int, 4); comm.Parameters["@IsSupplier"].Value = IsSupplier; comm.Parameters["@IsSupplier"].Direction = System.Data.ParameterDirection.Input; comm.Parameters.Add("@RoleID", SqlDbType.Int, 4); comm.Parameters["@RoleID"].Value = RoleID; comm.Parameters["@RoleID"].Direction = System.Data.ParameterDirection.Input; comm.Parameters.Add("@SupplierCode", SqlDbType.VarChar,200); comm.Parameters["@SupplierCode"].Value = SupplierCode; comm.Parameters["@SupplierCode"].Direction = System.Data.ParameterDirection.Input; int counts = comm.ExecuteNonQuery(); if (conn.Conn.State == ConnectionState.Open) conn.Conn.Close(); return counts; } } 四、业务层的建立 public class UserBLL
{ public UserBLL() { // // TODO: 在此处添加构造函数逻辑 // } User user = new User(); /**//// <summary> /// 根据用户名获取用户信息 /// </summary> /// <param name="UserName"></param> /// <returns></returns> public DataTable SelectUsersDynamic(string WhereCondition, string OrderByExpression) { return user.SelectUsersDynamic(WhereCondition, OrderByExpression); } /**//// <summary> /// 查询所有用户信息 /// </summary> /// <returns></returns> public DataTable SelectUsersAll() { return user.SelectUsersAll(); } /**//// <summary> /// 删除用户 /// </summary> /// <param name="UserID"></param> /// <returns></returns> public int DeleteUser(int UserID) { return user.DeleteUser(UserID); } /**//// <summary> /// 修改用户信息 /// </summary> /// <param name="UserID"></param> /// <param name="IsSupplier"></param> /// <param name="RoleID"></param> /// <returns></returns> public int UpdateUser(int UserID, string UserName, int IsSupplier, int RoleID) { return user.UpdateUser(UserID,UserName,IsSupplier, RoleID); } /**//// <summary> /// 设置密码 /// </summary> /// <param name="UserName"></param> /// <param name="Password"></param> /// <returns></returns> public int UpdateUserPassword(string UserName, string Password) { return user.UpdateUserPassword(UserName , new StockBaseBLL().MD5( Password,16)); } /**//// <summary> /// 新增用户 /// </summary> /// <param name="UserName"></param> /// <param name="Password"></param> /// <param name="IsSupplier"></param> /// <param name="RoleID"></param> /// <returns></returns> public int InsertUser(string UserName, string Password, int IsSupplier, int RoleID, string SupplierCode) { return user.InsertUser(UserName, Password, IsSupplier, RoleID,SupplierCode); } }
上面的调用感觉像是多余的,其实不是的,他可以隔离表示层和数据库层的方法。还有如果对数据集要处理的话,也可以在这里处理好,然后返回给表示层。
|
|