membermgr.cs

来自「ASP.NET的一些开发实例,有论坛管理系统等」· CS 代码 · 共 329 行

using System;
using System.Data;
using System.Data.SqlClient;

namespace MemberMgr
{
	/// <summary>
	/// MemberMgr 的摘要说明。
	/// </summary>
	public class MemberMgr
	{
		ConnStr ConnStr=new ConnStr();
		SqlCommand mycmd;
		SqlDataReader Sqldr;
		
		public MemberMgr()
		{
			//
			// TODO: 在此处添加构造函数逻辑
			//
		}

		//修改会员信息
		protected int RegNMod(int op,ref MemberDetail user)
		{
			//新建一个Sql连接，给定连接数据库的字符串
			SqlConnection myConn=new SqlConnection(ConnStr.ConnectionString);
			//创建一个Sql语句的参数，制定参数的名称和类型
			SqlParameter ParamID=new SqlParameter("@ID",SqlDbType.Int);
			//根据参数的不同确定是注册还是修改会员信息
			switch(op)
			{
				case 0:
					//实例化一个SqlCommand命令
					mycmd=new SqlCommand("Register",myConn);
					//指定参数的类型
					ParamID.Direction=ParameterDirection.Output;
					//将参数加入SqlCommand中
					mycmd.Parameters.Add(ParamID);
					break;
				case 1:
					mycmd=new SqlCommand("Modify",myConn);
					ParamID.Value=user.ID;
					mycmd.Parameters.Add(ParamID);
					break;
			}
			//指定SqlCommand的类型是存储过程
			mycmd.CommandType=CommandType.StoredProcedure;
			//给存储过程每一个参数赋值
			SqlParameter Param;

			Param = new SqlParameter("@PIN", SqlDbType.NVarChar, 30);
			Param.Value = user.PIN;
			mycmd.Parameters.Add(Param);

			Param = new SqlParameter("@Password", SqlDbType.NVarChar, 30);
			Param.Value = user.Password;
			mycmd.Parameters.Add(Param);

			Param = new SqlParameter("@Name", SqlDbType.NVarChar, 30);
			Param.Value = user.Name;
			mycmd.Parameters.Add(Param);

			Param = new SqlParameter("@Birth", SqlDbType.SmallDateTime);
			Param.Value = user.Birth;
			mycmd.Parameters.Add(Param);

			Param = new SqlParameter("@Sex", SqlDbType.Bit);
			Param.Value = user.Sex;
			mycmd.Parameters.Add(Param);

			Param = new SqlParameter("@City", SqlDbType.Int);
			Param.Value = user.City;
			mycmd.Parameters.Add(Param);

			Param = new SqlParameter("@Zip", SqlDbType.NVarChar, 6);
			Param.Value = user.Zip;
			mycmd.Parameters.Add(Param);

			Param = new SqlParameter("@EMail", SqlDbType.NVarChar, 30);
			Param.Value = user.EMail;
			mycmd.Parameters.Add(Param);

			Param = new SqlParameter("@Telephone", SqlDbType.NVarChar, 30);
			Param.Value = user.Telephone;
			mycmd.Parameters.Add(Param);

			Param = new SqlParameter("@Address", SqlDbType.NVarChar, 50);
			Param.Value = user.Address;
			mycmd.Parameters.Add(Param);

			Param = new SqlParameter("@Question", SqlDbType.NVarChar, 50);
			Param.Value = user.Question;
			mycmd.Parameters.Add(Param);

			Param = new SqlParameter("@Answer", SqlDbType.NVarChar, 50);
			Param.Value = user.Answer;
			mycmd.Parameters.Add(Param);

			try
			{
				//打开数据库连接
				myConn.Open();
				//执行SqlCommand命令
				mycmd.ExecuteNonQuery();
			}
			catch(Exception e)
			{
				throw(e);
			}
			finally
			{
				if(myConn.State==ConnectionState.Open)
				{
					myConn.Close();
				}
			}
			if(op==0)
			{
				return Convert.ToInt32(ParamID.Value);
			}
			else
			{
				return user.ID;
			}
		}

		//登记信息
		public int Register(ref MemberDetail user)
		{
			return RegNMod(0,ref user);
		}

		public MemberDetail GetDetailByID(int ID)
		{
			return GetDetail(0,ID);
		}

		public MemberDetail GetDetailByPIN(string PIN)
		{
			return GetDetail(1,PIN);
		}

