Click here to Skip to main content
15,867,453 members
Articles / Programming Languages / C#

SQLite Helper (C#)

Rate me:
Please Sign up or sign in to vote.
4.79/5 (82 votes)
28 Mar 2014Public Domain5 min read 247.2K   12.9K   212   44
Simplify the usage between C# and SQLite

Introduction

SQLite is an open source, embed, cross platform (Windows, IOS, Android, Linux) database engine. It requires no installation and zero configuration in order to work at client's computer.

I have written a small class, SQLiteHelper which aims to simplify the usage of SQLite in C#.

Prerequisite

This small class (SQLiteHelper.cs) is built on top of System.Data.SQLite.DLL. A reference of this DLL must be added into your projects.

Download: https://system.data.sqlite.org

Change Log

27 March 2014 - V1.2

  • Added parameter support for "Select", "Execute" and "ExecuteScalar" methods.

22 March 2014 - V1.1

  • Add: Parameterized SQL Execution Support
  • New Method: Update Table's Structure
  • New Method: LastInsertRowId
  • New Method: GetTableList
  • New Method: ShowDatabase
  • New Method: AttachDatabase, DetachDatabase
  • Modify: ExecuteScalar applies generics.
  • Demo App Updated

List of Simplified Functions

  1. GetTableStatus
  2. GetTableList
  3. GetColumnStatus
  4. CreateTable
  5. UpdateTableStructure
  6. BeginTransaction, Commit, Rollback
  7. Select
  8. Execute
  9. ExecuteScalar
  10. Escape
  11. Insert
  12. Update
  13. LastInsertRowId
  14. RenameTable
  15. CopyAllData
  16. DropTable
  17. ShowDatabase
  18. AttachDatabase, DetachDatabase

Getting Start

Add this using statement at the top of your class:

C#
using System.Data.SQLite;

SQLiteConnection and SQLiteCommand have to be initialized before using SQLiteHelper:

Example:

C#
using (SQLiteConnection conn = new SQLiteConnection("data source=C:\\data"))
{
    using (SQLiteCommand cmd = new SQLiteCommand())
    {
        cmd.Connection = conn;
        conn.Open();
 
        SQLiteHelper sh = new SQLiteHelper(cmd);
 
        // do something...

        conn.Close();
    }
}

1. GetTableStatus

Get all information of tables in the database.

C#
DataTable dt = sh.GetTableStatus();

Sample result:

type name tbl_name rootpage sql
table sqlite_sequence sqlite_sequence 3 CREATE TABLE sqlite_sequence(name,seq)
table person2 person2 5 CREATE TABLE "person2"(
id integer primary key autoincrement,
name text,
tel text,
email text,
job text,
remarks text)
table player player 4 CREATE TABLE `player`(
id integer primary key autoincrement,
lvl integer,
weaponid integer,
teamid integer,
location text,
team_name text,
remarks text)
table product product 6 CREATE TABLE "product"(
id integer primary key autoincrement,
name text,
qty integer)

2. GetTableList

Get a list of tables in database.

C#
DataTable dt = sh.GetTableList();

3. GetColumnStatus

Get all information of columns in specific table.

C#
// Get column's information from table "person"
DataTable dt = sh.GetColumnStatus("person");

Sample Result:

cid name type notnull dflt_value pk
0 id integer 0   1
1 lvl integer 0   0
2 weaponid integer 0   0
3 teamid integer 0   0
4 location text 0   0
5 team_name text 0   0
6 remarks text 0   0

4. CreateTable

Create table.

Example table structure: Person

Column Name Data Type Primary Key Auto Increment Not Null Default Value
id int true true    
name text        
membershipid int        
level decimal       5.5
C#
SQLiteTable tb = new SQLiteTable("person");
 
tb.Columns.Add(new SQLiteColumn("id", true));
tb.Columns.Add(new SQLiteColumn("name"));
tb.Columns.Add(new SQLiteColumn("membershipid", ColType.Integer));
tb.Columns.Add(new SQLiteColumn("level", ColType.Decimal, false, false, "5.5"));
 
sh.CreateTable(tb);

5. UpdateTableStructure

As the name said, it is used to update a table's structure. Maybe you have added new columns, or drop/deleted some columns. This method helps you to update it.

The process at code behind:

  • Assume that the old table is named: person
  • The class creates a temporary table (named: person_temp) with your new defined structure.
  • Copy all rows from person to person_temp.
  • Drop/delete table of person.
  • Rename table of person_temp to person

Code example:

C#
SQLiteTable tb = new SQLiteTable();
tb.Columns.Add(new SQLiteColumn("id", true));
tb.Columns.Add(new SQLiteColumn("name"));
tb.Columns.Add(new SQLiteColumn("sku"));
tb.Columns.Add(new SQLiteColumn("code"));
tb.Columns.Add(new SQLiteColumn("category"));
tb.Columns.Add(new SQLiteColumn("remarks"));

sh.UpdateTableStructure("person", tb);

6. BeginTransaction, Commit, Rollback

What is transaction?

By default, every SQL query that is sent to SQLite database engine happens in a transaction. The engine automatically BEGIN a transaction and COMMIT it at the end. COMMIT is something like "Make it take effect".

If we send 3 SQL queries (INSERT, UPDATE, DELETE, etc...), 3 transactions are taken place. According to [SQLite official documentation - Frequently Asked Questions]:

"...A transaction normally requires two complete rotations of the disk platter, which on a 7200RPM disk drive limits you to about 60 transactions per second..."

Which means, with a 7200RPM hard disk, the best that we can do is 60 INSERTs (or UPDATE, DELETE, etc) per second.

But, If we manually issue a BEGIN TRANSACTION, all the queries will be wrapped in single transaction, then SQLite can execute huge amount of queries per second. Somebody said he can execute 10 million per second at [stackoverflow.com], but this is also depends on the speed of hard disk that you are using.

Code example with SQLiteHelper:

C#
sh.BeginTransaction();
 
try
{
    // INSERT.....
    // INSERT.....
    // UPDATE....
    // ... skip for another 50,000 queries....
    // DELETE....
    // UPDATE...
    // INSERT.....

    sh.Commit();
}
catch
{
    sh.Rollback();
}

ROLLBACK, in the above example means Cancel Transaction. All queries that have sent to SQLite database within that specific transaction are dismissed.

7. Select

Return the query result in DataTable format.

  • Select(string sql)
  • Select(string sql, Dictionary<string, object> dicParameters = null)
  • Select(string sql, IEnumerable<SQLiteParameter> parameters = null)

Example 1:

C#
DataTable dt = sh.Select("select * from person order by id;");

Example 2 (With parameters support):

C#
var dic = new Dictionarystring, object();
dic["@aaa"] = 1;
dic["@bbb"] = 1;
DataTable dt = sh.Select("select * from member where membershipid = @aaa and locationid = @bbb;", dic);

Example 3 (With parameters support):

C#
DataTable dt = sh.Select("select * from member where membershipid = @aaa and locationid = @bbb;",
    new SQLiteParameter[] { 
        new SQLiteParameter("@aaa", 1),
        new SQLiteParameter("@bbb", 1)
    });

8. Execute

Execute single SQL query.

  • Execute(string sql)
  • Execute(string sql, Dictionary<string, object> dicParameters = null)
  • Execute(string sql, IEnumerable<SQLiteParameter> parameters = null)

Example:

C#
sh.Execute("insert into person(name)values('hello');");

9. ExecuteScalar

Return the result of first row first column in specific data type.

  • ExecuteScalar(string sql)
  • ExecuteScalar(string sql, Dictionary<string, object> dicParameters = null)
  • ExecuteScalar(string sql, IEnumerable<SQLiteParameter> parameters = null)
  • ExecuteScalar<datatype>(string sql)
  • ExecuteScalar<datatype>(string sql, Dictionary<string, object> dicParameters = null)
  • ExecuteScalar<datatype>(string sql, IEnumerable<SQLiteParameter> parameters = null)

Example:

C#
string a = sh.ExecuteScalar<string>("select 'Hello!';");

int b = sh.ExecuteScalar<int>("select 1000;");

decimal c = sh.ExecuteScalar<decimal>("select 4.4;");

DateTime d = sh.ExecuteScalar<DateTime>("select date('now');");

byte[] e = sh.ExecuteScalar<byte[]>("select randomblob(16);");

10. Escape

Escape string sequence for text value to avoid SQL injection or invalid SQL syntax to be constructed.

C#
sh.Execute("insert into person(name) values('" + Escape(input) + "');");

11. Insert

Insert new row of data. All data will be added as parameters at code behind. This support blob (byte[]) value too.

C#
var dic = new Dictionary<string, object>();
dic["name"] = "John";
dic["membershipid"] = 1;
dic["level"] = 6.8;
 
sh.Insert("person", dic);

12. Update

Update row. All data will be added as parameters at code behind. This support blob (byte[]) value too.

Example 1: Update with single condition (where id = 1)

C#
var dicData = new Dictionary<string, object>();
dicData["name"] = "no name";
dicData["membershipid"] = 0;
dicData["level"] = 5.5;
 
sh.Update("person", dicData, "id", 1);

Example 2: Update with multiple condition (where membership = 1 and level = 5.5 and teamid = 1)

C#
var dicData = new Dictionary<string, object>();
dicData["name"] = "no name";
dicData["status"] = 0;
dicData["money"] = 100;
dicData["dateregister"] = DateTime.MinValue;
 
var dicCondition = new Dictionary<string, object>();
dicCondition["membershipid"] = 1;
dicCondition["level"] = 5.5;
dicCondition["teamid"] = 1;
 
sh.Update("person", dicData, dicCondition);

13. LastInsertRowId

Get the last issued id (Auto-Increment)

sh.Insert("person", dicData);
long id = sh.LastInsertRowId();

14. RenameTable

Rename a table.

C#
sh.RenameTable("person", "person_backup");

15. CopyAllData

Copy all data from one table to another.

C#
sh.CopyAllData("person", "person_new");

Before copying, SQLiteHelper will scan the two tables for match columns. Only columns that exist in both tables will be copied.

16. DropTable

Drop table, delete a table

C#
sh.DropTable("person");

17. ShowDatabase

Display attached databases.

C#
DataTable dt = sh.ShowDatabase();

18. AttachDatabase, DetachDatabase

Attach or detach a database

C#
sh.AttachDatabase("C:\\data2013.sq3", "lastyeardb");
sb.DetachDatabase("lastyeardb");

That's it, guys/girls. Comments are welcome.

Happy coding Smile | :)

