framework.cs

一阵子

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

namespace PSP
{
	/// <summary>
	/// Summary description for Framework.
	/// </summary>
	public class Framework
	{
		public static bool isSQL = Convert.ToBoolean(System.Configuration.ConfigurationSettings.AppSettings["UseSQL"]);
		public static string SqlConnStr = System.Configuration.ConfigurationSettings.AppSettings["SQLConnectionString"];		
		public static string ConnStr = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];

		public static string Login(string userName, string password)
		{
			userName = CMS.HandleString(userName);
			password = CMS.HandleString(password);

			string sql = "";
			DataTable dt= null;

			if(Framework.isSQL)
			{
				sql = "User_Login";
				SqlParameter[] p = 
				{
					SqlHelper.MakeInParam("@userName",SqlDbType.VarChar,50, userName),
					SqlHelper.MakeInParam("@password",SqlDbType.VarChar,50, password)
				};
				dt = SqlHelper.ExecuteDataTable(Framework.SqlConnStr, CommandType.StoredProcedure, sql,p);
			}
			else
			{
				sql = "select * from PSP_User WHERE UserName= '" + userName + "' AND Password='" + password + "'";
				dt = SqlHelper.ExecuteDataTable(Framework.ConnStr, sql);
			}

			if(dt.Rows.Count==1)
				return dt.Rows[0]["UserID"].ToString();
			else
				return "-1";
		}

		public static void AddMessage(string userID, string toUserID, string body)
		{
			string sql = "";

			if(Framework.isSQL)
			{
				sql = "User_AddMessage";
				SqlParameter[] p = 
				{
					SqlHelper.MakeInParam("@userID",SqlDbType.Int,4, userID),
					SqlHelper.MakeInParam("@toUserID",SqlDbType.Int,4, toUserID),
					SqlHelper.MakeInParam("@body",SqlDbType.VarChar,4000, body)
				};

				SqlHelper.ExecuteNonQuery(Framework.SqlConnStr, CommandType.StoredProcedure,sql,p);
			}
			else
			{
				sql = String.Format("INSERT INTO PSP_Message(UserID, toUserID, body, CreatedDate) VALUES({0},{1}, '{2}', '{3}')",
					userID, toUserID, body, DateTime.Now);
			
				SqlHelper.ExecuteNonQuery(Framework.ConnStr, sql);

				sql = String.Format("Update PSP_User SET MsgNum= MsgNum+1 WHERE UserID={0} ",
					toUserID);

				SqlHelper.ExecuteNonQuery(Framework.ConnStr,sql);
			}
		}

		public static DataTable GetUserMessages(string userID)
		{
			string sql = "select TOP 5 PSP_Message.*, PSP_User.UserName, PSP_User.ScreenName from PSP_Message INNER JOIN PSP_User ON PSP_User.UserID = PSP_Message.UserID WHERE ToUserID = " + userID;

			if(Framework.isSQL)
			{
				return SqlHelper.ExecuteDataTable(Framework.SqlConnStr, CommandType.Text,sql);
			}
			else
			{
				return SqlHelper.ExecuteDataTable(Framework.ConnStr, sql);	
			}
		}

		public static void AddComment(string userID, string itemID, string body)
		{
			string sql = "";
			
			if(Framework.isSQL)
			{
				sql = "Item_AddComment";
				SqlParameter[] p = 
				{
					SqlHelper.MakeInParam("@userID",SqlDbType.Int,4, userID),
					SqlHelper.MakeInParam("@itemID",SqlDbType.Int,4, itemID),
					SqlHelper.MakeInParam("@body",SqlDbType.VarChar,4000, body)
				};
				
				SqlHelper.ExecuteNonQuery(Framework.SqlConnStr, CommandType.StoredProcedure,sql,p);
			}
			else
			{
				sql = String.Format("INSERT INTO PSP_Comment(UserID, itemID, body, CreatedDate) VALUES({0},{1}, '{2}', '{3}')",
					userID, itemID, body, DateTime.Now);
			
				SqlHelper.ExecuteNonQuery(Framework.ConnStr, sql);

				sql = String.Format("Update PSP_Item SET CommentNum = CommentNum +1 WHERE ItemID = " + itemID);
			
				SqlHelper.ExecuteNonQuery(Framework.ConnStr, sql);
			}
		}

		public static DataTable GetItemComments(string itemID)
		{
			string sql = "select TOP 5 PSP_Comment.*, PSP_User.Picture, PSP_User.UserName, PSP_User.ScreenName from PSP_Comment INNER JOIN PSP_User ON PSP_User.UserID = PSP_Comment.UserID WHERE ItemID = " + itemID;
			if(Framework.isSQL)
			{
				return SqlHelper.ExecuteDataTable(Framework.SqlConnStr, CommandType.Text, sql);
			}
			else
			{
				return SqlHelper.ExecuteDataTable(Framework.ConnStr, sql);	
			}
		}

