OracleClient使用整理

虽然，微软各种说抛弃OracleClient，但是，目前尚未找到简单粗暴的接口可使用，姑且整理一下。

ps：OracleDataAccess需要监听等条件

    

Curd（非批量操作）部分：

由于代码使用基本一致，所以整合成一组方法来使用，对于复杂的查询不适用。

1、制作sql查询字符串的类

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Base.Oracle
{
    class SqlPatch
    {
        //对于某些情况而言可以按照声明顺序来遍历属性，但是，微软的说法是不按照特定顺序。。。
        public static List<string> GetDefaultColsList(object target)
        {
            Type tempType = target.GetType();

            System.Reflection.PropertyInfo[] temparray = tempType.GetProperties();

            List<string> tempList = new List<string>();
            for (int tempIndex = 0; tempIndex < temparray.Length; tempIndex++)
            {
                tempList.Add(temparray[tempIndex].Name);
            }

            return tempList;
        }

        //curPatch
        public static string PatchForCreatOperation(string tableName,List<string> colsList)
        {
            //"insert into SHORTTERMWEATHER(STARTTIME,ENDTIME,ISRAIN,RATEOFDROP) values (:STARTTIME,:ENDTIME,:ISRAIN,:RATEOFDROP)"
 
            string tempsql="";
            for(int tempName=0; tempName<colsList.Count;tempName++)
            {
                tempsql+=(":"+colsList[tempName]);

                if(tempName!=colsList.Count-1)tempsql +=",";
            } 

            StringBuilder sqlBuilder=new StringBuilder();
            sqlBuilder.Append("insert into ");
            sqlBuilder.Append(tableName);
            sqlBuilder.Append("(");
            sqlBuilder.Append(tempsql.Replace(":",""));
            sqlBuilder.Append(") values (");
            sqlBuilder.Append(tempsql);
            sqlBuilder.Append(")");

            return sqlBuilder.ToString();
             
        }

        //sumstring在没有使用汇总函数必要的情况下为null
        public static string PatchForReserchOperation(string tableName,string sumString)
        {
            //"select */count(xxx) from CONGESTIONINDEXHISTORY "
             
            StringBuilder sqlBuilder = new StringBuilder();
            sqlBuilder.Append("select ");

            if(sumString==null) sqlBuilder.Append("*");
            else sqlBuilder.Append(sumString);

            sqlBuilder.Append(" from ");

            sqlBuilder.Append(tableName);

            return sqlBuilder.ToString(); 
        }

        public static string PatchForUpdateOperation(string tableName, List<string> colsList)
        {
            //"update PREDICTIONRESULT set TRUEVALUE =:TRUEVALUE"  
            //"update SHORTTERMWEATHER set ISRAIN=:ISRAIN,RATEOFDROP=:RATEOFDROP"  

            string tempsql = "";
            for (int tempName = 0; tempName < colsList.Count; tempName++)
            {
                tempsql += (colsList[tempName] + " =:" + colsList[tempName]);

                if (tempName != colsList.Count - 1) tempsql += ",";
            }

            StringBuilder sqlBuilder = new StringBuilder();
            sqlBuilder.Append("update ");
            sqlBuilder.Append(tableName);
            sqlBuilder.Append(" set ");
            sqlBuilder.Append(tempsql);

            return sqlBuilder.ToString();
        }

        //filterPatch

        //字段名列表，比较关系（<,>,=等），数据列表，条件组合（and，or等）
        public static string PatchTheFilter( List<string> colsList, List<string> relation, List<Object> data, List<string> patchKey)
        {
            //where t.TARGETDATE= to_date('"
            //+tsData.ToString("yyyy/MM/dd HH:mm:ss") + "','yyyy-mm-dd hh24:mi:ss')";

            string filter = "where ";

            int tempIndex;
            for (tempIndex = 0; tempIndex < colsList.Count; tempIndex++)
            {
                   filter += (colsList[tempIndex] + relation[tempIndex]);

                   Type tempType= data[tempIndex].GetType();
                   switch (tempType.Name)
                   {
                       case "Int32": filter += Convert.ToInt32(data[tempIndex]); 
                           break;
                       case "Int16": filter += Convert.ToInt16(data[tempIndex]); 
                           break;
                       case "Double":filter += Convert.ToDouble(data[tempIndex]); 
                           break;
                       case "String":filter +=("'"+ Convert.ToString(data[tempIndex])+"'"); 
                           break;
                       case "DateTime":filter +=("to_date('"   +  Convert.ToDateTime(data[tempIndex]).ToString("yyyy/MM/dd HH:mm:ss") + "','yyyy-mm-dd hh24:mi:ss')"); 
                           break;
                       default: break;
                   }

                   if (tempIndex != colsList.Count - 1) filter +=(" "+patchKey[tempIndex]+" ");
            }

            return filter;
        }

    }
}

