roomtypeservice.cs

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

using System;
using System.Collections.Generic;
using System.Collections;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using HotelManager.Models;
/***********************************
 * 类名: RoomTypeService
 * 创建日期: 2007-9-15
 * 功能描述:提供客房分类信息数据访问
 * ********************************/
namespace HotelManager.DAL
{
   public class RoomTypeService
    {
        #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="typeName">类型名称</param>
       /// <returns></returns>
       public  int GetRoomTypeIDByTypeName(string typeName)
        {
            int roomTypeID = 0;
            using (SqlConnection conn = new SqlConnection(connString))
            {
                SqlCommand objCommand = new SqlCommand(dboOwner + ".usp_SelectRoomTypeIDByTypeName", conn);
                objCommand.CommandType = CommandType.StoredProcedure;
                objCommand.Parameters.Add("@TypeName", SqlDbType.NVarChar, 255).Value = typeName;
                conn.Open();
                using (SqlDataReader objReader = objCommand.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    if (objReader.Read())
                    {
                        roomTypeID = Convert.ToInt32(objReader["TypeID"]);
                    }
                    objReader.Close();
                    objReader.Dispose();
                }
                conn.Close();
                conn.Dispose();
            }
            return roomTypeID;
        }
       /// <summary>
       /// 得到客房类型名称
       /// </summary>
       /// <returns></returns>
       public  IList  GetRoomTypeName()
       {
           IList roomTypeList = new List<object>();
           using (SqlConnection conn = new SqlConnection(connString))
           {
               SqlCommand objCommand = new SqlCommand(dboOwner + ".usp_SelectTypeId", conn);
               objCommand.CommandType = CommandType.StoredProcedure;
               conn.Open();
               using (SqlDataReader objReader = objCommand.ExecuteReader(CommandBehavior.CloseConnection))
               {
                   while (objReader.Read())
                   {
                       roomTypeList.Add(objReader["TypeName"]);
                   }
                   objReader.Close();
                   objReader.Dispose();
               }
               conn.Close();
               conn.Dispose();
           }
           return roomTypeList;
       }
       /// <summary>
       /// 通过客房类型名称得到客房价格
       /// </summary>
       /// <param name="roomTypeName">类型名称</param>
       /// <returns></returns>
       public  string GetTypePriceByTypeName(string roomTypeName)
       {
           string roomPrice = string.Empty;
           using (SqlConnection conn = new SqlConnection(connString))
           {
               SqlCommand objCommand = new SqlCommand(dboOwner + ".usp_SelectTypePriceByTypeName", conn);
               objCommand.CommandType = CommandType.StoredProcedure;
               objCommand.Parameters.Add("@TypeName", SqlDbType.NVarChar,50).Value = roomTypeName;
               conn.Open();
               using (SqlDataReader objReader = objCommand.ExecuteReader(CommandBehavior.CloseConnection))
               {
                   if (objReader.Read())
                   {
                     roomPrice =Convert.ToString(objReader["TypePrice"]);
                   }
                   objReader.Close();
                   objReader.Dispose();
               }
               conn.Close();
               conn.Dispose();
           }
           return roomPrice;
       }
       /// <summary>
       /// 通过客房类型ＩＤ得到客房类型信息
       /// </summary>
       /// <param name="typeID">类型ID</param>
       /// <returns></returns>
       public  RoomType GetRoomTypeByTypeID(int typeID)
       {
           RoomType roomType = new RoomType();
           using (SqlConnection conn = new SqlConnection(connString))
           {
               SqlCommand objCommand = new SqlCommand(dboOwner + ".usp_SelectRoomType", conn);
               objCommand.CommandType = CommandType.StoredProcedure;
               objCommand.Parameters.Add("@TypeId", SqlDbType.Int).Value = typeID;
               conn.Open();
               using (SqlDataReader objReader = objCommand.ExecuteReader(CommandBehavior.CloseConnection))
               {
                   if (objReader.Read())
                   {
                       roomType.TypeName = Convert.ToString(objReader["TypeName"]);
                       roomType.TypePrice = Convert.ToDecimal(objReader["TypePrice"]);
                       roomType.IsAddBed = Convert.ToString(objReader["IsAddBed"]);
                       roomType.AddBedPrice = Convert.ToDecimal(objReader["AddBedPrice"]);
                       roomType.Remark = Convert.ToString(objReader["Remark"]);
                   }
                   objReader.Close();
                   objReader.Dispose();
               }
               conn.Close();
               conn.Dispose();
           }
           return roomType;
       }
      
