sqlserverroomservice.cs

酒店管理系统项目内容 包含当前常用的全部功能

using System;
using System.Collections.Generic;
using System.Text;
using MyHotelIDAL;
using MyHotelModel;
using System.Data.SqlClient;
using System.Data;

namespace MyHotelDAL
{
    public class SqlServerRoomService:IRoomService
    {
        SqlServerDBHelper helper;

        public SqlServerRoomService()
        {
            helper = new SqlServerDBHelper();
        }

        #region IRoomService 成员

        public bool InsertRoom(Room room)
        {
            string sql = "insert into Room(Number,TypeID,BedNumber,Description,State,GuestNumber)" +
                "values(@Number,@TypeID,@BedNumber,@Description,@State,@GuestNumber)";
            SqlParameter[] paras = new SqlParameter[]{
                    new SqlParameter("@Number",room.Number),
                    new SqlParameter("@TypeID",room.TypeId),
                    new SqlParameter("@BedNumber",room.BedNumber),
                    new SqlParameter("@Description",room.Description),
                    new SqlParameter("@State",room.State),
                    new SqlParameter("@GuestNumber",room.GuestNumber)
                };
            return helper.ModifyDB(sql, paras);
        }

        public bool UpdateRoom(Room room)
        {
            string sql = "update Room set Number=@Number,TypeID=@TypeID,BedNumber=@BedNumber,Description=@Description,State=@State,GuestNumber=@GuestNumber where RoomID=@RoomID ";

            SqlParameter[] paras = new SqlParameter[]{
                    new SqlParameter("@Number",room.Number),
                    new SqlParameter("@TypeID",room.TypeId),
                    new SqlParameter("@BedNumber",room.BedNumber),
                    new SqlParameter("@Description",room.Description),
                    new SqlParameter("@State",room.State),
                    new SqlParameter("@GuestNumber",room.GuestNumber),
                    new SqlParameter("@RoomID",room.RoomId)
                };
            return helper.ModifyDB(sql, paras);
        }

        public bool DeleteRoom(int id)
        {
            string sql = "delete from Room where RoomID=@RoomID";

            SqlParameter para = new SqlParameter("@RoomID", id);

            return helper.ModifyDB(sql, para);
        }

        public IList<Room> SelectAllRooms()
        {
            string sql = "select RoomID,Number,TypeID,BedNumber,Description,State,GuestNumber from Room";

            DataSet ds = helper.GetDataSet(sql);

            IList<Room> list = new List<Room>();

            foreach (DataRow row in ds.Tables[0].Rows)
            {
                Room room = new Room();
                room.RoomId = Convert.ToInt32(row["RoomID"]);
                room.Number = row["Number"].ToString();
                room.TypeId = Convert.ToInt32(row["TypeID"]);
                room.BedNumber = Convert.ToInt32(row["BedNumber"]);
                room.Description = row["Description"].ToString();
                room.State = row["State"].ToString();
                room.GuestNumber = Convert.ToInt32(row["GuestNumber"]);

                list.Add(room);
            }

            return list;
        }

        public Room SelectRoomById(int id)
        {
            string sql = "select RoomID,Number,TypeID,BedNumber,Description,State,GuestNumber from Room where RoomID=@RoomID";

            DataSet ds = null;

            SqlParameter para = new SqlParameter("@RoomID", id);

            ds = helper.GetDataSet(sql, para);

            Room room = new Room();

            foreach (DataRow row in ds.Tables[0].Rows)
            {
                room.RoomId = Convert.ToInt32(row["RoomID"]);
                room.Number = row["Number"].ToString();
                room.TypeId = Convert.ToInt32(row["TypeID"]);
                room.BedNumber = Convert.ToInt32(row["BedNumber"]);
                room.Description = row["Description"].ToString();
                room.State = row["State"].ToString();
                room.GuestNumber = Convert.ToInt32(row["GuestNumber"]);
            }

            return room;
        }

        public IList<Room> SelectRoomByRoomNumber(string roomNumber)
        {
            string sql = "select RoomID,Number,TypeID,BedNumber,Description,State,GuestNumber from Room ";

            DataSet ds = null;

            if (roomNumber != "")
            {
                sql += "where Number = @Number";

                SqlParameter para = new SqlParameter("@Number", roomNumber);

                ds = helper.GetDataSet(sql, para);
            }
            else
            {
                ds = helper.GetDataSet(sql);
            }

            IList<Room> list = new List<Room>();

            foreach (DataRow row in ds.Tables[0].Rows)
            {
                Room room = new Room();

                room.RoomId = Convert.ToInt32(row["RoomID"]);
                room.Number = row["Number"].ToString();
                room.TypeId = Convert.ToInt32(row["TypeID"]);
                room.BedNumber = Convert.ToInt32(row["BedNumber"]);
                room.Description = row["Description"].ToString();
                room.State = row["State"].ToString();
                room.GuestNumber = Convert.ToInt32(row["GuestNumber"]);

                list.Add(room);
            }

            return list;
        }

        #endregion
    }
}