members.cs

wrox c#高级编程

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


namespace Wrox.WebModules.Forums.Data
{
	public class MemberDetails
	{
		public int MemberID;
		public int UserID;
		public string Name;
		public string Email;
		public bool ShowEmail;
		public string Signature;
		public string AvatarUrl;
		public string Homepage;
		public DateTime AddedDate;
	}

	public class Members : Wrox.WebModules.Data.DbObject
	{
		public Members(string newConnectionString) : base(newConnectionString)
		{	}

		// return all the members
		public DataSet GetMembers()
		{
			return RunProcedure("sp_Forums_GetMembers", new IDataParameter[]{}, "Members");
		}


		// return only the record with the specified ID
		public DataRow GetDetailsRow(int memberID)
		{
			// create the parameter
			SqlParameter[] parameters = { new SqlParameter("@MemberID", SqlDbType.Int, 4) };
			parameters[0].Value = memberID;
			
			using(DataSet members = RunProcedure("sp_Forums_GetMemberDetails", parameters, "Members"))
			{
				// return the first row, which is the only one
				return members.Tables[0].Rows[0];
			}
		}

		// return only the record with the specified ID
		public MemberDetails GetDetails(int memberID)
		{
			// create the parameter
			SqlParameter[] parameters = { new SqlParameter("@MemberID", SqlDbType.Int, 4) };
			parameters[0].Value = memberID;
			
			using(DataSet members = RunProcedure("sp_Forums_GetMemberDetails", parameters, "Members"))
			{
				MemberDetails details = new MemberDetails();
				// if the record was found, set the properties of the class instance
				if (members.Tables[0].Rows.Count > 0)
				{
					DataRow rowMember = members.Tables[0].Rows[0];
					details.MemberID = (int)rowMember["MemberID"];
					details.UserID = (int)rowMember["UserID"];
					details.Name = rowMember["MemberName"].ToString();
					details.Email = rowMember["Email"].ToString();
					details.Signature = rowMember["Signature"].ToString();
					details.AvatarUrl = rowMember["AvatarUrl"].ToString();
					details.Homepage = rowMember["Homepage"].ToString();
					details.ShowEmail = Convert.ToBoolean(rowMember["ShowEmail"]);
					details.AddedDate = Convert.ToDateTime(rowMember["AddedDate"]);
				}
				else
					details.MemberID = -1;

				return details;
			}
		}


		// retrieve the ID of the member "attached" to the specified user
		public int GetMemberID(int userID)
		{
			int numAffected;
	
			// create the parameters
			SqlParameter[] parameters = { 
				new SqlParameter("@UserID", SqlDbType.Int, 4),
				new SqlParameter("@MemberID", SqlDbType.Int, 4)
			};	
			
			// set the values
			parameters[0].Value = userID;
			parameters[1].Direction = ParameterDirection.Output;

			// run the procedure
			RunProcedure("sp_Forums_GetMemberID", parameters, out numAffected);

			return (int)parameters[1].Value;
		}


		// add a new record
		public int Add(int userID, string memberName, bool showEmail, string signature, string avatarUrl, string homepage)
		{
			int numAffected;
	
			// create the parameters
			SqlParameter[] parameters = { 
				new SqlParameter("@UserID", SqlDbType.Int, 4),
				new SqlParameter("@MemberName", SqlDbType.VarChar, 15),
				new SqlParameter("@ShowEmail", SqlDbType.Bit, 1),
				new SqlParameter("@Signature", SqlDbType.VarChar, 300),
				new SqlParameter("@AvatarUrl", SqlDbType.VarChar, 100),
				new SqlParameter("@Homepage", SqlDbType.VarChar, 100),
				new SqlParameter("@MemberID", SqlDbType.Int, 4)
			};	
			
			// set the values
			parameters[0].Value = userID;
			parameters[1].Value = memberName.Trim();
			parameters[2].Value = showEmail;
			parameters[3].Value = signature;
			parameters[4].Value = avatarUrl.Trim();
			parameters[5].Value = homepage.Trim();
			parameters[6].Direction = ParameterDirection.Output;

			// run the procedure
			RunProcedure("sp_Forums_InsertMember", parameters, out numAffected);

			return (int)parameters[6].Value;
		}


		// update all the fields of the specified category
		public bool Update(int memberID, string memberName, bool showEmail, string signature, string avatarUrl, string homepage)
		{
			int numAffected;
		
			// create the parameters
			SqlParameter[] parameters = {
				new SqlParameter("@MemberID", SqlDbType.Int, 4),
				new SqlParameter("@MemberName", SqlDbType.VarChar, 15),
				new SqlParameter("@ShowEmail", SqlDbType.Bit, 1),
				new SqlParameter("@Signature", SqlDbType.VarChar, 300),
				new SqlParameter("@AvatarUrl", SqlDbType.VarChar, 100),
				new SqlParameter("@Homepage", SqlDbType.VarChar, 100),
			};
			
			// set the values
			parameters[0].Value = memberID;
			parameters[1].Value = memberName.Trim();
			parameters[2].Value = showEmail;
			parameters[3].Value = signature;
			parameters[4].Value = avatarUrl.Trim();
			parameters[5].Value = homepage.Trim();

			RunProcedure("sp_Forums_UpdateMember", parameters, out numAffected);

			return (numAffected == 1);
		}


		// delete the record identified by the specified ID
		public bool Delete(int memberID)
		{
			int numAffected;
		
			// create the parameter
			SqlParameter[] parameters = { new SqlParameter("@MemberID", SqlDbType.Int, 4) };
			parameters[0].Value = memberID;
			
			RunProcedure("sp_Forums_DeleteMember", parameters, out numAffected);

			return (numAffected == 1);
		}

	}
}