Developer Product Briefs

SQL Server 2005 Mobile Edition Goes Everywhere

A forthcoming Win32 upgrade to SSM joins SSE with SSX and Jet in Microsoft's new triumvirate of freely distributable database engines.

• By Roger Jennings
• 05/01/2006

SQL Server 2005 Mobile Edition Goes Everywhere
A forthcoming Win32 upgrade to SSM joins SSE with SSX and Jet in Microsoft's new triumvirate of freely distributable database engines.
by Roger Jennings

May 1, 2006

Paul Flessner, Microsoft's Senior Vice President of Server Applications, accompanied his April 6, 2006 announcement of SQL Server 2005 Service Pack 1 with news that SQL Server 2005 Mobile Edition (SSM) will morph to a new SQL Server 2005 Everywhere Edition (SSE) by the end of 2006 (see Resources). SSE extends SSM's platform reach to include all Win32 systems, not just Windows XP Tablet PC Edition, by removing current SSM licensing restrictions.

Flessner also announced that the first SSE Community Technical Preview (CTP) is scheduled for summer 2006. Steve Lasker says Microsoft plans to release the CTP at Tech•Ed 2006 (see Resources). The major contributor to the delay in releasing SSE is the need for a special Microsoft Installer configuration to handle more sophisticated setup approaches than simply copying the SSE DLLs to the application folder.

SSE will be a single-user, file-based database engine that's intended for synchronization with SQL Server 2000 SP3a or later and a lightweight local SQL data store that offers high performance, support for transactions, a simple security implementation, and data-file encryption. The SSE DLLs weigh in at only 1.4 MB. SSE has the potential to be a much more efficient and secure data store than XML for persisting DataSets locally. Roger Wolter's "SQL Server Express & Everywhere" blog post provides an overview of the types of smart-client applications for which SSE is appropriate.

Create a Master-Child Form With SSM
SSM lets you generate typed DataSets and bind data-enabled controls to TableAdapters and Binding Sources. Run this quick drill to create a Windows form with the Northwind.sdf sample SSM database file as the data source for master-child DataGridView controls:

1. Open a new Windows Form project, choose Data, Add New Data Source to open the DataSource Configuration Wizard, select the Database icon in the Choose a Data Type dialog, and click Next to open the Choose Your Data Connection dialog.
2. Click New Connection to open the Choose Data Source dialog, select SQL Server Mobile Edition, which selects .NET Framework Data Provider for SQL Server Mobile Edition as the Data Provider (see Figure 1), and click Continue to open the Add Connection dialog.
3. Click Browse to open the Select SQL Server Mobile Edition Database File dialog. If the Northwind.sdf file isn't present, navigate to the \Program Files\Microsoft Visual Studio 8\SmartDevices\SDK\SQL Server\Mobile\v3.0 folder. Select Northwind.sdf and click Open to return to the Add Connection dialog (see Figure 2).
4. Click Test Connection to verify connectivity and click OK to return to the Choose Your Data Connection dialog.
5. Click Next and click Yes to copy Northwind.sdf to your project and fix up the connection, and then open the Save the Connection String to the Application Configuration File dialog. Click Next to open the Choose Your Database Objects dialog.
6. Mark the Tables checkbox to add all tables to the DataSet (see Figure 3). SSM doesn't support Views (other than System Views), Stored Procedures, or Functions. Click Finish to dismiss the Wizard and add Northwind.sdf to Server Explorer's Connections node, which has Tables, System Views, and Replication subnodes.
7. Choose Data, Show Data Sources to open the Data Sources window, and drag the Orders table icon to Form1 to add a bound DataGridView control and Orders Binding Navigator to the form.
8. Expand the Orders table node and drag its Order Details subnode to Form1 (see Figure 4).
9. Press F5 to build and run the project (see Figure 5). Form1 appears almost identical to that which results from applying similar steps to create a master-child form from a SQL Server or Jet database. Only the OrderID values differ from those of the SQL Server 2000 Northwind.mdf sample database.

Code behind the Northwind.xsd DataSet in NorthwindDataSet.Designer.vb (or cs) is almost identical to that for an SQL Server data source; System.Data.SqlServerCe objects substitute for System.Data.SqlClient objects.

