Home » A Lap Around SQL Server 2005 Compact Edition

A Lap Around SQL Server 2005 Compact Edition

n April 6 of this year, Microsoft announced that SQL Server Mobile Edition (SQL Server Mobile) would be replaced later this year with Microsoft SQL Server 2005 Everywhere Edition (SQL Server Everywhere). Later, in early November, Microsoft announced that it would rename the product again to SQL Server Compact Edition (SQLce). As of this writing, SQL Server Everywhere (the download page has not been updated with the new name) has been released as a CTP on Microsoft’s Web site.

In the past, SQL Server Mobile focused strictly on mobile devices, but with the release of SQL Server Compact Edition, Microsoft has begun targeting a broader developer audience?anyone looking for a small footprint desktop database engine that provides robust SQL Server functionality.

Note: Don’t confuse the name change to SQL Server Compact Edition (SQLce) with the CE platform. Instead, think of it as a compact version of SQL Server.

In its original incarnation, SQL Server Mobile provided a set of tools intended to connect remote databases with centralized SQL Servers?regardless of whether the remote application connected to SQL Server indirectly using IIS or directly via ActiveSync synchronization. SQLce extends the platform’s reach by providing SQL Server functionality to desktop and embedded applications. While SQL Server Compact still supports all the original SQL Server Mobile functionality, this article concentrates on the product’s new desktop application focus.

The Specifics
On the desktop, SQLce targets applications that require robust SQL Server functionality bundled in a small package. Microsoft supports SQLce on all versions of Windows XP, Windows Server 2003, and Windows Vista, and intends to add support for Windows 2000 in an upcoming release. You can access SQLce from all .NET languages and from older languages such as VB6 that use OLEDB. You do not need to have administrative rights to install SQLce, and the installation supports ClickOnce deployment (so you can deploy it with your applications).

SQLce does not run as a service; instead, it includes a small process that runs only when applications access its data files. The process launches very quickly and requires only about 5 MB of memory (at its base level) and less than 1.4 MB on disk. Microsoft claims that it supports databases up to 4GB, although I would urge you to think twice before considering using SQL Server Compact for databases that large.

Each SQLce database deploys as a single .sdf file. You can either ship existing database files with an application, or create new .sdf files using the SQL Server Compact engine. When connecting to the file, the SQLce framework creates the small application that provides multi-user access to the database file.

The biggest downside is that SQLce does not support stored procedures in the upcoming release; however, stored procedure support has been planned for a future release of the product. Currently, SQLce supports multiple connections, transactions, referential integrity constraints, and many other basic SQL Server functions. Developers access SQL Server Compact data through a standard ADO.NET data provider located in the System.Data.SqlServerCe namespace.

The documentation and naming conventions (not surprisingly, given the multiple name changes) could use some work. Except for the product literature published by Microsoft, the SQLce software components all refer to the product as SQL Server 2005 Mobile Edition, or SqlServerCe. It’s clear that the product’s heritage is the mobile or CE platform, but I would have expected that?as a newly-branded product?the software components would have been re-branded as well. I hope that this will be remedied in the final release.

For more specifics on the SQL Server Compact (aka SQL Server Everywhere) database, consult the SQL Server 2005 Everywhere Edition Datasheet.

Author’s Note: Again, I’m using the old terminology for many of the links in this article because at publication time Microsoft hadn’t yet updated the documentation with the new name. Depending on when you access these links, the names may be updated.

Getting Started
To get started using SQLce, first download the installer from Microsoft’s SQL Server Everywhere web site. The web page displays some high-level information about the new version of SQL Server, including a comparison between SQL Server 2005 Express Edition and SQL Server 2005 Everywhere Edition. After completing the installation, you can create your first SQL Server Compact database file.

There are two ways to create these files. First, you can use the Server Explorer panel from within Visual Studio. Second, you can use the object explorer in the Microsoft SQL Server Management Studio. I will walk you through both methods briefly before jumping into the sample code.

Connecting with Server Explorer
Open Visual Studio and open the Server Explorer pane. Click the “Connect to Database” button as shown in Figure 1.

	?
Figure 1. Connecting Via Server Explorer: Click the “Connect to Database” button in Visual Studio’s Server Explorer.

In the “Choose Data Source” window that appears, select “Microsoft SQL Server Mobile Edition” from the list of data. In the “Add Connection” window that appears you are given two options:

Create a new data file,
Browse for an existing database file.

In this case, you’re starting from scratch, so click the “Create…” button to create a new database file (see Figure 2).

You can browse for a location for the database and give it a name of your choosing. Note that you have the option to encrypt the data by checking the Encrypt checkbox. Finally, you must supply a single password used to secure the contents of the database. All applications that access the database will use the same password?SQL Server Compact does not support the creation of users within the database. When you’ve supplied all the information, click OK to create the database file.

When you’ve completed the database creation process, you’ll be returned to the “Add Connection” window, with the database filename and password fields now populated (see Figure 3)

	?
Figure 2. Database Creation Dialog: In this window, choose a location, filename and password for the new database file.

	?
Figure 3. Adding a Connection: After creating a database, the “Add Connection” window now shows the location of the database file and the password you configured for the new database.

If you choose to select an existing database file instead, you’ll have to fill in the Password field manually. Click the “Test Connection” button to verify the location of the database and the password. In this case, because the information was populated as a result of creating a new database, the test should be successful.

	?
Figure 4. The Advanced Properties Window: This window shows the connection string you should use when writing code to connect to the database. Copy and paste this connection string into your project.

You may have noticed the “Advanced…” button in the “Add Connection” dialog, which is useful because it provides the connection string you will use later when writing code to connect to the database. Figure 4 shows the “Advanced Properties” window with the connection string selected.

Finally, click the “OK” button to open the database in Visual Studio’s Server Explorer. Figure 6 shows the Server Explorer with the newly created DevXSample database open. Using the Server Explorer, you can create and manage tables, edit data within the tables, etc. Because the tools available for a SQL Server Compact database act so similar to the other standard SQL Server tools for managing tables and data, I won’t belabor that topic.

Remember, there are two ways to connect to a SQL Server Compact database, using Visual Studio Server Explorer, as just discussed, and using SQL Server 2005’s Management Studio. Note that the Management studio is not shipped with SQL Server Compact.

Connect Using SQL Server Management Studio
The other way to connect to SQLce databases is through SQL Server 2005’s SQL Server Management Studio. Open SQL Server Management Studio and select “SQL Server Mobile…” from the Connect menu, as shown in Figure 5.

	?
Figure 5. Connecting with SQL Server Management Studio: Select “SQL Server Mobile…” from the Connect menu within Microsoft SQL Server Management Studio.

	?
Figure 6. Connected Database: The figure shows the Management Studio connected to the DevXSample database.

This example browses for the new database file created in the previous section using the Visual Studio Server Explorer. Select “Browse for More…” from the “Database File” dropdown. Find the database file you want to open, and enter the password. When you click “Connect” SQL Server Management Studio will open the file for modification, as shown in Figure 6.

Building the Sample Application
Download the sample code for this article to follow along with the process of writing code to access SQL Server Compact as described in the rest of this article.

Author’s Note: This sample application uses a database file named DevXSample.sdf located in the c:SQLServerServerEverywhere directory. For demonstration purposes, the path and filename are hard-coded in the c_strDatabasePath constant. If you created a different database in a different path, you’ll need to adjust the name and path accordingly.

