dataprovider.cs

具有一般blog的相册、文章、作品等功能程序结构也比较清晰采用三层结构开发（利用了SQLHelper.cs（源码））采用了UrlReWrite技术后台采用FTB(FreeTextBox)编辑器

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

namespace liuwei.FrameWork.DB
{
	/// <summary>
	/// DataProvider 的摘要说明。
	/// </summary>
	public class DataProvider : IDbProvider
	{
		public DataProvider()
		{
			//
			// TODO: 在此处添加构造函数逻辑
			//
		}

		
		# region 获取连接字符串
		private string _connectionString = ConfigurationSettings.AppSettings["strConnection"];//私有字段，获取数据库连接字符串
		public string ConnectionString//存取连接字符串
		{
			get {return this._connectionString;}
			set {this._connectionString = value;}
		}
		# endregion

		# region 执行SQLHelper的方法
		/// <summary>
		/// 这里封装了所有的数据库操作，返回DataSet，DataReader等。
		/// </summary>
		/// <param name="sql"></param>
		/// <param name="p"></param>
		/// <returns></returns>
		private DataSet GetDataSet(string sql, params SqlParameter[] p)
		{
			return SqlHelper.ExecuteDataset(ConnectionString,CommandType.StoredProcedure,sql,p);
		}

		private DataTable GetDataTable(string sql, params SqlParameter[] p)
		{
			return SqlHelper.ExecuteDataTable(ConnectionString,CommandType.StoredProcedure,sql,p);
		}

		private IDataReader GetReader(string sql, params SqlParameter[] p)
		{
			return SqlHelper.ExecuteReader(ConnectionString,CommandType.StoredProcedure,sql,p);
		}

		private int NonQueryInt(string sql, params SqlParameter[] p)
		{
			return SqlHelper.ExecuteNonQuery(ConnectionString,CommandType.StoredProcedure,sql,p);
		}

		private bool NonQueryBool(string sql, params SqlParameter[] p)
		{
			return NonQueryInt(sql,p) > 0;
		}

		private void NonQuery(string sql, params SqlParameter[] p)
		{
			SqlHelper.ExecuteNonQuery(ConnectionString,CommandType.StoredProcedure,sql,p);
		}
		# endregion

		#region 获取内容列表
		public DataTable GetContentList(int bigTypeID,int smallTypeID,int List_count)
		{
			return GetDataTable("ceocio_GetContentList",GetContentList_Parameters(bigTypeID,smallTypeID,List_count));
		}
		private SqlParameter[] GetContentList_Parameters(int bigTypeID,int smallTypeID,int List_count)
		{

			SqlParameter[] p = 
			{
				SqlHelper.MakeInParam("@bigTypeID",SqlDbType.Int,4,bigTypeID),
				SqlHelper.MakeInParam("@smallTypeID",SqlDbType.Int,4,smallTypeID),
				SqlHelper.MakeInParam("@List_count",SqlDbType.Int,4,List_count)
			};
			return p;
		}
		#endregion

		#region 获取数据显示---按月份
		public DataTable GetContentListByMonth(int bigTypeID,int smallTypeID,int postYear,int postMonth)
		{
			return GetDataTable("ceocio_GetContentListByMonth",GetContentListByMonth_Parameters(bigTypeID,smallTypeID,postYear,postMonth));
		}
		private SqlParameter[] GetContentListByMonth_Parameters(int bigTypeID,int smallTypeID,int postYear,int postMonth)
		{

			SqlParameter[] p = 
			{
				SqlHelper.MakeInParam("@bigTypeID",SqlDbType.Int,4,bigTypeID),
				SqlHelper.MakeInParam("@smallTypeID",SqlDbType.Int,4,smallTypeID),
				SqlHelper.MakeInParam("@postYear",SqlDbType.Int,4,postYear),
				SqlHelper.MakeInParam("@postMonth",SqlDbType.Int,4,postMonth)
			};
			return p;
		}
		#endregion

		#region 获取数据显示---按年
		public DataTable GetContentListByYear(int bigTypeID,int smallTypeID,int postYear)
		{
			return GetDataTable("ceocio_GetContentListByYear",GetContentListByYear_Parameters(bigTypeID,smallTypeID,postYear));
		}
		private SqlParameter[] GetContentListByYear_Parameters(int bigTypeID,int smallTypeID,int postYear)
		{

			SqlParameter[] p = 
			{
				SqlHelper.MakeInParam("@bigTypeID",SqlDbType.Int,4,bigTypeID),
				SqlHelper.MakeInParam("@smallTypeID",SqlDbType.Int,4,smallTypeID),
				SqlHelper.MakeInParam("@postYear",SqlDbType.Int,4,postYear)
			};
			return p;
		}
		#endregion

