How can I access SQLite with C#?

Question

Hi everybody,

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.

Answer 1

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

Answer 2

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

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

Answer 3

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

using System.Data.SQLite;
using System.Data.Common;


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())
  {
    //...
  }
}