1: using System;
2: using System.Configuration;
3: using System.Data;
4: using System.Data.OleDb;
5:
6: namespace Ia.Cl.Model.Db
7: {
8: ////////////////////////////////////////////////////////////////////////////
9:
10: /// <summary publish="true">
11: /// OLEDB support class
12: /// </summary>
13: /// <remarks>
14: /// Copyright © 2001-2020 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 OleDb
27: {
28: private string connectionString;
29:
30: ////////////////////////////////////////////////////////////////////////////
31:
32: /// <summary>
33: ///
34: /// </summary>
35: public OleDb()
36: {
37: connectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
38: }
39:
40: ////////////////////////////////////////////////////////////////////////////
41:
42: /// <summary>
43: ///
44: /// </summary>
45: public OleDb(string _connectionString)
46: {
47: connectionString = _connectionString;
48: }
49:
50: ////////////////////////////////////////////////////////////////////////////
51:
52: /// <summary>
53: /// Return a DataTable of result rows
54: /// </summary>
55: public bool Sql(string sql)
56: {
57: return Sql(sql, false, connectionString);
58: }
59:
60: ////////////////////////////////////////////////////////////////////////////
61:
62: /// <summary>
63: ///
64: /// </summary>
65: public bool Sql(string sql, bool changeEmptyStringsWithSingleQuotesToNull)
66: {
67: return Sql(sql, changeEmptyStringsWithSingleQuotesToNull, connectionString);
68: }
69:
70: ////////////////////////////////////////////////////////////////////////////
71:
72: /// <summary>
73: ///
74: /// </summary>
75: public bool Sql(string sql, string connectionString)
76: {
77: return Sql(sql, false, connectionString);
78: }
79:
80: ////////////////////////////////////////////////////////////////////////////
81:
82: /// <summary>
83: /// Execute and SQL command
84: /// </summary>
85: /// <param name="sql">SQL string</param>
86: /// <param name="changeEmptyStringsWithSingleQuotesToNull">Indicator weather single quotes '' should be replaced with NULL string</param>
87: /// <param name="connectionString">connection string</param>
88: /// <returns>Boolean</returns>
89: public bool Sql(string sql, bool changeEmptyStringsWithSingleQuotesToNull, string connectionString)
90: {
91: bool b;
92: OleDbCommand oleDbCommand;
93:
94: b = true;
95:
96: if (changeEmptyStringsWithSingleQuotesToNull) sql = sql.Replace("''", "NULL");
97:
98: using (OleDbConnection oleDbConnection = new OleDbConnection(connectionString))
99: {
100: oleDbCommand = new OleDbCommand();
101:
102: oleDbCommand.CommandType = CommandType.Text; // default
103: oleDbCommand.CommandText = sql;
104: oleDbCommand.Connection = oleDbConnection;
105:
106: oleDbConnection.Open();
107: oleDbCommand.ExecuteNonQuery();
108: //odc.Close();
109: }
110:
111: return b;
112: }
113:
114: ////////////////////////////////////////////////////////////////////////////
115:
116: /// <summary>
117: /// Return a DataTable of result rows
118: /// </summary>
119: public DataTable Select(string sql)
120: {
121: return Select(sql, connectionString);
122: }
123:
124: ////////////////////////////////////////////////////////////////////////////
125:
126: /// <summary>
127: /// Return a DataTable of result rows
128: /// </summary>
129: public DataTable Select(string sql, string connectionString)
130: {
131: DataTable dataTable;
132: DataSet dataSet;
133: OleDbCommand oleDbCommand;
134: OleDbDataAdapter oleDbDataAdapter;
135:
136: dataSet = new DataSet();
137: dataTable = new DataTable();
138: oleDbDataAdapter = new OleDbDataAdapter();
139:
140: using (OleDbConnection oleDbConnection = new OleDbConnection(connectionString))
141: {
142: oleDbCommand = new OleDbCommand(sql, oleDbConnection);
143:
144: oleDbConnection.Open();
145:
146: oleDbDataAdapter.SelectCommand = oleDbCommand;
147:
148: oleDbDataAdapter.Fill(dataSet);
149:
150: //odc.Close();
151: }
152:
153: try
154: {
155: dataTable = dataSet.Tables[0];
156: }
157: catch (Exception)
158: {
159: dataTable = new DataTable();
160: }
161:
162: return dataTable;
163: }
164:
165: ////////////////////////////////////////////////////////////////////////////
166:
167: /// <summary>
168: /// Execute SQL and return a scalar.
169: /// </summary>
170: /// <param name="sql"></param>
171: /// <returns>string</returns>
172: public string Scalar(string sql)
173: {
174: string s;
175:
176: s = string.Empty;
177:
178: OleDbCommand oleDbCommand;
179:
180: using (OleDbConnection oleDbConnection = new OleDbConnection(connectionString))
181: {
182: oleDbCommand = new OleDbCommand();
183:
184: oleDbCommand.CommandType = CommandType.Text; // default
185: oleDbCommand.CommandText = sql;
186: oleDbCommand.Connection = oleDbConnection;
187: oleDbConnection.Open();
188:
189: try
190: {
191: s = oleDbCommand.ExecuteScalar().ToString();
192: }
193: catch (Exception)
194: {
195: s = string.Empty;
196: }
197:
198: //odc.Close();
199: }
200:
201: return s;
202: }
203:
204: ////////////////////////////////////////////////////////////////////////////
205:
206: /// <summary>
207: /// Return an OleDb friendly string of a smalldatetime value
208: /// </summary>
209: public string SmallDateTime(DateTime dateTime)
210: {
211: string s;
212:
213: s = dateTime.ToString("MM/dd/yyyy HH:mm:ss");
214:
215: return s;
216: }
217:
218: ////////////////////////////////////////////////////////////////////////////
219: ////////////////////////////////////////////////////////////////////////////
220: }
221: }