Simplest Example

As always, I started my exploration of ADO.NET with the simplest example I could think of that demonstrated anything useful. You can find this sample program on this book’s Web site, http://www.introducingmicrosoft.net. The application is an ASP.NET page that performs a canned database query when you request the page. It uses DataConnection and DataAdapter objects to request all the entries in the Authors table in the pubs database in the Duwamish Books sample program distributed with the .NET Framework SDK. The query produces an ADO.NET DataSet object, which I display to the user in a Web Forms DataGrid control on the Web page. The page itself is shown in Figure 6-5 and the sample code in Listing 6-1.

An ADO.NET example starts here.

Figure 6-5: Web page from the simplest ADO.NET sample.

Listing 6-1: The Page_Load event handler of the simplest ADO.NET sample.

Private Sub Page_Load(ByVal sender As System.Object, _ ByVal e As System.EventArgs) _ Handles MyBase.Load ’ Create Connection object containing connection string Dim Connection As New _ SqlConnection("server=(local);uid=sa;pwd=‘‘;database=pubs") ’ Create DataAdapter object containing query string Dim Adapter As New _ SqlDataAdapter("select * from Authors", Connection) ’ Create new empty DataSet object Dim DS = New DataSet() ’ Fill DataSet object with results of query Adapter.Fill(DS, "Authors") ’ Place data set into DataGrid control for user to look at DataGrid1().DataSource = DS.Tables("Authors").DefaultView ’ Tell DataGrid control to display its contents DataGrid1().DataBind() ’ Clean up database connection Connection.Close() End Sub

When the user requests the page in her browser, the request comes to Internet Information Services (IIS) and ASP.NET, which fires the Page_Load event on the page as part of the rendering process. All the interesting code in this example lives in the handler for this event.

The first thing we have to do is create the Connection object. This object represents the opening in the database program through which requests flow in and data flows out, roughly analogous to the Ethernet jack on your office wall. ADO.NET provides two different common language runtime classes that we can use for our database connection. The class System.Data.SqlClient.SqlConnection, which I use in this example, is optimized to work only with Microsoft SQL Server. ADO.NET also provides the class System.Data.OleDb.OleDbConnection, which is a generic Connection object that works with any OLE DB data provider, including SQL Server. Except for the names of the object classes and some slight differences in the connection string, the generic Connection object works the same from a client perspective as the dedicated SQL Server interface. Obviously, writing two different sets of data access objects was more work for Microsoft, but Microsoft probably figured that doing so was worth the effort to make SQL Server work better than generic databases, and they were probably right. I use the SQL- specific classes in this book.

You first create an ADO.NET Connection object representing the connection to your database.

In the constructor of the SqlConnection object, we pass it the connection string that we use to connect to the database, containing such items as the data provider’s name and location, the database inside the provider to use, and the user ID and password that we use to connect to it. The values in this string are the same as they were in standard, pre-.NET ADO.

Having created the Connection object, we now need to create the DataAdapter object, which mediates between the Connection object and the client application. Think of the DataAdapter object as the Ethernet card in your PC. Programs talk to the network card (the DataAdapter object), which in turn talks to the jack on the wall (the Connection object). Your client program issues commands to the DataAdapter object, which transmits them to the database through the Connection object and then accepts the results from the Connection object and returns them to your client program. In the DataAdapter class constructor, we pass it the command that we want it to execute in the database—in this case selecting all the entries from a table of authors—and the Connection object for it to use in making that query. ADO.NET provides two DataAdapter classes, which are System.Data.OleDb.OleDbDataAdapter and System.Data.SqlClient.SqlDataAdapter. As was the case with Connection objects, the former is a generic class that works with any OLE DB–compliant data source, and the latter is optimized to work with SQL Server.

You next create a DataAdapter object, which uses the Connection object to make calls into the database.

Now that we have our DataAdapter object, we want to use it to query the database and fetch some data for us to make money with. ADO.NET provides the class System.Data.DataSet as the fundamental holder for all types of data. A DataSet object contains its own internal tables that will contain the results of the queries that we will make on the data provider through the DataAdapter object and the Connection object. We start by creating an object of this class, which is empty when we first create it. We fill the DataSet object with data by calling the DataAdapter object’s Fill method, passing the DataSet object itself and the name of the table inside the DataSet object that we want the data to live in. If, as in this case, the table doesn’t currently exist in the DataSet object, it will be created as a result of this call. The table name need not match the table in the underlying database, as the name is used only within the DataSet object by client programs.

You create an empty DataSet object and use the DataAdapter object to fill it with data.

Once I have my DataSet object filled, I want to display its contents to the user. I do this by placing it into a DataGrid control, a Web Forms control developed expressly for this purpose. The control lives on an .aspx page, and it knows how to eat a DataSet object and render its contents into HTML for display to the user. I tell it which DataSet object to eat by setting its DataSource property to the DataSet object I just got from my query. I then tell the control “make it so” by calling its DataBind method.

Once I have the data set, I use a DataGrid control to easily display it to the user.

Finally, when I am finished with the database connection, it’s a good idea to explicitly close it by using the Connection object’s Close method. If I simply let the Connection object go out of scope, the object wouldn’t be finalized and the underlying database connection that it wraps wouldn’t be freed until the next garbage collection, whenever that is. Database connections are scarce resources, and I’d like to recover them as soon as possible. Therefore I call the Close method to tell the Connection object that I am finished with the database connection so that it should reclaim those resources. Enforcing this determinism in a garbage-collected memory management environment is obviously slightly harder to code, and therefore slightly easier to mess up, than was the automatic reference counting scheme used in Visual Basic 6.0, which would have released the Connection object immediately. However, garbage collection makes it impossible for you to permanently leak away resources, which reference counting allowed in certain cases. See my discussion of garbage collection in Chapter 2 for more details about the benefits of foolproofness vs. easy determinism.

It’s a good idea to explicitly close the database connection.

This simple example required very little code, but it illustrates important concepts of ADO.NET and also demonstrates that it doesn’t take a lot of programming to get a lot of stuff done.

This example required very little code.

Tips from the Trenches

One of the best ways to improve the performance of database applications is by pooling database connections. ADO.NET automatically provides this service by default. The first time a client creates a Connection object, it really is created. When the client calls Close or Dispose on this object, rather than shredding it, the pooling manager puts it into a pool that it maintains until the client application’s process is terminated. Subsequent creations of a connection with the same connection string parameters cause the object to be fetched from the pool; a new object is not created. You can modify the behavior of the connection pool—for example, its maximum and minimum number of connections—by making entries in the connection string. Generally, however, the default behaviors (pooling enabled, minimum of zero objects, maximum of 100) give you good performance with no development effort. Still, for proper operation of the pool, you must remember to call Close or Dispose on your Connection object, as this example does. This would be a very good application of a try- finally block, as described in Chapter 2.