Unable retrieve records from SQLite database for smart device application

1
votes

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.

3
c#sqlitesmart-device
Are you using same database file in pc and device? - Renatas M.
ya, I using the database 'emp.db' in both Applications. - janasoft
try to specify full path to database. seems to me that your device app cant reach it - Renatas M.
What's the purpose of the transaction on a select statement? - Tim
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

3 Answers

3
votes

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.

2
votes

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.

1
votes
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.