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

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

OLEDB support class

    1: using System;
    2: using System.Configuration;
    3: using System.Data;
    4: using System.Data.OleDb;
    5:  
    6: namespace Ia.Cl.Models.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: }