accessroomoperatorservice.cs

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

using System;
using System.Collections.Generic;
using System.Text;

using System.Collections;
using System.Data.OleDb;
using System.Data;
using IDAL;
using Model;
using DBUtility;

namespace AccessDAL
{
    public class AccessRoomOperatorService : IRoom
    {
        #region IOperator 成员

        private OleDbConnection _con = null;//连接对象
        private OleDbDataAdapter _adapter = null;//数据适配器,用来填充DataSet
        private OleDbCommand _cmd = null;//命令对象
        private AccessRoomTypeOperatorService roomTypeOp = new AccessRoomTypeOperatorService();//操作房间类型的数据访问层类
        //Parameters 初始化参数常量
        private const string INSERT_INTO_ROOM = "insert into Room(Numbers,BedNumber,Description,State,GuessNumber,TypeID) values(";
        private const string PARM_ROOM_ID = "@roomId";
        private const string PARM_ROOM_NUMBER = "@number";
        private const string PARM_BED_NUMBER = "@bedNumber";
        private const string PARM_DESCRIPTION = "@description";
        private const string PARM_STATE = "@state";
        private const string PARM_GUESS_NUMBER = "@guessNumber";
        private const string PARM_TYPE_ID = "@typeID";
        /// <summary>
        /// 查询所有的房间信息
        /// </summary>
        /// <returns></returns>
        public IList<Model.Room> SelectAllDataByRoom()
        {
            //实例化连接对象
            this._con = new OleDbConnection(AccessHelper.AccessConnString);
            string sql = "select RoomId,Numbers,BedNumber,Description as ShortDescription,State,GuessNumber,TypeID from Room order by Numbers";
            //泛型集合
            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;
        }

        /// <summary>
        /// 按房间编号查询房间信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Room SelectRoomByID(int id)
        {
            this._con = new OleDbConnection(AccessHelper.AccessConnString);
            string sql = "select * from Room where RoomId=" + PARM_ROOM_ID;
            Room room = null;
            if (this._con != null)
            {
                try
                {
                    this._con.Open();//打开连接
                    this._cmd = _con.CreateCommand();//使用连接对象的方法创建命令对象
                    this._cmd.CommandText = sql;//指定命令的T-SQL语句
                    this._cmd.Connection = _con;//指定命令的连接对象
                    this._cmd.Parameters.Add(PARM_ROOM_ID, OleDbType.Integer).Value = id;//添加命令中的参数
                    OleDbDataReader dr = this._cmd.ExecuteReader();//指定命令并返回SqlDataReader只进只读的对象
                    if (dr.Read())//如果可以读取
                    {
                        room = new Room();
                        room.Roomid = dr.GetInt32(0);
                        room.Roomnumber = dr.GetString(1);
                        room.Bednumber = dr.GetInt32(2);
                        room.Description = dr.GetString(3);
                        room.State = dr.GetString(4);
                        room.Guessnumber = dr.GetInt32(5);
                        room.TypeId = roomTypeOp.SelectRoomTypeByID(dr.GetInt32(6));
                        dr.Close();
                    }
                }
                catch (OleDbException ex)
                {
                    room = null;
                    throw ex;
                }
                finally
                {
                    this._con.Close();
                }
            }
            return room;
        }

        /// <summary>
        /// 按房间号查询房间信息(模糊查询)
        /// </summary>
        /// <returns></returns>
        public IList<Room> SelectRoomByNumber(string number)
        {
            //实例化连接对象
            this._con = new OleDbConnection(AccessHelper.AccessConnString);
            string sql = "select RoomId,Numbers,BedNumber,Description as ShortDescription,State,GuessNumber,TypeID from Room where Numbers like " + number;
            //泛型集合
            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;
        }


        public IList<Room> GetRoomBySafeSql(string sql)
        {
            //实例化连接对象
            this._con = new OleDbConnection(AccessHelper.AccessConnString);
            //泛型集合
            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;
        }