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

Integrated Applications Programming Company

Skip Navigation LinksHome » Code Library » OleDb

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