CODEWITHSUNDEEP
c#sqlite
Richard Gourlay
Richard Gourlay

Reputation: 5668

How do I connect and use an SQLite database from C#?

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

Upvotes: 62

Views: 155936

Answers (6)

DA.
DA.

Reputation: 871

System.Data.SQLite class example:

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 also is a NuGet System.Data.SQLite package.

Upvotes: 87

Leo
Leo

Reputation: 1

I think the easiest way to connect to SQLite from any .NET application is Devart.Data.SQLite Nuget package. Simple example of connection and retrieving SQLite data:

public static List SQLite_RunTime_Connection()
{
    List actors = new List();
    try
    {
        // Open a connection to the SQLite database
        string connectionString = @"DataSource=sakila.db;LicenseKey=**********";
        Devart.Data.SQLite.SQLiteConnection sqLiteConnection = new Devart.Data.SQLite.SQLiteConnection();
        sqLiteConnection.ConnectionString = connectionString;
        sqLiteConnection.Open();
        var command = "SELECT * FROM actor LIMIT 10";
        var results = new Devart.Data.SQLite.SQLiteCommand(command, sqLiteConnection);
        using (Devart.Data.SQLite.SQLiteDataReader reader = results.ExecuteReader())
        {
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    int id = reader.GetInt32(0);
                    string firstname = reader.GetString(1);
                    string lastname = reader.GetString(2);
                    DateTime lastupdate = reader.GetDateTime(3);
                    Actor actor = new Actor();
                    actor.ActorId = id;
                    actor.FirstName = firstname;    
                    actor.LastName = lastname;
                    actor.LastUpdate = lastupdate;
                    actors.Add(actor);
                }
            }
            else
            {
                Console.WriteLine("No rows found.");
            }
        }
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.Message);
    }
    return actors;
}

Full instructions here https://www.devart.com/dotconnect/sqlite/connect-sqlite-in-net.html

Upvotes: 0

Espo
Espo

Reputation: 41909

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}!");
        }
    }
}

Upvotes: 52

ali asghar tofighian
ali asghar tofighian

Reputation: 349

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.

Upvotes: 2

floyd70s
floyd70s

Reputation: 101

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

Upvotes: 0

Bronek
Bronek

Reputation: 11235

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.

Upvotes: 3

Related Questions