		#region 浏览内容
		public IDataReader GetContentView(int postID)
		{
			//view count
			ViewCount(postID);
			return GetReader("ceocio_GetContentView",GetContentView_Parameters(postID));
		}

		private SqlParameter[] GetContentView_Parameters(int postID)
		{

			SqlParameter[] p = 
			{
				SqlHelper.MakeInParam("@postID",SqlDbType.Int,4,postID)
			};
			return p;
		}
		#endregion

		#region 获取内容分类
		public DataTable GetContentType(int bigTypeID)
		{
			return GetDataTable("ceocio_GetContentType",GetContentType_Parameters(bigTypeID));
		}
		private SqlParameter[] GetContentType_Parameters(int bigTypeID)
		{

			SqlParameter[] p = 
			{
				SqlHelper.MakeInParam("@bigTypeID",SqlDbType.Int,4,bigTypeID),
			};
			return p;
		}
		#endregion

		#region 获取小类信息
		public DataTable GetSmallTypeInfo(int smallTypeID)
		{
			return GetDataTable("ceocio_GetSmallTypeInfo",GetSmallTypeInfo_Parameters(smallTypeID));
		}
		private SqlParameter[] GetSmallTypeInfo_Parameters(int smallTypeID)
		{

			SqlParameter[] p = 
			{
				SqlHelper.MakeInParam("@smallTypeID",SqlDbType.Int,4,smallTypeID),
			};
			return p;
		}
		#endregion

		#region 获取大类信息
		public DataTable GetBigTypeInfo(int bigTypeID)
		{
			return GetDataTable("ceocio_GetBigTypeInfo",GetBigTypeInfo_Parameters(bigTypeID));
		}
		private SqlParameter[] GetBigTypeInfo_Parameters(int bigTypeID)
		{

			SqlParameter[] p = 
			{
				SqlHelper.MakeInParam("@bigTypeID",SqlDbType.Int,4,bigTypeID),
			};
			return p;
		}
		#endregion

		#region 获取5条评论
		public DataTable GetCommentTop5(int postID)
		{
			return GetDataTable("ceocio_GetCommentTop5",GetCommentTop5_Parameters(postID));
		}
		private SqlParameter[] GetCommentTop5_Parameters(int postID)
		{

			SqlParameter[] p = 
			{
				SqlHelper.MakeInParam("@postID",SqlDbType.Int,4,postID),
			};
			return p;
		}
		#endregion

		#region 获取全部评论
		public DataTable GetComment(int postID)
		{
			return GetDataTable("ceocio_GetComment",GetComment_Parameters(postID));
		}
		private SqlParameter[] GetComment_Parameters(int postID)
		{

			SqlParameter[] p = 
			{
				SqlHelper.MakeInParam("@postID",SqlDbType.Int,4,postID),
			};
			return p;
		}
		#endregion

		#region 方法组

		#region 添加评论
		public void AddComment(int postID,string CommentTitle,string Comment,string Commenter,string CommenterUrl,string PubIP)
		{
			NonQuery("ceocio_AddComment",AddComment_Parameters(postID,CommentTitle,Comment,Commenter,CommenterUrl,PubIP));
		}
		private SqlParameter[] AddComment_Parameters(int postID,string CommentTitle,string Comment,string Commenter,string CommenterUrl,string PubIP)
		{

			SqlParameter[] p = 
			{
				SqlHelper.MakeInParam("@postID",SqlDbType.Int,4,postID),
				SqlHelper.MakeInParam("@CommentTitle",SqlDbType.NVarChar,100,CommentTitle),
				SqlHelper.MakeInParam("@Comment",Comment),
				SqlHelper.MakeInParam("@Commenter",SqlDbType.NVarChar,50,Commenter),
				SqlHelper.MakeInParam("@CommenterUrl",SqlDbType.NVarChar,50,CommenterUrl),
				SqlHelper.MakeInParam("@PubIP",SqlDbType.VarChar,15,PubIP)
			};
			return p;
		}
		#endregion

		#endregion

		#region 管理员

