hoteluserdal.cs

酒店管理系统项目需求说明 第一部分 引言 1 1.1编写目的 1 1.2背景 1 第二部分 任务概述 2 3.1实现目标 2 3.1.1客房类型模块： 2 3.1.2客房信息

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Entity;

namespace DAL
{
    public class HotelUserDAL
    {
        /// <summary>
        /// 查询HotelUser表
        /// </summary>
        /// <returns></returns>
        public static List<HotelUserEntity> GetHotelUser()
        {
            //创建HotelUserEntity的泛型对象HUE
            List<HotelUserEntity> HUE = new List<HotelUserEntity>();

            //创建以个表格对象dt，用来保存SQLHelp.FillTable(sql语句)所返回的一张表
            DataTable dt = SQLHelp.FillTable("select * from HotelUser");

            //如果返回的表格dt不为空并且有数据，则执行一下代码
            if (dt != null && dt.Rows.Count > 0)
            {
                //用foreach循环将dt里的数据取出来放到HotelUserEntity对象里
                foreach (DataRow dr in dt.Rows)
                {
                    //创建HotelUserEntity对象HUE
                    HotelUserEntity h = new HotelUserEntity();

                    //将表格的数据封装到HUE里
                    h.Password = dr["Password"].ToString();
                    h.UserID = (int)dr["UserID"];
                    h.UserName = dr["UserName"].ToString();
                    h.Address = dr["Address"].ToString();
                    h.Email = dr["Email"].ToString();
                    h.Name = dr["Name"].ToString();
                    h.Status = Convert.ToInt32(dr["Status"]);
                    h.Phone = dr["Phone"].ToString();

                    //循环一次将r放入泛型对象HUE里
                    HUE.Add(h);
                }
            }
            //返回泛型对象HUE
            return HUE;
        }

        public static DataTable GetHotelUserByUserID(int UserID)
        {
            string sql = "select * from HotelUser where UserID = "+UserID;
            return SQLHelp.FillTable(sql);
        }

        public static DataTable GetHotelUserByUserName(string UserName)
        {
            string sql = "select * from HotelUser where UserName = '"+UserName+"'";
            return SQLHelp.FillTable(sql);
        }

        public static DataTable GetHotelUserByName(string Name)
        { 
            string sql = "select * from HotelUser where Name like '%"+Name+"%'";
            return SQLHelp.FillTable(sql);
        }

        public static int GetUserIDByUserName(string UserName)
        {
            string sql = "select userid from hoteluser where username = '"+UserName+"'";
            return Convert.ToInt32(SQLHelp.FillTable(sql).Rows[0][0]);
        }

        /// <summary>
        /// 登录验证
        /// </summary>
        /// <param name="HUE"></param>
        /// <returns></returns>
        public static DataTable ExistUser(HotelUserEntity HUE)
        {
            string sql = "select count(*) from HotelUser where UserName = '"+HUE.UserName+"'and Password ='"+HUE.Password+"'";
            return SQLHelp.FillTable(sql);
        }

        public static HotelUserEntity GetUserInfoByUserName(string UserName)
        {
            HotelUserEntity HUE = new HotelUserEntity();
            string sql = "select Name, Status from HotelUser where UserName = '"+UserName+"'";
            DataTable dt = SQLHelp.FillTable(sql);
            HUE.Name = dt.Rows[0]["Name"].ToString();
            HUE.Status = Convert.ToInt32(dt.Rows[0]["Status"]);
            return HUE;
        }
        public static int GetCountUserName(string UserName)
        {
            string sql = "select count(*) from HotelUser where UserName = '"+UserName+"'";
            return Convert.ToInt32(SQLHelp.FillTable(sql).Rows[0][0]);
        }

        /// <summary>
        /// 增加HotelUser表
        /// </summary>
        /// <param name="HUE"></param>
        /// <returns></returns>
        public static int AddHotelUser(HotelUserEntity HUE)
        {
            //从传进来的HotelUserEntity对象拼凑sql语句
            string sql = "insert into HotelUser (UserName,Password,Name,Address,Phone,Email) values('" + HUE.UserName + "','" + HUE.Password + "','" + HUE.Name + "','" + HUE.Address + "','" + HUE.Phone + "','" + HUE.Email + "')";

            //调用SQLHelp类的ExecQuery() 方法执行拼凑的sql，及返回相应的数据
            return SQLHelp.ExecQuery(sql);
        }

        /// <summary>
        /// 修改HotelUser表
        /// </summary>
        /// <param name="HUE"></param>
        /// <returns></returns>
        public static int UpdateHotelUser(HotelUserEntity HUE)
        {
            //从传进来的HotelUserEntity对象拼凑sql语句
            string sql = "update HotelUser set UserName = '" + HUE.UserName + "',Password = '" + HUE.Password + "' where UserID = " + HUE.UserID;

            //调用SQLHelp类的ExecQuery() 方法执行拼凑的sql，及返回相应的数据
            return SQLHelp.ExecQuery(sql);
        }

        public static int UpdateStatusByUserName(int Status,string UserName,string SessionName)
        {
            string sql = "update HotelUser set Status = "+Status+" where UserName = '"+UserName+"' and UserName <> '"+SessionName+"'";
            return SQLHelp.UnusualExecQuery(sql);
        }


        /// <summary>
        /// 密码修改
        /// </summary>
        /// <param name="HUE"></param>
        /// <returns></returns>
        public static int ChangePwd(HotelUserEntity HUE,string pwd)
        {
            //从传进来的HotelUserEntity对象拼凑sql语句
            string sql = "update HotelUser set Password = '" + HUE.Password + "' where UserName = '" + HUE.UserName + "' and password = '"+pwd+"'";

            //调用SQLHelp类的ExecQuery() 方法执行拼凑的sql，及返回相应的数据
            return SQLHelp.UnusualExecQuery(sql);
        }

        /// <summary>
        /// 删除HotelUser表
        /// </summary>
        /// <param name="HUE"></param>
        /// <returns></returns>
        public static int DeleteHotelUser(HotelUserEntity HUE)
        {
            //从传进来的HotelUserEntity对象拼凑sql语句
            string sql = "delete HotelUser where UserID = "+HUE.UserID;

            //调用SQLHelp类的ExecQuery() 方法执行拼凑的sql，及返回相应的数据
            return SQLHelp.ExecQuery(sql);
        }

        public static int DeleteHotelUserByUserID(int UserID)
        {
            string sql = "EXEC DEL_HOTELUSER "+UserID;
            return SQLHelp.UnusualExecQuery(sql);
        }
    }
}