categories.cs

wrox c#高级编程

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


namespace Wrox.WebModules.Forums.Data
{
	public class CategoryDetails
	{
		public int CategoryID;
		public string Name;
		public string ImageUrl;
		public int Position;
	}

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

		// return all the Categories
		public DataSet GetCategories()
		{
			return RunProcedure("sp_Forums_GetCategories", new IDataParameter[]{}, "Categories");
		}


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

		// return only the record with the specified ID
		public CategoryDetails GetDetails(int categoryID)
		{
			// create the parameter
			SqlParameter[] parameters = { new SqlParameter("@CategoryID", SqlDbType.Int, 4) };
			parameters[0].Value = categoryID;
			
			using(DataSet categories = RunProcedure("sp_Forums_GetCategoryDetails", parameters, "Categories"))
			{
				CategoryDetails details = new CategoryDetails();
				// if the record was found, set the properties of the class instance
				if (categories.Tables[0].Rows.Count > 0)
				{
					DataRow rowCategory = categories.Tables[0].Rows[0];
					details.CategoryID = (int)rowCategory["CategoryID"];
					details.Name = rowCategory["CategoryName"].ToString();
					details.ImageUrl = rowCategory["CategoryImageUrl"].ToString();
					details.Position = (rowCategory["CategoryPosition"]==DBNull.Value ? 
						0 : (int)rowCategory["CategoryPosition"]);
				}
				else
					details.CategoryID = -1;

				return details;
			}
		}


		// add a new record
		public int Add(string categoryName, string categoryImageUrl, int categoryPosition)
		{
			int numAffected;
	
			// create the parameters
			SqlParameter[] parameters = { 
				new SqlParameter("@CategoryName", SqlDbType.VarChar, 100),
				new SqlParameter("@CategoryImageUrl", SqlDbType.VarChar, 100),
				new SqlParameter("@CategoryPosition", SqlDbType.Int, 4),
				new SqlParameter("@CategoryID", SqlDbType.Int, 4)
			};	
			
			// set the values
			parameters[0].Value = categoryName.Trim();
			parameters[1].Value = categoryImageUrl.Trim();
			parameters[2].Value = categoryPosition;
			parameters[3].Direction = ParameterDirection.Output;

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

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


		// update all the fields of the specified category
		public bool Update(int categoryID, string categoryName, string categoryImageUrl, int categoryPosition)
		{
			int numAffected;
		
			// create the parameters
			SqlParameter[] parameters = {
				new SqlParameter("@CategoryID", SqlDbType.Int, 4),
				new SqlParameter("@CategoryName", SqlDbType.VarChar, 100),
				new SqlParameter("@CategoryImageUrl", SqlDbType.VarChar, 100),
				new SqlParameter("@CategoryPosition", SqlDbType.Int, 4)
			};
			
			// set the values
			parameters[0].Value = categoryID;
			parameters[1].Value = categoryName.Trim();
			parameters[2].Value = categoryImageUrl.Trim();
			parameters[3].Value = categoryPosition;

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

			return (numAffected == 1);
		}


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

			return (numAffected == 1);
		}

	}
}