/*
 * Name：Kevin Liang
 * URL ：http://www.cnblogs.com/FreeDong
 * E-mail：dongdongl@yahoo.cn
 */

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;
using System.Web.Configuration;

/// <summary>
/// DBHelper 的摘要说明
/// </summary>
public class DBHelper
{
    SqlCommand cmd = null;
    public DBHelper()
    {
        cmd = new SqlCommand();
        cmd.Connection = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["LeaveWordString"].ToString());
    }

    /// <summary>
    /// 判断数据是否存在
    /// </summary>
    /// <param name="sql">SQL语句</param>
    /// <param name="paras">参数</param>
    /// <returns>存在的行，除第一行外全部忽略</returns>
    public int ExecuteScalar(string sql, SqlParameter[] paras)
    {
        int count = 0;
        if (cmd.Connection.State == ConnectionState.Closed)
            cmd.Connection.Open();
        cmd.CommandText = sql;
        foreach (SqlParameter para in paras)
        {
            cmd.Parameters.Add(para);
        }
        try
        {
            count = Convert.ToInt32(cmd.ExecuteScalar());
        }
        catch (Exception ex)
        {
            throw;
        }
        finally
        {
            if (cmd.Connection.State == ConnectionState.Open)
                cmd.Connection.Close();
            cmd.Dispose();
            cmd.Parameters.Clear();
        }
        return count;
    }

    /// <summary>
    /// 执行非查询
    /// </summary>
    /// <param name="sql">数据操作SQL</param>
    /// <param name="paras">SQL参数</param>
    /// <returns>受影响行</returns>
    public int ExecuteNonQuery(string sql, SqlParameter[] paras)
    {
        int affectRow = 0;
        if (cmd.Connection.State == ConnectionState.Closed)
            cmd.Connection.Open();
        cmd.CommandText = sql;
        foreach (SqlParameter para in paras)
        {
            cmd.Parameters.Add(para);
        }
        try
        {
            affectRow = cmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {            
            throw;
        }
        finally
        {
            if (cmd.Connection.State == ConnectionState.Open)
                cmd.Connection.Close();
            cmd.Dispose();
            cmd.Parameters.Clear();
        }
        return affectRow;
    }

    /// <summary>
    /// 获取DataReader对象
    /// </summary>
    /// <param name="sql">SQL语句</param>
    /// <param name="paras">参数</param>
    /// <returns>DataReader对象</returns>
    public SqlDataReader GetDataReader(string sql, SqlParameter[] paras)
    {
        SqlDataReader sda = null;
        if (cmd.Connection.State == ConnectionState.Closed)
            cmd.Connection.Open();
        cmd.CommandText = sql;
        foreach (SqlParameter para in paras)
        {
            cmd.Parameters.Add(para);
        }
        try
        {
            sda = cmd.ExecuteReader();
        }
        catch (Exception ex)
        {            
            throw;
        }
        finally
        {
            //if (cmd.Connection.State == ConnectionState.Open)
            //    cmd.Connection.Close();
            cmd.Dispose();
            cmd.Parameters.Clear();
        }
        return sda;
    }

    /// <summary>
    /// 获取数据集（无参数）
    /// </summary>
    /// <param name="sql">SQL语句</param>
    /// <returns>返回数据集Table</returns>
    public DataTable GetTableByQuery(string sql)
    {
        DataTable dt = null;
        DataSet ds = new DataSet();
        if (cmd.Connection.State == ConnectionState.Closed)
            cmd.Connection.Open();
        cmd.CommandText = sql;
        using (SqlDataAdapter sda = new SqlDataAdapter())
        {
            sda.SelectCommand = cmd;
            sda.Fill(ds);
        }
        dt = ds.Tables[0];
        if (cmd.Connection.State == ConnectionState.Open)
            cmd.Connection.Close();
        cmd.Dispose();
        cmd.Parameters.Clear();
        return dt;
    }

    /// <summary>
    /// 获取数据集
    /// </summary>
    /// <param name="sql">SQL语句</param>
    /// <param name="paras">查询参数</param>
    /// <returns>返回数据集Table</returns>
    public DataTable GetTableByQuery(string sql,SqlParameter[] paras)
    {
        DataTable dt = null;
        DataSet ds = new DataSet();
        if (cmd.Connection.State == ConnectionState.Closed)
            cmd.Connection.Open();
        cmd.CommandText = sql;
        foreach (SqlParameter para in paras)
        {
            cmd.Parameters.Add(para);
        }

        using (SqlDataAdapter sda = new SqlDataAdapter())
        {
            sda.SelectCommand = cmd;
            sda.Fill(ds);
        }
        dt = ds.Tables[0];
        if (cmd.Connection.State == ConnectionState.Open)
            cmd.Connection.Close();
        cmd.Dispose();
        cmd.Parameters.Clear();
        return dt;
    }
}

/// <summary>
/// 此类仅是测试时使用
/// </summary>
public class DataAccessTest
{
    string conStr = "server=.;database=PersonalWord;uid=sa;pwd=";
    SqlCommand cmd = null;
    public DataAccessTest()
    {
        cmd.Connection = new SqlConnection(conStr);
    }
    public int ExecuteNonQuery(string sql, SqlParameter[] parms)
    {
        int affectRow = 0;
        if (cmd.Connection.State == ConnectionState.Closed)
            cmd.Connection.Open();

        cmd.CommandText = sql;
        cmd.CommandType = CommandType.Text;
        foreach (SqlParameter parm in parms)
        {
            cmd.Parameters.Add(parm);
        }
        try
        {
            affectRow = cmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            throw;
        }
        finally
        {
            if (cmd.Connection.State == ConnectionState.Open)
                cmd.Connection.Close();
            cmd.Parameters.Clear();
            cmd.Dispose();
        }
        return affectRow;
    }

    public DataSet GetDataQuery(string sql, SqlParameter[] parms)
    {
        DataSet ds = new DataSet();
        if (cmd.Connection.State == ConnectionState.Closed)
            cmd.Connection.Open();

        cmd.CommandText = sql;
        foreach (SqlParameter parm in parms)
        {
            cmd.Parameters.Add(parm);
        }
        using (SqlDataAdapter sda = new SqlDataAdapter())
        {
            sda.SelectCommand = cmd;
            sda.Fill(ds);
        }

        if (cmd.Connection.State == ConnectionState.Open)
            cmd.Connection.Close();
        cmd.Parameters.Clear();
        cmd.Dispose();

        return ds;
    }

    public DataTable GetTestTable(int id)
    {
        string sql = "select * from tableName where id = @id";
        SqlParameter[] parms = new SqlParameter[1];
        parms[0] = new SqlParameter("@id", SqlDbType.Int);
        parms[0].Value = id;
        DataTable dt = GetDataQuery(sql, parms).Tables[0];
        return dt;
    }
}