Chapter 4: Designing ADO .NET Applications

 Download CD Content

.NET Application Models

The .NET Framework supports a variety of application architectures. In this chapter, we examine where and how to use ADO .NET in the different application architectures of the .NET Framework. First, though, let’s have a look at the different architectures that you can use in .NET.

There are mainly four kinds of applications that you can build:

  • Windows Forms applications

  • Console applications

  • Windows Services applications

  • ASP .NET web applications

Windows Forms Applications

This is the classic Windows application. The user interfaces are done through Windows Forms and Windows Form Controls, which are fully object oriented. In nearly all of the cases, these types of applications involve some sort of data modification in the application, and the modified data is then stored in a data source. The data source can be a database or a file, as is the case with a word processor.

Database client-server applications are also part of this architecture. The application is usually installed on the client’s machine, and connections are made directly to the databases using ADO .NET. With Windows Forms, you typically bind sources to a Windows Forms Control. The control then becomes the interface through which you view and modify the data.

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.

Note 

The IList interface represents a collection of objects that can be individually accessed by index.

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.

click to expand
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.

Console Applications

Console applications are non-GUI, text-based interfaced applications. Console applications are dated architecture but are still used for some applications. They are very useful when the communication line is slow and processing power is limited. Console applications are widely used for remote administration.

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.

Windows Services Applications

Windows Services replaces what was formerly NT Services. A Windows Service is an application or a module that runs on a server and provides services to other applications and modules. A server in this context means the provider of the service. This can include NT Workstation and Windows 2000 Professional, which is not considered a server in the traditional sense. A service has no other user interface. If one is required, a separate module must be written that controls the behavior of the service.

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.

ASP .NET Web Applications

ASP .NET is not only the next version of Active Server Pages (ASP), but it also provides a unified web development platform for the development of enterprise-class web applications. Although ASP .NET syntax is largely compatible with ASP, it also provides new enhanced features for robust and scalable web applications.

ASP .NET is a compiled, .NET-based environment; you can author applications in any .NET-compatible web language. For now, the languages available are C#, J#, and VB .NET. As ASP .NET is one of the core .NET class libraries, the entire .NET Framework is available to any ASP .NET application, including ADO .NET. This adds the benefits of these technologies to web development, which includes the managed Common Language Runtime environment, type safety, inheritance, object-oriented design, and compiled (instead of interpreted) applications.

ASP .NET makes extensive use of Web Forms and XML Web Services. XML Web Services are used to build Business to Business (B2B) and Business to Client (B2C) applications. You can consider Web Forms to be the presentation tier and Web Services to be the middle tier or business tier of a distributed application.

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.

Note 

In this context, Web Form page means the web page that is sent to the browser and is generated by the ASP .NET compatible web server from a Web Form.

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)

Disconnected Model

Web Forms are disconnected. With each request a client makes to the server, the page is built, processed, sent to the client, and discarded from the server memory. As a result, the data on the server are discarded from the server memory along with other elements of the Web Form page.

Data you are working with are not automatically available with each round-trip to the server. If you want to access the data, you must reload it from the source or you must include logic to save and restore the data as part of the page processing. This makes it impractical to maintain database connection. Instead, for each round-trip cycle you need to connect, process data (read or write), and then disconnect from the database.

Reading and Updating

The Web Forms model presumes that most data accessed by pages are read-only. This means that there are more read operations than write operations being performed on the data source. As a result, the Web Forms data binding architecture is one-way. The data binding only displays data in controls but does not write from controls to the data source.

The one-way architecture makes the page more efficient as it removes the bigger overhead that updating requires. If you have a page that requires updating the data source, you must explicitly write code to perform the update operations yourself.

Minimizing Server Resource Requirements

Since the Web Forms pages are processed on the server before they are sent to the browser, any data access adds additional load to the server resources, both in terms of processing time and memory usage.

If you choose to keep the data on the server between round-trips (e.g., using session state variables to store the data), you use server resources even when the page is not being processed. This might work when you have a small set of users, but it will not allow your application to be scalable to a larger user set.

Tip 

When designing Web Form applications, consider the following:

  • Be conservative with data retrieval. Only retrieve what you need and no more.

  • Use client-side state management to store data if possible.

Accessing Data Remotely

Web Forms are the presentation tier of your web application. Although you can include data access in your page, in a distributed architecture paradigm it is common to separate data access logic and business logic from the user interface logic. This can be achieved by building an XML Web Service that contains the data access logic.

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.

click to expand
Figure 4-2: XML Web Service communication cycle

Note 

The DataSet is converted to an XML stream using the DiffGram format. A DiffGram is an XML format that is used to identify current and original versions of data elements. The DataSet uses the DiffGram format to load and persist its contents and to serialize its contents for transport across a network connection. When a DataSet is written as a DiffGram, it populates the DiffGram with all the necessary information to accurately recreate the contents, though not the schema, of the DataSet, including column values from both the original and current row versions, row error information, and row order.

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.

DataSets or Data Commands?