2、执行查询的类：

<pre name="code" class="csharp">using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data.OracleClient;
using System.Data;

namespace OracleOperation
{
    /// <summary>
    /// oracle数据库curd操作
    /// </summary>
    public class OraclientOpera
    {
        /// <summary>
        /// 统一连接字符串
        /// </summary>
        public static string ConnString = string.Format("data source={0};user id={1};password={2}", "XXX", "XXX", "XXX");

        protected static OracleConnection ConnectionOpen()
        {
            OracleConnection conn = null;
            try
            {
                conn = new OracleConnection(ConnString);
                conn.Open();

                return conn;
            }
            catch (Exception ex)
            {
                if (conn != null)
                {
                    conn.Close(); conn = null;
                }
                
                LogHelper.Helper.WriteErrorLog("ConnectionOpen", ex);

                throw ex;
            }
        }

        protected static void GetOracleParameterCollection(OracleParameterCollection tempCollection, Object result, List<bool> indexList)
        {
            //顺序遍历类属性，根据其属性类型选择赋值方式。
            System.Reflection.PropertyInfo[] tempInfoarray = result.GetType().GetProperties();

            System.Reflection.PropertyInfo tempinfo;
            for (int count = 0; count < tempInfoarray.Length; count++)
            {
                tempinfo = tempInfoarray[count];

                if (indexList[count])
                    tempCollection.Add(
                           new OracleParameter(":" + tempinfo.Name,
                                               tempinfo.GetValue(result, null)
                                               )
                                       );

            }
        }

        /// <summary>
        /// 根据target和默认逻辑判断值得到对应的逻辑判断列表
        /// </summary>
        /// <param name="target"></param>
        /// <param name="defaultBool"></param>
        /// <returns></returns>
        public static List<bool> GetChangeJudgeDefaultList(object target, bool defaultBool)
        {
            Type tempType = target.GetType();

            System.Reflection.PropertyInfo[] temparray = tempType.GetProperties();

            List<bool> tempList = new List<bool>();
            for (int tempIndex = 0; tempIndex < temparray.Length; tempIndex++)
            {
                tempList.Add(defaultBool);
            }

            return tempList;
        }

        #region C、U操作
        /// <summary>
        /// 单记录更新或者插入oracle数据库的操作
        /// </summary>
        /// <param name="SQL"></param>
        /// <param name="dataForInsert"></param>
        /// <param name="indexList"></param>
        /// <returns></returns>
        public static bool TableRecordChange(string SQL, object dataForInsert, List<bool> indexList)
        {
            OracleConnection Conn1 = null;
            OracleCommand CMD1 = null;
            bool result = false;
            try
            {

                Conn1 = ConnectionOpen();
                CMD1 = new OracleCommand(SQL, Conn1);

                GetOracleParameterCollection(CMD1.Parameters, dataForInsert, indexList);

                CMD1.ExecuteNonQuery();

                CMD1.Dispose();
                Conn1.Close();

                result = true;

                return result; 
            }
            catch (Exception ex)
            {
                if (Conn1 != null) Conn1.Close();
                if (CMD1 != null) CMD1.Dispose(); 
                result = false;

                LogHelper.Helper.WriteErrorLog("TableRecordChange", ex);

                throw ex;
            } 
        }

