memberaccessor.cs

新闻网站

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using Common;
using Common.Entities;

namespace DAL.Accessor
{
    public class MemberAccessor
    {
        //登陆验证
        public bool Validate(string userName, string password)
        {
            SqlParameter[] sqlParameter = {
                 new SqlParameter("@memberName",SqlDbType.NVarChar,8),
                 new SqlParameter("@memberPass",SqlDbType.NVarChar,20)
            };
            sqlParameter[0].Value = userName;
            sqlParameter[1].Value = password;
            SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.StoredProcedure, "Member_Validate", sqlParameter);
            if (reader.HasRows)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        //查询会员资料
        public MemberData GetModel(string userName)
        {
            SqlParameter[] sqlParameters ={ 
                new SqlParameter("@memberName",SqlDbType.NVarChar)
            };
            sqlParameters[0].Value = userName;
            SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.StoredProcedure, "Member_GetModel", sqlParameters);
            MemberData memberData = new MemberData();
            if (reader.Read())
            {
                memberData.MemberName = reader["MemberName"].ToString();
                memberData.MemberPass = reader["MemberPass"].ToString();
                memberData.Question = reader["Question"].ToString();
                memberData.Answer = reader["Answer"].ToString();
                memberData.Email = reader["Email"].ToString();
                memberData.Birthday = reader["Birthday"].ToString();
                memberData.City = reader["City"].ToString();
                memberData.Introduction = reader["Introduction"].ToString();
                memberData.Phone = reader["Phone"].ToString();
                memberData.Postcode = reader["Postcode"].ToString();
                memberData.Province = reader["Province"].ToString();
                memberData.RealName = reader["RealName"].ToString();
                memberData.Sex = bool.Parse(reader["Sex"].ToString());
                memberData.Address = reader["Address"].ToString();
                memberData.AddNum = int.Parse(reader["AddNum"].ToString());
            }
            return memberData;
        }
        //添加会员资料
        public void Add(MemberData memberData)
        {
            SqlParameter[] sqlParameters = {
					new SqlParameter("@MemberName", SqlDbType.NVarChar),
					new SqlParameter("@MemberPass", SqlDbType.NVarChar),
					new SqlParameter("@Email", SqlDbType.NVarChar),
					new SqlParameter("@Question", SqlDbType.NVarChar),
					new SqlParameter("@Answer", SqlDbType.NVarChar),
					new SqlParameter("@RealName", SqlDbType.NVarChar),
					new SqlParameter("@Sex", SqlDbType.Bit,1),
					new SqlParameter("@Birthday", SqlDbType.NVarChar),
					new SqlParameter("@Province", SqlDbType.NVarChar),
					new SqlParameter("@City", SqlDbType.NVarChar),
					new SqlParameter("@Phone", SqlDbType.NVarChar),
					new SqlParameter("@Address", SqlDbType.NVarChar),
					new SqlParameter("@Postcode", SqlDbType.NVarChar),
					new SqlParameter("@Introduction", SqlDbType.NVarChar)};
            sqlParameters[0].Value = memberData.MemberName;
            sqlParameters[1].Value = memberData.MemberPass;
            sqlParameters[2].Value = memberData.Email;
            sqlParameters[3].Value = memberData.Question;
            sqlParameters[4].Value = memberData.Answer;
            sqlParameters[5].Value = memberData.RealName;
            sqlParameters[6].Value = memberData.Sex;
            sqlParameters[7].Value = memberData.Birthday;
            sqlParameters[8].Value = memberData.Province;
            sqlParameters[9].Value = memberData.City;
            sqlParameters[10].Value = memberData.Phone;
            sqlParameters[11].Value = memberData.Address;
            sqlParameters[12].Value = memberData.Postcode;
            sqlParameters[13].Value = memberData.Introduction;

            using (SqlConnection conn = new SqlConnection())
            {
                conn.ConnectionString = SqlHelper.ConnectionStringLocalTransaction;
                SqlHelper.ExecuteNonQuery(conn, CommandType.StoredProcedure, "Member_ADD", sqlParameters);
            }

        }
        //修改会员资料
        public void UpdateMember(MemberData memberData)
        {
            SqlParameter[] sqlParameters = {
					new SqlParameter("@MemberName", SqlDbType.NVarChar),
					new SqlParameter("@MemberPass", SqlDbType.NVarChar),
					new SqlParameter("@Email", SqlDbType.NVarChar),
					new SqlParameter("@Question", SqlDbType.NVarChar),
					new SqlParameter("@Answer", SqlDbType.NVarChar),
					new SqlParameter("@RealName", SqlDbType.NVarChar),
					new SqlParameter("@Sex", SqlDbType.Bit,1),
					new SqlParameter("@Birthday", SqlDbType.NVarChar),
					new SqlParameter("@Province", SqlDbType.NVarChar),
					new SqlParameter("@City", SqlDbType.NVarChar),
					new SqlParameter("@Phone", SqlDbType.NVarChar),
					new SqlParameter("@Address", SqlDbType.NVarChar),
					new SqlParameter("@Postcode", SqlDbType.NVarChar),
					new SqlParameter("@Introduction", SqlDbType.NVarChar)};
            sqlParameters[0].Value = memberData.MemberName;
            sqlParameters[1].Value = memberData.MemberPass;
            sqlParameters[2].Value = memberData.Email;
            sqlParameters[3].Value = memberData.Question;
            sqlParameters[4].Value = memberData.Answer;
            sqlParameters[5].Value = memberData.RealName;
            sqlParameters[6].Value = memberData.Sex;
            sqlParameters[7].Value = memberData.Birthday;
            sqlParameters[8].Value = memberData.Province;
            sqlParameters[9].Value = memberData.City;
            sqlParameters[10].Value = memberData.Phone;
            sqlParameters[11].Value = memberData.Address;
            sqlParameters[12].Value = memberData.Postcode;
            sqlParameters[13].Value = memberData.Introduction;
            using (SqlConnection conn = new SqlConnection())
            {
                conn.ConnectionString = SqlHelper.ConnectionStringLocalTransaction;
                SqlHelper.ExecuteNonQuery(conn, CommandType.StoredProcedure, "Member_Update", sqlParameters);
            }
        }
        //查询会员数量
        public int GetMemberNum()
        {
            using (SqlConnection conn = new SqlConnection())
            {
                conn.ConnectionString = SqlHelper.ConnectionStringLocalTransaction;
                int count = int.Parse(SqlHelper.ExecuteScalar(conn, CommandType.StoredProcedure, "Member_GetCount", null).ToString());
                return count;    
            }
           
 
        }

    }
}