This installment of “The Baker's Dozen” presents a Windows Forms database application that demonstrates some of the primary attributes of a distributed architecture.

These attributes include authentication and connectivity, data management, business objects, user-interface modules, and reporting. The featured application is a job-costing and invoicing application for a Masonry company, and is available for download. The application contains many functions that are required in most business applications. This article steps through the construction of these key pieces and provides classes and methodologies that you can apply to your next application.

Beginning with the End in Mind

When I started working in .NET, I longed for a small but meaningful demo application that addressed the challenges and requirements that I faced as an applications developer. After I became productive in .NET (thanks too many authors and on-line community leaders), I made a promise to build a Web-based, smart-client business application to serve as a knowledge base for others facing the same learning curve I experienced.

The Common Ground Framework provides capabilities to use multiple connections/databases without changing the software or swapping configuration files.

Along the way, I also decided to build a set of reusable classes that represented the common functionality across the .NET systems I've built. In the spirit of my company name and philosophy, I'm calling this set of classes the Common Ground Framework for .NET. Thus, I've combined my own knowledge, techniques, and experiences with questions I've seen posted on technical forums to drive the content of this demo?and hopefully increase your productivity.

The demo application is based on the job-costing and invoicing needs of a typical contractor company. Many of the features I discuss apply to almost any application type. Thus some of the features may seem very familiar to you. In addition, I've also added some features specifically for demonstrative purposes.

The application utilizes concepts from previous Baker's Dozen articles (DataGrid, Crystal Reports, and Transact-SQL). Each tip focuses on a particular requirement in a distributed database application, and offers either reusable classes and/or methodologies for handling the situation. I will cover the following topics:

• An overview of the primary pieces of the architecture, and the major tasks that each piece will perform
• An authentication form class that prompts for user ID and password, database (test DB, production DB) and connection type (Web Service over HTTP, Remoting via TCP)
• Developing server-side Web service and remoting server interfaces so that client pieces can access the back-end
• Baker's Dozen Spotlight: developing client-side code to access the back-end through the Web services/remoting service interfaces referenced above
• A data access layer for executing stored procedures against a requested database
• A one-way synchronization approach for managing lookup tables, to minimize network traffic
• A basic MDI Parent form class with data-driven menus and a status bar that displays system information
• A set of subclassed Winform controls to extend base functionality
• A generic interface to bind ADO.NET data with these controls
• A generic form class with base functions to handle standard data maintenance tasks
• Examples of server-side and client-side business objects to validate data and enforce rules
• Baker's Dozen Potpourri: several advanced uses of ADO.NET functionality
• An implementation of basic audit trail functionality to track database changes, and to allow users to view the changes

With each tip, I'll discuss the areas of the architecture (Web service, client-side, and so on) that are affected.

You can find all listings in this article on the Web at www.commongroundsolutions.net. So once again borrowing from the theme of Van Amsterdam's bakery, I bring you thirteen productivity tips, in a new “Baker's Dozen.”

Tip 1: Planning the Architecture

The demo application contains six components:

• Client piece
• Web service proxy/remoting interfaces
• Web service/remoting server
• Business objects
• Data access layer
• Back-end database (SQL Server)

Table 1 lists the namespaces for the CG Framework classes. Table 2 lists the namespaces for the invoicing application that uses the framework. The download project contains a single solution (Figure 1) with 19 projects.

Figure 1: The Masonry Solution project.

The client piece contains a rich UI for retrieving and updating data, and reporting capabilities using Crystal Reports. The client communicates with the back end using either Web service references or remoting interfaces. Interfaces provide tremendous value in distributed computing by guaranteeing that back-end classes will contain specific properties and methods. The client piece contains a set of classes and interfaces to simplify back-end access.

Many functions in the Common Ground Framework are defined as virtual-allowing a developer to extend or override.

The back-end uses either Web services or a remoting server to respond to client requests. While many corporations use Web services for distributed computing, some prefer the increased performance and security of TCP remoting. The demo application supports both. The Web services/remoting server will work with business objects and then data access classes to save/retrieve data from SQL Server, and return result sets to the client. The data access class contains several reusable functions to simply common data tasks.

The application will make extensive use of datasets. Tasks such as databinding, data views, XML integration, reporting, and many other data maintenance tasks are significantly easier with datasets. However, because of the overhead associated with datasets, the application will create an XML string representation when passing dataset contents across physical boundaries/domains.

Note The code listings in this article are written in C#. In recognition that roughly half the .NET developers use VB.NET, the downloadable project on www.commongroundsolutions.net contains a separate zip file containing VB.NET code for the listings.

Tip 2: Building a Login Form

You must present users with a sign-on screen to prompt for a user ID and password. You must also allow the user to specify which database to use?a company production database, a test database, and so on. In addition, the sign-on screen must allow the user to select how they'll connect?the user may be running from a hotel room or other external location where they'll connect through Web services, or perhaps from inside corporate headquarters where the client piece will access the middle-tier and back-end via remoting.

