sql-server-ce compact-framework windows-ce versioning handheld

Which SqlServerCe DLL version do I need to reference in my project to match what's in sqlce.wce4.armv4.CAB?

The "rest of the story" is told below in all its gory details, but to cut to the chase, it comes down to this:

SQLCE 2.0 (contained in sqlce.wce4.armv4.CAB) seems to be installed on the device on which my app runs; the source project references SqlServerCe with a Runtime Version of v2.0.50727 and a Version of 3.5.1.0

Is this a match or a mismatch? If the latter, which version of SqlServerCe.dll do I need to reference in my project?

THE REST OF THE GORY STORY

My Windows CE app is failing when it attempts to open a SQL CE (.SDF) file; altho an underlying earlier problem was apparently connected (no pun intended) with versioning (calling SqlCeEngine.Upgrade() got me past it), the err msg now indicates a password problem. See this

But nowhere do I see where a password is being set on the table - nor where a SQLCE version is being specified.

We have a setup utility that installs this app along with the necessary ancillary files on the handheld device. In trying to figure out what exactly the two apps (the install app the app proper) are doing regarding versioning and password, I searched for "SDF" and "SQLCE" in both code bases, and here is what I found*:

The only reference to "SDF" in the Setup utility is:

{_T("OpenNETCF.SDF.WCE4.ARMV4.CAB"),_T("OpenNETCF SDF v1.4"),(DWORD)0,true},

The only references to "SDF" in the app itself do not create a SqlCe ("SDF") database. Where such references do exist, they look for a specific SDF file that already exists like so:

filename = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Personal), "HHSDB.SDF");

Then the engine database engine is instantiated and upgraded and a database is conditionally created (in my case, the file exists, so CreateDatabase() is not called):

engine = new SqlCeEngine(conStr);
engine.Upgrade(conStr); // <= Recommended by ctacke

if (File.Exists(filename))
{
    MessageBox.Show(string.Format("file {0} exists", filename)); // TODO: Comment out or remove
}
else
{
    engine.CreateDatabase(); 
}

The only references to "SQLCE" in the Setup utility are:

//{{NO_DEPENDENCIES}}
// Microsoft Visual C++ generated include file.
// Used by HHSetupCon.rc
//
. . .
#define IDR_NETCF                       103
#define IDR_SQLCLIENT                   104
#define IDR_SQLCEDEV                    105
#define IDR_SQLCE                       106
. . .

IDR_SQLCLIENT           BIN                     "J:\\sql.wce4.armv4.zip"
IDR_SQLCEDEV            BIN                     "J:\\sqlce.dev.wce4.armv4.zip"
IDR_SQLCE               BIN                     "J:\\sqlce.wce4.armv4.zip"

{_T("sqlce.dev.wce4.armv4.CAB"),_T("Microsoft SQLCE 2.0 Dev"),IDR_SQLCEDEV,false},
{_T("sqlce.wce4.armv4.CAB"),_T("Microsoft SQLCE 2.0"),IDR_SQLCE,false},

References to "SQLCE" in the app itself create or query SDF tables, such as:

// This creates a table (a *table*, not the database itself)
public static void CreateSettingsTable()
{
    try
    {
        string sqlddl =
            "create table platypus_settings (setting_id int identity (1,1) Primary key,  setting_name nvarchar(40) not null, setting_value nvarchar(63))";
        DBConnection.GetInstance().DBCommand(sqlddl, false);
    }
    catch (SqlCeException sqlcex)
    {
        SSCS.ExceptionHandler(sqlcex, "DBUtils.CreateSettingsTable");
    }
    catch (Exception ex)
    {
        SSCS.ExceptionHandler(ex, "DBUtils.CreateSettingsTable");
    }
}

Note: DBConnection is a custom class that contains, among other things, some SqlCe* members:

SqlCeConnection objCon = null;
SqlCeEngine engine;
public SqlCeTransaction SqlTrans;

...so, neither the util nor the app itself seem to create the SqlCe database (HHSDB.SDF) - the app simply locates the .SDF file and then reads from it/writes to it.