		#region 提取用户信息
		public void GetUser(string userName)
		{
			NonQuery("ceocio_GetUser",GetUser_Parameters(userName));
		}
		private SqlParameter[] GetUser_Parameters(string userName)
		{

			SqlParameter[] p = 
			{
				SqlHelper.MakeInParam("@userName",SqlDbType.NVarChar,50,userName)
			};
			return p;
		}
		#endregion

		#region 提取内容
		//若smallTypeID=0则显示全部
		public DataTable GetContentAdmin(int smallTypeID)
		{
			return GetDataTable("ceocio_GetContentAdmin",GetContentAdmin_Parameters(smallTypeID));
		}
		private SqlParameter[] GetContentAdmin_Parameters(int smallTypeID)
		{

			SqlParameter[] p = 
			{
				SqlHelper.MakeInParam("@smallTypeID",SqlDbType.Int,4,smallTypeID)
			};
			return p;
		}
		#endregion

		#region 提取大类
		public DataTable GetBigTypeList()
		{
			return GetDataTable("ceocio_GetBigTypeList",GetBigTypeList_Parameters());
		}
		private SqlParameter[] GetBigTypeList_Parameters()
		{
			SqlParameter[] p = 
			{
			};
			return p;
		}
		#endregion

		#region 根据大类号提取小类
		public DataTable GetSmallTypeList(int bigTypeID)
		{
			return GetDataTable("ceocio_GetSmallTypeList",GetSmallTypeList_Parameters(bigTypeID));
		}
		private SqlParameter[] GetSmallTypeList_Parameters(int bigTypeID)
		{

			SqlParameter[] p = 
			{
				SqlHelper.MakeInParam("@bigTypeID",SqlDbType.Int,4,bigTypeID)
			};
			return p;
		}
		#endregion

		#region 添加内容
		public void AddContent(int bigTypeID,int smallTypeID,string title,string intro,string content,bool allowShow,bool allowComment)
		{
			NonQuery("ceocio_AddContent",AddContent_Parameters(bigTypeID,smallTypeID,title,intro,content,allowShow,allowComment));
		}
		private SqlParameter[] AddContent_Parameters(int bigTypeID,int smallTypeID,string title,string intro,string content,bool allowShow,bool allowComment)
		{

			SqlParameter[] p = 
			{
				SqlHelper.MakeInParam("@bigTypeID",SqlDbType.Int,4,bigTypeID),
				SqlHelper.MakeInParam("@smallTypeID",SqlDbType.Int,4,smallTypeID),
				SqlHelper.MakeInParam("@title",SqlDbType.NVarChar,100,title),
				SqlHelper.MakeInParam("@intro",SqlDbType.NVarChar,500,intro),
				SqlHelper.MakeInParam("@content",content),
				SqlHelper.MakeInParam("@allowshow",SqlDbType.Bit,1,allowShow),
				SqlHelper.MakeInParam("@allowComment",SqlDbType.Bit,1,allowComment)
			};
			return p;
		}
		#endregion

		#region 删除内容
		public void DelContent(int postID)
		{
			NonQuery("ceocio_DelContent",DelContent_Parameters(postID));
		}
		private SqlParameter[] DelContent_Parameters(int postID)
		{

			SqlParameter[] p = 
			{
				SqlHelper.MakeInParam("@postID",SqlDbType.Int,4,postID)
			};
			return p;
		}
		#endregion

		#region 获取所有小类信息,用于后台内容分类浏览
		public DataTable GetSmallTypeListAll()
		{
			return GetDataTable("ceocio_GetSmallTypeListAll",GetSmallTypeListAll_Parameters());
		}
		private SqlParameter[] GetSmallTypeListAll_Parameters()
		{

			SqlParameter[] p = 
			{
			};
			return p;
		}
		#endregion