Figure 2 displays a sign-on screen from the CGS.Winforms.FrmLogin class. The screen is a generic form that prompts for a user ID and password. The form binds these controls to the static properties found in CGS.Globals. Static properties allow a developer to set properties to specific values and read them later in the application.

Figure 2: A login form class that prompts for user ID, password, and connection.

The login form also shows a list of available connections by reading the entries in the local XML file CONNECT.XML (Listing 1). Note that the pulldown contains various options for connecting, either from outside or inside headquarters, and which database to use. The connection class in Tip 4 explains how the application uses the specific contents of this XML file to make the appropriate connection.

The Masonry application creates an inherited form from the CG base login form (Masonry.Client.Winforms, as FrmMasonrytLogin). The base form contains several virtual methods that a developer can override?the most critical is the Boolean function ValidateUserID, which returns a true or false based on the implemented authentication rule. Again, Tip 4 will cover this in detail.

Tip 3: Using Interfaces to Define the Back-End Components for Web Services and Remoting

Suppose you've already defined a back-end user business object to perform basic validation of a user ID/password, and a back-end user data object to validate against a back-end database. Now you must construct a Web service and a remoting service for remote clients to execute these functions. Finally, you must also define an interface for the client piece to access either service.

The client piece communicates with back-end functionality by means of interfaces. Interfaces guarantee that when a connection is made, classes will contain specific properties and methods.

The first step is to identify all function points where the client piece will interact (interface) with the back-end. In the case of the sign-on screen, the ValidateUserID function in the login form must call a back-end function with the same name. It will pass the User ID, password, and database key corresponding to the selected database. The back-end will return an XML string with the user's full name and application status.

Both the client and server pieces will use this interface (Listing 2). Although the client piece will not have design-time access to the actual back-end ValidateUserID function, the interface guarantees that classes implementing the interface will contain certain methods. This also allows the developer to develop for specific interfaces.

The second step is to define and develop the business object for ValidateUserID (Listing 3), which resides in Masonry.Server.BusinessObjects. Note four key items in Listing 3.

• The user business object implements the IUserObject interface.
• The user business object inherits from the CG base business object.
• The base business object in Listing 4 inherits from MarshalByRefObject, from the System namespace. Classes that support remoting must inherit from MarshalByRefObject in order to provide access across application domain boundaries.
• Finally, the business object references a data access layer that Tip 5 will cover.

After building the business object and making it “remoting-friendly,” the third step is to define both a Web service and remoting service to manage client Web/remoting requests.

You can easily create a Web service in VS.NET. Assuming you have IIS running, you can create a new project as an ASP.NET Web service, and then add as many different Web services as needed. The demo project utilizes one Web service per business object. Each Web service is named after the corresponding business object, with a ‘w’ prefix (for example, wUserObject, wInvoice, and so on). In many instances, the Web service function is a basic wrapper for the client piece to access the desired business object. Listing 5 shows the Web service for the user object. Note that the Web service implements the IUserObject interface.

The client piece will access the Web service by creating a proxy for this service, and specifying the actual URL. I'll cover this in more detail in Tip 4.

You have to work more to create a remoting server because VS.NET does not offer a project template for remoting. Listing 6 shows a basic remoting server.

Setting up a remoting server requires a few steps. Because the client piece will access the remoting server through a TCP port, you must open and register a TCP channel for that port. The example has port number 8228 hard-coded?you may wish to read that entry out of a configuration file on the server. Finally, you need to register the business object to which the client piece will remote.

The application uses a simple Windows Form application to launch the remoting server. In a real-world situation, you'll want to define this as a Windows service that automatically launches when the server boots up.

Tip 4: Baker's Dozen Spotlight: Building the Client Piece to Connect via Web Services or Remoting

The client piece must communicate with business objects through either a .NET Web service or a remoting service. I'll show you how to construct a set of classes to build and utilize these services, where the server address is not known until run time. For remoting, you'll construct a basic but functional solution for the client to invoke and execute code on a server machine.

Now that you've constructed the back-end, you need to add components to the client piece to connect to the server. Let's start with the Web service.

First, in the project where you intend to call the Web service, you must add a reference to Masonry.Interfaces. Remember that this contains the public interface IUserObject: while the client piece doesn't have the code for the user object, it knows what functions exist through the interface. This will become very important over the next several steps.

Next, you must add a Web reference to the Web service you created in Tip 3. You can right-click and select Add Web Reference**...,** and select the Web service you created earlier. Note that .NET adds the Web service with a default name of “localhost”: you'll need to change the name to “wUserObject.” (Remember that all Web services are named after their corresponding business object, plus a “w” prefix.)

