using System;
using System.Data;
using System.Data.OleDb;

/// <summary>
///Access數據庫操作類
///創建時間：2012年8月17日
/// </summary>
public class AccessHelper
{
    private OleDbConnection conn = null;
    private OleDbCommand cmd = null;
    private OleDbDataReader sdr = null;


    public AccessHelper()
    {
        //  string connStr = WebConfigurationManager.ConnectionStrings["connStr"].ToString();
        string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|db.mdb";
        conn = new OleDbConnection(connStr);
    }


    /// <summary>創建Command對象
    /// 
    /// </summary>
    /// <param name="sql">SQL語句</param>
    public void CreateCommand(string sql)
    {
        conn.Open();
        cmd = new OleDbCommand(sql, conn);
    }


    /// <summary>添加參數
    /// 
    /// </summary>
    /// <param name="paramName">參數名稱</param>
    /// <param name="value">值</param>
    public void AddParameter(string paramName, object value)
    {
        cmd.Parameters.Add(new OleDbParameter(paramName, value));


    }


    /// <summary>執行不帶參數的增刪改SQL語句
    ///  
    /// </summary>
    /// <param name="cmdText">增刪改SQL語句</param>
    /// <param name="ct">命令類型</param>
    /// <returns></returns>
    public bool ExecuteNonQuery()
    {
        int res;
        try
        {
            res = cmd.ExecuteNonQuery();
            if (res > 0)
            {
                return true;
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            if (conn.State == ConnectionState.Open)
            {
                conn.Close();
            }
        }
        return false;
    }


    /// <summary>執行查詢SQL語句
    ///  
    /// </summary>
    /// <param name="cmdText">查詢SQL語句</param>
    /// <returns></returns>
    public DataTable ExecuteQuery()
    {
        DataTable dt = new DataTable();
        using (sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
        {
            dt.Load(sdr);
        }
        return dt;
    }


    /// <summary>分頁
    /// 
    /// </summary>
    /// <param name="tblName">表名</param>
    /// <param name="fldName">字段名</param>
    /// <param name="OrderfldName">排序字段名</param>
    /// <param name="OrderType">排序方式：asc或者desc</param>
    /// <param name="strWhere">條件，不用加where</param>
    /// <param name="PageSize">頁大小</param>
    /// <param name="PageIndex">頁索引</param>
    /// <returns></returns>
    public DataTable FengYe(string tblName, string fldName, string OrderfldName, string OrderType, string strWhere, int PageSize, int PageIndex)
    {
        DataTable dt = new DataTable();
        string strSQL = ""; // 主語句 
        string strTmp = ""; // 臨時變量 
        string strOrder = ""; // 排序類型


        if (OrderType == "desc")
        {
            // 降序
            strTmp = "<(select min";
            strOrder = " order by " + OrderfldName + " desc";
        }
        else
        {
            // 升序
            strTmp = ">(select max";
            strOrder = " order by " + OrderfldName + " asc";
        }


        #region 第一頁
        if (PageIndex == 1)
        {
            strTmp = string.IsNullOrEmpty(strWhere) ? "" : " where " + strWhere;
            strSQL = "select top " + PageSize + " " + fldName + " from " + tblName + strTmp + " " + strOrder;
            CreateCommand(strSQL);
            dt = ExecuteQuery();
            return dt;
        }
        #endregion


        #region 不是第一頁
        if (string.IsNullOrEmpty(strWhere))
        {
            // 條件為空
            strSQL = string.Format("select top {0} {1} from {2} where {3}{4}({5}) from (select top {6} {7} from {8} {9}) as tblTmp) {10}", PageSize, fldName, tblName, OrderfldName, strTmp, OrderfldName, (PageIndex - 1) * PageSize, OrderfldName, tblName, strOrder, strOrder);
            CreateCommand(strSQL);
            dt = ExecuteQuery();
        }
        else
        {
            // 條件不為空
            strSQL =string.Format( "select top {0} {1} from {2} where  {3}{4}({5}) from (select top {6} {7} from {8} where {9} {10}) as tblTmp) and {11} {12}",PageSize,fldName,tblName,OrderfldName,strTmp,OrderfldName,(PageIndex-1)*PageSize,OrderfldName,tblName,strWhere,strOrder,strWhere,strOrder);
            CreateCommand(strSQL);
            dt = ExecuteQuery();
        }
        #endregion


        return dt;
    }


    /// <summary>返回查詢SQL語句查詢出的結果的第一行第一列的值
    /// 
    /// </summary>
    /// <returns></returns>
    public string ExecuteScalar()
    {
        string res = "";
        try
        {
            object obj = cmd.ExecuteScalar();
            if (obj != null)
            {
                res = obj.ToString();
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            if (conn.State == ConnectionState.Open)
            {
                conn.Close();
            }
        }
        return res;
    }


}