using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SQLite;
using MySql.Data.MySqlClient;
using System.Collections;

namespace POSConfig
{
    public class DbClass
    {
        private string host = "localhost";         // 数据库主机
        private string user;         // 数据库用户名
        private string pass;         // 数据库密码
        private string database;         // 数据库名
        public MySqlConnection Mysqlconn;         // Mysql数据库连接标识
        public SQLiteConnection Sqliteconn;         // Sqlite数据库连接标识

        private string code = "utf8";         // 数据库编码，GBK,UTF8,GB2312
        public DataTable resultDT;       // 执行query命令的结果数据集

        public string ErrMess = ""; // 错误信息

        public bool status = false; // 状态



        private int dbclassid = 1;//1为连接mysql,2为连接sqlite

        /******************************************************************
         -- 函数名：DbClass(string host,string user,string pass,string database,string code )
         -- 作  用：构造函数
         -- 参  数：host 数据库主机地址(必填)
                    user 数据库用户名(必填)
                    pass 数据库密码(必填)
                    database 数据库名(必填) 
                    code 数据库编码(必填)
    
         -- 返回值：无 
         -- 实  例：无
         *******************************************************************/
        public DbClass(string host, string user, string pass, string database, string code)
        {
            // Mysq;
            this.host = host;
            if (host == "") this.host = "localhost";
            this.user = user;
            this.pass = pass;
            this.database = database;
            this.code = code;
            this.dbclassid = 1;
            this.connect();
        }

        public DbClass(string pass, string database)
        {
            //Sqlite
            this.pass = pass;
            this.database = database;
            this.dbclassid = 2;
            this.connect();
        }
        public DbClass(string database)
        {
            //Sqlite
            this.pass = "";
            this.database = database;
            this.dbclassid = 2;
            this.connect();
        }

        public DataTable getDT() { return this.resultDT; }

        public void setDT(DataTable value) { this.resultDT = value; }

        public MySqlConnection getMysqlconn() { return this.Mysqlconn; }

        public SQLiteConnection getSqliteconn() { return this.Sqliteconn; }


        // 数据库连接
        private void connect()
        {
            switch (this.dbclassid)
            {
                //mysql 连接
                case 1:
                    this.Mysqlconn = new MySqlConnection();
                    Mysqlconn.ConnectionString = "server=" + this.host + ";user id=" + this.user + "; password=" + this.pass + "; database=" + this.database + "; pooling=false";
                    try
                    {
                        Mysqlconn.Open();
                        this.status = true;
                        this.ErrMess = "";
                    }
                    catch (Exception ex)
                    {
                        this.status = false;
                        this.ErrMess = "无法连 Mysql 接服务器:"+ex.Message;
                    }

                    break;
                //sqlite 连接
                case 2:
                    this.Sqliteconn = new SQLiteConnection();

                    SQLiteConnectionStringBuilder connStr = new SQLiteConnectionStringBuilder();

                    connStr.DataSource = this.database;
                    if (this.pass != "") connStr.Password = this.pass;

                    Sqliteconn.ConnectionString = connStr.ToString();
                    try
                    {
                        Sqliteconn.Open();
                        this.status = true;
                        this.ErrMess = "";
                    }
                    catch
                    {
                        this.status = false;
                        this.ErrMess = "无法连接 Sqlite 数据库";
                    }
                    break;
            }
        }


