How to create and connect to an SQLite database in C#
The aim of this tutorial is to teach you how to create a new SQLite database from scratch, create a new table in it, insert and read values from it. This is merely an entry level example to give you an idea on how to start.
First you will need System.Data.SQLite library from system.data.sqlite.org. Head over to their download section and download the libraries that best suit your need depending on the .NET Framework you want to target and the Windows bit version.
Extract the file and add System.Data.SQLite.dll as a reference in your project. Keep in mind that SQLite.Interop.dll also needs to be in your executables directory but doesn’t need to be added as a reference in your project. Moreover, if your application is targeting Any CPU it is likely that you will get an exception. So make sure to navigate to Project properties -> Build and set the Platform target to the bit version of the System.Data.SQLite.dll binary you have downloaded.
Finally, the snippet below should give you the general idea on the main functions you will need to learn first, mainly
- Creating a file for your database
- Creating a table in your database
- Inserting information in the database
- Retrieving information from the database
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 | // This is the query which will create a new table in our database file with three columns. An auto increment column called "ID", and two NVARCHAR type columns with the names "Key" and "Value" string createTableQuery = @"CREATE TABLE IF NOT EXISTS [MyTable] ( [ID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, [Key] NVARCHAR(2048) NULL, [Value] VARCHAR(2048) NULL )"; System.Data.SQLite.SQLiteConnection.CreateFile("databaseFile.db3"); // Create the file which will be hosting our database using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=databaseFile.db3")) { using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con)) { con.Open(); // Open the connection to the database com.CommandText = createTableQuery; // Set CommandText to our query that will create the table com.ExecuteNonQuery(); // Execute the query com.CommandText = "INSERT INTO MyTable (Key,Value) Values ('key one','value one')"; // Add the first entry into our database com.ExecuteNonQuery(); // Execute the query com.CommandText = "INSERT INTO MyTable (Key,Value) Values ('key two','value value')"; // Add another entry into our database com.ExecuteNonQuery(); // Execute the query com.CommandText = "Select * FROM MyTable"; // Select all rows from our database table using (System.Data.SQLite.SQLiteDataReader reader = com.ExecuteReader()) { while (reader.Read()) { Console.WriteLine(reader["Key"] + " : " + reader["Value"]); // Display the value of the key and value column for every row } } con.Close(); // Close the connection to the database } } |
The output will be
1 2 | key one : value one key two : value value |
Thanks a lot,
Given code working.
Thanks for article. Worked great, only made use of https://www.nuget.org/packages/System.Data.SQLite.Core/
hello
when I try to use
SQLiteConnection.CreateFile
Vs say: Active The name ‘SQLiteConnection.CreateFile’ does not exist in the current context.
Thank you.
Used Package Manager in Visual Studio and entered PM>Install-Package System.Data.SQLite
Used your code and it worked first try! Simple and clean.
It saved the sqlite database in the bin/Debug folder.
Unable to load DLL SQLite.Interop.DLL : The specified module could not be found
Make sure
SQLite.Interop.DLLis located in the same folder as your application and also that they are both (your application and the .dll) using the same architecture (32bit < --> 32bit).After more than an hour of searching for a developer who knows what they’re talking about, here you are. Excellent post, kudos to you. For other people new to C# out there, if you are using Visual Studio Express 2013, you can install SQLite by right clicking on “References” in your project, then go to “Manage NuGet packages” type SQLite in the search, wait for it to come up, then click install. The entire code placed under a button press event (put a button on a form, and double click it, put the code between those brackets.) One last thing, you will need to add “using System.Data.SQLite” at the top of the code.
Robert said it all except for one thing; Robert’s comment provided the key I needed to get this to work. So I guess there is hope for even us total noobs. Great post CooLMinE, thanks to you both.
I tinkered with this for several hours using a different tutorial. I kept getting “SQLite is not in System.Data, are you missing an assembly reference?” I had a reference; I had using statement. I tried downloading and installing SQLite separately and through NuGet command line for my .NET version using both x86 and x64 and setting CPU target. Your steps worked. Thanks.