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