Normally you only need to add the Web service reference. However, because you declared earlier that the Web service implements the IUserObject interface, you must implement that interface in the client Web reference. Why? Because .NET “drops” the interface implementation when the Web reference proxy is created; however, you can add it back in by selecting “show all files” in Solution Explorer, and navigating all the way down the Web reference until you see the file Reference.cs. You'll see the declaration of public class wUserObject that derives from the SoapHttpClientProtocol class, but does not implement IUserObject. Simply add a reference to the Masonry.Interfaces namespace, and add the reference to IUserObject at the end of the class declaration.

Important If you modify the Web service project, you must update the Web reference. Doing so will overwrite the manual change you made to Reference.cs, so you'll need to add it back in. (Some developers may choose to bypass the .NET step of adding a Web reference, and instead opt to generate the Web service proxy with their own custom tool.)

Finally, you can make the connection to the outside world to validate the user ID and password. Because the user may connect via Web services or remoting, you want to abstract out that behavior to a function that returns a generic connection object that you can use for either purpose. Listing 7 demonstrates the heart of the CG Framework: a class that returns a generic connection object. It contains the mechanics of most connection attempts that occur in the client piece. This is an example of the factory design pattern, which creates an object that subsequent classes will instantiate.

Listing 8 provides a full example that uses this connection class. These two listings (7 and 8) represent the most critical pieces of code in the client piece.

• Declare an object (oUserObject) of the interface being used for the particular module (IUserObject).
• Create an instance of the ClientRemoteAccess class (Listing 8) as oRemoteAccess. This class creates an object reference to either the Web service or the remoting server object, depending on which connection the user chose at startup.
• Set the appropriate connection properties for either the remoting interface and/or the Web service reference.
• Call the function GetAccessObject in ClientRemoteAccess. The function will create a new instance of a connection object, using the URL or TCP address that's associated with the connection that the user selected at login.
• Cast the return object to the interface. You can now use the return object to directly communicate with the back-end business objects.

Again, you can use these four steps for most connections to the server: this represents the mechanics of communicating with the back-end, and demonstrates the value of interfaces.

Tip 5: Building a Data Access Layer

The application will use stored procedures to retrieve, add, and update data. You must develop a function to build a connection associated with the requested database, and build necessary functions to execute stored procedures. This ties together tips 2, 3, and 4 to show a complete “wire-to-wire, and back” process.

The CG Framework provides a base data access class (CGS.DataAccess.cgsDataAccess) for many common database tasks. These tasks include building connection strings, executing stored procedures with parameters, managing transactions, and supporting multiple databases. As stated in Tip 1, developers can build their data access layer by inheriting from cgsDataAccess.

As a basic introduction, the ValidateUserID function in the Masonry.DataAccess.UserDA (Listing 9) provides an example of using methods that the base data access class exposes. The primary base method you'll use in ValidateUserID is SPRetrieveData. Let's take a few minutes and walk through the parameters for this method.

The first parameter (required) is the database key. The base data access class utilizes the database key (sent by the client piece) to build the appropriate connection string. It does this by reading an XML file (Database.XML, Listing 10) on the server that contains the connection information for each possible database key. As stated earlier, this functionality allows a company to add database connections without the need for a software update.

The second parameter is the name of the stored procedure, and it is also a required parameter. The third and fourth parameters are optional. The third parameter is an arraylist of the parameters to be passed to the stored procedure. The fourth parameter is the timeout factor. The developer should only pass the collection of parameters if necessary, and they only need to pass a timeout factor if the specified stored procedure is intensive and requires more time than the default command timeout provides.

While not included in the listing example, SPRetrieveData contains an additional parameter for typed datasets, so that result sets can be specifically named. By default, result sets are named “table,” “Table1,” “Table2,” etc. Developers who have constructed typed datasets and wish to use specific names for each table in the corresponding result set will find the default names for the result sets frustrating. Fortunately, the CG Framework deals with this by permitting the developer to pass a reference to the typed dataset as a parameter. SPRetrieveData will utilize the TableMappings function to name each result set based on the corresponding tablename in the typed dataset.

**Note **Developers must keep stored procedure result sets in sync with the corresponding tables in the typed dataset!

Tip 6: Handling Lookup Tables that Change Periodically or Infrequently

The application uses several lookup tables (list of items, different business codes, and so on) that change once a month, or maybe a few times a year. You would waste bandwidth by constantly querying these tables to populate picklists; however, you cannot go to the other extreme and hard-code these values in the software. I'll show you how to implement a process that will retrieve these tables at initial start-up, save them locally as XML tables, load them back into datasets at subsequent start-up, and compare datetime stamps on the server for any new insertions/modifications.

The demo application uses a picklist of possible Material Types (Slate, Fuel, Stone, Rock, etc.) for each construction job. The company might add a new type each month. To avoid retrieving the entire list every time, the system can perform a check at various times to see if any new types have been added by using a DateLastUpdated column. For instance:

