user.cs

建立系统架构的说明(教你怎样搭好框架).rar

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using IAS.BookShop.Model;
namespace IAS.BookShop.DAL
{
    public class User
    {
        #region 常量定义

        private const string PARM_Id = "@Id";
        private const string PARM_LoginId = "@LoginId";
        private const string PARM_LoginPwd = "@LoginPwd";
        private const string PARM_Name = "@Name";
        private const string PARM_Address = "@Address";
        private const string PARM_Phone = "@Phone";
        private const string PARM_Mail = "@Mail";
        private const string PARM_UserRoleId = "@UserRoleId";
        private const string PARM_UserStateId = "@UserStateId";

        private const string SELECTQUERY = "SELECT Id, LoginId, LoginPwd, Name, Address, Phone, Mail, UserRoleId, UserStateId FROM Users ";
        private const string WHEREQUERYBYID = " WHERE Id = @Id";
        private const string WHEREQUERYBYLOGINID = " WHERE LoginId = @LoginId";
        private const string INSERTQUERY = "INSERT INTO Users (LoginId, LoginPwd, Name, Address, Phone, Mail, UserRoleId, UserStateId) VALUES (@LoginId, @LoginPwd, @Name, @Address, @Phone, @Mail, @UserRoleId, @UserStateId)";
        private const string UPDATEQUERY = "UPDATE Users SET (LoginId = @LoginId, LoginPwd=@LoginPwd, Name=@Name, Address=@Address, Phone=@Phone, Mail=@Mail, UserRoleId = @UserRoleId, UserStateId = @UserStateId)";
        private const string DELETEQUERY = "DELETE FROM Users WHERE Id = @Id";
        
        #endregion

