[HowTo] Use SqLite ADO.NET with C#

SQLite 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
  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.

Comments

  1. charlie says:

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

    • Anonymous says:

      Yes, thank you charlie.
      My bad. Sorry…

      • Dionisius Jonathan says:

        ivan, it's jonathan..your college friend..how r u bro? actually i'm using sqlite right now..but i'm don't know how to create installer file that embed this sqlite with my software..can you help me?

  2. charlie says:

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

  3. anatili says:

    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. anatili says:

    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.

  5. Murlogue says:

    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. Murlogue says:

    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

  7. tjsoftware says:

    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. tjsoftware says:

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

  9. Lohengrin Percival says:

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

  10. Warren Chin says:

    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

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

  12. Warren Chin says:

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

  13. Tesh_a2 says:

    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

  14. 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.

  15. Cauvery says:

    Thanks for the sample

  16. Ram says:

    Hi, What do you actually mean Ivan when you said you used SQLite tools to creat the db file.

  17. Asdasd says:

    thanks

  18. Rajarajappan007 says:

    thank you for the turorial… was searching for some alternative to sqlserver..
    can u please post one such tutorial for Mysql too??it'll be really helpful

  19. HoNgOuRu says:

    Hi, I'm following your howto.
    How do I create the file "data.db3" ???
    thanks

  20. NurAdilah says:

    hi..

    How to create the data.db3?.. As u say use sqlite. Do we need to install the Sqlite or something?

  21. @NurAdilah There are many tools for creating .db3 you can find it with google. and i can find it in less then a minute. here you go: http://stackoverflow.com/questions/1543337/best-free-sqlite-management-tool

  22. sure. more to go.

  23. NurAdilah says:

    can i use listview instead of datagridview? wat changes do i have to make?

  24. Sure you can use any controller as usual.

  25. rogerx19 says:

    Hi
    Excellent tutorial! 
     
    I'm using Visual C# 2010 Express.
     
    Some of the things that tripped me up, but I was able to figure out:
     
    1) The SQLite ADO.NET file that is needed is now available at:
     
    http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki
     
    Just download the sqlite-netFx version that matches your version of Windows (32 bit or 64 bit) and .NET (3.5 or 4).
     
    The downloaded file is in .zip format, so you need to un-zip it. Make a note of where it is going to Extract the files to, so that you can find them again.
     
    Once you have it uncompressed, go back to your Visual Studio project and look in the Solution Explorer window.
     
    Right click on "References" then click Add Reference. click the "Browse tab, and find the folder with the System.Data.SQLite.dll file in it. Click on that file, then click OK.
     
    Back in Solution Explorer, right click on System.Data.SQLite and click Properties. In the Properties window, change "Copy Local" from False to True, and Save your work.
     
    2) Step 5 "Add new folder Data". In Solution Explorer, right click on SQLite Test (in his example) and click Add, then New Folder. Rename it to Data.
     
    3) Step 6 "Add the database file". In his example, the database file has already been created using SQLite and named "data.db3" (yours may be different).  
     
    Right click on the new Data folder, and click Add, then  Existing Item. Use the file finder window to find your existing db3 file.
     
    Once your db3 file is shown in Solution Explorer in the Data folder, click it once to open its properties in the Properties Window (or right click the db3 file and click Properties). In the Properties window, change Copy to Output Directory to "copy always".
     
    4) Step 9  in the Source Code that he has you add, if your db3 file is not named data.db3, then change "Data Source=Data/data.db3 " to match your filename.
     
    Also change all instances of "Person" to the name of the table in your database file that you want to display

Give me your feedback

This site uses Akismet to reduce spam. Learn how your comment data is processed.