roomoperatorservice.cs

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

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

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

namespace SQLServerDAL
{
    public class RoomOperatorService : IRoom
    {
        #region IOperator 成员

        private SqlConnection _con = null;//连接对象
        private SqlDataAdapter _adapter = null;//数据适配器,用来填充DataSet
        private SqlCommand _cmd = null;//命令对象
        private RoomTypeOperatorService roomTypeOp = new RoomTypeOperatorService();//操作房间类型的数据访问层类
        //Parameters 初始化参数常量
        private const string INSERT_INTO_ROOM = "insert into Room(Number,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 SqlConnection(SQLHelper.SQLConnString);
            string sql = "select RoomId,Number,BedNumber,SubString(Description,0,40) as ShortDescription,State,GuessNumber,TypeID from Room order by Number";
            //泛型集合
            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="id"></param>
        /// <returns></returns>
        public Room SelectRoomByID(int id)
        {
            this._con = new SqlConnection(SQLHelper.SQLConnString);
            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, SqlDbType.Int).Value = id;//添加命令中的参数
                    SqlDataReader 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 (SqlException ex)
                {
                    room = null;
                    throw ex;
                }
                finally
                {
                    this._con.Close();
                }
            }
            return room;
        }

        /// <summary>
        /// 按房间号查询房间编号
        /// </summary>
        /// <param name="number"></param>
        /// <returns></returns>
        public int GetRoomIdByRoomNumber(string number)
        {
            this._con = new SqlConnection(SQLHelper.SQLConnString);
            string sql = "select RoomId from Room where Number=" + 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 (SqlException ex)
                {
                    throw ex;
                }
                finally
                {
                    this._con.Close();
                }
            }
            return id;
        }


        /// <summary>
        /// 按类型编号查询房间信息
        /// </summary>
        /// <param name="roomTypeId"></param>
        /// <returns></returns>
        public IList<Room> GetAllRoomsByTypeId(int roomTypeId)
        {
            //实例化连接对象
            this._con = new SqlConnection(SQLHelper.SQLConnString);
            string sql = "select RoomId,Number,BedNumber,SubString(Description,0,40) as ShortDescription,State,GuessNumber,TypeID from Room where TypeID=" + roomTypeId;
            //泛型集合
            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>
        /// <returns></returns>
        public IList<Room> SelectRoomByNumber(string number)
        {
            //实例化连接对象
            this._con = new SqlConnection(SQLHelper.SQLConnString);
            string sql = "select RoomId,Number,BedNumber,SubString(Description,0,40) as ShortDescription,State,GuessNumber,TypeID from Room where Number like '" + number + "%'";
            //泛型集合
            IList<Room> list = new List<Room>();