using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Windows.Forms;

using System.Data.OracleClient;

namespace Cyberpipe
{
    class OledbHelper
    {
        private static OracleConnection conn = null;
        private static OracleCommand oracleCmd = null;
        private static OracleDataReader oracleReader = null;
        
        public static DataTable QueryTable(string cmdText)
        {
            string connectString = @"Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=" + Utility.DBServer 
                + ")(PORT=1521))(CONNECT_DATA=(SID=" + Utility.dbdatabase 
                + ")));Persist Security Info=True;User Id=" + Utility.userID 
                + "; Password=" + Utility.DBPassword + "";
            try
            {
                if (conn == null)
                    conn = new OracleConnection(connectString);

                conn.Open();
                oracleCmd = conn.CreateCommand();
                oracleCmd.CommandText = cmdText;
                oracleReader = oracleCmd.ExecuteReader();

                DataTable table = new DataTable();
                table.Load(oracleReader);

                oracleReader.Close();
                conn.Close();

                return table;
            }
            catch (Exception ex)
            {
                if (oracleReader != null)
                {
                    oracleReader.Close();                    
                }
                if (conn != null)
                {
                    conn.Close();
                }
                MessageBox.Show(ex.Message, "提示");              
                return null;
            }
        }
        public static int ExecuteNonQuery(string cmdText)
        {
            OleDbConnection oleConn = new OleDbConnection("Data Source=" + Utility.DBServer + ";Initial Catalog=" + Utility.dbdatabase + ";Persist Security Info=True;User ID=" + Utility.userID + ";pwd=" + Utility.DBPassword + "");
            OleDbCommand oleCmd = new OleDbCommand();
            oleCmd.Connection = oleConn;
            oleCmd.CommandText = cmdText;
            int rowCount = 0;
            try
            {
                oleConn.Open();
                rowCount = oleCmd.ExecuteNonQuery();
            }
            catch(Exception ex)
            {
               // LogError.PublishError(ex);
            }
            finally
            {
                if (oleConn.State == ConnectionState.Open)
                {
                    oleConn.Close();
                }
            }
            return rowCount;
        }
        public static OracleConnection sqlConnection()
        {
            string connectString = @"Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=" + Utility.DBServer
                + ")(PORT=1521))(CONNECT_DATA=(SID=" + Utility.dbdatabase
                + ")));Persist Security Info=True;User Id=" + Utility.userID
                + "; Password=" + Utility.DBPassword + "";
            OracleConnection conn = new OracleConnection(connectString);
            return conn;
        }
        public static DataTable ExecuteDataTable(string sql, params OracleParameter[] ops)
        {
            string connectString = @"Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=" + Utility.DBServer
                + ")(PORT=1521))(CONNECT_DATA=(SID=" + Utility.dbdatabase
                + ")));Persist Security Info=True;User Id=" + Utility.userID
                + "; Password=" + Utility.DBPassword + "";
            conn = new OracleConnection(connectString);
            try
            {
                conn.Open();
                oracleCmd = conn.CreateCommand();
                oracleCmd.CommandText = sql;
                foreach (OracleParameter op in ops)
                {
                    oracleCmd.Parameters.Add(op);
                }
                OracleDataAdapter adapter = new OracleDataAdapter(oracleCmd);
                DataSet ds = new DataSet();
                adapter.Fill(ds);
                conn.Close();
                return ds.Tables[0];
            }
            catch (Exception ex)
            {
                return null;
            }
        }
        public static int sqlExecuteNonQuery(string sql)
        {
            string connectString = @"Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=" + Utility.DBServer
                + ")(PORT=1521))(CONNECT_DATA=(SID=" + Utility.dbdatabase
                + ")));Persist Security Info=True;User Id=" + Utility.userID
                + "; Password=" + Utility.DBPassword + "";
            conn = new OracleConnection(connectString);
            oracleCmd = conn.CreateCommand();
            oracleCmd.CommandText = sql;
            int rowCount = 0;
            try
            {
                conn.Open();
                rowCount = oracleCmd.ExecuteNonQuery();
            }
            catch(Exception ex)
            {
                rowCount = -1;               
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
            return rowCount;
        }
        public static int ExecuteScalar(string sql)
        {
            string connectString = @"Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=" + Utility.DBServer
                + ")(PORT=1521))(CONNECT_DATA=(SID=" + Utility.dbdatabase
                + ")));Persist Security Info=True;User Id=" + Utility.userID
                + "; Password=" + Utility.DBPassword + "";
            conn = new OracleConnection(connectString);
            oracleCmd = conn.CreateCommand();
            oracleCmd.CommandText = sql;
            int num = 0;
            try
            {
                conn.Open();
                num = Convert.ToInt32(oracleCmd.ExecuteScalar().ToString());
            }
            catch(Exception ex)
            {
                num = -1;              
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
            return num;
        }

        public static DataSet getDataSet(string SQLstr, string tableName)
        {
            string connectString = @"Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=" + Utility.DBServer
                + ")(PORT=1521))(CONNECT_DATA=(SID=" + Utility.dbdatabase
                + ")));Persist Security Info=True;User Id=" + Utility.userID
                + "; Password=" + Utility.DBPassword + "";
            conn = new OracleConnection(connectString);
            try {
                 conn.Open();
                 oracleCmd = conn.CreateCommand();
                 oracleCmd.CommandText = SQLstr;
                 OracleDataAdapter adapter = new OracleDataAdapter(oracleCmd);
                 DataSet ds = new DataSet();
                 adapter.Fill(ds, tableName);
                 conn.Close();
                 return ds;  //返回DataSet对象的信息
            }
            catch (Exception ex)
            {
                return null;
            } 
        }

        public static void getsqlcom(string SQLstr)
        {
            string connectString = @"Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=" + Utility.DBServer
                + ")(PORT=1521))(CONNECT_DATA=(SID=" + Utility.dbdatabase
                + ")));Persist Security Info=True;User Id=" + Utility.userID
                + "; Password=" + Utility.DBPassword + "";
            conn = new OracleConnection(connectString);
            try { 
                conn.Open();

                oracleCmd = conn.CreateCommand();
                oracleCmd.CommandText = SQLstr;
                oracleCmd.ExecuteNonQuery();
                oracleCmd.Dispose();
                conn.Close();     //调用con_close()方法，关闭与数据库的连接
            }catch(Exception ex){

            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
        }

        public static int sqlExecuteNonQuery(string sql, string dbip, string dbname, string usrname, string pwd) {
            string connectString = @"Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=" + dbip
                    + ")(PORT=1521))(CONNECT_DATA=(SID=" + dbname
                    + ")));Persist Security Info=True;User Id=" + usrname
                    + "; Password=" + pwd + "";
            conn = new OracleConnection(connectString);
            oracleCmd = conn.CreateCommand();
            oracleCmd.CommandText = sql;
            int rowCount = 0;
            try
            {
                conn.Open();
                rowCount = oracleCmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                rowCount = -1;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                    conn = null;
                }
            }
            return rowCount;
        }
    }
}