newsletters.cs

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

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

namespace Wrox.WebModules.MailingLists.Data
{
	public class NewsletterDetails
	{
		public int NewsletterID;
		public int ListID;
		public string Subject;
		public string Body;
		public bool IsHTML;
		public DateTime SentDate;
	}

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

		// return all the news of the specified list
		public DataSet GetNewsletters(int listID)
		{
			// create the parameter
			SqlParameter[] parameters = { new SqlParameter("@ListID", SqlDbType.Int, 4) };
			parameters[0].Value = listID;
			
			return RunProcedure("sp_MLists_GetNewsletters", parameters, "Newsletters");
		}
		
		
		// return the row that identifies the specified News record
		public DataRow GetDetailsRow(int newsletterID)
		{
			// create the parameter
			SqlParameter[] parameters = { new SqlParameter("@NewsletterID", SqlDbType.Int, 4) };
			parameters[0].Value = newsletterID;
			
			using(DataSet newsletters = RunProcedure("sp_MLists_GetNewsletterDetails", parameters, "Newsletters"))
			{
				// return the first row, which is the only one
				return newsletters.Tables[0].Rows[0];
			}
		}

		// return the row that identifies the specified News record
		public NewsletterDetails GetDetails(int newsletterID)
		{
			// create the parameter
			SqlParameter[] parameters = { new SqlParameter("@NewsletterID", SqlDbType.Int, 4) };
			parameters[0].Value = newsletterID;
			
			DataSet newsletters = RunProcedure("sp_MLists_GetNewsletterDetails", parameters, "Newsletters");

			NewsletterDetails details = new NewsletterDetails();
			// if the record was found, set the properties of the class instance
			if (newsletters.Tables[0].Rows.Count > 0)
			{
				DataRow rowNewsletter = newsletters.Tables[0].Rows[0];
				details.NewsletterID = (int)rowNewsletter["NewsletterID"];
				details.ListID = (int)rowNewsletter["ListID"];
				details.Subject = rowNewsletter["Subject"].ToString();
				details.Body = rowNewsletter["Body"].ToString();
				details.IsHTML = Convert.ToBoolean(rowNewsletter["IsHTML"]);
				details.SentDate = Convert.ToDateTime(rowNewsletter["SentDate"]);
			}
			else
				details.NewsletterID = -1;

			return details;
		}

		
		// delete the specified news record
		public bool Delete(int newsletterID)
		{
			int numAffected;
		
			// create the parameter
			SqlParameter[] parameters = { new SqlParameter("@NewsletterID", SqlDbType.Int, 4) };
			parameters[0].Value = newsletterID;
			
			RunProcedure("sp_MLists_DeleteNewsletter", parameters, out numAffected);

			return (numAffected == 1);
		}
		
		
		// add a news record with the specified Subject, Body and format, for the
		// specified list
		public int Add(int listID, string subject, string body, bool isHTML)
		{
			int numAffected;
	
			// create the parameters
			SqlParameter[] parameters = { 
				new SqlParameter("@ListID", SqlDbType.Int, 4),
				new SqlParameter("@Subject", SqlDbType.VarChar, 200),
				new SqlParameter("@Body", SqlDbType.Text),
				new SqlParameter("@IsHTML", SqlDbType.Bit, 1),
				new SqlParameter("@NewsletterID", SqlDbType.Int, 4)
			};	
			
			// set the values
			parameters[0].Value = listID;
			parameters[1].Value = subject.Trim();
			parameters[2].Value = body.Trim();
			parameters[3].Value = isHTML;
			parameters[4].Direction = ParameterDirection.Output;

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

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

	}

}