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

Integrated Applications Programming Company

Skip Navigation LinksHome » Code Library » Sqlite

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

SQLite support class.

   1:  using System;
   2:  using System.Collections.Generic;
   3:  using System.Configuration;
   4:  using System.Data;
   5:  using System.Data.SQLite;
   6:  using System.Linq;
   7:   
   8:  namespace Ia.Cl.Model.Db
   9:  {
  10:      ////////////////////////////////////////////////////////////////////////////
  11:   
  12:      /// <summary publish="true">
  13:      /// SQLite support class.
  14:      /// </summary>
  15:      /// <remarks>
  16:      /// Copyright © 2008-2016 Jasem Y. Al-Shamlan (info@ia.com.kw), Integrated Applications - Kuwait. All Rights Reserved.
  17:      ///
  18:      /// 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
  19:      /// the Free Software Foundation, either version 3 of the License, or (at your option) any later version.
  20:      ///
  21:      /// This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of
  22:      /// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.
  23:      /// 
  24:      /// You should have received a copy of the GNU General Public License along with this library. If not, see http://www.gnu.org/licenses.
  25:      /// 
  26:      /// Copyright notice: This notice may not be removed or altered from any source distribution.
  27:      /// </remarks>
  28:   
  29:      public class Sqlite
  30:      {
  31:          private static string connectionString;
  32:   
  33:          ////////////////////////////////////////////////////////////////////////////
  34:   
  35:          /// <summary>
  36:          ///
  37:          /// </summary>
  38:          public Sqlite()
  39:          {
  40:              connectionString = ConfigurationManager.ConnectionStrings["SqLiteConnectionString"].ConnectionString;
  41:          }
  42:   
  43:          ////////////////////////////////////////////////////////////////////////////
  44:   
  45:          /// <summary>
  46:          ///
  47:          /// </summary>
  48:          public Sqlite(string dataSource)
  49:          {
  50:              string version;
  51:   
  52:              version = "3";
  53:   
  54:              connectionString = "Data Source=" + dataSource + ";Version=" + version + ";";
  55:          }
  56:   
  57:          /*
  58:          ////////////////////////////////////////////////////////////////////////////
  59:  
  60:          /// <summary>
  61:          ///
  62:          /// </summary>
  63:          public Sqlite(string dataSource, string version)
  64:          {
  65:              version = "3";
  66:  
  67:              connectionString = "Data Source=" + dataSource + ";Version=" + version + ";";
  68:          }
  69:  
  70:          ////////////////////////////////////////////////////////////////////////////
  71:  
  72:          /// <summary>
  73:          ///
  74:          /// </summary>
  75:          public Sqlite(string dataSource, string version, string password)
  76:          {
  77:              version = "3";
  78:  
  79:  
  80:              connectionString = "Data Source=" + dataSource + ";Version=" + version + ";Password=" + password + ";";
  81:          }
  82:          */
  83:   
  84:          ////////////////////////////////////////////////////////////////////////////
  85:          ////////////////////////////////////////////////////////////////////////////
  86:   
  87:          /// <summary>
  88:          ///
  89:          /// </summary>
  90:          public void Sql(string sql)
  91:          {
  92:              SQLiteCommand sco;
  93:   
  94:              using (var sc = new SQLiteConnection(connectionString))
  95:              {
  96:                  sc.Open();
  97:   
  98:                  sco = new SQLiteCommand(sql, sc);
  99:                  sco.CommandType = CommandType.Text; // default
 100:   
 101:                  sco.ExecuteNonQuery();
 102:                  //sc.Close();
 103:              }
 104:   
 105:              GC.Collect();
 106:          }
 107:   
 108:          ////////////////////////////////////////////////////////////////////////////
 109:   
 110:          /// <summary>
 111:          ///
 112:          /// </summary>
 113:          public void Sql(List<string> sqlList)
 114:          {
 115:              SQLiteCommand sco;
 116:   
 117:              using (var sc = new SQLiteConnection(connectionString))
 118:              {
 119:                  sc.Open();
 120:   
 121:                  foreach (string sql in sqlList)
 122:                  {
 123:                      sco = new SQLiteCommand(sql, sc);
 124:                      sco.CommandType = CommandType.Text; // default
 125:   
 126:                      sco.ExecuteNonQuery();
 127:                  }
 128:   
 129:                  //sc.Close();
 130:              }
 131:   
 132:              GC.Collect();
 133:          }
 134:   
 135:          ////////////////////////////////////////////////////////////////////////////
 136:   
 137:          /// <summary>
 138:          /// Copy dataTable values to SQLite database. Not that we will change all Id primary keys to _id
 139:          /// </summary>
 140:          public int SqlBulkCopy(DataTable dataTable, out global::Ia.Cl.Model.Result result)
 141:          {
 142:              var results = new List<int>();
 143:   
 144:              result = new global::Ia.Cl.Model.Result();
 145:   
 146:              using (SQLiteConnection sc = new SQLiteConnection(connectionString))
 147:              {
 148:                  sc.Open();
 149:   
 150:                  using (var transaction = sc.BeginTransaction())
 151:                  {
 152:                      using (var cmd = sc.CreateCommand())
 153:                      {
 154:                          if (dataTable != null && dataTable.Rows.Count > 0)
 155:                          {
 156:                              cmd.CommandText = CommandText(dataTable);
 157:   
 158:                              if (!string.IsNullOrEmpty(cmd.CommandText))
 159:                              {
 160:                                  foreach (DataRow dr in dataTable.Rows)
 161:                                  {
 162:                                      foreach (DataColumn dc in dataTable.Columns)
 163:                                      {
 164:                                          // cmd.Parameters["@Name"] = user.Name;
 165:   
 166:                                          // For Andriod, the database tables should use the identifier _id for the primary key of the table.Several Android functions rely on this standard.
 167:                                          if (dc.ColumnName == "Id") cmd.Parameters.AddWithValue("@_id", dr[dc.ColumnName]);
 168:                                          else cmd.Parameters.AddWithValue("@" + dc.ColumnName, dr[dc.ColumnName]);
 169:                                      }
 170:   
 171:                                      results.Add(cmd.ExecuteNonQuery());
 172:                                  }
 173:   
 174:                                  result.AddSuccess("DataTable: " + dataTable.TableName + " had " + dataTable.Rows.Count + " records. ");
 175:                              }
 176:                              else
 177:                              {
 178:                                  result.AddError("CommandText is null. ");
 179:                              }
 180:                          }
 181:                          else
 182:                          {
 183:                              result.AddError("Data table: " + dataTable.TableName + " is null or row count is zero. ");
 184:                          }
 185:                      }
 186:   
 187:                      transaction.Commit();
 188:                  }
 189:              }
 190:   
 191:              GC.Collect();
 192:   
 193:              return results.Sum();
 194:          }
 195:   
 196:          ////////////////////////////////////////////////////////////////////////////
 197:   
 198:          private string CommandText(DataTable dataTable)
 199:          {
 200:              string s, names, values;
 201:   
 202:              if (dataTable != null && dataTable.Columns.Count > 0)
 203:              {
 204:                  names = values = string.Empty;
 205:   
 206:                  foreach (DataColumn dc in dataTable.Columns)
 207:                  {
 208:                      // ([Name]) VALUES (@Name);"
 209:                      if (dc.ColumnName == "Id")
 210:                      {
 211:                          // For Andriod, the database tables should use the identifier _id for the primary key of the table.Several Android functions rely on this standard.
 212:                          names += "[_id],";
 213:                          values += "@_id,";
 214:                      }
 215:                      else
 216:                      {
 217:                          names += "[" + dc.ColumnName + "],";
 218:                          values += "@" + dc.ColumnName + ",";
 219:                      }
 220:                  }
 221:   
 222:                  if (!string.IsNullOrEmpty(names) && !string.IsNullOrEmpty(values))
 223:                  {
 224:                      names = names.Remove(names.Length - 1, 1); // remove last ','
 225:                      values = values.Remove(values.Length - 1, 1); // remove last ','
 226:   
 227:                      s = @"insert into [" + dataTable.TableName + "] (" + names + ") VALUES (" + values + ");";
 228:                  }
 229:                  else s = null;
 230:              }
 231:              else s = null;
 232:   
 233:              return s;
 234:          }
 235:   
 236:          ////////////////////////////////////////////////////////////////////////////
 237:          ////////////////////////////////////////////////////////////////////////////
 238:   
 239:          /// <summary>
 240:          ///
 241:          /// </summary>
 242:          public DataTable Select(string sql)
 243:          {
 244:              DataSet dataSet;
 245:              DataTable dataTable;
 246:              SQLiteCommand sco;
 247:              SQLiteDataAdapter da;
 248:   
 249:              dataTable = null;
 250:   
 251:              dataSet = new DataSet();
 252:              dataTable = new DataTable();
 253:              da = new SQLiteDataAdapter();
 254:   
 255:              using (var sc = new SQLiteConnection(connectionString))
 256:              {
 257:                  sc.Open();
 258:   
 259:                  sco = new SQLiteCommand(sql, sc);
 260:                  sco.CommandType = CommandType.Text; // default
 261:   
 262:                  da.SelectCommand = sco;
 263:                  da.Fill(dataSet);
 264:   
 265:                  dataTable = dataSet.Tables[0];
 266:                  //sc.Close();
 267:              }
 268:   
 269:              GC.Collect();
 270:   
 271:              return dataTable;
 272:          }
 273:   
 274:          ////////////////////////////////////////////////////////////////////////////
 275:   
 276:          /// <summary>
 277:          ///
 278:          /// </summary>
 279:          public static string DateTimeFormat(DateTime dateTime)
 280:          {
 281:              // below: return an SQLite friendly string without "'"
 282:              string s;
 283:   
 284:              s = dateTime.ToString("yyyy-MM-dd HH:mm:ss"); // '2007-01-01 10:00:00'
 285:   
 286:              return s;
 287:          }
 288:   
 289:          ////////////////////////////////////////////////////////////////////////////
 290:   
 291:          /// <summary>
 292:          ///
 293:          /// </summary>
 294:          public string Scalar(string sql)
 295:          {
 296:              string scalar;
 297:              DataSet ds;
 298:              DataTable dt;
 299:              SQLiteCommand sco;
 300:              SQLiteDataAdapter da;
 301:   
 302:              dt = null;
 303:   
 304:              ds = new DataSet();
 305:              dt = new DataTable();
 306:              da = new SQLiteDataAdapter();
 307:   
 308:              using (var sc = new SQLiteConnection(connectionString))
 309:              {
 310:                  sc.Open();
 311:   
 312:                  sco = new SQLiteCommand(sql, sc);
 313:                  sco.CommandType = CommandType.Text; // default
 314:   
 315:                  try { scalar = sco.ExecuteScalar().ToString(); }
 316:                  catch { scalar = null; }
 317:   
 318:                  //sc.Close();
 319:              }
 320:   
 321:              GC.Collect();
 322:   
 323:              return scalar;
 324:          }
 325:   
 326:          ////////////////////////////////////////////////////////////////////////////
 327:   
 328:          /// <summary>
 329:          /// Delete contents of a table.
 330:          /// </summary>
 331:          public void Delete(string dataTableName)
 332:          {
 333:              Sql("delete from table " + dataTableName);
 334:          }
 335:   
 336:          /*
 337:          ////////////////////////////////////////////////////////////////////////////
 338:  
 339:          /// <summary>
 340:          ///
 341:          /// </summary>
 342:          public int IntegerScalar(string sql)
 343:          {
 344:              // below: return a DataTable of result rows
 345:              int n;
 346:  
 347:              SQLiteConnection sc = null;
 348:              SQLiteCommand sco;
 349:  
 350:              sc = new SQLiteConnection(connectionString);
 351:              sco = new SQLiteCommand(sql, sc);
 352:  
 353:              sc.Open();
 354:  
 355:              try
 356:              {
 357:                  n = (System.Int32)sco.ExecuteScalar();
 358:              }
 359:              catch (Exception)
 360:              {
 361:                  n = 0;
 362:              }
 363:  
 364:              sc.Close();
 365:  
 366:              return n;
 367:          }
 368:  
 369:          ////////////////////////////////////////////////////////////////////////////
 370:  
 371:          /// <summary>
 372:          ///
 373:          /// </summary>
 374:          public int SmallIntegerScalar(string sql)
 375:          {
 376:              // below: return a DataTable of result rows
 377:              int n;
 378:  
 379:              SQLiteConnection sc = null;
 380:              SQLiteCommand sco;
 381:  
 382:              sc = new SQLiteConnection(connectionString);
 383:              sco = new SQLiteCommand(sql, sc);
 384:  
 385:              sc.Open();
 386:  
 387:              try
 388:              {
 389:                  n = (System.Int16)sco.ExecuteScalar();
 390:              }
 391:              catch (Exception)
 392:              {
 393:                  n = 0;
 394:              }
 395:  
 396:              sc.Close();
 397:  
 398:              return n;
 399:          }
 400:          */
 401:   
 402:          ////////////////////////////////////////////////////////////////////////////
 403:          ////////////////////////////////////////////////////////////////////////////
 404:   
 405:          // Create database file
 406:          //SQLiteConnection.CreateFile(dataSource); not needed see below
 407:   
 408:          /*
 409:  
 410:          
 411:                      string password, sql, dataSource;
 412:              DataTable dt;
 413:              SQLiteCommand command;
 414:              SQLiteDataReader reader;
 415:              Ia.Cl.Model.Db.Sqlite s;
 416:              List<string> sqlList;
 417:  
 418:              password = "00000";
 419:              dataSource = "2.sqlite";
 420:  
 421:              s = new Ia.Cl.Model.Db.Sqlite(dataSource);
 422:              sqlList = new List<string>();
 423:  
 424:              sql = "drop table if exists highscores";
 425:              s.Sql(sql);
 426:  
 427:              sql = "create table highscores (name varchar(20), score int)";
 428:              s.Sql(sql);
 429:  
 430:              sql = "insert into highscores (name, score) values ('Me', 3000)";
 431:              s.Sql(sql);
 432:  
 433:              sqlList.Add("insert into highscores (name, score) values ('Me', 1)");
 434:              sqlList.Add("insert into highscores (name, score) values ('Me', 2)");
 435:              sqlList.Add("insert into highscores (name, score) values ('Me', 3)");
 436:              sqlList.Add("insert into highscores (name, score) values ('Me', 4)");
 437:              sqlList.Add("insert into highscores (name, score) values ('Me', 5)");
 438:              sqlList.Add("insert into highscores (name, score) values ('Me', 6)");
 439:              sqlList.Add("insert into highscores (name, score) values ('Me', 7)");
 440:              sqlList.Add("insert into highscores (name, score) values ('Me', 8)");
 441:              sqlList.Add("insert into highscores (name, score) values ('Me', 9)");
 442:              sqlList.Add("insert into highscores (name, score) values ('Me', 1)");
 443:              sqlList.Add("insert into highscores (name, score) values ('Me', 2)");
 444:              sqlList.Add("insert into highscores (name, score) values ('Me', 3)");
 445:  
 446:              s.Sql(sqlList);
 447:  
 448:              sql = "select * from highscores order by score desc";
 449:              dt = s.Select(sql);
 450:              
 451:          
 452:          
 453:          
 454:          
 455:              using (var connection = new SQLiteConnection("Data Source="+ dataSource + ";Version=3;Password=" + password + ";"))
 456:          {
 457:              connection.Open();
 458:  
 459:              // create
 460:              sql = "create table highscores (name varchar(20), score int)";
 461:              command = new SQLiteCommand(sql, connection);
 462:              command.ExecuteNonQuery();
 463:  
 464:              // insert
 465:              sql = "insert into highscores (name, score) values ('And I', 9001)";
 466:              command = new SQLiteCommand(sql, connection);
 467:              command.ExecuteNonQuery();
 468:  
 469:              // select
 470:              sql = "select * from highscores order by score desc";
 471:              command = new SQLiteCommand(sql, connection);
 472:              reader = command.ExecuteReader();
 473:  
 474:              while (reader.Read())
 475:                  Console.WriteLine("Name: " + reader["name"] + "\tScore: " + reader["score"]);
 476:  
 477:              // connection.Close(); not needed in a using() block
 478:          }
 479:          */
 480:      }
 481:  }