Writing the Code
In any project that uses SQL Server Compact, you must set a reference to the assembly containing its ADO.NET data provider implementation. For this application, add a reference in your project to the System.Data.SqlServerCe assembly. In VB.NET, include an Imports statement (uses in C#) to include the System.Data.SqlServerCe namespace in your source.

For demonstration purposes, this application has a set of buttons (see Figure 7) that perform various database activities such as creating a database, creating, initializing, and dropping a table, and retrieving data.

	?
Figure 7. The Sample Application: The sample application demonstrates how to use several useful functions of SQL Server Compact.

First, create the sample database file on disk by clicking the “Create Database” button. The code for the button Click event creates an instance of the SqlCeEngine class, passing a connection string to the class constructor, and then calls its CreateDatabase method. The connection string contains the database location, filename, and password to use when creating the new database file.

   objEngine = New SqlCeEngine(c_strConnectionString)   objEngine.CreateDatabase()

After creating the database, click the “Create Table” button to create the Products table. The application creates the table using an ADO.NET Command object (implemented by the SqlServerCe data provider) to execute standard SQL. The code below demonstrates the process of creating the table.

   strSQL = "CREATE TABLE Products "   strSQL += "  ("   strSQL += "     prID int NOT NULL, "   strSQL += "     prName nvarchar(100) NOT NULL, "   strSQL += "     prDescription nvarchar(1000) NOT NULL, "   strSQL += "     prBasePrice money NOT NULL"   strSQL += "  )"      objConn = New SqlCeConnection(c_strConnectionString)   objConn.Open()      objComm = New SqlCeCommand(strSQL, objConn)   objComm.ExecuteNonQuery()

To insert the sample records into the Products table, click the “Initialize Table” button. This button calls the code shown in Listing 1. It uses a series of standard ADO.NET Command objects to execute SQL insert statements. Initializing the Products table inserts five product records into the table.

‘Insert the demo products into the Products table

   Private Sub btnInitializeTables_Click( _      ByVal sender As System.Object, _      ByVal e As System.EventArgs) _      Handles btnInitializeTables.Click         Dim objConn As SqlCeConnection      Dim objComm As SqlCeCommand      Dim strSQL As String         objConn = New SqlCeConnection(c_strConnectionString)      objConn.Open()         '---- first delete any existing products      strSQL = "DELETE FROM Products"      objComm = New SqlCeCommand(strSQL, objConn)      objComm.ExecuteNonQuery()         '---- insert a row into the Products table      strSQL = "INSERT INTO Products "      strSQL += "(prID, prName, prDescription, prBasePrice) "      strSQL += "VALUES "      strSQL += "(1, 'Shoes', 'Leather Shoes', 10.00)"      objComm = New SqlCeCommand(strSQL, objConn)      objComm.ExecuteNonQuery()         ' insert more rows here      ...      ' clean up      objConn.Close()         MessageBox.Show( _          "The Products table was initialized successfully.")      End Sub

To verify the correct insertion of the product records, click the “Refresh” button below the data grid (see Figure 7). The button calls a method named PopulateGrid, which reads the data from the Products table into a dataset, and then binds the dataset to the data grid on the form.

   'Populate the datagrid   Private Sub PopulateGrid()      Dim objAdapter As SqlCeDataAdapter      Dim objDataset As DataSet      Dim strSQL As String         strSQL = "SELECT prID as [ID], prName AS [Name], _         prDescription as [Description], "      strSQL += "prBasePrice as [Base Price] FROM Products ORDER BY prName"         objAdapter = New SqlCeDataAdapter(strSQL, c_strConnectionString)         objDataset = New DataSet()      objAdapter.Fill(objDataset, "Products")         dgvProducts.DataSource = objDataset.Tables("Products")   End Sub

It’s important to reiterate that all the code in this article is standard ADO.NET with no special considerations given for SQL Server Compact. However, in practice, if you make frequent use of stored procedures, you are no doubt accustomed to the technique of repeatedly executing a command and simply changing the values of the parameters associated with the command. Since stored procedures are not supported, you will need to develop an alternate method of repeatedly inserting different string values into a SQL statement.

The sample application includes other functions to manipulate the data, such as changing all the product prices by a given percentage and counting the rows in the Products table. These are purposefully simplistic to demonstrate common ways that developers typically access data and modify it in a real-world application. Because they are similar to code listings already described, I won’t discuss them individually. You can view and test them by downloading the sample project.

As you begin using SQL Server Compact, you fill find that writing software to use it is remarkably similar to using the larger server versions of SQL Server. This is due in large part to the ADO.NET data provider framework, which supports similar functions against a wide range of data providers, making the task of writing software to access these data providers nearly identical across all SQL Server platforms.

You will also notice some differences. The data types available in SQL Server Compact are a subset of what is available in richer versions of SQL Server. Further, because the current version does not support stored procedures, you will find you’ll need to break up and simplify complex SQL processes typically implemented in stored procedures. However, the SQL Server Compact platform was built for the express purpose of bringing your data everywhere you go?not for implementing enterprise applications on mobile phones.

For more information about SQL Server Compact and to keep up-to-date with updates to the product, visit the following sites.

SQL Server 2005 Everywhere Edition Home Page??This page provides links to download the SQL Server Everywhere installer, datasheet, and books online. It is also provides a brief comparison between SQL Server Everywhere and SQL Server Express.
SQL Server Everywhere Blog?This page provides lots of details about the product, including good tips for using the product in the wild.
Steve Lasker’s Web Log for SQL Server Everywhere?This page provides a good general discussion of the SQL Server Compact platform.

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.