roomdal.cs

酒店管理系统

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using HotelManagerModels;

namespace HotelManagerDAL
{
    public class RoomDAL
    {
        //从配置文件中取出数据库连接的字符串
        private string strConn = ConfigurationManager.ConnectionStrings["HotelConn"].ToString();

        #region 查询所有房间信息
        /// <summary>
        /// 查询所有房间信息
        /// </summary>
        /// <returns>房间集合</returns>
        public List<Room> SelectRoomsAll()
        { 
            using(SqlConnection conn = new SqlConnection(strConn))
            {
                List<Room> list = new List<Room>();
                SqlCommand cmd = new SqlCommand("usp_SelectRoomsAll", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                try
                {
                    conn.Open();
                    SqlDataReader dr = cmd.ExecuteReader();
                    while (dr.Read())
                    {
                        Room r = new Room();
                        RoomStatus rs=new RoomStatus();
                        r.RoomID = Convert.ToInt32(dr["RoomID"]);
                        r.Number = dr["Number"].ToString();
                        r.TypeID = Convert.ToInt32(dr["TypeID"]);
                        r.BedNumber = Convert.ToInt32(dr["BedNumber"]);
                        r.Description = dr["Description"].ToString();
                        rs.ID = Convert.ToInt32(dr["StatusID"]);
                        rs.Status = dr["Status"].ToString();
                        r.State = rs;
                        r.GuestNumber = Convert.ToInt32(dr["GuestNumber"]);
                        list.Add(r);
                    }
                    return list;
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
                finally
                {
                    conn.Close();
                }
            }
        }
        #endregion

        #region 根据房间号查询房间信息
        /// <summary>
        /// 根据房间号查询房间信息
        /// </summary>
        /// <param name="number">房间号</param>
        /// <returns>房间集合</returns>
        public List<Room> SelectRoomsByRoomNumber(string number)
        {
            using (SqlConnection conn = new SqlConnection(strConn))
            {
                List<Room> list = new List<Room>();
                SqlCommand cmd = new SqlCommand("usp_SelectRoomsByRoomNumber", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@Number", SqlDbType.NVarChar, 50).Value = number;
                try
                {
                    conn.Open();
                    SqlDataReader dr = cmd.ExecuteReader();
                    while (dr.Read())
                    {
                        Room r = new Room();
                        RoomStatus rs = new RoomStatus();
                        r.RoomID = Convert.ToInt32(dr["RoomID"]);
                        r.Number = dr["Number"].ToString();
                        r.TypeID = Convert.ToInt32(dr["TypeID"]);
                        r.BedNumber = Convert.ToInt32(dr["BedNumber"]);
                        r.Description = dr["Description"].ToString();
                        rs.Status = dr["Status"].ToString();
                        r.State = rs;
                        r.GuestNumber = Convert.ToInt32(dr["GuestNumber"]);
                        list.Add(r);
                    }
                    return list;
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
                finally
                {
                    conn.Close();
                }
            }
        }
        #endregion

        #region 根据房间编号查询房间信息
        /// <summary>
        /// 根据房间编号查询房间信息
        /// </summary>
        /// <param name="roomID">房间编号</param>
        /// <returns>泛型集合</returns>
        public List<Room> SelectRoomByRoomID(int roomID)
        {
            using (SqlConnection conn = new SqlConnection(strConn))
            {
                List<Room> list = new List<Room>();
                SqlCommand cmd = new SqlCommand("usp_SelectRoom", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@RoomID", SqlDbType.Int).Value = roomID;
                try
                {
                    conn.Open();
                    SqlDataReader dr = cmd.ExecuteReader();
                    while (dr.Read())
                    {
                        Room r = new Room();
                        RoomStatus rs = new RoomStatus();
                        r.RoomID = Convert.ToInt32(dr["RoomID"]);
                        r.Number = dr["Number"].ToString();
                        r.TypeID = Convert.ToInt32(dr["TypeID"]);
                        r.BedNumber = Convert.ToInt32(dr["BedNumber"]);
                        r.Description = dr["Description"].ToString();
                        rs.ID = Convert.ToInt32(dr["StatusID"]);
                        rs.Status = dr["Status"].ToString();
                        r.State = rs;
                        r.GuestNumber = Convert.ToInt32(dr["GuestNumber"]);
                        list.Add(r);
                    }
                    return list;
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
                finally
                {
                    conn.Close();
                }
            }
        }
        #endregion

        #region 增加房间信息
        /// <summary>
        /// 增加房间信息
        /// </summary>
        /// <param name="room">房间对象</param>
        /// <returns>bool</returns>
        public void InsertRomm(Room room)
        {
            using(SqlConnection conn=new SqlConnection(strConn))
            {
                SqlCommand cmd = new SqlCommand("usp_InsertRoom", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                SqlParameter[] pars = new SqlParameter[]{
                    new SqlParameter("@Number",SqlDbType.NVarChar,50),
                    new SqlParameter("@TypeID",SqlDbType.Int),
                    new SqlParameter("@BedNumber",SqlDbType.Int),
                    new SqlParameter("@Description",SqlDbType.NVarChar,50),
                    new SqlParameter("@StatusID",SqlDbType.Int),
                    new SqlParameter("@GuestNumber",SqlDbType.Int)
                };
                pars[0].Value = room.Number;
                pars[1].Value = room.TypeID;
                pars[2].Value = room.BedNumber;
                pars[3].Value = room.Description;
                pars[4].Value = room.State.ID;
                pars[5].Value = room.GuestNumber;
                foreach(SqlParameter par in pars)
                {
                    cmd.Parameters.Add(par);
                }
                try
                {
                    conn.Open();
                    cmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
                finally
                {
                    conn.Close();
                }
            }
        }
        #endregion
        
        #region 修改房间信息
        /// <summary>
        /// 修改房间信息
        /// </summary>
        /// <param name="room">room实体对象</param>