?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>
/// Reply 的摘要說明
/// </summary>
public class Reply : IReply
{
	private static readonly string GETREPLYS = "SELECT * FROM Replies";
	private static readonly string GETSINGLEREPLY = "SELECT * FROM Replies WHERE ReplyID=";
	private static readonly string UPDATEREPLY = "UPDATE Replies SET Body=";
	private static readonly string DELETEREPLY = "DELETE Replies WHERE ReplyID=";

	public Reply()
	{
		///
	}

	/// <summary>
	/// 獲取所有留言回復
	/// </summary>
	/// <returns></returns>
	public SqlDataReader GetReplys()
	{
		///創建鏈接
		SqlConnection myConnection = new SqlConnection(
			ConfigurationManager.ConnectionStrings["SQLCONNECTIONSTRING"].ConnectionString);
		///創建Command
		SqlCommand myCommand = new SqlCommand(GETREPLYS,myConnection);

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

	/// <summary>
	/// 獲取單個留言回復
	/// </summary>
	/// <param name="nReplyID"></param>
	/// <returns></returns>
	public SqlDataReader GetSingleReply(int nReplyID)
	{
		///創建鏈接
		SqlConnection myConnection = new SqlConnection(
			ConfigurationManager.ConnectionStrings["SQLCONNECTIONSTRING"].ConnectionString);

		///定義SQL語句
		string cmdText = GETSINGLEREPLY + "'" + nReplyID.ToString() + "'";
		///創建Command
		SqlCommand myCommand = new SqlCommand(cmdText,myConnection);

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

	/// <summary>
	/// 添加新的留言回復
	/// </summary>
	/// <param name="sBody"></param>
	/// <returns></returns>
	public int AddReply(string sBody)
	{
		///創建鏈接
		SqlConnection myConnection = new SqlConnection(
			ConfigurationManager.ConnectionStrings["SQLCONNECTIONSTRING"].ConnectionString);
		///創建Command
		SqlCommand myCommand = new SqlCommand("Pr_AddReply",myConnection);
		myCommand.CommandType = CommandType.StoredProcedure;

		///添加參數
		SqlParameter pBody = new SqlParameter("@Body",SqlDbType.Text);
		pBody.Value = sBody;
		myCommand.Parameters.Add(pBody);

		SqlParameter pReplyID = new SqlParameter("@ReplyID",SqlDbType.Int,4);
		pReplyID.Direction = ParameterDirection.ReturnValue;
		myCommand.Parameters.Add(pReplyID);

		try
		{
			///打開鏈接
			myConnection.Open();
			///執行SQL語句
			myCommand.ExecuteNonQuery();
		}
		catch(SqlException ex)
		{
			///拋出異常
			throw new Exception(ex.Message,ex);
		}
		finally
		{   ///關閉鏈接
			myConnection.Close();
		}
		///返回
		return (int)myCommand.Parameters[1].Value;	
	}

	/// <summary>
	/// 修改留言回復
	/// </summary>
	/// <param name="nReplyID"></param>
	/// <param name="sBody"></param>
	/// <returns></returns>
	public int UpdateReply(int nReplyID,string sBody)
	{
		///創建鏈接
		SqlConnection myConnection = new SqlConnection(
			ConfigurationManager.ConnectionStrings["SQLCONNECTIONSTRING"].ConnectionString);

		///定義SQL語句
		string cmdText = UPDATEREPLY
			+ "'" + sBody + "'"			
			+ " WHERE ReplyID=" + "'"
			+ nReplyID.ToString() + "'";
		///創建Command
		SqlCommand myCommand = new SqlCommand(cmdText,myConnection);

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

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

	/// <summary>
	/// 刪除留言回復
	/// </summary>
	/// <param name="nReplyID"></param>
	/// <returns></returns>
	public int DeleteReply(int nReplyID)
	{
		///創建鏈接
		SqlConnection myConnection = new SqlConnection(
			ConfigurationManager.ConnectionStrings["SQLCONNECTIONSTRING"].ConnectionString);

		///定義SQL語句
		string cmdText = DELETEREPLY
			+ "'" + nReplyID.ToString() + "'";
		///創建Command
		SqlCommand myCommand = new SqlCommand(cmdText,myConnection);

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

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