1using System;
  2using System.Collections.Generic;
  3using System.Linq;
  4using System.Text;
  5using System.Data;
  6using Entity;
  7
  8namespace DAL
  9...{
 10    public class HotelUserDAL
 11    ...{
 12        /**//// <summary>
 13        /// 查询HotelUser表
 14        /// </summary>
 15        /// <returns></returns>
 16        public static List<HotelUserEntity> GetHotelUser()
 17        ...{
 18            //创建HotelUserEntity的泛型对象HUE
 19            List<HotelUserEntity> HUE = new List<HotelUserEntity>();
 20
 21            //创建以个表格对象dt，用来保存SQLHelp.FillTable(sql语句)所返回的一张表
 22            DataTable dt = SQLHelp.FillTable("select * from HotelUser");
 23
 24            //如果返回的表格dt不为空并且有数据，则执行一下代码
 25            if (dt != null && dt.Rows.Count > 0)
 26            ...{
 27                //用foreach循环将dt里的数据取出来放到HotelUserEntity对象里
 28                foreach (DataRow dr in dt.Rows)
 29                ...{
 30                    //创建HotelUserEntity对象HUE
 31                    HotelUserEntity h = new HotelUserEntity();
 32
 33                    //将表格的数据封装到HUE里
 34                    h.Password = dr["Password"].ToString();
 35                    h.UserID = (int)dr["UserID"];
 36                    h.UserName = dr["UserName"].ToString();
 37                    h.Address = dr["Address"].ToString();
 38                    h.Email = dr["Email"].ToString();
 39                    h.Name = dr["Name"].ToString();
 40                    h.Status = Convert.ToInt32(dr["Status"]);
 41                    h.Phone = dr["Phone"].ToString();
 42
 43                    //循环一次将r放入泛型对象HUE里
 44                    HUE.Add(h);
 45                }
 46            }
 47            //返回泛型对象HUE
 48            return HUE;
 49        }
 50
 51        public static DataTable GetHotelUserByUserID(int UserID)
 52        ...{
 53            string sql = "select * from HotelUser where UserID = "+UserID;
 54            return SQLHelp.FillTable(sql);
 55        }
 56
 57        public static DataTable GetHotelUserByUserName(string UserName)
 58        ...{
 59            string sql = "select * from HotelUser where UserName = '"+UserName+"'";
 60            return SQLHelp.FillTable(sql);
 61        }
 62
 63        public static DataTable GetHotelUserByName(string Name)
 64        ...{ 
 65            string sql = "select * from HotelUser where Name like '%"+Name+"%'";
 66            return SQLHelp.FillTable(sql);
 67        }
 68
 69        public static int GetUserIDByUserName(string UserName)
 70        ...{
 71            string sql = "select userid from hoteluser where username = '"+UserName+"'";
 72            return Convert.ToInt32(SQLHelp.FillTable(sql).Rows[0][0]);
 73        }
 74
 75        /**//// <summary>
 76        /// 登录验证
 77        /// </summary>
 78        /// <param name="HUE"></param>
 79        /// <returns></returns>
 80        public static DataTable ExistUser(HotelUserEntity HUE)
 81        ...{
 82            string sql = "select count(*) from HotelUser where UserName = '"+HUE.UserName+"'and Password ='"+HUE.Password+"'";
 83            return SQLHelp.FillTable(sql);
 84        }
 85
 86        public static HotelUserEntity GetUserInfoByUserName(string UserName)
 87        ...{
 88            HotelUserEntity HUE = new HotelUserEntity();
 89            string sql = "select Name, Status from HotelUser where UserName = '"+UserName+"'";
 90            DataTable dt = SQLHelp.FillTable(sql);
 91            HUE.Name = dt.Rows[0]["Name"].ToString();
 92            HUE.Status = Convert.ToInt32(dt.Rows[0]["Status"]);
 93            return HUE;
 94        }
 95        public static int GetCountUserName(string UserName)
 96        ...{
 97            string sql = "select count(*) from HotelUser where UserName = '"+UserName+"'";
 98            return Convert.ToInt32(SQLHelp.FillTable(sql).Rows[0][0]);
 99        }
100
101        /**//// <summary>
102        /// 增加HotelUser表
103        /// </summary>
104        /// <param name="HUE"></param>
105        /// <returns></returns>
106        public static int AddHotelUser(HotelUserEntity HUE)
107        ...{
108            //从传进来的HotelUserEntity对象拼凑sql语句
109            string sql = "insert into HotelUser (UserName,Password,Name,Address,Phone,Email) values('" + HUE.UserName + "','" + HUE.Password + "','" + HUE.Name + "','" + HUE.Address + "','" + HUE.Phone + "','" + HUE.Email + "')";
110
111            //调用SQLHelp类的ExecQuery() 方法执行拼凑的sql，及返回相应的数据
112            return SQLHelp.ExecQuery(sql);
113        }
114
115        /**//// <summary>
116        /// 修改HotelUser表
117        /// </summary>
118        /// <param name="HUE"></param>
119        /// <returns></returns>
120        public static int UpdateHotelUser(HotelUserEntity HUE)
121        ...{
122            //从传进来的HotelUserEntity对象拼凑sql语句
123            string sql = "update HotelUser set UserName = '" + HUE.UserName + "',Password = '" + HUE.Password + "' where UserID = " + HUE.UserID;
124
125            //调用SQLHelp类的ExecQuery() 方法执行拼凑的sql，及返回相应的数据
126            return SQLHelp.ExecQuery(sql);
127        }
128
129        public static int UpdateStatusByUserName(int Status,string UserName,string SessionName)
130        ...{
131            string sql = "update HotelUser set Status = "+Status+" where UserName = '"+UserName+"' and UserName <> '"+SessionName+"'";
132            return SQLHelp.UnusualExecQuery(sql);
133        }
134
135
136        /**//// <summary>
137        /// 密码修改
138        /// </summary>
139        /// <param name="HUE"></param>
140        /// <returns></returns>
141        public static int ChangePwd(HotelUserEntity HUE,string pwd)
142        ...{
143            //从传进来的HotelUserEntity对象拼凑sql语句
144            string sql = "update HotelUser set Password = '" + HUE.Password + "' where UserName = '" + HUE.UserName + "' and password = '"+pwd+"'";
145
146            //调用SQLHelp类的ExecQuery() 方法执行拼凑的sql，及返回相应的数据
147            return SQLHelp.UnusualExecQuery(sql);
148        }
149
150        /**//// <summary>
151        /// 删除HotelUser表
152        /// </summary>
153        /// <param name="HUE"></param>
154        /// <returns></returns>
155        public static int DeleteHotelUser(HotelUserEntity HUE)
156        ...{
157            //从传进来的HotelUserEntity对象拼凑sql语句
158            string sql = "delete HotelUser where UserID = "+HUE.UserID;
159
160            //调用SQLHelp类的ExecQuery() 方法执行拼凑的sql，及返回相应的数据
161            return SQLHelp.ExecQuery(sql);
162        }
163
164        public static int DeleteHotelUserByUserID(int UserID)
165        ...{
166            string sql = "EXEC DEL_HOTELUSER "+UserID;
167            return SQLHelp.UnusualExecQuery(sql);
168        }
169    }
170}
171