连接Oracle数据库

 using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OracleClient;

using Systool.com.constant;
using System.Data;
using System.Collections;
namespace Systool.com.util
{
    /************************************************************
     * システム：   SysTool
     * クラス：     DBUtil
     * 概要：
     * 作成時間：   2009/05/15
     * 作成者：     王暁光
     * <変更履歴>
     * ---------------------------------------------------------
     * 日付           変更者　　　　　変更内容
     * ---------------------------------------------------------
     * 2009/05/15     王暁光          作成
     *
     *
     ************************************************************/
    class OracleDBUtil
    {
        protected static  OracleConnection orConnection ;
        protected static OracleTransaction orTransation;
        static OracleDBUtil()
        {
            orConnection = GetConnect();
        }
        public OracleDBUtil()
        {
            orConnection = GetConnect();
        }
       
        public static void ReConnect()
        {
           
            orConnection = null;
            orConnection = GetConnect();
        }
        public static OracleCommand getOracleCommand()
        {
            if(orConnection==null)
            {
                ReConnect();
            }
            return orConnection.CreateCommand();
        }
        public static OracleCommand getTransactionOracleCommand()
        {
            if (orConnection == null)
            {
                ReConnect();
            }
            OracleCommand cmd = orConnection.CreateCommand();
            cmd.Transaction=orTransation;
            return cmd;
        }
        public static DataTable DBExeQuery(String Sql, ArrayList SqlParams)
        {    
            DataTable resDT=null;
            try
            {   if(orConnection==null)
                {
                    ReConnect();
                }
                OracleCommand cmd =orConnection.CreateCommand();
                cmd.CommandText = Sql;
                SetComParam(cmd, SqlParams);
              

                orConnection.Open();
                OracleDataReader dr = cmd.ExecuteReader();

                resDT = ReadDataFromDataReader(dr);
           
                //閉める
                dr.Close();
                orConnection.Close();
                return resDT;
                
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message, ex);
            }
        }
        public static void DBExeSQL(String Sql, ArrayList SqlParams)
        {
            try
            {
                if (orConnection == null)
                {
                    ReConnect();
                }
                OracleCommand cmd = new OracleCommand(Sql, orConnection);
                SetComParam(cmd, SqlParams);
              

                orConnection.Open();
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message, ex);
            }
        }
        public static void DBExeSQLBatch(ArrayList SqlBatch, ArrayList SqlParams)
        {
            if (orConnection == null)
            {
                ReConnect();
            }
            orConnection.Open();
            OracleCommand cmd = orConnection.CreateCommand();
            OracleTransaction orTransaction = orConnection.BeginTransaction(IsolationLevel.ReadCommitted);
            cmd.Transaction = orTransaction;
            String Sql;
            try
            {
                
                for (int i = 0; i < SqlBatch.Count; i++)
                {
                    Sql = SqlBatch[i].ToString();
                    cmd.CommandText = Sql;
                    SetComParam(cmd, (ArrayList)SqlParams[i]);
                    cmd.ExecuteNonQuery();
                }

                orTransaction.Commit();
            }
            catch (Exception ex)
            {
                orTransaction.Rollback();
                throw new Exception(ex.Message, ex);
            }
        }
        public static void BeginTransaction()
        {

            try
            {
                if (orTransation == null)
                {
                    orTransation = orConnection.BeginTransaction(IsolationLevel.ReadCommitted);
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message, ex);
            }
        }
        public static void CommitTransaction()
        {
            try
            {
                if (orTransation != null)
                {
                    orTransation.Commit();
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message, ex);
            }
        }
        private static void SetComParam(OracleCommand cmd, ArrayList SqlParams)
        {
            if (cmd == null || SqlParams == null)
            {
                return;
            }
            try
            {
              
                foreach (OracleParameter obj in SqlParams)
                {
                    cmd.Parameters.Add(obj);
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message, ex);
            }
            
        }
        private static OracleConnection GetConnect()
        {
           
                return new OracleConnection(SysConsts.DB_CONNECT_STRING);
               
          
        }
        private static DataTable ReadDataFromDataReader(OracleDataReader dr)
        {
            
            DataTable dt = new DataTable();
            DataColumn dc=null;
            try
            {   
                //列を追加する
                for (int i = 0; i < dr.FieldCount; i++)
                {
                    dc = new DataColumn();
                    dc.ColumnName = dr.GetName(i);
                    dt.Columns.Add(dc);
                }
                //横とデータを追加する
                while (dr.Read())
                {
                    DataRow dtr = dt.NewRow();
                    for (int i = 0; i < dr.FieldCount; i++)
                    {
                        dtr[i] = dr[i];
                    }

                    dt.Rows.Add(dtr);
                }
                return dt;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message, ex);
            }
        }

    }
}