roomoperatorservice.cs

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

            if (this._con != null)
            {
                try
                {
                    this._con.Open();
                    _adapter = new SqlDataAdapter(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;
        }

        /// <summary>
        /// 根据房间状态查询房间信息
        /// </summary>
        /// <param name="state"></param>
        /// <returns></returns>
        public IList<Room> GetRoomBySafeSql(string sql)
        {
            //实例化连接对象
            this._con = new SqlConnection(SQLHelper.SQLConnString);
            //泛型集合
            IList<Room> list = new List<Room>();
            if (this._con != null)
            {
                try
                {
                    this._con.Open();
                    _adapter = new SqlDataAdapter(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;
        }


        /// <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
            {
                SqlParameter[] 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 = SQLHelper.ExecuteNonQuery(SQLHelper.SQLConnString, sqlRoom.ToString(), CommandType.Text, parms);

            }
            catch (SqlException 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 Number=").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=@RoomID");
            try
            {
                //声明参数集合
                SqlParameter[] parms = new SqlParameter[7];
                int i = 0;
                foreach (SqlParameter parm in CreateParameters())
                {
                    parms[i] = parm;
                    i++;
                }
                parms[6] = new SqlParameter("@RoomID", SqlDbType.Int);
                //给指定的参数赋值
                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;
                //调用SQLHelper类的方法返回执行的结果并判断是否修改成功
                count = SQLHelper.ExecuteNonQuery(SQLHelper.SQLConnString, sql.ToString(), CommandType.Text, parms);
            }
            catch (SqlException 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 =" + PARM_ROOM_ID;
            try
            {
                SqlParameter[] parms = new SqlParameter[1];
                parms[0] = new SqlParameter(PARM_ROOM_ID, SqlDbType.Int);
                parms[0].Value = room.Roomid;
                count = SQLHelper.ExecuteNonQuery(SQLHelper.SQLConnString, sql, CommandType.Text, parms);
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            if (count > 0)
                return true;
            else
                return false;
        }

        /// <summary>
        /// 创建参数数组
        /// </summary>
        /// <returns></returns>
        private SqlParameter[] CreateParameters()
        {
            SqlParameter[] parms = new SqlParameter[6];
            parms[0] = new SqlParameter(PARM_ROOM_NUMBER, SqlDbType.VarChar);
            parms[1] = new SqlParameter(PARM_BED_NUMBER, SqlDbType.Int);
            parms[2] = new SqlParameter(PARM_DESCRIPTION, SqlDbType.VarChar);
            parms[3] = new SqlParameter(PARM_STATE, SqlDbType.VarChar);
            parms[4] = new SqlParameter(PARM_GUESS_NUMBER, SqlDbType.Int);
            parms[5] = new SqlParameter(PARM_TYPE_ID, SqlDbType.Int);
            return parms;
        }

        #endregion
    }
}
//51ａsｐｘ