Professional Applications Programmers/Consultants برمجة واستشارات تطبيقات الإنترنت
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.Web;
   3:  using System.Xml;
   4:  using System.Configuration;
   5:  using System.Data;
   6:  using System.Data.OleDb;
   7:  using System.Collections;
   8:  using System.Text;
   9:   
  10:  namespace Ia.Cl.Model.Db
  11:  {
  12:      ////////////////////////////////////////////////////////////////////////////
  13:   
  14:      /// <summary publish="true">
  15:      /// OLEDB support class
  16:      /// </summary>
  17:      /// <remarks> 
  18:      /// Copyright © 2001-2015 Jasem Y. Al-Shamlan (info@ia.com.kw), Internet Applications - Kuwait. All Rights Reserved.
  19:      ///
  20:      /// 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
  21:      /// the Free Software Foundation, either version 3 of the License, or (at your option) any later version.
  22:      ///
  23:      /// This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of
  24:      /// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.
  25:      /// 
  26:      /// You should have received a copy of the GNU General Public License along with this library. If not, see http://www.gnu.org/licenses.
  27:      /// 
  28:      /// Copyright notice: This notice may not be removed or altered from any source distribution.
  29:      /// </remarks> 
  30:      public class OleDb
  31:      {
  32:          ////////////////////////////////////////////////////////////////////////////
  33:   
  34:          /// <summary>
  35:          ///
  36:          /// </summary>
  37:          public OleDb() { }
  38:   
  39:          ////////////////////////////////////////////////////////////////////////////
  40:   
  41:          /// <summary>
  42:          ///
  43:          /// </summary>
  44:          public static bool Sql(string sql)
  45:          {
  46:              // return a DataTable of result rows
  47:              return Sql(sql, false, ConfigurationManager.ConnectionStrings["OledbConnectionString"].ConnectionString);
  48:          }
  49:   
  50:          ////////////////////////////////////////////////////////////////////////////
  51:   
  52:          /// <summary>
  53:          ///
  54:          /// </summary>
  55:          public static bool Sql(string sql, bool empty_string_single_quote_to_null)
  56:          {
  57:              // 
  58:              return Sql(sql, empty_string_single_quote_to_null, ConfigurationManager.ConnectionStrings["OledbConnectionString"].ConnectionString);
  59:          }
  60:   
  61:   
  62:          ////////////////////////////////////////////////////////////////////////////
  63:   
  64:          /// <summary>
  65:          ///
  66:          /// </summary>
  67:          public static bool Sql(string sql, string connection_string)
  68:          {
  69:              return Sql(sql, false, connection_string);
  70:          }
  71:   
  72:          ////////////////////////////////////////////////////////////////////////////
  73:   
  74:          /// <summary>
  75:          /// Execute and SQL command
  76:          /// </summary>
  77:          /// <param name="sql">SQL string</param>
  78:          /// <param name="empty_string_single_quote_to_null">Indicator weather single quotes '' should be replaced with NULL string</param>
  79:          /// <param name="connection_string">connection string</param>
  80:          /// <returns>Boolean</returns>
  81:          public static bool Sql(string sql, bool empty_string_single_quote_to_null, string connection_string)
  82:          {
  83:              // execute an SQL command
  84:              bool b = true;
  85:              OleDbCommand odco;
  86:   
  87:              if (empty_string_single_quote_to_null) sql = sql.Replace("''", "NULL");
  88:   
  89:              using (OleDbConnection odc = new OleDbConnection(connection_string))
  90:              {
  91:                  odco = new OleDbCommand();
  92:   
  93:                  odco.CommandType = CommandType.Text; // default
  94:                  odco.CommandText = sql;
  95:                  odco.Connection = odc;
  96:   
  97:                  odc.Open();
  98:                  odco.ExecuteNonQuery();
  99:                  //odc.Close();
 100:              }
 101:   
 102:              return b;
 103:          }
 104:   
 105:          ////////////////////////////////////////////////////////////////////////////
 106:   
 107:          /// <summary>
 108:          ///
 109:          /// </summary>
 110:          public static DataTable Select(string sql)
 111:          {
 112:              // return a DataTable of result rows
 113:              return Select(sql, ConfigurationManager.ConnectionStrings["OledbConnectionString"].ConnectionString);
 114:          }
 115:   
 116:          ////////////////////////////////////////////////////////////////////////////
 117:   
 118:          /// <summary>
 119:          ///
 120:          /// </summary>
 121:          public static DataTable Select(string sql, string connectionString)
 122:          {
 123:              // return a DataTable of result rows
 124:              OleDbCommand odco;
 125:   
 126:              DataSet ds = new DataSet();
 127:              DataTable dt = new DataTable();
 128:              OleDbDataAdapter da = new OleDbDataAdapter();
 129:   
 130:              using (OleDbConnection odc = new OleDbConnection(connectionString))
 131:              {
 132:                  odco = new OleDbCommand(sql, odc);
 133:   
 134:                  odc.Open();
 135:   
 136:                  da.SelectCommand = odco;
 137:   
 138:                  da.Fill(ds);
 139:   
 140:                  //odc.Close();
 141:              }
 142:   
 143:              try { dt = ds.Tables[0]; }
 144:              catch { dt = null; }
 145:   
 146:              return dt;
 147:          }
 148:   
 149:          ////////////////////////////////////////////////////////////////////////////
 150:   
 151:          /// <summary>
 152:          /// Execute SQL and return a scalar.
 153:          /// </summary>
 154:          /// <param name="sql"></param>
 155:          /// <returns>string</returns>
 156:          public static string Scalar(string sql)
 157:          {
 158:              // return a scalar
 159:              string s;
 160:   
 161:              s = null;
 162:   
 163:              OleDbCommand odco;
 164:   
 165:              using (OleDbConnection odc = new OleDbConnection(ConfigurationManager.ConnectionStrings["OledbConnectionString"].ConnectionString))
 166:              {
 167:                  odco = new OleDbCommand();
 168:   
 169:                  odco.CommandType = CommandType.Text; // default
 170:                  odco.CommandText = sql;
 171:                  odco.Connection = odc;
 172:                  odc.Open();
 173:   
 174:                  try { s = odco.ExecuteScalar().ToString(); }
 175:                  catch { s = null; }
 176:   
 177:                  //odc.Close();
 178:              }
 179:   
 180:              return s;
 181:          }
 182:   
 183:          ////////////////////////////////////////////////////////////////////////////
 184:   
 185:          /// <summary>
 186:          ///
 187:          /// </summary>
 188:          public static string SmallDateTime(DateTime dt)
 189:          {
 190:              // return an OleDb friendly string of a smalldatetime value
 191:              string s;
 192:   
 193:              //s = "'" + dt.ToString("dd/MM/yyyy HH:mm:ss") + "'";
 194:              //s = dt.ToString("dd/MM/yyyy HH:mm:ss");
 195:              s = dt.ToString("MM/dd/yyyy HH:mm:ss");
 196:   
 197:              return s;
 198:          }
 199:   
 200:          ////////////////////////////////////////////////////////////////////////////
 201:          ////////////////////////////////////////////////////////////////////////////
 202:      }
 203:  }