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

Integrated Applications Programming Company

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