شركة التطبيقات المتكاملة لتصميم النظم البرمجية الخاصة ش.ش.و.

Integrated Applications Programming Company

Skip Navigation LinksHome » Code Library » Oracle

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.Model.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:  }