)>}]
شركة التطبيقات المتكاملة لتصميم وبرمجة البرمجيات الخاصة ش.ش.و.
Integrated Applications Programming Company
Home » Code Library » Oracle (Ia.Cl.Models.Db)

Public general use code classes and xml files that we've compiled and used over the years:

Oracle support class.

    1: using Oracle.ManagedDataAccess.Client;
    2: using System;
    3: using System.Configuration;
    4: using System.Data;
    5:  
    6: namespace Ia.Cl.Models.Db
    7: {
    8:     ////////////////////////////////////////////////////////////////////////////
    9:  
   10:     /// <summary publish="true">
   11:     /// Oracle support class.
   12:     /// </summary>
   13:     /// <remarks> 
   14:     /// Copyright © 2001-2022 Jasem Y. Al-Shamlan (info@ia.com.kw), Integrated Applications - Kuwait. All Rights Reserved.
   15:     ///
   16:     /// This library is free software: you can redistribute it and/or modify it under the terms of the GNU General Public License as published by
   17:     /// the Free Software Foundation, either version 3 of the License, or (at your option) any later version.
   18:     ///
   19:     /// This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of
   20:     /// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.
   21:     /// 
   22:     /// You should have received a copy of the GNU General Public License along with this library. If not, see http://www.gnu.org/licenses.
   23:     /// 
   24:     /// Copyright notice: This notice may not be removed or altered from any source distribution.
   25:     /// </remarks> 
   26:     public class Oracle
   27:     {
   28:         private string connectionString;
   29:         private static OracleConnection oracleConnection;
   30:  
   31:         ////////////////////////////////////////////////////////////////////////////
   32:  
   33:         /// <summary>
   34:         ///
   35:         /// </summary>
   36:         public Oracle()
   37:         {
   38:             connectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
   39:  
   40:             oracleConnection = new OracleConnection();
   41:  
   42:             oracleConnection.ConnectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
   43:             oracleConnection.Open();
   44:         }
   45:  
   46:         ////////////////////////////////////////////////////////////////////////////
   47:  
   48:         /// <summary>
   49:         ///
   50:         /// </summary>
   51:         public Oracle(string _connectionString)
   52:         {
   53:             connectionString = _connectionString;
   54:  
   55:             oracleConnection = new OracleConnection();
   56:  
   57:             oracleConnection.ConnectionString = _connectionString;
   58:             oracleConnection.Open();
   59:         }
   60:  
   61:         ////////////////////////////////////////////////////////////////////////////
   62:  
   63:         /// <summary>
   64:         ///
   65:         /// </summary>
   66:         public bool Sql(string sql)
   67:         {
   68:             bool b;
   69:             OracleCommand oracleCommand;
   70:  
   71:             oracleCommand = null;
   72:  
   73:             try
   74:             {
   75:                 oracleCommand = new OracleCommand
   76:                 {
   77:                     Connection = oracleConnection,
   78:                     CommandText = sql,
   79:                     CommandType = CommandType.Text
   80:                 };
   81:  
   82:                 oracleCommand.ExecuteNonQuery();
   83:  
   84:                 b = true;
   85:             }
   86:             catch (OracleException)
   87:             {
   88:                 b = false;
   89:             }
   90:             catch (Exception)
   91:             {
   92:                 b = false;
   93:             }
   94:             finally
   95:             {
   96:                 oracleCommand.Dispose();
   97:             }
   98:  
   99:             return b;
  100:         }
  101:  
  102:         ////////////////////////////////////////////////////////////////////////////
  103:  
  104:         /// <summary>
  105:         ///
  106:         /// </summary>
  107:         public DataTable Select(string sql)
  108:         {
  109:             DataTable dataTable;
  110:             OracleCommand oracleCommand;
  111:             OracleDataReader oracleDataReader;
  112:  
  113:             oracleCommand = null;
  114:             oracleDataReader = null;
  115:  
  116:             try
  117:             {
  118:                 dataTable = new DataTable();
  119:  
  120:                 oracleCommand = new OracleCommand
  121:                 {
  122:                     Connection = oracleConnection,
  123:                     CommandText = sql,
  124:                     CommandType = CommandType.Text
  125:                 };
  126:  
  127:                 oracleDataReader = oracleCommand.ExecuteReader();
  128:  
  129:                 dataTable.Load(oracleDataReader);
  130:             }
  131:             catch (OracleException)
  132:             {
  133:                 dataTable = null; // very important, keep
  134:             }
  135:             catch (Exception)
  136:             {
  137:                 dataTable = null; // very important, keep
  138:             }
  139:             finally
  140:             {
  141:                 if (oracleDataReader != null) oracleDataReader.Dispose();
  142:  
  143:                 if (oracleCommand != null) oracleCommand.Dispose();
  144:             }
  145:  
  146:             return dataTable;
  147:         }
  148:  
  149:         ////////////////////////////////////////////////////////////////////////////
  150:  
  151:         /// <summary>
  152:         ///
  153:         /// </summary>
  154:         public string Scalar(string sql)
  155:         {
  156:             string s;
  157:             OracleCommand oracleCommand;
  158:  
  159:             using (OracleConnection oracleConnection = new OracleConnection(connectionString))
  160:             {
  161:                 oracleCommand = new OracleCommand(sql, oracleConnection);
  162:  
  163:                 oracleConnection.Open();
  164:  
  165:                 try
  166:                 {
  167:                     s = oracleCommand.ExecuteScalar().ToString();
  168:                 }
  169:                 catch
  170:                 {
  171:                     s = null;
  172:                 }
  173:             }
  174:  
  175:             return s;
  176:         }
  177:  
  178:         ////////////////////////////////////////////////////////////////////////////
  179:  
  180:         /// <summary>
  181:         ///
  182:         /// </summary>
  183:         public string Script(string script)
  184:         {
  185:             var s = "begin " + script + " end;";
  186:  
  187:             return Scalar(s);
  188:         }
  189:  
  190:         ////////////////////////////////////////////////////////////////////////////
  191:  
  192:         /// <summary>
  193:         ///
  194:         /// </summary>
  195:         public DataTable Desc(string tableName, string schemaName)
  196:         {
  197:             // see https://stackoverflow.com/questions/24393191/execute-a-desc-table-command-in-oracle-using-javascript
  198:  
  199:             string s;
  200:             DataTable dataTable;
  201:  
  202:             if (!string.IsNullOrEmpty(tableName))
  203:             {
  204:                 if (!string.IsNullOrEmpty(schemaName))
  205:                 {
  206:                     s = "select COLUMN_NAME as column_name, DATA_TYPE || '(' || DATA_LENGTH || ')' as datatype_length from all_tab_cols where table_name = upper('" + tableName + "') and owner = upper('" + schemaName + "')";
  207:                 }
  208:                 else s = "select COLUMN_NAME as column_name, DATA_TYPE || '(' || DATA_LENGTH || ')' as datatype_length from all_tab_cols where table_name = upper('" + tableName + "')";
  209:  
  210:                 dataTable = Select(s);
  211:                 dataTable.TableName = tableName;
  212:             }
  213:             else dataTable = null;
  214:  
  215:             return dataTable;
  216:         }
  217:  
  218:         ////////////////////////////////////////////////////////////////////////////
  219:  
  220:         /// <summary>
  221:         ///
  222:         /// </summary>
  223:         public DataTable Desc(string tableName)
  224:         {
  225:             return Desc(tableName, string.Empty);
  226:         }
  227:  
  228:         ////////////////////////////////////////////////////////////////////////////
  229:         ////////////////////////////////////////////////////////////////////////////
  230:     }
  231: }