Using SQLite Database With Dotnet C-Sharp

What is SQLite?

SQLite is an small database. It is ACID-compliant and very small in size and used as in process lib( dll/lib/so or any other library format). This database is used as part of application but not as separate process.
SQLite is a public domain open source software, so you can use it freely in any of your application and this is best suitable for embedded relational database management system.
You can find it on android phone, windows mobile and other DTV platforms. You can also use it in small desktop application on windows as well as Linux and other OS.

SQLite is a multitasking database for read operation but write can be done only one at a time.

For more details of SQLite database you can refer at here

If you want to use SQLite in you dotnet applications there are a public domain implementation of original SQLite database engine as provider in single mix mode assembly. All native code lib of SQLite3.dll is embedded into one dotnet assembly.
Note: There are different build of dotnet assembly for 32 bit, 64 bit and compact framework because it is mixed of managed provider and native SQLite database engine. 2.0 Provider for SQLite:

It has Complete ADO.NET 2.0 Implementation, Supports the Full and Compact .NET Framework, Support for the ADO.NET 3.5 Entity Framework and Visual Studio 2005/2008 Design-Time Support as Microsoft provides for other provider like System.Data.SQL.

How to use it in your dotnet program ?
For using 2.0 Provider for SQLite, you have to download it from SQLite- from here.

Install it by running setup after download.

After installation check files at installation location by default it is "C:\Program Files\SQLite.NET\". At this location you will find two folders named bin and doc. Bin folder contain all binaries and doc contains .chm documentation of library.

In bin folder you will find files System.Data.SQLite.dll, System.Data.SQLite.Linq.dll, and System.Data.SQLite.dll in subfolder for different processor architecture like itanium, x64, compact.
In designer subfolder of bin you will find SQLite.Designer.dll. This dll will be used by Visual Studio it self for designing your SQLite database table, queries etc. This file is not for application use.

For using these SQLite provider System.Data.SQLite.dll reference carefully for your target platform in you application while developing with Visual Studio.

Most Important in you visual studio project properties set Platform target to specific like x86, x64 but donot use any platform. This may create problem while deploying using setup. If you want to use LINQ then add referent of System.Data.SQLite.Linq.dll also.

Creating SQLite database using Visual Studio:

For creating SQLite database and table you can use other free tool, here i am going to explain Visual Studio.

  1. In Visual Studio go to menu View->Server Explorer, it will open server explorer tab.
  2. In Server Explorer right click on data connections and select option add connection.
  3. It will open add connection dialog.
    In Add connection dialog change data source and it will open change data source dialog. In this dialog select SQLite Database file.
  4. Now on add connection dialog you can connect to existing SQLite db file by browsing it or you can create new file by using New button and save database file to desired location.
  5. If you want to encrypt you database then you have to give some password or left it blank.
  6. Now test connection by click Test Connection button and click OK to connect SQLite data base.

Now in server explorer dialog you can see new SQLite[test] item tree. Now by expanding this tree item you will find three option folders Tables, System Tables and Views. By right clicking on these item you will get option for creating tables and views.
This database designer is almost same as other database designer available in Visual studio so i am skipping this as this is very user friendly and any one can use them.

After Creating database and tables now you have to make connection to that and perform different database operations. For these you can refer SQLite.NET.chm which has all documentation of different classes and methods given in this provider. All the classes and methods name and their behavior is same as System.Data.SQL classes for MS SQL server. All classes name are like System.Data.SQL classes only Sql is replaced with SQLite.


SQLite data provider is just like other provider because it implement Microsoft base classes and interfaces.

Simple example of SQLite with C#

string connectionString =
@"data source="C:\Users\sheel\Docume\test.db"";
using (SQLiteConnection myconnection = 
    new SQLiteConnection(connectionString))
    using (SQLiteTransaction mytransaction = myconnection.BeginTransaction())
        using (SQLiteCommand mycommand = 
            new SQLiteCommand(myconnection))
            SQLiteParameter myparam = new SQLiteParameter();
            int n;
            mycommand.CommandText = 
                "INSERT INTO [MyTable] ([MyId]) VALUES(?)";
            for (n = 0; n < 100000; n++)
                myparam.Value = n + 1;

If you are familiar with programming for other database then you can user it in same way.

For more details please see my other post for SQL Lite chm documentation

SQLite data base has not support of row or table level locks. At a time only one connection or process can perform write operation.
It support only few basic data types.
A SQLiteCommand object can be re-assigned a new SQLiteConnection object as long as no DataReaders are active on the command.
Opening a transaction is considered a write operation, so only use them when you want to write to the database! If you hold open a transaction, all readers on other connections will be blocked until the transaction is closed!
Thread Safety
Multi-threading in SQLite must be done carefully
For more details on thread safety please refer chm documentation.

blog comments powered by Disqus