sqlite3.dll and system.data.sqlite.dll

Question

Hello people I've been struggling to use sqlite in my C#2.0 application and I have finally decided to get rid of assumptions and ask really basic questions.

When I created a database say iagency with table users, from external tools like firefox plugging and another sqladmin tool I can't query it from sqlicommand inside vs2005 it displays System.Data.SQLite.SQLiteException:Sqlite Error no such table users, please be assured that I've made reference to system.data.sqlite installed with SQLite-1.0.61.0-setup

When I do the opposite like create a database and a table from VS server explorer and VS database gui tools it can't be queried neither but can be seen by other tools, but tables created through query from VS using stringbuilder eg create table bla bla. it can be display in a datagrid but none of the tools can see and display that table.

WHAT DO I NEED EXACTLY TO MAKE SQLITE WORK IN MY APPLICATION?

I've tried to add sqlite3.dll of sqlitedll-3_6_14.zip downloaded from sqlite site under section precompiled binaries for windows as reference to my application but it fails with make sure it's accessible an it's a valid assembly or com component.

Answer 1

I downloaded this SQLite-1.0.61.0-setup.exe Ran the installation then I wrote this to access the firefox favorites sqlite db.

using System.Data.SQLite;  // Dont forget to add this to your project references
                           // If the installation worked you should find it under
                           // the .Net tab of the "Add Reference"-dialog

namespace sqlite_test
{
    class Program
    {
        static void Main(string[] args)
        {
            var path_to_db = @"C:\places.sqlite"; // copied here to avoid long path
            SQLiteConnection sqlite_connection = new SQLiteConnection("Data Source=" + path_to_db + ";Version=3;New=True;Compress=True;");

            SQLiteCommand sqlite_command = sqlite_connection.CreateCommand();

            sqlite_connection.Open();

            sqlite_command.CommandText = "select * from moz_places";

            SQLiteDataReader sqlite_datareader = sqlite_command.ExecuteReader();

            while (sqlite_datareader.Read())
            {
                // Prints out the url field from the table:
                System.Console.WriteLine(sqlite_datareader["url"]);
            }
        }
    }
}

Answer 2

Try opening up the database in the command line SQLite tool (from SQLite.org), and check the schema.

You can check the schema in this way:

.schema

This will dump out all the SQL necessary to create the tables in the database. Make sure the table is there, with the name you assume it should have.

You do not need the .dll file from SQLite.org, all you need is the assemblies from System.Data.SQLite.

Answer 3

For me - this link helped a lot at start.

Was harder to get subsonic work, to make database accessible through web application -
but that's another story.