        /// <summary>
        /// 添加一个用户信息到数据库中。
        /// </summary>
        /// <param name="userInfo">UserInfo对象，不可为null
        /// <remarks>UserInfo对象中的Id属性可以为任何值，并不影响数据库的更新。</remarks>
        /// </param>
        public void Add(UserInfo userInfo)
        {
            SqlParameter[] parms = SQLHelper.GetCachedParameters(INSERTQUERY);
            if (parms == null)
            {
                parms = new SqlParameter[]{
                                                    new SqlParameter(PARM_LoginId, SqlDbType.NVarChar, 50),
                                                    new SqlParameter(PARM_LoginPwd, SqlDbType.NVarChar, 50),
                                                    new SqlParameter(PARM_Name, SqlDbType.NVarChar, 50),
                                                    new SqlParameter(PARM_Address, SqlDbType.NVarChar, 200),
                                                    new SqlParameter(PARM_Phone, SqlDbType.NVarChar, 100),
                                                    new SqlParameter(PARM_Mail, SqlDbType.NVarChar, 100),
                                                    new SqlParameter(PARM_UserRoleId, SqlDbType.Int, 4),
                                                    new SqlParameter(PARM_UserStateId, SqlDbType.Int, 4)
                                                };
                SQLHelper.CacheParameters(INSERTQUERY, parms);
            }
            parms[0].Value = userInfo.LoginId;
            parms[1].Value = userInfo.LoginPwd;
            parms[2].Value = userInfo.Name;
            parms[3].Value = userInfo.Address;
            parms[4].Value = userInfo.Phone;
            parms[5].Value = userInfo.Mail;
            parms[6].Value = userInfo.Role;
            parms[7].Value = userInfo.State;

            SQLHelper.ExecuteNonQuery(SQLHelper.ConnectionString, CommandType.Text, INSERTQUERY, parms);
        }
        /// <summary>
        /// 修改一个用户信息到数据库中。
        /// </summary>
        /// <param name="userInfo">UserInfo对象，不可为null</param>
        public void Modify(UserInfo userInfo)
        {
            SqlParameter[] parms = new SqlParameter[]{
                                                    new SqlParameter(PARM_LoginId, SqlDbType.NVarChar, 50),
                                                    new SqlParameter(PARM_LoginPwd, SqlDbType.NVarChar, 50),
                                                    new SqlParameter(PARM_Name, SqlDbType.NVarChar, 50),
                                                    new SqlParameter(PARM_Address, SqlDbType.NVarChar, 200),
                                                    new SqlParameter(PARM_Phone, SqlDbType.NVarChar, 100),
                                                    new SqlParameter(PARM_Mail, SqlDbType.NVarChar, 100),
                                                    new SqlParameter(PARM_UserRoleId, SqlDbType.Int, 4),
                                                    new SqlParameter(PARM_UserStateId, SqlDbType.Int, 4),
                                                    new SqlParameter(PARM_Id, SqlDbType.Int, 4)
                                                };
            parms[0].Value = userInfo.LoginId;
            parms[1].Value = userInfo.LoginPwd;
            parms[2].Value = userInfo.Name;
            parms[3].Value = userInfo.Address;
            parms[4].Value = userInfo.Phone;
            parms[5].Value = userInfo.Mail;
            parms[6].Value = userInfo.Role;
            parms[7].Value = userInfo.State;
            parms[8].Value = userInfo.Id;

            SQLHelper.ExecuteNonQuery(SQLHelper.ConnectionString, CommandType.Text, UPDATEQUERY, parms);
        }
        /// <summary>
        ///从数据库中删除自定ID的数据。
        /// </summary>
        /// <param name="id">用户的数据库表中的标识ID。</param>
        public void Delete(int id)
        {
            SqlParameter parm = new SqlParameter(PARM_Id, SqlDbType.Int, 4);
            parm.Value = id;
            SQLHelper.ExecuteNonQuery(SQLHelper.ConnectionString, CommandType.Text, DELETEQUERY, parm);
        }
        /// <summary>
        /// 通过登录ID获得一个用户。
        /// </summary>
        /// <param name="loginId">提供的登录ID。</param>
        /// <returns>返回相应的用户信息。</returns>
        public UserInfo GetUserByLoginId(string loginId) 
        {
            UserInfo userInfo = null;

            SqlParameter parm = new SqlParameter(PARM_LoginId, SqlDbType.NVarChar, 50);
            parm.Value = loginId;
            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.ConnectionString, CommandType.Text, SELECTQUERY + WHEREQUERYBYLOGINID, parm))
            {
                if (rdr.Read())
                    return TransafDataToObject(rdr);
            }
            return userInfo;
        }
        /// <summary>
        /// 通过主键ID获得一个用户。
        /// </summary>
        /// <param name="id">提供的主键ID。</param>
        /// <returns></returns>
        public UserInfo GetUserById(int id)
        {
            UserInfo userInfo = null;

            SqlParameter parm = new SqlParameter(PARM_Id, SqlDbType.Int, 4);
            parm.Value = id;
            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.ConnectionString, CommandType.Text, SELECTQUERY + WHEREQUERYBYID, parm))
            {
                if (rdr.Read())
                    return TransafDataToObject(rdr);
            }
            return userInfo;
        }
        /// <summary>
        /// 获得数据库中所有的用户信息。
        /// </summary>
        /// <returns>返回表示用户信息的集合。</returns>
        public IList<UserInfo> GetList()
        {
            IList<UserInfo> list = null;
            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.ConnectionString, CommandType.Text, SELECTQUERY))
            {
                if (rdr.HasRows)
                    list = new List<UserInfo>();
                else
                    return list;
                while (rdr.Read())
                    list.Add(TransafDataToObject(rdr));
            }
            return list;
        }

        #region private methods
        /// <summary>
        /// 此方法主要负责将数据库中的一列转变成一个UserInfo对象。
        /// </summary>
        /// <param name="rdr"></param>
        /// <returns></returns>
        private UserInfo TransafDataToObject(SqlDataReader rdr)
        { 
            UserRoleInfo role = new UserRole().GetRoleById(rdr.GetInt32(7));
            UserStateInfo state = new UserState().GetStateById(rdr.GetInt32(8));
            return new UserInfo(rdr.GetInt32(0), rdr.GetString(1), rdr.GetString(2), rdr.GetString(3), rdr.GetString(4),
                                                rdr.GetString(5), rdr.GetString(6), role, state);
        }

        #endregion
    }
}