What is the best way to connect and use a sqlite database from C#

Question

I've done this before in C++ by including sqlite.h but is there a similarly easy way in C#?

Answer 1

I'm with, Bruce. I AM using http://system.data.sqlite.org/ with great success as well. Here's a simple class example that I created:

using System;
using System.Text;
using System.Data;
using System.Data.SQLite;

namespace MySqlLite
{
      class DataClass
      {
        private SQLiteConnection sqlite;

        public DataClass()
        {
              //This part killed me in the beginning.  I was specifying "DataSource"
              //instead of "Data Source"
              sqlite = new SQLiteConnection("Data Source=/path/to/file.db");

        }

        public DataTable selectQuery(string query)
        {
              SQLiteDataAdapter ad;
              DataTable dt = new DataTable();

              try
              {
                    SQLiteCommand cmd;
                    sqlite.Open();  //Initiate connection to the db
                    cmd = sqlite.CreateCommand();
                    cmd.CommandText = query;  //set the passed query
                    ad = new SQLiteDataAdapter(cmd);
                    ad.Fill(dt); //fill the datasource
              }
              catch(SQLiteException ex)
              {
                    //Add your exception code here.
              }
              sqlite.Close();
              return dt;
  }
}

There is also an NuGet package: System.Data.SQLite available.

Answer 2

Microsoft.Data.Sqlite by Microsoft has over 9000 downloads every day, so I think you are safe using that one.

Example usage from the documentation:

using (var connection = new SqliteConnection("Data Source=hello.db"))
{
    connection.Open();

    var command = connection.CreateCommand();
    command.CommandText =
    @"
        SELECT name
        FROM user
        WHERE id = $id
    ";
    command.Parameters.AddWithValue("$id", id);

    using (var reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
            var name = reader.GetString(0);

            Console.WriteLine($"Hello, {name}!");
        }
    }
}

Answer 3

I've used this with great success:

https://system.data.sqlite.org/

Free with no restrictions.

Answer 4

There is a list of Sqlite wrappers for .Net at http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers. From what I've heard http://sqlite.phxsoftware.com/ is quite good. This particular one lets you access Sqlite through ADO.Net just like any other database.

Answer 5

There's also now this option: http://code.google.com/p/csharp-sqlite/ - a complete port of SQLite to C#.

Answer 6

https://github.com/praeclarum/sqlite-net is now probably the best option.

Answer 7

Another way of using SQLite database in NET Framework is to use Fluent-NHibernate.
[It is NET module which wraps around NHibernate (ORM module - Object Relational Mapping) and allows to configure NHibernate programmatically (without XML files) with the fluent pattern.]

Here is the brief 'Getting started' description how to do this in C# step by step:

https://github.com/jagregory/fluent-nhibernate/wiki/Getting-started

It includes a source code as an Visual Studio project.

Answer 8

Here I am trying to help you do the job step by step: (this may be the answer to other questions)

1. Go to this address , down the page you can see something like "List of Release Packages". Based on your system and .net framework version choose the right one for you. for example if your want to use .NET Framework 4.6 on a 64-bit Windows, choose this version and download it.
2. Then install the file somewhere on your hard drive, just like any other software.
3. Open Visual studio and your project. Then in solution explorer, right-click on "References" and choose "add Reference...".
4. Click the browse button and choose where you install the previous file and go to .../bin/System.Data.SQLite.dll and click add and then OK buttons.

that is pretty much it. now you can use SQLite in your project. to use it in your project on the code level you may use this below example code:

1. make a connection string:

   string connectionString = @"URI=file:{the location of your sqlite database}";

2. establish a sqlite connection:

   SQLiteConnection theConnection = new SQLiteConnection(connectionString );

3. open the connection:

   theConnection.Open();

4. create a sqlite command:

   SQLiteCommand cmd = new SQLiteCommand(theConnection);

5. Make a command text, or better said your SQLite statement:

   cmd.CommandText = "INSERT INTO table_name(col1, col2) VALUES(val1, val2)";

6. Execute the command

   cmd.ExecuteNonQuery();

that is it.

Answer 9

Mono comes with a wrapper, use theirs!

https://github.com/mono/mono/tree/master/mcs/class/Mono.Data.Sqlite/Mono.Data.Sqlite_2.0 gives code to wrap the actual SQLite dll ( http://www.sqlite.org/sqlite-shell-win32-x86-3071300.zip found on the download page http://www.sqlite.org/download.html/ ) in a .net friendly way. It works on Linux or Windows.

This seems the thinnest of all worlds, minimizing your dependence on third party libraries. If I had to do this project from scratch, this is the way I would do it.

Answer 10

if you have any problem with the library you can use Microsoft.Data.Sqlite;

 public static DataTable GetData(string connectionString, string query)
        {
            DataTable dt = new DataTable();
            Microsoft.Data.Sqlite.SqliteConnection connection;
            Microsoft.Data.Sqlite.SqliteCommand command;

            connection = new Microsoft.Data.Sqlite.SqliteConnection("Data Source= YOU_PATH_BD.sqlite");
            try
            {
                connection.Open();
                command = new Microsoft.Data.Sqlite.SqliteCommand(query, connection);
                dt.Load(command.ExecuteReader());
                connection.Close();
            }
            catch
            {
            }

            return dt;
        }

you can add NuGet Package Microsoft.Data.Sqlite