accessroomoperatorservice.cs

酒店管理 主要实现了基础设施管理（客房管理、客房类型管理）、业务管理（入住、退房、数据库切换） 本系统简单明了,适合初学者学习,采用三层加抽象工厂实现

        /// <summary>
        /// 添加房间信息
        /// </summary>
        /// <param name="room"></param>
        /// <returns></returns>
        public bool InsertRoomValues(Model.Room room)
        {
            int count = 0;
            StringBuilder sqlRoom = new StringBuilder();//使用StringBuilder提高性能
            sqlRoom.Append(INSERT_INTO_ROOM).Append(PARM_ROOM_NUMBER).Append("," + PARM_BED_NUMBER).Append("," + PARM_DESCRIPTION).Append("," + PARM_STATE).Append("," + PARM_GUESS_NUMBER).Append("," + PARM_TYPE_ID + ")");

            try
            {
                OleDbParameter[] parms = this.CreateParameters();
                parms[0].Value = room.Roomnumber;
                parms[1].Value = room.Bednumber;
                parms[2].Value = room.Description;
                parms[3].Value = room.State;
                parms[4].Value = room.Guessnumber;

                RoomType temp = roomTypeOp.SelectRoomTypeByTypeName(room.TypeId.Typename);
                if (temp != null)
                    parms[5].Value = temp.Typeid;
                count = AccessHelper.ExecuteNonQuery(AccessHelper.AccessConnString, sqlRoom.ToString(), CommandType.Text, parms);

            }
            catch (OleDbException ex)
            {
                throw ex;
            }
            if (count > 0)
                return true;
            else
                return false;
        }

        /// <summary>
        /// 修改房间信息
        /// </summary>
        /// <param name="room"></param>
        /// <returns></returns>
        public bool ModifyRoomValues(Model.Room room)
        {
            int count = 0;
            StringBuilder sql = new StringBuilder();//使用StringBuilder提高性能
            sql.Append("update Room set Numbers=").Append(PARM_ROOM_NUMBER).Append(",BedNumber=" + PARM_BED_NUMBER);
            sql.Append(",Description=" + PARM_DESCRIPTION).Append(",State=" + PARM_STATE).Append(",GuessNumber=" + PARM_GUESS_NUMBER);
            sql.Append(",TypeID=" + PARM_TYPE_ID + " where RoomId=" + PARM_ROOM_ID);
            try
            {
                //声明参数集合
                OleDbParameter[] parms = new OleDbParameter[7];
                int i = 0;
                foreach (OleDbParameter parm in CreateParameters())
                {
                    parms[i] = parm;
                    i++;
                }
                parms[6] = new OleDbParameter(PARM_ROOM_ID, OleDbType.Integer);
                //给指定的参数赋值
                parms[0].Value = room.Roomnumber;
                parms[1].Value = room.Bednumber;
                parms[2].Value = room.Description;
                parms[3].Value = room.State;
                parms[4].Value = room.Guessnumber;

                RoomType temp = roomTypeOp.SelectRoomTypeByTypeName(room.TypeId.Typename);
                if (temp != null)
                    parms[5].Value = temp.Typeid;
                parms[6].Value = room.Roomid;
                //调用AccessHelper类的方法返回执行的结果并判断是否修改成功
                count = AccessHelper.ExecuteNonQuery(AccessHelper.AccessConnString, sql.ToString(), CommandType.Text, parms);
            }
            catch (OleDbException ex)
            {
                throw ex;
            }
            if (count > 0)
                return true;
            else
                return false;
        }

        /// <summary>
        /// 删除房间信息
        /// </summary>
        /// <param name="room"></param>
        /// <returns></returns>
        public bool DeleteRoomValues(Model.Room room)
        {
            int count = 0;
            string sql = "delete from Room where RoomId = @RoomID";
            try
            {
                OleDbParameter[] parms = new OleDbParameter[1];
                parms[0] = new OleDbParameter("@RoomID", OleDbType.Integer);
                parms[0].Value = room.Roomid;
                count = AccessHelper.ExecuteNonQuery(AccessHelper.AccessConnString, sql, CommandType.Text, parms);
            }
            catch (OleDbException ex)
            {
                throw ex;
            }
            if (count > 0)
                return true;
            else
                return false;
        }

        /// <summary>
        /// 创建参数数组
        /// </summary>
        /// <returns></returns>
        private OleDbParameter[] CreateParameters()
        {
            OleDbParameter[] parms = new OleDbParameter[6];
            parms[0] = new OleDbParameter(PARM_ROOM_NUMBER, OleDbType.VarChar);
            parms[1] = new OleDbParameter(PARM_BED_NUMBER, OleDbType.Integer);
            parms[2] = new OleDbParameter(PARM_DESCRIPTION, OleDbType.VarChar);
            parms[3] = new OleDbParameter(PARM_STATE, OleDbType.VarChar);
            parms[4] = new OleDbParameter(PARM_GUESS_NUMBER, OleDbType.Integer);
            parms[5] = new OleDbParameter(PARM_TYPE_ID, OleDbType.Integer);
            return parms;
        }
        /// <summary>
        /// 按房间号查询房间编号
        /// </summary>
        /// <param name="number"></param>
        /// <returns></returns>
        public int GetRoomIdByRoomNumber(string number)
        {
            this._con = new OleDbConnection(AccessHelper.AccessConnString);
            string sql = "select RoomId from Room where Numbers='" + number + "'";
            int id = 0;
            if (this._con != null)
            {
                try
                {
                    this._con.Open();//打开连接
                    this._cmd = _con.CreateCommand();//使用连接对象的方法创建命令对象
                    this._cmd.CommandText = sql;//指定命令的T-SQL语句
                    this._cmd.Connection = _con;//指定命令的连接对象
                    object temp = this._cmd.ExecuteScalar();//指定命令并返回SqlDataReader只进只读的对象
                    if (temp != null)//如果有数据
                        id = int.Parse(temp.ToString());
                }
                catch (OleDbException ex)
                {
                    throw ex;
                }
                finally
                {
                    this._con.Close();
                }
            }
            return id;
        }

        public IList<Room> GetAllRoomsByTypeId(int roomTypeId)
        {
            //实例化连接对象
            this._con = new OleDbConnection(AccessHelper.AccessConnString);
            string sql = "select RoomId,Numbers,BedNumber,Description,State,GuessNumber,TypeID from Room where TypeID=" + roomTypeId;
            //泛型集合
            IList<Room> list = new List<Room>();
            if (this._con != null)
            {
                try
                {
                    this._con.Open();
                    _adapter = new OleDbDataAdapter(sql, _con);//实例化数据适配器
                    DataSet ds = new DataSet();//实例化数据集对象
                    _adapter.Fill(ds, "Room");//使用数据适配器填充数据集
                    //遍历数据集中的行并添加到泛型集合中
                    for (int i = 0; i < ds.Tables["Room"].Rows.Count; i++)
                    {
                        Room temp = new Room();
                        temp.Roomid = Convert.ToInt32(ds.Tables["Room"].Rows[i][0]);
                        temp.Roomnumber = ds.Tables["Room"].Rows[i][1].ToString();
                        temp.Bednumber = Convert.ToInt32(ds.Tables["Room"].Rows[i][2]);
                        temp.Description = ds.Tables["Room"].Rows[i][3].ToString();
                        temp.State = ds.Tables["Room"].Rows[i][4].ToString();
                        temp.Guessnumber = Convert.ToInt32(ds.Tables["Room"].Rows[i][5]);
                        temp.TypeId = roomTypeOp.SelectRoomTypeByID(int.Parse(ds.Tables["Room"].Rows[i][6].ToString()));
                        list.Add(temp);
                    }
                }
                catch
                {
                    list = null;
                    throw;
                }
                finally
                {
                    this._con.Close();
                }
            }
            return list;
        }


        #endregion
    }
}