| Since ADO .NET is so different from ADO 2.x (or just ADO), a comparison is a good idea. Those of you who are fluent with ADO will find the transition easier if you have some basis for associating ADO practices with ADO .NET's. For those of you who have never used ADO, it is still worth the time to see how ADO .NET evolved from ADO and why the changes were made. Here we will look at the differences from a programming point of view. Also, just because ADO .NET is the new kid on the block doesn't mean that there are not still applications that would be better served by ADO. Each has its strengths and weaknesses. Two Different Design Goals ADO and ADO .NET are fundamentally different because the designers had two different goals in mind when they designed the software. ADO is a descendant of the Data Access Objects (DAO)/Remote Data Object (RDO) models. One goal of the designers of ADO was to combine the two older data access technologies into one homogeneous object model that could be used to access any supported database. The other goal was to reduce dependency on open database connectivity (ODBC), an older database standard and replace it with OLEDB, a COM-based standard for connecting to databases. Because of this, the ADO object model was aligned with the two older technologies, DAO and RDO. ADO is also an inherently connected technology. This means that an open database connection is maintained for the life of any of the RecordSet objects that hold data. In later versions of ADO (2.1 and later) it was possible to use a disconnected architecture. This was added to support stateless web applications. Most desktop applications continued to use the connected model. In some cases this is because the applications needed the concurrency support and were highly transactional. In other cases it was because that was what the programmer knew how to do. ADO .NET was designed to support the stateless model by default. It is an inherently disconnected architecture. While ADO .NET continues to support the OLEDB COM-based database access standard, the new goal is to eliminate even that. ADO .NET uses the new SOAP object protocol and uses XML as its storage and communications medium. The SqlClient classes that support SQL Server are the example of the direction Microsoft is headed. The plan is for developers to create native ADO .NET class libraries to support their database product. The SqlClient classes do not use OLEDB. It is one of the long- term goals of Microsoft to eliminate COM. If it seems to you that they took a 180-degree turn , you are not imagining things. That is part of the reason for this book. The ADO Architecture and Practice With all that said, let's very quickly review the ADO object model. There are three main objects in ADO. -
The Connection object manages database connections and transactions. -
The Command object encapsulates SQL statements and manages access to stored procedures. It contains a Parameters collection to manage passing data into and out of stored procedures. -
The RecordSet object holds the results of SQL queries and implements methods for navigating through the rows and for adding, deleting, and updating rows. In its native connected mode, when you update the RecordSet, the results are immediately updated in the underlying database as well. If the RecordSet is opened with a dynamic cursor, other user 's changes to the database are immediately made available. The RecordSet can also be used as a stand-alone object. It has all of the properties required to connect to and access the database. The practice for using ADO in a typical client/server Windows Desktop application written using Visual Basic 6.0 was: -
The programmer would create an instance of a Connection object as either a global variable (a public module level variable in a standard module) or as a module-level variable in a class module. Sometimes the class would be compiled into a separate ActiveX component so it could be deployed as a middle- tier component. Other times it would just be a class module in the Desktop application project. -
As part of the program's initialization sequence, the programmer would prompt the user for security information (user name and password) and then open a connection to the database. -
The open Connection object would remain open and be used throughout the life of the application to open RecordSets and execute commands. If it were a global variable, it would be referenced directly. If it were a module-level variable in a class module, it would be used by the class, and all database access would be initiated through an instance of the class. -
When the user closed the program, the connection would be closed and the connection object deinstantiated. This practice generally worked fine in a local area network (LAN) environment where the server was on the same LAN as the clients . It also worked decently when the server was located across a WAN, depending on traffic and how resource intensive the application in question was. There are some problems with this type of arrangement: -
This kind of application does not scale up well. Because it holds an open connection throughout its life, the server must allocate resources to the user until the user closes the application. The application generally performs well with a low number of users, but performance deteriorates rapidly as the server's resources and memory utilization exceed certain limits. The answer is to expand the server. This helps for a while, but every server has its limits. -
Sloppy programming can easily cause memory and resource leaks that will bring the application and the server to its knees. Programmers must be disciplined enough to remember to release resources when they have finished with them. I have not met many who are. Even when they are, a simple oversight can cause this problem. For example, the failure to close and uninstantiate a RecordSet can be disastrous. This is especially true if the RecordSet is opened many times during the course of the application's life. Also, I've known programmers who think that just setting a RecordSet's variable to nothing without explicitly closing it is good enough, not realizing that the server keeps the cursor alive until the user closes the connection. If this is part of the main processing of the program, the server will get bogged down in short order. -
The more simultaneous open connections that are required, the more client licenses you need to buy. If you want to use a concurrent licensing model for SQL Server, then you must have enough licenses for each user logged on at the same time. This issue, obviously, is economic, not technical. Even with these shortcomings, sometimes this type of application architecture is indicated. This is especially true in applications that are highly transactional and require instant data availability. Some of these would include trading systems and banking applications. The users of these applications need to see database updates as they happen. The only way to do this is by maintaining an open connection. These applications require high-performance networks, workstations, and servers. They also require pretty savvy programming techniques. If those are your requirements, ADO may still be the way to go for you. You can use ADO in your VB .NET application. As a matter of fact, with VB .NET's multithreading capability and ADO it is possible to design these extremely high performance applications. Previously C++ would have been the language of choice because these applications usually use multithreading to achieve the performance they require. ADO .NET Architecture and Practice ADO .NET is inherently disconnected. This is the exact opposite of ADO. It was designed this way to support the stateless nature of ASP .NET and Web Services. We have already outlined the ADO .NET class library so let's outline the practice for using ADO .NET in a Windows Forms application. -
Your application will consist of multiple forms. At first glance, you would think that you would create an ADO .NET Connection object in the main class of your application and pass it to your forms as they needed it. However, this turns out to be more work than you need to do. Since connections only need to last as long as the latest call, we really do not need one central Connection object the way we might want in a VB 6 ADO application. -
In ADO .NET, the best practice is to have each form have its own connection object. This way the forms are entirely encapsulated. What I do is pass the connection string to each form. Once the connection has been validated during the logon sequence, all you need to retain is the formed connection string. If you are worried about holding a password in memory where it could possibly be compromised, you can encrypt the connection string and decrypt it just before setting the property of the Connection object. That way it only exists in a plain-text state for a very short period of time. -
When you need to populate your bound controls, use the DataAdapter's Fill method on the DataSet the controls are bound to. The DataAdapter will open and close connections as needed. -
When you need to update the database, use the Update method of the DataAdapter. -
When the user closes the program, there is no need to worry about closing database connections because they remain closed until needed. The process for an ASP .NET web-based application is: -
Display an ASP .NET Web Form. In this form, there would be fields the user will fill in that will be used to build an SQL query. -
When the user clicks a button, the program executes a DataAdapter's Fill method passing the values from the form that the user entered through the DataAdapter's SelectCommand parameters collection. -
Assuming you have bound controls on the form to the DataSet, when you execute the form's DataBind method, it will post the page back to the client with the values from the DataSet in the controls. -
The user makes changes to the data. When finished, the user clicks another button. Since data binding on an ASP .NET form is display only, you now have to manually assign each value from the form to the parameters of the DataAdapter's UpdateCommand parameters collection. -
You would then call the UpdateCommand's ExecuteNonQuery method to update the database. Why can't I use the same techniques in ASP Windows Forms as I do in Windows Forms? Because the ASP .NET form is stateless. What do we mean by stateless? We will go into this more in the chapter on ASP .NET, but essentially it means that there is no connection between calls to the server. Each time the client calls code in the server, the whole server-side program must reexecute. For this reason, any data generated in one call to the server is lost as soon as the call is completed. This is true of DataSets as well. Therefore, even though you are using bound controls, as soon as the call to the server completes, the underlying DataSet is destroyed . When you make changes and send them back up to the server, there is no DataSet to rebind the controls to. In a Windows Form, the DataSet is continually in existence therefore the form controls can communicate with it whenever they need to. As we have said, ADO .NET is optimized for web-based stateless applications. However, there are some potential problems with this architecture as well: -
There is some overhead to authenticating a connection each time the database is accessed. The Connection class uses connection pooling to minimize this effect. For SQL Server it is minimal, but it is still a concern. You can control this programmatically, and if a single form makes several accesses in rapid succession, it would be wise to maintain the open connection until the form is finished. In a Windows Form this is easy, but in an ASP .NET application you must make sure you don't execute rebind too often as there will be a round-trip to the server each time and the connection could be lost between requests . Make sure all of your database access happens during one ASP request. If you don't, you will be opening and closing connections for each database access. -
You will not be able to see database updates made by other users in real-time. As we said, if you need this kind of real-time availability, then ADO may be a better choice.. This is true of ASP Windows Forms and Windows Forms. -
This is related to the previous item. You cannot use pessimistic concurrency with ADO .NET. With pessimistic concurrency, each row is locked as soon as the client reads it. The lock is not released until the user moves to another row or until the row is updated. In a highly transactional environment, where you need row-level read locking, you cannot use ADO .NET. There are other techniques for achieving a similar result, but they would have to be implemented by the programmer. -
In any client/server application it is always desirable to manage your database queries so that they return reasonable amounts of data. All too often this is not considered when programmers write database access code. They often develop using a test database with a limited number of rows, and then when they try it on the production database they find that their application fails because the query returns millions of rows. ADO would attempt to handle this by using the RecordSet's row caching and delayed fetching to keep in memory only enough rows to display or manipulate. Even so, it could easily bring an application to its knees. With ADO .NET, this is even more important because when you fill a DataSet, ADO .NET must fetch all of the rows returned by the server. If your query returns millions of rows, this could be disastrous. You absolutely must manage your database queries so that they return manageable numbers of rows. ADO .NET is not the answer for all types of applications. ADO is still alive and kicking. If you need high-concurrency and real-time data availability, ADO should still be considered. ADO .NET is best for web applications that use a stateless architecture. It is also indicated when a Windows application does not require real-time access to data. Since most everyday applications do not need high-performance real-time access, ADO .NET should be used since it is very efficient and is designed to work with XML. You could also use a combination of both techniques in your system, using each for its strengths.  |
