using Oracle.ManagedDataAccess.Client;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;

namespace DataexchangeServer.Helper
{
    public class OracleDb
    {
        //public static string connectionString = "Data Source = 192.168.1.19; Persist Security Info=True;User ID = abtdev; Password=abt_dev";
        public static string connectionString = "Data Source = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.13)(PORT = 1521))(CONNECT_DATA = (SERVICE_NAME = ORCL))); Persist Security Info=True;User ID = jfprd; Password=jf_prd";
        private OracleTransaction _transaction;
        private readonly string _connectionString;
        private OracleConnection _connection4Tran;
        private readonly Queue<SqlTask> _transactionTaskList;
        public OracleDb(string connectionString)
        {
            _transactionTaskList = new Queue<SqlTask>();
            this._connectionString = connectionString;
        }

        public DataTable ExecuteDataTable(string sql)
        {
            return this.ExecuteDataTable(sql, CommandType.Text, null);
        }

        private DataTable ExecuteDataTable(string sql, CommandType commandType, OracleParameter[] parameters)
        {
            DataTable dataTable = new DataTable(Guid.NewGuid().ToString());
            using (OracleConnection connection = new OracleConnection(connectionString))
            {
                using (OracleCommand command = new OracleCommand(sql, connection))
                {
                    command.CommandType = commandType;
                    if (parameters != null)
                    {
                        foreach (OracleParameter parameter in parameters)
                        {
                            command.Parameters.Add(parameter);
                        }
                    }
                    new OracleDataAdapter(command).Fill(dataTable);
                }
            }
            return dataTable;
        }


        public object ExecuteScalar(string sql)
        {
            return this.ExecuteScalar(sql, CommandType.Text, null);
        }

        private object ExecuteScalar(string sql, CommandType commandType, OracleParameter[] parameters)
        {
            object obj2;
            using (OracleConnection connection = new OracleConnection(connectionString))
            {
                using (OracleCommand command = new OracleCommand(sql, connection))
                {
                    command.CommandType = commandType;
                    if (parameters != null)
                    {
                        foreach (OracleParameter parameter in parameters)
                        {
                            command.Parameters.Add(parameter);
                        }
                    }
                    connection.Open();
                    obj2 = command.ExecuteScalar();
                }
            }
            return obj2;
        }

        public bool SaveDataToDB(DataTable dataTable)
        {
            //dataTable.AcceptChanges();
            //foreach (DataRow drTemp in dataTable.Rows)
            //{
            //    drTemp.SetAdded();
            //}
            try
            {
                using (OracleConnection connection = new OracleConnection(connectionString))
                {
                    using (OracleCommand command = new OracleCommand("select  * from  " + dataTable.TableName+"   WHERE 1<>1", connection))
                    {
                        OracleDataAdapter adapter = new OracleDataAdapter(command);

                        OracleCommandBuilder builder = new OracleCommandBuilder(adapter);
                        DataTable dsNew = new DataTable();
                        int count = adapter.Fill(dsNew);
                        //for (int i = 0; i < dataTable.Rows.Count; i++)
                        //{
                        //    DataRow dr = dsNew.NewRow();
                        //    for (int j = 0; j < dataTable.Columns.Count; j++)
                        //    {
                        //        dr[dsNew.Columns[j].ColumnName] = dataTable.Rows[i][j];
                        //    }
                        //    dsNew.Rows.Add(dr);
                        //}
                        count = adapter.Update(dataTable);
                        adapter.UpdateBatchSize = 5000;
                        //adapter.Update(dataTable);
                        return true;                      
                        //DataTable tbchange = dataTable.GetChanges();
                        //if (tbchange != null)
                        //    try
                        //    {
                        //        adapter.Update(dataTable);
                        //    }
                        //    catch (Exception)
                        //    {

                        //        return false;
                        //    }
                            
                    }
                }
            }
            catch (Exception)
            {
                return false;
            }
           // return true;
        }


        private class SqlTask
        {
            public string Text { get; private set; }
            public CommandType CommandType { get; private set; }
            public OracleParameter[] Parameters { get; private set; }

            public SqlTask(string text, CommandType commandType, OracleParameter[] paras)
            {
                Text = text;
                CommandType = commandType;
                Parameters = paras;
            }
        }

        /// <summary>
		/// 执行一条计算查询结果语句，返回查询结果（object）。
		/// </summary>
		/// <param name="SQLString">计算查询结果语句</param>
		/// <returns>查询结果（object）</returns>
		public  object GetSingle(string SQLString)
        {
            using (OracleConnection connection = new OracleConnection(connectionString))
            {
                using (OracleCommand cmd = new OracleCommand(SQLString, connection))
                {
                    try
                    {
                        connection.Open();
                        object obj = cmd.ExecuteScalar();
                        if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                        {
                            return null;
                        }
                        else
                        {
                            return obj;
                        }
                    }
                    catch (OracleException e)
                    {
                        connection.Close();
                        throw new Exception(e.Message);
                    }
                }
            }
        }
        public int ExecuteNonQuery(string sql)
        {
            return this.ExecuteNonQuery(sql, CommandType.Text, null);
        }

        private int ExecuteNonQuery(string sql, CommandType commandType, OracleParameter[] parameters)
        {
            return this.ExecuteNonQuery(sql, commandType, parameters, false);
        }

        private int ExecuteNonQuery(string sql, CommandType commandType, OracleParameter[] parameters,  bool joinTransaction)
        {
            int num;
            OracleCommand command;
            if (joinTransaction)
            {
                if ((this._transaction == null) || (this._connection4Tran == null))
                {
                    throw new Exception("事务未初始化！");
                }
             _transactionTaskList.Enqueue(new SqlTask(sql, commandType, parameters));
            }
            using (OracleConnection connection = new OracleConnection(connectionString))
                {
                    using (OracleCommand commandt = new OracleCommand(sql, connection))
                    {
                        commandt.CommandType = commandType;
                        if (parameters != null)
                        {
                            foreach (OracleParameter parameter in parameters)
                            {
                                commandt.Parameters.Add(parameter);
                            }
                        }
                        connection.Open();
                        num = commandt.ExecuteNonQuery();
                    }
                }           
            return num;
        }


        /// <summary>
		/// 执行多条SQL语句，实现数据库事务。
		/// </summary>
		/// <param name="SQLStringList">多条SQL语句</param>		
		public  bool ExecuteSqlTran(ArrayList SQLStringList)
        {
            using (OracleConnection conn = new OracleConnection(connectionString))
            {
                conn.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.Connection = conn;
                OracleTransaction tx = conn.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    for (int n = 0; n < SQLStringList.Count; n++)
                    {
                        string strsql = SQLStringList[n].ToString();
                        if (strsql.Trim().Length > 1)
                        {
                            cmd.CommandText = strsql;
                            cmd.ExecuteNonQuery();
                        }
                    }
                    tx.Commit();
                    return true;
                }
                catch (OracleException E)
                {
                    tx.Rollback();
                    return false;
                }
            }
        }
    }
}