        /// <summary>
        /// 批量插入操作
        /// </summary>
        /// <param name="dt">插入的表格</param>
        /// <returns></returns>
        public static bool DataInsert(DataTable dt)
        {
            OracleConnection connection=null;
            OracleCommandBuilder custCB=null;
            OracleDataAdapter myDataAdapter=null;
            bool result = false;

            try
            {
                //1
                connection = new OracleConnection(ConnString);
                connection.Open(); 

                string tempColumns_1 = "";
                foreach (DataColumn temp_1 in dt.Columns) {
                    tempColumns_1 += temp_1.ColumnName + ",";
                }
                tempColumns_1.Remove(tempColumns_1.LastIndexOf(','));
                string sqlString = string.Format("select {0} from {1} where rownum=0", tempColumns_1, dt.TableName);

                //2
                myDataAdapter = new OracleDataAdapter();
                myDataAdapter.SelectCommand = new OracleCommand(sqlString, connection);
                myDataAdapter.UpdateBatchSize = 0;
                custCB = new OracleCommandBuilder(myDataAdapter);
                myDataAdapter.InsertCommand = custCB.GetInsertCommand();

                //3
                DataTable dtTemp_3 = dt.Clone();
                int times_3 = 0;
                for (int count_3 = 0; count_3 < dt.Rows.Count; times_3++)
                {
                    for (int i_3 = 0; i_3 < 1 && 1 * times_3 + i_3 < dt.Rows.Count; i_3++, count_3++)
                    {
                        dtTemp_3.Rows.Add(dt.Rows[count_3].ItemArray);
                    }
                    myDataAdapter.Update(dtTemp_3);
                    dtTemp_3.Rows.Clear();
                }

                //4
                myDataAdapter.Dispose();
                custCB.Dispose();
                connection.Close();

                result = true;

                return result;
            }
            catch (Exception E)
            { 
               if(myDataAdapter!=null)  myDataAdapter.Dispose();
               if (custCB != null) custCB.Dispose();
               if(connection!=null) connection.Close();

               result = false;

               LogHelper.Helper.WriteErrorLog("DataInsert", E);

               throw E;
            } 
        }
        #endregion

        #region R操作
        /// <summary>
        /// 单记录汇总查询操作
        /// </summary>
        /// <param name="SQL"></param>
        /// <param name="type"></param>
        /// <returns></returns>
        public static object SumOperationOnSingleResult(string SQL, int type)
        {
            OracleConnection conn1 = null;
            OracleCommand CMD1 = null;
            OracleDataReader reader = null;
            object count = null;

            try
            {
                conn1 = ConnectionOpen();
                CMD1 = new OracleCommand(SQL, conn1);

                reader = CMD1.ExecuteReader();

                while (reader.Read())
                    if (type == 1) count = reader.GetInt32(0);
                    else if (type == 2)
                    {
                        if (reader.IsDBNull(0)) count = null;

                        else count = reader.GetDateTime(0);
                    }
                 
                reader.Close();

                CMD1.Dispose();
                conn1.Close();

                return count;
            }
            catch (Exception ex)
            {
               if(reader!=null) reader.Close();

               if(CMD1!=null) CMD1.Dispose();
               if(conn1!=null) conn1.Close();

               count = null;

               LogHelper.Helper.WriteErrorLog("SumOperationOnSingleResult", ex);

               throw ex; 
            }  
        }

        protected static void GetRecordData(OracleDataReader target, Object result)
        {
            //顺序遍历类属性，根据其属性类型选择赋值方式。对于某些情况而言可以按照声明顺序来遍历属性，但是，微软的说法是不按照特定顺序。。。
            System.Reflection.PropertyInfo[] tempInfoarray = result.GetType().GetProperties();

            System.Reflection.PropertyInfo tempinfo;
            for (int count = 0; count < tempInfoarray.Length; count++)
            {
                tempinfo = tempInfoarray[count];
                switch (tempinfo.PropertyType.Name)
                {
                    case "Int32":
                        if (target.IsDBNull(count)) tempinfo.SetValue(result, 0, null);
                        else tempinfo.SetValue(result, target.GetInt32(count), null);
                        break;
                    case "Int16":
                        if (target.IsDBNull(count)) tempinfo.SetValue(result, 0, null);
                        else tempinfo.SetValue(result, target.GetInt16(count), null);
                        break;
                    case "Double":
                        if (target.IsDBNull(count)) tempinfo.SetValue(result, 0, null);
                        else tempinfo.SetValue(result, target.GetDouble(count), null);
                        break;
                    case "String":
                        if (target.IsDBNull(count)) tempinfo.SetValue(result, "", null);
                        else tempinfo.SetValue(result, target.GetString(count), null);
                        break;
                    case "DateTime":
                        if (target.IsDBNull(count)) tempinfo.SetValue(result, null, null);
                        else tempinfo.SetValue(result, target.GetDateTime(count), null);
                        break;
                    default: break;
                }

            }
        }

