Getting started with SQLite in C#

This tutorial will teach you how to create and connect to an SQLite database in C#. You will also learn how to create and modify tables and how to execute SQL queries on the database and how to read the returned results.

I’ll assume that you’re already familiar with SQL and at least have some knowledge of how it works (for example: what to expect as a result from “select * from table1” )

There will probably be two parts, with the first one discussing the basics needed to do pretty much anything, and in the second part I’ll discuss some miscellaneous subjects like how to parameterize your queries to make them much faster and safer.

Let’s get started.

Create a standard C# console project.

Since we’re working in C# we’ll be using the System.Data.SQLite library. This library is not a standard library (packaged with .NET for example) so we’ll need to download it. It is being developed by the people who’re also working on the (original) SQLite.

All you’ll need are two files, a .dll and a .xml file for some documentation. These files are available for download at the end of this article, you can also download these from their website, but you’ll also get some files that you don’t need.

Put these files in the folder of your project and add an assembly reference to the .dll. Just browse to System.Data.SQLite.dll and select it.

Now add using System.Data.SQLite; to the usings and you’re done. You’ve successfully added the SQLite library to you project!

Creating a database file:

You usually don’t need to create a new database file, you work with an existing one, but for those cases where you do need to create a brand new one, here’s the code:

SQLiteConnection.CreateFile("MyDatabase.sqlite");

Connecting to a database:

Before you can use the database, you’ll need to connect to it. This connection is stored inside a connection object. Every time you interact with the database, you’ll need to provide the connection object. Therefore, we’ll declare the connection object as a member variable.

SQLiteConnection m_dbConnection;

When creating a connection, we’ll need to provide a “connection string” this string can contain information about the… connection. Things like the filename of the database, the version, but can also contain things like a password, if it’s required.

You can find a few of these at: http://www.connectionstrings.com/sqlite

The first one is good enough to get our connection up and running, so we get:

m_dbConnection =
new SQLiteConnection("Data Source=MyDatabase.sqlite;Version=3;");
m_dbConnection.Open();

After we create the connection object, we’ll have to open it. And with every Open() there comes a Close(), so don’t forget to call that after you’re done with your connection.

Creating a table:

Let’s write some SQL now. We’ll create a table with two columns, the first one contains names and the second one contains scores. See it as a high scores table.

string sql = "create table highscores (name varchar(20), score int)";

You could also spam caps if you like and get something like this:

string sql = "CREATE TABLE highscores (name VARCHAR(20), score INT)";

Now we’ll need to create an SQL command in order to execute it. Luckily, we’ve got the SQLiteCommand class for this. We create a command by entering the sql query along with the connection object.

SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);

Afterwards, we execute the command. But before we execute our command, i’d like to mention that not all commands are the same, some commands return results (like SELECT etc.) and others don’t (like the one we just wrote) That’s why there are two execute methods (actually, there are three) One returns the actual results (the rows of the table) the other returns an integer indicating the number of rows that have been modified. We’ll use the last one now.

command.ExecuteNonQuery();

At this time, we’re not interested in the number of rows that have been modified (it’s 0) But you could imagine that it might be interesting to know this information in UPDATE queries.

Filling our table:

Let’s fill our table with some values, so we can do some SELECT queries. Let’s create a new command. We’ll see later that this process can be made a bit easier and faster with command parameters.

string sql = "insert into highscores (name, score) values ('Me', 9001)";

We create and execute the command the same way as we created the table. I added two more rows (or records) to the table. Here’s the code:

string sql = "insert into highscores (name, score) values ('Me', 3000)";
SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
command.ExecuteNonQuery();
sql = "insert into highscores (name, score) values ('Myself', 6000)";
command = new SQLiteCommand(sql, m_dbConnection);
command.ExecuteNonQuery();
sql = "insert into highscores (name, score) values ('And I', 9001)";
command = new SQLiteCommand(sql, m_dbConnection);
command.ExecuteNonQuery();

As you can see, this is three times pretty much the same piece code. But it works!

Getting the high scores out of our database:

Let’s query the database for the high scores sorted on score in descending order. Our SQL query becomes: “select * from highscores order by score desc”

We create a command in the regular fashion:

string sql = "select * from highscores order by score desc";
SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);

However, we execute this command using a different method, we’ll use the ExecuteReader() method which returns an SQLiteDataReader object. We’ll use this object to read the results of the query.

