Smart Device Extensions, SQL Server CE, and Me

  • Article

Larry Roof
LarryRoof.com

May 17, 2002

Download Northwind_mobile.exe.

Is it just me or are television shows getting shorter and shorter. Before you dump a ton of that "What the heck is wrong with you?" e-mail on me, let me explain my point.

Physically, I know that television shows are the same length. In fact, I count on them being the same length. TV allowed me to get rid of all of the clocks in my house. I use TV shows as a kind of modern day replacement for a sundial. Gunsmoke is on TVLand; it's time for lunch. Trading Spaces is on TLC; it's time to go for a run. Baseball Tonight is on ESPN; turn off the Pocket PCs and call it a day.

What I'm upset about is that the producers of TV shows continue to whittle away the amount of content that a particular show contains. How are they doing it? Through the use of the fiendish trickery of showing us highlights from previous weeks. As best as I can figure out, some up-and-coming producer had a lightning bolt of an idea that showing rerun material was way easier than producing new content. From there it was only a mental baby step to the infamous "seen previously on" that is now found at the start of many TV programs.

Now don't get me wrong. There are good, practical applications of this technique in your job. Follow me for a moment. Your boss asks you what you did this week, and you start by giving a summary of what you did in previous weeks. That keeps them guessing. If they press you on how much work you got done on a critical project, simply overload them with details on stuff you did a couple of months ago. Use a lot of quick cutaways (I did this, I did that) and speak in that deep movie trailer voice. Heck, chances are you'll hardly have to do any new work at all. You can just keep taking credit for work you have already done.

But enough about attempting to topple the foundation of corporate America, let's continue our exploration of Smart Device Extensions (SDE) and take a first look at using SDE with SQL Server CE (SQLCE).

SDE + SQLCE = Mobile Data

In my last column, I walked you through building a simple Hello World application with SDE. Now, I know the market for such an application is pretty limited, and you would be hard pressed to sell 10,000 copies at a $9.95 price tag. So this month I'll focus on something a bit more practical—leveraging SQLCE from SDE.

Before I get started, let me say that working with data using SDE is significantly different than how you would do it with eMbedded Visual Basic®. With eMbedded Visual Basic you could work with two types of databases, Pocket Access and SQLCE. Many developers chose to use Pocket Access because of the ease in which it could be implemented and its integration with Microsoft Access. SDE supports only SQLCE. With eMbedded Visual Basic you had ADOCE. SDE offers ADO.NET.

Now, I'm not going to delve into one of those theoretical diatribes on ADO.NET and the .NET Compact Framework. To be honest with you, those types of articles just give me headaches. Instead, through this and several follow-up articles, I will lead you through a set of examples that will demonstrate the functions and features that you would most commonly need to use in the construction of mobile applications.

Northwind Mobile SDE-Style

I will introduce you to SDE and SQLCE with a simple application called Northwind Mobile. This application takes its name from the database on which it is built—the legendary Northwind database. Now I don't know how you feel about it, but Northwind is my favorite database. In fact, back at the height of the dotcom bubble, I launched an eCompany that offered a set of services dealing exclusively with the Northwind database. The guiding principle behind this startup was to find companies that needed the Northwind database just as it was delivered from Microsoft. As luck would have it, the company went belly-up for two glaringly obvious reasons:

  1. Everyone already had a copy of Northwind.
  2. No one needed it in the first place.

Overview of Northwind Mobile

Northwind Mobile is a simple application. Its sole function is to display orders that are stored in a local SQL Server CE database. It has a list of customers from which you can pick. As you select a customer, a list of their orders is built. Selecting an order from that list will result in the order details being displayed. An example of the Northwind Mobile application in use is shown in Figure 1.

Figure 1. The Northwind Mobile application

The Northwind Mobile Interface

The interface to Northwind Mobile is comprised of seven controls: two ComboBoxes, two Labels, two TextBoxes, and a ListView. Figure 2 shows the interface within the SDE Forms Designer.

Figure 2. The Northwind Mobile interface design

The Northwind Mobile Database

