Chapter 4: Designing ADO .NET Applications

Form Data Binding

Providers and consumers of data are required to allow form data binding. It is simpler to look at Windows Forms data binding from the provider perspective. Data binding is versatile in that you can bind to almost any structure that contains data. This can be an array that implements the IList interface, a collection, or one of the data structures from ADO .NET. In this section, I will only concentrate on binding with ADO .NET data structures.

You can bind the control to the following ADO .NET data objects:

• DataColumn object: This is the building block of a DataTable object. It represents a column in a database table. You can bind a simple control, such as a TextBox control’s Text Property, to a column within the data table.

• DataTable object: This represents one table of in-memory data in ADO .NET. It can be a one-to-one matching to a database table, or it can be a virtual table derived from the result of a retrieve operation on the database. It contains rows and columns that are represented by two collections, the DataColumn and the DataRow. You can bind a complex control, such as a DataGrid control, to a DataTable. However, when you bind to a DataTable, you are really binding to the DataTable’s default DataView.

• DataView object: This object is a customized view of a single DataTable that may be filtered or sorted. Just like DataTable, you can bind DataView to complex controls, but be aware that you are binding to a fixed snapshot of the data rather than an updating data source.

• DataSet object: This is a collection of tables, relationships, and constraints of the data in a database. If you bind to a DataSet, you are actually binding to its default DataViewManager.

• DataViewManager object: This represents a customized view of the entire DataSet and is similar to a DataView but with relations included.

  Note

  Simple controls consist mainly of controls that display or hold one element of information. These include controls like text boxes, radio buttons, and check boxes. Complex controls hold a set of elements of information and, at times, even the relationship between the elements. These include grid controls, list boxes, and many other OLE controls.

A CurrencyManager object is associated with any Windows Form that you bind to data source. It is the job of the CurrencyManager object to keep track of the position in the data source (for example, what row is current) and manage the bindings to the data source. In addition, every Windows Form has a BindingContext object. There is a CurrencyManager for each discrete data source that you bind to per BindingContext object. The BindingContext object keeps track of all the CurrencyManager objects on the form. So, any Windows Forms with data-bound controls will have at least one BindingContext object. You can also create a BindingContext object for a container control, such as GroupBox, Panel, or TabControl, that contains data-bound controls. This allows each part of your form to be managed by its own CurrencyManager object. Figure 4-1 shows the data binding architecture of Windows Forms.

Figure 4-1: Windows Forms data binding architecture

Common Scenarios for Data Binding

If you take a look at all Windows applications today, you will find that nearly all commercial applications use information read from data sources of one sort or another. Most of those use some kind of data binding technology to display and manipulate the data source. Below are a few of the most common scenarios that use data binding as a method of data presentation and manipulation:

• Reporting: Reports provide a flexible way to display and summarize data in printed documents or on screen. Common reports include lists, invoices, summaries, and even cross tabs. The data is formatted to facilitate reading rather than data entry. For example, you would format the date to display in long date format rather than short date format if space is available.

• Data entry: A data entry form is a common way to enter a large amount of related data. Users can enter information directly or select choices using text boxes, option buttons, drop-down lists, and check boxes. The database is updated with the new or modified data.

• Parent/child relationship: A parent/child relationship is one format for looking at related data. Typically, there are two tables of data with a relation connecting them (for example, invoice headers and invoice details tables). The relationship is usually one-to-many.

• Lookup table: Another common scenario is the table lookup. This is usually a way of finding more details about a row of data. For example, the form will display a list of products sold by a company, but the actual data saved is the primary key of the products table. Since the primary key is just a number and meaningless to a human operator, the name of the item is shown instead.

Data Access Strategy for Windows Forms Applications

There is no correct strategy for accessing data in Windows Forms. Since Windows Forms are typically thick clients and most of the resources consumed are on the client machine, your choice would be mainly in relation to what you expect the client machine to be able to handle. There a few points you might want to keep in mind:

• DataSets: This component allows you to maintain complex relationships and referential integrity, simplifying data manipulation.

• Data binding: You can bind data sources to controls in the development environment instead of in the code, simplifying development.

• Data commands: You cannot bind to data commands, but some operations that modify database structure can only be done through data commands.

• Stored procedures: Creating stored procedures in a database is more efficient than using direct SQL commands to manipulate the data because the stored procedures are compiled.

There is no strategy that fits all situations. Keep in mind the different points mentioned above, and develop your own strategy according to your requirements. You will find that you develop the right strategy as you gain more experience.

Data Access Strategy for Console Applications

Even though console applications do not have a GUI interface, you might still need to access and process data. You might build a console application that does extensive data processing. You can build an application that carries out data maintenance and administrative tasks such as creating users in the database, or simply allows legacy hardware to access your program.

The data access requirements of a console application are no different from a Windows Forms application. Data is accessed and processed in the same way. The only thing you cannot do is bind the data to visual components. You will have to write logic to display the data to the user, if required.

Data Access Strategy for Windows Services