Alternative

Lastly, I shall introduce other tools which you might consider when developing C#, VB.NET apps with SQLite:

1. SQLite.NET

SQLite.NET is designed to make working with sqlite very easy in a .NET environment. It is an open source, minimal library to allow .NET and Mono applications to store data in [http://www.sqlite.org SQLite 3 databases]. It is written in C# and is meant to be simply compiled in with your projects. It was first designed to work with MonoTouch on the iPhone, but has grown up to work on all the platforms (Mono for Android, .NET, Silverlight, WP7, WinRT, Azure, etc.).

2. System.Data.SQLite.EF6

History

  • 27 Mar 2014 - Release of V1.2
  • 22 Mar 2014 - Release of V1.1
  • 19 Mar 2014 - Initial work

License

This article, along with any associated source code and files, is licensed under A Public Domain dedication


Written By
Software Developer
Other Other
Programming is an art.

Comments and Discussions

 
QuestionUnable to download "SQLiteHelper V1.2 Demo App" Pin
AndySolo3-Jul-22 7:35
AndySolo3-Jul-22 7:35 
AnswerRe: Unable to download "SQLiteHelper V1.2 Demo App" Pin
adriancs30-Aug-22 17:18
mvaadriancs30-Aug-22 17:18 
QuestionDataTable TableName is empty Pin
yetibrain20-Oct-16 3:37
yetibrain20-Oct-16 3:37 
QuestionWell written and very helpful Pin
James McCullough28-Mar-16 18:29
professionalJames McCullough28-Mar-16 18:29 
QuestionNuget Package Pin
Spencer Kittleson10-Oct-15 7:37
professionalSpencer Kittleson10-Oct-15 7:37 
Generalmy vote of 5 Pin
Southmountain28-May-15 9:25
Southmountain28-May-15 9:25 
QuestionSQLite local database for UNITY 3D v 4.5 prof Pin
Member 1155520128-Mar-15 15:08
Member 1155520128-Mar-15 15:08 
QuestionUpdate with date as the condition. Pin
Member 108017895-Feb-15 0:35
Member 108017895-Feb-15 0:35 
AnswerRe: Update with date as the condition. Pin
Member 1268901719-Aug-16 15:03
Member 1268901719-Aug-16 15:03 
GeneralMy vote of 5 Pin
Truong Chau Hien28-Jan-15 6:54
Truong Chau Hien28-Jan-15 6:54 
GeneralMy vote of 5 Pin
PapyRef22-Dec-14 0:42
professionalPapyRef22-Dec-14 0:42 
QuestionDataTable to SQLiteTable Pin
Rablinz7-Sep-14 12:22
professionalRablinz7-Sep-14 12:22 
AnswerRe: DataTable to SQLiteTable Pin
adriancs7-Sep-14 15:44
mvaadriancs7-Sep-14 15:44 
Questionmore link for simply step by step for using sqlite with csharp with Source code Pin
heemanshubhalla4-Aug-14 3:25
heemanshubhalla4-Aug-14 3:25 
GeneralMy vote of 5 Pin
Diana Lucia29-Mar-14 6:55
Diana Lucia29-Mar-14 6:55 
Generalwhy not use that? Pin
aqie1327-Mar-14 17:06
aqie1327-Mar-14 17:06 
GeneralRe: why not use that? Pin
adriancs28-Mar-14 16:05
mvaadriancs28-Mar-14 16:05 
GeneralMy vote of 5 Pin
Volynsky Alex21-Mar-14 1:45
professionalVolynsky Alex21-Mar-14 1:45 
QuestionSQLite.net Pin
Marc Clifton20-Mar-14 5:03
mvaMarc Clifton20-Mar-14 5:03 
AnswerRe: SQLite.net Pin
adriancs20-Mar-14 15:11
mvaadriancs20-Mar-14 15:11 
QuestionSemi-useful Pin
HaBiX19-Mar-14 23:17
HaBiX19-Mar-14 23:17 
AnswerRe: Semi-useful Pin
adriancs19-Mar-14 23:59
mvaadriancs19-Mar-14 23:59 
GeneralRe: Semi-useful Pin
HaBiX20-Mar-14 0:05
HaBiX20-Mar-14 0:05 
GeneralRe: Semi-useful Pin
adriancs20-Mar-14 14:45
mvaadriancs20-Mar-14 14:45 
For the simplified INSERT and UPDATE mentioned in the article,
the values will be checked before constructing the SQL syntax.

In this case:
C#
var sql = "SELECT * FROM XY WHERE USERID=" + someStringValue;

Due to the column of USERID is integer type, the code behind will do the conversion before adding the value to the SQL string. Example:
C#
int i = 0;
int.TryParse(someStringValue, out i);
var sql = "SELECT * FROM XY WHERE USERID=" + i;

Perhaps, I should change to:
C#
int i = 0;
if (!int.TryParse(someStringValue, out i))
{
    throw new Exception("Invalid value detected.");
}
var sql = "SELECT * FROM XY WHERE USERID=" + i;

I'm using a dictionary to store the data and pass it to the method like this:
C#
var dic = new Dictionary<string, object>();
dic["name"] = "some name";
dic["dateregister"] = DateTime.Now;
dic["memberid"] = 1;
dic["money"] = 23.24;

sh.Insert("person", dic);

Key = Column Name
Value = data

For each value, the class will first check the column's data type, then perform <DataType>.TryParse() before append it to the final SQL syntax.

Example:
C#
var dic = new Dictionary<string, object>();
dic["name"] = "some name'; DROP TABLE xy;";
dic["dateregister"] = "'2014-03-21 08:32:00'; DROP TABLE xy;";
dic["memberid"] = "1; DROP TABLE xy;";
dic["money"] = "23.24; DROP TABLE xy;";


"dateregister", "memberid" and "money" will result invalid value.

This is because the class will do something like this:
C#
dic["name"] = "some name'; DROP TABLE xy;".Replace("'", "''").Replace("\\", "\\\\");
dic["dateregister"] = Convert.ToDateTime("'2014-03-21 08:32:00'; DROP TABLE xy;");
dic["memberid"] = Convert.ToInt32("1; DROP TABLE xy;");
dic["money"] = Convert.ToDecimal("23.24; DROP TABLE xy;");

But, however, your suggestion of parameterized should be better than manually parsing the data.
GeneralRe: Semi-useful Pin
HaBiX20-Mar-14 20:45
HaBiX20-Mar-14 20:45 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.