As I mentioned previously, the Northwind Mobile application makes use of a SQL Server CE database. In fact, it creates the database as part of its functionality. Why did I use this approach? Simply because it is the easiest way to get started with SDE and SQLCE. Yes, I know that SQLCE offers a multitude of functionality in the way of retrieving data from a SQL Server database. Still, sometimes you just need a local database and nothing more. The trick is how you create that database. There are two methods:

  • Create the database from code.
  • Create the database with the SQLCE Query tool.

Let's take a look at the code approach first.

Building a database with code

Before you can work with a SQLCE database from an SDE application, you need to add a reference to the System.Data.SqlServerCe component to your project. This is accomplished by selecting Add Reference… under the Project menu in the development environment. Then, select the System.Data.SqlServerCe component from the Add Reference dialog as shown in Figure 3.

Figure 3. Selecting the System.Data.SqlServerCe component from the Add Reference dialog

The CreateDatabase routine in the Northwind Mobile application demonstrates creating a database from code. This routine is called when the application is launched.

At the top of CreateDatabase, you will see a small block of commented code. I added it for debugging purposes and decided to leave it for your benefit. It demonstrates how to check if a file exists and how to whack a file.

  Private Sub CreateDatabase()
    Dim cn As System.Data.SqlServerCe.SqlCeConnection
    Dim SQL As String
    Dim SQLEngine As System.Data.SqlServerCe.Engine

' If the database exists, blow it away.
'    If System.IO.File.Exists("\My Documents\Northwind.sdf") Then
'      System.IO.File.Delete("\My Documents\Northwind.sdf")
'    End If

A check is made to determine if the database exists. If not, it is created. At this point the database is empty, so there is no structure or content.

' If the database does not already exist, then create it.
    If Not System.IO.File.Exists("\My Documents\Northwind.sdf") Then
      SQLEngine = New System.Data.SqlServerCe.Engine _
       ("data source=\My Documents\Northwind.sdf")
      SQLEngine.CreateDatabase()

Now that the database exists, it can be opened.

' Next, open the database.
      cn = New _
        System.Data.SqlServerCe.SqlCeConnection("Provider=Microsoft.SQLServer.O _
        LEDB.CE.1.0;Data Source=\My Documents\Northwind.sdf")
      cn.Open()

A set of SQL CREATE TABLE statements is used to define the tables and their associated fields. Only four of the original Northwind tables are included in this sample: Customers, Orders, OrderDetails, and Products.

' Now, through a series of SQL statements create the structure of the database.
' Create the Customers table.
      SQL = "CREATE TABLE Customers (CustomerID nchar(5) Primary Key _
        NOT NULL,CompanyName nvarchar(40) NOT NULL)"
      Dim cmd As New System.Data.SqlServerCe.SqlCeCommand(SQL, cn)
      cmd.CommandType = CommandType.Text
      cmd.ExecuteNonQuery()

' Create the Orders table.
      SQL = "CREATE TABLE Orders (OrderID int Primary Key NOT _
        NULL,CustomerID nchar(5) NOT NULL,OrderDate datetime _
        NULL,ShippedDate datetime NULL)"
      cmd.CommandText = SQL
      cmd.ExecuteNonQuery()

' Create the OrderDetails table.
      SQL = "CREATE TABLE OrderDetails (OrderID int NOT NULL,ProductID _
        int NOT NULL,UnitPrice money NOT NULL,Quantity smallint NOT _
        NULL,Discount real NOT NULL)"
      cmd.CommandText = SQL
      cmd.ExecuteNonQuery()

' Create the Products table.
      SQL = "CREATE TABLE Products (ProductID int Primary Key NOT _
        NULL,ProductName nvarchar (40) NOT NULL)"
      cmd.CommandText = SQL
      cmd.ExecuteNonQuery()

At this point the database has its basic structure, but no data. I'm about to correct that using a large number of SQL INSERT statements that are used to load a set of test data into the database. Again, this is just a subset of the data found in the original Northwind database. You can think of this as Northwind Lite.

Note For the purpose of saving space, I have trimmed the SQL INSERT statements down to a single INSERT for each table. For those of you that have an interest and way too much time on your hands, you can see all of the INSERT statements in their lengthy glory by downloading the Northwind Mobile sample project at the top of this article.

