impuserdal.cs

酒店在线订餐管理系统

using System;
using System.Collections.Generic;
using System.Text;
using IDDal;
using Model;
using System.Data.Common;
using System.Data;
using System.Data.SqlClient;
namespace ImpDDal
{
    public class ImpUserDal:DbCommandStore,UserDal<User>
    {
        #region UserDal<User> 成员
        DbCommand cmd = GetCommand();
        #region 创建一个新用户
        public int CreateNewUser(User user)
        {
            cmd.CommandText = "INSERT INTO UT_User(LoginName,Pwd,Sex,DisPlayName,Mark,Contact,Tel) VALUES(@LN,@PWD,@S,@D,@M,@C,@T)";
            SqlParameter[] parameter = new SqlParameter[7];
            parameter[0] = new SqlParameter("LN", user.LoginName);
            parameter[1] = new SqlParameter("PWD", user.Pwd);
            parameter[2] = new SqlParameter("S", user.Sex);
            parameter[3] = new SqlParameter("D", user.DisPlay);
            parameter[4] = new SqlParameter("M", user.Mark);
            parameter[5] = new SqlParameter("C", user.Contact);
            parameter[6] = new SqlParameter("T", user.Tel);

            cmd.Parameters.Add(parameter[0]);
            cmd.Parameters.Add(parameter[1]);
            cmd.Parameters.Add(parameter[2]);
            cmd.Parameters.Add(parameter[3]);
            cmd.Parameters.Add(parameter[4]);
            cmd.Parameters.Add(parameter[5]);
            cmd.Parameters.Add(parameter[6]);
            try
            {
                if(cmd.Connection.State==System.Data.ConnectionState.Closed)
                   cmd.Connection.Open();
                return cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new Exception("增加用户失败"+ex.Message);
            }
            finally
            {
                cmd.Parameters.Clear();
                cmd.Connection.Close();
            }
        }
        #endregion

        #region 修改一个用户信息
        public int ModifyUser(User user)
        {
            cmd.CommandText = "UPDATE UT_User SET Pwd=@PWD,DisPlayName=@DN,[Group]=@G WHERE LoginName=@LN";
            SqlParameter[] parameter = new SqlParameter[4];
            parameter[0] = new SqlParameter("PWD", user.Pwd);
            parameter[1] = new SqlParameter("DN", user.DisPlay);
            parameter[2] = new SqlParameter("G", user.Group);
            parameter[3] = new SqlParameter("LN", user.Guid);


            cmd.Parameters.Add(parameter[0]);
            cmd.Parameters.Add(parameter[1]);
            cmd.Parameters.Add(parameter[2]);
            cmd.Parameters.Add(parameter[3]);


            try
            {
                if (cmd.Connection.State == System.Data.ConnectionState.Closed)
                    cmd.Connection.Open();
                return cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                cmd.Parameters.Clear();
                throw new Exception("修改用户失败" + ex.Message);
            }
            finally
            {
                cmd.Parameters.Clear();
                cmd.Connection.Close();
            }
        }

        public int ModifyUserInfo(User user)
        {
            cmd.CommandText = "UPDATE UT_User SET Pwd=@PWD,DisPlayName=@DN,Contact=@G,Tel=@T WHERE LoginName=@LN";
            SqlParameter[] parameter = new SqlParameter[5];
            parameter[0] = new SqlParameter("PWD", user.Pwd);
            parameter[1] = new SqlParameter("DN", user.DisPlay);
            parameter[2] = new SqlParameter("G", user.Contact);
            parameter[3] = new SqlParameter("T", user.Tel);
            parameter[4] = new SqlParameter("LN", user.LoginName);


            cmd.Parameters.Add(parameter[0]);
            cmd.Parameters.Add(parameter[1]);
            cmd.Parameters.Add(parameter[2]);
            cmd.Parameters.Add(parameter[3]);
            cmd.Parameters.Add(parameter[4]);


            try
            {
                if (cmd.Connection.State == System.Data.ConnectionState.Closed)
                    cmd.Connection.Open();
                return cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                cmd.Parameters.Clear();
                throw new Exception("修改用户失败" + ex.Message);
            }
            finally
            {
                cmd.Parameters.Clear();
                cmd.Connection.Close();
            }
        }
        #endregion

        #region 删除一个用户
        public int DeleteUser(User user)
        {
            cmd.CommandText = "DELETE FROM UT_User WHERE LoginName=@LN";
            cmd.Parameters.Add(user.Guid);

            try
            {
                if (cmd.Connection.State == System.Data.ConnectionState.Closed)
                    cmd.Connection.Open();
                return cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new Exception("删除用户失败" + ex.Message);
            }
            finally
            {
                cmd.Parameters.Clear();
                cmd.Connection.Close();
            }
        }
        #endregion

        #region 用户登录
        public User Login(string account, string password)
        {
            cmd.CommandText = "SELECT Pwd FROM UT_User WHERE LoginName=@LN";
            SqlParameter p = new SqlParameter("LN", account);
            cmd.Parameters.Add(p);
            try
            {
                if (cmd.Connection.State == System.Data.ConnectionState.Closed)
                    cmd.Connection.Open();
                if (cmd.ExecuteScalar() == null)
                {
                    throw new Exception("用户不存在");
                }
                else if (!cmd.ExecuteScalar().ToString().Equals(password))
                {
                    throw new Exception("密码错误");
                }
                else
                {
                    cmd.CommandText = "SELECT * FROM UV_ActiveUser WHERE LoginName=@LN";
                    if (cmd.Connection.State == System.Data.ConnectionState.Closed)
                        cmd.Connection.Open();
                    DbDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    User u = null;
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                            u = new User(reader.GetString(1), reader.GetString(2), reader.GetString(3), reader.GetString(4), reader.GetString(5), reader.GetString(6), reader.GetString(7), int.Parse(reader.GetValue(8).ToString()), reader.IsDBNull(9) ? 0 : reader.GetInt32(9));
                    }
                    else
                    {
                        reader.Close();
                        cmd.CommandText = "SELECT * FROM UT_User WHERE LoginName=@LN";
                        if (cmd.Connection.State == System.Data.ConnectionState.Closed)
                            cmd.Connection.Open();
                        reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                        while (reader.Read())
                            u = new User(reader.GetString(1), reader.GetString(2), reader.GetString(3), reader.GetString(4), reader.GetString(5), reader.GetString(6), reader.GetString(7), int.Parse(reader.GetValue(8).ToString()), 0);
                    }

                    cmd.Connection.Close();
                    return u;
                }

            }
            catch (Exception ex)
            {
                throw new Exception("登陆异常" + ex.Message);
            }
            finally
            {
                cmd.Parameters.Clear();
                cmd.Connection.Close();
            }
        }
        #endregion

        #region 检测是否存在用户
        public int ExsitUser(User user)
        {
            cmd.CommandText = "SELECT * FROM UT_User WHERE LoginName=@LN";
            SqlParameter parameter=new SqlParameter("LN",user.LoginName);
            cmd.Parameters.Add(parameter);

            try
            {
              if(cmd.Connection.State==System.Data.ConnectionState.Closed)
                  cmd.Connection.Open();
              if (cmd.ExecuteScalar() == null)
                  return 0;
              else
                  return 1;
            }
            catch (Exception ex)
            {
                throw new Exception("检索用户名失败" + ex.Message);
            }
            finally
            {
                cmd.Parameters.Clear();