roomtypeoperatorservice.cs

酒店管理 主要实现了基础设施管理（客房管理、客房类型管理）、业务管理（入住、退房、数据库切换） 本系统简单明了,适合初学者学习,采用三层加抽象工厂实现

using System;
using System.Collections.Generic;
using System.Text;

using IDAL;
using DBUtility;
using System.Data.SqlClient;
using System.Data;
using Model;

namespace SQLServerDAL
{
    public class RoomTypeOperatorService : IRoomType
    {
        #region IRoomType 成员
        private SqlConnection _con = null;
        private SqlDataAdapter _adapter = null;
        private SqlCommand _cmd = null;
        //初始化参数
        private const string INSERT_INTO_ROOMTYPE = "insert into RoomType(TypeName,TypePrice,IsAddBed,AddBedPrice,Remark) values(";
        private const string PARM_TYPE_ID = "@TypeId";
        private const string PARM_TYPE_NAME = "@typeName";
        private const string PARM_TYPE_PRICE = "@typePrice";
        private const string PARM_IS_ADD_BED = "@isAddBed";
        private const string PARM_ADD_BED_PRICE = "@addBedPrice";
        private const string PARM_REMARK = "@remark";
        /// <summary>
        /// 查询所有的房间类型
        /// </summary>
        /// <returns></returns>
        public IList<Model.RoomType> SelectAllDataByRoomType()
        {
            this._con = new SqlConnection(SQLHelper.SQLConnString);
            string sql = "select TypeId,TypeName,TypePrice,IsAddBed,AddBedPrice,SubString(Remark,0,40) as ShortRemark from RoomType ";
            IList<RoomType> list = new List<RoomType>();
            if (this._con != null)
            {
                try
                {
                    this._con.Open();
                    _adapter = new SqlDataAdapter(sql, _con);
                    DataSet ds = new DataSet();
                    _adapter.Fill(ds, "RoomType");
                    for (int i = 0; i < ds.Tables["RoomType"].Rows.Count; i++)
                    {
                        RoomType temp = new RoomType();
                        temp.Typeid = Convert.ToInt32(ds.Tables["RoomType"].Rows[i][0]);
                        temp.Typename = ds.Tables["RoomType"].Rows[i][1].ToString();
                        temp.Typeprice = Convert.ToDouble(ds.Tables["RoomType"].Rows[i][2]);
                        temp.Isaddbed = ds.Tables["RoomType"].Rows[i][3].ToString();
                        temp.Addbedprice = Convert.ToDouble(ds.Tables["RoomType"].Rows[i][4]);
                        temp.Remark = ds.Tables["RoomType"].Rows[i][5].ToString();
                        list.Add(temp);
                    }

                }
                catch (SqlException ex)
                {
                    list = null;
                    throw ex;
                }
                finally
                {
                    this._con.Close();
                }
            }
            return list;
        }


        public RoomType SelectRoomTypeByID(int id)
        {
            this._con = new SqlConnection(SQLHelper.SQLConnString);
            string sql = "select * from RoomType where TypeId=" + PARM_TYPE_ID;
            RoomType type = null;
            if (this._con != null)
            {
                try
                {
                    this._con.Open();
                    _adapter = new SqlDataAdapter(sql, _con);
                    _cmd = new SqlCommand(sql, _con);
                    _cmd.Parameters.Add(PARM_TYPE_ID, SqlDbType.Int).Value = id;
                    SqlDataReader dr = _cmd.ExecuteReader();
                    if (dr.Read())
                    {
                        type = new RoomType();
                        type.Typeid = dr.GetInt32(0);
                        type.Typename = dr.GetString(1);
                        type.Typeprice = Convert.ToDouble(dr.GetInt32(2));
                        type.Isaddbed = dr.GetString(3);
                        type.Addbedprice = Convert.ToDouble(dr.GetInt32(4));
                        type.Remark = dr.GetString(5);
                        dr.Close();
                    }
                }
                catch (SqlException ex)
                {
                    type = null;
                    throw ex;
                }
                finally
                {
                    this._con.Close();
                }
            }
            return type;
        }
        /// <summary>
        /// 按类型名称查询房间类型
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>

        public Model.RoomType SelectRoomTypeByTypeName(string name)
        {
            this._con = new SqlConnection(SQLHelper.SQLConnString);
            string sql = "select * from RoomType where TypeName=" + PARM_TYPE_NAME;
            RoomType type = null;
            if (this._con != null)
            {
                try
                {
                    this._con.Open();
                    _adapter = new SqlDataAdapter(sql, _con);
                    _cmd = new SqlCommand(sql, _con);
                    _cmd.Parameters.Add(PARM_TYPE_NAME, SqlDbType.VarChar).Value = name;
                    SqlDataReader dr = _cmd.ExecuteReader();
                    if (dr.Read())
                    {
                        type = new RoomType();
                        type.Typeid = dr.GetInt32(0);
                        type.Typename = dr.GetString(1);
                        type.Typeprice = Convert.ToDouble(dr.GetInt32(2));
                        type.Isaddbed = dr.GetString(3);
                        type.Addbedprice = Convert.ToDouble(dr.GetInt32(4));
                        type.Remark = dr.GetString(5);
                        dr.Close();
                    }
                }
                catch (SqlException ex)
                {
                    type = null;
                    throw ex;
                }
                finally
                {
                    this._con.Close();
                }
            }
            return type;
        }