Since there is no direct user interaction, there is no need for data binding in a Windows Service. A Windows Service is typically always running and might service multiple users or multiple processes for the same user. This property puts some unique requirements on Windows Services when it comes to data access.

Since the Windows Service is always running, you might consider not having a permanent connection to the data source, but instead connect to the data source when it is needed. This has the disadvantage of overhead when connecting but frees server resources when the Windows Service is idle. It is also a good idea to check if the connection is live each time you start a data access cycle. Another disadvantage of this strategy is that you can get a lot of connection and disconnection cycles, which is not very efficient.

An improved method is to use a timeout strategy. This means that the connection to the data source disconnects after a set time of being idle. You can then check if a connection is live and only reestablish the connection if it has timed out.

We have so far looked at three types of application architectures, all with similar requirements. Next, we will look at ASP .NET applications, which require more planning and careful attention when it comes to data access strategy.

Web Forms

Web Forms are used to create programmable web pages that provide the user interface for web applications. A Web Form page presents information to the user in any browser or client device and implements application logic using server-side code.

Previously in ASP, there was no clear separation between code and visual components of the user interface. All code and scripts were included in *.asp files. In ASP, you can use COM to separate business logic, but you still have to have codes that manipulate the visual component in the same file as the component. With ASP .NET, this has now changed. User interface programming of Web Forms is divided into two distinct parts: the visual component in *.aspx files and the logic in *.aspx.vb or *.aspx.cs files.

Data Access in Web Forms

The nature of web programming itself is such that data access in Web Forms differs in several ways from data access in Windows Forms or in older forms technology. You must consider issues such as state management, separation of server and client, designing for scalability, and so on. In addition, because you will be working with databases, you must also understand the important points of how to manage data in Web Forms.

There are a few fundamental principles that you need to bear in mind when accessing data in Web Forms:

• Using a disconnected model

• Reading data more often than updating it

• Minimizing server resource requirements

• Accessing data using remote processes (distributing data access)

Data to XML Web Services

An XML Web Service is a programmable entity that provides a particular set of services or functionality, such as application logic or data access control. It is accessible to any number of systems using ubiquitous Internet standards, such as XML and HTTP. The methods of communication used by XML Web Services are XML-based messaging. This helps bridge the difference that exists between systems that use incongruent component models, operating systems, and programming languages.

As it is designed to work in the heterogeneity of the web environment, XML Web Services must have the following properties:

• They must be loosely coupled: Loosely coupled systems have only the requirement of understanding self-describing, text-based messages to be able to communicate between each other.

• They must use ubiquitous communication: The Internet communication capability is now a standard requirement for new operating systems, at least in the near future, thus providing an omnipresent communication channel. The ability to connect almost any system or device to the Internet will ensure such systems and devices are universally available to any other system or device connected to the Internet.

• They must use universal data format: Any system supporting the same widely accepted open standards is capable of understanding XML Web Services. By using XML, communication between autonomous and disparate systems is now a possibility.

The DataSet was engineered in such a way to provide convenient transport of data over the Internet. The DataSet and DataTable can be specified as an input or an output of XML Web Services without any additional coding required to stream the contents of the DataSet between the XML Web Services and the client. The DataSet is implicitly converted to an XML stream on the sending end, sent over the network, and reconstructed from the XML stream to a DataSet on the receiving end. This provides a simple way for XML Web Services to exchange data with its clients. Figure 4-2 shows the XML Web Services communication cycle.

Figure 4-2: XML Web Service communication cycle

The DataSet was architected with a disconnected design, in part to facilitate the convenient transport of data over the Internet. The DataSet and DataTable are “serializeable” in that they can be specified as an input to or output from XML Web Services without any additional coding required to stream the contents of the DataSet from an XML Web Service to a client and back. The DataSet is implicitly converted to an XML stream using the DiffGram format, sent over the network, and reconstructed from the XML stream as a DataSet on the receiving end. This gives you a very simple and flexible method for transmitting and returning relational data using XML Web Services.

The code sample below shows a simple use of the DataSet in an XML Web Service.

The first thing you need to do is create the XML Web Service. We will first create a Class called Service1 that will manipulate employee data in the Northwind database. Once you have generated a template for your services, you will add the following code to the top of the  Service1.asmx.vb file:

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.Services

