第三步 SQLDAL 放操作层 (操作类) Member(一)

2008-01-16 16:36

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

using Buygo.Model;
using Buygo.DBUtility;
namespace Buygo.SQLDAL
{
    public class BuygoMember
    {
        private const string PARM_MEMBERINFO_MEMBERNAME = "@MemberName";
        private const string PARM_MEMBERINFO_MEMBERID = "@MemberID";
        private const string PARM_MEMBERINFO_L_MEMBERID = "@LMemberID";
        private const string PARM_MEMBERINFO_USERNAME = "@UserName";
        private const string PARM_MEMBERINFO_SEX = "@Sex";
        private const string PARM_MEMBERINFO_PASSWORD = "@Password";
        private const string PARM_MEMBERINFO_NATIVEPLACE = "@NativePlace";
        private const string PARM_MEMBERINFO_QUESTION = "@Question";
        private const string PARM_MEMBERINFO_ANSWER = "@Answer";
        private const string PARM_MEMBERINFO_BIRTHDAY = "@Birthday";
        private const string PARM_MEMBERINFO_NICKNAME = "@nickname";

        private const string PARM_MONTACTMODE_MOBILEPHONE = "@MobilePhone";
        private const string PARM_MONTACTMODE_TELEPHONE = "@Telephone";
        private const string PARM_MONTACTMODE_QQ = "@QQ";
        private const string PARM_MONTACTMODE_MSN = "@MSN";
        private const string PARM_MONTACTMODE_EMAIL = "@EMail";
        private const string PARM_MONTACTMODE_PAOPAO = "@Paopao";
        private const string PARM_MONTACTMODE_OTHER = "@Other";
        private const string PARM_MONTACTMODE_ICQ = "@ICQ";

        private const string PARM_RETURN_VALUE = "@return_value";
        private const string PARM_MEMBERINFO_PICTURENAME = "@PictureName";
        public BuygoMember()
        {

        }
        ///

        /// 添加用户(用户注册)
        ///

        /// 会员对象
        /// 密码
        /// 问题
        /// 答案
        ///
        public int InsertMember(Member member, string password, string question, string answer)
        {
            SqlParameter[] sqlpar = new SqlParameter[]
            {
                new SqlParameter(PARM_MEMBERINFO_MEMBERNAME,        member.MemberName),
                new SqlParameter(PARM_MEMBERINFO_SEX,               member.Sex),
                new SqlParameter(PARM_MEMBERINFO_PASSWORD,          password),
                new SqlParameter(PARM_MEMBERINFO_NATIVEPLACE,       member.NativePlace.ID),
                new SqlParameter(PARM_MEMBERINFO_QUESTION,          question),
                new SqlParameter(PARM_MEMBERINFO_ANSWER,            answer),
                new SqlParameter(PARM_MEMBERINFO_BIRTHDAY,          member.Birthday),
                new SqlParameter(PARM_RETURN_VALUE,                 SqlDbType.Int)
            };

            sqlpar[sqlpar.Length - 1].Direction = ParameterDirection.ReturnValue;

            SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringLocalBuygoMember, CommandType.StoredProcedure, "proc_Register", sqlpar);

            return int.Parse(sqlpar[sqlpar.Length - 1].Value.ToString());
        }
        public Member Login(string membername, string password)
        {
            SqlParameter[] sqlpar = new SqlParameter[]
            {
                new SqlParameter (PARM_MEMBERINFO_MEMBERNAME,membername),
                new SqlParameter(PARM_MEMBERINFO_PASSWORD, password),
                new SqlParameter(PARM_RETURN_VALUE, SqlDbType.Int),
            };

            sqlpar[sqlpar.Length - 1].Direction = ParameterDirection.ReturnValue;

            DataSet ds = SqlHelper.ExecuteDataset(SqlHelper.ConnectionStringLocalBuygoMember, CommandType.StoredProcedure, "proc_Member_Login", sqlpar);

            if (ds.Tables.Count == 0)
            {
                return new Member(int.Parse(sqlpar[sqlpar.Length - 1].Value.ToString()));
            }
            else
            {
                //return new Member(int.Parse(sqlpar[sqlpar.Length - 1].Value.ToString()));
                DataRow dr = ds.Tables[0].Rows[0];
                return new Member(dr["MemberID"].ToString(),
                                    dr["MemberName"].ToString(),
                                    int.Parse(dr["MemberNumber"].ToString()),
                                    dr["nickname"].ToString(),
                                    dr["Sex"].ToString(),
                                    new City(1, "1",""));
            }

        }
        ///

