subscriptions.cs

来自「wrox c#高级编程」· CS 代码 · 共 211 行

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

namespace Wrox.WebModules.MailingLists.Data
{
	public class SubscriptionDetails
	{
		public int SubscriptionID;
		public int UserID;
		public int ListID;
		public bool Active;
		public string FirstName;
		public string LastName;
		public string Email;
		public DateTime AddedDate;
	}

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

		// return all the subscriptions, active or inactive
		public DataSet GetSubscriptions(int listID)
		{
			return GetSubscriptions(listID, false);
		}

		// return all the subscriptions of the specified list
		public DataSet GetSubscriptions(int listID, bool activeOnly)
		{
			// create the parameter
			SqlParameter[] parameters = {
				new SqlParameter("@ListID", SqlDbType.Int, 4),
				new SqlParameter("@ActiveOnly", SqlDbType.Bit, 1)
			};
			parameters[0].Value = listID;
			parameters[1].Value = activeOnly;
			
			return RunProcedure("sp_MLists_GetSubscriptions", parameters, "Subscriptions");
		}
		
		
		// return the row with all the details of the specified subscription
		public DataRow GetDetailsRow(int subscriptionID)
		{
			// create the parameter
			SqlParameter[] parameters = { new SqlParameter("@SubscriptionID", SqlDbType.Int, 4) };
			parameters[0].Value = subscriptionID;
			
			using(DataSet subscriptions = RunProcedure("sp_MLists_GetSubscriptionDetails", parameters, "Subscriptions"))
			{
				// return the first row, which is the only row returned by the query
				return subscriptions.Tables[0].Rows[0];
			}
		}

		// return the row with all the details of the specified subscription
		public SubscriptionDetails GetDetails(int subscriptionID)
		{
			// create the parameter
			SqlParameter[] parameters = { new SqlParameter("@SubscriptionID", SqlDbType.Int, 4) };
			parameters[0].Value = subscriptionID;
			
			using(DataSet subscriptions = RunProcedure("sp_MLists_GetSubscriptionDetails", parameters, "Subscriptions"))
			{
				SubscriptionDetails details = new SubscriptionDetails();
				// if the record was found, set the properties of the class instance
				if (subscriptions.Tables[0].Rows.Count > 0)
				{
					DataRow rowSubscription = subscriptions.Tables[0].Rows[0];
					details.SubscriptionID = (int)rowSubscription["SubscriptionID"];
					details.UserID = (int)rowSubscription["UserID"];
					details.ListID = (int)rowSubscription["ListID"];
					details.Active = Convert.ToBoolean(rowSubscription["Active"]);
					details.FirstName = rowSubscription["FirstName"].ToString();
					details.LastName = rowSubscription["LastName"].ToString();
					details.Email = rowSubscription["Email"].ToString();
					details.AddedDate = Convert.ToDateTime(rowSubscription["AddedDate"]);
				}
				else
					details.SubscriptionID = -1;

				return details;
			}
		}


		// delete the subscriber identified by the specified ID
		// the user is deleted only if this is the last list he is subscribed to
		public bool Delete(int subscriptionID)
		{
			int numAffected;
		
			// create the parameter
			SqlParameter[] parameters = { new SqlParameter("@SubscriptionID", SqlDbType.Int, 4) };
			parameters[0].Value = subscriptionID;
			
			RunProcedure("sp_MLists_DeleteSubscription", parameters, out numAffected);

			return (numAffected == 1);
		}


		// update FirstName, LastName and Email of the user identified by the subscription ID
		public bool Update(int subscriptionID, string firstName, string lastName, string email, bool active)
		{
			int numAffected;
		
			// create the parameter
			SqlParameter[] parameters = {
				new SqlParameter("@SubscriptionID", SqlDbType.Int, 4),
				new SqlParameter("@FirstName", SqlDbType.VarChar, 50),
				new SqlParameter("@LastName", SqlDbType.VarChar, 50),
				new SqlParameter("@Email", SqlDbType.VarChar, 50),
				new SqlParameter("@Active", SqlDbType.Bit, 1)
			};
			
			// set the values
			parameters[0].Value = subscriptionID;
			parameters[1].Value = firstName.Trim();
			parameters[2].Value = lastName.Trim();
			parameters[3].Value = email.Trim();
			parameters[4].Value = active;

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

			return (numAffected == 1);
		}


		// add a new subscription
		public int Add(int listID, string firstName, string lastName, string email, bool active)
		{
			int numAffected;
	
			// create the parameters
			SqlParameter[] parameters = { 
				new SqlParameter("@ListID", SqlDbType.Int, 4),
				new SqlParameter("@FirstName", SqlDbType.VarChar, 50),
				new SqlParameter("@LastName", SqlDbType.VarChar, 50),
				new SqlParameter("@Email", SqlDbType.VarChar, 50),
				new SqlParameter("@Active", SqlDbType.Bit, 1),
				new SqlParameter("@SubscriptionID", SqlDbType.Int, 4)
			};	
			
			// set the values
			parameters[0].Value = listID;
			parameters[1].Value = firstName.Trim();
			parameters[2].Value = lastName.Trim();
			parameters[3].Value = email.Trim().ToLower();
			parameters[4].Value = active;
			parameters[5].Direction = ParameterDirection.Output;

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

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

		// set the Active state of a subscription
		public bool SetActive(int subscriptionID, bool active)
		{
			int numAffected;
		
			// create the parameters
			SqlParameter[] parameters = {
				new SqlParameter("@SubscriptionID", SqlDbType.Int, 4),
				new SqlParameter("@Active", SqlDbType.Bit, 1)
			};

			// set the values
			parameters[0].Value = subscriptionID;
			parameters[1].Value = active;

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

			return (numAffected == 1);
		}


		// return the ID of the subscription to the specified list, and whose user's Email
		// is the specified Email
		public int GetSubscriptionID(int listID, string email)
		{
			int numAffected;
	
			// create the parameters
			SqlParameter[] parameters = { 
				new SqlParameter("@ListID", SqlDbType.Int, 4),
				new SqlParameter("@Email", SqlDbType.VarChar, 50),
				new SqlParameter("@SubscriptionID", SqlDbType.Int, 4)
			};	
			
			// set the values
			parameters[0].Value = listID;
			parameters[1].Value = email.Trim().ToLower();
			parameters[2].Direction = ParameterDirection.Output;

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

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

}