		public static DataRow GetItem(string itemID)
		{	
			string sql = "";

			if(Framework.isSQL)
			{
				sql = "Item_GetItem";
				SqlParameter[] p = 
				{					
					SqlHelper.MakeInParam("@itemID",SqlDbType.Int,4, itemID)
				};
				return SqlHelper.ExecuteDataTable(Framework.SqlConnStr,CommandType.StoredProcedure , sql,p ).Rows[0];
			}
			else
			{
				sql = "Update PSP_Item Set viewnum=viewnum+1 where itemID=" + itemID;
				SqlHelper.ExecuteNonQuery(Framework.ConnStr, sql);

				sql = "Update PSP_User Set viewnum=viewnum+1 where userID = (Select UserID FROM PSP_Item where itemID=" + itemID + ")";
				SqlHelper.ExecuteNonQuery(Framework.ConnStr, sql);

				sql = "select * from PSP_Item WHERE ItemID = " + itemID;
				return SqlHelper.ExecuteDataTable(Framework.ConnStr, sql).Rows[0];	
			}
		}

		public static DataRow GetUserInfo(string userID)
		{
			string sql = "select * from PSP_User WHERE UserID = " + userID;
			if(Framework.isSQL)
			{
				return SqlHelper.ExecuteDataTable(Framework.SqlConnStr,CommandType.Text, sql).Rows[0];	
			}
			else
			{
				return SqlHelper.ExecuteDataTable(Framework.ConnStr, sql).Rows[0];	
			}
		}

		public static DataRow GetUserInfoByName(string userName)
		{
			string sql = "select * from PSP_User WHERE userName = '" + userName + "'";
			if(Framework.isSQL)
			{
				return SqlHelper.ExecuteDataTable(Framework.SqlConnStr, CommandType.Text, sql).Rows[0];	
			}
			else
			{
				return SqlHelper.ExecuteDataTable(Framework.ConnStr, sql).Rows[0];	
			}
		}

		public static int GetUserItemsCount(string userID)
		{
			string sql = "select count(*) from PSP_Item WHERE UserID = " + userID;
			if(Framework.isSQL)
			{
				return Int32.Parse(SqlHelper.ExecuteDataTable(Framework.SqlConnStr, CommandType.Text, sql).Rows[0][0].ToString());
			}
			else
			{
				return Int32.Parse(SqlHelper.ExecuteDataTable(Framework.ConnStr,sql).Rows[0][0].ToString());
			}
		}

		public static DataTable GetUserItems(string userID)
		{
			string sql = "select TOP 5 * from PSP_Item WHERE isHide=0 AND UserID = " + userID;
			if(Framework.isSQL)
			{
				return SqlHelper.ExecuteDataTable(Framework.SqlConnStr, CommandType.Text, sql);	
			}
			else
			{
				return SqlHelper.ExecuteDataTable(Framework.ConnStr, sql);	
			}
		}

		public static DataTable GetUserItems(string userID, int pageIndex, int pageSize)
		{
			string sql = "select TOP " + pageIndex*pageSize + " * from PSP_Item WHERE isHide=0 AND UserID = " + userID;
			DataTable dt = null;
			if(Framework.isSQL)
			{
				dt = SqlHelper.ExecuteDataTable(Framework.SqlConnStr, CommandType.Text, sql);
			}
			else
			{
				dt = SqlHelper.ExecuteDataTable(Framework.ConnStr, sql);
			}
			return Framework.FilterData(dt, pageSize, pageIndex);
		}

		public static DataTable GetRelatedItems(string itemID)
		{					
			string sql = String.Format("select TOP 5 * from PSP_Item WHERE isHide=0 AND ItemID <> {0} AND UserID = (SELECT UserID FROM PSP_Item WHERE ItemID = {0})", itemID);
			if(Framework.isSQL)
			{
				return SqlHelper.ExecuteDataTable(Framework.SqlConnStr, CommandType.Text, sql);
			}
			else
			{
				return SqlHelper.ExecuteDataTable(Framework.ConnStr, sql);	
			}
		}
		
		public static DataTable GetTopItems()
		{		
			return GetTopItems(5);
		}

		public static DataTable GetTopItems(int num)
		{
			string sql = "select TOP " + num + " PSP_Item.*, PSP_User.UserName  from PSP_Item INNER JOIN PSP_User ON PSP_Item.UserID = PSP_User.UserID WHERE isHide=0 ORDER BY PSP_Item.ViewNum DESC";
			if(Framework.isSQL)
			{
				return SqlHelper.ExecuteDataTable(Framework.SqlConnStr, CommandType.Text, sql);
			}
			else
			{
				return SqlHelper.ExecuteDataTable(Framework.ConnStr, sql);	
			}
		}

		public static DataTable GetRecentItems()
		{			
			return GetRecentItems(5);
		}

		public static DataTable GetRecentItems(int num)
		{
			string sql = "select TOP " + num + " PSP_Item.*, PSP_User.UserName from PSP_Item INNER JOIN PSP_User ON PSP_Item.UserID = PSP_User.UserID WHERE isHide=0 ORDER BY ItemID DESC";
			if(Framework.isSQL)
			{
				return SqlHelper.ExecuteDataTable(Framework.SqlConnStr, CommandType.Text, sql);
			}
			else
			{
				return SqlHelper.ExecuteDataTable(Framework.ConnStr, sql);