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

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.Models.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 = Ia.Cl.Models.ApplicationConfiguration.GetSetting("ConnectionStrings:DefaultConnection");
   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.Models.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.Models.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.Models.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.Models.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: }