Wednesday, October 8, 2008

SQL Server Compact Edition, Best Practices


Since the last April to these days, I have attended some conferences and have spoken with a several colleagues about SQL Server Compact 3.5. One thought really surprise me is that most of these people still think that SQL Server Compact is a relational database only for device developments. In fact, one of the most important features, from my point of view, of this SQL Server edition is multiplatform support, since it was introduced as is in the first betas of SQL Server Everywhere (SQL Server Compact 3.1).

SQL Server Compact 3.5 is a relational database manager specifically built for Smart Clients for occasionally connected systems. In fact, sync services for ado.net 1.0 was released with the first betas in order to introduce a set of tools working together to respond to sync solutions demands. Sync Services for ADO.NET introduce an alternative way to get the data updated between two or more data sources based on oriented serviced technology. ClickOnce, on the other hand, is other SmartClient technology.

Actually, all these technological pieces converge into a powerful set of development tools that allow easier occasionally connected systems building with all aspects, peculiarities and own features.
Probably, the key piece is the proper database, a simple sdf extension file which brings all or most important weight of responsibility. The programming model used in this kind of solutions is not so different than we have used with desktop or client/server applications but it has its own peculiarities we might know, so this database may be used on either Windows Desktop or Windows CE platforms, that’s mean either on .NET Framework or .NET Compact Framework, so, if a set of peculiarities exists and the evolution of the technology we use allows it, why don’t we create multiplatforms data access components?, in other words, for .NET Framework and .NET CF.

Actually, it has got its pros and cons, obviously. .NET Compact Framework is nearly 30% subset of full .NET Framework so to face development of multiplatform’s components is possible but with its own questions. We got an example by using transactional operations, as new feature in version 3.5, can be handled by Lightweight Transaction Manager through managed classes in System.Transaction namespace, however it may be used only on Windows Desktop platforms. Here you are an example of peculiarity on multiplatform’s code.

Despite SQL Server Compact has not been made as a great storage and processing data base we cannot forget application’s scalability knowing what kind of type are available and benefit from it in order to make the task easy to query optimizer, as far as knowing the main differences between Compact and others editions. A feature I’d like to remark is that SQL Server Compact gives an exceptional exception handling through managed classes based on native typify errors and operating system error spread as HResult.

For more than 14 months I was working trying to looking for SQL Server Compact’s development models, I synthesized the most important issues for the experimented developer with this kind of applications kick off his projects with a bit average, showing it in a eBook with few more of 110 pages which editor, SolidQ@Press, have just edited and now is available.

Application development with SQL Server Compact Edition: Best Practices do not pretend to be a reference guide, but a vertical book with alls and each important aspects a developer must know for developing applications for “the little” of SQL Server family, directly and without introductions.