Click here to Skip to main content
11,500,106 members (59,462 online)
Click here to Skip to main content

Using SQLite in your C# Application

, 16 Dec 2007 CPOL 448.5K 47.2K 202
Rate this:
Please Sign up or sign in to vote.
Using SQLite in your C# application

Introduction

SQLite is a small, fast and embeddable database where the database engine and the interface are combined into a single library. It also has the ability to store all the data in a single file. So if your application requires a standalone database, SQLite is perhaps the perfect choice for you. There are, of course, other reasons for choosing SQLite including:

  • SQLite has a small memory footprint and only a single library is required to access databases, making it ideal for embedded database applications.
  • SQLite has been ported to many platforms and runs even on Windows CE and Palm OS.
  • SQLite is ACID-compliant, meeting all four criteria - Atomicity, Consistency, Isolation, and Durability.
  • SQLite implements a large subset of the ANSI-92 SQL standard, including views, sub-queries and triggers.
  • No problem of extra database drivers, ODBC configuration required. Just include the library and the data file with your application.
  • SQLite has native language APIs for C/C++, PHP, Perl, Python, Tcl etc. Native API for C# is still not present.

Using the Code

To use SQLite in your C# application, you need to download a third party free ADO.NET data provider (SQLite.NET.0.21_x68_dll.zip) from here.

Screenshot - Executable
Fig 1: Demo C# SQLite application.

Our demo application uses SQLite database to store id and description value pairs in a single table – mains. We will display the contents of this table in a grid with added functionalities of adding/deleting and modifying records to and from the table. To set up the system, please follow the steps below:

  1. Unzip the archive, and place the *.dll files into the Binary folder.

  2. Use the SQLite.NET.dll in your .NET application by ‘Project -> Add Reference’.

  3. Add Using Finisar.SQLite; in the using directive declaration region of your *.cs file.

  4. Declare the following private fields to be used in your application:

    private SQLiteConnection sql_con;
    private SQLiteCommand sql_cmd;
    private SQLiteDataAdapter DB;
    private DataSet DS = new DataSet();
    private DataTable DT = new DataTable();
  5. Create a function to set up the Connection String. DemoT.db is the name of the single data file. It already has a single table – mains. Version (=3) is the version of the database engine.

    private void SetConnection() 
    { 
    sql_con = new SQLiteConnection
    	("Data Source=DemoT.db;Version=3;New=False;Compress=True;"); 
    } 
  6. Create a generic function ExecuteQuery, to execute Create Command queries.

    private void ExecuteQuery(string txtQuery) 
    { 
    SetConnection(); 
    sql_con.Open(); 
    sql_cmd = sql_con.CreateCommand(); 
    sql_cmd.CommandText=txtQuery; 
    sql_cmd.ExecuteNonQuery(); 
    sql_con.Close(); 
    }
  7. Now create a LoadData function to access the SQLite database and retrieve the data from the mains table and fill the Dataset. Please refer to the following code:

    private void LoadData() 
    { 
    SetConnection(); 
    sql_con.Open(); 
    sql_cmd = sql_con.CreateCommand(); 
    string CommandText = "select id, desc from mains"; 
    DB = new SQLiteDataAdapter(CommandText,sql_con); 
    DS.Reset(); 
    DB.Fill(DS); 
    DT= DS.Tables[0]; 
    Grid.DataSource = DT; 
    sql_con.Close(); 
    }
  8. To add/edit/delete an entry to and from the table, just pass the required query to the already created ExecuteQuery function. Please see the following code for an example:

    private void Add()
    {
    string txtSQLQuery = "insert into  mains (desc) values ('"+txtDesc.Text+"')";
    ExecuteQuery(txtSQLQuery);            
    }

To administer SQLite database, an open-source SQLite GUI Database Browser utility is of much use. It can be downloaded from here.

Conclusion

So should SQLite be used for all database driven applications? No.

Like all databases, SQLite has its list of shortcomings. It is not suitable for a client server application or as a networked database. It’s not suited well for a multi user scenario and can have serious file locking issues when accessed simultaneously over the network. Quite for the same reason, SQLite is not suited for a multi-threaded or a multi-process application-database access scenario.

To conclude, SQLite is very suitable for memory constraint systems like WinCE, Palms, Smart Phones, embedded devices and also normal single user desktop applications where its small memory footprint, single library and its copy and paste deployment feature give it a distinct advantage.

