Wednesday, August 27, 2008

Detecting the installed SQL Compact OLE DB providers

Here is a minor (but important) improvement to the CreateSDF project: installed SQL Compact engine detection. Why should the little application allow the user to select all versions of the SQL Compact engine when the device has only one installed? We are opening the door for a bad user experience and some unexplained errors. So what can we do to detect the installed SQL Compact engines?

A word of caution is required here: this method detects only the installed OLE DB providers, not the SQL Compact engines. From version 3.0 onwards, Microsoft split the engine interfaces into two different stacks (managed and native) that are separately installable. This means that you can install the managed SQL Compact 3.5 SP1 stack without installing the native one. This methods detects only the installed OLE DB stacks.

The process of detecting the installed OLE DB providers is quite simple: just try to instantiate the IDBInitialize interface using the engine's CLSID:

bool IsInstalled(const CLSID& clsid)
{
CComPtr<IDBInitialize> spInitialize;
HRESULT hr;


hr = CoCreateInstance(clsid, NULL, CLSCTX_INPROC_SERVER, IID_IDBInitialize, (void**)&spInitialize);

return SUCCEEDED(hr);
}


You simply use this code like this:

if(IsInstalled(CLSID_SQLSERVERCE_3_5)) ...

In the revised sample, this test is used to filter out the nonexistent OLE DB providers from the engine combo box.

2 comments:

Scott said...

Hi,

I was wondering if you could expand a bit about different native vs managed SQL Server engines.
There is currently a bug in the managed ADO.NET provider for SQL CE that prevents streaming blogs out of a database. There is some information about it

here:

http://stackoverflow.com/questions/399719/hacky-sql-compact-workaround

and

here:

http://social.msdn.microsoft.com/forums/en-US/sqlce/thread/3cce86b2-9866-4f91-b8c8-57b7e7af0b98/

There is a hacky workaround I have that uses reflection to update private fields.

This, of course, isn't something I want to ship, because I don't want Windows Update to break my app on my customer's machines.

I was thinking I could use the native interfaces to access the database and stream the data that I need. It's important, for perf reasons, that I stream blobs out of the database rather than buffer them.


In reading your blog post, which talks about the native stack using a different engine from the managed stack, I was thinking this might not be possible.

SQL CE does not allow two processes to access the same DB concurrently. Do you know if 2 different engines in the same process will behave the same as 2 seperate processes? If they do, then I can't use the native workaround I was thinking of.

Thanks.

Scott said...

That should say "streaming blobs" not "streaming blogs"