categories.cs

wrox c#高级编程

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


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

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

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


		// 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_News_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["Name"].ToString();
					details.Description = rowCategory["Description"].ToString();
					details.ImageUrl = rowCategory["ImageUrl"].ToString();
				}
				else
					details.CategoryID = -1;

				return details;
			}
		}

		// 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_News_GetCategoryDetails", parameters, "Categories"))
			{
				return categories.Tables[0].Rows[0];
			}
		}


		// 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_News_DeleteCategory", parameters, out numAffected);

			return (numAffected == 1);
		}


		// update Name, Description and ImageUrl of the record identified by the specified ID
		public bool Update(int categoryID, string name, string description, string imageUrl)
		{
			int numAffected;
		
			// create the parameters
			SqlParameter[] parameters = {
				new SqlParameter("@CategoryID", SqlDbType.Int, 4),
				new SqlParameter("@Name", SqlDbType.VarChar, 50),
				new SqlParameter("@Description", SqlDbType.VarChar, 250),
				new SqlParameter("@ImageUrl", SqlDbType.VarChar, 100)
			};
			// set the values
			parameters[0].Value = categoryID;
			parameters[1].Value = name.Trim();
			parameters[2].Value = description.Trim();
			parameters[3].Value = imageUrl.Trim();

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

			return (numAffected == 1);
		}

		
		// add a new category
		public int Add(string name, string description, string imageUrl)
		{
			int numAffected;
	
			// create the parameters
			SqlParameter[] parameters = { 
				new SqlParameter("@Name", SqlDbType.VarChar, 50),
				new SqlParameter("@Description", SqlDbType.VarChar, 250),
				new SqlParameter("@ImageUrl", SqlDbType.VarChar, 100),
				new SqlParameter("@CategoryID", SqlDbType.Int, 4),
			};	
			
			// set the values
			parameters[0].Value = name.Trim();
			parameters[1].Value = description.Trim();
			parameters[2].Value = imageUrl.Trim();
			parameters[3].Direction = ParameterDirection.Output;
			
			// run the procedure
			RunProcedure("sp_News_InsertCategory", parameters, out numAffected);

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

	}
}