        /// 添加用户(用户注册)
        ///

        /// 注册名
        /// 密码
        /// 问题
        /// 答案
        ///
        public Member InsertMember(string memberName, string password, string question, string answer, string nickname)
        {
            SqlParameter[] sqlpar = new SqlParameter[]
            {
                new SqlParameter(PARM_MEMBERINFO_MEMBERNAME,        memberName),
                 new SqlParameter(PARM_MEMBERINFO_NICKNAME,          nickname),
                new SqlParameter(PARM_MEMBERINFO_PASSWORD,          password),
                new SqlParameter(PARM_MEMBERINFO_QUESTION,          question),
                new SqlParameter(PARM_MEMBERINFO_ANSWER,            answer),
                new SqlParameter(PARM_RETURN_VALUE,                 SqlDbType.Int),
            };

            sqlpar[sqlpar.Length - 1].Direction = ParameterDirection.ReturnValue;

            DataSet ds = SqlHelper.ExecuteDataset(SqlHelper.ConnectionStringLocalBuygoMember, CommandType.StoredProcedure, "proc_Register", sqlpar);
            if (ds.Tables.Count == 0)
            {
                return new Member(int.Parse(sqlpar[sqlpar.Length - 1].Value.ToString()));
            }
            else
            {
                DataRow dr = ds.Tables[0].Rows[0];
                return new Member(dr["MemberID"].ToString(),
                                    dr["MemberName"].ToString(),
                                    int.Parse(dr["MemberNumber"].ToString()),
                                    dr["nickname"].ToString(),
                                    dr["Sex"].ToString(),
                                    new City(int.Parse(dr["CityID"].ToString()),
                                                dr["CityName"].ToString(),
                                                dr["ProvinceName"].ToString()));
            }
            //return int.Parse(sqlpar[sqlpar.Length - 1].Value.ToString());
        }

        ///

        /// 添加联系方式
        ///

        /// 用户信息
        ///
        public int EditContactMode(Member member)
        {
            SqlParameter[] sqlpar = new SqlParameter[]
            {
                new SqlParameter(PARM_MEMBERINFO_MEMBERNAME,        member.MemberName),
                new SqlParameter(PARM_MONTACTMODE_MOBILEPHONE,      member.MobilePhone),
                new SqlParameter(PARM_MONTACTMODE_EMAIL,            member.EMail),
                new SqlParameter(PARM_MONTACTMODE_QQ,               member.QQ),
                new SqlParameter(PARM_MONTACTMODE_ICQ,              member.ICQ),
                new SqlParameter(PARM_MONTACTMODE_OTHER,            member.Other),
                new SqlParameter(PARM_MONTACTMODE_MSN,              member.MSN),
                new SqlParameter(PARM_MONTACTMODE_TELEPHONE,        member.Telephone),
                new SqlParameter(PARM_MONTACTMODE_PAOPAO,           member.Paopao),
                new SqlParameter(PARM_RETURN_VALUE,                 SqlDbType.Int)
            };
            sqlpar[sqlpar.Length - 1].Direction = ParameterDirection.ReturnValue;

            SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringLocalBuygoMember, CommandType.StoredProcedure, "proc_EditContactMode", sqlpar);

            return int.Parse(sqlpar[sqlpar.Length - 1].Value.ToString());
        }

        ///

        /// 判断是否已经存在该用户
        ///

        /// 用户名
        ///
        public int HaveMember(string memberName)
        {
            SqlParameter[] sqlpar = new SqlParameter[]
            {
                new SqlParameter(PARM_MEMBERINFO_MEMBERNAME,        memberName),
                new SqlParameter(PARM_RETURN_VALUE,SqlDbType.Int)
            };
            sqlpar[sqlpar.Length - 1].Direction = ParameterDirection.ReturnValue;

            SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringLocalBuygoMember, CommandType.StoredProcedure, "proc_HvaeMember", sqlpar);

            return int.Parse(sqlpar[sqlpar.Length - 1].Value.ToString());
        }