Monday, 21 March 2011

C# and SQLite

This post will show how you can add SQLite to a C# project.

First up, you’ll need to download two components to help you send commands to your SQLite database:

1) The SQLite ADO.NET framework System.Data.SQLite (installer)

 http://sourceforge.net/projects/sqlite-dotnet2/

2) The SQLite Administrator tool (standalone tool)

http://sqliteadmin.orbmu2k.de/

Step 1: Create your Console application in Visual Studio

new-project

Step 2: Run the SQLite Administrator tool and select Database > New to create your database.  Create the file within the console project you have just created so you can easily add the file to your project in a moment.

administrator

Step 3: Right click on Tables and select Create Table.  Click Add Field to add the fields to the table, once you’ve finished click the Create button.

create-table

Step 4: On the right hand side on the SQLite Administrator screen you select the “Edit Data” tabe, choose your new table from the dropdown list and directly add new records to the table.  We’ll be reading these new records from our console application shortly.

 edit-data

Step 5: You’ll need to add the SQLite.dll reference to your project,  Right click on the project and select Add Reference then navigate to C:\Program Files\SQLite.Net\Bin directory and add the System.Data.SQLite.dll file.

 sqlite-dll

Step 6: We’ll be storing the connection string in an App.Config so right click on your project and “Add > New item” then select App.Config from the General section.

add-app-config

Step 7: Paste the following code into the app.config file.


Step 8:  To read the Connection String from our App.Config file we’ll need to add the System.Configuration framework to our project.  Right click on the project and select “Add Reference”:


config-dll 


Step 9: On the Visual Studio main menu, select Project > Show All Files to see your new SQLiteDemoDb.s3db file.  To add it to your project just right click on the file and select Include In Project.  In the Properties for this file, set “Copy To Output Directory” to “Copy Always”.


copy-always


Step 10: Our last step is to paste the following code into our Program.cs file:


Now you’re ready to run your Console application by pressing F5


output



I hope you found this post helpful, happy coding!


Source code:  SqliteDemo.zip 


System.Data.SQLite (installer):  http://sourceforge.net/projects/sqlite-dotnet2/


SQLite Administrator tool (standalone tool):  http://sqliteadmin.orbmu2k.de/

5 comments:

  1. awesome, thanks alot been looking everywhere for this

    ReplyDelete
  2. Very nice tutorial..

    ReplyDelete
  3. I use with sqlite a free tool Valentina Studio http://www.valentina-db.com/en/valentina-studio-overview at the moment. Does everything you need, and does it very well.

    ReplyDelete
  4. there is also a step by step good article with source code here for SQLITE database connect with Csharp
    http://geeksprogrammings.blogspot.com/2014/08/using-sqlite-database-csharp.html

    ReplyDelete