themeaccess.cs

前台：asp.net；后台: sql server 一个功能完善的BBS系统源码。

using System;
using System.Collections;
using Stella.Model;
using Stella.Utility;
using System.Data;
using System.Data.SqlClient;

namespace Stella.SQLServerDAL
{
	/// <summary>
	/// 版块操作
	/// </summary>
	public class ThemeAccess
	{
		private string connString=ConfigHelper.ConnString;

		#region Create

		/// <summary>
		/// 创建新版块
		/// </summary>
		/// <param name="district">乘放待添加的版块的集合</param>
		public void Create(District district)
		{
			SqlParameter[] parms=this.getCreateParms();

			using(SqlConnection conn=new SqlConnection(connString))
			{
				foreach(Theme theme in district.Themes)
				{
					this.setCreateParms(parms,theme);
					SQLHelper.ExecuteNonQuery(conn,CommandType.StoredProcedure,"themes_Create",parms);
				}
			}
		}

		private SqlParameter[] getCreateParms()
		{
			SqlParameter[] parms=SQLHelper.GetCachedParameters("CreateTheme");
			
			if(parms==null)
			{
				parms=new SqlParameter[]{
											new SqlParameter("@tname",SqlDbType.VarChar,50),
											new SqlParameter("@comment",SqlDbType.VarChar,100),
											new SqlParameter("@district",SqlDbType.Int,0),
											new SqlParameter("@lv",SqlDbType.SmallInt,0)
										};
				SQLHelper.CacheParameters("CreateTheme",parms);
			}

			return parms;
		}

		private void setCreateParms(SqlParameter[] parms,Theme th)
		{
			parms[0].Value=th.Tname;
			parms[1].Value=th.Comment;
			parms[2].Value=th.District;
			parms[3].Value=th.Lv;
		}

		#endregion

		#region Update

		/// <summary>
		/// 更新版块的资料
		/// </summary>
		/// <param name="theme">要更新的版块</param>
		public void Update(Theme theme)
		{
			SqlParameter[] parms=this.getUpdateParms();
			this.setUpdateParms(parms,theme);

			SQLHelper.ExecuteNonQuery(connString,CommandType.StoredProcedure,"themes_Update",parms);
		}

		private SqlParameter[] getUpdateParms()
		{
			SqlParameter[] parms=SQLHelper.GetCachedParameters("UpdateTheme");
			
			if(parms==null)
			{
				parms=new SqlParameter[]{
											new SqlParameter("@tid",SqlDbType.Int,0),
											new SqlParameter("@tname",SqlDbType.VarChar,50),
											new SqlParameter("@comment",SqlDbType.VarChar,100),
											new SqlParameter("@district",SqlDbType.Int,0),
											new SqlParameter("@lv",SqlDbType.SmallInt,0)
										};
				SQLHelper.CacheParameters("UpdateTheme",parms);
			}

			return parms;
		}

		private void setUpdateParms(SqlParameter[] parms,Theme th)
		{
			parms[0].Value=th.Tid;
			parms[1].Value=th.Tname;
			parms[2].Value=th.Comment;
			parms[3].Value=th.District;
			parms[4].Value=th.Lv;
		}

		#endregion

		#region UpdateSum

		/// <summary>
		/// 更新版块的资料
		/// </summary>
		/// <param name="sum">要更新的版块</param>
		public void UpdateSum(ThemeInfoSum sum)
		{
			SqlParameter[] parms=this.getUpdateSumParms();

			using(SqlConnection conn=new SqlConnection(connString))
			{
				foreach(ThemeInfo info in sum.Infos)
				{
					this.setUpdateSumParms(parms,info);
					SQLHelper.ExecuteNonQuery(conn,CommandType.StoredProcedure,"themes_UpdateSum",parms);
				}
			}
		}

		private SqlParameter[] getUpdateSumParms()
		{
			SqlParameter[] parms=SQLHelper.GetCachedParameters("UpdateThemeSum");
			
			if(parms==null)
			{
				parms=new SqlParameter[]{
											new SqlParameter("@tid",SqlDbType.Int,0),
											new SqlParameter("@topicSum",SqlDbType.Int,0),
											new SqlParameter("@artSum",SqlDbType.Int,0)
										};
				SQLHelper.CacheParameters("UpdateThemeSum",parms);
			}

			return parms;
		}

		private void setUpdateSumParms(SqlParameter[] parms,ThemeInfo th)
		{
			parms[0].Value=th.Themeid;
			parms[1].Value=th.TopicSum;
			parms[2].Value=th.ArtSum;
		}

		#endregion


		#region Remove

		/// <summary>
		/// 删除版块
		/// </summary>
		/// <param name="id">该版块的id</param>
		public void Remove(int id)
		{
			SqlParameter[] parms=new SqlParameter[]{
													   new SqlParameter("@tid",SqlDbType.Int,0)
												   };
			parms[0].Value=id;

			SQLHelper.ExecuteNonQuery(connString,CommandType.StoredProcedure,"themes_DeleteBytid",parms);
		}

		#endregion

		#region FillWithTopics

		/// <summary>
		/// 使用主题填充版块
		/// </summary>
		/// <param name="theme">要填充的版块</param>
		/// <param name="pageSize">每页大小</param>
		/// <param name="pageNumber">当前页数</param>
		public void FillWithTopics(Theme theme,int pageSize,int pageNumber)
		{
			SqlParameter[] parms=this.getFillWithThemesParms();
			this.setFillWithThemesParms(parms,theme.Tid,pageSize,pageNumber);

			using(SqlDataReader sdr=SQLHelper.ExecuteReader(connString,CommandType.StoredProcedure,"topics_SelectInfoBythemeId",parms))
			{
				Topic tc;

				while(sdr.Read())
				{
					tc=new Topic();

					tc.Tid=sdr.GetInt32(0);
					tc.Caption=sdr.GetString(1);
					tc.AddedMember=sdr.GetString(2);
					tc.AddedDate=sdr.GetDateTime(3);
					tc.HitSum=sdr.GetInt16(4);
					tc.ReplySum=sdr.GetInt16(5);
					tc.LRMember=sdr.GetString(6);
					tc.LRDate=sdr.GetDateTime(7);
					tc.ThemeId=sdr.GetInt32(8);
					tc.Up=sdr.GetInt16(9);

					theme.AddTopic(tc);
				}
			}
		}

		private SqlParameter[] getFillWithThemesParms()
		{
			SqlParameter[] parms=SQLHelper.GetCachedParameters("FillWithThemes");
			
			if(parms==null)
			{
				parms=new SqlParameter[]{
											new SqlParameter("@themeId",SqlDbType.Int,0),
											new SqlParameter("@pageSize",SqlDbType.Int,0),
											new SqlParameter("@pageNumber",SqlDbType.Int,0),
			                            };
				SQLHelper.CacheParameters("FillWithThemes",parms);
			}

			return parms;
		}

		private void setFillWithThemesParms(SqlParameter[] parms,int themeId,int pageSize,int pageNumber)
		{
			parms[0].Value=themeId;
			parms[1].Value=pageSize;
			parms[2].Value=pageNumber;
		}

		#endregion

	}
}