Most of the applications we software developers build need to interact somehow with data from a database. The .NET Framework defined by Microsoft provides a rich set of objects to manage database interaction; these classes are collectively referred to as ADO.NET and the latest versions of DataWindow .NET (which is now version 2.01).

SQL Anywhere (version 10) fits perfectly in this environment, making development a lot easier; in addition, your applications will look good without spending a lot of time painting the user interface. In this article we'll look closer at exactly this kind of combination: DataWindow.NET with SQL Anywhere.

Our Application: Pamalot
This project started about a year and a half ago. A nice guy with a lot of enthusiasm entered my office and started to tell me a story about a business he started a few months before. It was a franchising system, where one can buy vending machines (a minimum of 15) from a franchise master and become a franchise partner. The machines are put in different locations and filled with sweets or nuts. A customer puts in a coin, takes a cup and chooses pistachios, corn, chocolate drops, or sweet nuts. The franchise partner passes by every week, refills or replaces the food in the machines, counts the money and gives the guy who owns the place where the machine is located a commission. To make a long story short: This nice guy was the one running this business for the EMEA region and he needed an application that could handle it easily. He had an application that was written in Spanish and used MS Access as the underlying database. As you might guess it wasn't useful at all so we started to look at the requirements by talking with franchise partners about how they run their business and what their needs are.

After about two weeks, when we had a proper picture of the underlying business (and its problems like the counting mechanism sometimes forgetting to count a coin or counting it twice, or customers complaining about the food and the owner of the place giving them their money back) we started to convert and extend the original underlying database from MS Access to SQL Anywhere, which went pretty smoothly using PowerDesigner. (See www.ianywhere.com/developer/sql_anywhere_developer_corner/migrate_access.html for a detailed description of the process.) We also decided to build a WinForms application using Visual Studio 2003 (the most recent version at the time) and DataWindow.NET on top of the new SQL Anywhere database.

The Existing WinForm Application
We initially wrote the application PAMALOT as a WinForm application. These were the requirements:

• It should take two different roles, the franchise master and the franchise partner (a difference is that a franchise partner orders the goods from the dedicated franchise master).
• Details of every machine should be entered (serial number, color, key numbers for the goods and the money lock, size of the doses).
• The machine might move over time.
• The place where the machine is moved to is owned by a partner so the partner details have to be entered.
• The machines need service to refill the goods and take the money out.
• The partners get a commission, so after a few services a commission report has to be sent to the current partner/location.
• Display the current stock (goods, machines, spare parts).
• New goods need to be ordered before the stock is empty. The system should be able to suggest the amount and the product to order.
• A lot of reports should be available to show the success of the business-particularly a report on every machine.
• It has to be multilingual since it will be installed in different countries.

The New Requirements
When we finished the WinForm application and installed it in Germany, Austria, Hungary, and the Czech Republic, a new idea came up. Since everyone is going to the Web, we were asked to build a WebForm application besides the existing WinForm application. This made sense because nearly everyone now has fast and reliable Web access. When a franchisee is doing a maintenance tour on the machine it's common that there's a Web terminal available, and all the information from the machines can be entered into the system immediately. Later on, the only administrative work left to do is print the generated commission reports or orders for the food and cups. Anyway, I'm sure you'll agree that bringing the application to the Web is a good idea. But how to implement it, which tools should be used, and what should we convert first?

From a technical point-of-view, the logical choice was to use DataWindow.NET and ASP.NET, which were used for display and data entry in the WebForm application.

Moving Data Entry to the Web
The first thing we had to do was bring data capturing to the Web, when one is doing maintenance on a customer site. As almost everybody has an Internet connection, it's easier than filling in some paper form, returning home, and re-entering the data on the WinForm application.

Another requirement was to have a PocketPC application do the same, but we'll discuss this elsewhere.

SQL Anywhere 10
To be honest, I've had a love affair with SQL Anywhere for years. Why? Because it's a full-featured database (tables, PKs, FKs, indexes, triggers, stored procedures, users, groups), but it requires zero administration. It can handle a lot of data with ease (I've seen databases worth several gig) and on the other side one could copy the file to a PocketPC to carry around or run from a CD. The newest version, SQL Anywhere 10, offers a lot of new features like materialized views and enhanced .NET support. It also includes DataWindow .NET.

What Is DataWindow .NET
DataWindow .NET is a component that boosts the performance of .NET application development environments. It was developed by Sybase more than 10 years ago. It initially comes from Sybase PowerBuilder, a 4GL RAD development tool. Based on technology so powerful there are several patents, DataWindow .NET helps you rapidly build and deploy data-driven applications, easily incorporating complex business rules, and delivering sophisticated data presentation. With hundreds of built-in functions, properties, and declarative programming, developers experience high levels of productivity in a virtually code-free tool, which we'll see later in an example we'll create together. There are different presentation styles available as you can see in the table below. (Table 1)

DataWindow .NET isn't limited to WinForm applications - it can also be used for building nice-looking WebForm applications.

Dataset Binding
In PowerBuilder, the DataWindow is used to display data to users and insert new data and update data into the database, since the DataWindow can handle the creation of the SQL statements needed on its own (including extensive programming support that can be used to react to errors or override default behavior).

All these capabilities are included in DataWindow.NET. DataWindow.NET 2.0 also supports using DataSets (which represent an in-memory cache of your data from the database) as the data source for a DataWindow object (the visual presentation from the database) since many .NET developers prefer to separate data access from data presentation (the Model-View-Controller pattern) and want to use the DataWindow only for its presentation capabilities. The data-binding model lets you do this. If you prefer to combine data access and presentation, you can use the retrieve and update model. The great advantage over built-in Visual Studio components is that a person designing the visual presentation can do this without knowing a programming language - everything can be done in the DataWindow designer. Later on the programmer takes the DataWindow control created and combines its business logic with the visual presentation (the DataWindow object) through the DataWindow control, which helps him by exposing a lot of events and additional properties.