SQLiteDataReader reader = command.ExecuteReader();

With this reader you can read the result row by row. Here’s some code that iterates trough all the rows and writes them to the console:

string sql = "select * from highscores order by score desc";
SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
SQLiteDataReader reader = command.ExecuteReader();
while (reader.Read())
       Console.WriteLine("Name: " + reader["name"] + "\tScore: " + reader["score"]);

The Read() method of the reader moves the reader to the next row. With the [] operators, you can read the value of a certain column. The value returned is of the type object. So you’ll usually need to cast it before you can use it. Fortunately, you usually know what this type is.

Well, that’s about it for this tutorial. You should now be able to do pretty much anything you want with your database.

Click here to download the project files that implement what we’ve discussed in this article.

The above link also contains the libraries. If you’d like to have only the library, click the link below: The version of the library provided here is the .NET 4.0 x86 version. This should work fine for anyone working in Visual Studio 2010. If it doesn’t work, download the correct version from their website (mentiond at the beginning of this article)

Download only the libraries.

  • sunil

    Very Useful Tutorial.. Thank you very much… I am able to use the SQLite database with C#… One update you can make to this is in Visual Studio express 2013 edition, you can get the System.Data.SQLite library by using “manage Nuget Package Solution” under tools->LibraryPackage Manger menu…

  • Amit Bhati

    System.Data.Sqlite.dll is not a microsoft .Net module
    when i am trying to import this dll file in my Visual Studio 2008 the error occur.
    please help me to recover it.
    thanks in advance

  • Will Davis

    The correct dll version for my windows 7, 64-bit, .NET 4.0 machine turns out to be 1.0.66.0, which I only stumbled upon out of blind luck at sourceforge.net. Is there an easier way to figure out which version you need, other than trying out every single one?

  • Heemanshu

    Nice one, I have founded a post for how to use sqlite database with csharp with screenshots step by step and Download source code

    http://geeksprogrammings.blogspot.com/2014/08/using-sqlite-database-csharp.html

  • Ovidiu

    Nice and neat. Thank you.
    I observed something funny: you can give any extension name for database file.

  • Pingback: Introducing SQLite « El TecnoBaúl de Kiquenet()

  • Udder

    Really nice tut, thanks. Did you ever get around to making that second part?

    • Tigran Gasparian

      Thanks Udder 🙂
      Unfortunately, I never really got around to make the second part of the tutorial. I’m currently swamped and not working with sqlite at the moment, so I wouldn’t expect a second part soon.
      If anyone knows any resources that cover more advanced topics, please mail/tweet them to me and I’ll gladly add them to the article in a further reading section 🙂

  • lea

    thaaaaanks!

  • timmah

    Just wanted to say thanks, used this tutorial to get me started, really helpful. Nice one

  • Android Training in Chennai

    Hi this is Kathiresan i am having 3 years of experience as a dot net developer and i am certified. i have knowledge on OOPS concepts in .NET but dont know indepth. After learning android will be enough to get a good career in IT with good package? and i crossed Android Training in Chennai website where someone please help me to identity the syllabus covers everything or not??

    Thanks,
    kathiresan

  • Android Training in Chennai

    Hi this is Kathiresan i am having 3 years of experience as a dot net developer and i am certified. i have knowledge on OOPS concepts in .NET but dont know indepth. After learning android will be enough to get a good career in IT with good package? and i crossed Android Training in Chennai website where someone please help me to identity the syllabus covers everything or not??

    Thanks,
    kathiresan

  • Frans

    Thank you

  • Annette

    Thanks a lott..

  • Sai Mohini

    hi

    When I try to add a reference to the the dll , it is showing that my app is in .NET Core but the dlls for .NET framework? PLease HElp

  • hijeenu

    thanks , it was very helpful

  • Carlos Martínez

    What’s the location of the db?

    • Tigran Gasparian

      In this example, the database would reside in the working directory of the program because we specified the path here: SQLiteConnection.CreateFile(“MyDatabase.sqlite”);

      We can also choose to give it an other path, for example, the following absolute path:
      SQLiteConnection.CreateFile(“C:\MyDatabase.sqlite”);

      Now the database location would be on the C drive (assuming you’re on Windows)

    • John Aven

      I would suggest using an absolute path. Stay away from relative paths. Construct the absolute path on the fly if it is not known ahead of time.

  • Pingback: C#使用sqlite | 皓弟的网志()

  • Pedro

    This article helped me,
    thank you Sir.

  • Sebastiaan de Kooter

    Example project was really helpfull. Would be nice if there was a second part that covers stuff like displaying the data properly (I guess needs datagrid?) and setting up the database if it does not exist, and loading it if it does.

    Anyway pro tip:
    In Visual Studio 2013 you can go to Tools>Nuget Package manager>Manage NuGet Packages for Solution. Now type Sqlite in the search bar and install “System.data.Sqlite Core (x86/x64)”. It includes ADO.net so there is zero configuration required.

  • globalTop

    Hello, is there a way to integrate the .mode and .import command with c#? Many thanks

  • moqi

    Very useful tutorial~ In the sample project, you can add “m_dbConnection.Close();” in the end as you mentioned.

  • Thank you

  • Topper

    Nice Post. Appreciate it.

  • Hans Milling

    Great article. Helped me a lot getting started.

  • nick

    Please post some new and advanced tutorial about the same concept

  • Shubham Ambavale

    are there no steps given for update query?

  • Jenilia Mohan

    well described about how to get connected the SQL in C#. your step by step explanation of how to get database connection in the C# software. and very much useful once the app develop, to answer the query. thanks for sharing.

  • Pingback: SQLite and Dapper in C# – Richard's Blog()

  • Peixiang Zhong

    This is a very helpful article! Thank you very much!!!!!!!!!!!!!!

  • i hope this is still active, mind if someone can answer my question?

    • Ben

      I can’t find our question. Did you post it here, or are you asking if someone will answer if you do post a question?

      • yes! exactly, i have so many questions in using SQLite, I found out about this when I asked my best option in creating a local database, i tried it and i’m liking it so far. one is the proper way to handle query that returns value. i am able to get a specific column value in a data row by specifying it in the query for example. “SELECT id FROM table…”, but what if I wanted to get the whole data row and put it in a variable so i won’t to query again. is that possible?

        • Ben

          The question you are asking is basic SQL syntax. It is not specific to SQLite. So, find yourself an intro to SQL site or book.

          The answer to your question is that if you want more columns, you specify them in your query in a comma delimited list.

          SELECT id, fname, lname FROM Customer will return all three columns. If you are not sure what column names are contained in a table, you may use the wild card method. This is discouraged for all serious software development. Still, it will help you get things moving; especially if you didn’t create the table yourself.

          SELECT * FROM Customer returns all rows with all columns in the entire Customer table.

          Great question. So, get digging and enjoy learning SQL. It is a great language. While you’re at it, the SQLite interface for Dot Net is much the same as when connecting to any other database. So another great thing to learn will be how to use ADO.Net. Since there is a TON of documentation on ADO.Net, it will teach you quickly how do use the ADO client for SQLite.

          Cheers,

          Ben

          • yes, i know that when i’m getting a value to a specific colum, i will have to specify it in the query “SELECT id FROM tbl…” and when i’m looking for a selection of value, either i will specifically select the colums “SELECT id, fname, lname FROM tbl” or by getting all the columns using “SELECT * FROM tbl…”, but what i’m not familiar is the syntax of SQLite, i hope you can give me a link or something that i can read to get to know SQLite more, i am familiar in using MySQL in PHP, i can get the specific value in a query by specifying it as an index in an array for example.

            query[“id”];

            and it will return the value of id, but i don’t know the syntax in getting a specific column when Command.ExecuteScalar(); returns a result, i can only get the first column, i dont know how to get the next one

          • Ben

            ExecuteScalar() is an ADO.Net method made to get one value from a SELECT statement. Even if the statement returns more than one row, and/or more than one column, ExecuteScalar() only returns the first column in the first row. This method is often used instead of using OUTPUT parameters in the SQL statement.

            If you want to get more than a single value you have to retrieve a data table instead. SQLite supports retrieving data tables (a single result set) or data sets (a list of result sets). DataTable and DataSet are ADO.net objects found in the System.Data namespace. They are generally populated using a DataAdapter, also from the same namespace. SQLite implements the interfaces for these objects and should support their use.

            So, find a good tutorial on ADO.Net. That will tell you how to get and enumerate through a dataset, a datatable, etc.

            This isn’t the best example, but I’m to tired to look further.
            http://stackoverflow.com/questions/1346132/how-do-i-extract-data-from-a-datatable

            I prefer getting a datatable back, and then using a DataTableReader to walk through the results. It has casting to native types build into the DataTableReader class.

            Once I have a datatable back from ADO.net I can do something like the following:

            Var dr = new DataTableReader(myDataTable)
            while dr.Read()
            {

            }

          • thanks! I will look this up, and hopefully you can still get back to me if ever i still have questions. thank you so much, this means a lot. 😉

  • a_c

    I get errors when I try executing SQL statemens. If i try to create a table that does not exists it says the table exist or if i try to executeNonQuery() ist says that the “;” token is invalid. any solution?

  • Pingback: Ανακαλύπτοντας την System.Data.SQLite | I she #...()

  • tounsi13003

    Thank you !! This tut change my life 😉

  • panoukos

    Nice this helped a lot. Now one question is there a way to get the same for UWP apps?

    • John Aven

      Have you tried NuGet?

  • Huda

    any chance you could continue this magnificent tutorial with a little info on using TransactionScope ??

  • HABIB UL REHMAN

    Thanks
    Your article is very nice and very useful for beginners

  • hadoop training

    Thanks for posting this useful content, Good to know about new things here, Let me share this, . Hadoop training in pune

  • Christian

    How can I insert many rows in one query?

    • Dan Kaschel

      No, but you can create many strings (which represent sql statements) in one line of code, then run all of them as queries.

    • heidarj

      if you use the SQLite commandline tools, you can import á csv or .SQL file, I was able to work it out by googling around a bit

  • Christian

    I got unhandled exception
    Where does this sqlite file goes or will be save?
    https://uploads.disquscdn.com/images/66af833b0df5a2fe6ab8398d46b6751ab091e378d9b872aee86de06f4eaad4c9.png

    • MrTorque

      Remove ‘/’ if that doesn’t work then run VS as administrator

  • Navdeep Kaur

    Thanks for the post!
    C# Programming

  • kiquenet kiquenet

    Great! For newbies.

    IMHO, it would be very useful (and marvellous) an “Definitive guide” -or all i need know-about SQLite and C#.

    Not full source code real application sample using good paatterns and practices about SQLite and C# ?

    Nuget Packages for install SQLite (ADO.NET)
    Plugin Firefox for edit SQLite database

    anything else?

  • Keshav Kumar

    Very nice introduction to SQLite database using C#.

  • J.

    This article was a big help in learning SQLite and C#. Thank you!

  • jesica wilde

    nice blog

  • Thanks for the awesome share
    We at Colan Infotech Private Limited best web design company in chennai,is Situated in US and India,
    will provide you best service in qa testing services
    Design Services and Colan Infotech has a group of exceedingly dedicated, inventive and creative experts with an energy for delivering exciting ,
    helpful and stylish Web and Mobile Applications, We are one of the best software testing services company
    and of course we stepped in bangalore too we are best qa and testing services
    can provide quality assurance and testing services,
    we are the best among the software testing company india

  • gian82

    hi all . i have a problem , I have do a sqlitedb using db browser ,now I try to use it in visual studio ,all work fine when i do debug but i get an error when i try use the deployed version : “System.Data.SQLite.SQLiteException (0x80004005): SQLite error
    no such table: tb1” , how can i do? ,thank you in advance for a response

  • ananthi ayiram

    It’s interesting that many of the bloggers your tips helped to clarify a few things for me as well as giving.. very specific nice content. And tell people specific ways to live their lives.Sometimes you just have to yell at people and give them a good shake to get your point across.
    Web Design Company

  • nice post it seems .

  • Interesting post! I’ve been eyeing-up Sketch for some time now… maybe I’ll test it out. One question…

    How clean is the exported code? Is it easy to modify as needed? It would be a shame if one had to re-export from Sketch just to, say, adjust the color of the tag.

  • great and nice blog thanks sharing..I just want to say that all the information you have given here is awesome…Thank you very much for this one.

  • great and nice blog thanks sharing..I just want to say that all the information you have given here is awesome…Thank you very much for this one.

  • Aman Deep

    i am suffering With “DATABASE LOCKED ” Problem Please Help

  • This article is very much helpful and i hope this will be an useful information for the needed one. Keep on updating these kinds of informative things…

  • Harneet Singh

    Thank you. This was extremely useful to me.

  • I am crm developer with back end using SQL . Thanks For sharing about C# .
    https://goo.gl/yA3oay