using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Configuration;
using System.Data.SqlClient;
using System.Collections;

namespace Common.Dba
{
    public class DbAccess
    {

        private string _ConnectionString = "";
        public string ConnectionString
        {
            get {
                return "Data Source=ORACLE9I;User Id=wangdetian;Password=wangdetian;";
                //_ConnectionString = string.IsNullOrEmpty(_ConnectionString) ? ("") : (_ConnectionString);
                //return _ConnectionString.Length > 0 ? (_ConnectionString) : (Convert.ToString(ConfigurationManager.AppSettings["datasource"] ?? ""));
            }
        }
       public DbConnection conn = null;
       protected DbCommand cmd = null;
  
        /// <summary>
        /// 獲取數據根據Oracle語句
        /// </summary>
        /// <param name="Oracle"></param>
        /// <returns></returns>
        public virtual DataTable GetTable(string sql)
        {
         
            return  new DataTable();
        }
        /// <summary>
        /// 獲取數據根據sql語句
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public virtual DataSet GetDataSet(string sql)
        {
            return new DataSet();
        }
        /// <summary>
        /// 獲取數據根據sql語句
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public virtual DataSet GetDataSet(string sql, DbParameter[] pas)
        {
            return new DataSet();
        }
        /// <summary>
        /// 獲取數據根據sql語句 帶參數 的
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pas"></param>
        /// <returns></returns>
        public virtual DataTable GetTable(string sql, params DbParameter[] pas)
        {
           return  new DataTable();
        }
        /// <summary>
        /// 獲取數據根據sql語句 帶參數 的
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pas"></param>
        /// <returns></returns>
        public virtual DataTable GetProcTable(string procname, params DbParameter[] pas)
        {      
            return  new DataTable();
        }
        /// <summary>
        /// 獲取數據根據sql語句 帶參數 的
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pas"></param>
        /// <returns></returns>
        public virtual DataTable GetProcCursorTable(string procname, params DbParameter[] pas)
        {
      
            return  new DataTable();
        }
        /// <summary>
        /// 獲取數據根據sql語句 帶參數 的
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pas"></param>
        /// <returns></returns>
        public virtual int GetProcState(string procname, params DbParameter[] pas)
        {
            return 0;
        }
        /// <summary>
        /// 獲取數據根據sql語句 帶參數 的
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pas"></param>
        /// <returns></returns>
        public virtual int GetProcStateNo(string procname, params DbParameter[] pas)
        {
            return 0;
        }
        /// <summary>
        /// 存儲過程返回值的
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pas"></param>
        /// <returns></returns>
        public virtual string GetProcStateReturnValue(string procname, params DbParameter[] pas)
        {   
                return "";        
        }
        /// <summary>
        /// 根據sql語句返回跟新狀態
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public virtual bool GetState(string sql)
        {
            return false;

        }
        /// <summary>
        /// 根據sql語句返回跟新狀態帶參數的
        /// </summary>
        /// <param name="sql">sql語句</param>
        /// <param name="pas">參數的集合</param>
        /// <returns></returns>
        public virtual bool GetState(string sql, params DbParameter[] pas)
        {
            return false;

        }
        /// <summary>
        /// 根據sql語句返回第一個單元格的數據
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public virtual string GetOne(string sql)
        {
        
            return "";
        }
        /// <summary>
        ///  根據sql語句返回第一個單元格的數據帶參數的
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pas"></param>
        /// <returns></returns>
        public virtual string GetOne(string sql, params DbParameter[] pas)
        {
            return "";
        }
        /// <summary>
        /// 返回數據的DataReader
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public virtual DbDataReader GetDataReader(string sql)
        {
           DbDataReader dr = null;
          
            return dr;
        }
        /// <summary>
        /// 返回數據的DataReader帶參數的
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pas"></param>
        /// <returns></returns>
        public virtual DbDataReader GetDataReader(string sql, params DbParameter[] pas)
        {
            DbDataReader dr = null;
           
            return dr;
        }
        /// <summary>
        /// 事務處理函數
        /// </summary>
        /// <param name="al"></param>
        /// <returns></returns>
        public virtual bool GetTranState(ArrayList al)
        {
            return false;

        }
        /// <summary>
        /// 事務處理函數
        /// </summary>
        /// <param name="al"></param>
        /// <returns></returns>
        public virtual bool GetTranStateParameter(ArrayList al)
        {
            return false;

        }
        /// <summary>
        /// 分頁函數
        /// </summary>
        /// <param name="pagesize"></param>
        /// <param name="columns"></param>
        /// <param name="tablename"></param>
        /// <param name="pid"></param>
        /// <param name="order"></param>
        /// <param name="current"></param>
        /// <returns></returns>
        public virtual DataTable GetPageData(int current, int pagesize, string columns, string tablename, string pid, string where, string order)
        {
            current = current - 1 >= 0 ? (current - 1) : (0);
            string sql = string.Format("select top {0} {1} from {2} where 1=1 and {3} not in(select top {4}{3} from {2} where 1=1{5}  order by {6}){5} order by {6}", pagesize, columns, tablename, pid, current * pagesize, where, order);
            return GetTable(sql);

        }
        /// <summary>
        /// 分頁存儲過程的調用
        /// </summary>
        /// <param name="current"></param>
        /// <param name="pagesize"></param>
        /// <param name="columns"></param>
        /// <param name="tablename"></param>
        /// <param name="pid"></param>
        /// <param name="where"></param>
        /// <param name="order"></param>
        /// <returns></returns>
        public virtual DataTable GetProcPageData(int current, int pagesize, string columns, string tablename, string pid, string where, string order, string ordertype)
        {
            return new DataTable();
        }
        /// <summary>
        /// 分頁存儲過程的調用
        /// </summary>
        /// <param name="current"></param>
        /// <param name="pagesize"></param>
        /// <param name="columns"></param>
        /// <param name="tablename"></param>
        /// <param name="pid"></param>
        /// <param name="where"></param>
        /// <param name="order"></param>
        /// <returns></returns>
        public virtual DataTable GetProcData(int current, int pagesize, string columns, string tablename, string pid, string where, string order, string resultCount, string distinct)
        {
            return new DataTable();

        }
        /// <summary>
        /// 分頁存儲過程的調用
        /// </summary>
        /// <param name="current"></param>
        /// <param name="pagesize"></param>
        /// <param name="columns"></param>
        /// <param name="tablename"></param>
        /// <param name="pid"></param>
        /// <param name="where"></param>
        /// <param name="order"></param>
        /// <returns></returns>
        public virtual DataTable GetProcAdminData(int current, int pagesize, string columns, string tablename, string pid, string where, string order, string resultCount, string distinct)
        {
            return new DataTable();

        }
        public virtual DbParameter[] MakeParameters(params string[] str)
        {
            return null;
        }
        /// <summary>
        /// 打開連接
        /// </summary>
        public void OpenConn()
        {
            if (conn.State != ConnectionState.Open)
            {
                try
                {
                    conn.Open();
                }
                catch (Exception ex)
                {

                    throw ex;
                }
            }
        }
        /// <summary>
        /// 關閉連接
        /// </summary>
        public void CloseConn()
        {
            if (conn.State != ConnectionState.Closed)
            {
                try
                {
                    conn.Close();

                }
                catch (Exception ex)
                {

                    throw ex;
                }
            }
        }
        /// <summary>
        /// 銷毀連接
        /// </summary>
        public void DisposeConn()
        {
            try
            {
                if (conn.State != ConnectionState.Closed)
                {
                    try
                    {
                        conn.Close();

                    }
                    catch (Exception ex)
                    {

                        throw ex;
                    }
                }
            }
            catch
            {

            }
        }
    }
}