Connected Data Access
| The ADO.NET Framework encompasses a huge number of classes. However, at its heart, it really consists of the following three classes:
Most of the other classes in the ADO.NET Framework are built from these three classes. These three classes provide you with the fundamental methods of working with database data. They enable you to connect to a database, execute commands against a database, and represent the data returned from a database. Now that you understand the importance of these three classes, it's safe to tell you that they don't really exist. ADO.NET uses the Provider model. You use different sets of ADO.NET classes for communicating with different data sources. For example, there is no such thing as the Connection class. Instead, there is the SqlConnection class, the OracleConnection class, the OleDbConnection class, and the ODBCConnection class. You use different Connection classes to connect to different data sources. The different implementations of the Connection, Command, and DataReader classes are grouped into the following namespaces:
If you are connecting to Microsoft SQL Server 7.0 or higher, you should always use the classes from the SqlClient namespace. These classes provide the best performance because they connect directly to SQL Server at the level of the Tabular Data Stream (the low-level protocol that Microsoft SQL Server uses to communicate with applications). Of course, there are other databases in the world than Microsoft SQL Server. If you are communicating with an Oracle database, you should use the classes from the OracleClient namespace. If you are communicating with another type of database, you need to use the classes from either the OleDb or Odbc namespaces. Just about every database ever created by man has either an OLEDB provider or an ODBC driver. Because ADO.NET follows the Provider model, all implementations of the Connection, Command, and DataReader classes inherit from a set of base classes. Here is a list of these base classes:
These base classes are contained in the System.Data.Common namespace. All the sample code in this chapter assumes that you are working with Microsoft SQL Server. Therefore, all the sample code uses the classes from the SqlClient namespace. However, because ADO.NET uses the Provider model, the methods that you would use to work with another database are very similar to the methods described in this chapter. Note Before you can use the classes from the SqlClient namespaces in your components and pages, you need to import the System.Data.SqlClient namespace. Before we examine the Connection, Command, and DataReader classes in detail, let's look at how you can build a simple data access component with these classes. The component in Listing 16.1, named Movie1, includes a method named GetAll() that returns every record from the Movies database table. Listing 16.1. App_Code\Movie1.vb
In Listing 16.1, a SqlConnection object is used to represent a connection to a Microsoft SQL Server database. A SqlCommand object is used to represent a SQL SELECT command. The results of executing the command are represented with a SqlDataReader. Each row returned by the SELECT command is retrieved by a call to the SqlDataReader.Read() method from within a While loop. When the last row is retrieved from the SELECT command, the SqlDataReader.Read() method returns False and the While loop ends. Each row retrieved from the database is added to a List collection. An instance of the Movie1 class is used to represent each record. The page in Listing 16.2 uses a GridView and ObjectDataSource control to display the records returned by the Movie1 data access component (see Figure 16.1). Figure 16.1. Displaying movie records. Listing 16.2. ShowMovie1.aspx
Using the Connection ObjectThe Connection object represents a connection to a data source. When you instantiate a Connection, you pass a connection string to the constructor, which contains information about the location and security credentials required for connecting to the data source. For example, the following statement creates a SqlConnection that represents a connection to a Microsoft SQL Server database named Pubs that is located on the local machine:
For legacy reasons, there are a number of ways to write a connection string that does exactly the same thing. For example, the keywords Data Source, Server, Address, Addr, and Network Address are all synonyms. You can use any of these keywords to specify the location of the database server. Note You can use the SqlConnectionStringBuilder class to convert any connection string into canonical syntax. For example, this class replaces the keyword Server with the keyword Data Source in a connection string. Before you execute any commands against the data source, you first must open the connection. After you finish executing commands, you should close the connection as quickly as possible. A database connection is a valuable resource. Strive to open database connections as late as possible and close database connections as early as possible. Furthermore, always include error handling code to make sure that a database connection gets closed even when there is an exception. For example, you can take advantage of the Using statement to force a connection to close even when an exception is raised, like this:
The Using statement forces the connection to close, regardless of whether or not there is an error when a command is executed against the database. The Using statement also disposes of the Connection object. (If you need to reuse the Connection, then you need to reinitialize it.) Alternatively, you can use a TRy...Catch statement to force a connection to close like this:
The Finally clause in this TRy...Catch statement forces the database connection to close both when there are no errors and when there are errors. Retrieving Provider StatisticsWhen you use the SqlConnection object, you can retrieve statistics about the database commands executed with the connection. For example, you can retrieve statistics on total execution time. The GetAll() method exposed by the component in Listing 16.3 includes a parameter named executionTime. After the database command executes, the value of executionTime is retrieved from the Connection statistics. Listing 16.3. App_Code\Movie2.vb
In Listing 16.3, the SqlConnection.StatisticsEnabled property is set to the value true. You must enable statistics before you can gather statistics. After the command executes, a dictionary of statistics is retrieved with the SqlConnection.RetrieveStatistics() method. Finally, you retrieve the executionTime by looking up the ExecutionTime key in the dictionary. Note In Listing 16.3, the SQL WAITFOR statement is used to pause the execution of the SELECT command for three seconds so that a more interesting execution time is retrieved from the ExecutionTime statistic. Because the SELECT command is such a simple command, if you don't add a delay, you often receive an execution time of 0 milliseconds. The page in Listing 16.4 illustrates how you can use this component to display both the results of a database query and the database query execution time (see Figure 16.2). Listing 16.4. ShowMovie2.aspx
Figure 16.2. Displaying execution time statistics. The SqlConnection object supports the following properties and methods related to gathering statistics:
You can call the RetrieveStatistics() method multiple times on the same SqlConnection. Each time you call the method, you get another snapshot of the Connection statistics. Here's a list of the statistics that you can gather:
The page in Listing 16.5 displays the values of all these statistics in a GridView control (see Figure 16.3). Figure 16.3. Displaying all provider statistics. Listing 16.5. ShowAllStatistics.aspx
Improving Performance with Connection PoolingDatabase connections are precious resources. If you want your ASP.NET application to scale to handle the demands of thousands of users, then you need to do everything in your power to prevent database connections from being wasted. Opening a database connection is a slow operation. Rather than open a new database connection each time you need to connect to a database, you can create a pool of connections that can be reused for multiple database queries. When connection pooling is enabled, closing a connection does not really close the connection to the database server. Instead, closing the connection releases the database connection back into the pool. That way, the next time a database query is performed, a new connection to the database does not need to be opened. When you use the SqlConnection object, connection pooling is enabled by default. By default, the ADO.NET framework keeps a maximum of 100 connections opened in a connection pool. You need to be warned about two things in regard to connection pooling. First, when taking advantage of connection pooling, it is still very important to close your connections by calling the SqlConnection.Close() method. If you don't close a connection, the connection is not returned to the pool. It might take a very long time for an unclosed connection to be reclaimed by ADO.NET. Second, different connection pools are created for different connection strings. In particular, a different connection pool is created for each unique combination of connection string, process, application domain, and Windows identity. An exact character-by-character match is performed on the connection string. For this reason, you should always store your connection strings in the web configuration file. Don't hardcode connection strings inside your components. If there is a slight variation between two connection strings, then separate connection pools are created, which defeats the performance gains that you get from connection pooling. The SqlConnection object supports two methods for clearing connection pools programmatically:
These methods are useful when you are working with a cluster of database servers. For example, if you take a database server down, you can programmatically clear the connection pool to the database server that no longer exists. You can control how connections are pooled by using the following attributes in a connection string:
The page in Listing 16.6 displays a list of all the current user connections to a database in a GridView (see Figure 16.4). Notice that the connection string used when connecting to the database creates a minimum connection pool size of 10 connections. (You'll have to refresh the page at least once to see the 10 connections.) Listing 16.6. ShowUserConnections.aspx
Figure 16.4. Displaying user database connections. Using the Command ObjectThe Command object represents a command that can be executed against a data source. In this section, you learn how to use the SqlCommand object to execute different types of database commands against Microsoft SQL Server. Executing a CommandYou can use the SqlCommand.ExecuteNonQuery() method to execute a SQL command that does not return a set of rows. You can use this method when executing SQL UPDATE, DELETE, and INSERT commands. You can also use this method when executing more specialized commands, such as a CREATE TABLE or DROP DATABASE command. For example, the component in Listing 16.7 includes Update() and Delete() methods that update and delete movie records. Listing 16.7. App_Code\Movie3.vb
The page in Listing 16.8 contains a GridView that binds to the data access component in Listing 16.7. The GridView enables you to display, update, and delete database records (see Figure 16.5). Figure 16.5. Updating and deleting database records. Listing 16.8. ShowMovie3.aspx
Executing a Command with ParametersMost database commands that you execute include parameters. For example, when updating a database record, you need to supply parameters that represent the new values of the database record columns. Warning Never build command parameters through string concatenation because concatenating strings is an open invitation for SQL injection attacks. If a user enters the proper sequence of characters in a form field, and a SQL command is built through concatenation, then a user can execute an arbitrary SQL command. Always explicitly create parameters by creating instances of the SqlParameter object. When a SQL command is executed with explicit parameters, the parameters are passed individually to a SQL Server stored procedure named sp_executesql. You represent a parameter with the SqlParameter object. You can create a new SqlParameter in multiple ways. The easiest way is to call the SqlCommand.AddWithValue() method like this: Dim cmd As New SqlCommand("INSERT Titles (Title) VALUES (@Title)", con) cmd.Parameters.AddWithValue("@Title", "ASP.NET 2.0 Unleashed") The first statement creates a SqlCommand object that represents a SQL INSERT command. Notice that the command includes a parameter named @Title. The second statement adds a SqlParameter to the SqlCommand object's Parameters collection. The AddWithValue() method enables you to add a parameter with a certain name and value. In this case, the method is used to supply the value for the @Title parameter. When you execute the SqlCommmand, the following command is sent to Microsoft SQL Server:
The SqlCommand object calls the sp_executesql stored procedure when it executes a command. In this case, it passes the type, size, and value of the @Title parameter to the sp_executesql stored procedure. When you use AddWithValue(), the SqlCommand object infers the type and size of the parameter for you. The method assumes that string values are SQL NVarChar values, integer values are SQL Int values, decimal values are SQL decimal values, and so on. As an alternative to using the AddWithValue() method, you can create a SqlParameter explicitly and add the SqlParameter to a SqlCommand object's Parameters collection. The advantage of creating a parameter explicitly is that you can specify parameter properties explicitly, such as its name, type, size, precision, scale, and direction. For example, the following code creates a parameter named @Title with a particular data type, size, and value: Dim cmd As New SqlCommand("INSERT Titles (Title) VALUES (@Title)", con) Dim paramTitle As New SqlParameter() paramTitle.ParameterName = "@Title" paramTitle.SqlDbType = SqlDbType.NVarChar paramTitle.Size = 50 paramTitle.Value = "ASP.NET 2.0 Unleashed" cmd.Parameters.Add(paramTitle) If this seems like a lot of code to do something simple, then you can use one of the overloads of the Add() method to create a new SqlParameter like this: Dim cmd As New SqlCommand("INSERT Test (Title) VALUES (@Title)", con) cmd.Parameters.Add("@Title", SqlDbType.NVarChar,50).Value = "ASP.NET 2.0 Unleashed"; In general, in this book and in the code that I write, I use the AddWithValue() method to create parameters. I like the AddWithValue() method because it involves the least typing. Executing a Command That Represents a Stored ProcedureYou can use a SqlCommand object to represent a Microsoft SQL Server stored procedure. For example, you can use the following two statements to create a SqlCommand object that represents a stored procedure named GetTitles: Dim cmd As New SqlCommand("GetTitles", con) cmd.CommandType = CommandType.StoredProcedure When you execute this SqlCommand, the GetTitles stored procedure is executed. When you create SqlParameters for a SqlCommand that represents a stored procedure, the SqlParameters represent stored procedure parameters. The modified Movie component in Listing 16.9 uses stored procedures to retrieve and update movie records. Listing 16.9. App_Code\Movie4.vb
The component in Listing 16.9 uses the MovieSelect and MovieUpdate stored procedures contained in Listing 16.10. Listing 16.10. MovieStoredProcedures.sql
The ASP.NET page in Listing 16.11 contains a GridView that is bound to the modified Movie component. This GridView enables you to display and update movie records. Listing 16.11. ShowMovie4.aspx
You can use a SqlParameter to represent not only stored procedure input parameters, but to represent stored procedure return values and output parameters. If you need to return an integer value from a stored procedure, then you can create a SqlParameter that represents a return value. For example, the stored procedure in Listing 16.12 returns the number of rows in the Movies database table. Listing 16.12. GetMovieCount.sql
The page in Listing 16.13 displays the return value from the GetMovieCount stored procedure with a Label control (see Figure 16.6). Figure 16.6. Displaying a stored procedure return value. Listing 16.13. ShowMovieCount.aspx
In Listing 16.13, a SqlParameter is created that has the name ReturnVal. The name of the SqlParameter is not important. However, notice that the SqlParameter.Direction property is set to the value ReturnValue. After the SqlCommand is executed, the return value can be retrieved by reading the value of this parameter. A stored procedure has only one return value, and it must be an integer value. If you need to return more than one value, or values of a different data type than an integer, then you need to use stored procedure output parameters. For example, the stored procedure in Listing 16.14 returns movie titles and box office totals. Notice that the stored procedure includes an output parameter named @SumBoxOfficeTotals. This output parameter represents a sum of all box office totals. Listing 16.14. GetBoxOfficeTotals.sql
The data access component in Listing 16.15 contains a method named GetBoxOffice() that calls the GetBoxOfficeTotals stored procedure. The method adds an output parameter to the SqlCommand object. Listing 16.15. App_Code\Movie5.vb
In Listing 16.15, notice that the SqlDataReader is explicitly closed before the output parameter is read. If you do not close the SqlDataReader first, then attempting to read the value of the output parameter raises an exception. Finally, the page in Listing 16.16 displays the movie box office totals in a GridView. In addition, it displays the value of the output parameter in a Label control (see Figure 16.7). Figure 16.7. Displaying an output parameter. Listing 16.16. ShowMovie5.aspx
Returning a Single ValueIf you need to return a single value from a database query, you can use the SqlCommand.ExecuteScalar() method. This method always returns the value of the first column from the first row of a resultset. Even when a query returns hundreds of columns and billions of rows, everything is ignored except for the value of the first column from the first row. For example, the page in Listing 16.17 contains a lookup form. If you enter the title of a movie, the movie's total box office returns are displayed in a Label control (see Figure 16.8). Figure 16.8. Retrieving a value with ExecuteScalar(). Listing 16.17. ShowExecuteScalar.aspx
The ExecuteScalar() method returns a value of type Object. This means that you must cast the value returned from ExecuteScalar() to a particular type before you do anything with the value. In Listing 16.17, after verifying that a value is returned, the value is cast to a decimal. Notice that you have a choice here. Rather than use the ExecuteScalar() method, you can use an output parameter. You can use either method to return a single value from a database. There is no real difference in performance between using the ExecuteScalar() method with a stored procedure or using an output parameter. The approach you take is largely a matter of preference. Note For performance comparisons between ExecuteScalar and output parameters, see Priya Dhawan's article at the Microsoft MSDN website (msdn.Microsoft.com), entitled "Performance Comparison: Data Access Techniques." Returning a ResultsetIf you need to return multiple rows of data with a SqlCommand object, then you can call the SqlCommand.ExecuteReader() method. This method returns a SqlDataReader that you can use to fetch each row of records from the database. For example, the data access component in Listing 16.18 contains a method named GetAll() that returns all the movies from the Movies database table. After the ExecuteReader() method is called, each row is retrieved from the SqlDataReader and dumped into a generic List collection. Listing 16.18. App_Code\Movie6.vb
The page in Listing 16.19 contains a GridView bound to an ObjectDataSource that represents the component in Listing 16.18 (see Figure 16.9). Figure 16.9. Returning a resultset. Listing 16.19. ShowMovie6.aspx
The component in Listing 16.18 copies all the records from the SqlDataReader to a collection before returning the results of the query. If you want to skip the copying step, and not add the records to a collection, then you can pass a CommandBehavior.CloseConnection parameter to the ExecuteReader() method. This parameter causes the database connection associated with the SqlDataReader to close automatically after all the records have been fetched from the SqlDataReader. The component in Listing 16.20 illustrates how you can use CommandBehavior.CloseConnection with the ExecuteReader() method. Listing 16.20. App_Code\Movie7.vb
The page in Listing 16.21 displays the records returned from the component in Listing 16.20 in a GridView. Listing 16.21. ShowMovie7.aspx
The CommandBehavior.CloseConnection parameter enables you to return a SqlDataReader from a method. When all the records are read from the SqlDataReader, the CommandBehavior.CloseConnection parameter causes the SqlConnection object associated with the SqlDataReader to close automatically. The big disadvantage of using the CommandBehavior.CloseConnection parameter is that it prevents you from adding any exception handling code. You can't use a Using statement or TRy...Catch statement with the SqlConnection created in the component in Listing 16.19. A Using statement or TRy...Catch statement would force the SqlConnection to close before the SqlDataReader is returned from the method. Using the DataReader ObjectThe DataReader object represents the results of a database query. You get a DataReader by calling a Command object's ExecuteReader() method. You can verify whether a DataReader represents any rows by checking the HasRows property or calling the Read() method. The Read() method returns TRue when the DataReader can advance to a new row. (Calling this method also advances you to the next row.) The DataReader represents a single row of data at a time. To get the next row of data, you need to call the Read() method. When you get to the last row, the Read() method returns False. There are multiple ways to refer to the columns returned by a DataReader. For example, imagine that you are using a SqlDataReader named reader to represent the following query: SELECT Title, Director FROM Movies If you want to retrieve the value of the Title column for the current row represented by a DataReader, then you can use any of the following methods: Dim Title As String = CType(reader("Title"), String) Dim Title As String = CType(reader(0), String) Dim Title As String = reader.GetString(0) Dim Title As String = reader.GetSqlString(0) The first method returns the Title column by name. The value of the Title column is returned as an Object. Therefore, you must cast the value to a string before you can assign the value to a string variable. The second method returns the Title column by position. It also returns the value of the Title column as an Object, so you must cast the value before using it. The third method returns the Title column by position. However, it retrieves the value as a String value. You don't need to cast the value in this case. Finally, the last method returns the Title column by position. However, it returns the value as a SqlString rather than a normal String. A SqlString represents the value as a Microsoft SQL Server 2005 String. Note SqlTypes is a new feature of ADO.NET 2.0. There is a SqlType that corresponds to each of the types supported by Microsoft SQL Server 2005. For example, there is a SqlDecimal, SqlBinary, and SqlXml type. There are tradeoffs between the different methods of returning a column value. Retrieving a column by its position rather than its name is faster. However, this technique also makes your code more brittle. If the order of your columns changes in your query, your code no longer works. Returning Multiple ResultsetsA single database query can return multiple resultsets. For example, the following query returns the contents of both the MovieCategories and Movies tables as separate resultsets: SELECT * FROM MoviesCategories;SELECT * FROM Movies Notice that a semicolon is used to separate the two queries. Executing multiple queries in one shot can result in better performance. When you execute multiple queries with a single command, you don't tie up multiple database connections. The component in Listing 16.22 illustrates how you can retrieve multiple resultsets with a single query when using a SqlDataReader. The GetMovieData() method returns two collections: a collection representing MovieCategories and a collection representing Movies. Listing 16.22. App_Code\DataLayer1.vb
The SqlDataReader.NextResult() method is called to advance to the next resultset. This method returns either true or False depending on whether a next resultset exists. In Listing 16.22, it is assumed that there is both a movies category and movies resultset. The page in Listing 16.23 displays the contents of the two database tables in two GridView controls (see Figure 16.10). Figure 16.10. Displaying two resultsets. Listing 16.23. ShowDataLayer1.aspx
Working with Multiple Active ResultsetsADO.NET 2.0 includes a new feature named Multiple Active Results Sets (MARS). In the previous version of ADO.NET, a database connection could represent only a single resultset at a time. If you take advantage of MARS, you can represent multiple resultsets with a single database connection. Using MARS is valuable in scenarios in which you need to iterate through a resultset and perform an additional database operation for each record in the resultset. MARS is disabled by default. To enable MARS, you must include a MultipleActiveResultSets=True attribute in a connection string. For example, the page in Listing 16.24 programmatically builds the nodes in a treeView control. The page displays a list of movie categories and, beneath each movie category, it displays a list of matching movies (see Figure 16.11). Figure 16.11. Fetching database records with MARS enabled. Listing 16.24. ShowMARS.aspx
Notice that the MultipleActiveResultSets attribute is included in the connection string used to open the database connection. If MARS were not enabled, then you would not be able to loop through the interior SqlDataReader that represents the matching movies while the containing SqlDataReader that represents the movie categories is open. |
.ConnectionString End Sub End Class 