1using System;
  2using System.Collections.Generic;
  3using System.Linq;
  4using System.Text;
  5using Entity;
  6using System.Data;
  7
  8
  9namespace DAL
 10...{
 11    public class RoomDAL
 12    ...{
 13        /**//// <summary>
 14        /// 查询Room表
 15        /// </summary>
 16        /// <returns></returns>
 17        public static List<RoomEntity> GetRoom()
 18        ...{
 19            //创建RoomEntity的泛型对象RE
 20            List<RoomEntity> RE = new List<RoomEntity>();
 21
 22            //创建以个表格对象dt，用来保存SQLHelp.FillTable(sql语句)所返回的一张表
 23            DataTable dt = SQLHelp.FillTable("select * from room");
 24
 25            //如果返回的表格dt不为空并且有数据，则执行一下代码
 26            if ((dt != null) && (dt.Rows.Count > 0))
 27            ...{
 28                //用foreach循环将dt里的数据取出来放到RoomEntity对象里
 29                foreach (DataRow dr in dt.Rows)
 30                ...{
 31                    //创建RoomEntity对象R
 32                    RoomEntity R = new RoomEntity();
 33
 34                    //将表格的数据封装到R里
 35                    R.BedNumber = (int)dr["BedNumber"];
 36                    R.Description = dr["Description"].ToString();
 37                    R.GuestNumber = (int)dr["GuestNumber"];
 38                    R.Number = dr["Number"].ToString();
 39                    R.RoomID = (int)dr["RoomID"];
 40                    R.State = dr["State"].ToString();
 41                    R.TypeID = (int)dr["Type"];
 42
 43                    //循环一次将R放入泛型对象RE里
 44                    RE.Add(R);
 45                }
 46            }
 47            //返回泛型对象RE
 48            return RE;
 49        }
 50
 51        public static RoomEntity GetRoomByRoomID(int RoomID)
 52        ...{
 53            RoomEntity RE = new RoomEntity();
 54            string sql = "SELECT dbo.Room.*, dbo.RoomType.TypeName FROM dbo.Room INNER JOIN dbo.RoomType ON dbo.Room.TypeID = dbo.RoomType.TypeID and RoomID = "+RoomID;
 55
 56            DataTable dt = SQLHelp.FillTable(sql);
 57            RE.Number = dt.Rows[0]["Number"].ToString();
 58            RE.State = dt.Rows[0]["State"].ToString();
 59            RE.GuestNumber = (int)dt.Rows[0]["GuestNumber"];
 60            RE.TypeID = (int)dt.Rows[0]["TypeID"];
 61            RE.Description = dt.Rows[0]["Description"].ToString();
 62            RE.BedNumber = (int)dt.Rows[0]["BedNumber"];
 63            RE.TypeName = dt.Rows[0]["TypeName"].ToString();
 64            return RE;
 65
 66        }
 67
 68        public static DataTable GetStateByRoomID(int RoomID)
 69        ...{
 70            string sql = "select state from Room where RoomID = "+RoomID;
 71            return SQLHelp.FillTable(sql);
 72
 73        }
 74
 75        public static DataTable GetRoom_RoomTypeInfo(string TypeName,int BedNumber,int GuestNumber)
 76        ...{
 77          //  string sql = "EXEC SEL_ROOM @TypeName,@BedNumber,@GuestNumber";
 78
 79            string sql = "EXEC SEL_ROOM '"+TypeName+"',"+BedNumber+","+GuestNumber+"";
 80            return SQLHelp.FillTable(sql);
 81        }
 82
 83        public static DataTable GetRoom_RoomTypeInfo()
 84        ...{
 85            //  string sql = "EXEC SEL_ROOM @TypeName,@BedNumber,@GuestNumber";
 86
 87            string sql = "EXEC SEL_ROOM1";
 88            return SQLHelp.FillTable(sql);
 89        }
 90
 91
 92        public static DataTable GetRoom_RoomTypeInfoByRoomID(int RoomID)
 93        ...{
 94            string sql = "select * from view_room_roomtype where roomid="+RoomID;
 95            return SQLHelp.FillTable(sql);
 96        }
 97
 98
 99        public static DataTable GetStateByNumber(string Number)
100        ...{
101            string sql = "select state from room where Number = '" + Number + "'";
102            return SQLHelp.FillTable(sql);
103        }
104
105        public static DataTable GetUserByFilter(string TypeName, string Number)
106        ...{
107            string sql = "";
108            if (Number != "")
109            ...{
110                sql = "select * from View_RoomInfoByFilter where TypeName = '"+TypeName+"' and Number = '"+Number+"'";
111            }
112            else
113            ...{
114                sql = "select * from View_RoomInfoByFilter where TypeName = '"+TypeName+"'";
115            }
116            return SQLHelp.FillTable(sql);
117        }
118
119
120        public static DataTable GetUserByFilter()
121        ...{
122            string sql = "select * from View_RoomInfoByFilter";
123            return SQLHelp.FillTable(sql);
124        }
125
126        public static int GetRoomIDByNumber(string Number)
127        ...{ 
128            string sql = "select RoomID from room where number = '"+Number+"'";
129            return Convert.ToInt32(SQLHelp.FillTable(sql).Rows[0][0]);
130        }
131
132        public static bool SelectRoom(string Number)
133        ...{
134            string sql = "select * from Room where Number =  '" + Number + "'";
135            DataTable dd = new DataTable();
136            dd = SQLHelp.FillTable(sql);
137            if (dd.Rows.Count > 0)
138            ...{
139                return false;
140            }
141            else
142            ...{
143                return true;
144            }
145        }
146
147        /**//// <summary>
148        /// 增加Room表
149        /// </summary>
150        /// <param name="RE"></param>
151        /// <returns></returns>
152        public static int AddRoom(RoomEntity RE)
153        ...{
154
155            //从传进来的RoomEntity对象拼凑sql语句
156            string sql = "insert into Room values('"+RE.Number+"',"+RE.BedNumber+",'"+RE.Description+"','"+RE.State+"',"+RE.GuestNumber+","+RE.TypeID+")";
157
158            //调用SQLHelp类的ExecQuery() 方法执行拼凑的sql，及返回相应的数据
159            return SQLHelp.ExecQuery(sql);
160        }
161        /**//// <summary>
162        /// 修改Room表
163        /// </summary>
164        /// <param name="RE"></param>
165        /// <returns></returns>
166        public static int UpdateRoom(RoomEntity RE)
167        ...{
168            //从传进来的RoomEntity对象拼凑sql语句
169            string sql = "update Room set Number = '"+RE.Number+"',BedNumber = "+RE.BedNumber+" ,Description = '"+RE.Description+"',State = '"+RE.State+"',GuestNumber = "+RE.GuestNumber+" ,TypeID = "+RE.TypeID+" where RoomID = "+RE.RoomID;
170
171            //调用SQLHelp类的ExecQuery() 方法执行拼凑的sql，及返回相应的数据
172            return SQLHelp.ExecQuery(sql);
173        }
174
175        public static int UpdateRoomByNumber(RoomEntity RE)
176        ...{
177            string sql = "update Room set BedNumber = " + RE.BedNumber + " ,Description = '" + RE.Description + "',State = '" + RE.State + "',GuestNumber = " + RE.GuestNumber + " ,TypeID = " + RE.TypeID + " where RoomID = '" + RE.RoomID + "'";
178            return SQLHelp.ExecQuery(sql);
179        }
180
181        public static int UpdateStateByRoomID(int RoomID)
182        ...{
183            string sql = "update Room set State = '已预订' where RoomID = "+RoomID;
184            return SQLHelp.ExecQuery(sql);
185        }
186
187        public static int UpdateStateByNumber(string Number)
188        ...{
189            string sql = "update Room set State = '空房' where Number = '"+Number+"'";
190            return SQLHelp.ExecQuery(sql);
191        }
192
193
194
195
196        /**//// <summary>
197        /// 删除Room表
198        /// </summary>
199        /// <param name="RE"></param>
200        /// <returns></returns>
201        public static int DeleteRoom(RoomEntity RE)
202        ...{
203            //从传进来的RoomEntity对象拼凑sql语句
204            string sql = "delete from Room where RoomID = "+RE.RoomID;
205
206            //调用SQLHelp类的ExecQuery() 方法执行拼凑的sql，及返回相应的数据
207            return SQLHelp.ExecQuery(sql);
208        }
209    }
210}
211