roomdal.cs

酒店管理系统项目需求说明 第一部分 引言 1 1.1编写目的 1 1.2背景 1 第二部分 任务概述 2 3.1实现目标 2 3.1.1客房类型模块： 2 3.1.2客房信息

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Entity;
using System.Data;


namespace DAL
{
    public class RoomDAL
    {
        /// <summary>
        /// 查询Room表
        /// </summary>
        /// <returns></returns>
        public static List<RoomEntity> GetRoom()
        {
            //创建RoomEntity的泛型对象RE
            List<RoomEntity> RE = new List<RoomEntity>();

            //创建以个表格对象dt，用来保存SQLHelp.FillTable(sql语句)所返回的一张表
            DataTable dt = SQLHelp.FillTable("select * from room");

            //如果返回的表格dt不为空并且有数据，则执行一下代码
            if ((dt != null) && (dt.Rows.Count > 0))
            {
                //用foreach循环将dt里的数据取出来放到RoomEntity对象里
                foreach (DataRow dr in dt.Rows)
                {
                    //创建RoomEntity对象R
                    RoomEntity R = new RoomEntity();

                    //将表格的数据封装到R里
                    R.BedNumber = (int)dr["BedNumber"];
                    R.Description = dr["Description"].ToString();
                    R.GuestNumber = (int)dr["GuestNumber"];
                    R.Number = dr["Number"].ToString();
                    R.RoomID = (int)dr["RoomID"];
                    R.State = dr["State"].ToString();
                    R.TypeID = (int)dr["Type"];

                    //循环一次将R放入泛型对象RE里
                    RE.Add(R);
                }
            }
            //返回泛型对象RE
            return RE;
        }

        public static RoomEntity GetRoomByRoomID(int RoomID)
        {
            RoomEntity RE = new RoomEntity();
            string sql = "SELECT dbo.Room.*, dbo.RoomType.TypeName FROM dbo.Room INNER JOIN dbo.RoomType ON dbo.Room.TypeID = dbo.RoomType.TypeID and RoomID = "+RoomID;

            DataTable dt = SQLHelp.FillTable(sql);
            RE.Number = dt.Rows[0]["Number"].ToString();
            RE.State = dt.Rows[0]["State"].ToString();
            RE.GuestNumber = (int)dt.Rows[0]["GuestNumber"];
            RE.TypeID = (int)dt.Rows[0]["TypeID"];
            RE.Description = dt.Rows[0]["Description"].ToString();
            RE.BedNumber = (int)dt.Rows[0]["BedNumber"];
            RE.TypeName = dt.Rows[0]["TypeName"].ToString();
            return RE;

        }

        public static DataTable GetStateByRoomID(int RoomID)
        {
            string sql = "select state from Room where RoomID = "+RoomID;
            return SQLHelp.FillTable(sql);

        }

        public static DataTable GetRoom_RoomTypeInfo(string TypeName,int BedNumber,int GuestNumber)
        {
          //  string sql = "EXEC SEL_ROOM @TypeName,@BedNumber,@GuestNumber";

            string sql = "EXEC SEL_ROOM '"+TypeName+"',"+BedNumber+","+GuestNumber+"";
            return SQLHelp.FillTable(sql);
        }

        public static DataTable GetRoom_RoomTypeInfo()
        {
            //  string sql = "EXEC SEL_ROOM @TypeName,@BedNumber,@GuestNumber";

            string sql = "EXEC SEL_ROOM1";
            return SQLHelp.FillTable(sql);
        }


        public static DataTable GetRoom_RoomTypeInfoByRoomID(int RoomID)
        {
            string sql = "select * from view_room_roomtype where roomid="+RoomID;
            return SQLHelp.FillTable(sql);
        }


        public static DataTable GetStateByNumber(string Number)
        {
            string sql = "select state from room where Number = '" + Number + "'";
            return SQLHelp.FillTable(sql);
        }

        public static DataTable GetUserByFilter(string TypeName, string Number)
        {
            string sql = "";
            if (Number != "")
            {
                sql = "select * from View_RoomInfoByFilter where TypeName = '"+TypeName+"' and Number = '"+Number+"'";
            }
            else
            {
                sql = "select * from View_RoomInfoByFilter where TypeName = '"+TypeName+"'";
            }
            return SQLHelp.FillTable(sql);
        }


        public static DataTable GetUserByFilter()
        {
            string sql = "select * from View_RoomInfoByFilter";
            return SQLHelp.FillTable(sql);
        }

        public static int GetRoomIDByNumber(string Number)
        { 
            string sql = "select RoomID from room where number = '"+Number+"'";
            return Convert.ToInt32(SQLHelp.FillTable(sql).Rows[0][0]);
        }

        public static bool SelectRoom(string Number)
        {
            string sql = "select * from Room where Number =  '" + Number + "'";
            DataTable dd = new DataTable();
            dd = SQLHelp.FillTable(sql);
            if (dd.Rows.Count > 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }

        /// <summary>
        /// 增加Room表
        /// </summary>
        /// <param name="RE"></param>
        /// <returns></returns>
        public static int AddRoom(RoomEntity RE)
        {

            //从传进来的RoomEntity对象拼凑sql语句
            string sql = "insert into Room values('"+RE.Number+"',"+RE.BedNumber+",'"+RE.Description+"','"+RE.State+"',"+RE.GuestNumber+","+RE.TypeID+")";

            //调用SQLHelp类的ExecQuery() 方法执行拼凑的sql，及返回相应的数据
            return SQLHelp.ExecQuery(sql);
        }
        /// <summary>
        /// 修改Room表
        /// </summary>
        /// <param name="RE"></param>
        /// <returns></returns>
        public static int UpdateRoom(RoomEntity RE)
        {
            //从传进来的RoomEntity对象拼凑sql语句
            string sql = "update Room set Number = '"+RE.Number+"',BedNumber = "+RE.BedNumber+" ,Description = '"+RE.Description+"',State = '"+RE.State+"',GuestNumber = "+RE.GuestNumber+" ,TypeID = "+RE.TypeID+" where RoomID = "+RE.RoomID;

            //调用SQLHelp类的ExecQuery() 方法执行拼凑的sql，及返回相应的数据
            return SQLHelp.ExecQuery(sql);
        }

        public static int UpdateRoomByNumber(RoomEntity RE)
        {
            string sql = "update Room set BedNumber = " + RE.BedNumber + " ,Description = '" + RE.Description + "',State = '" + RE.State + "',GuestNumber = " + RE.GuestNumber + " ,TypeID = " + RE.TypeID + " where RoomID = '" + RE.RoomID + "'";
            return SQLHelp.ExecQuery(sql);
        }

        public static int UpdateStateByRoomID(int RoomID)
        {
            string sql = "update Room set State = '已预订' where RoomID = "+RoomID;
            return SQLHelp.ExecQuery(sql);
        }

        public static int UpdateStateByNumber(string Number)
        {
            string sql = "update Room set State = '空房' where Number = '"+Number+"'";
            return SQLHelp.ExecQuery(sql);
        }




        /// <summary>
        /// 删除Room表
        /// </summary>
        /// <param name="RE"></param>
        /// <returns></returns>
        public static int DeleteRoom(RoomEntity RE)
        {
            //从传进来的RoomEntity对象拼凑sql语句
            string sql = "delete from Room where RoomID = "+RE.RoomID;

            //调用SQLHelp类的ExecQuery() 方法执行拼凑的sql，及返回相应的数据
            return SQLHelp.ExecQuery(sql);
        }
    }
}