userstate.cs

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

using System;
using System.Collections.Generic;
using System.Text;
using IAS.BookShop.Model;
using System.Data;
using System.Data.SqlClient;
namespace IAS.BookShop.DAL
{
    public class UserState
    {
        #region 
        private const string PARM_Id = "@Id";
        private const string PARM_Name = "@Name";
        private const string SELECTQUERY = "SELECT Id, Name FROM  UserStates";
        private const string WHEREQUERY = " WHERE Id = @Id";
        private const string INSERTQUERY = "INSERT INTO UserStates (Name) VALUES (@Name)";
        private const string UPDATEQUERY = "UPDATE UserStates SET (Name = @Name) WHERE Id = @Id";
        private const string DELETEQUERY = "DELETE FROM UserStates WHERE Id = @Id";

        #endregion
        public void Add(UserStateInfo userStateInfo)
        {
            SqlParameter parm = new SqlParameter(PARM_Name, SqlDbType.NVarChar, 50);
            parm.Value = userStateInfo.Name;
            SQLHelper.ExecuteNonQuery(SQLHelper.ConnectionString, CommandType.Text, INSERTQUERY, parm);
        }
        public void Modify(UserStateInfo userStateInfo)
        {
            SqlParameter parm = new SqlParameter(PARM_Name, SqlDbType.NVarChar, 50);
            parm.Value = userStateInfo.Name;
            SQLHelper.ExecuteNonQuery(SQLHelper.ConnectionString, CommandType.Text, UPDATEQUERY, parm);
        }
        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);
        }
        public UserStateInfo GetStateById(int id)
        {
            SqlParameter parm = new SqlParameter(PARM_Id, SqlDbType.Int, 4);
            parm.Value = id;
            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.ConnectionString, CommandType.Text,
                                                                        SELECTQUERY + WHEREQUERY, parm))
            {
                if (rdr.Read())
                    return new UserStateInfo(rdr.GetInt32(0), rdr.GetString(1));
            }
            return null;
        }
        public IList<UserStateInfo> GetList()
        {
            IList<UserStateInfo> list = null;
            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.ConnectionString, CommandType.Text, SELECTQUERY))
            { 
                if(rdr.HasRows)
                    list = new List<UserStateInfo>();
                else
                    return list;

                while(rdr.Read())
                    list.Add(new UserStateInfo(rdr.GetInt32(0), rdr.GetString(1)));
            }
            return list;
        }
    }
}