Accessing the Enterprise with Ease
August 6, 2002
Summary: Larry Roof expands on his Northwind Mobile application from last month's column, and through some simple code modifications creates an enterprise edition that leverages a SQL Server 2000 database. (14 printed pages)
Here's a fact you can use to amaze your friends and impress people at your next tech mixer. Larry Roof, the mobile man component of the Two for the Road team, that frustrated surf bum and part-time tech freak, has had the same telephone number his whole life. That's right, the same number from when I was a wee hacker, through high school, college, and the remainder of my pseudo-adult life. I had that same number all the way up until two weeks ago. That's when I moved. Now, here is the real annoying part. No matter how much reasoning, threatening, begging, and pleading I did with the phone company, they would not comply with my simple wish to rearrange a few area codes and numbers so that I could keep my birth number. This is just another in a long line of examples of modern technology running rough shod over history and tradition.
So now I'm walking around looking like a bad stand-in for Memento, with my new phone number tattooed on my forearm. But it goes beyond that. I knew everyone where I lived. I don't know anyone here. I've had to resort to taking Polaroids of everyone I meet. I write little notes on the bottom of each photo like "John the Postman. Don't chase him away anymore." or "This is the neighbor's dog Skippy. Stop taking him to the pound."
So why did I move? The answer is simply, the view from my new office.
The only downside of the new place is that the waves are not big enough to ride. I tried to convince the neighbors to get out in the water and splash vigorously about, but at best they were going through the motions. Then by luck I stumbled across an article on Carveboards, a cross-trainer for surfing. I have to tell you that this is simply the coolest ride available for frustrated, land-locked surfers like me. You can check them out at http://www.carveboard.com/.
Well enough about the new place, let's get back to exploring the functionality found in Smart Device Extensions (SDE) for Visual Studio® .NET.
Accessing Enterprise Data with SDE
In my last article I introduced you to the Northwind Mobile application, which demonstrated creating, loading, and accessing a device-based SQL Server™ CE database with SDE. In this article, I take that same Northwind Mobile application and through some simple code modifications, create an enterprise edition that leverages a SQL Server 2000 database.
Before I get started, let me take a moment to discuss the development environment I am using. My "enterprise" consists of a notebook along with an iPaq Pocket PC 2002. SQL Server 2000 is running on the notebook. The two computers are connected through Ethernet, which is implemented on the iPaq using a CompactFlash (CF) card jacket and a rugged CF Ethernet card from Socket (http://www.socketcom.com/).
Tip If you have any hope at maintaining your sanity while developing with SDE, get Ethernet for your Pocket PC. Your development process will be vastly improved, your family will like you better, you will drink a lot less, and the whole world will seem nicer.
Having SQL Server on my notebook enables me to tweak configurations and securities as required to get various data access functionality to work. You know, just the type of things that freaks out system administrators.
The configuration I use is a suitable development substitute for a wireless environment. It saves the cost of access points and PCMCIA cards and allows direct access to enterprise data.
Overview of Northwind Mobile Enterprise Edition
Like last month's version, Northwind Mobile Enterprise Edition is a simple application. Its sole purpose is to display orders that are stored in an enterprise-based SQL Server database. It allows you to pick from a list of customers. As you select a customer, a list of that customer's orders is loaded. By selecting an order you can review its details. An example of the Northwind Mobile application in use is shown in Figure 1.
Figure 1. The Northwind Mobile Enterprise Edition application
At first glance, this new version doesn't look any different than last month's version. Figure 2 demonstrates the "prowess" of the enterprise version, which offers all of the customers, not just the lame two the regular version provides.
Figure 2. The complete list of customers
Selecting a customer causes a list of that customer's orders to be loaded. Selecting an order will result in the order details being displayed.
Figure 3. All of the orders for the selected customer
Walking Through Northwind Mobile Enterprise Edition
In this section, I will walk you through the Northwind Mobile Enterprise Edition application.
The Northwind Mobile Interface
The interface to Northwind Mobile Enterprise Edition is unchanged from the version presented in my last article. It is comprised of seven controls: two ComboBoxes, two Labels, two TextBoxes, and a ListView. Figure 4 shows the interface within the SDE Forms Designer.
Figure 4. The Northwind Mobile interface design
The Northwind Mobile Enterprise Edition Database
The Northwind Mobile Enterprise Edition application makes use of the version of Northwind delivered with SQL Server 2000. There are no modifications or additions necessary to prepare that database for use.
To access a SQL Server database from an SDE application, you need to add a reference to the System.Data.SqlClient component to your project. To do this:
- In the Visual Studio .NET IDE open the Project menu.
- From the Project menu select Add Reference… The Add Reference dialog will be displayed.
- From the Add Reference dialog select the System.Data.SqlClient component as shown in Figure 5.
- Click the Select button. The System.Data.SqlClient component will be added to the Selected Component box.
- Click the OK button.
Figure 5. The Add Reference dialog highlighting the System.Data.SqlClient component
To verify that the System.Data.SqlClient component has been added to your project, expand the References section within Solution Explorer as shown in Figure 6. You should see the new component.
Figure 6. The Solution Explorer with the additional reference
Next, we'll look at the code behind the Northwind Mobile Enterprise Edition application.
Starting the Application
The load event procedure for Northwind Mobile performs two functions:
- Formats the ListView control.
- Loads the list of customers.
Private Sub frmMain_Load(ByVal sender As System.Object, ByVal e As _ System.EventArgs) Handles MyBase.Load ' Format the ListView control. FormatListView() ' Load the Customer combo box. LoadCustomers() End Sub
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
Note This routine is identical to the one used in last month's application. I've included it again here so that you don't need to flip back and forth between the two articles.
Loading a List of Customers
In the last article, I showed you how to access a SQL Server CE database that was resident on a device. One of the cool new features of SDE and the .NET Compact Framework is the data options it provided. The LoadCustomers routine gives us our first look at retrieving data from a SQL Server database directly, much as you would from a desktop PC sitting on a network.
Private Sub LoadCustomers() Dim cmd As System.Data.SqlClient.SqlCommand Dim dtr As System.Data.SqlClient.SqlDataReader
I start by opening the database. Here the SQL Server provider is specified along with the database to access.
Note I'm referencing the IP address of my server/notebook. You will need to modify this address to point to your server.
' Open the database. cn = New System.Data.SqlClient.SqlConnection("user _ id=sa;password=;database=NorthWind;server=192.168.1.100") cn.Open()
Next, a simple SQL SELECT statement is used to retrieve the list of customers from the Northwind database into a DataReader object.
' Retrieve a list of the customers. cmd = New System.Data.SqlClient.SqlCommand("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 the DataReader object 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.
Note I want to point out a small but important point. In the last article, I closed both the DataReader and Connection objects at the completion of the LoadCustomers routine . For performance reasons, I have altered that approach in this application. Rather than close and reopen that connection with each database operation, I open the connection one time, leave it open, and then close it before the application exits. This dramatically improves subsequent queries to the enterprise database.
Before using this approach, you need to consider the impact open connections will have on your network and database server.
' Clean-up. dtr.Close() ' Select the first customer. cmbCustomers.SelectedIndex = 0 End Sub
Loading a List of Orders
Selecting a customer results 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.
Private Sub LoadOrders() Dim cmd As System.Data.SqlClient.SqlCommand Dim dtr As System.Data.SqlClient.SqlDataReader Dim SQL As String ' 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 & "')"
Note The data retrieval can be made quickly because the connection is already open.
cmd = New System.Data.SqlClient.SqlCommand(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() End Sub
Loading Order Details
When an individual order is selected, the details for that order are first retrieved from the SQL Server 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, and the other to bring back the individual items that comprise the selected order.
Private Sub LoadOrderDetails() Dim cmd As System.Data.SqlClient.SqlCommand Dim Discount As Single Dim dtr As System.Data.SqlClient.SqlDataReader Dim ItemTotal As Single Dim LVItem As ListViewItem Dim OrderTotal As Single Dim Quantity As Int16 Dim SQL As String Dim UnitPrice As Single ' Retrieve the order summary information. SQL = "SELECT OrderDate, ShippedDate FROM Orders WHERE " SQL = SQL & "OrderID = " & cmbOrders.Text cmd = New System.Data.SqlClient.SqlCommand(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 [Order Details].UnitPrice, [Order Details].Quantity, " SQL = SQL & "[Order Details].Discount, Products.ProductName FROM [Order Details] " SQL = SQL & "INNER JOIN Products ON " SQL = SQL & "[Order Details].ProductID = Products.ProductID WHERE " SQL = SQL & "[Order Details].OrderID = " & cmbOrders.Text cmd = New System.Data.SqlClient.SqlCommand(SQL, cn) dtr = cmd.ExecuteReader() ' 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 ' 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() End Sub
Closing the Connection
As I mentioned earlier in this article, the connection object is left open while the application is running. The connection to the Northwind database is closed when the application exits. This is handled in the Closing event of the Form object.
Private Sub frmMain_Closing(ByVal sender As Object, ByVal e As _ System.ComponentModel.CancelEventArgs) Handles MyBase.Closing cn.Close() End Sub
Summary of Northwind Mobile Enterprise Edition
My point with this application was to show you how easy it is to access enterprise data from an application written with SDE. The .NET Compact Framework SQL Server client allows you to quickly construct wireless solutions that leverage the power of enterprise data.
In addition, if you take the time to work through both this and my last article, you will see the similarities between working with a local SQL Server CE database and a remote SQL Server database. The minimal differences are comprised of the appropriate data reference, connection settings, and slightly altered SQL statements.
Even though this is my second article on accessing data with SDE, I have barely scratched the surface of the functionality offered by SDE. In future articles, I will delve into how to update databases, working with XML, and replicating and synchronizing databases.
SDE Tip: Customizing Your Program's Icon
If you are an eMbedded Visual Basic® veteran, you are used to dealing with not being able to alter the icon associated with your applications. This shortcoming is corrected with SDE. To add a custom icon to your application, perform the following:
- In the Solution Explorer window, right-click your project. Select Properties from the pop-up window. The Properties dialog will be displayed as shown in Figure 7.
- From that dialog, select Build under Common Properties. The build properties will be displayed.
- Click the ellipse button to open a dialog from which you can select the icon to associate with your application. Select an icon from the open file dialog.
Figure 7. Working with the Properties dialog
- Click the OK button to close the property dialog.
- You should see the addition of your selected icon in the Solution Explorer window as shown in Figure 8.
Figure 8. Project with the additional icon
At this point, if you were to do nothing else your application would display the icon you selected. As currently configured though, that icon would not be part of your executable. It would instead be copied down to the target device as a separate file. Typically, this is not the desired result. There is one additional property to configure to make the icon part of your application.
- Select the icon file in the Solution Explorer window.
- In the Properties window, configure the Build Action property to Embedded Resource. This will cause the icon to be included within the executable.
Figure 9. Configuring the build action for the icon
- Finally, Figure 10 shows the new icon as it appears to the user.
Figure 10. How the icon appears to the user
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 Carveboard in the car and I'm heading out to Boise, Idaho where there is a legendary 12-mile hill. Next time I'll delve into some additional data functionality that is provided through SDE and the .NET Compact Framework. 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.