        public void TableFill(MySqlConnection conn, string strSql, DataTable mydatatable, IDbTransaction trans)
        {

            mydatatable = new DataTable();
            MySqlDataAdapter myadapter = new MySqlDataAdapter();
            MySqlTransaction tr = (MySqlTransaction)trans;
            if (trans != null)
            {
                myadapter.SelectCommand.Transaction = tr;
            }
            try
            {
                myadapter.SelectCommand = new MySqlCommand(strSql, conn);
                myadapter.Fill(mydatatable);
                this.resultDT = mydatatable;
                this.status = true;
                this.ErrMess = "";
            }
            catch (MySqlException ex)
            {
                this.status = false;
                this.ErrMess = ex.Message + "[" + strSql + "]";
            }
        }
        public void TableFill(SQLiteConnection conn, string strSql, DataTable mydatatable, IDbTransaction trans)
        {

            mydatatable = new DataTable();

            SQLiteCommand mycmd = new SQLiteCommand(strSql, conn);
            SQLiteTransaction tr = (SQLiteTransaction)trans;
            if (trans != null)
            {
                mycmd.Transaction = tr;
            }
            try
            {
                SQLiteDataAdapter dao = new SQLiteDataAdapter(mycmd);
                dao.Fill(mydatatable);
                this.resultDT = mydatatable;
                this.status = true;
                this.ErrMess = "";
            }
            catch (SQLiteException ex)
            {
                this.status = false;
                this.ErrMess = ex.Message + "[" + strSql + "]";
            }
        }
        public void TableFill(MySqlConnection conn, string strSql, ref DataTable mydatatable)
        {

            mydatatable = new DataTable();
            MySqlDataAdapter myadapter = new MySqlDataAdapter();

            try
            {
                myadapter.SelectCommand = new MySqlCommand(strSql, conn);
                myadapter.Fill(mydatatable);
                this.resultDT = mydatatable;
                this.status = true;
                this.ErrMess = "";
            }
            catch (MySqlException ex)
            {
                this.status = false;
                this.ErrMess = ex.Message + "[" + strSql + "]";
            }
        }
        public void TableFill(SQLiteConnection conn, string strSql, ref DataTable mydatatable)
        {

            mydatatable = new DataTable();

            SQLiteCommand mycmd = new SQLiteCommand(strSql, conn);

            try
            {

                SQLiteDataAdapter dao = new SQLiteDataAdapter(mycmd);
                dao.Fill(mydatatable);
                this.resultDT = mydatatable;
                this.status = true;
                this.ErrMess = "";
            }
            catch (SQLiteException ex)
            {
                this.status = false;
                this.ErrMess = ex.Message + "[" + strSql + "]";
            }
        }

        public void dbfill(MySqlConnection conn, string strSql, DataTable mydatatable)
        {

            MySqlDataAdapter myadapter = new MySqlDataAdapter();

            MySqlCommandBuilder custCB = new MySqlCommandBuilder(myadapter);
              try
            {
                myadapter.SelectCommand = new MySqlCommand(strSql, conn);
                myadapter.Update(mydatatable);

                mydatatable.AcceptChanges();
                this.resultDT = mydatatable;
                this.status = true;
                this.ErrMess = "";
            }
            catch (MySqlException ex)
            {
                this.status = false;
                this.ErrMess = ex.Message + "[" + strSql + "]";
            }
        }

        public void dbfill2(MySqlConnection conn, string strSql, DataTable mydatatable,string tbName)
        {

            MySqlDataAdapter myadapter = new MySqlDataAdapter();

            MySqlCommandBuilder custCB = new MySqlCommandBuilder(myadapter);
            try
            {
                myadapter.SelectCommand = new MySqlCommand(strSql, conn);

                DataSet ds = new DataSet();
                myadapter.Fill(ds, tbName);
                for (int i = 0; i < mydatatable.Rows.Count; i++)
                {
                    DataRow dr = ds.Tables[0].NewRow();
                  //  foreach (DataColumn aDataColumn in mydatatable.Columns)
                  //  {
                  //      dr[aDataColumn.ColumnName] = mydatatable.Rows[i][aDataColumn.ColumnName];
                  //  }
                    dr.ItemArray = mydatatable.Rows[i].ItemArray;
                    ds.Tables[0].Rows.Add(dr);
                }
                myadapter.Update(ds, tbName);

                mydatatable.AcceptChanges();
                this.resultDT = mydatatable;
                this.status = true;
                this.ErrMess = "";
            }
            catch (MySqlException ex)
            {
                this.status = false;
                this.ErrMess = ex.Message + "[" + strSql + "]";
            }
        }
 
