)>}]
شركة التطبيقات المتكاملة لتصميم وبرمجة البرمجيات الخاصة ش.ش.و.
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"].ToString();
   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 Ia.Cl.Model.Result result)
  141:         {
  142:             var results = new List<int>();
  143:  
  144:             result = new 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: }