?using System;
using System.Data;
using System.Data.SqlClient;
using System.Text;
/// <summary>
/// 投票項目的操作類
/// </summary>
public class ItemOperation
{
//該源碼下載自www.51aspx.com(５１ａｓｐｘ．ｃｏｍ)
//5_1_a_s_p_x.c_o_m
    private const string PARM_ITEM_NAME = "@ItemName";
    private const string PARM_ITEM_ID = "@ItemID";
    private const string SQL_INSERT_VOTE = "INSERT INTO VoteItem  values(@ItemName,0)";
    private const string SQL_DELETE_VOTE = "DELETE FROM VoteItem WHERE ItemID=@ItemID";

    private const string SQL_UPDATE_VOTE = "UPDATE VoteItem  set itemcount=itemcount +1 WHERE ItemID=@ItemID";
    private const string SQL_SELECT_COUNT = "SELECT SUM(itemcount) FROM VoteItem";


	public ItemOperation()
	{	}
    /// <summary>
    /// 添加投票項目
    /// </summary>
    /// <param name="votename">投票項目的名稱</param>
    public void AddVote(string votename)
    {
        StringBuilder strSQL = new StringBuilder();
        SqlCommand cmd = new SqlCommand();
        // 獲取緩存的參數列表
        SqlParameter parm = new SqlParameter(PARM_ITEM_NAME, SqlDbType.NVarChar, 30);
        // 設置參數的值
        parm.Value = votename;
        //將參數添加到SQL命令中
        cmd.Parameters.Add(parm);

        // 創建連接字符串
        using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocalTransaction))
        {
            // 添加SQL語句
            strSQL.Append(SQL_INSERT_VOTE);
            conn.Open();
            //設置SqlCommand的屬性
            cmd.Connection = conn;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = strSQL.ToString();
            //執行添加語句
            cmd.ExecuteNonQuery();
            //清空參數列表
            cmd.Parameters.Clear();
        }
    }
    /// <summary>
    /// 刪除投票項目
    /// </summary>
    /// <param name="voteID">投票項目的ID</param>
    public void DelVote(int voteID)
    {
        StringBuilder strSQL = new StringBuilder();
        SqlCommand cmd = new SqlCommand();
        // 獲取緩存的參數列表
        SqlParameter parm = new SqlParameter(PARM_ITEM_ID, SqlDbType.Int);
        // 設置參數的值
        parm.Value = voteID;
        //將參數添加到SQL命令中
        cmd.Parameters.Add(parm);
        // 創建連接字符串
        using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocalTransaction))
        {
            // 添加SQL語句
            strSQL.Append(SQL_DELETE_VOTE);
            conn.Open();
            //設置SqlCommand的屬性
            cmd.Connection = conn;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = strSQL.ToString();
            //執行添加語句
            cmd.ExecuteNonQuery();
            //清空參數列表
            cmd.Parameters.Clear();
        }
    }
    /// <summary>
    /// 更新項目被投次數
    /// </summary>
    /// <param name="voteID">投票項目的ID</param>
    public void UpdateVote(int voteID)
    {
        StringBuilder strSQL = new StringBuilder();
        SqlCommand cmd = new SqlCommand();
        // 獲取緩存的參數列表
        SqlParameter parm = new SqlParameter(PARM_ITEM_ID, SqlDbType.Int);
        // 設置參數的值
        parm.Value = voteID;
        //將參數添加到SQL命令中
        cmd.Parameters.Add(parm);
        // 創建連接字符串
        using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocalTransaction))
        {
            // 添加SQL語句
            strSQL.Append(SQL_UPDATE_VOTE);
            conn.Open();
            //設置SqlCommand的屬性
            cmd.Connection = conn;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = strSQL.ToString();
            //執行添加語句
            cmd.ExecuteNonQuery();
            //清空參數列表
            cmd.Parameters.Clear();
        }
    }
    /// <summary>
    /// 獲取總投票數量
    /// </summary>
    /// <returns>總投票數</returns>
    public int GetVoteCount()
    {
        //調用SqlHelper訪問組件的方法返回第一行第一列的值
        object obj = SqlHelper.ExecuteScalar(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, SQL_SELECT_COUNT,null);
        return int.Parse(obj.ToString());
    }
}