customersinfodal.cs

酒店管理系统

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using HotelManagerModels;

namespace HotelManagerDAL
{
    public class CustomersInfoDAL
    {
        string strConn = ConfigurationManager.ConnectionStrings["HotelConn"].ToString();

        #region 查询所有用户信息
        /// <summary>
        /// 查询所有用户信息
        /// </summary>
        /// <returns>用户实体</returns>
        public List<CustomersInfo> SelectCustomersInfoAll()
        {
            using(SqlConnection conn = new SqlConnection(strConn))
            {
                SqlCommand cmd = new SqlCommand("usp_SelectCustomersInfoAll", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                List<CustomersInfo> list = new List<CustomersInfo>();
                try
                {
                    conn.Open();
                    SqlDataReader dr = cmd.ExecuteReader();
                    while (dr.Read())
                    {
                        CustomersInfo c = new CustomersInfo();
                        c.ID = dr["Id"].ToString();
                        c.CName = dr["CName"].ToString();
                        c.CPhone = dr["CPhone"].ToString();
                        CustomersType ct=new CustomersType();
                        ct.ID=Convert.ToInt32(dr["CTypeID"]);
                        ct.Name = dr["Name"].ToString();
                        c.Ct = ct;
                        list.Add(c);
                    }
                    return list;
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
                finally
                {
                    conn.Close();
                }
            }
        }
        #endregion

        #region 根据身份证号码查询用户信息
        /// <summary>
        /// 根据身份证号码查询用户信息
        /// </summary>
        /// <returns>用户实体</returns>
        public List<CustomersInfo> SelectCustomersInfoByID(string id)
        {
            using (SqlConnection conn = new SqlConnection(strConn))
            {
                SqlCommand cmd = new SqlCommand("usp_SelectCustomersInfoByID", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@ID", SqlDbType.VarChar, 18).Value = id;
                List<CustomersInfo> list = new List<CustomersInfo>();
                try
                {
                    conn.Open();
                    SqlDataReader dr = cmd.ExecuteReader();
                    while (dr.Read())
                    {
                        CustomersInfo c = new CustomersInfo();
                        c.ID = dr["Id"].ToString();
                        c.CName = dr["CName"].ToString();
                        c.CPhone = dr["CPhone"].ToString();
                        CustomersType ct = new CustomersType();
                        ct.ID = Convert.ToInt32(dr["CTypeID"]);
                        ct.Name = dr["Name"].ToString();
                        c.Ct = ct;
                        list.Add(c);
                    }
                    return list;
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
                finally
                {
                    conn.Close();
                }
            }
        }
        #endregion

        #region 根据身份证号码模糊查询用户信息
        /// <summary>
        /// 根据身份证号码模糊查询用户信息
        /// </summary>
        /// <returns>用户实体</returns>
        public List<CustomersInfo> SelectCustomersInfoByCID(string id)
        {
            using (SqlConnection conn = new SqlConnection(strConn))
            {
                SqlCommand cmd = new SqlCommand("usp_SelectCustomersInfoByCID", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@ID", SqlDbType.VarChar, 18).Value = id;
                List<CustomersInfo> list = new List<CustomersInfo>();
                try
                {
                    conn.Open();
                    SqlDataReader dr = cmd.ExecuteReader();
                    while (dr.Read())
                    {
                        CustomersInfo c = new CustomersInfo();
                        c.ID = dr["Id"].ToString();
                        c.CName = dr["CName"].ToString();
                        c.CPhone = dr["CPhone"].ToString();
                        CustomersType ct = new CustomersType();
                        ct.ID = Convert.ToInt32(dr["CTypeID"]);
                        ct.Name = dr["Name"].ToString();
                        c.Ct = ct;
                        list.Add(c);
                    }
                    return list;
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
                finally
                {
                    conn.Close();
                }
            }
        }
        #endregion

        #region 添加用户信息
        /// <summary>
        /// 添加用户信息
        /// </summary>
        /// <param name="ci">用户实体对象</param>
        public void InsertCustomersInfo(CustomersInfo ci)
        {
            using (SqlConnection conn = new SqlConnection(strConn))
            {
                SqlCommand cmd = new SqlCommand("usp_InsertCustomersInfo",conn);
                cmd.CommandType = CommandType.StoredProcedure;
                SqlParameter[] pars = new SqlParameter[]{
                    new SqlParameter("@Id",SqlDbType.VarChar,18),
                    new SqlParameter("@CName",SqlDbType.VarChar,50),
                    new SqlParameter("@CPhone",SqlDbType.VarChar,50),
                    new SqlParameter("@CTypeID",SqlDbType.Int)
                };
                pars[0].Value = ci.ID;
                pars[1].Value = ci.CName;
                pars[2].Value = ci.CPhone;
                pars[3].Value = ci.Ct.ID;
                foreach (SqlParameter par in pars)
                {
                    cmd.Parameters.Add(par);
                }
                try
                {
                    conn.Open();
                    cmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
                finally
                {
                    conn.Close();
                }
            }
        }
        #endregion

        #region 修改用户信息
        /// <summary>
        /// 修改用户信息
        /// </summary>
        /// <param name="ci">用户实体对象</param>
        /// <returns>bool</returns>
        public bool UpdateCustomersInfo(CustomersInfo ci)
        {
            using (SqlConnection conn = new SqlConnection(strConn))
            {
                SqlCommand cmd = new SqlCommand("usp_UpdateCustomersInfo", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                SqlParameter[] pars = new SqlParameter[]{
                    new SqlParameter("@Id",SqlDbType.VarChar,18),
                    new SqlParameter("@CName",SqlDbType.VarChar,50),
                    new SqlParameter("@CPhone",SqlDbType.VarChar,50),
                    new SqlParameter("@CTypeID",SqlDbType.Int)
                };
                pars[0].Value = ci.ID;
                pars[1].Value = ci.CName;
                pars[2].Value = ci.CPhone;
                pars[3].Value = ci.Ct.ID;
                foreach (SqlParameter par in pars)
                {
                    cmd.Parameters.Add(par);
                }
                try
                {
                    conn.Open();
                    cmd.ExecuteNonQuery();
                    return true;
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
                finally
                {
                    conn.Close();
                }
            }
        }
        #endregion

        #region 根据身份证号删除用户信息
        /// <summary>
        /// 根据身份证号删除用户信息
        /// </summary>
        /// <param name="id">身份证号</param>
        public void DeleteCustomersInfo(string id)
        { 
            using(SqlConnection conn=new SqlConnection(strConn))
            {
                SqlCommand cmd = new SqlCommand("usp_DeleteCustomersInfo",conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@Id",SqlDbType.VarChar,18).Value=id;
                try
                {
                    conn.Open();
                    cmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
                finally
                {
                    conn.Close();
                }
            }
        }
        #endregion
    }
}