using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Collections;
using System.Data.SQLite;
using System.Data.Common;
namespace Common.Dba
{
    class DbSqlLiteData:DbAccess
    {
         new SQLiteCommand cmd = null;
        public DbSqlLiteData()
        {
            //
            // TODO: 在此處添加構(gòu)造函數(shù)邏輯
            //
            conn = new SQLiteConnection();
            conn.ConnectionString = ConnectionString;//Convert.ToString(ConfigurationManager.AppSettings["datasource"]); //Convert.ToString(System.Configuration.ConfigurationSettings.AppSettings["datasource"]);
            //conn.ConnectionString = "initial catalog=idyan_new;data source=.;user id=bt;password=btbtbtbt;Connect Timeout=5000";
            cmd = new SQLiteCommand();
            cmd.Connection = conn as SQLiteConnection;
            cmd.CommandTimeout = 0;

        }
        public DbSqlLiteData(string constr)
        {

            //
            // TODO: 在此處添加構(gòu)造函數(shù)邏輯
            //
            conn = new SQLiteConnection();
            //conn.ConnectionString = "initial catalog=pubs;data source=.;user id=sa;password=";
            //conn.ConnectionString = Convert.ToString(System.Configuration.ConfigurationSettings.AppSettings["datasource"]);
            conn.ConnectionString = constr;// "initial catalog=idyan_new;data source=.;user id=bt;password=btbtbtbt";
            cmd = new SQLiteCommand();
            cmd.Connection = conn as SQLiteConnection;
            cmd.CommandTimeout = 0;

        }
        /// <summary>
        /// 獲取數(shù)據(jù)根據(jù)Sql語(yǔ)句
        /// </summary>
        /// <param name="Sql"></param>
        /// <returns></returns>
        public override DataTable GetTable(string sql)
        {
            DataSet ds = new DataSet();

            try
            {
                cmd.CommandText = sql;
                cmd.CommandType = CommandType.Text;
                SQLiteDataAdapter da = new SQLiteDataAdapter();
                da.SelectCommand = cmd;
                da.Fill(ds);
            }
            catch (Exception ex)
            {

             
                return null;

            }
            return ds.Tables[0] ?? new DataTable();
        }
        /// <summary>
        /// 獲取數(shù)據(jù)根據(jù)sql語(yǔ)句
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public override DataSet GetDataSet(string sql)
        {
            DataSet ds = new DataSet();

            try
            {
                cmd.CommandText = sql;
                cmd.CommandType = CommandType.Text;
                SQLiteDataAdapter da = new SQLiteDataAdapter();
                da.SelectCommand = cmd;

                da.Fill(ds);
            }
            catch (Exception ex)
            {

              
                return null;

            }
            return ds;
        }
        /// <summary>
        /// 獲取數(shù)據(jù)根據(jù)sql語(yǔ)句
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public override DataSet GetDataSet(string sql, DbParameter[] pas)
        {
            DataSet ds = new DataSet();

            try
            {
                cmd.Parameters.Clear();
                cmd.CommandText = sql;
                foreach (SQLiteParameter pa in pas)
                {
                    cmd.Parameters.Add(pa);
                }
                cmd.CommandType = CommandType.Text;
                SQLiteDataAdapter da = new SQLiteDataAdapter();
                da.SelectCommand = cmd;

                da.Fill(ds);
            }
            catch (Exception ex)
            {

             
                return null;

            }
            return ds;
        }
        /// <summary>
        /// 獲取數(shù)據(jù)根據(jù)sql語(yǔ)句 帶參數(shù) 的
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pas"></param>
        /// <returns></returns>
        public override DataTable GetTable(string sql, params DbParameter[] pas)
        {
            DataSet ds = new DataSet();
            try
            {
                cmd.CommandText = sql;
                cmd.CommandType = CommandType.Text;
                SQLiteDataAdapter da = new SQLiteDataAdapter();
                da.SelectCommand = cmd;
                cmd.Parameters.Clear();

                foreach (SQLiteParameter temppa in pas)
                {
                    cmd.Parameters.Add(temppa);
                }

                da.Fill(ds);
            }
            catch (Exception ex)
            {

                return null;
            }
            return ds.Tables[0] ?? new DataTable();
        }
        /// <summary>
        /// 獲取數(shù)據(jù)根據(jù)sql語(yǔ)句 帶參數(shù) 的
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pas"></param>
        /// <returns></returns>
        public override DataTable GetProcTable(string procname, params DbParameter[] pas)
        {
            DataSet ds = new DataSet();
            try
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = procname;
                //cmd.CommandText = sql;
                SQLiteDataAdapter da = new SQLiteDataAdapter();
                da.SelectCommand = cmd;
                cmd.Parameters.Clear();

                foreach (SQLiteParameter temppa in pas)
                {
                    cmd.Parameters.Add(temppa);
                }

                da.Fill(ds);
            }
            catch (Exception ex)
            {

                return null;
            }
            return ds.Tables[0] ?? new DataTable();
        }
        /// <summary>
        /// 獲取數(shù)據(jù)根據(jù)sql語(yǔ)句 帶參數(shù) 的
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pas"></param>
        /// <returns></returns>
        public override DataTable GetProcCursorTable(string procname, params DbParameter[] pas)
        {
            DataSet ds = new DataSet();
            try
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = procname;
                //cmd.CommandText = sql;
                SQLiteDataAdapter da = new SQLiteDataAdapter();
                da.SelectCommand = cmd;
                cmd.Parameters.Clear();

                foreach (SQLiteParameter temppa in pas)
                {
                    cmd.Parameters.Add(temppa);
                }

                da.Fill(ds);
            }
            catch (Exception ex)
            {

                return null;
            }
            return ds.Tables[1] ?? new DataTable();
        }
        /// <summary>
        /// 獲取數(shù)據(jù)根據(jù)sql語(yǔ)句 帶參數(shù) 的
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pas"></param>
        /// <returns></returns>
        public override int GetProcState(string procname, params DbParameter[] pas)
        {
            int state = 0;
            try
            {
                OpenConn();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = procname;
                cmd.Parameters.Clear();
                foreach (SQLiteParameter temppa in pas)
                {
                    cmd.Parameters.Add(temppa);
                }
                cmd.ExecuteNonQuery();
                CloseConn();
                state = Convert.ToInt32(pas[pas.Length - 1].Value);

            }
            catch
            {
                return 0;
            }
            return state;
        }
        /// <summary>
        /// 獲取數(shù)據(jù)根據(jù)sql語(yǔ)句 帶參數(shù) 的
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pas"></param>
        /// <returns></returns>
        public override int GetProcStateNo(string procname, params DbParameter[] pas)
        {
            int state = 0;
            try
            {
                OpenConn();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = procname;
                cmd.Parameters.Clear();
                foreach (SQLiteParameter temppa in pas)
                {
                    cmd.Parameters.Add(temppa);
                }
                cmd.ExecuteNonQuery();
                CloseConn();
                state = 1;

                // state = Convert.ToInt32(pas[pas.Length - 1].Value);

            }
            catch
            {

                return 0;
            }
            return state;
        }
        /// <summary>
        /// 存儲(chǔ)過(guò)程返回值的
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pas"></param>
        /// <returns></returns>
        public override string GetProcStateReturnValue(string procname, params DbParameter[] pas)
        {
            string state = "";
            try
            {
                OpenConn();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = procname;
                cmd.Parameters.Clear();
                foreach (SQLiteParameter temppa in pas)
                {
                    cmd.Parameters.Add(temppa);
                }
                cmd.Parameters.AddWithValue("@Return_Value", "").Direction = ParameterDirection.ReturnValue;
                cmd.ExecuteNonQuery();
                state = Convert.ToString(cmd.Parameters["@Return_Value"].Value);
                state = state == null ? ("") : (state);
                CloseConn();

                // state = Convert.ToInt32(pas[pas.Length - 1].Value);

            }
            catch
            {

                return "";
            }
            return state;
        }
        /// <summary>
        /// 根據(jù)sql語(yǔ)句返回跟新?tīng)顟B(tài)
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public override bool GetState(string sql)
        {
            bool succ = false;
            try
            {
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = sql;
                OpenConn();
                succ = cmd.ExecuteNonQuery() > 0 ? (true) : (false);
                CloseConn();
            }
            catch (Exception ex)
            {

                return false;
            }
            return succ;

        }
        /// <summary>
        /// 根據(jù)sql語(yǔ)句返回跟新?tīng)顟B(tài)帶參數(shù)的
        /// </summary>
        /// <param name="sql">sql語(yǔ)句</param>
        /// <param name="pas">參數(shù)的集合</param>
        /// <returns></returns>
        public override bool GetState(string sql, params DbParameter[] pas)
        {
            bool succ = false;
            try
            {
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = sql;
                cmd.Parameters.Clear();

                foreach (SQLiteParameter temppa in pas)
                {
                    cmd.Parameters.Add(temppa);
                }
                OpenConn();
                succ = cmd.ExecuteNonQuery() > 0 ? (true) : (false);
                CloseConn();
            }
            catch
            {

                // this.ShowError(ex.Message);
                //using (System.IO.StreamWriter sw = new System.IO.StreamWriter("D:\\error2008.txt"))
                //{
                //    sw.Write(ex.Message);
                //    sw.Flush();
                //}
                return false;
            }
            return succ;

        }
        /// <summary>
        /// 根據(jù)sql語(yǔ)句返回第一個(gè)單元格的數(shù)據(jù)
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public override string GetOne(string sql)
        {
            string res = "";
            try
            {
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = sql;
                OpenConn();
                res = cmd.ExecuteScalar() == null ? ("") : (Convert.ToString(cmd.ExecuteScalar()));
                CloseConn();
            }
            catch (Exception ex)
            {

                return null;
            }
            return res;
        }
        /// <summary>
        ///  根據(jù)sql語(yǔ)句返回第一個(gè)單元格的數(shù)據(jù)帶參數(shù)的
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pas"></param>
        /// <returns></returns>
        public override string GetOne(string sql, params DbParameter[] pas)
        {
            string res = "";
            try
            {
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = sql;
                cmd.Parameters.Clear();

                foreach (SQLiteParameter temppa in pas)
                {
                    cmd.Parameters.Add(temppa);
                }
                OpenConn();
                res = cmd.ExecuteScalar() == null ? ("") : (Convert.ToString(cmd.ExecuteScalar()));
                CloseConn();
            }
            catch (Exception ex)
            {
                CloseConn();

                return null;
            }
            return res;
        }
        /// <summary>
        /// 返回?cái)?shù)據(jù)的DataReader
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public override DbDataReader GetDataReader(string sql)
        {
            SQLiteDataReader dr = null;
            try
            {
                cmd.CommandType = CommandType.Text;
                conn.Open();
                cmd.CommandText = sql;
                dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception ex)
            {

                return null;
            }
            return dr;
        }
        /// <summary>
        /// 返回?cái)?shù)據(jù)的DataReader帶參數(shù)的
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pas"></param>
        /// <returns></returns>
        public override DbDataReader GetDataReader(string sql, params DbParameter[] pas)
        {
            SQLiteDataReader dr = null;
            try
            {
                cmd.CommandType = CommandType.Text;
                conn.Open();
                cmd.Parameters.Clear();

                foreach (SQLiteParameter temppa in pas)
                {
                    cmd.Parameters.Add(temppa);
                }

                cmd.CommandText = sql;
                dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception ex)
            {

                return null;
            }
            return dr;
        }
        /// <summary>
        /// 事務(wù)處理函數(shù)
        /// </summary>
        /// <param name="al"></param>
        /// <returns></returns>
        public override bool GetTranState(ArrayList al)
        {
            cmd.CommandType = CommandType.Text;
            OpenConn();
            SQLiteTransaction tran = (conn as SQLiteConnection).BeginTransaction();
            cmd.Transaction = tran;
            try
            {
                for (int i = 0; i < al.Count; i++)
                {
                    cmd.CommandText = Convert.ToString(al[i]);
                    cmd.ExecuteNonQuery();
                }

                tran.Commit();
                CloseConn();
            }
            catch
            {

                tran.Rollback();
                CloseConn();
                return false;
            }
            return true;

        }
      
        /// <summary>
        /// 分頁(yè)函數(shù)
        /// </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 override 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>
        /// 分頁(yè)存儲(chǔ)過(guò)程的調(diào)用
        /// </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 override DataTable GetProcPageData(int current, int pagesize, string columns, string tablename, string pid, string where, string order, string ordertype)
        {
            SQLiteParameter[] pas = { new SQLiteParameter("@PageIndex", current), new SQLiteParameter("@PageSize", pagesize), new SQLiteParameter("@Columns", columns), new SQLiteParameter("@Tablename", tablename), new SQLiteParameter("@Where", where), new SQLiteParameter("@Order", order), new SQLiteParameter("@OrderType", ordertype), new SQLiteParameter("@Pid", pid) };
            return GetProcTable("Pages", pas);
            //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>
        /// 分頁(yè)存儲(chǔ)過(guò)程的調(diào)用
        /// </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 override DataTable GetProcData(int current, int pagesize, string columns, string tablename, string pid, string where, string order, string resultCount, string distinct)
        {

            SQLiteParameter[] pas = { new SQLiteParameter("@TableNames", DbType.String), new SQLiteParameter("@PrimaryKey", DbType.String), new SQLiteParameter("@Order", DbType.String), new SQLiteParameter("@CurrentPage", DbType.Int32), new SQLiteParameter("@PageSize", DbType.Int32), new SQLiteParameter("@Fields", DbType.String), new SQLiteParameter("@Filter", DbType.String), new SQLiteParameter("@ResultCount", DbType.String), new SQLiteParameter("@distinct", DbType.String) };
            pas[0].Value = tablename;
            pas[1].Value = pid;
            pas[2].Value = order;
            pas[3].Value = current;
            pas[4].Value = pagesize;
            pas[5].Value = columns;
            pas[6].Value = where;
            pas[7].Value = resultCount;
            pas[8].Value = distinct;
            return this.GetProcTable("Pages", pas);
      
        }
        /// <summary>
        /// 分頁(yè)存儲(chǔ)過(guò)程的調(diào)用
        /// </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 override DataTable GetProcAdminData(int current, int pagesize, string columns, string tablename, string pid, string where, string order, string resultCount, string distinct)
        {
            SQLiteParameter[] pas = { new SQLiteParameter("@TableNames", DbType.String), new SQLiteParameter("@PrimaryKey", DbType.String), new SQLiteParameter("@Order", DbType.String), new SQLiteParameter("@CurrentPage", DbType.Int32), new SQLiteParameter("@PageSize", DbType.Int32), new SQLiteParameter("@Fields", DbType.String), new SQLiteParameter("@Filter", DbType.String), new SQLiteParameter("@ResultCount", DbType.String), new SQLiteParameter("@Distinct", DbType.String) };
            pas[0].Value = tablename;
            pas[1].Value = pid;
            pas[2].Value = order;
            pas[3].Value = current;
            pas[4].Value = pagesize;
            pas[5].Value = columns;
            pas[6].Value = where;
            pas[7].Value = resultCount;
            pas[8].Value = distinct;
            return this.GetProcTable("Pages", pas);

        }
        public override DbParameter[] MakeParameters(params string[] str)
        {
            SQLiteParameter[] pas = new SQLiteParameter[str.Length/ 2];
            for (int i = 0; i < str.Length / 2; i++)
            {
                pas[i] = new SQLiteParameter(string.Format("@{0}", str[2 * i]), str[2 * i + 1]);
            }
            return pas;
        }
    }
}