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

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