Unlike SqlClient's SqlResultset command, which the VS team removed from ADO.NET 2.0, SqlServerCe.SqlCeResultset provides the equivalent of a bidirectional, read-write cursor for SSM databases. You can expect SSE to support all current SSM features, but SSM will remain one of SQL Server's seven editions: Enterprise, Standard, Workgroup, Developer, Express, Mobile, and Everywhere.

Program Within SSE Feature Limitations
The SQL Server 2005 Mobile Edition Books Online Home page on MSDN (see Resources) or SQL Server 2005 Books Online (April 2006) on your test machine with Filtered By set to SQL Server Mobile is the primary source of SSE programming information for its subset of T-SQL. Here's a list of the more important SQL Server 2005 and some SQL Server Express (SSX) programming features that SSM—and thus SSE—do not support:

• Multiple users of a database file on a network share. SSM permits multiple connections, but they must originate from the same machine.
• Use as a data source for Web applications running in the Internet Information Services (IIS) process.
• Views (other than System Views), stored procedures or user-defined functions (as mentioned earlier), or full-text search.
• CLR integration (SQLCLR), native Web services, Service Broker or ServiceBroker-enabled features, such as Event and Query Notifications.
• High-availability or disaster-recovery features, such as database mirroring or log shipping (SSM supports transactions but doesn't generate a transaction log).
• Data types varchar(max), nvarchar(max), varbinary(max), char(n), or varchar(n); nchar(n), nvarchar(n) and varbinary(n) are supported for sizes up to 4,000 characters and ntext can store up to 536,870,911 characters.
• String functions for the ntext data type.
• Case-sensitivity (CS) or accent-insensitivity (AI); CI_AS is the only supported sensitivity option and Latin1_General_CI_AS is the default collation.
• The native XML data type, XQuery or SQLXML; SSM stores XML data in varbinary fields.
• File sizes greater than 4 GB.
• T-SQL aggregate functions other than AVG, COUNT, MAX, MIN, and SUM.
• System functions other than @@IDENTITY, COALESCE, and DATALENGTH.
• Date and time functions other than DATEADD, DATEDIFF, DATENAME, DATEPART, and GETDATE.
• Mathematical functions other than ABS, ACOS, ASIN, ATAN, ATN2, CEILING, COS, COT, DEGREES, EXP, FLOOR, LOG, LOG10, PI, POWER, RADIANS, RAND, ROUND, SIGN, SIN, SQRT, and TAN.
• String functions other than CHARINDEX, LEN, LOWER, LTRIM, NCHAR, PATINDEX, REPLACE, REPLICATE, RTRIM, SPACE, STR, STUFF, SUBSTRING, UNICODE, UPPER.
• Logical operators other than ALL, AND, ANY, BETWEEN, EXISTS, IN, LIKE, NOT, OR, and SOME.

So you'll easily be able to upsize to SQL Server 2005 or SSX a Windows app that uses SSE as a data source. But it's not likely that you can downsize an app that uses a SQL Server 2005 or SSX data source to SSE.

Microsoft's April 19, 2006 SP1 press release states: "SP1 also advances dynamic applications with the new SQL Server Management Studio Express [SSMSE], a simplified graphical management environment for SQL Server Express Edition." You can't use the current SSMSE SP1 tool to open a connection to an SDF file, but Tim Tow, a SQL Server product manager, said, "When SSE is released, customers will be able to use SQL Server Management Studio Express." In the meantime, SQL Server Management Studio (full version) opens and creates SDF files, provides Data Definition Language (DDL) support, and enables ad-hoc T-SQL queries (see Figure 6).

SQL Server 2005 Everywhere won't be useful to all VS developers nor for the majority of today's data-intensive, Win32 client projects. On the other hand, it will provide a high-performance, relational data cache without involving client-side SSX resource consumption, installation and, potentially, maintenance. You'll also be able to move SSE database files between devices and desktop PCs with a simple file transfer. A free, unrestricted SSE is another demonstration of Microsoft's determination to provide developers and DBAs with editions to suit virtually every Windows operating environment and IT budget.

About the Author
Roger Jennings is an independent XML Web service and database developer and author. Roger's latest book is Expert One-on-One Visual Basic 2005 Database Programming (ISBN 0-7645-7678-X) for WROX/Wiley. He's also a Visual Studio Magazine contributing editor and FTPOnline columnist, and writes the OakLeaf Systems Weblog. His Code of Federal Regulations Web services won Microsoft's 2002 .NET Best Horizontal Solution Award. Reach Roger at [email protected].