framework.cs

一阵子

			}
		}

		public static DataTable GetRecentItems(int pageIndex, int pageSize)
		{
			string sql = "select TOP " + pageIndex*pageSize + " 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";
			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 GetTopCommentedItems()
		{
			string sql = "select TOP 10 * from PSP_Item ORDER BY CommentNum DESC";
			if(Framework.isSQL)
			{
				return SqlHelper.ExecuteDataTable(Framework.SqlConnStr, CommandType.Text, sql);
			}
			else
			{
				return SqlHelper.ExecuteDataTable(Framework.ConnStr, sql);	
			}
		}

		public static DataTable GetRecentUsers()
		{
			string sql = "select TOP 10 * from PSP_User ORDER BY UserID DESC";
			if(Framework.isSQL)
			{
				return  SqlHelper.ExecuteDataTable(Framework.SqlConnStr, CommandType.Text, sql);
			}
			else
			{
				return SqlHelper.ExecuteDataTable(Framework.ConnStr, sql);	
			}
		}

		public static DataTable GetRecentUsers(int pageIndex, int pageSize)
		{
			string sql = "select TOP " + pageIndex*pageSize + " * from PSP_User ORDER BY UserID DESC";
			DataTable dt = null;
			
			if(Framework.isSQL)
			{
				dt = SqlHelper.ExecuteDataTable(Framework.SqlConnStr, CommandType.Text, sql);
			}
			else
			{
				dt = SqlHelper.ExecuteDataTable(Framework.ConnStr, sql);	
			}
			dt = Framework.FilterData(dt, pageSize, pageIndex);
			return dt;
		}

		public static DataTable GetRecentComments()
		{
			string sql = "select TOP 10 * from PSP_Comment ORDER BY CommentID DESC";
			if(Framework.isSQL)
			{
				return SqlHelper.ExecuteDataTable(Framework.SqlConnStr, CommandType.Text, sql);
			}
			else
			{
				return SqlHelper.ExecuteDataTable(Framework.ConnStr, sql);	
			}
		}

		public static DataTable GetTopUsers()
		{
			string sql = "select TOP 10 * from PSP_User ORDER BY ViewNum DESC";
			if(Framework.isSQL)
			{
				return SqlHelper.ExecuteDataTable(Framework.SqlConnStr, CommandType.Text, sql);
			}
			else
			{
				return SqlHelper.ExecuteDataTable(Framework.ConnStr, sql);	
			}
		}

	
		public static string AddUser(string email, string password, 
			string screenName, string ip, bool igooiUpdate, string userName)
		{
			string sql = "";
			string userID = "";

			if(Framework.isSQL)
			{
				sql = "User_AddUser";
				SqlParameter[] p = 
								{
									SqlHelper.MakeInParam("@email",SqlDbType.NVarChar,50, email),
									SqlHelper.MakeInParam("@password",SqlDbType.NVarChar,50, password),
									SqlHelper.MakeInParam("@screenName",SqlDbType.NVarChar,50, screenName),
									SqlHelper.MakeInParam("@IPAddress",SqlDbType.NVarChar,30, ip),
									SqlHelper.MakeInParam("@userName",SqlDbType.NVarChar,50, userName)
								};
				userID = SqlHelper.ExecuteScalar(Framework.SqlConnStr, CommandType.StoredProcedure, sql,p).ToString();
			}	
			else
			{
				sql = String.Format("INSERT INTO PSP_User(Email, [Password], ScreenName, IPAddress, userName) VALUES ('{0}', '{1}', '{2}', '{3}', '{4}')",
					email, password, screenName, ip, userName);

				SqlHelper.ExecuteNonQuery(Framework.ConnStr, sql);

				sql = "select userid from psp_user where username='" + userName + "'";

				userID = SqlHelper.ExecuteScalar(
					Framework.ConnStr,sql);	
			}
			
			return userID;
		}

		public static string AddNewItem(string userID, string title, 
			string desc, string link)
		{
			string sql = "";
			string itemID = "";

			if(Framework.isSQL)
			{
				sql = "Item_AddItem";
				SqlParameter[] p = 
								{
									SqlHelper.MakeInParam("@userID",SqlDbType.Int,4, userID),
									SqlHelper.MakeInParam("@title",SqlDbType.NVarChar,50, title),
									SqlHelper.MakeInParam("@desc",SqlDbType.NVarChar,4000, desc),
									SqlHelper.MakeInParam("@link",SqlDbType.NVarChar,50, link)									
								};
				itemID = SqlHelper.ExecuteScalar(Framework.SqlConnStr, CommandType.StoredProcedure, sql, p).ToString();
			}
			else
			{
				sql = "Update PSP_User SET ItemNum=ItemNum+1 WHERE UserID=" + userID;

				SqlHelper.ExecuteNonQuery(Framework.ConnStr,sql);

				sql = String.Format("INSERT INTO PSP_Item(userID, Itemtitle, ItemDesc, ItemLink, isHide) VALUES ({0}, '{1}', '{2}', '{3}', 0)",
					userID, title, desc, link);			
				SqlHelper.ExecuteNonQuery(Framework.ConnStr,sql);

				sql = "SELECT ItemID FROM PSP_Item WHERE ItemLink='" + link + "'";
				itemID = SqlHelper.ExecuteScalar(Framework.ConnStr, sql);

				sql = String.Format("Update PSP_Item SET ItemPic ='files/{0}.jpg', ItemLink ='files/{0}.flv' WHERE ItemID={0}", itemID);
				SqlHelper.ExecuteNonQuery(Framework.ConnStr,sql);
			}

			return itemID;
		}

		public static void UpdateUserInfo(string userID, string userName, string screenName)
		{
			string sql = "";
			
			if(Framework.isSQL)
			{
				sql = "User_UpdateUserInfo";
				SqlParameter[] p = 
								{
									SqlHelper.MakeInParam("@userID",SqlDbType.Int,4, userID),
									SqlHelper.MakeInParam("@UserName",SqlDbType.NVarChar,50, userName),
									SqlHelper.MakeInParam("@screenName",SqlDbType.NVarChar,100, screenName)									
								};
				SqlHelper.ExecuteNonQuery(Framework.SqlConnStr, CommandType.StoredProcedure, sql,p);
			}
			else
			{
				sql = String.Format("Update PSP_User SET UserName='{0}', ScreenName='{1}' WHERE UserID={2}",
					userName, screenName, userID);
				
				SqlHelper.ExecuteNonQuery(Framework.ConnStr,sql);
			}
			
		}
	
		public static void UpdateItemInfo(string itemID, string itemTitle, string itemDesc)
		{
			string sql = "";
			
			if(Framework.isSQL)
			{
				sql = "Item_UpdateItemInfo";
				SqlParameter[] p = 
								{
									SqlHelper.MakeInParam("@itemID",SqlDbType.Int,4, itemID),
									SqlHelper.MakeInParam("@itemTitle",SqlDbType.NVarChar,50, itemTitle),
									SqlHelper.MakeInParam("@itemDesc",SqlDbType.NVarChar,4000, itemDesc)									
								};
				SqlHelper.ExecuteNonQuery(Framework.SqlConnStr, CommandType.StoredProcedure, sql,p);
			}
			else
			{
				sql = String.Format("Update PSP_Item SET ItemTitle='{0}', itemDesc='{1}' WHERE itemID={2}",
					itemTitle, itemDesc, itemID);

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

		public static void DeleteItem(string itemID, string userID)
		{
			string sql = "";
			
			if(Framework.isSQL)
			{
				sql = "Item_DeleteItem";
				SqlParameter[] p = 
								{
									SqlHelper.MakeInParam("@itemID",SqlDbType.Int,4, itemID),
									SqlHelper.MakeInParam("@userID",SqlDbType.Int,4, userID)									
								};
				SqlHelper.ExecuteNonQuery(Framework.SqlConnStr, CommandType.StoredProcedure, sql,p);
			}
			else
			{
				sql = String.Format("Delete FROM PSP_Item WHERE ItemID={0} AND UserID = {1} ",
					itemID, userID);

				SqlHelper.ExecuteNonQuery(Framework.ConnStr,sql);

				sql = String.Format("Update PSP_User SET ItemNum= ItemNum-1 WHERE UserID={0} ",
					userID);

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

		//Private Methods
		private static DataTable FilterData(DataTable dt, int pageSize, int pageIndex)
		{
			DataTable dt2 = new DataTable();
			for(int i=0;i<dt.Columns.Count;i++)
			{
				dt2.Columns.Add(dt.Columns[i].ColumnName,dt.Columns[i].DataType);
			}

			int end = pageSize*pageIndex;
			if(dt.Rows.Count< end)
				end = dt.Rows.Count;
			
			for(int i=pageSize *(pageIndex-1) ; i<end; i++)
			{
				DataRow dr = dt2.NewRow();
				for(int j=0;j<dt.Columns.Count;j++)
				{	
					string col = dt.Columns[j].ColumnName;
					dr[col] = dt.Rows[i][col];
				}
				dt2.Rows.Add(dr);
			}

			return dt2;
		}
		
	}
}