• When the user loads the application for the first time, run a stored procedure to return the material type table to a client-side dataset.
• When the user closes the software, save the client-side dataset to an XML file on the user's hard disk.
• When the user re-loads the software, read the XML table back into memory, and sort on DateLastUpdated (descending order) to obtain the most recent chronological date.
• Call a stored procedure to return all rows in the Material Type table with a more recent DateLastUpdated value than the most recent DateLastUpdated on the client end.
• Use the ADO.NET Merge function to combine the new incoming rows from Step 4 into the existing in-memory table. Since some of incoming rows from Step 4 could be updates and some could be insertions, you must define primary keys for the Merge function to work correctly.

Listing 11 contains a stored procedure for steps 1 and 4. The procedure contains a parameter for DateLastUpdated. Note the parameter is initialized to NULL, and the query utilizes the T-SQL COALESCE function. This allows you to execute the procedure with no parameters and return all rows, and also execute the procedure with a date and return only those rows that have been added/modified more recently than the parameter you supply. The calls to execute the stored procedure would be as follows.

DsMaterials = new Masonry.DataSets.DsMaterials();
DsMaterials = oMaterialObject.GetData();

You can do steps 2 and 3 on the client side.

// Step 2
DsMaterials.WriteXml("Materials.xml",
   XmlWriteMode.WriteSchema);

// Step 3
DsMaterials.ReadXml("Materials.xml",
   XmlReadMode.ReadSchema);

// Create view, sort on Date desc, look at top row
DataView Dv = new DataView(DsMaterials.Tables[0]);
Dv.Sort = "DateLastUpdate DESC";
DateTime dMaxLastUpdate =
   Convert.ToDateTime(Dv[0]["DateLastUpdate"]);

After you retrieve the most recent last update in step 3, you can call the same function you called in Step 1, this time with the parameter. You can call it once at the beginning of the application, or anytime you want to check for updates to the Materials file.

DsNewData = new Masonry.DataSets.DsMaterials();
DsNewData = 
oMaterialObject.GetData(dMaxLastUpdate);

If DsNewData contains any data, the last step is to merge the new data into the existing data.

DsMaterials.Merge(DsNewData);

Tip 7: Building a Statusbar Control on the Main Screen

You want to display specific system information in a status bar at the bottom of the screen.

The CG Framework contains a base MDI parent class. A developer can inherit from his form (CGS.Winforms.Forms.cgsFrmMDIParent) to create a main MDI parent screen.

The cgsFrmMDIParent form contains a statusbar control with three pre-designed panels to display the current user, connection, and system info (Figure 3). The form contains a virtual method called SetStatusBar (Listing 12), which a developer can accept as the default, or override.

Figure 3: Status bar at the bottom of the main screen.

Tip 8: Extending Windows Forms Controls

You want to subclass the main Windows Forms controls to extend behavior and establish default appearance.

One of the first recommendations when developing with a new tool is to subclass the UI controls. Many organizations and clients have specific requirements about user interfaces, such as data entry behavior and colors. By abstracting the desired functionality and appearance into a set of subclasses, developers can deliver a solution with a consistent look and feel.

Unlike other development environments, a developer cannot subclass the Windows Forms controls visually ?you can only subclass them through code. While some initially view this as disconcerting, the reality is that one spends very little development time subclassing. Additionally, the process is a good exercise in working with the language.

The CG Framework contains a set of starter classes in the CGS.Winforms.Controls namespace, as cgsControls. Let's take a look at a subclassed version of the label control.

public class cgsLabel: System.Windows.Forms.Label
{
override public Font Font
{
   get { return base.Font; }
   set { ; }
}
public cgsLabel()
{
   base.Font = new  Font("Verdana",8);
}
}

This is a very simple example that merely sets the font to Verdana. However, it differs from the most commonly seen method for setting the default font.

// Don't use this example
public cgsLabel()
{
   this.Font = new  Font("Verdana",8);
}

This approach is problematic?not so much in concept as in practice in VS.NET. In the latter example, when you drop an instance of this label on a form, and you set other design-time properties of the label (such as caption, location, and so on), the Windows Forms designer generates code in the InitializeComponent method. This generated code includes the value for the font property.

