votingutility.cs

一个ASP.NET下的中文内容管理和社区系统

				myParam.Value=DBNull.Value;
			else
				myParam.Value=StartDate;
			myCommand.Parameters.Add(myParam);

			myParam = new SqlParameter("@InEndDate",SqlDbType.DateTime);
			if (EndDate.Equals(DateTime.MaxValue))
				myParam.Value=DBNull.Value;
			else
				myParam.Value=EndDate;
			myCommand.Parameters.Add(myParam);
			
			myParam = new SqlParameter("@InIsGlobal",SqlDbType.Bit);
			myParam.Value=IsGlobal?1:0;
			myCommand.Parameters.Add(myParam);

			myParam = new SqlParameter("@InDisplayResultsToPublic",SqlDbType.Bit);
			myParam.Value=DisplayResultsToPublic?1:0;
			myCommand.Parameters.Add(myParam);

			myParam = new SqlParameter("@InIsActive",SqlDbType.Bit);
			myParam.Value=IsActive?1:0;
			myCommand.Parameters.Add(myParam);

			myConnection.Open();
			myCommand.ExecuteNonQuery();
			myConnection.Close();

			// Update Cache
			UpdatePollCache(Poll_ID);
		}

		/// <summary>
		/// Create's a new PollChoice.
		/// </summary>
		public static void CreatePollChoice(int Poll_ID, string Choice) {
			SqlConnection myConnection = new SqlConnection(CommunityGlobals.ConnectionString);
			SqlCommand myCommand = new SqlCommand("Community_VotingPollChoicesInsert",myConnection);
			myCommand.CommandType=CommandType.StoredProcedure;
			SqlParameter myParam;

			myParam = new SqlParameter("@InPoll_ID",SqlDbType.Int);
			myParam.Value=Poll_ID;
			myCommand.Parameters.Add(myParam);
			
			myParam = new SqlParameter("@InChoice",SqlDbType.NVarChar,100);
			myParam.Value=Choice;
			myCommand.Parameters.Add(myParam);

			myConnection.Open();
			myCommand.ExecuteNonQuery();
			myConnection.Close();

			// Update Cache
			UpdatePollChoiceCache(Poll_ID);
		}

		// For voting consistancy this operation is not allowed.
		// This design decision is still debateable so the code is available if
		// needed.
		[Obsolete("Please delete Choice and create a new one.",true)]
		public static void UpdatePollChoice(int PollChoice_ID, string Choice) {
			SqlConnection myConnection = new SqlConnection(CommunityGlobals.ConnectionString);
			SqlCommand myCommand = new SqlCommand("Community_VotingPollChoicesUpdate",myConnection);
			myCommand.CommandType=CommandType.StoredProcedure;
			SqlParameter myParam;

			myParam = new SqlParameter("@InPollChoice_ID",SqlDbType.Int);
			myParam.Value=PollChoice_ID;
			myCommand.Parameters.Add(myParam);
			
			myParam = new SqlParameter("@InChoice",SqlDbType.NVarChar,100);
			myParam.Value=Choice;
			myCommand.Parameters.Add(myParam);

			myParam = new SqlParameter("@OutPoll_ID",SqlDbType.Int);
			myParam.Direction=ParameterDirection.Output;
			myCommand.Parameters.Add(myParam);

			myConnection.Open();
			myCommand.ExecuteNonQuery();
			myConnection.Close();

			// Update Cache
			UpdatePollChoiceCache((int)myCommand.Parameters["@OutPoll_ID"].Value);
		}

