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"].ToString();
39:
40: oracleConnection = new OracleConnection();
41:
42: oracleConnection.ConnectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString();
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: }