<WebService(Namespace:="http://localhost/CHO7- _
  Sample-01")> Public Class Service1
  Inherits System.Web.Services.WebService

You will place the rest of the code after the Web Services designer generated code, still in  Service1.asmx.vb file:

    'WEB SERVICE SAMPLE

    'Create SQL Connection to database
    Public nwindConn As SqlConnection =
      New SqlConnection ("Data Source=localhost; _
        Integrated Security=SSPI;
        Initial Catalog=northwind")

    'Describe public function for getting employee data
    <WebMethod(Description:="Returns Northwind
    employee", EnableSession:=False)> _
      Public Function GetEmployee() As DataSet

        'Declare and initialize SQL DataAdapter
        Dim employeeDA As SqlDataAdapter = 
          New SqlDataAdapter ("SELECT EmployeeID,
          LastName, FirstName, Title FROM Employees",
          nwindConn)

        'Declare and initialize DataSet
        Dim employeeDS As DataSet = New DataSet()

        'Determine action to take if column name does not
        'match
        employeeDA.MissingSchemaAction = _
          MissingSchemaAction.AddWithKey
        employeeDA.Fill(employeeDS, "Employees")

        GetEmployee = employeeDS

    End Function

    'Public function for modifying Employee table
    <WebMethod(Description:="Updates Northwind
      Customers", EnableSession:=False)>
      Public Function UpdateEmployee(ByVal employeeDS As
      DataSet) As DataSet

        'Create SQL DataAdapter
        Dim employeeDA As SqlDataAdapter = New
          SqlDataAdapter()

        'Define the insert command
        employeeDA.InsertCommand = New SqlCommand
          ("INSERT INTO Employees (EmployeeID, LastName,
          FirstName)" & "Values(@EmployeeID, @LastName,
          @FirstName)", nwindConn)

        employeeDA.InsertCommand.Parameters.Add _
       ("@EmployeeID", SqlDbType.NChar, 5, "EmployeeID")

        employeeDA.InsertCommand.Parameters.Add _
        ("@LastName", SqlDbType.NChar, 15, "LastName")

        employeeDA.InsertCommand.Parameters.Add _
        ("@FirstName", SqlDbType.NChar, 15, "FirstName")

        'Define the update command
        employeeDA.UpdateCommand = New SqlCommand _
       ("UPDATE Employees Set LastName = @LastName, " & _
        "FirstName = @FirstName WHERE EmployeeID = _
        @EmployeeID", nwindConn)

        employeeDA.UpdateCommand.Parameters.Add _
       ("@LastName", SqlDbType.NChar, 15, "LastName")

        employeeDA.UpdateCommand.Parameters.Add _
       ("@FirstName", SqlDbType.NChar, 15, "FirstName")

        'Define the where clause parameter as that of
        'the original employee ID
        Dim myParm As SqlParameter = _
        employeeDA.UpdateCommand.Parameters.Add _
       ("@EmployeeID", SqlDbType.NChar, 5, "EmployeeID")

        myParm.SourceVersion = DataRowVersion.Original

        'Define the Delete command
        employeeDA.DeleteCommand = New SqlCommand _
       ("DELETE FROM Employees WHERE EmployeeID = _
        @EmployeeID", nwindConn)

        'Define the where clause parameter as that
        'of the original employee ID
        myParm = employeeDA.DeleteCommand.Parameters.Add _
       ("@EmployeeID", SqlDbType.NChar, 5, "EmployeeID")

        myParm.SourceVersion = DataRowVersion.Original

        employeeDA.Update(employeeDS, "Employees")

        UpdateEmployee = employeeDS

    End Function

End Class

After you have created the XML Web Service, you can test it by running the debugger in Visual Studio .NET. This should open a web page with the hyperlinks for the two methods you have just created: GetEmployee and UpdateEmployee. If you click on GetEmployee, a new web page opens containing a button called Invoke. If you click on Invoke, you get the XML result of running the GetEmployee method. There is no such button for the UpdateEmployee method. This is because it requires parameters, and to test it, you will need to define debug data for the method.

Once you have created the required web reference in your client application, you can access the methods as if it were any other local object. In the listing below, a web reference has been defined to the XML Web Service we created above; it is called ServiceSample.

        'Define Object from XML WebService
        Dim ServiceClient As New ServiceSample.Service1()

        'Get the DataSet using the GetEmployee method
        Dim myDS As DataSet = ServiceClient.GetEmployee

        'Get table
        Dim myTable As DataTable = myDS.Tables("Employee")

As you can see, once you have created the web reference, it is a simple matter of creating an object based on the class in the XML Web Service. Communication and getting results from methods of the object are handled transparently, just as if the object were local.

Data Access Strategy for ASP .NET Applications

When you design your web applications, you will need to decide what data access strategy you wish to adopt. There is no right strategy; each one has its own advantages and disadvantages that you must first consider. You will have to make a choice as to which strategy you adopt, depending on your particular requirements.

Private Sub Page_Load(ByVal sender As System.Object, _
                      ByVal e As System.EventArgs) _
                      Handles MyBase.Load
   ' Check to see if the page is loaded again
   If Page.IsPostBack Then
      ' Cast object as dsEmployees from session
      ' and assign to variable
      dsEmployeeLD = CType(Session("myDsEmployees"), _
      dsEmployees)
   Else
      ' If this is first time page is loaded check
      ' session variable
      If Session("myDsEmployees ") Is Nothing Then
         ' Variable does not exist, create it
         ' and set its value
         OleDbDataAdapter1.Fill(dsEmployeeLD)
         Session("myDsEmployees ") = dsEmployeeLD
      End If
   End If
End Sub