' Next, through a far greater set of SQL statements load some content into the database.
' Load the Customers table.
      SQL = "INSERT INTO Customers (CustomerID, CompanyName) VALUES _
        ('ALFKI','Alfreds Futterkiste')"
      cmd.CommandText = SQL
      cmd.ExecuteNonQuery()

' Load the Orders table.
      SQL = "INSERT INTO Orders (OrderID, CustomerID, OrderDate, _
        ShippedDate) VALUES (10643,'ALFKI','1997-08-25 00:00:00', _
        '1997-09-02 00:00:00')"
      cmd.CommandText = SQL
      cmd.ExecuteNonQuery()

' Load the OrderDetails table.
      SQL = "INSERT INTO OrderDetails (OrderID, ProductID, UnitPrice, _
        Quantity, Discount) VALUES (10643,28,45.6,15,0.25)"
      cmd.CommandText = SQL
      cmd.ExecuteNonQuery()

' Load the Products table.
      SQL = "INSERT INTO Products (ProductID, ProductName) VALUES (1,'Chai')"
      cmd.CommandText = SQL
      cmd.ExecuteNonQuery()

' Close the database.
      cn.Close()

    End If

  End Sub

Building a database with SQLCE query

When the beta version of SQL Server CE 2.0 is installed on your device, along with bits and piece of SQLCE itself, you get a tool called SQLCE Query. It is placed under the Start menu for easy access. With this tool, you can create and query SQLCE databases on your device. Now granted, since this tool requires you to use the SIP (Soft Input Panel) it will probably take five to seven years off of your life, but if you have a Targus keyboard or a desktop utility like Pocket Controller (https://www.soti.net/), it works pretty well. This tool is helpful to debug any SQLCE-based application, and in a pinch, to create a database to ship with your application. An example of the SQLCE Query interface is shown in Figure 4.

Figure 4. The SQLCE Query application

Walking Through Northwind Mobile

Now that we have our database, I'll walk you through the routines that comprise the Northwind Mobile application.

Starting the Application

The load event procedure for Northwind Mobile performs three functions:

  1. Creates the database if necessary.
  2. Formats the ListView control.
  3. Loads the list of customers.
  Private Sub frmMain_Load(ByVal sender As System.Object, ByVal e As _
    System.EventArgs) Handles MyBase.Load

' Check to see if the database exists. If it does not exist it will be created.
    CreateDatabase()

' Format the ListView control.
    FormatListView()

' Load the Customer combo box.
    LoadCustomers()

  End Sub

We've already looked at the CreateDatabase routine. A brief overview of FormatListView and LoadCustomers follows.

Formatting the ListView Control

Some simple formatting is applied to the ListView control to give it column headers and to set the widths of the columns. The view to use with our application, the details view, is applied here as well.

  Private Sub FormatListView()
' This routine handles configuring the listview control.
    lvwOrder.View = View.Details

    lvwOrder.Columns.Add("Product", 120, HorizontalAlignment.Left)
    lvwOrder.Columns.Add("Qty", 40, HorizontalAlignment.Right)
    lvwOrder.Columns.Add("Price", 50, HorizontalAlignment.Right)
    lvwOrder.Columns.Add("Disc", 50, HorizontalAlignment.Right)
    lvwOrder.Columns.Add("Total", 60, HorizontalAlignment.Right)

  End Sub

Loading a List of Customers

The LoadCustomers routine gives us our first look at retrieving data from a SQLCE database.

  Private Sub LoadCustomers()
    Dim cn As System.Data.SqlServerCe.SqlCeConnection
    Dim cmd As System.Data.SqlServerCe.SqlCeCommand
    Dim dtr As System.Data.SqlServerCe.SqlCeDataReader

I start by opening the database. Here the SQLCE provider is specified along with the location of our Northwind database.

' Open the database.
    cn = New _
      System.Data.SqlServerCe.SqlCeConnection("Provider=Microsoft.SQLServer. _
      OLEDB.CE.1.0;Data Source=\My Documents\Northwind.sdf")
    cn.Open()

A SQL SELECT statement is used to retrieve all of the customers into a DataReader object.

' Retrieve a list of the customers.
    cmd = New System.Data.SqlServerCe.SqlCeCommand("SELECT * FROM Customers", cn)
    dtr = cmd.ExecuteReader()

A DataReader object functions much like a forward-only, read-only recordset. It allows you to make a single pass through a set of records and is ideally suited for loading ComboBoxes.

' Load the list into the customer combo box.
    cmbCustomers.Items.Clear()
    While dtr.Read()
      cmbCustomers.Items.Add(dtr("CompanyName"))
    End While

The LoadCustomers routine completes by closing our database objects and then selecting the first customer from the ComboBox. Selecting the first customer initiates a cascading set of events that results in the orders for a customer being loaded and an individual order being displayed.

' Clean-up.
    dtr.Close()
    cn.Close()

' Select the first customer.
  cmbCustomers.SelectedIndex = 0

  End Sub

Loading a List of Orders

Selecting a customer will result in the loading of the list of orders for that customer. This is carried out by the LoadOrders routine. Functionally, this routine is much like LoadCustomers, but with a slightly more complex SQL SELECT statement, which demonstrates the SQL Server CE support for the SQL INNER JOIN statement.

  Private Sub LoadOrders()
    Dim cn As System.Data.SqlServerCe.SqlCeConnection
    Dim cmd As System.Data.SqlServerCe.SqlCeCommand
    Dim dtr As System.Data.SqlServerCe.SqlCeDataReader
    Dim SQL As String

' Open the database.
    cn = New System.Data.SqlServerCe.SqlCeConnection("Provider=Microsoft.SQLServer. _
  OLEDB.CE.1.0;Data Source=\My Documents\Northwind.sdf")
    cn.Open()

' Retrieve the orders for the selected customer.
    SQL = "SELECT Orders.OrderID FROM Orders INNER JOIN Customers ON "
    SQL = SQL & "Orders.CustomerID = Customers.CustomerID WHERE "
    SQL = SQL & "(Customers.CompanyName = '" & cmbCustomers.Text
    SQL = SQL & "')"
    cmd = New System.Data.SqlServerCe.SqlCeCommand(SQL, cn)
    dtr = cmd.ExecuteReader()

' Load the list into the order combo box.
    cmbOrders.Items.Clear()
    While dtr.Read()
      cmbOrders.Items.Add(dtr("OrderID"))
    End While

' Clean-up.
    dtr.Close()
    cn.Close()

  End Sub

Loading Order Details

When an individual order is selected, the details for that order are first retrieved from the SQLCE database and then loaded into the ListView control. These functions are performed by the LoadOrderDetails routine. As with the LoadCustomers and LoadOrders routines, the heart of the LoadOrderDetails routine contains a couple of SQL SELECT statements. One is used to grab the order summary information, the other to bring back the individual items that comprise the selected order.

  Private Sub LoadOrderDetails()
    Dim cn As System.Data.SqlServerCe.SqlCeConnection
    Dim cmd As System.Data.SqlServerCe.SqlCeCommand
    Dim Discount As Single
    Dim dtr As System.Data.SqlServerCe.SqlCeDataReader
    Dim ItemTotal As Single
    Dim LVItem As ListViewItem
    Dim OrderTotal As Single
    Dim Quantity As Int16
    Dim SQL As String
    Dim UnitPrice As Single

' Open the database.
    cn = New System.Data.SqlServerCe.SqlCeConnection("Provider=Microsoft.SQLServer._
  OLEDB.CE.1.0;Data Source=\My Documents\Northwind.sdf")
    cn.Open()

' Retrieve the order summary information.
    SQL = "SELECT OrderDate, ShippedDate FROM Orders WHERE "
    SQL = SQL & "OrderID = " & cmbOrders.Text
    cmd = New System.Data.SqlServerCe.SqlCeCommand(SQL, cn)
    dtr = cmd.ExecuteReader()

    dtr.Read()
    txtOrdered.Text = dtr("OrderDate")
    txtShipped.Text = dtr("ShippedDate")
    dtr.Close()

' Retrieve the order details for the selected order.
    SQL = "SELECT OrderDetails.UnitPrice, OrderDetails.Quantity, "
    SQL = SQL & "OrderDetails.Discount, Products.ProductName FROM OrderDetails "
    SQL = SQL & "INNER JOIN Products ON "
    SQL = SQL & "OrderDetails.ProductID = Products.ProductID WHERE "
    SQL = SQL & "OrderDetails.OrderID = " & cmbOrders.Text
    cmd = New System.Data.SqlServerCe.SqlCeCommand(SQL, cn)
    dtr = cmd.ExecuteReader()

As is the case with eMbedded Visual Basic, working with the SDE ListView control is not intuitively obvious. Below you can see the process used to first create a new item to add to the list, then load the individual values into that item, and finally how the item is added into the ListView control.

' Load the order details into the ListView control.
    lvwOrder.Items.Clear()
    While dtr.Read()
      LVItem = New ListViewItem()
      LVItem.Text = dtr("ProductName")
      Quantity = dtr("Quantity")
      LVItem.SubItems.Add(Quantity.ToString)
      UnitPrice = dtr("UnitPrice")
      LVItem.SubItems.Add(UnitPrice.ToString("N2"))
      Discount = dtr("Discount")
      LVItem.SubItems.Add(Discount.ToString("N2"))

      ItemTotal = Quantity * UnitPrice * Discount
      OrderTotal = OrderTotal + ItemTotal
      LVItem.SubItems.Add(ItemTotal.ToString("N2"))

      lvwOrder.Items.Add(LVItem)

    End While

A total line is added to the ListView control to complete the displaying of the order details.

' Add the total for the order.
    LVItem = New ListViewItem()
    LVItem.Text = "TOTAL"
    LVItem.SubItems.Add("")
    LVItem.SubItems.Add("")
    LVItem.SubItems.Add("")
    LVItem.SubItems.Add(OrderTotal.ToString("N2"))
    lvwOrder.Items.Add(LVItem)

' Clean-up.
    dtr.Close()
    cn.Close()

  End Sub

The Unused Code

This is one of those Geraldo Rivera-type of moments. If you happen to be one of those code freaks that would rather browse through the source than read my article, you will come upon a routine in the Northwind Mobile application called ApplicationLocation. Beside the fact that it is fun to say ApplicationLocation, this routine serves absolutely no purpose at all. It is never called or referenced in any way.

Why did I include it? I wanted to show you how to get the equivalent to the eMbedded Visual Basic App.Path statement in SDE. In the Northwind Mobile application, I create the Northwind database in the My Documents folder. In your applications, you might need to create a database or another file in the same folder where your application resides. Hence the need for ApplicationLocation. This is just another example of how doing something in SDE can be a bit more complicated than how you would do it in eMbedded Visual Basic.

  Private Function ApplicationLocation() As String

' Fetch and return the location where the application was launched.
    ApplicationLocation = _
      System.IO.Path.GetDirectoryName(Reflection.Assembly. _
      GetExecutingAssembly().GetName().CodeBase.ToString())

  End Function

Summary of Northwind Mobile

I had two points in building Northwind Mobile. First, I wanted to show you how to create a SQLCE database from code. While this is not always going to be the approach you use, it can come in handy when you are building an application that does not involve a backend SQL Server database. Second, I wanted to show you how easy it is to work with a SQLCE database from SDE. SQLCE is often portrayed as being difficult to work with, in large part because of the complexities surrounding retrieving data from a SQL Server database. As this application demonstrates, that does not need to be the case.

Realize I have only shown you the bare tip of what SDE offers in the way of data access. There is much more to come.

Jump Start Your SDE Development

Want to get up to speed with SDE in a week? My training can get you there. I offer a five-day SDE class that provides you with everything you need to get up and running quickly. I show you how to create robust mobile solutions using SDE, SQL Server CE and XML. A detailed outline is available at www.larryroof.com.

Back on the Road

That's it for this month. I've got my wetsuit and board and I'm heading out. The reports from Huntington Beach are there are non-stop swells. Next time I'll show you some more cool ways to work with SDE and SQLCE. Until then, I'm back on the road.

John Kennedy is a Technical Writer/Programmer in the Visual C++ group by day and leads a secret life as a Pocket PC developer by night.

Larry Roof heads up larryroof.com, a firm that specializes in consulting on mobile projects and training on eMbedded Visual Basic, Smart Device Extensions, and SQL Server CE.