Bookmark and Share
You are here: Home / Featured Content / [HowTo] Use SqLite ADO.NET with C#

September 26, 2009 by Ivan  

[HowTo] Use SqLite ADO.NET with C#

Ads by Google

SQLiteSQLite is software library written in C that implement self-contained (very minimal support from external libraries), serverless (read/write process directly to database file), cross-platform (run in any Operating System), zero-configuration (no setup/installation needed), transactional (implement serializeable transaction) SQL database engine. SQLite is relatively small, approx 275 KB in size and single database file.

For me SQLite is better solution rather than use MS.Access for medium to small scale application, because it cross-platform and i don’t need to install Microsoft Office in client computer. And because it doesn’t need any configuration, just plug and play application and run anywhere. Such a convenient. And also SQLite support custom function and trigger function. In Ms.Access you need to write your custom function in VBA, but in SQLite you can write your custom function in your own language and later it will automatically bind to SQLite process.

Advantage of SQLite:
1. Zero-Configuration
2. Serverless
3. Single Database File
4. Stable Cross Platform Database File
5. Compact Size
6. Variable-length records
7. Free with Public Domain license

Disadvantage of SQLite:
1. In most case database size is restricted to 2GB
2. Not fully SQL92 compliant
3. Lock whole file while writing
4. No caching mechanism
5. Not very scalable

Next is i will show you how to use SQLite with C# programming language. To access SQLite in C# you need to use SQLite ADO.NET that you can download here. And now follow this instruction:
1. Open your Visual Studio 2005/2008 (I’m using 2008 express)
2. Create C# project, you can call it “SqliteTest”

Create Project
3. Import the dlls from sqlite ADO.NET (System.Data.SQLite.dll)
4. Change the “Copy Local” property of System.Data.SQLite.dll to true, so this dll will be copied when compiled

Add-Reference
5. Then Add new folder called Data, we are going to put the database file here.
6. Add the database file, then change the  ”Copy To Output Directory” property to “Always”

add-database
7. Now select the form1 design and insert a DataGridView, adjust the properties as you like

Form
8. Double click Form1 so you will go to text editor and in Form1_Load events
9. Add this code:

private void Form1_Load(object sender, EventArgs e)
{
SQLiteConnection ObjConnection = new SQLiteConnection("Data Source=Data/data.db3;");
SQLiteCommand ObjCommand = new SQLiteCommand("SELECT * FROM PERSON", ObjConnection);
ObjCommand.CommandType = CommandType.Text;
SQLiteDataAdapter ObjDataAdapter = new SQLiteDataAdapter(ObjCommand);
DataSet dataSet = new DataSet();
ObjDataAdapter.Fill(dataSet, "Person");
dataGridView1.DataSource = dataSet.Tables["Person"];
}

10. And the last thing don’t forget to add this code to the top of the file:

using System.Data.SQLite;

11. Compile and running the file. And you are connected to SQLite database. You can do any experiment like Insert, Update and Delete operation or do other complex query.

Result

You can download the example project  and database file from those instruction above here.

Thanks for reading this article, please leave me any comment or any tips you would like to share. Good luck.

What people search:

sqlite ado net, c# visual 2010 sqlite, visual c# connectionstring sqlite, sqlite c#, SQLite NET dll, sqlite c# howto, sqlite DataSet, sqlite trigger c#, sqlite csharp, spring net sqlite, sqllite c#, dataset sqlite c#, ado net sqlite, sqlite c sharp, using System Data SQLite; how to using sqlite c#, sqlite c# 2010, sqlite net, System Data SQLite dll LINUX, sqlite disadvantages, sqlite db lock net, sqlite csharp-sqlite 2010, sqlite c# get double from database, sqlite c# express 2010 tutorial, sqlite C# example, sqlite C# 4, СКАЧАТЬ SQLITE ДЛЯ c#, sqlite ado net visual studio 2010, sqlite ado net tutorial, sqlite ado net reader, sqlite download net, sqlite entity framework tutorial, sqlite entity framework visual studio 2010, vs net sqlite conectionstring, visual studio sqlite select, visual studio express 2010 sqlite, system data sqlite tutorial, System Data SQLite net 4 0, sqllite installer visual studio 2008, sqllite for С№, sqllite c# sample, sqllite ado net, sqliteconnection visual studio 2010, sqlite sharp, sqlite NET without install, sqlite net example, sqlite net entity dll, sqlite net 4, sqlite how to install in windows vista for net, sqlite from Visual C#, web app development c# mono development sqlite sample, sqlite add reference visual, ADO for sqlite tutorial, C# sqlite insert, c# sqlite visual studio 2010, C# use dataset in sqlite, call instead of insert trigger from sqlite net, Create SQLite DB and SQLite table using C# in Visual Studio 2005, CRUD Visual C# Sharp, csharp ado net video download, DataSet c# tutorial, download facebook dll C#, c# sqlite importar sql, c# sqlite create database, c# sqlite change pass, ado net c#, ado net c# visual studio tutorial, ado net for sqlite, c sharp sqlite, c sharp visual instruction, c# ado net cf sample, c# net read db3 file, C# simple sqlite crud example, c# sqlite ado db, entity framework tutorial sqlite video, executing sqlite scripts c#, HOWTO visual studio 2010 sqlite, importing dll C3 compact framework, installing sqlite net in visual c# 2010, ipad c# mono example, net MOBILE sqlite no such table, no such function: sqlite c#, script sqlite c# create database, silverlight sqlite, how to use sqlite in net, how we can bind notepad file on window form in c# ado net, how to web browser as desktop wall paper using c sharp, How to use SQLite ubuntu, fill sqlite from txt file c#, free sownload ado demo in c# and sql, functions in c# in ado net example, how to access sqlite with visual studio 2005, how to import a dll visual c# 2010, how to read sqllite files using c#, how to select sqllight silverlight entity framework, how to use ado sqlite, how to use SQlite Date Base Browser, sql lite net 4, 

