CodeSnippet: Using SQL Compact Edition
Many multi-user applications require a database, be it a web app (probably more often a web app!) or win forms. In single user applications, often it is beneficial if there is a database (in speed, complexity and robustness), but the problem is distributing the database server. I’ve had a few applications that come bundled with MySQL, and it just plain ole sucks.
Some of the reasons for using a database in your application include
- Faster than flat file storage such as XML
- Can have complex relationships
- Can have varying datatypes that don’t break an XML parser
- Can then later redeploy in another project, such as a website using SQL Server easier
One of the easier ways to distribute your winform/WPF application with a database is with Microsoft’s SQL Compact Edition (SQLCE). It has a fairly low footprint, is very fast, and is reasonably powerful despite the reduced operations (which allows it to be fast/low footprint/etc). Visual Studio’s deployment tools even allow you to check for the SQL CE prerequisite on the target machine before installing, and will download SQL CE if it isn’t found!
Adding SQL CE to your project
Providing SQL CE was installed during your Visual Studio install (default install settings), to get a SQL CE database in to your project, all you need to do is add a New Item > Local Database to your project. This will automagically add the references your project will need to access SQL CE databases (although you’ll still need Using System.Data.SqlServerCE;)
Double clicking on your new database in the Project Explorer will activate the Server Explorer, allowing you to add Tables to the database.
Using SQL CE
The way you access data is nearly identical to a normal SQL Server, except it all lives in the System.Data.SqlServerCE namespace, and usually has SqlCeClass rather than SqlClass. ie:
SqlCeConnection sqlCon = new SqlCeConnection(TestApp.Properties.Settings.Default.TestConnectionString); SqlCeCommand sqlCom = new SqlCeCommand(); sqlCom.Connection = sqlCon; sqlCom.CommandText = "SELECT * FROM TestTable"; SqlCeDataAdapter sqlDa = new SqlCeDataAdapter(sqlCom); DataSet ds = new DataSet(); sqlCon.Open(); sqlDa.Fill(ds); sqlCon.Close();
Using LINQ with SQL CE
In .NET 3.5, you can also use LINQ against SQL CE. Currently (VS2008) you have to use the SQLMetal tool from the Visual Studio CLI, then add the generated dbml file to your project (Add -> Existing Item)
SqlMetal SqlCeFileName.sdf /dbml:LinqClassName.dbml
In the new version of MahTweetsMediaHorn (correction, thanks Will), I’ll be using my own database rather than relying on Windows Media Players database. The LINQ to select all the albums (after generated the dbml via SqlMetal) is..
Music db = new Music(MediaHorn.Properties.Settings.Default.MusicConnectionString); var album = from a in db.Album select a; listBox2.ItemsSource = album;
Any and all of the usual tricks for LINQ apply to SQLCE - once you generate the dbml via SQLMetal, it really doesn’t care what is underneath. If you want to bind it properly to WPF with automagic updates for when the datasource updates, I strongly recommend looking into the very clever SyncLINQ by the equally clever Paul Stovell, which also supports Silverlight.
Deploying apps with SQL CE
As I mentioned, the Visual Studio “Publish” tool makes sure the target system has all the prerequisites before installing. SQL CE is automatically added to the list, but if you need to modify it, right click on your project, Properties, then down to the Publish tab, and the Prerequisites option will launch a new dialog.
Just make sure you distribute your application via Build -> Publish :)