---

Download

AdventureWorks2008WMSamplesInstaller.msi

Click the link above to download the Windows Installer module to your computer.

---

Introduction

Using the ‘AdventureWorks2008 Sample Database for SQL Anywhere’, and leveraging Microsoft .NET 3.5 technologies, the following samples are intended to demonstrate the key features of SQL Anywhere for Windows Mobile. Specifically, the points of .NET integration and support are highlighted. The samples were all developed in Microsoft’s Visual Studio 2008 SP1, and are freely distributed in their source-code format. The data and information provided are fictitious, but are meant to simulate real-world business solutions.

Overview

Adventure Works Cycling is a medium-size company specializing in the manufacturing and resale of cycling products and parts. Two of their groups, Sales and Production, have identified a need for a mobile computing solution. Sales representatives often find themselves meeting with customers and not having access to their laptops to enter new sales orders, give customers information on their current orders, or inform them of new products. Similarly, engineers often leave their desk to check on individual assembly lines, the current work-load, products that are being produced, and future orders.

To sustain this increased mobility, the IT group has developed solutions for Windows Mobile devices. Leveraging SQL Anywhere’s embeddability and .NET support, all the business functions and needs of both groups can easily be addressed without spending significant resources on development.

SalesMobile (ADO.NET, Mobile Link, UltraLite)

Jillian Carson often meets with clients without having her laptop and needs to be able to enter sales orders offline. This means that she needs specific product and client information from the sales database on her Windows Mobile smartphone. To avoid duplicating effort, the sales group wants orders entered offline to be easily synchronized to headquarters as it would be inefficient to have to re-enter the order when Jillian gets back to the office.

Thanks to Mobile Link synchronization technology, Jillian only needs to keep a subset of the sales database with her: the list of products and some information about her clients. Leveraging the light-weight capabilities of the UltraLite database, Jillian is able to store all this data offline on her smartphone. Whenever Jillian has network connectivity on her phone, she is able to synchronize and alert other groups of any new orders.

This sample uses a custom-defined Mobile Link synchronization model that is deployed to the AdventureWorks2008 database, as well as a custom-created UltraLite remote database that mimics a subset of AdventureWorks2008 features. An ADO.NET application accesses the data in the remote (offline) database using both SqlCommand style objects and SqlTableAdapter style objects, demonstrating both methods of database interaction and .NET support.

Please refer to the online documentation for more information about UltraLite .NET support (PDF P.2) and Mobile Link synchronization (PDF P.4).

ManufacturingMobile (ADO.NET, Mobile Link, Server Initiated Synchronization, Web Services)

Taylor Maxwell regularly checks the assembly lines for the status on currently produced items, production schedules, failure rates, and so on. Since he is frequently away from his desk, he needs to get notifications of new orders directly on his Windows Mobile handheld. Specifically, he needs to know which products are below safety levels so that he may schedule them for future production.

Thanks to SQL Anywhere’s server-initiated synchronization capabilities, Taylor can get updates on current inventory versus demand levels as soon as new orders come in. He can then take action on priority items on the fly, and synchronize new work orders without having to return to his desk.

This sample uses a custom-defined Mobile Link synchronization model and a custom built SQL Anywhere remote database that holds a subset of the manufacturing information. Using the Mobile Link client API and server-initiated synchronization, data can be synchronized either on demand at the remote database, or when specific business logic is met at the consolidated database. An ADO.NET application creates new work orders at the remote by using SQLCommand style objects. In addition, reporting of inventory levels is done on the mobile device using SQL Anywhere web services and Internet Explorer Mobile.

Please refer to the online documentation for more information about SQL Anywhere .NET support (PDF P.4), Mobile Link synchronization (PDF P.4), Mobile Link Client API for .NET (PDF P.335), Server-initiated synchronization (PDF P.2) and SQL Anywhere web services (PDF P.882).

Requirements

• SQL Anywhere 11 or higher
• Microsoft Visual Studio 2008 SP1 or higher
• AdventureWorks2008 Sample Database for SQL Anywhere
• Windows Mobile 6 (or higher) Professional or Standard devices or emulator images

Installing the Samples

1. Install the ‘AdventureWorks2008 Sample Database’.
2. Run the installer for the ‘AdventureWorks2008 Windows Mobile Samples’.

The installer for the Windows Mobile Samples also configures the AdventureWorks2008 SQL Anywhere database for data synchronization by adding two remote users: Jillian (sales staff) and Taylor (engineer). You can examine these settings by connecting to the database using the Mobile Link plug-in in Sybase Central.

Running the Samples

1. Deploy SQL Anywhere to the Windows Mobile device or emulator (PDF P.356).
2. Run the AdventureWorks2008 Mobile Link Server, by selecting ‘Start MobiLink Server with Notifier’ from the ‘AdventureWorks2008’ program group located under ‘SQL Anywhere 11’.
3. Open the Visual Studio 2008 solution file in Microsoft Visual Studio 2008 SP1 by either selecting ‘Open WM Samples Visual Studio 2008 Solution’ from the ‘AdventureWorks2008’ program group, or by manually browsing to and opening the file ‘AdventureWorksWMSamples.sln’ in the samples directory.
4. You can build and run any of the projects from the ‘Solution Explorer’.

Project Considerations

Sales Mobile	Manufacturing Mobile
The Sales Mobile application is designed to run on a Windows Mobile Standard smartphone (no touch screen). The database running on the smartphone is UltraLite. Before building the project, you will need to resolve the path to the UltraLite assemblies. In the Visual Studio Solution Explorer, remove and re-add the references to the following: iAnywhere.Data.UltraLite located in %SQLANY11%\UltraLite\UltraLite.NET\CE\Assembly\V2 iAnywhere.Data.UltraLite.resources located in %SQLANY11%\UltraLite\UltraLite.NET\CE\Assembly\V2\xx, where xx is a two-letter abbreviation for the desired language (for example, use "en" for English) You will also need to remove ‘ulnet11.dll’ from the project, and re-add it to the project As a Link from: ‘%SQLANY11%\UltraLite\UltraLite.NET\CE\Arm.50’. Make sure that the Copy to Output Directory property for that file is set to either ‘Copy if newer’ or ‘Copy always’. After deploying, if your device/emulator is not connected through ActiveSync to the machine where the Mobile Link server is running, you will need to edit the configuration settings by starting the ‘SalesMobile’ application, going to File > Settings and editing the ‘stream’ paramater.	The Manufacturing Mobile application is designed to run on a Windows Mobile Professional device (touch screen). The database running on the handheld is SQL Anywhere for Windows Mobile. You must deploy SQL Anywhere to your Windows Mobile device/emulator. Select ‘Deploy SQL Anywhere for Windows Mobile’ in the SQL Anywhere 11 program group. You need the following options installed on your device: Database > Server Synchronization and Messaging > MobiLink International Components for Unicode (ICU) You will also need to resolve the path for the SQL Anywhere assemblies before building the project. In the Visual Studio Solution Explorer, remove and re-add the references to the following: iAnywhere.Data.SQLAnywhere located in %SQLANY11%\CE\Assembly\V2 iAnywhere.MobiLink.Client located in %SQLANY11%\CE\Assembly\V2 If your device/emulator is not connected through ActiveSync to the machine on which the Mobile Link server is running, then at the start of the application set the Host/Port/Protocol options accordingly.
Please refer to the online documentation for more information about Mobile Link client network protocol option summary (PDF P.37), Deploying SQL Anywhere to Windows Mobile (PDF P.356), Adding a reference to the Data Provider DLL in your project (PDF P.116) and Setting dbmlsync extended options (PDF P.158).

Samples Walkthrough

Part I: Jillian Carson

In this part of the walkthrough, Jillian Carson, a sales representative, enters a new order on her smartphone (Windows Mobile 6 Standard) while working offline. Once the order is entered, Jillian synchronizes it with the main corporate database at Adventure Works Cycling headquarters.

1. Run the ‘SalesMobile’ project from the Visual Studio 2008 solution.
2. It will automatically load all existing customers inside the UltraLite database running on the Windows Mobile smartphone.
3. Scroll through the list of customers and choose ‘AW00030118’ as the customer - it's the last customer in the list (simply scroll to the left to wrap around).
4. Go to ‘File’ > ‘View Orders’ to display all the orders that have been placed for this customer.
5. Go to ‘File’ > ‘Add Order’ to add a new order.
6. Add the following items using ‘File’ > ‘Add Item ’(click Save after adding each item):
   • 100 x ML Road Frame - Red, 58
   • 90 x Mountain-500 Black, 52
   • 110 x Road-750 Black, 52
   • 100 x Touring-3000 Blue, 44
7. You will see all four items for this new order. All this information is stored inside the UltraLite database.
8. Click ‘Back’ until you return to the ‘Customers’ form.
9. Click ‘File’ > ‘Synchronize’ to sync data to the consolidated database. Make sure that your device/emulator can connect to the Mobile Link server; see Project Considerations.
10. Click ‘Exit’ to close the application. Disconnect your Windows Mobile Standard smartphone or emulator from ActiveSync.

You can launch Sybase Central (PDF P.712) or Interactive SQL (PDF P.730) and connect to the consolidated database AdventureWorks2008 to see the new order that was just entered. It will be stored in the table Sales.SalesOrderHeader.

Please refer to the online documentation for more information about the UltraLite .NET support (PDF P.2) and the Mobile Link synchronization (PDF P.3).

Part II: Taylor Maxwell

In this part of the walkthrough, Taylor Maxwell, an engineer, will place work orders for more products to satisfy current demand. He will then verify inventory levels for all products. Data is synchronized with the consolidated database using both server-initaited synchronizations (push) and on demand synchronizations (pull). The application used to place orders makes use of SQL Anywhere .NET support. Furthermore, inventory data is gathered using SQL Anywhere web services.

1. Run the ‘ManufacturingMobile’ project from the Visual Studio 2008 solution.
2. At the beginning of the application, if your device/emulator is not connected through ActiveSync to the machine on which the Mobile Link server runs, edit the Host/Port/Protocol options. Otherwise, use the defaults. Tap on OK, the Listener (PDF P.19) (used to receive synchronization requests from the Mobile Link Notifier (PDF P.16) ) will be started - hide it to continue.
3. The ‘Product List’ tab displays all products that are currently under Safety Level in the database. The ‘Pending Orders’ tab displays information on future orderes only for the products that are checked in the ‘Product List’ tab. The ‘Product Info’ tab displays inventory and assembly information on any product in the database. The main popup menu for the application is accessed in the ‘Product List’ tab by tapping and holding for a few seconds (Windows Mobile right-click).
4. Check all the products that are displayed in the ‘Product List’ tab, so that more units get added to the inventory.
5. In the ‘Product List’ tab, tap and hold on the main list for a few seconds to bring up the main menu. Tap ‘Place Work Orders’ so that the checked product orders get inserted into the SQL Anywhere database.
6. If you completed the SalesMobile sample and met the Project Considerations above, after approximately 30 seconds (the pooling frequency set for this solution) a server-initiated synchronization will start. The 4 products you added in the SalesMobile walkthrough are displayed here (tap and hold on the product list for a few seconds to bring up the main menu and tap ‘Refresh’). This is because the new order has pushed the inventory levels for those products below safety level.
7. Alternativley, you can perform an on-demand synchronization request: in the ‘Product List’ tab, tap and hold on the product list for a few seconds to bring up the main menu and tap ‘Synchronize’ so that the orders placed get synchronized to the consolidated database.
8. Minimize the application by tapping the ‘X’ in the top right corner. Open ‘Internet Explorer’ (on the handheld) and navigate to http://localhost/Inventory. The web page you see is generating using SQL Anywhere web services.
9. Ensure that only the 4 products that have been used in the SalesMobile sample are below safety level (colours red and yellow).
10. Return to the application using File Explorer: browse to ‘\Program Files\ManufacturingMobile\’ and tap on ‘ManufacturingMobile.exe’.
11. You can check the remaining products for manufacturing as you have done in steps 4 and 5.
12. In the ‘Product List’ tab, tap and hold on the main list for a few seconds to bring up the main menu. Tap ‘Exit’ to close the application.
13. Shut down the Listener and Internet Explorer on the Windows Mobile device. Disconnect your Windows Mobile Standard smartphone or emulator from ActiveSync.
14. Shut down the Mobile Link server and Notifier running on your desktop: run ‘Stop MobiLink Server with Notifier’ from the ‘AdventureWorks2008’ program group.
15. Shut down the consolidated SQL Anywhere database: run ‘Stop Sample Database ’ from the ‘AdventureWorks2008’ program group.

Please refer to the online documentation for more information about Server-initiated synchronization (PDF P.2)and SQL Anywhere web services (PDF P.882).

Clean the AdventureWorks2008 Database

If you want to restore the AdventureWorks2008 consolidated database to its original state (in case you want to run the walkthoughs again), then run ‘SalesMobile - Clean consolidated database’ and ‘ManufacturingMobile - Clean consolidated database’ from the ‘AdventureWorks2008’ program group. Make sure you also remove the applications from the Windows Mobile devices.

Relevant Code Highlights

The following section highlights the code that is used to interface with SQL Anywhere.

ADO.NET Connectivity (ULCommand / SACommand)

One method of ADO.NET data provision is using SQL Anywhere client objects, similar to SqlClient provided by the base class. The following code demonstrates the basics of retrieving data using the UltraLite and SQL Anywhere ADO.NET data providers.

ULCommand		SACommand
// Connect to the database. ULConnection conn = new ULConnection("DBF=File_path;UID=dba;PWD=sql"); conn.Open(); ULCommand cmd = conn.CreateCommand(); // Retrieve the data cmd.CommandText = "SELECT Column FROM Table"; ULDataReader data = cmd.ExecuteReader(); while (data.Read()) {     myString = data.GetString(1); } data.Close(); // Close the connection. conn.Close();		// Connect to the database. SAConnection conn = new SAConnection("DSN=DataSource_name;UID=dba;PWD=sql"); conn.Open(); SACommand cmd = conn.CreateCommand(); // Retrieve the data cmd.CommandText = "SELECT Column FROM Table"; SADataReader data = cmd.ExecuteReader(); while (data.Read()) {     myString = data.GetString(1); } data.Close(); // Close the connection. conn.Close();

Manufacturing Scenario

The following queries are used to view manufacturing and product data. They have been slightly modified from their original version found here: Manufacturing Scenario.

Viewing a multilevel bill-of-materials list for a parent product

The following SQL stored procedure running on the Windows Mobile database gets all the components that are used to create a specific parent product: pid

CREATE PROCEDURE DBA."getAssembly"( IN pid INT )
BEGIN
  WITH RECURSIVE Parts(AssemblyID,
                       ComponentID,
                       PerAssemblyQty,
                       EndDate,
                       ComponentLevel) AS
  ( SELECT b.ProductAssemblyID,
           b.ComponentID,
           b.PerAssemblyQty,
           b.EndDate,
           0 AS ComponentLevel
    FROM Production.BillOfMaterials AS b
    WHERE b.ProductAssemblyID = pid AND b.EndDate IS NULL
    UNION ALL
    SELECT bom.ProductAssemblyID,
           bom.ComponentID,
           p.PerAssemblyQty,
           bom.EndDate,
           ComponentLevel + 1
    FROM Production.BillOfMaterials AS bom INNER JOIN Parts AS p ON bom.ProductAssemblyID = p.ComponentID AND bom.EndDate IS NULL )
  SELECT "Name",
         ComponentID,
         AssemblyID
  FROM Parts AS p INNER JOIN Production.Product AS pr ON p.ComponentID = pr.ProductID
  ORDER BY AssemblyID
END