// 		/// <summary>
// 		/// Updates the Roles for a give Poll based on a comma delimited RoleList
// 		/// </summary>
// 		public static void UpdatePollRoles(int Poll_ID, string[] ActiveRoleList) {
// 			SqlConnection myConnection = new SqlConnection(CommunityGlobals.ConnectionString);
// 			SqlCommand myCommand = new SqlCommand("Community_VotingPollRolesUpdate",myConnection);
// 			myCommand.CommandType=CommandType.StoredProcedure;
// 			SqlParameter myParam;
// 
// 			myParam = new SqlParameter("@InPoll_ID",SqlDbType.Int);
// 			myParam.Value=Poll_ID;
// 			myCommand.Parameters.Add(myParam);
// 
// 			// Parse ActiveRoleList
// 			StringBuilder parsedActiveRoleList = new StringBuilder();
// 			foreach (string role in ActiveRoleList) {
// 				if (parsedActiveRoleList.Length>0)
// 					parsedActiveRoleList.Append(',');
// 				parsedActiveRoleList.Append("'");
// 				parsedActiveRoleList.Append(role);
// 				parsedActiveRoleList.Append("'");
// 			}
// 
// 			myParam = new SqlParameter("@InActiveRoleList",SqlDbType.NVarChar,4000);
// 			myParam.Value=parsedActiveRoleList.ToString();
// 			myCommand.Parameters.Add(myParam);
// 
// 			myConnection.Open();
// 			myCommand.ExecuteNonQuery();
// 			myConnection.Close();
// 		}

		/// <summary>
		/// Updates a Poll's Sections by taking a list of comma seperated Section_ID's
		/// </summary>
		public static void UpdatePollRoles(int Poll_ID, string[] ActiveRoleList) {
			SqlConnection con = new SqlConnection(CommunityGlobals.ConnectionString);
            con.Open();

            // first, delete all the roles associated with Poll_ID
            SqlCommand cmdDelete = new SqlCommand("Community_VotingPollRolesDeleteAll", con);
            cmdDelete.CommandType = CommandType.StoredProcedure;
            cmdDelete.Parameters.Add("@InPoll_ID", Poll_ID);
            cmdDelete.ExecuteNonQuery();
            
            // next, add each selected role
            SqlCommand cmdUpdate = new SqlCommand("Community_VotingPollRolesInsert", con);
            cmdUpdate.CommandType = CommandType.StoredProcedure;
            cmdUpdate.Parameters.Add("@InPoll_ID", Poll_ID);
            cmdUpdate.Parameters.Add("@InRoleName", SqlDbType.NVarChar);


            foreach (string roleName in ActiveRoleList) { 
                cmdUpdate.Parameters["@InRoleName"].Value = roleName;
                cmdUpdate.ExecuteNonQuery();
            }

            con.Close();            
		}





		/// <summary>
		/// Updates a Poll's Sections by taking a list of comma seperated Section_ID's
		/// </summary>
		public static void UpdatePollSections(int Poll_ID, string[] ActiveSectionList) {
			SqlConnection con = new SqlConnection(CommunityGlobals.ConnectionString);
            con.Open();

            // first, delete all the sections associated with Poll_ID
            SqlCommand cmdDelete = new SqlCommand("Community_VotingPollSectionsDeleteAll", con);
            cmdDelete.CommandType = CommandType.StoredProcedure;
            cmdDelete.Parameters.Add("@InPoll_ID", Poll_ID);
            cmdDelete.ExecuteNonQuery();
            
            // next, add each selected section
            SqlCommand cmdUpdate = new SqlCommand("Community_VotingPollSectionsUpdate", con);
            cmdUpdate.CommandType = CommandType.StoredProcedure;
            cmdUpdate.Parameters.Add("@InPoll_ID", Poll_ID);
            cmdUpdate.Parameters.Add("@InSection_Name", SqlDbType.NVarChar);


            foreach (string sectionName in ActiveSectionList) { 
                cmdUpdate.Parameters["@InSection_Name"].Value = sectionName;
                cmdUpdate.ExecuteNonQuery();
            }

            con.Close();            
		}

		/// <summary>
		/// Deletes ALL voting information regarding a specific poll.
		/// </summary>
		/// <remarks>
		/// This operation performs a cascade delete on any related objects.  Use
		/// this method with care.
		/// </remarks>
		public static void DeletePoll(int Poll_ID) {
			SqlConnection myConnection = new SqlConnection(CommunityGlobals.ConnectionString);
			SqlCommand myCommand = new SqlCommand("Community_VotingPollsDelete",myConnection);
			myCommand.CommandType=CommandType.StoredProcedure;
			SqlParameter myParam;

			myParam = new SqlParameter("@InPoll_ID",SqlDbType.Int);
			myParam.Value=Poll_ID;
			myCommand.Parameters.Add(myParam);

			myConnection.Open();
			myCommand.ExecuteNonQuery();
			myConnection.Close();
		}

		/// <summary>
		/// Removes a PollChoice for a given Poll.
		/// </summary>
		public static void DeletePollChoice(int PollChoice_ID) {
			SqlConnection myConnection = new SqlConnection(CommunityGlobals.ConnectionString);
			SqlCommand myCommand = new SqlCommand("Community_VotingPollChoicesDelete",myConnection);
			myCommand.CommandType=CommandType.StoredProcedure;
			SqlParameter myParam;

			myParam = new SqlParameter("@InPollChoice_ID",SqlDbType.Int);
			myParam.Value=PollChoice_ID;
			myCommand.Parameters.Add(myParam);
			
			myParam = new SqlParameter("@OutPoll_ID",SqlDbType.Int);
			myParam.Direction=ParameterDirection.Output;
			myCommand.Parameters.Add(myParam);

			myConnection.Open();
			myCommand.ExecuteNonQuery();
			myConnection.Close();

			// Update Cache
			UpdatePollChoiceCache((int)myCommand.Parameters["@OutPoll_ID"].Value);
		}

		/// <summary>
		/// Cast's a vote for a given User and Poll.
		/// </summary>
		public static void CreatePollResult(string Username, int PollChoice_ID) {
			SqlConnection myConnection = new SqlConnection(CommunityGlobals.ConnectionString);
			SqlCommand myCommand = new SqlCommand("Community_VotingPollResultsInsert",myConnection);
			myCommand.CommandType=CommandType.StoredProcedure;
			SqlParameter myParam;

			myParam = new SqlParameter("@InUsername",SqlDbType.NVarChar,50);
			if (Username.Length==0)
				myParam.Value=DBNull.Value;
			else
				myParam.Value=Username;
			myCommand.Parameters.Add(myParam);

			myParam = new SqlParameter("@InPollChoice_ID",SqlDbType.Int);
			myParam.Value=PollChoice_ID;
			myCommand.Parameters.Add(myParam);

			myConnection.Open();
			myCommand.ExecuteNonQuery();
			myConnection.Close();

		}

		/*
		 * Poll Caching Functions - Note: Cache will not update accross a Web Farm.
		 */ 
		private static DataRow UpdatePollCache(int Poll_ID) {
			Cache myCache = HttpContext.Current.Cache;
			string cacheKey=GetPollCacheKey(Poll_ID);

			if (myCache[cacheKey]!=null)
				myCache.Remove(cacheKey);

			SqlConnection myConnection = new SqlConnection(CommunityGlobals.ConnectionString);
			SqlDataAdapter myAdapter = new SqlDataAdapter("Community_VotingGetpoll",myConnection);
			myAdapter.SelectCommand.CommandType=CommandType.StoredProcedure;
			SqlParameter myParam;

			myParam = new SqlParameter("@InPoll_ID",SqlDbType.Int);
			myParam.Value=Poll_ID;
			myAdapter.SelectCommand.Parameters.Add(myParam);

			DataSet ds = new DataSet();
			myAdapter.Fill(ds);
			DataRow dr = ds.Tables[0].Rows[0];
			
			if (dr!=null)
				myCache.Insert(cacheKey,dr,null,DateTime.Now.AddMinutes(DEFAULT_CACHE_TIMEOUT),Cache.NoSlidingExpiration);

			return dr;
		}

		private static DataRow GetPollCache(int Poll_ID) {
			Cache myCache = HttpContext.Current.Cache;
			DataRow dr = myCache[GetPollCacheKey(Poll_ID)] as DataRow;
			if (dr==null)
				return UpdatePollCache(Poll_ID);
			else
				return dr;
		}

		private static string GetPollCacheKey(int Poll_ID) {
			return "VotingUtility_PollCache_" + Poll_ID.ToString();
		}

		private static DataTable UpdatePollChoiceCache(int Poll_ID) {
			Cache myCache = HttpContext.Current.Cache;
			string cacheKey=GetPollChoiceCacheKey(Poll_ID);
			
			if (myCache[cacheKey]!=null)
				myCache.Remove(cacheKey);

			SqlConnection myConnection = new SqlConnection(CommunityGlobals.ConnectionString);
			SqlDataAdapter myDataAdapter = new SqlDataAdapter("Community_VotingGetPollChoices",myConnection);
			myDataAdapter.SelectCommand.CommandType=CommandType.StoredProcedure;
			SqlParameter myParam;

			myParam = new SqlParameter("@InPoll_ID",SqlDbType.Int);
			myParam.Value=Poll_ID;
			myDataAdapter.SelectCommand.Parameters.Add(myParam);

			DataSet ds = new DataSet();
			myDataAdapter.Fill(ds);
			
			if (ds.Tables[0].Rows.Count>0)
				myCache.Insert(cacheKey,ds.Tables[0],null,DateTime.Now.AddMinutes(DEFAULT_CACHE_TIMEOUT),Cache.NoSlidingExpiration);
			
			return ds.Tables[0];
		}

		private static DataTable GetPollChoiceCache(int Poll_ID) {
			Cache myCache = HttpContext.Current.Cache;
			DataTable dt = myCache[GetPollChoiceCacheKey(Poll_ID)] as DataTable;
			if (dt==null)
				return UpdatePollChoiceCache(Poll_ID);
			else
				return dt;
		}
		
		private static string GetPollChoiceCacheKey(int Poll_ID) {
			return "VotingUtility_PollChoiceCache_" + Poll_ID.ToString();
		}
		
	}
}