Unable retrieve records from SQLite database for smart device application

Question

I am using SQLite.NET with C#[ visual studio 2008] and added the 'System.Data.SQLite.dll' as reference. The following code works with Windows Application for fetching data from my database file into a datagrid.

    private void SetConnection()
    {
        sql_con = new SQLiteConnection("Data Source=emp.db;Version=3;");
    }

    public DataTable LoadData()
    {
        SetConnection();
        sql_con.Open();
        sql_cmd = sql_con.CreateCommand();
        string CommandText = "select * from employeedetails";
        transaction=sql_con.BeginTransaction();
        DA = new SQLiteDataAdapter(CommandText, sql_con);
        DS.Reset();
        DA.Fill(DS);
        DT = DS.Tables[0];
        transaction.Commit();
        sql_con.Close();
        return DT;
    }

Invoking in:

    private void Form1_Load(object sender, EventArgs e)
    {
        EmpTable obj = new EmpTable();     
        this.dataGridView1.DataSource = obj.LoadData();
    }

The same code not working with C# 'Smart Device Application'. It shows an exception: no such table 'employeedetails'.

For Smart Device Application I have added the System.Data.SQLite.dll from "SQLite/CompactFramework" folder.

Please help me to resolve this.

Thanks in advance.

try to specify full path to database. seems to me that your device app cant reach it — Renatas M., Jun 02 '11 at 06:29
What's the purpose of the transaction on a select statement? — Tim, Jun 02 '11 at 13:01
Is this happening when running a deployed version or running against an emulator? — Jason Down, Jun 02 '11 at 13:07
Seems to be creating a new database as it can't find the existing one. Add 'failifmissing=True' onto your SQLite connection string. It will raise a SQLiteException if the database can't be found instead of creating a blank empty one. — Siy Williams, Jun 02 '11 at 16:21

Jason Down · Answer 1 · 2011-06-02T13:05:50.747

I'm not sure if this is what is causing your issue, but we've had issues in the past with mobile devices and sqlite where this solved it:

For an application using the compact framework, you need to include SQLite.Interop.xxx.DLL in your project (in addition to the regular System.Data.SQLite.DLL). Make sure you set the file to copy if newer or copy always.

Keep in mind that you can't set a reference to the interop dll. You must include it in the project by adding it as a file.

For more information check this website under the Distributing The SQLite Engine and ADO.NET Assembly section.

janasoft · Accepted Answer · 2011-06-03T14:19:17.567

Thank You Guys,

I've resolved the Problem. As 'siyw' said, the problem was the application could not find the "db file".

For windows application,

The db file has to be located in debug folder where the .exe file is generated. So that, no need to specify the path of the DB file.

For Smart Device Application,

The DB file will be generated in the root folder of the device. If we bind the db file with exe means, it wont find it and it will create a new database in root folder.

To avoid that, while crating CAB file we have to create custom folder [ ex. DBfolder ] and put the db file in the folder. Set the path in the connection String as,

sql_con = new SQLiteConnection("Data Source=DBfolder/emp.db;Version=3;");

Now, the db is found by the Application.

score 1 · Answer 3 · answered Jun 02 '11 at 16:08

1

sql_con = new SQLiteConnection("Data Source=emp.db;Version=3;");

I think you need to specify the full path here, and make sure it's correct. If the path is to a non-existent file, it will create a new emp.db database file there instead. This new file will obviously be empty and without any tables, hence it would give a no such table 'employeedetails' exception such as you're getting if you query it.

answered Jun 02 '11 at 16:08

Michael Low

24,276
16
82
119

+1 Good point. We also ran into that one as well. I think this is more likely the issue than what I suggested. – Jason Down Jun 02 '11 at 17:37

Unable retrieve records from SQLite database for smart device application

3 Answers3