       /// <summary>
       /// 得到客房类型列表
       /// </summary>
       /// <returns></returns>
       public IList<RoomType> GetRoomTypeList()
       {
           IList<RoomType> roomTypes = new List<RoomType>();
           using (SqlConnection conn = new SqlConnection(connString))
           {
               SqlCommand objCommand = new SqlCommand(dboOwner + ".usp_SelectRoomTypesAll", conn);
               objCommand.CommandType = CommandType.StoredProcedure;
               conn.Open();
               using (SqlDataReader objReader = objCommand.ExecuteReader(CommandBehavior.CloseConnection))
               {
                   while (objReader.Read())
                   {
                       RoomType roomType = new RoomType();
                       roomType.TypeId = Convert.ToInt32(objReader["TypeID"]);
                       roomType.TypeName = Convert.ToString(objReader["TypeName"]);
                       roomType.TypePrice = Convert.ToDecimal(objReader["TypePrice"]);
                       roomType.IsAddBed = Convert.ToString(objReader["IsAddBed"]);
                       roomType.AddBedPrice = Convert.ToDecimal(objReader["AddBedPrice"]);
                       if (objReader["Remark"] != null)
                           roomType.Remark = Convert.ToString(objReader["Remark"]);
                       roomTypes.Add(roomType);
                   }
                   objReader.Close();
                   objReader.Dispose();
               }
               conn.Close();
               conn.Dispose();
           }
           return roomTypes;
       }
       /// <summary>
       /// 通过客房类型名称得到客房类型列表
       /// </summary>
       /// <returns></returns>
       public IList<RoomType> GetRoomTypeListByTypeName(string typeName)
       {
           IList<RoomType> roomTypes = new List<RoomType>();
           using (SqlConnection conn = new SqlConnection(connString))
           {
               SqlCommand objCommand = new SqlCommand(dboOwner + ".usp_SelectRoomTypeListByTypeName", conn);
               objCommand.CommandType = CommandType.StoredProcedure;
               objCommand.Parameters.Add("@TypeName", SqlDbType.NVarChar, 50).Value = typeName;
               conn.Open();
               using (SqlDataReader objReader = objCommand.ExecuteReader(CommandBehavior.CloseConnection))
               {
                   while (objReader.Read())
                   {
                       RoomType roomType = new RoomType();
                       roomType.TypeId = Convert.ToInt32(objReader["TypeID"]);
                       roomType.TypeName = Convert.ToString(objReader["TypeName"]);
                       roomType.TypePrice = Convert.ToDecimal(objReader["TypePrice"]);
                       roomType.IsAddBed = Convert.ToString(objReader["IsAddBed"]);
                       roomType.AddBedPrice = Convert.ToDecimal(objReader["AddBedPrice"]);
                       if (objReader["Remark"] != null)
                           roomType.Remark = Convert.ToString(objReader["Remark"]);
                       roomTypes.Add(roomType);
                   }
                   objReader.Close();
                   objReader.Dispose();
               }
               conn.Close();
               conn.Dispose();
           }
           return roomTypes;
       }
       /// <summary>
       /// 新增客房类型信息
       /// </summary>
       /// <param name="roomType">类型实体对象</param>
       /// <returns></returns>
       public int AddRoomType(RoomType roomType)
       {

           int roomTypeID;
           using (SqlConnection conn = new SqlConnection(connString))
           {
               SqlCommand objCommand = new SqlCommand(dboOwner + ".usp_InsertRoomType", conn);
               objCommand.CommandType = CommandType.StoredProcedure;

               objCommand.Parameters.Add("@TypeName", SqlDbType.NVarChar, 50).Value = roomType.TypeName;
               objCommand.Parameters.Add("@TypePrice", SqlDbType.Money).Value = roomType.TypePrice;
               objCommand.Parameters.Add("@IsAddBed", SqlDbType.NChar, 10).Value = roomType.IsAddBed;
               objCommand.Parameters.Add("@AddBedprice", SqlDbType.Money).Value = roomType.AddBedPrice;
               objCommand.Parameters.Add("@Remark", SqlDbType.NVarChar, 255).Value = roomType.Remark;
               conn.Open();
               roomTypeID = Convert.ToInt32(objCommand.ExecuteScalar());
               conn.Close();
           }
           return roomTypeID;
       }
       /// <summary>
       /// 修改客房类型信息
       /// </summary>
       /// <param name="roomType">类型实体对象</param>
       public void ModifyRoomType(RoomType roomType)
       {
           using (SqlConnection conn = new SqlConnection(connString))
           {
               SqlCommand objCommand = new SqlCommand(dboOwner + ".usp_UpdateRoomType", conn);
               objCommand.CommandType = CommandType.StoredProcedure;

               objCommand.Parameters.Add("@TypeID", SqlDbType.Int).Value = roomType.TypeId;
               objCommand.Parameters.Add("@TypeName", SqlDbType.NVarChar, 50).Value = roomType.TypeName;
               objCommand.Parameters.Add("@TypePrice", SqlDbType.Money).Value = roomType.TypePrice;
               objCommand.Parameters.Add("@IsAddBed", SqlDbType.NChar, 10).Value = roomType.IsAddBed;
               objCommand.Parameters.Add("@AddBedprice", SqlDbType.Money).Value = roomType.AddBedPrice;
               objCommand.Parameters.Add("@Remark", SqlDbType.NVarChar, 255).Value = roomType.Remark;
               conn.Open();
               objCommand.ExecuteNonQuery();
               conn.Close();
           }
       }
       /// <summary>
       /// 删除客房类型信息
       /// </summary>
       /// <param name="typeID">类型ID</param>
       public void DeleteRoomType(int typeID)
       {
           using (SqlConnection conn = new SqlConnection(connString))
           {
               SqlCommand objCommand = new SqlCommand(dboOwner + ".usp_DeleteRoomType", conn);
               objCommand.CommandType = CommandType.StoredProcedure;

               objCommand.Parameters.Add("@TypeID", SqlDbType.Int).Value = typeID;

               conn.Open();
               objCommand.ExecuteNonQuery();
               conn.Close();
           }
       }
       #endregion
    }
}