1using System;
  2using System.Collections.Generic;
  3using System.Linq;
  4using System.Text;
  5using System.Data;
  6using Entity;
  7
  8namespace DAL
  9...{
 10    public class RoomTypeDAL
 11    ...{
 12        /**//// <summary>
 13        /// 查询RoomType表
 14        /// </summary>
 15        /// <returns></returns>
 16        public static List<RoomTypeEntity> GetRoomType()
 17        ...{
 18            //创建RoomTypeEntity的泛型对象RTE
 19            List<RoomTypeEntity> RTE = new List<RoomTypeEntity>();
 20
 21            //创建以个表格对象dt，用来保存SQLHelp.FillTable(sql语句)所返回的一张表
 22            DataTable dt = SQLHelp.FillTable("select * from RoomType");
 23
 24            //如果返回的表格dt不为空并且有数据，则执行一下代码
 25            if (dt != null && dt.Rows.Count > 0)
 26            ...{
 27                //用foreach循环将dt里的数据取出来放到RoomTypeEntity对象里
 28                foreach (DataRow dr in dt.Rows)
 29                ...{
 30                    //创建RoomTypeEntity对象r
 31                    RoomTypeEntity r = new RoomTypeEntity();
 32
 33                    //将表格的数据封装到r里
 34                    r.AddBedPrice = Convert.ToDouble(dr["AddBedPrice"]);
 35                    r.IsAddBed = dr["IsAddBed"].ToString();
 36                    r.Remark = dr["Remark"].ToString();
 37                    r.TypeID = (int)dr["TypeID"];
 38                    r.TypeName = dr["TypeName"].ToString();
 39                    r.TypePrice = Convert.ToDouble(dr["TypePrice"]);
 40
 41                    //循环一次将r放入泛型对象RTE里
 42                    RTE.Add(r);
 43                }
 44            }
 45            //返回泛型对象RTE
 46            return RTE;
 47        }
 48
 49        public static RoomTypeEntity GetRoomTypeByTypeID(int TypeID)
 50        ...{
 51            RoomTypeEntity RTE = new RoomTypeEntity();
 52            string sql = "select * from RoomType where TypeID = "+TypeID;
 53            DataTable dt = SQLHelp.FillTable(sql);
 54            RTE.AddBedPrice = Convert.ToDouble(dt.Rows[0]["AddBedPrice"]);
 55            RTE.IsAddBed = dt.Rows[0]["IsAddBed"].ToString();
 56            RTE.Remark = dt.Rows[0]["Remark"].ToString();
 57            RTE.TypeName = dt.Rows[0]["TypeName"].ToString();
 58            RTE.TypePrice = Convert.ToDouble(dt.Rows[0]["TypePrice"]);
 59            RTE.ImageURL = dt.Rows[0]["ImageURL"].ToString();
 60            return RTE;
 61
 62        }
 63
 64        public static int GetTypeIDByTypeName(string TypeName)
 65        ...{
 66            string sql = "select TypeID from RoomType where TypeName = '"+TypeName+"'";
 67            DataTable dt = SQLHelp.FillTable(sql);
 68            return Convert.ToInt32(dt.Rows[0][0]);
 69        }
 70
 71        public static int GetCountTypeNameByTypeName(string TypeName)
 72        ...{
 73            string sql = "select count(*) from roomtype where TypeName = '" + TypeName + "'";
 74            return Convert.ToInt32(SQLHelp.FillTable(sql).Rows[0][0]);
 75        }
 76
 77
 78        public static DataTable GetRoomTypeByFilter(string TypeName)
 79        ...{
 80            string sql = "select * from roomtype where typename like '%"+TypeName+"%'";
 81            return SQLHelp.FillTable(sql);
 82        }
 83        public static DataTable GetRoomTypeByFilter()
 84        ...{
 85            string sql = "select * from roomtype";
 86            return SQLHelp.FillTable(sql);
 87        }
 88
 89        public static DataTable GetImageURL()
 90        ...{
 91            string sql = "select * from roomtype";
 92            return SQLHelp.FillTable(sql);
 93        }
 94
 95        public static DataTable GetImageURL(int TypeID)
 96        ...{
 97            string sql = "select * from roomtype where TypeID = "+TypeID;
 98            return SQLHelp.FillTable(sql);
 99        }
100
101        
102
103        /**//// <summary>
104        /// 增加RoomType表
105        /// </summary>
106        /// <param name="RTE"></param>
107        /// <returns></returns>
108        public static int AddRoomType(RoomTypeEntity RTE)
109        ...{
110            //从传进来的RoomTypeEntiy对象拼凑sql语句
111            string sql = "insert into RoomType values('"+RTE.TypeName+"',"+RTE.TypePrice+",'"+RTE.IsAddBed+"',"+RTE.AddBedPrice+",'"+RTE.Remark+"','"+RTE.ImageURL+"')";
112
113            //调用SQLHelp类的ExecQuery() 方法执行拼凑的sql，及返回相应的数据
114            return SQLHelp.ExecQuery(sql);
115        }
116
117        /**//// <summary>
118        /// 修改RoomType表
119        /// </summary>
120        /// <param name="RTE"></param>
121        /// <returns></returns>
122        public static int UpdateRoomType(RoomTypeEntity RTE)
123        ...{
124            //从传进来的RoomTypeEntiy对象拼凑sql语句
125            string sql = "update RoomType set TypeName = '"+RTE.TypeName+"',TypePrice = "+RTE.TypePrice+",IsAddBed = '"+RTE.IsAddBed+"',AddBedPrice = "+RTE.AddBedPrice+",Remark = '"+RTE.Remark+"' where TypeID = "+RTE.TypeID;
126
127            //调用SQLHelp类的ExecQuery() 方法执行拼凑的sql，及返回相应的数据
128            return SQLHelp.ExecQuery(sql);
129        }
130
131        public static int UpdateRoomTypeByTypeName(RoomTypeEntity RTE)
132        ...{
133
134            string sql = "update RoomType set TypePrice = "+RTE.TypePrice+",IsAddBed = '"+RTE.IsAddBed+"',AddBedPrice = "+RTE.AddBedPrice+",Remark = '"+RTE.Remark+"',ImageURL = '"+RTE.ImageURL+"' where TypeName = '"+RTE.TypeName+"'";
135            return SQLHelp.ExecQuery(sql);
136        }
137
138        /**//// <summary>
139        /// 删除RoomType表
140        /// </summary>
141        /// <param name="RTE"></param>
142        /// <returns></returns>
143        public static int DeleteRoomType(RoomTypeEntity RTE)
144        ...{
145            //从传进来的RoomTypeEntiy对象拼凑sql语句
146            string sql = "delete RoomType where TypeID = "+RTE.TypeID;
147
148            //调用SQLHelp类的ExecQuery() 方法执行拼凑的sql，及返回相应的数据
149            return SQLHelp.ExecQuery(sql);
150        }
151
152        public static DataTable GetRoomTypeByRoom_TypeID(int TypeID)
153        ...{
154            string sql = "select count(*) from  Room where TypeId=(select typeid from roomtype where typeid = "+TypeID+")";
155            return SQLHelp.FillTable(sql);
156        }
157    }
158}
159