lists.cs

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

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


namespace Wrox.WebModules.MailingLists.Data
{
	public class ListDetails
	{
		public int ListID;
		public string Name;
		public string Description;
	}

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

		// return all the Lists
		public DataSet GetLists()
		{
			return RunProcedure("sp_MLists_GetLists", new IDataParameter[]{}, "Lists");
		}


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

		// return only the record with the specified ID
		public ListDetails GetDetails(int listID)
		{
			// create the parameter
			SqlParameter[] parameters = { new SqlParameter("@ListID", SqlDbType.Int, 4) };
			parameters[0].Value = listID;
			
			using(DataSet lists = RunProcedure("sp_MLists_GetListDetails", parameters, "Lists"))
			{
				ListDetails details = new ListDetails();
				// if the record was found, set the properties of the class instance
				if (lists.Tables[0].Rows.Count > 0)
				{
					DataRow rowList = lists.Tables[0].Rows[0];
					details.ListID = (int)rowList["ListID"];
					details.Name = rowList["Name"].ToString();
					details.Description = rowList["Description"].ToString();
				}
				else
					details.ListID = -1;

				return details;
			}
		}



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

			return (numAffected == 1);
		}


		// update Name and Description of the record identified by the specified ID
		public bool Update(int listID, string listName, string listDescr)
		{
			int numAffected;
		
			// create the parameters
			SqlParameter[] parameters = {
				new SqlParameter("@ListID", SqlDbType.Int, 4),
				new SqlParameter("@Name", SqlDbType.VarChar, 50),
				new SqlParameter("@Description", SqlDbType.VarChar, 250)
			};
			
			// set the values
			parameters[0].Value = listID;
			parameters[1].Value = listName.Trim();
			parameters[2].Value = listDescr.Trim();

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

			return (numAffected == 1);
		}

		
		// add a new record with the specified Name and Description
		public int Add(string listName, string listDescr)
		{
			int numAffected;
	
			// create the parameters
			SqlParameter[] parameters = { 
				new SqlParameter("@Name", SqlDbType.VarChar, 50),
				new SqlParameter("@Description", SqlDbType.VarChar, 250),
				new SqlParameter("@ListID", SqlDbType.Int, 4)
			};	
			
			// set the values
			parameters[0].Value = listName.Trim();
			parameters[1].Value = listDescr.Trim();
			parameters[2].Direction = ParameterDirection.Output;

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

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

	}
}