Professional Applications Programmers/Consultants برمجة واستشارات تطبيقات الإنترنت
Skip Navigation LinksHome » Code Library » SqlServer

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

SQL Server support class.

   1:  using System;
   2:  using System.Configuration;
   3:  using System.Data;
   4:  using System.Data.SqlClient;
   5:   
   6:  namespace Ia.Cl.Model.Db
   7:  {
   8:      ////////////////////////////////////////////////////////////////////////////
   9:   
  10:      /// <summary publish="true">
  11:      /// SQL Server support class.
  12:      /// </summary>
  13:      /// <remarks> 
  14:      /// Copyright © 2001-2017 Jasem Y. Al-Shamlan (info@ia.com.kw), Internet 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 SqlServer
  27:      {
  28:          private string connectionString;
  29:   
  30:          ////////////////////////////////////////////////////////////////////////////
  31:   
  32:          /// <summary>
  33:          ///
  34:          /// </summary>
  35:          public SqlServer()
  36:          {
  37:              connectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
  38:          }
  39:   
  40:          ////////////////////////////////////////////////////////////////////////////
  41:   
  42:          /// <summary>
  43:          ///
  44:          /// </summary>
  45:          public SqlServer(string _connectionString)
  46:          {
  47:              connectionString = _connectionString;
  48:          }
  49:   
  50:          ////////////////////////////////////////////////////////////////////////////
  51:   
  52:          /// <summary>
  53:          ///
  54:          /// </summary>
  55:          public void Sql(string sql)
  56:          {
  57:              // execute an SQL command
  58:              SqlCommand sco;
  59:   
  60:              using (SqlConnection sc = new SqlConnection(connectionString))
  61:              {
  62:                  sco = new SqlCommand();
  63:   
  64:                  sco.CommandType = CommandType.Text; // default
  65:                  sco.CommandText = sql;
  66:                  sco.Connection = sc;
  67:   
  68:                  sc.Open();
  69:   
  70:                  sco.ExecuteNonQuery();
  71:                  sc.Close();
  72:              }
  73:          }
  74:   
  75:          ////////////////////////////////////////////////////////////////////////////
  76:   
  77:          /// <summary>
  78:          ///
  79:          /// </summary>
  80:          public DataTable Select(string sql)
  81:          {
  82:              SqlCommand sco;
  83:   
  84:              DataSet ds = new DataSet();
  85:              DataTable dt = new DataTable();
  86:              SqlDataAdapter da = new SqlDataAdapter();
  87:   
  88:              using (SqlConnection sc = new SqlConnection(connectionString))
  89:              {
  90:                  sco = new SqlCommand(sql, sc);
  91:   
  92:                  sc.Open();
  93:   
  94:                  da.SelectCommand = sco;
  95:   
  96:                  da.Fill(ds);
  97:   
  98:                  //sc.Close();
  99:   
 100:                  dt = ds.Tables[0];
 101:              }
 102:   
 103:              return dt;
 104:          }
 105:   
 106:          ////////////////////////////////////////////////////////////////////////////
 107:   
 108:          /// <summary>
 109:          /// Truncate a table, completely deleting it
 110:          /// </summary>
 111:          public void Truncate(string dataTableName)
 112:          {
 113:              Sql("truncate table " + dataTableName);
 114:          }
 115:   
 116:          ////////////////////////////////////////////////////////////////////////////
 117:   
 118:          /// <summary>
 119:          ///
 120:          /// </summary>
 121:          public void SqlBulkCopy(DataTable dataTable, out global::Ia.Cl.Model.Result result)
 122:          {
 123:              using (SqlConnection sc = new SqlConnection(connectionString))
 124:              {
 125:                  sc.Open();
 126:   
 127:                  // Note that the column positions in the source DataTable match the column positions in the destination table so there is no need to map columns. 
 128:                  using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sc))
 129:                  {
 130:                      result = new global::Ia.Cl.Model.Result();
 131:   
 132:                      if (dataTable != null && dataTable.Rows.Count > 0)
 133:                      {
 134:                          bulkCopy.DestinationTableName = dataTable.TableName;
 135:   
 136:                          try
 137:                          {
 138:                              bulkCopy.WriteToServer(dataTable);
 139:   
 140:                              result.AddSuccess("DataTable copied: " + dataTable.TableName + " had " + dataTable.Rows.Count + " record(s). ");
 141:                          }
 142:                          catch (Exception ex)
 143:                          {
 144:                              result.AddError(ex.Message);
 145:                          }
 146:                      }
 147:                      else
 148:                      {
 149:                          result.AddWarning("DataTable " + dataTable.TableName + " is null or row count is zero. ");
 150:                      }
 151:                  }
 152:              }
 153:          }
 154:   
 155:          ////////////////////////////////////////////////////////////////////////////
 156:   
 157:          /// <summary>
 158:          /// Return a DataTable of properties of tables that start with "ia_" 
 159:          /// </summary>
 160:          public DataTable Schema()
 161:          {
 162:              DataTable dt;
 163:   
 164:              dt = Select("SELECT TABLE_NAME + '_' + COLUMN_NAME AS id, TABLE_NAME, COLUMN_NAME, COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.COLUMNS WHERE (TABLE_NAME LIKE 'ia_%')");
 165:   
 166:              DataColumn[] keys = new DataColumn[1];
 167:              keys[0] = dt.Columns["id"];
 168:              dt.PrimaryKey = keys;
 169:   
 170:              return dt;
 171:          }
 172:   
 173:          ////////////////////////////////////////////////////////////////////////////
 174:   
 175:          /// <summary>
 176:          ///
 177:          /// </summary>
 178:          public string Scalar(string sql)
 179:          {
 180:              string s;
 181:              SqlCommand sco;
 182:   
 183:              using (SqlConnection sc = new SqlConnection(connectionString))
 184:              {
 185:                  sco = new SqlCommand(sql, sc);
 186:   
 187:                  sc.Open();
 188:   
 189:                  try { s = sco.ExecuteScalar().ToString(); }
 190:                  catch { s = null; }
 191:   
 192:                  //sc.Close();
 193:              }
 194:   
 195:              return s;
 196:          }
 197:   
 198:          ////////////////////////////////////////////////////////////////////////////
 199:   
 200:          /// <summary>
 201:          ///
 202:          /// </summary>
 203:          public int ScalarInteger(string sql)
 204:          {
 205:              // return a DataTable of result rows
 206:              int n;
 207:              SqlCommand sco;
 208:   
 209:              using (SqlConnection sc = new SqlConnection(connectionString))
 210:              {
 211:                  sco = new SqlCommand(sql, sc);
 212:   
 213:                  sc.Open();
 214:   
 215:                  try
 216:                  {
 217:                      n = (System.Int32)sco.ExecuteScalar();
 218:                  }
 219:                  catch (Exception)
 220:                  {
 221:                      n = 0;
 222:                  }
 223:   
 224:                  //sc.Close();
 225:              }
 226:   
 227:              return n;
 228:          }
 229:   
 230:          ////////////////////////////////////////////////////////////////////////////
 231:   
 232:          /// <summary>
 233:          ///
 234:          /// </summary>
 235:          public int ScalarSmallInteger(string sql)
 236:          {
 237:              // return a DataTable of result rows
 238:              int n;
 239:              SqlCommand sco;
 240:   
 241:              using (SqlConnection sc = new SqlConnection(connectionString))
 242:              {
 243:                  sco = new SqlCommand(sql, sc);
 244:   
 245:                  sc.Open();
 246:   
 247:                  try
 248:                  {
 249:                      n = (System.Int16)sco.ExecuteScalar();
 250:                  }
 251:                  catch (Exception)
 252:                  {
 253:                      n = 0;
 254:                  }
 255:   
 256:                  //sc.Close();
 257:              }
 258:   
 259:              return n;
 260:          }
 261:   
 262:          ////////////////////////////////////////////////////////////////////////////
 263:   
 264:          /// <summary>
 265:          ///
 266:          /// </summary>
 267:          public string SmallDateTime(DateTime dt)
 268:          {
 269:              // return an SQL Server friendly string of a smalldatetime value
 270:              string s;
 271:   
 272:              //s = "'" + dt.ToString("yyyy-MM-ddTHH:mm:ss") + "'";
 273:              s = dt.ToString("yyyy-MM-ddTHH:mm:ss");
 274:   
 275:              return s;
 276:          }
 277:   
 278:          ////////////////////////////////////////////////////////////////////////////
 279:   
 280:          /// <summary>
 281:          ///
 282:          /// </summary>
 283:          public bool ImportXml(string tableName, string file)
 284:          {
 285:              bool b;
 286:              int i;
 287:              string c, path;
 288:              DataSet ds;
 289:              SqlDataAdapter sda;
 290:              SqlCommandBuilder scb;
 291:              DataRow dr;
 292:   
 293:              ds = new DataSet("ia_ngn");
 294:   
 295:              path = global::Ia.Cl.Model.Default.AbsolutePath();
 296:   
 297:              file = path + file;
 298:   
 299:              ds.ReadXml(file);
 300:   
 301:              using (SqlConnection sc = new SqlConnection(connectionString))
 302:              {
 303:                  sc.Open();
 304:   
 305:                  c = @"SELECT * FROM [" + tableName + @"]";
 306:                  sda = new SqlDataAdapter(c, sc);
 307:                  scb = new SqlCommandBuilder(sda);
 308:   
 309:                  try
 310:                  {
 311:                      sda.Fill(ds, tableName);
 312:   
 313:                      foreach (DataRow r in ds.Tables[tableName].Rows)
 314:                      {
 315:                          dr = ds.Tables[tableName].NewRow();
 316:   
 317:                          for (i = 0; i < r.ItemArray.Length; i++) dr[i] = r[i];
 318:   
 319:                          ds.Tables[0].Rows.Add(dr);
 320:                      }
 321:   
 322:                      scb.GetUpdateCommand();
 323:                      sda.Update(ds, tableName);
 324:   
 325:                      b = true;
 326:                  }
 327:                  catch (Exception) { b = false; }
 328:                  finally
 329:                  {
 330:                      //sc.Close();
 331:                  }
 332:              }
 333:   
 334:              return b;
 335:          }
 336:   
 337:          ////////////////////////////////////////////////////////////////////////////
 338:   
 339:          /// <summary>
 340:          ///
 341:          /// </summary>
 342:          public bool ExportXml(string tableName, string file)
 343:          {
 344:              // perform dump or backup of database table data into an XML document
 345:              bool b;
 346:              string c, path;
 347:              DataSet ds;
 348:              SqlDataAdapter sda;
 349:   
 350:              c = @"SELECT * FROM [" + tableName + @"]";
 351:   
 352:              using (SqlConnection sc = new SqlConnection(connectionString))
 353:              {
 354:                  sc.Open();
 355:   
 356:                  ds = new DataSet("ia_ngn");
 357:                  sda = new SqlDataAdapter(c, sc);
 358:   
 359:                  try
 360:                  {
 361:                      sda.Fill(ds, tableName);
 362:   
 363:                      path = global::Ia.Cl.Model.Default.AbsolutePath();
 364:   
 365:                      file = path + file;
 366:                      ds.WriteXml(file, XmlWriteMode.WriteSchema);
 367:   
 368:                      b = true;
 369:                  }
 370:                  catch (Exception) { b = false; }
 371:                  finally
 372:                  {
 373:                      //sc.Close();
 374:                  }
 375:              }
 376:   
 377:              return b;
 378:          }
 379:   
 380:          ////////////////////////////////////////////////////////////////////////////
 381:          ////////////////////////////////////////////////////////////////////////////
 382:      }
 383:  }