        /// <summary>
        /// 单记录查询操作
        /// </summary>
        /// <param name="result"></param>
        /// <param name="SQL"></param>
        /// <returns></returns>
        public static bool TableRecordRead(Object result, string SQL)
        {
            OracleConnection conn1 = null;
            OracleCommand CMD1 = null;
            OracleDataReader reader = null;
            bool readresult = false;

            try
            {
                conn1 = ConnectionOpen();

                CMD1 = new OracleCommand(SQL, conn1);

                reader = CMD1.ExecuteReader();
                while (reader.Read())
                {
                    GetRecordData(reader, result);
                    readresult = true;
                }

                reader.Close(); 
                CMD1.Dispose();
                conn1.Close();

                return readresult;
            }
            catch (Exception ex)
            { 
                if(reader!=null) reader.Close(); 
                if (CMD1 != null) CMD1.Dispose();
                if (conn1 != null) conn1.Close();

                readresult=false;

                LogHelper.Helper.WriteErrorLog("TableRecordRead", ex);

                throw ex;
            } 
        }
        /// <summary>
        /// 批量查询操作
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="result"></param>
        /// <param name="SQL"></param>
        /// <returns></returns>
        public static bool TableRecordsRead<T>(List<T> result, string SQL) where T : class,new()
        {
            OracleConnection connnn1 = null;
            OracleCommand CMD1 = null;
            OracleDataReader reader = null;
            bool readresult = false;

            try
            {
                connnn1 = ConnectionOpen();

                CMD1 = new OracleCommand(SQL, connnn1);
                 
                reader = CMD1.ExecuteReader();

                object tempresult;
                while (reader.Read())
                {
                    tempresult = new T();

                    GetRecordData(reader, tempresult);

                    result.Add((T)tempresult);

                    readresult = true;
                }
                reader.Close();
                CMD1.Dispose();
                connnn1.Close();

                return readresult;
            } 
            catch (Exception ex)
            { 
                if(reader!=null) reader.Close(); 
                if (CMD1 != null) CMD1.Dispose();
                if (connnn1 != null) connnn1.Close();

                readresult=false;

                LogHelper.Helper.WriteErrorLog("TableRecordRead", ex);

                throw ex;
            }  
        }

        #endregion
          
    }
}

批量操作部分（要效率就用DataAccess）：

1、批量插入

注意：DataTable的DataRow.RowState要为Added才能插入

  private bool DataInsert(DataTable dt,string Columns,string tableName)
        { 
                OracleConnection connection = new OracleConnection(connectionString); 
                string SQLString = string.Format("select {0} from {1} where rownum=0", Columns, tableName);
                   
                        try
                        {
                            connection.Open();

                            OracleDataAdapter myDataAdapter = new OracleDataAdapter();
                            myDataAdapter.SelectCommand = new OracleCommand(SQLString, connection);
                            myDataAdapter.UpdateBatchSize = 0; 
                            OracleCommandBuilder custCB = new OracleCommandBuilder(myDataAdapter);
                            myDataAdapter.InsertCommand = custCB.GetInsertCommand();
                          
                            DataTable dtTemp = dt.Clone(); 
                            int times = 0;
                            for (int count = 0; count < dt.Rows.Count; times++)
                            {
                                for (int i = 0; i < 500 && 500 * times + i < dt.Rows.Count; i++, count++)
                                {
                                    dtTemp.Rows.Add(dt.Rows[count].ItemArray); 
                                }
                                myDataAdapter.Update(dtTemp);
                                dtTemp.Rows.Clear();
                            }

                            myDataAdapter.Dispose();
                            connection.Close();

                            return true;
                        }
                        catch (System.Data.OracleClient.OracleException E)
                        {
                            MessageBox.Show(E.Message);
                            connection.Close();
                            return false;
                        }
        
        }