?using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;

/// <summary>
/// Topic 的摘要說(shuō)明
/// </summary>
public class Topic : ITopic
{
	private static readonly string GETTOPICS = "SELECT * FROM Topics";
	private static readonly string GETCURRENTTOPIC = "SELECT * FROM Topics WHERE IsCurrent=1";
	private static readonly string GETSINGLETOPIC = "SELECT * FROM Topics WHERE TopicID=";
	private static readonly string ADDTOPIC = "INSERT INTO Topics(Name,Body,IsCurrent)VALUES";
	private static readonly string UPDATETOPIC = "UPDATE Topics SET Name=";
	private static readonly string DELETETOPIC = "DELETE Topics WHERE TopicID=";
	
	public Topic()
	{
		///
	}

	/// <summary>
	/// 獲取所有投票主題信息
	/// </summary>
	/// <returns></returns>
	public SqlDataReader GetTopics()
	{
		///創(chuàng)建鏈接
		SqlConnection myConnection = new SqlConnection(
			ConfigurationManager.ConnectionStrings["SQLCONNECTIONSTRING"].ConnectionString);
		///創(chuàng)建Command
		SqlCommand myCommand = new SqlCommand(GETTOPICS,myConnection);

		///定義DataReader
		SqlDataReader dr = null;
		try
		{
			///打開(kāi)鏈接
			myConnection.Open();
			///讀取數(shù)據(jù)
			dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
		}
		catch(SqlException ex)
		{
			///拋出異常
			throw new Exception(ex.Message,ex);
		}
		///返回DataReader
		return dr;
	}

	/// <summary>
	/// 獲取當(dāng)前投票主題
	/// </summary>
	/// <returns></returns>
	public SqlDataReader GetCurrentTopic()
	{
		///創(chuàng)建鏈接
		SqlConnection myConnection = new SqlConnection(
			ConfigurationManager.ConnectionStrings["SQLCONNECTIONSTRING"].ConnectionString);
		///創(chuàng)建Command
		SqlCommand myCommand = new SqlCommand(GETCURRENTTOPIC,myConnection);

		///定義DataReader
		SqlDataReader dr = null;
		try
		{
			///打開(kāi)鏈接
			myConnection.Open();
			///讀取數(shù)據(jù)
			dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
		}
		catch(SqlException ex)
		{
			///拋出異常
			throw new Exception(ex.Message,ex);
		}
		///返回DataReader
		return dr;
	}

	/// <summary>
	/// 獲取單個(gè)投票主題信息
	/// </summary>
	/// <param name="nTopicID"></param>
	/// <returns></returns>
	public SqlDataReader GetSingleTopic(int nTopicID)
	{
		///創(chuàng)建鏈接
		SqlConnection myConnection = new SqlConnection(
			ConfigurationManager.ConnectionStrings["SQLCONNECTIONSTRING"].ConnectionString);

		///定義SQL語(yǔ)句
		string cmdText = GETSINGLETOPIC + "'" + nTopicID.ToString() + "'";
		///創(chuàng)建Command
		SqlCommand myCommand = new SqlCommand(cmdText,myConnection);

		///定義DataReader
		SqlDataReader dr = null;
		try
		{
			///打開(kāi)鏈接
			myConnection.Open();
			///讀取數(shù)據(jù)
			dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
		}
		catch(SqlException ex)
		{
			///拋出異常
			throw new Exception(ex.Message,ex);
		}
		///返回DataReader
		return dr;
	}

	/// <summary>
	/// 添加新的投票主題
	/// </summary>
	/// <param name="sName"></param>
	/// <param name="sBody"></param>
	/// <returns></returns>
	public int AddTopic(string sName,string sBody)
	{
		///創(chuàng)建鏈接
		SqlConnection myConnection = new SqlConnection(
			ConfigurationManager.ConnectionStrings["SQLCONNECTIONSTRING"].ConnectionString);

		///定義SQL語(yǔ)句
		string cmdText = ADDTOPIC + "("
			+ "'" + sName + "',"			
			+ "'" + sBody + "',"
			+ "'0'"
			+ ")";
		///創(chuàng)建Command
		SqlCommand myCommand = new SqlCommand(cmdText,myConnection);

		///定義返回值
		int nResult = -1;

		try
		{
			///打開(kāi)鏈接
			myConnection.Open();
			///執(zhí)行SQL語(yǔ)句
			nResult = myCommand.ExecuteNonQuery();
		}
		catch(SqlException ex)
		{
			///拋出異常
			throw new Exception(ex.Message,ex);
		}
		finally
		{   ///關(guān)閉鏈接
			myConnection.Close();
		}
		///返回nResult
		return nResult;
	}