		//密码提示
		public string PromptPassword(string PIN,string Answer)
		{
			SqlConnection myConn=new SqlConnection(ConnStr.ConnectionString);
			mycmd=new SqlCommand("PromptPassword", myConn);
		
			mycmd.CommandType = CommandType.StoredProcedure;		
			SqlParameter Param;

			Param = new SqlParameter("@PIN", SqlDbType.NVarChar,30);
			Param.Value = PIN;
			mycmd.Parameters.Add(Param);

			Param = new SqlParameter("@Answer", SqlDbType.NVarChar,50);
			Param.Value = Answer;
			mycmd.Parameters.Add(Param);

			Param = new SqlParameter("@Password", SqlDbType.NVarChar,30);
			Param.Direction = ParameterDirection.Output;
			mycmd.Parameters.Add(Param);
			try
			{
				myConn.Open();
				mycmd.ExecuteNonQuery();
			}
			catch(Exception e)
			{
				throw(e);
			}
			finally
			{
				if(myConn.State==ConnectionState.Open)
				{
					myConn.Close();
				}
			}		
			return Param.Value.ToString();
		}

		//密码问题提示
		public string PromptQuestion(string PIN)
		{
			SqlConnection myConn=new SqlConnection(ConnStr.ConnectionString);
			mycmd=new SqlCommand("PromptQuestion", myConn);

			mycmd.CommandType = CommandType.StoredProcedure;		
			SqlParameter Param;											

			Param = new SqlParameter("@PIN", SqlDbType.NVarChar,30);
			Param.Value = PIN;
			mycmd.Parameters.Add(Param);
			
			Param = new SqlParameter("@Question", SqlDbType.NVarChar,50);
			Param.Direction = ParameterDirection.Output;
			mycmd.Parameters.Add(Param);
			try
			{
				myConn.Open();
				mycmd.ExecuteNonQuery();
			}
			catch(Exception e)
			{
				throw(e);
			}
			finally
			{
				if(myConn.State==ConnectionState.Open)
				{
					myConn.Close();
				}
			}		
			return Param.Value.ToString();
		}
		
		

		//修改信息
		public int Modify(ref MemberDetail user)
		{
			return RegNMod(1,ref user);
		}

		//判断是否为合法用户
		public int IsValidUser(string PIN,string Password)
		{
			SqlConnection myConn=new SqlConnection(ConnStr.ConnectionString);
			mycmd=new SqlCommand("IsValidUser", myConn);
		
			mycmd.CommandType = CommandType.StoredProcedure;		
			SqlParameter Param;

			Param = new SqlParameter("@PIN", SqlDbType.NVarChar,30);
			Param.Value = PIN;
			mycmd.Parameters.Add(Param);

			Param = new SqlParameter("@Password", SqlDbType.NVarChar,30);
			Param.Value = Password;
			mycmd.Parameters.Add(Param);

			Param = new SqlParameter("@ID", SqlDbType.Int);
			Param.Direction = ParameterDirection.Output;
			mycmd.Parameters.Add(Param);

			try
			{
				myConn.Open();
				mycmd.ExecuteNonQuery();
			}
			catch(Exception e)
			{
				throw(e);
			}
			finally
			{
				if(myConn.State==ConnectionState.Open)
				{
					myConn.Close();
				}
			}		
			return Convert.ToInt32(Param.Value);
		}

		protected MemberDetail GetDetail(int op,object obj)
		{
			MemberDetail theUser=new MemberDetail();
			SqlConnection myConn=new SqlConnection(ConnStr.ConnectionString);
			switch(op)
			{
				case 0:
					int ID=Convert.ToInt32(obj);
					mycmd = new SqlCommand("GetDetailByID", myConn);
					SqlParameter Param;
					Param = new SqlParameter("@ID", SqlDbType.Int);
					Param.Value = ID;
        			mycmd.Parameters.Add(Param);
					break;
				case 1:
					string PIN=obj.ToString();
					mycmd = new SqlCommand("GetDetailByPIN", myConn);
					SqlParameter Param1;
					Param1 = new SqlParameter("@PIN", SqlDbType.NVarChar);
					Param1.Value = PIN;
					mycmd.Parameters.Add(Param1);
					break;
			}
			mycmd.CommandType=CommandType.StoredProcedure;
			
			try
			{
				myConn.Open();
				Sqldr=mycmd.ExecuteReader();
				if(Sqldr.Read())
				{
					theUser.Address=Sqldr["Address"].ToString();
					theUser.Answer=Sqldr["Answer"].ToString();
					theUser.Birth=Convert.ToDateTime(Sqldr["Birth"]);
					theUser.City=Convert.ToInt32(Sqldr["City"]);
					theUser.EMail=Sqldr["EMail"].ToString();
					theUser.ID=Convert.ToInt32(Sqldr["ID"]);
					theUser.Name=Sqldr["Name"].ToString();
					theUser.Password=Sqldr["Password"].ToString();
					theUser.PIN=Sqldr["PIN"].ToString();
					theUser.Question=Sqldr["Question"].ToString();
					theUser.Sex=Convert.ToBoolean(Sqldr["Sex"]);
					theUser.Telephone=Sqldr["Telephone"].ToString();
					theUser.Zip=Sqldr["Zip"].ToString();
				}
			}
			catch(Exception e)
			{
				throw(e);
			}

			finally
			{
				if(myConn.State==ConnectionState.Open)
				{
					myConn.Close();
				}
				Sqldr.Close();
			}
			return theUser;
		}
	}
}