It would seem that when the app creates a table (as in CreateSettingsTable() above) and suchlike activity, it would use a SqlCe DLL to do that, and since ctacke said that the version of that DLL (the one on the device) has to match the one affiliated with the project's source code, there should actually be one on the device, but since I do not see any SqlCe DLL on the device, it's impossible to verify that the versions on the device and the project build environment match...

Specifically: There is no sqlserverce.dll on the device; is it some other file I should be looking for? The lack of this DLL must not be a problem in itself, otherwise I would not get past the following code (which I do):

engine = new SqlCeEngine(conStr);

The System.Data.SqlServerCe DLL referenced in the project is Runtime Version "v2.0.50727", Version "3.5.1.0"

The setup project has no such references, AFAICT (it's a C++ project). However, it does have this code:

{_T("sqlce.dev.wce4.armv4.CAB"),_T("Microsoft SQLCE 2.0 Dev"),IDR_SQLCEDEV,false},
{_T("sqlce.wce4.armv4.CAB"),_T("Microsoft SQLCE 2.0"),IDR_SQLCE,false},

So it installs cab files for SQL CE version 2...does version 2 here correspond with "Runtime Version" or "Version" in the app's referenced SqlServerCE DLL?

I would think these cabs are supposed to be expanded/contain the SqlCe DLL[s] but, as I wrote, I don't see any on the device subsequent to running this setup util (which seems to work fine).

So, to sum up and reiterate:

Is this a match or a mismatch? If the latter, which version of SqlServerCe.dll do I need to reference in my project?

"The po-lice at the port of entry say..." - sorry, couldn't help myself (or didn't want to is more like it)

UPDATE

These are some files on the device:

enter image description here

UPDATE 2

Altho it's superficially obviously, based on the filenames, that these are version 3.5, I opened them in JetBrains' dotPeek in an attempt to verify that, but that fell flat - I get, "(not supported)" for those DLLs.

Attempting the same thing in Reflector gave me a little more info: "'C:\Bla\sqlceca35.dll' is not a .NET module."

Solution

Yes, you have a mismatch.

You have a database file that internally indicates that it is a SQL Compact 2.0 file.

You have a System.Data.SqlServerCe.dll assembly that is version 3.5.1. The "runtime version" means what CF runtime was it built against, so it was built against the 2.0 runtimes - it doesn't mean that it is 2.0 itself. This was the point of confusion - you kept naming two versions and an assembly has only one version.

This was my suspicion because you're calling Upgrade() which doesn't even exist in SQL Compact 2.0.

If you want to open the original database, in it's original state, and allow it to still be accessible by the original creator (and there has to be code that creates it or it's deployed already-built in a CAB file, there's no implicit way it could happen) then one again I reiterate, you must use the same version of SQL Compact to do so.

In your specific case what this means is that you should do the following:

Change the project reference (this means in Studio, on your PC) to point to a System.Data.SqlServerCe.dll that has an assembly version of 2.0.xxx.
Make sure that the device has the exact same version (2.0.xxx) numbered SQL Compact files on it. This includes System.Data.SqlServerCe.dll as well as all of the sscexx.dll native files. There are about a half dozen.

You do not need to call Upgrade() unless you want to make it a 3.5 database, at which point you can't go back, plus you'll be trying to upgrade every time you run, which I doubt is what you want anyway. That method is generally for utilities or one-time use.

It doesn't matter how many other versions of the assemblies are on your PC, and in fact you can have multiples on the device too. The important thing is that what you have in your project reference, meaning what the app is linked to, is the same DLL that gets loaded by the application on the device when it actually executes.

Generally I do 2 things to make sure this happens:

Put all of the SQL CE stuff right into the app folder on the device, not distributed via CAB but deploy each individual file, to make sure this is what happens.
Reference directly to the file on your PC. Don't use Studio's "magic" method of finding a .NET reference, but browse directly to the System.Data.SqlServerCe.dll you know is right and add the reference that way.