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