Article by Ivan Kristianto

Ivan Kristianto is a freelancer web developer and blogger. He started to blog since November 2007. He start this site since April 2008. With bachelor of Engineering degree background he writes articles in IT related field and share through this blog.

Tags: , , , , , , , , ,
Category: Featured Content, Visual Studio.NET

Bookmark and Share

Reader Comments

  1. charlie says:
    November 13, 2009 at 4:38 am

    you have a typo: you mean DISadvantages of SQLite for the one heading…

  2. Ivan says:
    November 14, 2009 at 12:17 am

    Yes, thank you charlie.
    My bad. Sorry…

  3. anatili says:
    January 17, 2010 at 4:35 pm

    Try EffiProz Database http://www.EffiProz.com,
    EffiProz is a database written entirely in C#. comprehensive SQL support including Stored Procedures, Triggers and Functions. Ideal for embed in .Net applications. Support Silverlight 3 and .Net compact framework as well
    Include Visual Studio ad-in, ADO.Net provider, Entity Framework provider, etc.

  4. Ivan says:
    January 17, 2010 at 9:33 pm

    Thanks, i’ll give it a look.

  5. Murlogue says:
    March 22, 2010 at 4:33 pm

    Hi Ivan, you mentiona 2Gb limit on the database size, they say 2Tb on the website, are you referring to Windows desktops? I've used Dbs up to 12Gb on Linux without a problem but on my Windows desktop 2Gb appears to be the max.

    Cheers

    Murlogue

  6. Ivan says:
    March 25, 2010 at 11:30 am

    Hi Murlogue,
    yes, i mean on windows machine, because i'm using ADO.NET on windows machine.

  7. tjsoftware says:
    June 26, 2010 at 3:16 am

    thank you for your useful post, i have translated this into Persian and published to my weblog.I mentioned the source, i respect copyright.

  8. Lohengrin Percival says:
    July 20, 2010 at 11:09 am

    Would have been nice if you included examples of CRUD operations in the tutorials, but it's a good start nonetheless. Thanks.

  9. Warren Chin says:
    August 2, 2010 at 1:01 am

    Hi Ivan, great tutorial … the database file at step 6 above … what type of file is that local database item, service-based database ite, or a text file renamed to data.db3? Thank you – Warren

  10. Ivan Kristianto says:
    August 2, 2010 at 2:06 am

    Hi Warren,
    it is a file based database. I create it with SQLite tools.

  11. Warren Chin says:
    August 2, 2010 at 10:44 am

    Hi Ivan,
    That's great – thank you very much for that. I'll check out the SQLite tools now.
    Cheers.

  12. Tesh_a2 says:
    August 10, 2010 at 7:49 am

    i just want to create a catalog software using C# , so what kind of Data Base (DB) is needed . And the db files should be portable. Because after the end of the program development the software should be easily deployed by anybody without the need to install the db software separately just by installing the application the db also should be accessible. Pls i need it badly. i need urgent solution

  13. Ivan Kristianto says:
    August 10, 2010 at 8:41 am

    Then you should use database entity framework rather than use ADO.net. There are lots of them out there. You can use Spring.Net, IBatis.Net, Castle proxy, entity framework by .net. you can even change database on runtime. Good luck.

Please do not add HTML tags in your comments. All comments are moderated.

Add a comment

(required, use real name)
(required, will not be published)
(optional, will not be published)
blog comments powered by Disqus