In-Depth

Data, Data, Everywhere Data!

• By Dan Fergus
• 08/01/2007

Who among us has ever written an application that didn't consume or save data? If we had a show of hands, I doubt the number would be very high. Except for some UI libraries I wrote about 18 years ago, I can't recall a single application that didn't require me to read data from some source or write data back to some storage system. Agreed, it wasn't always a true relational database like SQL Server, but it was data storage nonetheless.

Device applications -- whether kiosk-based or mobile handheld devices -- are typically used as data-collection devices. A handheld device might scan products on a store shelf, and a kiosk could collect data about the interests of users responding to a survey -- or even provide information about the area around the kiosk, from a database of information.

To write a useful device application, you'll need to understand how to collect and store data and then how to move it to another location -- a server, for example -- where the data can be used and viewed by others. To accomplish this, we'll use SQL Server 2005 Compact Edition.

One of the benefits of using SQL Server Compact Edition is that we can use the same application code in a device application or desktop application. The resulting database file can also be migrated from a mobile application scenario to a desktop scenario that may include a server or multiple users.

Earlier versions of mobile SQL database limited the database to a single connection for use only on a device. Theses limitations have since been eliminated; the database can start on a mobile device and used to collect data, and then the entire database file can be moved to the desktop for analysis and sharing on other systems.

Another problem with some older versions of SQL mobile databases was that there was no easy way to create and edit the physical database file. You either had to do it via code or by using the query analyzer on the device. Neither of those was a very good option. SQL Server 2005 Compact Edition databases can now be created and managed using the SQL Server Management Studio.

But enough background. Open Visual Studio and create a new Windows Mobile 6 Professional application. Once the solution has been built, open SQL Server Management Studio and create a new SQL Server 2005 Compact Edition database.

Notice in Figure 1, below, that you have a choice to encrypt the database or to password-protect the data file. If you protect your database with a password, your connection string must include the password in order for a connection to be made. However, data inside the file itself could still be viewed if the file was opened in a text editor. The encrypt option applies 128-bit encryption to the contents of the file, preventing anyone who may get a copy of the file from viewing any of the data. We won't cover how to create the individual field of the database in this article, but a completed database is in the sample code that accompanies this one.

[Click on image for larger view.]

The first thing you need to do when using SQL Compact Edition is to build the connection string and then open the connection. The connection string is very simple for an unprotected database; it's no more than the path to the database file, as shown in Listing 1.

It might be tempting to hardcode the connection string to a particular location, but that's just looking for trouble. In this code, we get the location of the executing application and append the database name.

Now, here's a common question: Do we leave the connection open or do we close it after each call to the database? Previously, due to the single connection limit, it was always best to open a connection, use it and immediately close it so that any other thread could then access the file. With the new multiple connections allowed in Compact Edition, it's possible to leave the connection open for the duration of an application, or at least for more than a single-execute operation. If you wanted to use the connection and immediately close it, the code would look as shown in Listing 2.

This is one of those "personal choices" that each developer or company must make. You might even choose to do it differently from application to application. Once the connection has been made and opened, data is retrieved from the database as shown in Listing 3.

The SqlServerCe namespace provides a SqlCeResultSet which is the best of both the DataSet and the DataReader. It has the speed of a DataReader, as well as the update and scroll capabilities of the DataSet.

Now that you see how easy it is to retrieve data from the database, let's see about putting data back into the database. The first technique uses the SqlCeResultSet class. The class has a CreateRecord method that creates a new, blank record in the recordset which is filled in using a series of Set methods, as shown in Listing 4.

If you prefer the more tried-and-true method of T-SQL code, you also have that option, as shown in Listing 5.

Those are the basics of using SQL Server 2005 Compact Edition. Of course, there's much more to learn; this article covers only a very small portion of data stored on a device. There are still more details regarding the use of SQL CE on the device, as well as how to implement the Client Factory Data namespace (Patterns and Practices). And once we get data onto the device, how do we, other than copying the database file directly, get the data back to a full SQL Server -- or anywhere else, for that matter? We'll answer these questions and more in the coming weeks.