How to connect to an SQLite(CE) database on a windows mobile phone?

Question

Hi

I want to make a database for my mobile 6 cellphone app. What I did was in visual studios I went to my project name and right clicked on it, went to add -> add new item -> data -> choose datafile.

It created a database file called "AppDatabase1.sdf"

Now I want to connect to my database but I don't know how what the connection string is since I see no app.config where I normally would expect it to be.

So I just have

 SqlCeConnection conn = new SqlCeConnection("Not sure what to put here");
 conn.Open();

When I click on the server explorer tab I see my database so I click on it. In the properties section it has something labeled "connection string" however it looks like the path to the actual file and when I run it in the emulator I get an error.

"The database file cannot be found. Check the path to the database."

Thanks

Answer 1

SqlCeConnection connection = new SqlCeConnection();
connection.ConnectionString = "Data Source =" + filename + ";password=" + password;
connection.Open();

Of course, the password=<password> part is required only if you protected the DB, which is not the case by default.

BTW, this isn't SQLite, it's SQLServer CE (or SQLCE for short).

EDIT: If you only specify the file's name (no path), make sure that the file is in the current working directory. Obviously, according to the error message, there's something wrong with the way you specify the filename. My bet is on the path, just specify it and you should step forward.

EDIT2: In order to check if the problem is in your program or in the DB file, download DataPort Console and use it to open your DB in the device. That wayn you can validate that your DB can be accessed correctly.

Answer 2

You don't have to create the database outside your application. It is probably better if you had a fail-safe so that if the user accidentally deletes it or it does not exist, then you can re-create it easily. This is good for initial installations. The following code will do what you want (SQLCE/Mobile 2005 example):

Private Sub ConnectToDB()
        If (File.Exists("\SystemCF\LPI\inventory.sdf")) Then
            Try
                cn = New SqlCeConnection("Data Source=\SystemCF\LPI\inventory.sdf;Password=")
                cn.Open()
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            End Try

        ElseIf (File.Exists("\UserCF\LPI\inventory.sdf")) Then
            Try
                cn = New SqlCeConnection("Data Source=\UserCF\LPI\inventory.sdf;Password=")
                cn.Open()
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            End Try

        Else
    Try
       If (Not (Directory.Exists("\SystemCF\LPI"))) Then
                Directory.CreateDirectory("\SystemCF\LPI")
       End If

       Dim engine As New SqlCeEngine("Data Source=\SystemCF\LPI\inventory.sdf")
       engine.CreateDatabase()

       cn = New SqlCeConnection("Data Source=\SystemCF\LPI\inventory.sdf")
       cn.Open()

       Call dropAndCreateDatabase()
     Catch ex As Exception
       MessageBox.Show(ex.Message)
       MessageBox.Show("Unable to create database!", "Database Create Failed")
     End Try
End Sub