		#region 修改内容
		public void ModifyContent(int postID,int bigTypeID,int smallTypeID,string title,string intro,string content,bool allowShow,bool allowComment)
		{
			NonQuery("ceocio_ModifyContent",ModifyContent_Parameters(postID,bigTypeID,smallTypeID,title,intro,content,allowShow,allowComment));
		}
		private SqlParameter[] ModifyContent_Parameters(int postID,int bigTypeID,int smallTypeID,string title,string intro,string content,bool allowShow,bool allowComment)
		{

			SqlParameter[] p = 
			{
				SqlHelper.MakeInParam("@postID",SqlDbType.Int,4,postID),
				SqlHelper.MakeInParam("@bigTypeID",SqlDbType.Int,4,bigTypeID),
				SqlHelper.MakeInParam("@smallTypeID",SqlDbType.Int,4,smallTypeID),
				SqlHelper.MakeInParam("@title",SqlDbType.NVarChar,100,title),
				SqlHelper.MakeInParam("@intro",SqlDbType.NVarChar,500,intro),
				SqlHelper.MakeInParam("@content",content),
				SqlHelper.MakeInParam("@allowshow",SqlDbType.Bit,1,allowShow),
				SqlHelper.MakeInParam("@allowComment",SqlDbType.Bit,1,allowComment)
			};
			return p;
		}
		#endregion

		#region 获取要修改的内容
		public DataTable GetModifyContent(int postID)
		{
			return GetDataTable("ceocio_GetModifyContent",GetModifyContent_Parameters(postID));
		}
		private SqlParameter[] GetModifyContent_Parameters(int postID)
		{

			SqlParameter[] p = 
			{
				SqlHelper.MakeInParam("@postID",SqlDbType.Int,4,postID)
			};
			return p;
		}
		#endregion

		#region 添加小类
		public void AddSmallType(int bigTypeID,string smallTypeName)
		{
			NonQuery("ceocio_AddSmallType",AddSmallType_Parameters(bigTypeID,smallTypeName));
		}
		private SqlParameter[] AddSmallType_Parameters(int bigTypeID,string smallTypeName)
		{

			SqlParameter[] p = 
			{
				SqlHelper.MakeInParam("@bigTypeID",SqlDbType.Int,4,bigTypeID),
				SqlHelper.MakeInParam("@smallTypeName",SqlDbType.NVarChar,50,smallTypeName)
			};
			return p;
		}
		#endregion

		#region 删除小类
		public void DelSmallType(int smalltypeid)
		{
			NonQuery("ceocio_DelSmallType",DelSmallType_Parameters(smalltypeid));
		}
		private SqlParameter[] DelSmallType_Parameters(int smalltypeid)
		{

			SqlParameter[] p = 
			{
				SqlHelper.MakeInParam("@smalltypeid",SqlDbType.Int,4,smalltypeid)
			};
			return p;
		}
		#endregion

		#region 修改小类
		public void ModifySmallType(int smalltypeid,int bigtypeid,string smalltypename)
		{
			NonQuery("ceocio_ModifySmallType",ModifySmallType_Parameters(smalltypeid,bigtypeid,smalltypename));
		}
		private SqlParameter[] ModifySmallType_Parameters(int smalltypeid,int bigtypeid,string smalltypename)
		{

			SqlParameter[] p = 
			{
				SqlHelper.MakeInParam("@smalltypeid",SqlDbType.Int,4,smalltypeid),
				SqlHelper.MakeInParam("@bigtypeid",SqlDbType.Int,4,bigtypeid),
				SqlHelper.MakeInParam("@smalltypename",SqlDbType.NVarChar,50,smalltypename)
			};
			return p;
		}
		#endregion

		#region 修改用户密码
		public void ModifyUser(string username,string password)
		{
			NonQuery("ceocio_ModifyUser",ModifyUser_Parameters(username,password));
		}
		private SqlParameter[] ModifyUser_Parameters(string username,string password)
		{

			SqlParameter[] p = 
			{
				SqlHelper.MakeInParam("@password",SqlDbType.NVarChar,50,password),
				SqlHelper.MakeInParam("@username",SqlDbType.NVarChar,50,username)
			};
			return p;
		}
		#endregion

		#region 删除评论
		public void DelComment(int commentID)
		{
			NonQuery("ceocio_DelComment",DelComment_Parameters(commentID));
		}
		private SqlParameter[] DelComment_Parameters(int commentID)
		{

			SqlParameter[] p = 
			{
				SqlHelper.MakeInParam("@commentID",SqlDbType.Int,4,commentID)
			};
			return p;
		}
		#endregion



		#endregion

		#region 访问计数
		public void ViewCount(int postID)
		{
			NonQuery("ceocio_ViewCount",ViewCount_Parameters(postID));
		}
		private SqlParameter[] ViewCount_Parameters(int postID)
		{

			SqlParameter[] p = 
			{
				SqlHelper.MakeInParam("@postID",SqlDbType.Int,4,postID)
			};
			return p;
		}
		#endregion



	}
}