Now suppose that your company decides to use Tahoma or a different point size. Changing the font property above will NOT change the font on all forms where the label subclass was used, because the forms use the generated code the first time any other property of the label was modified. The only workaround is to highlight all the label controls, bring up the property sheet, go to the Font control, right-click, and choose Reset. This is (in this author's opinion) a sub-standard way of addressing the situation. Hopefully future versions of Visual Studio .NET will address this behavior.

For now, you can address this by setting the base font in the constructor and blocking the Set statement. This prevents the Windows Forms designer from generating code, and ensures that the application will always derive the font from the subclass. (This will have the opposite effect?because the Set statement is blocked off, a developer cannot override the font of a label that's used on the control. If you need this capability you should modify the behavior to support this).

Tip 9: A Generic Solution for Data Binding

cgsControls also offers capabilities to simplify data binding. Some developers have difficulty implementing data binding across different controls. Data binding for a textbox differs from a combobox, and for a checkbox. Another challenge is that data binding sometimes requires a measurable amount of code.

The CG Framework simplifies this through a binding interface.

public interface IBoundControl
{
   void BindControl();
}

Once again, here is where interfaces become valuable. As I discussed in prior tips, an interface is an agreement?that any class/control that implements the interface will contain a specific set of functions. In the case above, all the subclassed controls in the cgsControls class will implement the public interface IboundControl.

public class cgsComboBox : 
System.Windows.Forms.ComboBox , IBoundControl

public class cgsTextBox : 
System.Windows.Forms.TextBox , IBoundControl

public class cgsDateTime : 
System.Windows.Forms.DateTimePicker, IBoundControl

public class cgsCheckBox : 
System.Windows.Forms.CheckBox, IBoundControl

The next step is to add some properties to each control that implements the IBoundControl interface. These properties will define the table and column to which each control will data bind. Most controls (checkbox, textbox, datetimepicker) only require two properties, one for the datatable and one for the data column.

private DataTable _DtSource;
public DataTable DtSource
{
   get {return _DtSource ;}
   set {_DtSource  = value; }
}

private DataColumn _DcSource;
public DataColumn DcSource
{
get {return _DcSource ;}
set {_DcSource  = value; }
}

The combobox, however, requires additional properties to display values from a foreign table:

• An additional data table property if the combobox displays descriptions from a foreign table (DtForeignTable, which becomes the combobox's datasource)
• A data column property for the column being displayed (DcDisplayMember, as displaymember)
• A data column property for the column from the foreign table that maps to the actual data column source (DcValueMember, as valuemember).

In many cases, DcValueMember and DcSource will be the same (e.g. a classcode in a Child table may also be called classcode in the parent description table), but that may not always be the case. The database schema may use ‘PK’ and ‘FK’ conventions, thus the support for two properties.

Next, controls that implement the IBoundControl interface must contain a function called BindControl. Here is one of the great beauties of interfaces: the BindControl function for each control can be different, based on the type of control. This is critical because each control is bound differently. The text and datetimepicker controls are bound as follows.

public void BindControl()
{
   if(this._DcSource != null &&
             this._DtSource != null)
{
      this.DataBindings.Clear();
      this.DataBindings.Add("Text",
                        this._DtSource,
                        this._DcSource);
}
}

The BindControl function for the checkbox differs because the binding applies to the checked property, not the text property. The checked property returns a Boolean that can be bound to a corresponding Boolean data type. The function for the combobox control also differs because the function must set the additional properties for the foreign table, and the display/value member.

In addition, the textbox class in cgsControls also contains code to display currency/decimal data in the correct format.

So the final question you may be asking is: Ok, so I've set binding properties… What calls the BindControl function? If all data-bound controls reside on a common entry container or form, you could, as one option, place the following code in the common form.

protected virtual void UpdateBindings()
{
   // 1) loop through each control
   // 2) if the control implements the interface,
   // 3)  call the control's BindControl function 

   foreach(Control oControl in this.Controls)
      if(oControl is cgsControls.IBoundControl)
   ((cgsControls.IBoundControl)oControl).
                         BindControl();
}

Tip 10: Integrating Filter Capabilities with Data Maintenance Functions

The application must allow users to retrieve a list of data according to a runtime criteria (jobs entered/updated between two dates, jobs for a set of customers, jobs exceeding a dollar threshold). You must develop a user-friendly interface screen for users to make filter selections and present the results in a screen that users may view, navigate through, print to Excel, etc. This tip presents a generic form class and form class manager for data maintenance functions.

The CG Framework contains a base data maintenance form named cgsFrmDataMaintenance in the cgs.Winforms namespace. You can use this form to automate many instances where users look up data by criteria on the first screen (Figure 4), view result sets on the second screen, add/edit/delete data on the third screen, and even view the history of changes to data (see Tip 13 for more on viewing audit trail data). The result set portion integrates the datagrid that I presented in the November/December 2004 issue of CoDe Magazine. I've enhanced the datagrid so that you can export the contents to Excel. The data maintenance screen utilizes the data binding in the previous tip.

Figure 4: Example of data maintenance screen.

The data maintenance form contains buttons for selection criteria, viewing result sets, and adding/editing data. A developer can design containers (user controls) to drop onto this form: the form manages the display of each container by looking for containers that implement one of three different interfaces (ICriteriaContainer, IResultContainer, and IEntryContainer). To simplify the creation of new containers to meet your data maintenance needs, the CG Framework supplies three base containers in cgs.Winforms.Containers that implement each of these interfaces (cgsContainerCriteria, cgsContainerResult, and cgsContainerEntry).

Figure 4 shows a basic example of the maintenance form. The active criteria panel displays a very simple criteria screen. The developer can design any criteria container, inheriting from cgsContainerEntry. The interface contains a function called GetCriteria, which you can override to read the criteria selections. The developer can then design a results screen that inherits from cgsContainerResult, using the function ShowCriteriaResults. Finally, the developer can design a data entry/update container that inherits from cgsContainerEntry, using the function ShowDetailData. An application could contain several maintenance forms, all using the same general mechanics and all containing the same look and feel. This type of consistency greatly helps users.

The criteria, results, and data buttons automatically look for containers on the form that implement the corresponding interfaces. Because of this, all a developer needs to do is define each of the three specific containers, drop them on an instance of a maintenance form based on cgsFrmDataMaintenance, and run the form. Once again, the power of interfaces!

Again, the base maintenance form contains stub virtual functions for each button: this allows you to implement your own code for retrieving result sets, adding data, and so on. The Word document in the zip download contains a list of all methods you can tap into.

Tip 11: Server-side and Client-side Business Objects

A development team often must decide where to implement business rules. For performance, or simply when no valid reason exists to go back to the server, some business objects may reside in the client piece. Automatic calculations during data entry represent one example.

The namespaces in Table 2 include business objects for both the client and server pieces. During the technical design phase of a project, the development team must decide where to implement business rules. Client-side business objects are a perfectly valid approach, so long as they remain separate from UI classes.

One example is a calculation of an extended amount during data entry. The user may change quantities and prices several times during data entry: a round-trip to the server to recalculate and display the total would be slow and inefficient. Therefore, developers should construct a client-side business object and call it at the appropriate times.

// Developer may chose to pass the entire datarow,
// or simply the quantity and unit price values

private decimal CalculateTotal(DataRow DrDetail)
{
   decimal nTotal = 0;

   nTotal = Convert.ToDecimal( DrDetail["Qty"])
      * Convert.ToDecimal( DrDetail["UnitPrice"]);
   
return nTotal;
}

Tip 12: Baker's Dozen Potpourri - Miscellaneous Uses of ADO.NET

Optimal use of database stored procedures should normally minimize the need for heavy processing in other layers of the application. However, there are instances where a developer needs to munge through several sets of data to produce a result. Here are a few mini-tips that demonstrate the more powerful capabilities in ADO.NET.

If you've used ADO.NET you know that it does not have the full capabilities of a SQL language. However, a combination of the more advanced capabilities of ADO.NET, along with a little “elbow grease,” can usually get the job done.

For example, suppose a user is looking at a set of orders, and wants to filter on orders where the Account name contains the word “National.” So long as a relation exists between the orders table and the account table, a developer can reference parent columns in a RowFilter as follows.

MyDataSet.Relations.Add("RelName",
DtAccounts.Columns["AcctID"],
DtOrders.Columns["AcctID"],false);

System.Text.StringBuilder sbRowFilter = new 
   System.Text.StringBuilder();

string cAcctName = "National";

sbRowFilter.AppendFormat("Parent(RelName) like 
'%{0}%'",cAcctName);

DtOrders.DefaultView.RowFilter = sbRowFilter;

While it's a simple property, some people (myself included!) get confused by the syntax for creating a primary key.

// Untyped dataset
DtMaterials.PrimaryKey = new DataColumn[] { 
   DtMaterials.Columns["PrimaryKey"]};

// Typed dataset
DtMaterials.PrimaryKey = new DataColumn[] { 
   DtMaterials.PrimaryKey};

A developer may need to create and maintain multiple views of a datatable?perhaps a different sort and rowfilter for each view.

// Multiple DataViews ? the developer can create
// two different dataviews on the same table, and
// then bind them to two different datagrids

DataView dv1 = new DataView(DtMaterials);
dv1.Sort = " amount Desc ";
dv1.RowFilter = " AccountType = 1 ";
myDataGrid1.DataSource = dv1;

DataView dv2 = new DataView(DtMaterials);
dv2.Sort = " acctname Desc ";
dv2.RowFilter = " amount > 5000 ";
myDataGrid2.DataSource = dv2;

Tip 13: Implementing Basic Audit Trail Capability into an Application

How many times have you heard someone ask, “Who changed this data, and when?” Audit trail functionality is a vital component in many database management systems. This tip introduces a generic form to show audit trail data to the user.

In my T-SQL article that appeared in the February/March issue of CoDe Magazine, I presented some update trigger code to write changes to an audit trail log. The CG Framework extends this by providing a base form class (FrmAuditTrail) for viewing audit trail changes. Figure 5 shows a basic example of audit trail history for a particular record.

Figure 5: Example of audit trail history screen.

The base data maintenance form back in Figure 4 contains an option to view the audit trail form for the current record, by performing the following.

cgsFrmAuditTrail oAuditTrailForm = new 
   cgsFrmAuditTrail();
oAuditTrailForm.cTableName = 
   this.GetPrimaryTable();
oAuditTrailForm.nPrimaryKey = 
   this.GetCurrentPrimaryKey();

oAuditTrailForm.ShowAuditTrail();

The download project contains a script to generate the entire SQL database. The database includes the necessary tables for the audit trail classes. This includes an audit trail table (AuditTrail), a user table (UserName), a data dictionary to store English-like names for each column (MasonryDataDictionary), and update trigger code to insert rows into AuditTrail.

Closing Thoughts

You can pull the entire project, as well as the script for generating the database, from www.commongroundsolutions.net. The zip file contains a Word document with release notes on all functionality in the CG Framework.

I hope this article provides some tips for developers who are learning .NET. There are many things in the framework that are not covered in this article, such as incorporating Crystal Reports, as well as some of the other capabilities in the base data access class. The release notes (Common Ground Framework.DOC) lists all the capabilities in the framework.

There are also many opportunities to improve the framework, such as encrypting the XML tables, retaining the selected connection at sign-on, and even adding some intelligence to the sign-on process to automatically determine which connection to use, based on the user's current IP address.

If you have suggestions or ideas you'd like to share, I'd love to hear from you. Feel welcome to contact me.

<Connect>
    <ConnectionInfo>
       <Description>Masonry Production (Outside HQ)</Description>
       <DBKey>1</DBKey>
       <ConnectionType>1</ConnectionType>
       <ServerAddr>http://localhost/MasonryWebService</ServerAddr>
       <PortNumber>0</PortNumber>
       </ConnectionInfo>

    <ConnectionInfo>
       <Description>Masonry Production (HQ)</Description>
       <DBKey>1</DBKey>
       <ConnectionType>2</ConnectionType>
       <ServAddr>tcp://localhost</ServAddr>
       <PortNumber>8228</PortNumber>
    </ConnectionInfo>

    <ConnectionInfo>
       <Description>Masonry Test (inside HQ only)</Description>
       <DBKey>2</DBKey>
       <ConnectionType>2</ConnectionType>
       <ServAddr>tcp://localhost</ServAddr>
       <PortNumber>8228</PortNumber>
     </ConnectionInfo>
 </Connect>

// IMasonryInterfaces.CS
// Defines interfaces for all business objects
// that will be externally accessed

using System;
using System.Data;

namespace Masonry.Interfaces
{
   public interface IUserObject  
{   
// returns empty Dataset if user not found
// 2 columns, UserName and Status
// Status of 'Active', 'Inactive', or 'Suspended'

string ValidateUser(string cUserID, 
                     string cPassword,
                     int nDatabaseKey);
   }
}

using System;
using System.Data;
using Masonry.DataAccess;

namespace Masonry.Server.BusinessObjects
{
   public class UserObject :  CGS.Business.cgsBaseBusinessObject, 
                        Masonry.Interfaces.IUserObject

public string ValidateUser (string cUserID, 
                             string cPassword, 
                             int nDatabaseKey)
{
      cgsUserDataAccess oUserDataAccess = 
                             new cgsUserDataAccess();
      DataSet DsReturn =  
             oUserDataAccess.ValidateUserID (cUserID, 
                                             cPassword, 
                                             nDatabaseKey);

      return DsReturn.GetXml();
}
}

using System;

namespace CGS.Business
{
public class cgsBaseBusinessObject : 
                              System.MarshalByRefObject
// Note that any classes used for remoting must inherit
// System.MarshalByRefObject
{
     public cgsBaseBusinessObject()
     {

     }
}

// <a href="http://wUserObject.asmx.cs">wUserObject.asmx.cs</a>
// This calls the ValidateUser method.  Note that this 
// implements the IUserObject interface

using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Diagnostics;
using System.Web;
using System.Web.Services;
using Masonry.Interfaces;
using Masonry.Server.BusinessObjects;

namespace Masonry.Webservice
{
   public class wUserObject : System.Web.Services.WebService, 
                  IUserObject
   public wUserObject()
   {
   }

   [WebMethod]      
   public string ValidateUser (string cUserID, string 
                  cPassword, int nDatabaseKey)
   {
            
      UserObject oUserObject = new UserObject();
      return oUserObject.ValidateUser(cUserID,
                               cPassword, nDatabaseKey);
}
}

using System.Runtime ;
using System.Runtime.Remoting;
using System.Runtime.Remoting.Channels;
using System.Runtime.Remoting.Channels.Tcp;

int nTCPPort = 8228;

TcpServerChannel Tcps = new TcpServerChannel(nTCPPort);
ChannelServices.RegisterChannel(Tcps);

RemotingConfiguration.RegisterWellKnownServiceType
(typeof(Masonry.Server.BusinessObjects.UserObject),
"UserObject" , WellKnownObjectMode.Singleton);

public object GetAccessObject()
{   
   Type oInterface = this.tInterface;
   string cServiceName = this.cServiceName;
   SoapHttpClientProtocol ows = this.wService;

   object oAccessObject = new object();
switch((ConnectionTypeOptions)this.nConnectionType)
   {
      case ConnectionTypeOptions.TcpRemoting:
         IChannel[] myIChannelArray = 
              ChannelServices.RegisteredChannels;
         // Only need to register channel if none are 
         // registered
         if(myIChannelArray.Length ==0)
            ChannelServices.RegisterChannel( new 
                                   TcpClientChannel());
            oAccessObject = 
               Activator.GetObject( oInterface,
                   this.cTcpServer.ToString().Trim() + ":" +
                   this.nTCPPort.ToString().Trim()  + "/" +
                   cServiceName  ) ;
        // URL could be "tcp://localhost:8228/UserObject"
         break;
      case ConnectionTypeOptions.WebServices:
         ows.Url = this.cWebServiceURL.ToString() + "/w" + 
                                    cServiceName + ".asmx";
         oAccessObject = ows;
         break;
      }
      // Return connection object, which can be casted to an 
      // interface
      return oAccessObject;
}

// Steps to define and use a remote object
// Required namespaces
using Masonry.Interfaces;
using CGS.RemoteAccess;

// First, declare an object based on the remote interface
IUserObject oUserObject;

// Second, get the remote access object
ClientRemoteAccess oRemoteAccess = new ClientRemoteAccess();

// Third, set connection properties
oRemoteAccess.tInterface = typeof(IUserObject);
oRemoteAccess.cServiceName = "UserObject";

//   only need to create WS object if WS was selected at start-up
if(oRemoteAccess.UsingWebServices())
   oRemoteAccess.wService = new wUserObject.wUserObject();

// Fourth, now we can call the remote access object
oUserObject = (IUserObject)oRemoteAccess.GetAccessObject();

// Now, we can call ValidateUser either as a web method 
// in our web service, or as a back-end business object via 
// remoting.  Remember that it returns an XML string

string cXMLResultSet = 
oUserObject.ValidateUser(oGlobals.cUserID, 
oGlobals.cUserPassword, oGlobals.nDataBaseKey);

// Since we pass XML strings across physical boundaries, 
// we must read the XML string back into a dataset

DataSet DsResults = new DataSet();
StringReader sReader = new StringReader(cXMLResultSet);
DsResults.ReadXml(sReader, XmlReadMode.InferSchema);

using System;
using System.Data;
using System.Data.SqlClient;
using System.ComponentModel;
using System.Collections;

namespace Masonry.DataAccess

public class UserDataAccess : CGS.DataAccess.cgsDataAccess

public DataSet ValidateUserID(string cUserID, string cUserPassword,
                        int nDatabaseKey)
{
// example of specifying alternate timeout
int nTimeOut = 100;    

// Create arraylist of parameters
ArrayList aSqlParms = new ArrayList();  
aSqlParms.Add(new SqlParameter("@cUserID",cUserID));
aSqlParms.Add(new SqlParameter("@cPassword",cUserPassword));

// Call the stored proc ? will build connection 
// based on database key, set new timeout,
// execute stored proc with parm, and return result set

DataSet DsReturn = this.SPRetrieveData( 
        "cgsValidateUserID",nDatabaseKey,aSqlParms,100 );

return DsReturn;
}

<DataBase>
   <ConnectionInfo>
      <DBKey>1</DBKey>
      <UserID>sa</UserID>
      <password></password>
      <DataSource>MyDatabase</DataSource>
      <InitialCatalog>MasonryProd</InitialCatalog>
      <Connectiontimeout>230</Connectiontimeout>
   </ConnectionInfo>

   <ConnectionInfo>
      <DBKey>2</DBKey>
      <UserID>sa</UserID>
      <password></password>
      <DataSource>MyDatabase</DataSource>
      <InitialCatalog>MasonryTest</InitialCatalog>
      <Connectiontimeout>230</Connectiontimeout>
   </ConnectionInfo>
</DataBase>

CREATE PROCEDURE dbo.GetMaterials 
(@dDateLastUpdated DateTime = null)
AS
SELECT * FROM Material
     WHERE COALESCE(@dDateLastUpdated,DateLastUpdated-1) <
                  DateLastUpdated
GO

protected virtual void SetStatusBar()
{
   this.statusBar.Panels[0].Text ="User" + cgsGlobals.cUserName;
   this.statusBar.Panels[1].Text = "Connection: " + 
      oGlobals.cCurrentConnection;
   ManagementObject disk = new 
      ManagementObject("win32_logicaldisk.deviceid='c:'");
disk.Get();
long availFree = long.Parse(disk["Freespace"].ToString());
string cMessage = "";
if(availFree > 1000000000)
{
   decimal nGB = 
System.Math.Round((decimal)(availFree / 1000000000.00),2);
   cMessage = nGB.ToString().Trim() + " GB";
}
// Add code to express in Megabytes, if available space
// is less than 1 GB
this.statusBar.Panels[2].Text="Free disk space: " + cMessage;
}