Microsoft SQL Server Compact 4.0 is the next generation of embedded database from the SQL Server family, and brings all of its goodness to the world of ASP.NET web application development. SQL Server Compact 4.0 will provide an easy and simple to use database storage for starter websites, and the features of Compact that wooed the mobile devices and desktop developers will now be available to the ASP.NET web application developers. In addition, the features and enhancements in SQL Server Compact 4.0 like the new Transact-SQL syntax for OFFSET & FETCH, higher reliability, seamless migration of schema and data to the higher SKUs of SQL Server, support for code-first & server generated keys in ADO.NET Entity Framework 4.0, and the ability to use Visual Studio 2010 to develop ASP.NET web applications for Compact 4.0 etc. will also excite the existing development community.
The first Community Technology Preview (CTP1) release of SQL Server Compact 4.0 is integrated with the WebMatrix Beta, which is a free ASP.NET web application development tool for Windows, and provides development & database support, and can easily publish/deploy websites to the web hosting providers. SQL Server Compact 4.0 CTP1 plays an important role in WebMatrix Beta, and enables WebMatrix Beta to be a small download, with a simple development experience, and contributes to the seamless end-to-end experiences from development through to publish. Scott Guthrie’s (ScottGu) blog has detailed information about the WebMatrix Beta and its different components. WebMatrix Beta can be installed in one-click through the Microsoft Web Platform Installer (Web PI) 3 Beta. In addition to the Web PI 3 Beta, SQL Server Compact 4.0 CTP1 is also available for download from the Microsoft download center.
The topics that are covered in the blog are listed below. SQL Server Compact will be referred to by its abbreviated form wiz. Compact throughout this blog for easy reading:
1. Specialty of SQL Server Compact – Highlights the reasons for using SQL Server Compact for application development. The section will be useful for the ASP.NET developers and will be a reaffirmation for the existing Compact developers.
2. New Features in SQL Server Compact 4.0 CTP1 – The section lists the new features in the first CTP release of Compact 4.0 and will be of interest to both the ASP.NET and the existing Compact development community.
3. Known Issues in SQL Server Compact 4.0 CTP1 – The known issues in the first CTP release of Compact 4.0 and the workarounds are listed in this section.
SQL Server Compact has many specialties that are well liked by the developer community. The specialties of Compact, among others, are as listed below:
· Small Size: Easy to download with a small size of 2 to 2.5 MB and installs fast with no configuration needed.
· Program with .NET Framework APIs: Simple to use because of the familiar .NET Framework APIs, including ADO.NET and ADO.NET Entity Framework, and similar programming patterns.
· Supports Transact-SQL Syntax: Supports a rich subset of the Transact-SQL syntax supported by higher SKUs of SQL Server.
· Private (xcopy) Deployable: Xcopy deployable within the application folders and no admin privileges are needed. The database file contains no code and is safe to deploy along with the database.
· In-process Database: Loads in the application’s process memory space and requires no inter-process communication.
· Integrated with Visual Studio: Applications can be rapidly developed using the integrated development experience in Visual Studio.
· Easy Migration to SQL Server: Smooth on-ramp to SQL Server when the applications requirements grow and there is a need to handle large amounts of data and high concurrent loads due to multiple users.
· Free Product with Free Tools: Compact is a free product, and is integrated with the free Express versions of Visual Studio and WebMatrix Beta, and requires low investment to start building and deploying applications.
· Simple Solutions for Syncing data with SQL Server: Schema and data can be synced easily with SQL Server using inbuilt and programmable technologies like Sync Framework (Sync FX) and merge replication.
The first CTP release of SQL Server Compact 4.0 has a lot of features that will be useful to all of the development community of Compact, including the latest entrants, the ASP.NET developers. The different features of SQL Server Compact 4.0 CTP1 categorized separately are as described below:
The set of features in this category are improvements that made Compact better, more reliable and secure. More details about the features are given below:
· The query below skips the first 10 rows and fetches all the other rows in the Employees table ordered by the Hire Date:
SELECT * FROM Employees ORDER BY [Hire Date] OFFSET 10 ROWS;
· The first query below skips the first 10 rows and fetches the next 10 rows in the Customers table and the rows are ordered by the Customer ID. The second query skips the first 2 rows and fetches the next one row in the Orders table ordered by the Shipped Date. This query a variation of the first query:
SELECT * FROM Customers ORDER BY [Customer ID] OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;
SELECT * FROM Orders ORDER BY [Shipped Date] OFFSET 2 ROWS FETCH NEXT 1 ROW ONLY;
SqlCeConnection conn = new SqlCeConnection("DataSource=Northwind.sdf");
DataTable dt = conn.GetSchema();
dt = conn.GetSchema("Tables");
dt = conn.GetSchema("Columns");
dt = conn.GetSchema("Indexes");
dt = conn.GetSchema("IndexColumns");
dt = conn.GetSchema("ForeignKeys");
catch (Exception e)
System.Data.SqlServerCe.SqlCeConnectionStringBuilder builder =
builder["Data Source"] = "Northwind.sdf";
builder["Mode”] = "Exclusive";
The result is the following connection string:
The known issues in this CTP release of Compact 4.0 are listed below:
1. Compact 4.0 CTP1 needs Visual C++ 2008 Runtime Libraries (x86, IA64 and x64), Service Pack 1: The native DLLs of SQL Server Compact 4.0 CTP1 need the Microsoft Visual C++ 2008 Runtime Libraries (x86, IA64 and x64), Service Pack 1. Installing the SQL Server Compact 4.0 CTP1 using the Windows Installer (.exe) file, also installs the Visual C++ 2008 Runtime Libraries SP1. If SQL Server Compact 4.0 CTP1 is deployed privately in the application’s folder the following have to be present on the machine for SQL Server Compact to function properly:
a. Installing the .NET Framework 3.5 SP1 also installs the Visual C++ 2008 Runtime Libraries SP1.
b. Visual C++ 2008 Runtime Libraries SP1 can be downloaded and installed from the location given below: http://go.microsoft.com/fwlink/?LinkId=194827
Note that installing .NET Framework 2.0 or 3.0 or 4 does not install the Visual C++ 2008 Runtime Libraries SP1.
2. Uninstallation and repair of Compact 4.0 CTP1 from the command line does not work: Uninstallation of Compact runtime using command line options does not work in this CTP release. For uninstallation of SQL Server Compact runtime go to Add/remove programs (Start->Run->appwiz.cpl), and right click on the entry ‘Microsoft SQL Server Compact 4.0 ENU CTP1’ or ‘Microsoft SQL Server Compact 4.0 x64 ENU CTP1’ , and select uninstall from the menu.
3. Compact 4.0 development support in Visual Studio 2010: An update to the Visual Studio 2010 is being worked upon that will add the support for Compact 4.0. The update will also provide for the development support for Compact 4.0 in the Visual Studio 2010 Web Developer Express.
4. Code first programming model support for Compact 4.0 in ADO.NET Entity Framework: The Microsoft ADO.NET Entity Framework Feature Community Technology Preview 3 is an early preview of the code-first programming model for the ADO.NET Entity Framework 4.0. The code-first feature in the ADO.NET Entity Framework CTP3 release does not work properly with Compact 4.0. Future releases of code-first programming model for ADO.NET Entity Framework will provide support for code-first programming model for Compact 4.0.
Program Manager – SQL Server Compact