VSM Cover Story
Lighten Up Your Local Databases
Put local data storage on a resource diet and gain performance with the newly upgraded (and free) SQL Server 2005 Compact Edition.
Technology Toolbox: VB.NET, C#, SQL Server 2005, XML, SQL Server 2005 Compact Edition runtime, Visual Studio 2005 Standard Edition SP1 or higher, SQL Server Management Studio [Express] SP2
SQL Server 2005 Compact Edition (SSCE) is Microsoft's recently released contender for lightweight champion of the desktop, laptop, and Tablet PC relational database market.
SSCE is an easy-to-deploy transactional database with a nominal 1.8-MB disk and 5-MB memory footprint. Contrast this with SQL Server 2005 Express Edition (SSX), which is a 53.8 MB download that expands to 197 MB on disk and starts at about 80 MB memory usage. SSCE also is a componentized, in-process database limited to a single-user. You can incorporate the SSCE database engine, query processor, and replication/synchronization components with your smart-client Windows app in a small Click Once MSI package. The components include a .NET managed provider that emulates SqlClient and an optional OLE DB provider. SSCE supports strong password protection and 128-bit RSA encryption. Its code-free database files and in-process architecture minimize exposure to malicious attacks.
SSCE provides several advantages over previously existing Microsoft database technologies if you need a lightweight database solution. For example, many developers find that the typed DataSets they persist as XML files load too slowly, and that SQL Server Express is more than they need for a local data store. Others want to simplify replication with SQL Server 2005 or SQL Server Express. SSCE's small disk and memory footprints make it likely that you'll be able to use it in a multitude of desktop client projects that can benefit from short- or long-term data caching with a lightweight, high-performance database and query engine that's freely distributable.
SSCE's primary role is delivering an efficient, strongly typed client-side data cache that's especially suited for occasionally or usually connected Windows systems. SSCE offers ADO.NET developers a secure, high-performance alternative to persisting DataSets as clear-text XML files. I'll walk you through how to create SSCE database files and add tables and indexes graphically with the Object Explorer of SQL Server Management Studio (SSMS) SP2 or its Express Edition (SSMSX) SP2; and Server Explorer of VS 2005 Standard Edition SP1 or higher. (The VS Express Editions don't include Server Explorer.) I'll also explain how SSCE's subset of SQL Server data types and T-SQL syntax differs from that of SQL Server 2005. This article's sample solution relies on VB code to add tables, indexes, and foreign key constraints, as well as create, populate, encrypt, compact, and repair SDF databases. To run the SSCETest.sln sample project (see the Go Online box for details), you must download and install the SSCE v3.1 runtime bits (see here) or run the Orcas January 2007 Community Technology Preview (CTP), which installs an early version of SSCE v3.5.
An SSCE database is an ideal candidate for storing large amounts of slowly changing reference or catalog information because you don't need to repopulate datasets from giant XML files when your client loads. My "Store Large Lookup Tables in DataSets" article in the July 2004 VSM edition provides typical loading times and memory consumption for various DataSet sizes. Your app can keep the reference data up-to-date by push or pull synchronization with Remote Data Access (RDA) and SQL Server 200x or SSX, or merge replication with SQL Server Workgroup Edition or higher. (SSX is a merge-replication subscriber only.) Merge replication lets you create the SSCE tables from the replication snapshot. Microsoft Synchronization Services (code-named OCS for occasionally-connected systems) became available as a CTP in late January 2006. OCS enables desktop, laptop, and Tablet PC SSCE clients to synchronize with SQL Server 2005, SSX, and SSCE databases. I'll cover OCS, RDA, merge replication, and how to use the Click Once bootstrapper to create a "central" MSI package in a future VSM article. A central installation, which requires Administrator credentials, puts the SSCE runtime files in a standard location to enable maintenance by Windows Update. Note that private deployment doesn't require Administrator credentials.
The SSCE database engine and query processor have been honed by six years of use with devices, initially as SQL Server CE 1.0 with Embedded VB and VC—launched in 2000—and later as SQL Server CE 2.0, which shipped with VS 2003 and the Compact Framework. VS 2005 includes SQL Server Mobile Edition 3.0, which Media Center PCs, Windows Vista, and the MSN Client use for data caching chores. Microsoft licensed versions 1.0 and 2.0 for use on devices only, then extended the 3.0 license to Tablet PCs. SSCE (version 3.1) is a substantial upgrade to Mobile Edition 3.0 and is licensed for standard PCs also. SSCE was called SQL Server Everywhere Edition during its Community Technical Preview (CTP) adolescence. Sybase claims "SQL Everywhere" as a trademark, so Microsoft decided that the original "Compact Edition" moniker would "avoid confusion." SSCE's maximum database size is 4GB—the same as SQL Server Express.
Create Your First SSCE Database
You can create a new SSCE database and add tables using any of four methods: Server Explorer in VS 2005 Standard Edition or higher SP1, SSMS SP2, SSMSX SP2, or with C#/VB code. To create a clear-text or encrypted database with VS 2005 Standard Edition SP1 or higher, open Server Explorer, right-click on Data Connections, and choose New Connection to open the Choose Data Source dialog. Then select Microsoft SQL Server Mobile Edition in the Data Source list, select .NET Framework Data Provider for SQL Mobile Edition in the Data Provider list, and click on Continue to open the Add Connection dialog. Click on the Create button to open the Create New SQL Server Compact Edition Database dialog. If you're using SSMS or SSMSX SP2, select SQL Server Compact Edition from the Server Type list in the Connect to Server dialog, and then choose <New Database File ?> in the Database File list to open the same Create New SQL Server Compact Edition Database dialog (Figure 1).
You can add tables, columns, default values, primary keys, and indexes with basic Edit Table or Edit Index dialogs (Figure 3). Note that there is no Table Designer to add these items. There's also no Open Table menu choice to display, add, or edit table data. There are no Table Design or Open Table Definition menus, so you have to write T-SQL data-definition language (DDL) code to add or modify relationships (foreign-key constraints) and check constraints. Fortunately, SSCE implements a subset of SQL Server 2005's T-SQL, so you won't need to relearn an entirely new SQL dialect. Unfortunately, many features in SQL Server 2005 [Express] that you've come to depend on are missing, including user-defined functions, stored procedures, triggers, and CLR integration (Table 1).
SQL Server 2005 Compact Edition is designed for minimum impact on disk and memory resources, as well as easy deployment, so you shouldn't expect SSCE to be a SQL Server 2005 clone. These limitations mean that it isn't likely that you'll be able to downsize an app that uses a SQL Server 2005 or SSX data sources to SSCE. On the other hand, you'll probably be able to upsize SSCE databases to SQL Server 2005 or SSX and reuse almost all your code fairly easily.
Note that using SSCE as a data source for Web applications running in the Internet Information Services (IIS) process is no longer prohibited, but Microsoft discourages this technique by blocking it by default. To enable using SSCE with ASP.NET, add this instruction to your code:
Create SSCE Objects with Managed Code
Your alternative to graphic designers for creating SSCE database objects is to write managed code. You'll also need VB or C# code to display, add, edit, or replicate data. SSCE supports DataSets, so it's a simple task to use the Data Source Wizard to create a database data source, drag a table to a Windows form, and generate a DataSet, BindingSource, BindingNavigator, and DataGridView or bound text boxes for data entry and editing. My online "SQL Server Mobile Goes Everywhere" article includes step-by-step instructions for creating a parent-child form from the Northwind.sdf sample SSCE database that's included with the SDK (see Go Online for details). However, most developers will probably use SSCE as a lightweight DataSet replacement, so it's better to save this approach for demos.
All SSCE managed code requires a reference to the System.Data.SqlServerCe namespace provided by the SqlCeConnection object. Running the installer puts the System.Data.SqlServerCe.dll in C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PublicAssemblies\ and adds it to the GAC. Simplify your code by adding an Imports System.Data.SqlServerCe or by using a System.Data.SqlServerCe directive. Then use this connection string and the SqlCeEngine object to create a new encrypted database with a 1-GB maximum database size:
SSCE 3.1 translates the Data Source's |DataDirectory| macro to the ?\ProjectName\bin\Debug folder by default. LCID is the locale ID for the sort order. The default ssce:Max Database Size value is 128 MB, the minimum is 16 MB, and the current maximum is 4,091 MB. The maximum size you specify doesn't affect the initial database storage requirement.
Data Source, Password, and Database Size are only a few of the SqlCeConnection.ConnectionString properties; you can find the complete list of properties by searching on "ssce: mode" for the properties table in SQL 2005 Compact Edition Books Online. Note that the current index doesn't contain SqlCeConnection or ConnectionString entries; you must search for most method or property names. If the defaults are OK, you can shorten the connection string to this:
Data Source = |DataDirectory|\NwindTest.sdf
You can find a production quality version of these snippets in the online sample code for this article available here. You can find them in the CreateCeDatabase procedure of the frmResultset class.
Create Connection and Command Objects
You create tables by constructing SqlCeConnection and SqlCeCommand objects and executing DDL statements:
SqlCeConnection, SqlCeCommand, SqlCeDataReader, and SqlCeResultSet use syntax similar to their ADO.NET 2.0 SqlClient equivalents, although the scrollable, updatable SqlClient.SqlResultSet cursor object was removed before RTM. SSCE developers are fortunate to have this versatile cursor for lightweight databinding scenarios with BindingSource, BindingNavigator, and DataGridView combinations.
There's also a SqlCeCommand.CommandType.TableDirect property value that decrees a valid table name as the CommandText value. This property value lets you use the high-speed SqlCeDataReader.Seek method on indexed columns to position the record pointer. SSCE Books Online (BOL) asserts: "TableDirect is only supported by the .NET Framework Data Provider for OLE DB." However, it appears to work as expected with SqlServerCe in the sample project's AddOrDropRandomOrders procedure. Unfortunately, BOL's help topic for the Seek method doesn't include an example that illustrates how to use it. SSCE throws an exception if you specify CommandType.StoredProcedure.
SSMS[X]'s Script Table As context menu command is missing for SSCE databases, so you can't generate CREATE TABLE or ALTER TABLE scripts that you incorporate into your code to create tables, constraints, or both automatically. If you're cloning a SQL Server table, you can right-click on the SQL Server table and choose Script Table As | CREATE to | New Query Window to deliver the DDL command (Listing 1). Unfortunately, it requires major surgery to edit the auto-generated T-SQL to accommodate SSCE's less articulate DDL syntax (Listing 2).
The sample project's CreateSqlCeConnection contains the connection code and the CreateOrDropTables procedure executes T-SQL DDL to generate the eight original Northwind tables and their primary keys. The CreateOrDropIndexes and AddOrDropFKConstraints procedures add 19 indexes and seven foreign-key constraints, respectively. Creating a clone of the Northwind database and its tables, indexes, and constraints takes less than 0.15 second on a fast machine. The initial database size is 20 kB; tables, indexes, and constraints add 192 kB.
Solve the Population Dilemma
You're stuck with creating either a databound UI or writing code to populate SSCE tables. You must also bear in mind that SSCE doesn't support SELECT INTO. If you use merge replication, you can populate the tables from the server publication's replication snapshot and then merge post-snapshot modifications. Alternatively, you can pull data from the server with RDA. You can take advantage of SSCE's scrollable, updatable SqlCeResultSet cursor to prepopulate the tables from a SQL Server or other relational data source. If you have a SqlCeDataReader with rows from the source table, you can create SqlCeUpdatableRecords and apply the Insert method in a SqlCeDataReader.Read loop with code like this:
This nested loop assumes that the destination table's first field is an identity column. For an all-column insert, you'd probably want to replace the individual GetValue(intCol) and SetValue(intCol) methods with a single GetValues(objRow) and SetValues (objRow) pair. However, this line throws an exception, with or without the new constructor:
Dim objRow As [New] Object()
SSCE implements implicit SQL Server-style autocommit transactions by default, but you can add explicit transactions to DDL and INSERT/UPDATE operations with conventional ADO.NET syntax:
Dim trnDest as SqlCeTransaction
'Start the transaction
trnDest = cnnDest.BeginTransaction(IsolationLevel)
cmdDest.Transaction = trnDest
'Commit or roll back
If blnSuccess Then
SSCE features four utility methods: Compact, Shrink, Verify, and Repair. Execute them by right-clicking on the database node in Server Explorer or SSMS[X], and then choosing Database Properties or Properties to open the dialog of the same name. Finally, select the Shrink and Repair page (Figure 5). As when working with Jet, deleting a row doesn't remove the row from the table, but instead marks the row as deleted. Compact and Repair are implemented similarly to their Jet counterparts. Compact creates a new database file and copies all database objects into it. If you don't use the UI, it's up to you to write file-system code to copy the new file over the original and then delete the extra file (Listing 3). You can change the password, maximum size, and other connection string parameters of the new database. The Shrink method reclaims unused pages in the database file, but only Compact reclaims unused space within pages. AutoShrink reclaims unused pages when their size grows to the percentage of file size you specify with the ssce:AutoShrink Threshold connection string property. Verify recalculates and compares page checksums and generates a DatabaseName.log file. Repair includes an option that attempts to fix damaged pages whose current checksums don't match the saved checksum value; the alternative is to discard damaged pages (Listing 4). All connections to the database must be closed to execute the Compact and Repair methods.
Download the SSCE runtime, SDK, and Tools for VS 2005 SP1, install SSMS[X] SP2 CTP, and then give the SSCETest.sln sample Windows form project a test drive. (You can run the project but not manage the databases in Visual Basic Express). Yes, there are some downsides in terms of missing features, but the small size and memory footprints, the fact that the database engine is freely distributable, and the robust functionality of SSCE mean that it should find a welcome home in a surprising number of scenarios within corporate computing environments. What's more, SSCE comes with a bonus—you don't need to relearn a SQL query language or ADO.NET syntax to use it.