roomservice.cs

简单的酒店管理系统 c# +9 SQLserver 2

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using HotelManager.Models;
/***********************************
 * 类名: RoomService
 * 创建日期: 2007-9-15
 * 功能描述:提供客房信息数据访问
 * ********************************/
namespace HotelManager.DAL
{
    public class RoomService
    {
        #region Private Members
        //从配置文件中读取数据库连接字符串
        private readonly string connString = ConfigurationManager.ConnectionStrings["HotelManagerConnectionString"].ToString();
        private readonly string dboOwner = ConfigurationManager.ConnectionStrings["DataBaseOwner"].ToString();
        #endregion

        #region Public Methods
        /// <summary>
        /// 新增客房信息
        /// </summary>
        /// <param name="room">客房实体对象</param>
        /// <returns></returns>
        public int AddRoom(Room room)
        {
            int roomID;
            using(SqlConnection conn = new SqlConnection(connString))
            {
                SqlCommand objCommand = new SqlCommand(dboOwner + ".usp_InsertRoom", conn);
                objCommand.CommandType = CommandType.StoredProcedure;

                objCommand.Parameters.Add("@BedNumber", SqlDbType.NVarChar, 50).Value = room.BedNumber;
                objCommand.Parameters.Add("@Description", SqlDbType.NVarChar, 50).Value = room.Description;
                objCommand.Parameters.Add("@GuestNumber", SqlDbType.Int).Value = room.GuestNumber;
                objCommand.Parameters.Add("@Number", SqlDbType.NVarChar, 255).Value = room.Number;
                objCommand.Parameters.Add("@State", SqlDbType.NVarChar, 50).Value = room.State;
                objCommand.Parameters.Add("@TypeID", SqlDbType.Int).Value = room.TypeID;
                conn.Open();
                roomID = Convert.ToInt32(objCommand.ExecuteScalar());
                conn.Close();
            }
            return roomID;
        }
        /// <summary>
        /// 根据客房号得到客房ＩＤ
        /// </summary>
        /// <param name="roomNumber">房间号</param>
        /// <returns></returns>
        public  int GetRoomIDByRoomNumber(string roomNumber)
        {
            int roomID =0;
            using (SqlConnection conn = new SqlConnection(connString))
            {
                SqlCommand objCommand = new SqlCommand(dboOwner + ".usp_SelectRoomIDByNumber", conn);
                objCommand.CommandType = CommandType.StoredProcedure;
                objCommand.Parameters.Add("@Number", SqlDbType.NVarChar, 255).Value = roomNumber;
                conn.Open();
                using (SqlDataReader objReader = objCommand.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    if (objReader.Read())
                    {
                        roomID = Convert.ToInt32(objReader["RoomID"]);
                    }
                    objReader.Close();
                    objReader.Dispose();
                }
                conn.Close();
                conn.Dispose();
            }
            return roomID;
        }
        /// <summary>
        /// 得到客房信息列表
        /// </summary>
        /// <returns></returns>
        public  IList<Room> GetRoomAll()
        {
            IList<Room> rooms = new List<Room>();
            using (SqlConnection conn = new SqlConnection(connString))
            {
                SqlCommand objCommand = new SqlCommand(dboOwner + ".usp_SelectRoomsAll", conn);
                objCommand.CommandType = CommandType.StoredProcedure;
                conn.Open();
                using (SqlDataReader objReader = objCommand.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (objReader.Read())
                    {
                        Room room = new Room();
                        room.RoomId = Convert.ToInt32(objReader["RoomID"]);
                        room.BedNumber = Convert.ToInt32(objReader["BedNumber"]);
                        room.Description = Convert.ToString(objReader["Description"]);
                        room.GuestNumber = Convert.ToInt32(objReader["GuestNumber"]);
                        room.Number = Convert.ToString(objReader["Number"]);
                        room.State = Convert.ToString(objReader["State"]);
                        rooms.Add(room);
                    }
                }
                conn.Close();
                conn.Dispose();
            }
            return rooms;
        }
        /// <summary>
        /// 通过客房ＩＤ得到客房信息
        /// </summary>
        /// <param name="roomId">客房ID</param>
        /// <returns></returns>
        public  Room GetRoomByRoomId(int roomId)
        {
            Room room = new Room();
            using (SqlConnection conn = new SqlConnection(connString))
            {
                SqlCommand objCommand = new SqlCommand(dboOwner + ".usp_SelectRoom", conn);
                objCommand.CommandType = CommandType.StoredProcedure;
                objCommand.Parameters.Add("@RoomID", SqlDbType.Int).Value = roomId;
                conn.Open();
                using (SqlDataReader objReader = objCommand.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    if  (objReader.Read())
                    {
                        room.RoomId = Convert.ToInt32(objReader["RoomID"]);
                        room.BedNumber = Convert.ToInt32(objReader["BedNumber"]);
                        room.Description = Convert.ToString(objReader["Description"]);
                        room.GuestNumber = Convert.ToInt32(objReader["GuestNumber"]);
                        room.Number = Convert.ToString(objReader["Number"]);
                        room.State = Convert.ToString(objReader["State"]);
                        room.TypeID = Convert.ToInt32(objReader["TypeID"]);
                    }
                }
                conn.Close();
                conn.Dispose();
            }
            return room;
        }
        /// <summary>
        /// 修改客房信息
        /// </summary>
        /// <param name="room">客房实体对象</param>
        public void ModifyRoom(Room room)
        {
            using (SqlConnection conn = new SqlConnection(connString))
            {
                SqlCommand objCommand = new SqlCommand(dboOwner + ".usp_UpdateRoom", conn);
                objCommand.CommandType = CommandType.StoredProcedure;

                objCommand.Parameters.Add("@RoomId", SqlDbType.Int).Value = room.RoomId;
                objCommand.Parameters.Add("@BedNumber", SqlDbType.NVarChar, 50).Value = room.BedNumber;
                objCommand.Parameters.Add("@Description", SqlDbType.NVarChar, 50).Value = room.Description;
                objCommand.Parameters.Add("@GuestNumber", SqlDbType.Int).Value = room.GuestNumber;
                objCommand.Parameters.Add("@Number", SqlDbType.NVarChar, 255).Value = room.Number;
                objCommand.Parameters.Add("@State", SqlDbType.NVarChar, 50).Value = room.State;
                objCommand.Parameters.Add("@TypeID", SqlDbType.Int).Value = room.TypeID;
                conn.Open();
                objCommand.ExecuteNonQuery();
                conn.Close();
            }
        }
        /// <summary>
        /// 删除客房信息
        /// </summary>
        /// <param name="roomId">客房ID</param>
        public  void DeleteRoom(int roomId)
        {
            using (SqlConnection conn = new SqlConnection(connString))
            {
                SqlCommand objCommand = new SqlCommand(dboOwner + ".usp_DeleteRoom", conn);
                objCommand.CommandType = CommandType.StoredProcedure;

                objCommand.Parameters.Add("@RoomId", SqlDbType.Int).Value = roomId;
                
                conn.Open();
                objCommand.ExecuteNonQuery();
                conn.Close();
            }
        }
        /// <summary>
        /// 根据房间号得到客房信息列表
        /// </summary>
        /// <param name="roomNumber">房间号</param>
        /// <returns></returns>
        public  IList<Room> GetRoomListByRoomNumber(string roomNumber)
        {
            IList<Room> rooms = new List<Room>();
            using (SqlConnection conn = new SqlConnection(connString))
            {
                SqlCommand objCommand = new SqlCommand(dboOwner + ".usp_SelectRoomsByRoomNumber", conn);
                objCommand.CommandType = CommandType.StoredProcedure;
                objCommand.Parameters.Add("@Number", SqlDbType.Int).Value = roomNumber;
                conn.Open();
                using (SqlDataReader objReader = objCommand.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (objReader.Read())
                    {
                        Room room = new Room();
                        room.RoomId = Convert.ToInt32(objReader["RoomID"]);
                        room.BedNumber = Convert.ToInt32(objReader["BedNumber"]);
                        room.Description = Convert.ToString(objReader["Description"]);
                        room.GuestNumber = Convert.ToInt32(objReader["GuestNumber"]);
                        room.Number = Convert.ToString(objReader["Number"]);
                        room.State = Convert.ToString(objReader["State"]);
                        rooms.Add(room);
                    }
                }
                conn.Close();
                conn.Dispose();
            }
            return rooms;
        }
        #endregion
    }
}