	/// <summary>
	/// 修改投票主題信息
	/// </summary>
	/// <param name="nTopicID"></param>
	/// <param name="sName"></param>
	/// <param name="sBody"></param>
	/// <returns></returns>
	public int UpdateTopic(int nTopicID,string sName,string sBody)
	{
		///創(chuàng)建鏈接
		SqlConnection myConnection = new SqlConnection(
			ConfigurationManager.ConnectionStrings["SQLCONNECTIONSTRING"].ConnectionString);

		///定義SQL語(yǔ)句
		string cmdText = UPDATETOPIC
			+ "'" + sName + "',"
			+ "Body='" + sBody + "'"
			+ " WHERE TopicID=" + "'"
			+ nTopicID.ToString() + "'";
		///創(chuàng)建Command
		SqlCommand myCommand = new SqlCommand(cmdText,myConnection);

		///定義返回值
		int nResult = -1;

		try
		{
			///打開(kāi)鏈接
			myConnection.Open();
			///執(zhí)行SQL語(yǔ)句
			nResult = myCommand.ExecuteNonQuery();
		}
		catch(SqlException ex)
		{
			///拋出異常
			throw new Exception(ex.Message,ex);
		}
		finally
		{   ///關(guān)閉鏈接
			myConnection.Close();
		}
		///返回nResult
		return nResult;
	}

	/// <summary>
	/// 設(shè)置投票主題為當(dāng)前投票主題
	/// </summary>
	/// <param name="nTopicID"></param>
	/// <returns></returns>
	public int UpdateTopicCurrent(int nTopicID)
	{
		///創(chuàng)建鏈接
		SqlConnection myConnection = new SqlConnection(
			ConfigurationManager.ConnectionStrings["SQLCONNECTIONSTRING"].ConnectionString);

		///創(chuàng)建Command
		SqlCommand myCommand = new SqlCommand("Pr_UpdateTopicCurrent",myConnection);
		///設(shè)置為執(zhí)行存儲(chǔ)過(guò)程
		myCommand.CommandType = CommandType.StoredProcedure;

		///添加存儲(chǔ)過(guò)程的參數(shù)
		SqlParameter pTopicID = new SqlParameter("@TopicID",SqlDbType.Int,4);
		pTopicID.Value = nTopicID;
		myCommand.Parameters.Add(pTopicID);

		///定義返回值
		int nResult = -1;

		try
		{
			///打開(kāi)鏈接
			myConnection.Open();
			///執(zhí)行SQL語(yǔ)句
			nResult = myCommand.ExecuteNonQuery();
		}
		catch(SqlException ex)
		{
			///拋出異常
			throw new Exception(ex.Message,ex);
		}
		finally
		{   ///關(guān)閉鏈接
			myConnection.Close();
		}
		///返回nResult
		return nResult;
	}

	/// <summary>
	/// 刪除投票主題
	/// </summary>
	/// <param name="nTopicID"></param>
	/// <returns></returns>
	public int DeleteTopic(int nTopicID)
	{
		///創(chuàng)建鏈接
		SqlConnection myConnection = new SqlConnection(
			ConfigurationManager.ConnectionStrings["SQLCONNECTIONSTRING"].ConnectionString);

		///定義SQL語(yǔ)句
		string cmdText = DELETETOPIC
			+ "'" + nTopicID.ToString() + "'";
		///創(chuàng)建Command
		SqlCommand myCommand = new SqlCommand(cmdText,myConnection);

		///定義返回值
		int nResult = -1;

		try
		{
			///打開(kāi)鏈接
			myConnection.Open();
			///執(zhí)行SQL語(yǔ)句
			nResult = myCommand.ExecuteNonQuery();
		}
		catch(SqlException ex)
		{
			///拋出異常
			throw new Exception(ex.Message,ex);
		}
		finally
		{   ///關(guān)閉鏈接
			myConnection.Close();
		}
		///返回nResult
		return nResult;
	}
}