        public void dbfillb(SQLiteConnection conn, string strSql, DataTable mydatatable)
        {
            SQLiteDataAdapter myadapter = new SQLiteDataAdapter();

            SQLiteCommandBuilder custCB = new SQLiteCommandBuilder(myadapter);

            DataTable dt = mydatatable;


            try
            {
                myadapter.SelectCommand = new SQLiteCommand(strSql, conn);

                int result = myadapter.Update(dt);

                dt.AcceptChanges();
                this.resultDT = dt;
                this.status = true;
                this.ErrMess = result.ToString();
            }
            catch (SQLiteException ex)
            {
                this.status = false;
                this.ErrMess = ex.Message + "[" + strSql + "]";
            }
        }

        public void dbfill(SQLiteConnection conn, string strSql, DataTable mydatatable)
        {
            SQLiteTransaction ts = conn.BeginTransaction();

            SQLiteCommand command = new SQLiteCommand(strSql, conn);
            SQLiteDataAdapter myadapter = new SQLiteDataAdapter(command);

            SQLiteCommandBuilder custCB = new SQLiteCommandBuilder(myadapter);

            DataSet ds = new DataSet();
            myadapter.Fill(ds);
            DataTable dt = ds.Tables[0];


            DataColumnCollection Mycollection = mydatatable.Columns;

            for (int i = 0; i < mydatatable.Rows.Count; i++)
            {

                DataRow dr = dt.NewRow();
                for (int j = 0; j < Mycollection.Count; j++)
                {

                    dr[Mycollection[j].ColumnName] = mydatatable.Rows[i][Mycollection[j].ColumnName];

                }
                dt.Rows.Add(dr);

            }
            try
            {
                myadapter.SelectCommand = new SQLiteCommand(strSql, conn);

                int result = myadapter.Update(ds);
                ts.Commit();
                ds.AcceptChanges();
                this.resultDT = dt;
                this.status = true;
                this.ErrMess = result.ToString();
            }
            catch (SQLiteException ex)
            {
                this.status = false;
                this.ErrMess = ex.Message + "[" + strSql + "]";
            }


        }
        public void readfill(MySqlConnection conn, ref double myReader, string strSql)
        {

            MySqlCommand myCommand = new MySqlCommand(strSql, conn);
            if (myCommand.ExecuteScalar() == System.DBNull.Value)
            {
                myReader = 0.0;
            }
            else
            {
                myReader = System.Convert.ToDouble(myCommand.ExecuteScalar());
            }
        }
        public void readfill(SQLiteConnection conn, ref double myReader, string strSql)
        {

            SQLiteCommand myCommand = new SQLiteCommand(strSql, conn);
            if (myCommand.ExecuteScalar() == System.DBNull.Value)
            {
                myReader = 0.0;
            }
            else
            {
                myReader = System.Convert.ToDouble(myCommand.ExecuteScalar());
            }
        }

        public void readfill(MySqlConnection conn, ref int myReader, string strSql)
        {

            MySqlCommand myCommand = new MySqlCommand(strSql, conn);
            if (myCommand.ExecuteScalar() == System.DBNull.Value)
            {
                myReader = 0;
            }
            else
            {
                myReader = System.Convert.ToInt32(myCommand.ExecuteScalar());
            }
        }
        public void readfill(SQLiteConnection conn, ref int myReader, string strSql)
        {

            SQLiteCommand myCommand = new SQLiteCommand(strSql, conn);
            if (myCommand.ExecuteScalar() == System.DBNull.Value)
            {
                myReader = 0;
            }
            else
            {
                myReader = System.Convert.ToInt32(myCommand.ExecuteScalar());
            }
        }

        public void readfill(MySqlConnection conn, ref string myReader, string strSql)
        {

            MySqlCommand myCommand = new MySqlCommand(strSql, conn);
            if (myCommand.ExecuteScalar() == System.DBNull.Value)
            {
                myReader = string.Empty;
            }
            else
            {
                myReader = System.Convert.ToString(myCommand.ExecuteScalar());
            }
        }
        public void readfill(SQLiteConnection conn, ref string myReader, string strSql)
        {

            SQLiteCommand myCommand = new SQLiteCommand(strSql, conn);
            if (myCommand.ExecuteScalar() == System.DBNull.Value)
            {
                myReader = string.Empty;
            }
            else
            {
                myReader = System.Convert.ToString(myCommand.ExecuteScalar());
            }
        }

