Tuesday, May 4, 2010

Access a local SQL Compact database from Silverlight 4

Thanks to the new COM support in Silverlight 4, it is now possible for an elevated OOB application to access a local SQL Compact database. Keep in mind that COM suppport only is available on the Windows platform, not on Mac.

And thanks to the excellent SilverlightCOM Toolkit on Codeplex, it is very easy to use the ADO COM objects from your Silverlight application in a familiar (.NET) manner. In addition to data access, the COM Toolkit also enables access to files and directories, including file contents, registry, environment and starting processes (for now).

In order to enable access to COM objects (on Windows platforms only, of course), the following properties must be set for your Silverlight 4 project (which becomes settings in the AppManifest.xaml file):

image

image o

UPDATE: Now updated for SL4 RTM, and hosted by me: http://www.freewebsite.dk/documents/1075668907TestPage.html#/About

Source code for the demo (only for 4.0 RC) is available here.

For the sample to compile with 4.0 RTM, changes as follows:

using System.Windows.Interop;

change to:

using System.Runtime.InteropServices.Automation;

and change from:

ComAutomationFactory.CreateObject

to

AutomationFactory.CreateObject

In addition for the ComToolkit.Data class, changes  to the 3 files included in the .zip file below were required.

 

Install the Silverlight 4 runtime,  scroll down to the test application, right click it and select Install:

image

You will get this security warning:

image

Click Install to install and create shortcuts on your Desktop or  Start Menu.

This is the contents of the shortcut created:

"C:\Program Files\Microsoft Silverlight\sllauncher.exe" 2619253130.webite.se

The demo application will launch – change the connection string (which is an OLEDB connection string, not a .NET connection string) to use the SQL Compact OLEDB provider, and change the path to point to a SQL Compact file on your local disk:

Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source=c:\install\projects\nwtest.sdf

image

Change the SQL statement to a valid statement for your database:

SELECT * FROM [Employees]

Press Connect to open the connection to the database and then press Execute to run the query, and voilá, you are accessing a local SQL Compact database with Silverlight 4!:

image

UPDATE: A full VS 2010 solution is now available for download:

8 comments:

ErikEJ said...

More ideas on how to use Silverlight COM here: http://justinangel.net/CuttingEdgeSilverlight4ComFeatures

MagicMax said...

unfortunately, this is not cross-platform approach...

ErikEJ said...

True, as I mention: on Windows platforms only, of course

Richard Allwood said...

What about if you have an SDF file that you want to host on the same site as silverlight?

ErikEJ said...

Not possible, must reside on local file system. Or you must access remote sdf via OData or similar.

Piotr Łuksza said...

Is there any way to increase performance? getting few records from any table takes 10seconds ...

ErikEJ said...

Piotr: Do you have the required indexes, otherwise probably not

Christian said...

Gracias, Me sirvio de Mucho tu Ejemplo... Saludos!!!