        /// <summary>
        /// 按类型名称查询类型信息(模糊查询使用like)
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        IList<RoomType> IRoomType.GetRoomTypeByTypeName(string name)
        {
            this._con = new SqlConnection(SQLHelper.SQLConnString);
            string sql = "select * from RoomType where TypeName like " + name;
            IList<RoomType> roomType = null;
            if (this._con != null)
            {
                try
                {
                    this._con.Open();
                    _adapter = new SqlDataAdapter(sql, _con);
                    _cmd = new SqlCommand(sql, _con);
                    SqlDataReader dr = _cmd.ExecuteReader();
                    //是否包含多行
                    if (dr.HasRows == false)
                        roomType = null;
                    else
                    {
                        roomType = new List<RoomType>();
                        while (dr.Read())
                        {
                            RoomType type = new RoomType();
                            type.Typeid = dr.GetInt32(0);
                            type.Typename = dr.GetString(1);
                            type.Typeprice = Convert.ToDouble(dr.GetInt32(2));
                            type.Isaddbed = dr.GetString(3);
                            type.Addbedprice = Convert.ToDouble(dr.GetInt32(4));
                            type.Remark = dr.GetString(5);
                            roomType.Add(type);
                        }
                        dr.Close();
                    }
                }
                catch (SqlException ex)
                {
                    roomType = null;
                    throw ex;
                }
                finally
                {
                    this._con.Close();
                }
            }
            return roomType;
        }

        /// <summary>
        /// 添加房间类型
        /// </summary>
        /// <param name="roomtype"></param>
        /// <returns></returns>
        public bool InsertRoomTypeValues(Model.RoomType roomtype)
        {
            int count = 0;
            StringBuilder sqlRoom = new StringBuilder();//使用StringBuilder提高性能
            sqlRoom.Append(INSERT_INTO_ROOMTYPE).Append(PARM_TYPE_NAME).Append("," + PARM_TYPE_PRICE).Append("," + PARM_IS_ADD_BED).Append("," + PARM_ADD_BED_PRICE).Append("," + PARM_REMARK + ")");

            try
            {
                SqlParameter[] parms = this.CreateParameters();
                parms[0].Value = roomtype.Typename;
                parms[1].Value = roomtype.Typeprice;
                parms[2].Value = roomtype.Isaddbed;
                parms[3].Value = roomtype.Addbedprice;
                parms[4].Value = roomtype.Remark;
                parms[5].Value = roomtype.Typeid;
                count = SQLHelper.ExecuteNonQuery(SQLHelper.SQLConnString, sqlRoom.ToString(), CommandType.Text, parms);

            }
            catch (SqlException ex)
            {
                throw ex;
            }
            if (count > 0)
                return true;
            else
                return false;
        }

        /// <summary>
        /// 修改房间类型
        /// </summary>
        /// <param name="roomtype"></param>
        /// <returns></returns>
        public bool ModifyRoomTypeValues(Model.RoomType roomtype)
        {
            int count = 0;
            StringBuilder sql = new StringBuilder();//使用StringBuilder提高性能
            sql.Append("update RoomType set TypeName=").Append(PARM_TYPE_NAME).Append(",TypePrice=" + PARM_TYPE_PRICE);
            sql.Append(",IsAddBed=" + PARM_IS_ADD_BED).Append(",AddBedPrice=" + PARM_ADD_BED_PRICE).Append(",Remark=" + PARM_REMARK);
            sql.Append(" where TypeId=" + PARM_TYPE_ID);
            try
            {
                //声明参数集合
                SqlParameter[] parms = this.CreateParameters();

                //给指定的参数赋值
                parms[0].Value = roomtype.Typename;
                parms[1].Value = roomtype.Typeprice;
                parms[2].Value = roomtype.Isaddbed;
                parms[3].Value = roomtype.Addbedprice;
                parms[4].Value = roomtype.Remark;
                parms[5].Value = roomtype.Typeid;
                //调用SQLHelper类的方法返回执行的结果并判断是否修改成功
                count = SQLHelper.ExecuteNonQuery(SQLHelper.SQLConnString, sql.ToString(), CommandType.Text, parms);
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            if (count > 0)
                return true;
            else
                return false;
        }

        /// <summary>
        /// 删除房间类型
        /// </summary>
        /// <param name="roomtype"></param>
        /// <returns></returns>
        public bool DeleteRoomTypeValues(Model.RoomType roomtype)
        {
            int count = 0;
            string proc = "proc_delete";
            try
            {
                SqlParameter[] parms = new SqlParameter[1];
                parms[0] = new SqlParameter(PARM_TYPE_ID, SqlDbType.Int);
                parms[0].Value = roomtype.Typeid;
                count = SQLHelper.ExecuteNonQuery(SQLHelper.SQLConnString, proc, CommandType.StoredProcedure, parms);
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            if (count > 0)
                return true;
            else
                return false;
        }


        /// <summary>
        /// 创建参数数组
        /// </summary>
        /// <returns></returns>
        private SqlParameter[] CreateParameters()
        {
            SqlParameter[] parms = new SqlParameter[6];
            parms[0] = new SqlParameter(PARM_TYPE_NAME, SqlDbType.VarChar);
            parms[1] = new SqlParameter(PARM_TYPE_PRICE, SqlDbType.Int);
            parms[2] = new SqlParameter(PARM_IS_ADD_BED, SqlDbType.VarChar);
            parms[3] = new SqlParameter(PARM_ADD_BED_PRICE, SqlDbType.Int);
            parms[4] = new SqlParameter(PARM_REMARK, SqlDbType.VarChar);
            parms[5] = new SqlParameter(PARM_TYPE_ID, SqlDbType.Int);
            return parms;
        }

        #endregion
    }
}