        public int UpdateDB(MySqlConnection conn, string strSql, IDbTransaction trans)
        {

            MySqlCommand mycmd = new MySqlCommand(strSql, conn);
            MySqlTransaction tr = (MySqlTransaction)trans;
            if (trans != null)
            {
                mycmd.Transaction = tr;
            }
            try
            {
                this.status = true;
                this.ErrMess = "";
                return mycmd.ExecuteNonQuery();

            }
            catch (MySqlException ex)
            {
                this.status = false;
                this.ErrMess = ex.Message + "[" + strSql + "]";
                return 0;
            }
        }

        public int UpdateDB(SQLiteConnection conn, string strSql, IDbTransaction trans)
        {

            SQLiteCommand mycmd = new SQLiteCommand(strSql, conn);
            SQLiteTransaction tr = (SQLiteTransaction)trans;
            if (trans != null)
            {
                mycmd.Transaction = tr;
            }
            if (trans != null)
            {
                mycmd.Transaction = (SQLiteTransaction)trans;
            }
            try
            {
                this.status = true;
                this.ErrMess = "";
                return mycmd.ExecuteNonQuery();
            }
            catch (SQLiteException ex)
            {
                this.status = false;
                this.ErrMess = ex.Message + "[" + strSql + "]";
                return 0;
            }
        }

        public int InsertDB(MySqlConnection conn, string strSql, IDbTransaction trans)
        {

            strSql = strSql + "; select LAST_INSERT_ID()";
            MySqlCommand mycmd = new MySqlCommand(strSql, conn);
            MySqlTransaction tr = (MySqlTransaction)trans;
            if (trans != null)
            {
                mycmd.Transaction = tr;
            }
            try
            {
                this.status = true;
                this.ErrMess = "";
                return System.Convert.ToInt32(mycmd.ExecuteScalar());

            }
            catch (MySqlException ex)
            {
                this.status = false;
                this.ErrMess = ex.Message + "[" + strSql + "]";
                return -1;
            }

        }
        public int InsertDB(SQLiteConnection conn, string strSql, IDbTransaction trans)
        {

            strSql = strSql + "; select last_insert_rowid()";
            SQLiteCommand mycmd = new SQLiteCommand(strSql, conn);
            SQLiteTransaction tr = (SQLiteTransaction)trans;
            if (trans != null)
            {
                mycmd.Transaction = tr;
            }

            try
            {
                this.status = true;
                this.ErrMess = "";
                return System.Convert.ToInt32(mycmd.ExecuteScalar());

            }
            catch (SQLiteException ex)
            {
                this.status = false;
                this.ErrMess = ex.Message + "[" + strSql + "]";
                return -1;
            }

        }