One of the first choices you need to make is whether to cache data in DataSets or access the database directly reading rows through a data reader. For some database operations, that results in modification of the database structure (for example, creating new tables—you cannot use DataSets, but you have to execute a data command instead). For most common data access scenarios, however, you have a choice between storing records in disconnected DataSets and accessing the records directly using data commands.

Each strategy has inherent advantages that apply to any data access scenarios and not just for web applications. Using DataSets makes it easier to work with related tables and data from disparate sources. On the other hand, using a data reader eliminates the extra steps of filling a DataSet. This often results in slightly better performance and memory usage. You also have more direct control over the statements and stored procedures you use.

DataSets and Data Commands in Web Forms Pages

When using Web Forms, additional factors come into play when choosing your data access strategy. One main factor is the Web Form life cycle; Web Forms are initialized, processed, sent to the client, and discarded with each round-trip to the server. If you just want to display data, using a DataSet is inefficient and requires unnecessary overhead since that DataSet will immediately be discarded.

In general, you can assume that using data commands is better when working with Web Forms pages. However, there are exceptions:

  • Working with related tables: With DataSets, you can maintain multiple related tables, including support for relations and referential integrity. When you work with related records, such as parent and child relationships, it can be much simpler to use a DataSet rather than fetching the records independently using data commands.

  • Exchanging data with other processes: If you exchange data with other components, such as XML Web Services, you will almost always use a DataSet to hold a local copy of the data. As discussed earlier, DataSets automatically read and write the XML stream used to communicate between components in the .NET Framework.

  • Working with a static set of records: If you use the same set of records repeatedly, such as paging in a grid, it is more efficient to place those records into a DataSet rather than retrieving the data from the database with each round-trip.

    Tip 

    Remember to always retrieve, whenever practical, only the records and columns that you need and no more. This will reduce load on server resources and make your application more scalable.

Cache or Recreate?

If you choose to use DataSets, your next choice is to decide whether to recreate the DataSet with each round-trip or create it once and save it in such a way that it can be accessed in a subsequent round-trip.

If you choose to recreate the DataSet with each round-trip, you have to run a query against the database each time a user clicks a button on your page. The advantage is there is less chance of the data being out of sync, but there is the added overhead of connecting to the database each time.

If you save and restore the DataSet with each round-trip, you reduce the overhead of connecting to the server but increase the load on server resources. If the recordset is large, and you have a lot of users, you can quickly run out of server resources (namely, memory). You can, however, store the DataSet on the client, as I will discuss in the next section. There is also a bigger chance of the data being out of sync since you are not refreshing the data with each round-trip.

Do You Store the DataSet on the Server or Client?

If you choose DataSets, the final decision is where to store the DataSet. You can store it on the server as a session variable or application variable, or you can store it in the client page in a hidden field. If you store it on the server, you will of course use server resources. This makes the application less scalable. Conversely, if you store the DataSet in the page, it will be passed as part of the HTML stream to the client. If the DataSet is large, the communication speed between server and client can be adversely affected.

No matter which strategy you choose, you will have to write the logic yourself for storing the DataSet on Web Forms. DataSets are stored as type Object in session variables, and you must cast it back as DataSet. See the following example:

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 

Concurrency Issues

In a multiuser and distributed environment, there is often the risk that two users will update the same records or the records will be out of sync. This is common to a concurrent system. There are two strategies that can be adopted to overcome concurrency issues: pessimistic concurrency and optimistic concurrency.

Pessimistic concurrency involves locking rows of records while the user is working on them. It means that no other user can update the records while one user is working on them. This strategy is primarily used in an environment where there is heavy contention for data. It is not really appropriate for web applications since the connection to each client is not maintained with each round-trip. Once a connection closes, all locks that it holds are automatically released.

Optimistic concurrency does not lock rows. Instead, it checks if the row has changed since it was last read before applying any update. If it has not been changed, the update can proceed as normal; otherwise, the user is informed about the change and given a choice to re-retrieve the data and discard changes or overwrite the changed record. The DataSet object is designed to encourage the use of optimistic concurrency for long-running processes, such as those found in distributed applications and web applications.

A common method to determine if the records have changed is to check each field against what was originally retrieved. This, though more accurate, will add additional overhead since the number of fields you have to compare can be potentially large. The more practical method is to design optimistic concurrency checking within your database and application. This can be done by having a date and time field with every updateable table. Each time the table is modified, the current date and time is set in the table. This can be achieved through database triggers or done by the application itself. You will then only have to check the date and time field to know if the record has changed since you last retrieved it.

Data, Data Everywhere

We have so far examined all the different application models available in .NET, different strategies you can follow when manipulating data, and what the different implications are. As you have seen, there is not always a clear-cut solution or correct strategy. You will have to weigh the advantages and disadvantages of each one before you can choose which strategy is appropriate for your application.



ADO. NET Programming
ADO.NET Programming with CDR (Wordware programming library)
ISBN: 1556229658
EAN: 2147483647
Year: 2005
Pages: 28

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net