How can I access SQLite with C#?

Question

I'm trying to get connected to my Sqlite database programmatically using C#/ASP.NET:

string requete_sql = "SELECT * FROM USERS";
connStr = @"Data Source=C:\LocalFolder\FooBar.db;";
using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(connStr)) {
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(requete_sql,conn);
conn.Open();
cmd.ExecuteNonQuery();
}

But an exception rises (on the conn.Open() line) telling that :

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

Which is very odd because I copied the exact connection string found in the Web.config file.

How can I avoid this exception?

PS: My goal is to get connected only programmatically to the database without the web.config file.

Thank you,

Regards.

score 14 · Answer 1 · edited May 30 '19 at 18:07

14

SQLite in C# (requires System.Data.SQLite in references)

// Required references, after installing SQLite via Nuget
using System.Data.SQLite;
using System.Data.Common;

// Example usage in code...
SQLiteConnection db = new SQLiteConnection("Data Source=C:\LocalFolder\FooBar.db;FailIfMissing=True;");
db.Open();
using (SQLiteCommand comm=db.CreateCommand()) {
  comm.CommandText = requete_sql;
  IDataReader dr=comm.ExecuteReader();
  while (dr.Read())
  {
    //...
  }
}

edited May 30 '19 at 18:07

Kevin

16,549
8
60
74

answered Sep 15 '10 at 14:13

Rudu

15,682
4
47
63

Maybe it should be mentioned that this isn't merely a reference but a NuGet dependency. – CodeMonkey Dec 04 '17 at 12:59

score 7 · Accepted Answer · answered Sep 15 '10 at 14:06

7

You can not connect to sqlite db using SQLProvider classes. They are for sql server. You need to use SQLite provider classes.

answered Sep 15 '10 at 14:06

Alex Reitbort

13,504
1
40
61

The url does not work anymore, I'm using Nuget to get the library, if not you can refer to https://system.data.sqlite.org – Dany Gauthier Jan 25 '16 at 16:56

score 3 · Answer 3 · answered Sep 15 '10 at 14:11

3

There's an article on MSDN magazine about just that:

http://msdn.microsoft.com/en-us/magazine/ff898405.aspx

answered Sep 15 '10 at 14:11

Jordão

55,340
13
112
144

score 1 · Answer 4 · answered Dec 29 '13 at 22:29

Download the appropriate distribution from http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki
Reference System.Data.SQLite.DLL in your project (this gives you the SQLiteConnection class)

Connect with

SQLiteConnection connection = new SQLiteConnection(@"DbLinqProvider=Sqlite;Data Source=Database.s3db"); 
Main main = new Main(connection);

See https://code.google.com/p/dblinq2007/wiki/Installation#To_use_DbLinq for details.

How can I access SQLite with C#?

4 Answers4

Linked