        public int UpdateDBNew(MySqlConnection conn, string strSql)
        {
            MySqlCommand mycmd = new MySqlCommand(strSql, conn);
            try
            {
                this.status = true;
                this.ErrMess = "";
                return mycmd.ExecuteNonQuery();
            }
            catch (MySqlException ex)
            {
                this.status = false;
                this.ErrMess = ex.Message + "[" + strSql + "]";
                return 0;
            }
        }
        public int UpdateDBNew(SQLiteConnection conn, string strSql)
        {
            SQLiteCommand mycmd = new SQLiteCommand(strSql, conn);
            try
            {
                this.status = true;
                this.ErrMess = "";
                return mycmd.ExecuteNonQuery();
            }
            catch (SQLiteException ex)
            {
                this.status = false;
                this.ErrMess = ex.Message + "[" + strSql + "]";
                return 0;
            }
        }
        public void GetStructionInfo(MySqlConnection conn, string strSql, ArrayList mylist)
        {

            mylist.Clear();

            MySqlCommand mycmd = new MySqlCommand(strSql, conn);
            MySqlDataReader reader;

            try
            {
                reader = mycmd.ExecuteReader();

                while (reader.Read())
                {
                    mylist.Add(reader.GetString(0));
                }
                if (reader != null)
                {
                    reader.Close();
                }
                this.status = true;
                this.ErrMess = "";
            }
            catch (MySqlException ex)
            {
                this.status = false;
                this.ErrMess = ex.Message + "[" + strSql + "]";
            }

        }
        public void GetStructionInfo(SQLiteConnection conn, string strSql, ArrayList mylist)
        {

            mylist.Clear();

            SQLiteCommand mycmd = new SQLiteCommand(strSql, conn);
            SQLiteDataReader reader;

            try
            {
                reader = mycmd.ExecuteReader();

                while (reader.Read())
                {
                    mylist.Add(reader.GetString(0));
                }
                if (reader != null)
                {
                    reader.Close();
                }
                this.status = true;
                this.ErrMess = "";
            }
            catch (SQLiteException ex)
            {
                this.status = false;
                this.ErrMess = ex.Message + "[" + strSql + "]";
            }

        }
        public DateTime GetServerDateTime(MySqlConnection conn)
        {

            MySqlCommand mycmd = new MySqlCommand("select now();", conn);
            try
            {
                string strDateTime = mycmd.ExecuteScalar().ToString();
                DateTime ServerDateTime = DateTime.Parse(strDateTime);
                this.status = true;
                this.ErrMess = "";
                return ServerDateTime;
            }
            catch (MySqlException ex)
            {
                this.status = false;
                this.ErrMess = ex.Message;
                return DateTime.Now;
            }
        }
        public DateTime GetServerDateTime(SQLiteConnection conn)
        {
            this.status = true;
            this.ErrMess = "";
            return DateTime.Now;
        }
        public void SetFill(MySqlConnection conn, string strSql, ref DataSet mydataset)
        {

            mydataset = new DataSet();

            MySqlDataAdapter myadapter = new MySqlDataAdapter();

            try
            {
                myadapter.SelectCommand = new MySqlCommand(strSql, conn);
                myadapter.Fill(mydataset);
                this.status = true;
                this.ErrMess = "";
            }
            catch (MySqlException ex)
            {
                this.status = false;
                this.ErrMess = ex.Message + "[" + strSql + "]";
            }
        }
        public void SetFill(SQLiteConnection conn, string strSql, ref DataSet mydataset)
        {

            mydataset = new DataSet();

            SQLiteDataAdapter myadapter = new SQLiteDataAdapter();

            try
            {
                myadapter.SelectCommand = new SQLiteCommand(strSql, conn);
                myadapter.Fill(mydataset);
                this.status = true;
                this.ErrMess = "";
            }
            catch (SQLiteException ex)
            {
                this.status = false;
                this.ErrMess = ex.Message + "[" + strSql + "]";
            }
        }
        public DataTable getDataTable(MySqlConnection conn, string strSql)
        {
            DataTable dt = new DataTable();

            TableFill(conn, strSql, dt, null);

            return dt;
        }
        public DataTable getDataTable(SQLiteConnection conn, string strSql)
        {
            DataTable dt = new DataTable();

            TableFill(conn, strSql, dt, null);

            return dt;
        }

        // 取最大的ID
        public Int32 GetMaxID(MySqlConnection conn, string sKeyField, string sTableName)
        {
            DataTable dt = getDataTable(conn, "select ifnull(max([" + sKeyField + "]),0) as MaxID from [" + sTableName + "]");

            if (dt != null && dt.Rows.Count > 0)
            {
                return Convert.ToInt32(dt.Rows[0][0].ToString());
            }
            return 0;
        }
        public Int32 GetMaxID(SQLiteConnection conn, string sKeyField, string sTableName)
        {
            DataTable dt = getDataTable(conn, "select ifnull(max([" + sKeyField + "]),0) as MaxID from [" + sTableName + "]");

            if (dt != null && dt.Rows.Count > 0)
            {
                return Convert.ToInt32(dt.Rows[0][0].ToString());
            }
            return 0;
        }

        // 取某个单一的元素
        public object GetSingle(MySqlConnection conn, string sSQL)
        {
            DataTable dt = getDataTable(conn, sSQL);

            if (dt != null && dt.Rows.Count > 0)
            {
                return dt.Rows[0][0];
            }
            return null;
        }
        public object GetSingle(SQLiteConnection conn, string sSQL)
        {
            DataTable dt = getDataTable(conn, sSQL);

            if (dt != null && dt.Rows.Count > 0)
            {
                return dt.Rows[0][0];
            }
            return null;
        }

    }
}