Pre-requisites

  1. Visual Studio .NET 2003 (C#)
  2. SQLite (www.sqlite.org)

History

  • 16-Dec-2007: Initial publication

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Chayan
Web Developer
India India
Chayan Ray has been working as a Technical Consultant in a CMM level 5 company in India. His technical domain includes ASP.NET, C#, PHP, Perl, Cold Fusion, MySQL and MSSQL 2000.

Comments and Discussions

 
Questionadox add new column and name it from textbox or string variable dynamically Pin
Member 1106451130-Sep-14 19:38
memberMember 1106451130-Sep-14 19:38 
QuestionEasy step by step guide for how to use sqlite with Csharp With Source code Pin
heemanshubhalla4-Aug-14 3:22
memberheemanshubhalla4-Aug-14 3:22 
GeneralRe: Easy step by step guide for how to use sqlite with Csharp With Source code [modified] Pin
Patrick Harris4-Aug-14 5:16
memberPatrick Harris4-Aug-14 5:16 
GeneralRe: Easy step by step guide for how to use sqlite with Csharp With Source code Pin
heemanshubhalla4-Aug-14 7:43
memberheemanshubhalla4-Aug-14 7:43 
GeneralRe: Easy step by step guide for how to use sqlite with Csharp With Source code Pin
Patrick Harris4-Aug-14 11:29
memberPatrick Harris4-Aug-14 11:29 
AnswerSimplified All This Pin
Patrick Harris17-Jul-14 12:15
memberPatrick Harris17-Jul-14 12:15 
GeneralRe: Simplified All This Pin
EmmadKareem27-Sep-14 21:58
memberEmmadKareem27-Sep-14 21:58 
GeneralRe: Simplified All This Pin
EmmadKareem27-Sep-14 21:59
memberEmmadKareem27-Sep-14 21:59 
QuestionAdding the SQLite database Pin
Michael Murphy18-Mar-14 13:39
memberMichael Murphy18-Mar-14 13:39 
AnswerRe: Adding the SQLite database Pin
Fernando E. Braz4-May-14 0:15
memberFernando E. Braz4-May-14 0:15 
QuestionUpdates for .NET 4.5.1? Pin
kiquenet.com28-Jan-14 4:22
memberkiquenet.com28-Jan-14 4:22 
GeneralÇok İyiii Pin
bilgic123425-Dec-13 22:23
memberbilgic123425-Dec-13 22:23 
GeneralMy vote of 5 Pin
Yusuf Aji Wibowo21-Nov-13 22:57
memberYusuf Aji Wibowo21-Nov-13 22:57 
QuestionSample Source Code For SQLlite Pin
AliDirik31-May-13 21:52
memberAliDirik31-May-13 21:52 
GeneralMy vote of 2 Pin
Can Yıldız14-May-13 5:33
memberCan Yıldız14-May-13 5:33 
SuggestionSolution; work with Win7 x64 VS2010 Pin
Member 164379230-Apr-13 7:31
memberMember 164379230-Apr-13 7:31 
NewsThanks... Pin
Mehdi14110-Jan-13 1:54
memberMehdi14110-Jan-13 1:54 
QuestionEmbed database file for deployemnt Pin
nikki8816-Aug-12 23:56
membernikki8816-Aug-12 23:56 
AnswerRe: Embed database file for deployemnt Pin
missSamso5-Nov-12 11:07
membermissSamso5-Nov-12 11:07 
Questionproblem with connecting sqlite Pin
Navneetmittal30-Apr-12 0:23
memberNavneetmittal30-Apr-12 0:23 
AnswerRe: problem with connecting sqlite Pin
Member 1051029928-Mar-14 1:48
memberMember 1051029928-Mar-14 1:48 
Questiontry the code in windows mobile Pin
Aka018-Dec-11 16:22
memberAka018-Dec-11 16:22 
Question64 bit problem Pin
Bobbin Paulose25-Aug-11 4:34
memberBobbin Paulose25-Aug-11 4:34 
AnswerRe: 64 bit problem Pin
priyra_xyz14-May-12 19:05
memberpriyra_xyz14-May-12 19:05 
QuestionUnable to load DLL Pin
Bobbin Paulose24-Aug-11 4:15
memberBobbin Paulose24-Aug-11 4:15 
QuestionLocalization support and database security? Pin
sbkk18-Aug-11 7:19
membersbkk18-Aug-11 7:19 
QuestionGetting the last row id. Short example. Pin
S Mario11-Aug-11 17:07
memberS Mario11-Aug-11 17:07 
GeneralAwesome Tutorial Pin
charles henington18-Apr-11 4:57
membercharles henington18-Apr-11 4:57 
GeneralProblem Reading SqlLite 2 Pin
ChithaMurali5-Apr-11 1:21
memberChithaMurali5-Apr-11 1:21 
Generalsqlite & csharp Pin
yaghoobpieri11-Aug-10 19:54
memberyaghoobpieri11-Aug-10 19:54 
Generalthanks a lot Pin
Xia Zheng Xin1-Mar-10 17:03
memberXia Zheng Xin1-Mar-10 17:03 
GeneralTry EffiProz Database Pin
arith silva19-Jan-10 13:57
memberarith silva19-Jan-10 13:57 
GeneralRe: Try EffiProz Database Pin
Ronni Marker12-Apr-10 9:35
memberRonni Marker12-Apr-10 9:35 
GeneralNeed Reports Integration with Sqllite. Using SQLite in your C# Application Pin
shahjinesh1120-Apr-09 21:12
membershahjinesh1120-Apr-09 21:12 
GeneralRe: Need Reports Integration with Sqllite. Using SQLite in your C# Application Pin
KarlS13-Jan-12 0:13
memberKarlS13-Jan-12 0:13 
GeneralGreat!!! Thank you Pin
korrawit10-Jul-08 9:02
memberkorrawit10-Jul-08 9:02 
GeneralThanks Pin
Rajesh Naik Ponda Goa17-Dec-07 23:18
memberRajesh Naik Ponda Goa17-Dec-07 23:18 
GeneralAnother Admin Option Pin
mpemberton17-Dec-07 17:52
membermpemberton17-Dec-07 17:52 
GeneralAnother Option Pin
mpemberton17-Dec-07 17:50
membermpemberton17-Dec-07 17:50 

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

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

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.150520.1 | Last Updated 16 Dec 2007
Article Copyright 2007 by Chayan
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid