Northwind Pocket Sales: Field Sales for Windows Mobile-based Pocket PCs
Christian Forsberg and Andy Sjostrom
Business anyplace
Contribution from: Odyssey Software
January 2005
Applies to:
Windows Mobile-based Pocket PCs
Microsoft® Visual Studio® .NET 2003
Microsoft .NET Compact Framework version 1.0
Microsoft SQL Server™ 2000
Microsoft SQL Server CE
Summary: Learn about mobile sales force automation and how to design and develop solutions for Windows Mobile-based Pocket PCs using Visual Studio .NET and .NET Compact Framework. The source code in this article implements server components, database, and a Pocket PC client. (56 printed pages)
Download Northwind Pocket Sales - Field Sales for Windows Mobile-based Pocket PC.msi from the Microsoft Download Center.
Odyssey Software CFCOM enables transparent access to controls such as the Windows Media Player and objects such as the Pocket Outlook Object Model and ADOCE, and virtually any third party COM or ActiveX component. CFCOM can be licensed from Odyssey Software, although the code sample in this article will work with the license key found in the source code.
Download cfcom.exe from the Microsoft Download Center.
Contents
Introduction
Mobile Field Force Automation Benefits
Northwind Traders's Field Sales Business Process
Server Walkthrough
Northwind Pocket Sales Application Client Walkthrough
Code Walkthrough
Conclusion
Introduction
This article builds on the topics presented in Northwind Pocket Service: Field Service for Windows Mobile-based Pocket PCs and Northwind Pocket Service: Field Service for Windows Mobile-based Smartphones. These two articles describe key elements about how to develop mobile field service applications. This article provides a sample solution that addresses the needs of the fictitious company Northwind Traders from a sales process and field sales point of view.
Mobile Field Force Automation Benefits
Regardless of the vertical industry segment, you will find that decision makers no longer ask the questions: "What is a mobile solution? What can it do for us? Is technology really ready for this?". Instead, most companies already have a clear and correct understanding of most, if not all, business- and technology- related aspects of mobile solutions. Some companies have already implemented mobile solutions in their business; some companies have managed to put pilot studies into production, while other companies are just about to initiate their first mobile solutions projects.
The majority of mobile enterprise applications that are either deployed or currently being developed fall into the field service or the field sales scenarios. This division is not surprising because the workers are already in the field. The industry's need is obvious and easy to both identify and quantify. Previous articles have illustrated how identification and quantification can be articulated. You can read more about field service topics in the previously-published articles, Northwind Pocket Service: Field Service for Windows Mobile-based Pocket PCs and Northwind Pocket Service: Field Service for Windows Mobile-based Smartphones.
A recent study conducted by IDC was published in Sweden and noted that about 60 percent of Nordic companies view mobile solutions as a must to stay competitive. The study included more than 600 people. Similar results were observed by the Swedish IT magazine CIO. Table 1 summarizes the driving forces behind mobile solution development that were reported by the respondents.
Table 1. Driving Forces and Respective Percentages for Mobile Solution Development According to CIO
| Driving force | Percent |
|---|---|
| Increased productivity | 73 |
| Increased efficiency | 62 |
| Demand from internal users | 60 |
| Cost reductions | 26 |
| Competitiveness | 22 |
| Demand from external users | 21 |
| Method to introduce support to field service staff | 18 |
The market for mobile solutions is clearly driven by quantifiable benefits and by business scenarios that rely heavily on information management. The key aspects of studies of benefits target time — and cost savings based on precise data. The CIO study reported the return-on-investment (ROI) benchmarking areas summarized in Table 2.
Table 2. Return-On-Investment Measurements Due to Mobile Solutions According to CIO
| ROI measurement | Percent |
|---|---|
| Increased productivity | 74 |
| Increased customer satisfaction (internal customers) | 51 |
| Cost reductions | 44 |
| Increased customer satisfaction (external customers) | 37 |
| Increased sales | 14 |
| Shorter sales cycles | 9 |
When you look at the specifics of the mobile field force automation projects, there are a number critical success factors that commonly are identified:
- Real-time access to sales orders leads to faster and more efficient deliveries
- Improved customer satisfaction
- Increased sales force productivity
- Cost reductions in administration and travel
- Faster invoicing process
- Improved sales reporting and follow-up actions
- More accurate sales forecasts
In conclusion, the mobile field force automation projects address real needs. The costs related to these projects need to be recovered by the benefits from the solution. Now you can take a look at Northwind Traders's field sales business process.
Northwind Traders's Field Sales Business Process
As described in previous articles, the customers of Northwind Traders have vending machines that are used to sell the various products from Northwind Traders. The mobile field sales force of Northwind Traders needs to keep track of what each customer needs in terms of products, quantities, and deliveries.
The following list illustrates the processes and in the order they occur;
- Customer needs more products.
- Customer places order.
- Order is placed in back office (or by field sales staff).
- Field sales staff retrieves (synchronizes) new order.
- Field sales staff plans the visit to the customer.
- Field sales staff makes the sale (visit) to the customer.
- Field sales staff finalizes the order.
- Field sales staff sends (synchronizes) order to back office.
- Back office delivers order.
- Back office invoices order.
- Customer pays invoice.
- Order is closed.
The sample application for this article covers processes 3 through 8. Processes 1 and 2 do not have any digital support, and processes 9 through 12 are not covered by this article because these are common back-office functionalities. As indicated by process 3, orders are normally placed in the back office, but the field sales staff can also create orders while they are mobile. If that is the case, process 4 is not necessary.
The creation of orders offline presents the opportunity not only to support the primary ROI measurements, like increasing the productivity and customer satisfaction, but also the highly-desirable ROI measurements to have shorter sales cycles and increased sales. Then, the cost of implementing the application is not only paid back in the cost savings, but it also increases revenue.
From a use-case point of view, the processes are shown in Figure 1.
.gif)
Figure 1. Use-case model
The use cases for the back office staff (places order in back office) is implemented as a server Web application, and the other use cases are included in the client Windows Mobile-based Pocket PC application for the field sales staff. For more details about the architecture, please see the previous articles Northwind Pocket Service: Field Service for Windows Mobile-based Pocket PCs and Northwind Pocket Service: Field Service for Windows Mobile-based Smartphones.
Next, you can take a look at the feature walkthrough of the back office server application and then the client.
Server Walkthrough
For the back office staff, a Web application has been prepared to create new sales order assignments. This Web application is an ASP.NET application written with Microsoft Visual Studio .NET 2003 in C# with data in Microsoft SQL Server 2000.
The application shows how to support the initial steps in a sales order business process. The intention is not to show how this is done with regards to source code because the focus of this article is the mobile solution. However, for completeness the walkthrough gives an example of how this sales order business process step is implemented. If desired, the source code can be examined in this article's sample code.
First Page
The first page shows a list of the sales orders and their respective status. The last order has the status Open, which means that it has not yet been delivered, as shown in Figure 2.
.gif)
Figure 2. First page of the Web application. Click the thumbnail for a larger image.
When you click the order No of this sales order, you are presented with the sales order details, as shown in Figure 3. The details for the sales order include information about the products to be sold (Product Name, Unit Price, Quantity, and Discount).
.gif)
Figure 3. Sales order details
Creation of a New Service
On the first page, if you click the Add new sales order link, you will start the wizard to create a new sales order, as shown in Figure 4. The first step is to select a customer. Type part of the customer name, and when you click Find, the search results are shown.
.gif)
Figure 4. Select a customer. Click the thumbnail for a larger image.
When you select one of the customers (in this walkthrough, White Clover Markets), select the other sales order information on the next wizard page, as shown in Figure 5. You can select the date that the delivery is required and who is assigned to handle the sale. Click Next when you are finished.
.gif)
Figure 5. Enter sales order information. Click the thumbnail for a larger image.
On the last wizard page, as shown in Figure 6, you can add the order details (information about the products). Each order detail (product) has a unit price, required quantity, and maybe a discount rate. When you select a product in the list, the normal unit price is suggested, but you can update it. You must enter quantity and discount amounts. When you click New, the order detail is added to the list. The Delete link on each row removes the respective order detail.
When you click Finish, you return to the first page, and the sales order is ready to be transferred to the field worker.
.gif)
Figure 6. Add product information with price, quantity, and discount. Click the thumbnail for a larger image.
Northwind Pocket Sales Application Client Walkthrough
The example client scenario is a Pocket PC application written with Microsoft Visual Studio .NET 2003 in C# and targets the Microsoft .NET Compact Framework.
The application shows how to support a sales business process by using a mobile handheld device. The design choices were made to align to the process as much as possible and also maximize efficiency for the field worker. The various design choices will be commented during the walkthrough of the application. Also note that the code is explored after the application screen design.
First Run
The first run of the application on the device will probably be made by a system administrator, and not by the end user. The inclusion of these screens in the walkthrough is because this article has a developer focus.
The first time the application is run on the device, no database exists, so you are notified with the message shown in Figure 7.
.gif)
Figure 7. No database found on first run message
After the initial message box, you are routed to the Local tab on the Options screen where the local options (current user, local database name, and location) are available, as shown in Figure 8.
.gif)
Figure 8. Local options with a suggested database name
You can change the Local database name, and you can specify the server (or remote) options on the Server tab, as shown in Figure 9.
On the Server tab, it is important to make sure the Server database (URL) option is correct. This is the URL for the Microsoft® SQL Server™ CE RDA server functionality, and it will be used during the first synchronization with the server (immediately following the creation of the database). Make sure you have correctly set this functionality up according to the SQL Server CE help file. In a real-world scenario, these settings are preset at installation of the client, and the end-user should not need to change these values.
.gif)
Figure 9. Server options
On the Local tab, when you tap Create New Database, you are routed to the first screen in the Synchronize wizard, as shown in Figure 10. Note that the only option available is to initialize (create) a new database.
.gif)
Figure 10. Synchronize wizard for creating new database
When you tap Next, you will be prompted for the Username and Password, as shown in Figure 11.
.gif)
Figure 11. Server authentication
When you tap Finish, the first synchronization is started, as shown in Figure 12. During the synchronization, the wait cursor is shown and the progress is reported on screen. During this process, several calls are made to the server to retrieve data in XML format (DataSets).
.gif)
Figure 12. Synchronization is performed
When the synchronization is complete, the database is created and loaded with reference data. At this point, the Close button is available, as shown in Figure 13.
.gif)
Figure 13. Synchronization complete
When you tap Close, you return to the main menu.
Main menu
The second time you start the application (and each subsequent time), you are presented with the Main menu screen, as shown in Figure 14. The functionality of the application is aligned with the business process of the sales field worker. The main steps in this process are to plan the sales, to make the sales, and to report the sales made. A ListView control has been used to present the main menu options as icons that can be tapped.
.gif)
Figure 14. Main menu
All menu options are also available in Menu, as shown in Figure 15.
.gif)
Figure 15. Alternative menu
The purpose of having the same functionality available in many ways is that different users prefer different ways of doing the same thing. Although this level of redundancy is common in desktop applications, care should be taken when providing redundant ways of accessing functionality on a Pocket PC so that screen estate is not wasted.
Synchronization
When the database is created and initialized from the server, it includes only reference data. To load the current set of sales orders, you need make a synchronization of sales orders. You can synchronize sales orders by tapping Sync on the Main menu screen, as shown in Figure 14. The first step in the Synchronize wizard appears, as shown in Figure 16. Synchronize sales orders is the default, and that is the option you want, so tap Next.
.gif)
Figure 16. Synchronize wizard to get new sales orders
On the second page of the Synchronize wizard, fill in the Username and Password boxes, as shown in Figure 17, and then tap Finish.
Figure 17. Server authentication
The synchronization of sales orders begins. Sales orders completed and reported are sent to the server, and new sales orders to handle are downloaded from the server. The synchronization is complete when the Close button appears, as shown in Figure 18.
.gif)
Figure 18. Sales orders synchronized
Tap Close to end the Synchronize wizard and return to the main menu.
Plan
The first step in the sales process is to plan the sales to be made. From the Main Menu screen, tap Plan.
The first page in the Plan wizard appears, as shown in Figure 19, and allows you to search for new sales orders. You can enter the search criteria (default values for the date search interval are from today and a month ahead). When you tap Find, the found service items are presented in the list.
.gif)
Figure 19. Find sales orders to plan
If you want to create a new order, select New on the first wizard page shown in Figure 19. The detailed sales order information for a new order is shown in Figure 20.
.gif)
Figure 20. New sales order information
If you select a sales order shown in Figure 19, and then tap Next, the detailed sales order information is displayed, as shown in Figure 21. The detailed sales order information includes some basic information, such as order number, customer name, order date, and required delivery date.
.gif)
Figure 21. Detailed sales order information
The customer name is a link that can be tapped to show more details. If you tap the customer name, more details are displayed, as shown in Figure 22.
.gif)
Figure 22. Detailed customer information
With the detailed customer information presented, you can add the customer contact to the standard Pocket PC Contacts. This is an example of how the application integrates with the standard applications of a Windows Mobile-based Pocket PC. The ability to integrate with the standard Pocket PC applications, such as Contacts, is very powerful because it helps the user maintain personal customer information. When the customer information is added to Contacts, the user can add more information (additional phone numbers or e-mail addresses) and the information is also more easily available to the user. When you tap Add to Contacts, as shown in Figure 22, the new contact is added and then is displayed in Contacts, as shown in Figure 23.
From a code point of view, you need to use a third-party wrapper to access contact information from your .NET Compact Framework application on Windows Mobile and Windows Mobile 2003 Second Edition software. The next release of the Windows Mobile platform will allow direct access to all PIM data from managed code.
.gif)
Figure 23. Customer contact added to Contacts
When you close the new contact (by tapping OK in the top-right corner of the screen), you return to the detailed customer information page. When you close that screen, you return to the detailed sales order information page (second page in the Plan wizard).
If you tap Add to Tasks, as shown in Figure 21, a new task in the standard Task application is created and displayed, as shown in Figure 24.
.gif)
Figure 24. Sales order added to Tasks
When you close the new task, and then you tap Add to Calendar, as shown in Figure 21, a new appointment in the standard Calendar application is created and displayed, as shown in Figure 25. This feature allows the user to make a personal appointment that can hold more information about the service (start and end time and reminder alarms).
.gif)
Figure 25. Sales order added to Calendar
When you close the new appointment, you return to the detailed sales order information screen (the second page in the Plan wizard, as shown in Figure 21). If you tap Next from the second page in the Plan wizard, you will see a screen showing the sales order details (products) to be sold, as shown in Figure 26. The sales order details also show the price, quantity, and discount for each product. When you tap and hold on a product, a pop-up menu appears, which allows you to edit the order detail.
.gif)
Figure 26. Sales order details (products)
If you want to add a new product, you can tap New, and then the Order Detail wizard starts, as shown in Figure 27. The first page allows you to search for a product. After you enter the search criteria, tap Find. The found products are presented in the list. If you tap and hold a product, the View option appears,
.gif)
Figure 27. Find a product to add to an order
You can tap View to show the detailed product information, as shown in Figure 28.
.gif)
Figure 28. Detailed product information
When you close this screen, you return to the first page in the Order Detail wizard, as shown in Figure 27. When you select a product from that page, and then tap Next, the order detail information is displayed, as shown in Figure 29. On this page, you can modify the product price, quantity, and discount rate. By using the spin box, you can change the numbers without using the soft input panel (for example, the soft keyboard). The discount percentage will increase and decrease in increments of 5 because that increment is the normal rates used.
.gif)
Figure 29. Order detail information
You can tap Finish after you enter the information into the Order Detail wizard. The product is added to the third page of the Plan wizard, as shown in Figure 26. When you tap Next, you will need to fill out the shipping information, as shown in Figure 30. Shipping information includes the shipping address, the shipper to use, and the freight amount for the shipment.
.gif)
Figure 30. Shipping information
When you tap Next, the final page in the Plan wizard appears, as shown in Figure 31. This page allows you to select a new status for the sales order. Because you can now consider the sales order planned (booked in Calendar, and so on), you need to change the status to Planned. The sales order is ready for the next step in the process, which is performing the actual sale.
.gif)
Figure 31. Set new sales order status
Sales
The sales process deals with the actual sale. As in the planning process, the sales process functionality is implemented through a wizard. From the Main Menu screen, tap Sales.
The first step is to find the sales order to sell, as shown in Figure 32. Just as in the Plan wizard, you can enter search criteria to find sales orders, which are presented in the list.
.gif)
Figure 32. Find sales order to sell
If you select a sales order to be sold, and then tap Next, the detailed sales order information is displayed, as shown in Figure 33. The basic details are presented much in the same way as in the Plan wizard. Again, you can tap the customer's name to show detailed information.
.gif)
Figure 33. Detailed sales order information
When you tap Next, the list of order details (products) is displayed, as shown in Figure 34. The sales order details show the products to be sold in addition to the price, quantity, and discount for each product. Similar to the Plan wizard (see Figures 26 to 28), the order details can be added, updated, and deleted.
.gif)
Figure 34. Sales order details (products)
When you tap the Next button, the shipping information page appears, as shown in Figure 35. Shipping information includes the shipping address, the shipper to use, and the freight amount for the shipment.
Figure 35. Shipping information
When you tap Next, the final step in the Sales wizard appears, as shown in Figure 36. Again, you can select a new status for the sales order. Because you now can consider the sales order sold (all order details/products are in place), you can change the status to Ordered. The sales order is ready for the next step in the process — reporting the sale.
.gif)
Figure 36. Set new sales order status
Report
The final step in the sales process is reporting the sale — that is, the confirmation of the sales order, including obtaining the customer's signature. As in the previous processes, the functionality is implemented as a wizard. From the Main Menu screen, tap Report.
The first step is to find the sales order to report, as shown in Figure 37. As in the previous wizards, you can enter search criteria. The sales orders that are found are presented in the list.
.gif)
Figure 37. Find sales order to report
When you select a sales order to be reported, and then tap Next, the sales order report is displayed, as shown in Figure 38. This report enables you to verify that the sales order made is correct.
.gif)
Figure 38. Sales order summary report
When you tap Next, the customer confirmation page is displayed, as shown in Figure 39. You can enter the name of the customer contact that confirms the completion of the sales order (the default value is the customer contact name). The customer contact can then sign the confirmation directly on the screen. You can also select how the customer will be notified that the order has been made (by e-mail, fax, or directly to a connected printer).
.gif)
Figure 39. Customer confirmation page
Tapping Next takes you to the final page in the Report wizard, as shown in Figure 40. In the final step, you can select a new status for the sales order. Because you now can consider the sales order completed (planned, sold, and reported), you change the status to Reported. The sales order is ready to be sent back to the server during the next synchronization of sales orders.
.gif)
Figure 40. Set new sales order status
Customers
As a support function, when you select Customers from the Main Menu, you can search for customers by name and contact. When you tap the Find button in Figure 41, the customer list is filled. Each customer in the list displays a name and contact. When you tap-and-hold a customer's name in the list, and then select View, the product details are displayed, as shown in Figure 22.
.gif)
Figure 41. Find customers
You can also start the various processes (plan, sales, and report) by selecting the respective context menu options. You can also add the customer as a contact in the standard Pocket PC application Contacts by selecting Add Contact.
Products
As a support function, when you select Products from the Main Menu, you can search for products in stock (that is, in the delivery vehicle) by name and category. When you tap the Find button in Figure 42, the inventory list is filled. Each product in the list displays a name, number of products in stock, and number of products on order. When you tap-and-hold a product in the list, and then select View, the product details are displayed, as shown in Figure 28.
.gif)
Figure 42. Find products
Options
When you select Options from the Main Menu, the Options screen appears. The available options are divided into two tabs: one for local options and one for server options. The Local tab is shown in Figure 43. On the Local tab, you can select the current user (Employee) and location of the database file.
.gif)
Figure 43. Local options
The Server options tab is shown in Figure 44. The Server tab provides the options for database synchronization using RDA, server login name, and sales order synchronization using a Web Service.
Figure 44. Server options
About
All applications should include a screen with the product name, version, copyright, and other legal information. In the Main menu screen, About displays this information, as shown in Figure 45. This screen can also include a link to a Web page with product or support information.
.gif)
Figure 45. About the application
World Ready
This completes the walkthrough of the application, but because the main technical feature of this sample application is to show how globalization (and localization) support can be implemented by using the support in the .NET Compact Framework, the walkthrough will continue.
When you change the Regional Settings (tap Start, Settings, and then tap Regional Settings) to Portuguese (Brazil) as the language, and then restart the application, the complete application is translated. The following figures are the Brazilian Portuguese versions of the screens shown in Figures 14 and 37 – 40.
Figure 46 shows the translated Main menu screen from Figure 14. You can see in Figure 46 that the form's title bar and form's controls (like the Menu principal label) are translated, as are the menu icon labels. And even if not shown, the alternative menu, originally shown in Figure 15, is also translated.
.gif)
Figure 46. Translated main menu
The translated first page in the Report wizard is shown in Figure 47. Just as before, the form title and form controls (labels and buttons) are translated. Further, the column titles in the list are translated in addition to the Edit menu and its menu options. Note also that the date format and the order dates in the list are presented in the correct format for Brazil (day/month/year).
.gif)
Figure 47. Translated first page in the Report wizard (find orders)
In Figure 48, you can see that even the report labels (Cliente, Data de ordem, and Produto) are translated. Again, the correct date format is used.
.gif)
Figure 48. Translated second page in the Report wizard (order summary report)
Figure 49 shows that controls, like radio buttons, are translated.
.gif)
Figure 49. Translated third page in the Report wizard (confirmation)
Because translations in languages other than English often require more space (characters), you should try to make the size of the controls as large as possible to prevent translated texts to be truncated. The red markings in Figure 50 show the size of the controls in the third page of the Report wizard.
.gif)
Figure 50. Maximize sizing of controls
Finally, as shown in Figure 51, you can see that the status for sales orders (Planned, Ordered, and Reported) is also translated.
.gif)
Figure 51. Translated final page in the Report wizard (set status)
Code Walkthrough
The previous section provided an example client scenario for the Pocket Sales application. As most of the general parts of the code is covered in the article Northwind Pocket Service: Field Service for Windows Mobile-based Pocket PCs, this article will mainly focus on how the Pocket Sales application is prepared to be translated to multiple languages.
Most of the code in the sample application is reusable, but the functionality related to globalization (and localization) that is located in the GlobalHandler class, can be reused with great benefits, such as the ease of translating forms, user messages, and constants. This type of functionality is probably needed most in enterprise applications that are intended to be used in more than one country. Also, the FormCache class, which implements the functionality to cache and stack forms, can increase performance of any application.
GlobalHandler
This walkthrough starts by examining the GlobalHandler class. The beginning of the class looks like the following code.
private const string resourceSource = "Sales.strings";
private ResourceManager resourceManager;
private CultureInfo currentCulture;
private Form form;
public static readonly GlobalHandler Translate = new GlobalHandler();
private GlobalHandler()
{
if (!IsUsEnglish())
{
resourceManager = new ResourceManager(resourceSource,
this.GetType().Assembly);
currentCulture = (CultureInfo)CultureInfo.CurrentCulture.Clone();
}
}
private bool IsUsEnglish()
{
return (CultureInfo.CurrentCulture.Name.ToLower() == "en-us");
}
The class is implemented as a singleton class with the instance name Translate. So to access the singleton class, you use the format **GlobalHandler.Translate.**property/method. In the private constructor, the resource manager is created, and the current culture is saved in instance variables. The first parameter of the resource manager constructor is the full path (assembly name and the first part of the resource file name) of the resource files. In this example, the assembly name is Sales, and the local resource files all begin with strings (default locale = "strings.resx", Brazilian Portuguese = "strings.pt-br.resx", and so on).
The IsUsEnghlish method is used to check if the current culture is U.S. English, and if so, the resource manager and current culture are not saved. Because the default language of the application (controls, messages, and so on) is English, the need to make a translation to English is not necessary. Therefore, the default locale resource file (strings.resx) does not include any entries because it is never used.
Each resource file is an XML file that has a specific format (schema), and the beginning of each file looks like the following.
<?xml version="1.0" encoding="utf-8" ?>
<root>
<xsd:schema id="root"
xmlns:xsd="https://www.w3.org/2001/XMLSchema"
xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xsd:element name="root" msdata:IsDataSet="true">
<xsd:complexType>
<xsd:choice maxOccurs="unbounded">
<xsd:element name="data">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="value" type="xsd:string"
minOccurs="0" msdata:Ordinal="1" />
<xsd:element name="comment" type="xsd:string"
minOccurs="0" msdata:Ordinal="2" />
</xsd:sequence>
<xsd:attribute name="name" type="xsd:string" />
<xsd:attribute name="type" type="xsd:string" />
<xsd:attribute name="mimetype" type="xsd:string" />
</xsd:complexType>
</xsd:element>
<xsd:element name="resheader">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="value" type="xsd:string"
minOccurs="0" msdata:Ordinal="1" />
</xsd:sequence>
<xsd:attribute name="name" type="xsd:string"
use="required" />
</xsd:complexType>
</xsd:element>
</xsd:choice>
</xsd:complexType>
</xsd:element>
</xsd:schema>
<resheader name="ResMimeType">
<value>text/microsoft-resx</value>
</resheader>
<resheader name="Version">
<value>1.0.0.0</value>
</resheader>
<resheader name="Reader">
<value>System.Windows.Forms.Design.CFResXResourceReader, System.CF.Design, Version=7.0.5000.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a</value>
</resheader>
<resheader name="Writer">
<value>System.Windows.Forms.Design.CFResXResourceWriter, System.CF.Design, Version=7.0.5000.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a</value>
</resheader>
The Brazilian Portuguese resource file continues like the following (this sample shows the first two entries).
<data name="Title">
<value>Pocket Vendas</value>
<comment>Pocket Sales</comment>
</data>
<data name="SalesStatusOpen">
<value>Aberto</value>
<comment>Open</comment>
</data>
In the schema definition at the beginning of the file, each entry has a data name used to identify text to be translated, a value that holds the translated text, and a comment. In this sample, the comment is used to hold the original English text, which is a recommended practice to simplify the translation work. You can use any XML tool to do the translation (and edit the file). If you use the development environment of Visual Studio .NET 2003, the environment looks like Figure 52.
.gif)
Figure 52. Editing the resource file in Visual Studio .NET 2003 for localized strings. Click the thumbnail for a larger image.
When the translation is finished, the file is simply added to the project as an embedded resource that is later compiled into a separate DLL for each locale (language).
Returning to the GlobalHandler class, the most basic function is the public Text method that is used to translate a generic text.
public string Text(string translationName, string defaultText)
{
string s;
if (!IsUsEnglish())
{
if ((object)(s = resourceManager.GetString(translationName,
currentCulture)) != null)
return s;
else
return defaultText;
}
else
return defaultText;
}
A default text (defaultText) is passed to the method and is used if the current locale is U.S. English or if the name of the resource string (parameter translationName) is not found.
This method is used in the sample application to translate texts that are not part of the user interface (controls, menus, and so on). These texts are notifications (like error messages) and also include global definitions (such as names of order statuses). As an example of the latter, the following example is the declaration of the array holding the status names from the Common singleton class.
public static readonly string[] SalesStatusText = new string[] {
GlobalHandler.Translate.Text("SalesStatusOpen", "Open"),
GlobalHandler.Translate.Text("SalesStatusPlanned", "Planned"),
GlobalHandler.Translate.Text("SalesStatusOrdered", "Ordered"),
GlobalHandler.Translate.Text("SalesStatusReported", "Reported"),
GlobalHandler.Translate.Text("SalesStatusShipped", "Shipped"),
GlobalHandler.Translate.Text("SalesStatusInvoiced", "Invoiced"),
GlobalHandler.Translate.Text("SalesStatusClosed", "Closed") };
Looking at the previous file extracts, you can see that after the last line in the above sample is executed, the first array item will hold the text Aberto if the locale Portuguese (Brazil) is selected in the Regional Settings when the application was started.
An example of a message is found in the AboutForm class:
MessageBox.Show(GlobalHandler.Translate.Text("MsgCantOpenWebPage",
"Could not open web page!"), this.Text);
In these two examples, you can see the point of supplying the default (US English) text in the code value rather than always retrieving the text (even when the current locale is US English) from a resource file by using only the name of the string (MsgCantOpenWebPage). This solution easies the readability of the code and helps the developer who always supply the default (US English) text of the message during development.
The Text method in this sample is also used to translate the main menu options (in the ListView) in the MainForm (showing the first menu option).
ListViewItem lvi;
lvi = new ListViewItem(GlobalHandler.Translate.Text(
"MainFormmitPlan", "Plan"));
lvi.ImageIndex = 0;
lvwMenu.Items.Add(lvi);
The string name used ("MainFormmitPlan") is the same resource string used to translate the menu option in the main form (see the following code samples for translation of form controls and menus). By using the exact same name, the consistency of the translated texts is secured.Now you will look at the real value of the GlobalHandler class because it allows complete forms, including all controls and menus, to be translated by using a single line of code in each form. The code required in the form looks like the following.
GlobalHandler.Translate.Form(this);
This code is normally placed at the beginning of the form's Load event. The code for the Form method looks like the following.
public void Form(Form form)
{
string s;
if (!IsUsEnglish())
{
this.form = form;
// Set title
if ((object)(s = resourceManager.GetString("Title", currentCulture)) != null)
form.Text = s;
// Translate form controls
Control(form);
// Translate menu
if (form.Menu != null)
Menu(form.Menu);
this.form = null;
}
}
Just like the previous Text method, no translations are made if the current locale is U.S. English. The passed form instance is saved in a private variable during the method call, and then the form title is replaced with the translated application name (according to Pocket PC standards, the titles are always the same for all forms in an application). Then, before the menu of the form is translated by using the Menu method, the form controls are translated by using the Control method (note that a form is also a control and can, therefore, be passed as a parameter of type Control):
private void Control(Control control)
{
string s;
foreach (Control ctrl in control.Controls)
{
// Translate with form prefix
if ((object)(s = resourceManager.GetString(form.GetType().Name + GetItemName(ctrl), currentCulture)) != null)
ctrl.Text = s;
else
// Translate with no prefix
if ((object)(s = resourceManager.GetString(GetItemName(ctrl), currentCulture)) != null)
ctrl.Text = s;
// Container with subcontrols to translate?
if (ctrl.Controls.Count > 0)
Control(ctrl);
// ContextMenu to translate?
if (ctrl.ContextMenu != null)
Menu(ctrl.ContextMenu);
// ListView with columns to translate?
if (ctrl.GetType().Name == "ListView")
foreach (ColumnHeader col in ((ListView)ctrl).Columns)
if ((object)(s = resourceManager.GetString(form.GetType().Name + GetItemName(col), currentCulture)) != null)
col.Text = s;
else
if ((object)(s = resourceManager.GetString(GetItemName(col), currentCulture)) != null)
col.Text = s;
}
}
For each control in the Controls collection (controls included in this container, that is, the form), a translation is made. First, a search is made for the resource string name including both the name of the form and the name of the control. If not found, a search is made for the control without the form prefix. For example, if the current form is AboutForm, and the current control's name is lblHeading, a search is first made for the resource string name "AboutFormlblHeading". If not found, a search is made for the resource string name "lblHeading". This way, general control names (such as Edit menu options) can be declared only one time in the resource file, but they are translated in all forms that include a control with that name. Again, this assures consistency in the translation of the application.
If the control contains other controls, that is, it is a container, the Control method will be called recursively for the control. And if the control has a connected context menu, that menu is translated by using the Menu method. Finally, if the control is a ListView, each column header is translated by using the same logic as for the controls. First, a search is made for a string name matching the form and the column (control) name. If not found, a search is made for only the column (control) name. The same approach can be used for other controls (for example, DATAGRIDS).
Now, you can see how menus are translated.
private void Menu(Menu menu)
{
string s;
foreach (MenuItem mit in menu.MenuItems)
{
// Translate with form prefix
if ((object)(s = resourceManager.GetString(form.GetType().Name +
GetItemName(mit), currentCulture)) != null)
mit.Text = s;
else
// Translate with no prefix
if ((object)(s = resourceManager.GetString(GetItemName(mit),
currentCulture)) != null)
mit.Text = s;
// Container with submenus to translate?
if (mit.MenuItems.Count > 0)
Menu(mit);
}
}
The same logic (first with form prefix and then without) is also applied to menu option translation. If the menu includes submenus, the Menu method is called recursively. In both the Control method and the Menu method, the GetItemName method is used to find the name of the current item (control or menu). This method is needed because the .NET Compact Framework does not publish the control name as a property of the control. This method is a derivation of the code written by fellow MVP Chris Tacke, which is shown on his blog and looks like the following.
private string GetItemName(object sourceItem)
{
FieldInfo[] fi = form.GetType().GetFields(
BindingFlags.NonPublic | BindingFlags.Instance |
BindingFlags.Public | BindingFlags.IgnoreCase);
foreach (FieldInfo f in fi)
{
try {
if (f.GetValue(form).Equals(sourceItem))
return f.Name;
} catch {}
}
return null;
}
Because a Name property is not available for controls and menus, this code uses reflection to get to the item's name. First, all the fields (controls and menus) for the form is retrieved in a field information collection. Each field is compared to the passed item for equality, and if true, the name of the field is returned. If the comparison raises an error, that error is ignored.
The GlobalHandler class can easily be extended to provide more globalization (and localization) functionality. The static method ShortDatePattern returns the current culture's short date pattern.
public static string ShortDatePattern
{
get { return
CultureInfo.CurrentCulture.DateTimeFormat.ShortDatePattern; }
}
This method can be used anywhere in the application to set the short date pattern. An example is in the Load event of the sample's form (PlanForm), where the DateTimePicker control's CustomFormat property is set.
dtpRequired.CustomFormat = GlobalHandler.ShortDatePattern;
If you look into the properties (and methods) of the current culture (CultureInfo), you will find many resources useful in making application fully world ready.
Before ending the GlobalHandler class walkthrough, it is only fair mentioning that because the translation is made when loading the form, this increases the wait before the form is shown. One solution to this problem could be to preload all forms at application startup and only show or hide the form when running the application. Another approach to avoid this wait and to minimize the size of the executable would be to use a tool that creates source code that compiles to executables for different languages. Such tools are available from companies like Alchemy, PASS, AIT, and Multilizer.
Form Cache (and Stack)
Each enterprise application that contains a large amount of forms requires that the forms, and the memory they consume, can be managed in an efficient way. There are several ways to implement a form cache, and one of the most important goals of the implementation in this application was to avoid form inheritance because it somewhat complicates the form design. Another goal was to allow for both caching forms (that is, loading them in and out of memory) in addition to handling the stacking of forms (that is, the order in which forms are navigated). Therefore, the FormCache class supports both the caching and stacking of forms. Briefly, the loading of a new form, or actually pushing a new form on the form stack, looks like the following.
FormCache.Instance.Push(typeof(SalesForm));
The push implicitly loads the form if it is not already loaded. And if any parameters need to be passed to the new form, the code looks like the following.
OptionsForm optionsForm = (OptionsForm)
FormCache.Instance.Load(typeof(OptionsForm));
optionsForm.DatabaseExist = databaseExist;
FormCache.Instance.Push(typeof(OptionsForm));
For more details about the management and code related to caching and stacking forms, please see the article Northwind Pocket Sales: Field Sales for Windows Mobile-based Smartphones.
Adding Data Offline
In the previous articles (Northwind Pocket Service: Field Service for Windows Mobile-based Pocket PCs and Northwind Pocket Service: Field Service for Windows Mobile-based Smartphones), the client application could only view and modify the data of the application. In this sample, the user can add new orders, and when the user taps the New button (in Figure 19), the following variables are set.
newOrder = true;
orderID = (new Guid(OpenNETCF.GuidEx.NewGuid(
).ToByteArray())).ToString();
The first variable is an indicator that this is a new order, and the second is the order identity of the new order. Note that a unique identifier (or Global Unique Identifier [GUID]) is used as the identity of the new order, and the new value is generated by the OpenNETCF.GuidEx class included in the Smart Device Framework from OpenNETCF. In fact, all keys in this sample's modified Northwind database are of type uniqueidentifier. The main reason for that is exactly this scenario. This way, a new order identity can be created offline in the Pocket PC, and when synchronized with the server, this new identity is guaranteed to be unique. The creation of a new identity offline is essential as it will also be used as a foreign key in new order detail rows. An effect of using uniqueidentifier keys in the database is that all foreign keys will be on only one field. All SQL JOINs that you want to do will always include (in the JOINs ON-condition) only one field in each table.
When the Plan wizard is finished, the code to save an order (new or existing) looks like the following.
using (SalesHandler salesHandler = new SalesHandler())
{
DataSet ds;
DataRow dr;
if(newOrder)
{
ds = salesHandler.GetEmpty();
dr = ds.Tables[0].NewRow();
dr["OrderID"] = orderID;
dr["OrderDate"] = DateTime.Today;
dr["LocalInsert"] = 1;
}
else
{
ds = salesHandler.GetForID(orderID);
dr = ds.Tables[0].Rows[0];
}
dr["CustomerID"] = customerID;
dr["ShipName"] = txtShipName.Text;
.
. (set other fields)
.
if (newOrder)
ds.Tables[0].Rows.Add(dr);
salesHandler.Save(ds);
}
If this is a new order, a DataSet with the correct structure (but without any rows) is retrieved from the salesHandler class's method GetEmpty.
public DataSet GetEmpty()
{
DataSet ds = new DataSet();
da.SelectCommand.CommandText = defaultSQL + " WHERE 'A'='B'";
da.Fill(ds, "Order");
da.SelectCommand.CommandText = defaultSQL;
return ds;
}
A private variable (defaultSQL) holds the default SELECT clause for the handler (in this case "SELECT * FROM Orders"). When this is finished, a new row is created (using the Table.NewRow method), the fields are set, the new row is added to the table, and then the DataSet is saved by using the salesHandler class's Save method.
public void Save(DataSet dataSetToSave)
{
da.Update(dataSetToSave, "Order");
}
The logic for saving order details is very similar.
using (SalesHandler salesHandler = new SalesHandler())
{
DataSet ds;
DataRow dr;
if (productID.Length < 1)
{
ds = salesHandler.GetEmptyDetail();
dr = ds.Tables[0].NewRow();
dr["OrderDetailID"] = (new Guid(OpenNETCF.GuidEx.NewGuid(
).ToByteArray())).ToString();
dr["OrderID"] = orderID;
dr["ProductID"] = lvwItems.Items[lvwItems.SelectedIndices[0]
].SubItems[3].Text;
}
else
{
ds = salesHandler.GetDetailForID(orderID, productID);
dr = ds.Tables[0].Rows[0];
}
dr["UnitPrice"] = double.Parse(txtUnitPrice.Text);
dr["Quantity"] = int.Parse(nudQuantity.Text);
dr["Discount"] = double.Parse(nudDiscount.Text) / 100;
if (productID.Length < 1)
ds.Tables[0].Rows.Add(dr);
salesHandler.SaveDetail(ds);
}
The order identity (in variable "OrderID") is the one that was created when the new order was previously saved. The salesHandler class's method to get an empty DataSet with the correct structure (GetEmptyDetail) and the method to save the DataSet (SaveDetail) are both very similar to the one shown for the previous order.
Synchronization
Now, you can see how the sales orders are synchronized. The synchronization uses a Web service that implements the synchronization logic that is called from the client with the following code.
WebServices.Sales salesWebService = new WebServices.Sales();
// Set URL of Web service
salesWebService.Url = Common.Values.RemoteWebServiceURL;
using (SalesHandler salesHandler = new SalesHandler())
{
DataSet ds = salesHandler.GetReportedSales();
// Transfer reported orders to server and get new orders back
salesWebService.Credentials = new NetworkCredential(
remoteLogin, remotePassword);
DataSet dsReturned = salesWebService.Sync(
Common.Values.EmployeeID, ds);
// Because call to server went OK, you can assume orders were // transferred
// and can therefore delete them from the local database
salesHandler.DeleteReportedSales();
// Insert new orders (and details) into local database
salesHandler.AddNewSales(dsReturned);
}
The Web service is instantiated, and the URL is set. The reported (completed) sales orders are retrieved by using the salesHandler object's GetReportedSales method and are sent to the Web service method (Sync) when the credentials are set. When the call to the Web service successfully completes, the reported (completed) sales orders are removed from the local database (DeleteReportedSales). Finally, the returned (new) sales orders are added to the local database (AddNewSales).
Here's the salesHandler.GetReportedSales method.
DataSet ds = new DataSet();
da.SelectCommand.CommandText = "SELECT * FROM Orders WHERE Status=" +
((int)Common.SalesStatus.Reported).ToString();
da.Fill(ds, "Orders");
da.SelectCommand.CommandText = "SELECT D.* FROM OrderDetails D" +
" INNER JOIN Orders O ON D.OrderID=O.OrderID WHERE O.Status=" +
((int)Common.SalesStatus.Reported).ToString();
da.Fill(ds, "OrderDetails");
da.SelectCommand.CommandText = defaultSQL;
ds.Tables["Orders"].ChildRelations.Add("Orders2Details",
ds.Tables["Orders"].Columns["OrderID"],
ds.Tables["OrderDetails"].Columns["OrderID"], true);
return ds;
Note that the relation between the Orders and OrderDetails tables are added to the DataSet, and the code for the DeleteReportedSales method is as follows.
SqlCeCommand cmd = cn.CreateCommand();
cmd.CommandText = "DELETE FROM OrderDetails WHERE OrderDetailID" +
" IN (SELECT D.OrderDetailID FROM OrderDetails D" +
" INNER JOIN Orders O ON D.OrderID=O.OrderID WHERE O.Status=" +
((int)Common.SalesStatus.Reported).ToString() + ")";
cmd.ExecuteNonQuery();
cmd.CommandText = "DELETE FROM Orders WHERE Status=" +
((int)Common.SalesStatus.Reported).ToString();
cmd.ExecuteNonQuery();
The two tables are emptied of reported sales orders, and the following is the AddNewSales implementation (dsNew is a DataSet parameter).
DataSet ds = this.GetEmpty();
foreach (DataRow dr in dsNew.Tables["Orders"].Rows)
ds.Tables["Order"].Rows.Add(dr.ItemArray);
this.Save(ds);
ds = this.GetEmptyDetail();
foreach (DataRow dr in dsNew.Tables["OrderDetails"].Rows)
ds.Tables["OrderDetail"].Rows.Add(dr.ItemArray);
this.SaveDetail(ds);
Note that the DataRow ItemArray property is used to add row data because the DataRow can belong to only one DataTable.
The code for the server Web service method looks like the following.
[WebMethod]
public DataSet Sync(string employeeID, DataSet dsClient)
{
SqlConnection cn;
SqlDataAdapter da;
SqlDataAdapter daDetail;
SqlCommandBuilder cb;
SqlCommandBuilder cbDetail;
DataSet dsServer;
DataRow drServer;
SqlCommand cmd;
int orderNo;
DataSet dsReturn;
// Get Orders and Order Details from server (with relation)
cn = new SqlConnection("data source=(local);" +
"initial catalog=NorthwindX;integrated security=SSPI;");
cn.Open();
da = new SqlDataAdapter("SELECT * FROM Orders", cn);
cb = new SqlCommandBuilder(da);
da.InsertCommand = cb.GetInsertCommand();
da.UpdateCommand = cb.GetUpdateCommand();
da.DeleteCommand = cb.GetDeleteCommand();
dsServer = new DataSet();
da.Fill(dsServer, "Orders");
daDetail = new SqlDataAdapter("SELECT * FROM OrderDetails", cn);
cbDetail = new SqlCommandBuilder(daDetail);
daDetail.InsertCommand = cbDetail.GetInsertCommand();
daDetail.UpdateCommand = cbDetail.GetUpdateCommand();
daDetail.DeleteCommand = cbDetail.GetDeleteCommand();
daDetail.Fill(dsServer, "OrderDetails");
dsServer.Tables["Orders"].ChildRelations.Add("Orders2Details",
dsServer.Tables["Orders"].Columns["OrderID"],
dsServer.Tables["OrderDetails"].Columns["OrderID"], true);
// Set up command to get new OrderNo
cmd = cn.CreateCommand();
cmd.CommandText = "spGetNewOrderNo";
foreach (DataRow drClient in dsClient.Tables["Orders"].Rows)
{
DataRow[] selectedRows;
selectedRows = dsServer.Tables["Orders"].Select("OrderID='" +
drClient["OrderID"].ToString() + "'");
if (selectedRows.Length < 1)
{
//if (drClient["LocalInsert"].ToString() == // bool.TrueString)
// If not found, add new Order to dsServer
drServer = dsServer.Tables["Orders"].NewRow();
drServer["OrderID"] = drClient["OrderID"];
orderNo = int.Parse(cmd.ExecuteScalar().ToString());
drServer["OrderNo"] = orderNo;
drServer["CustomerID"] = drClient["CustomerID"];
drServer["EmployeeID"] = drClient["EmployeeID"];
drServer["OrderDate"] = drClient["OrderDate"];
drServer["OrderedDate"] = drClient["OrderedDate"];
drServer["RequiredDate"] = drClient["RequiredDate"];
drServer["ShipVia"] = drClient["ShipVia"];
drServer["Freight"] = drClient["Freight"];
drServer["ShipName"] = drClient["ShipName"];
drServer["ShipAddress"] = drClient["ShipAddress"];
drServer["ShipCity"] = drClient["ShipCity"];
drServer["ShipRegion"] = drClient["ShipRegion"];
drServer["ShipPostalCode"] = drClient["ShipPostalCode"];
drServer["ShipCountry"] = drClient["ShipCountry"];
drServer["Status"] = drClient["Status"];
drServer["CustomerCopy"] = drClient["CustomerCopy"];
drServer["ContactName"] = drClient["ContactName"];
if (dsClient.Tables["Orders"].Columns.Contains("Signature"))
drServer["Signature"] = drClient["Signature"];
dsServer.Tables["Orders"].Rows.Add(drServer);
// Take new OrderNo and set dsClient/OrderNo
drClient["OrderNo"] = orderNo;
// Insert Order Details
foreach (DataRow dr in drClient.GetChildRows("Orders2Details"))
{
drServer = dsServer.Tables["OrderDetails"].NewRow();
drServer["OrderDetailID"] = dr["OrderDetailID"];
drServer["OrderID"] = dr["OrderID"];
drServer["ProductID"] = dr["ProductID"];
drServer["UnitPrice"] = dr["UnitPrice"];
drServer["Quantity"] = dr["Quantity"];
drServer["Discount"] = dr["Discount"];
dsServer.Tables["OrderDetails"].Rows.Add(drServer);
}
}
else
{
// If updated, update Order
drServer = selectedRows[0];
drServer["CustomerID"] = drClient["CustomerID"];
drServer["OrderDate"] = drClient["OrderDate"];
drServer["OrderedDate"] = drClient["OrderedDate"];
drServer["RequiredDate"] = drClient["RequiredDate"];
drServer["ShipVia"] = drClient["ShipVia"];
drServer["Freight"] = drClient["Freight"];
drServer["ShipName"] = drClient["ShipName"];
drServer["ShipAddress"] = drClient["ShipAddress"];
drServer["ShipCity"] = drClient["ShipCity"];
drServer["ShipRegion"] = drClient["ShipRegion"];
drServer["ShipPostalCode"] = drClient["ShipPostalCode"];
drServer["ShipCountry"] = drClient["ShipCountry"];
drServer["Status"] = drClient["Status"];
drServer["CustomerCopy"] = drClient["CustomerCopy"];
drServer["ContactName"] = drClient["ContactName"];
if (dsClient.Tables["Orders"].Columns.Contains("Signature"))
drServer["Signature"] = drClient["Signature"];
// Find and update Order Details
foreach (DataRow dr in drClient.GetChildRows("Orders2Details"))
{
selectedRows = dsServer.Tables["OrderDetails"].Select(
"OrderID='" + dr["OrderID"].ToString() +
"' AND ProductID='" + dr["ProductID"].ToString() + "'");
if (selectedRows.Length > 0)
{
// If Order Detail exist, update
drServer = selectedRows[0];
drServer["OrderDetailID"] = dr["OrderDetailID"];
drServer["UnitPrice"] = dr["UnitPrice"];
drServer["Quantity"] = dr["Quantity"];
drServer["Discount"] = dr["Discount"];
}
else
{
// If new, insert
drServer = dsServer.Tables["OrderDetails"].NewRow();
drServer["OrderDetailID"] = dr["OrderDetailID"];
drServer["OrderID"] = dr["OrderID"];
drServer["ProductID"] = dr["ProductID"];
drServer["UnitPrice"] = dr["UnitPrice"];
drServer["Quantity"] = dr["Quantity"];
drServer["Discount"] = dr["Discount"];
dsServer.Tables["OrderDetails"].Rows.Add(drServer);
}
}
// Find and delete Order Details deleted on client
selectedRows = dsServer.Tables["OrderDetails"].Select(
"OrderID='" + drClient["OrderID"].ToString() + "'");
bool rowExistOnClient;
foreach (DataRow drS in selectedRows)
{
rowExistOnClient = false;
foreach (DataRow drC in drClient.GetChildRows("Orders2Details"))
if (drS["ProductID"].ToString() == drC["ProductID"].ToString())
rowExistOnClient = true;
if (!rowExistOnClient)
drS.Delete();
}
}
}
// Update server database
da.Update(dsServer, "Orders");
daDetail.Update(dsServer, "OrderDetails");
// Use EmployeeID and status to select orders to return
da = new SqlDataAdapter("SELECT * FROM Orders" +
" WHERE EmployeeID='" + employeeID + "' AND Status=0", cn);
dsReturn = new DataSet();
da.Fill(dsReturn, "Orders");
if (!dsClient.Tables["Orders"].Columns.Contains("Signature"))
dsReturn.Tables["Orders"].Columns.Remove("Signature");
da.SelectCommand.CommandText = "SELECT D.* FROM OrderDetails D" +
" INNER JOIN Orders O ON D.OrderID=O.OrderID" +
" WHERE O.EmployeeID='" + employeeID + "' AND O.Status=0";
da.Fill(dsReturn, "OrderDetails");
// Update status flags (to avoid duplicate transfer)
cmd = cn.CreateCommand();
cmd.CommandText = "UPDATE Orders SET Status=1" +
" WHERE EmployeeID='" + employeeID + "' AND Status=0";
cmd.ExecuteNonQuery();
return dsReturn;
}
The synchronization logic on the server side is more complex than the sample in the article Northwind Pocket Service: Field Service for Windows Mobile-based Pocket PCs because the user is able to add new sales orders in the client Pocket PC application. This ability affects both the Orders and OrderDetails tables.
First, the database connection and data adapters for the two tables are set up, and then a SqlCommand object (cmd) is set up to retrieve new order numbers by means of a stored procedure (spGetNewOrderNo) that is implemented like the following.
CREATE PROCEDURE spGetNewOrderNo
AS
UPDATE Common SET LastOrderNo = LastOrderNo + 1
SELECT LastOrderNo OrderNo FROM Common
GO
It uses the Common table (and the LastOrderNo field) to update and get a new order number. Because this number is not the primary key of the Order table, new orders can be created on the client with another unique ID (of type uniqueidentifier) that is guaranteed to be unique when transferred to the server. Therefore, there is no need to get order numbers to the client in advance.
Each sales order transferred (included in the dsClient DataSet) are searched for, and if not found, a new order and order details are inserted in the server database. If the order was found, both the order and order details (products) are updated. Note that for order details, it is necessary to check for new and updated in addition to order details deleted on the client.
Finally, new sales orders (with status set to zero — that is, "Open") are filled into a DataSet that is returned to the client when the status in the server database is updated to prevent duplicate transfer.
Conclusion
We hope that this article helps you get going in defining business benefits within the areas of mobile field force automation, and more importantly, in getting the code in place. The Northwind Trader's mobile sales solution adds anywhere capabilities to their existing sales process, which leads to distinct competitive advantages.
From a code perspective, this article has shown how to implement a form stack in addition to how to make a mobile application world ready. We trust you will be able to benefit from these illustrations in your next project. Mobile sales force, anyone?