Understanding the .NET Managed Data Provider

A .NET managed data provider is a set of objects for connecting to a data source, executing commands, and retrieving data. The .NET data provider is designed to be lightweight, creating a minimal layer between the data source and your code, thereby increasing performance without sacrificing functionality. ADO.NET currently offers four widely used managed providers:

• SQL Server .NET data provider: This is the optimal choice for SQL Server 7.0 or greater. This provider uses the Tabular Data Stream (TDS) protocol, which is SQL Server's native data format. The TDS protocol operates by sending data packets over Remote Procedure Calls (RPC), and it is the fastest performing provider. To use this provider, you need to import the System.Data.SqlClient namespace into the .NET application.

• OLE DB .NET data provider: This is the optimal choice for SQL Server 6.5 or earlier or any data source that supports native OLE DB providers, including Microsoft Access. The OLE DB .NET data provider must communicate to an OLE DB data source through two intermediate layers : the OLE DB provider and the OLE DB Service component, which provides connection pooling and transaction services. For this reason, the OLE DB .NET data provider is not as fast as compared to the SQL Server .NET data provider, especially for SQL Server 7.0 or greater. To use this provider, you need to import the System.Data.OleDbClient namespace into the .NET application.

• Oracle .NET data provider: The optimal choice for Oracle. You can also use the OLE DB .NET data provider for accessing Oracle databases. However, the Oracle data provider will provide improved performance, especially for data retrieval operations. To use this provider, you need to import the System.Data.OracleClient namespace into the .NET application.

• ODBC .NET data provider: This provider is designed to work with any non-OLE DB data source that supports native ODBC drivers. However, the Microsoft Web site states that this provider has only been tested with the ODBC drivers for SQL Server, Access, and Oracle. You can read more about the Oracle .NET data provider at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dndotnet/html/ManProOracPerf.asp .

ADO.NET ships with the SQL Server and OLE DB .NET data providers, but as of press time you must download the Oracle and ODBC data providers from Microsoft's Web site.

Note

The Oracle and ODBC .NET data providers are available for download at http://msdn.microsoft.com/downloads . For Oracle, navigate to the .NET Framework Microsoft .NET Data Provider for Oracle node. For ODBC, navigate to the .NET Framework ODBC .NET Data Provider node.

Microsoft is encouraging the development of additional OLE DB .NET data providers, so you can expect managed provider support for additional data sources to be forthcoming. The advantage of the data provider architecture is that it supports a consistent set of objects that shield the implementation details from the end user . All .NET data providers are required to implement a default set of objects: the Connection, Command, DataAdapter, and DataReader objects. This allows developers to focus on writing data access code that is independent of the specific backend data source.

Using the Connection Class

The .NET data provider includes a Connection class for establishing the connection to a data source. The following code shows how to open a connection to a SQL Server 7.0 (or later) database using the SqlConnection class:

 Imports System.Data.SqlClient Dim sqlConn As SqlConnection = New_     SqlConnection(  <SqlConnection.ConnectionString>  ) sqlConn.Open() 

The syntax to create and open a connection using the OleDbConnection class is almost identical to that for the SqlConnection class:

 Imports System.Data.OleDbClient Dim oleConn As OleDbConnection =     New OleDbConnection(  <OleDbConnection.ConnectionString>  ) oleConn.Open() 

These code samples illustrate the similarities between different data providers.

Using the Connection String Property

The ConnectionString property includes the source database name and other parameters needed to establish the initial connection. Developers frequently debate the best location for storing connection strings in an ASP.NET application, but one good choice is in the Web.config file as a custom configuration setting. The Web.config file has global scope for all files in the application. The file cannot be browsed directly from the outside, so an outside party cannot discover the connection string or any other information in the file. In addition, the Web.config file is easy to update and does not require any recompilation when the file contents change. When the Web.config file changes, ASP.NET automatically loads the new file as soon as all current application requests have been completed.

Custom application settings are stored as simple key-value pairs below the <appSettings> element, which in turn resides below the root <configuration> element. This is how you would store a database connection string:

 <configuration>       <appSettings>                <add key=" ConnectionString"               value=" server=machineName\sqlServer;uid=myId;pwd=myPwd;database=dev;" />       </appSettings> </configuration> 

You can easily access the custom application setting from the code-behind file using the AppSettings property of the ConfigurationSettings class:

 ' Retrieve the connection string from Web.config Dim strConn As String = ConfigurationSettings.AppSettings("ConnectionString") Response.Write("The 'Connection String' is: "& strConn) 

The ConfigurationSettings class is a member of the System.Configuration namespace. You will not need to import this namespace in the code-behind file for an *.aspx page, but you will have to import it for a Web service in the code-behind file for the * .asmx page. Chapter 2, "Introducing ASP.NET Applications," discusses how to store custom application settings in the Web.config file in great detail.

Table 3-3 provides an overview of important ConnectionString parameters, including some lesser-known parameters that may improve the performance of your data access code. Note that several parameters have alternative names , and you can pick whichever one you want to use.

Table 3-3: Important ConnectionString Parameters

NAME	DEFAULT	DESCRIPTION
Data Source/Server	The name or network address of the instance of SQL Server to which to connect. Use an Internet Protocol (IP) address instead of a Domain Name System (DNS) name. By using an IP address, the DNS name does not have to resolve, which may reduce the amount of time it takes to connect. In addition, problems with DNS name resolution will not interfere with data access.
Initial Catalog/Database	The name of the database with which to connect.
User ID	The SQL Server login account.
Password/ Pwd	The password for the SQL Server login account.
Connect Timeout/Connection Timeout	15	The length of time (in seconds) to wait for a connection to the server before terminating the attempt and generating an error. You should never use a value of zero (0) because this may cause an indefinite wait if the database server is unresponsive .
Integrated Security /Trusted_Connection	"False"	Indicates whether the connection is trusted, which uses Windows Authentication credentials. To specify a trusted connection, set this parameter value to SSPI and omit the security credentials (name and password). Integrated security requires additional administrative work, as described in the "Using SQL Server 2000 Trusted Connections" section.
Enlist	"True"	Transaction context setting. When "True," the connection pooler automatically enlists the connection in the creation thread's current transaction context. For better performance, set to "False" if the application is not using transactions.
Packet Size	8192	The size in bytes of the network packets used to communicate with an instance of SQL Server. You can adjust this parameter value based on the size of the data packets being transferred between the client and the server. This parameter can help optimize the communication performance. The range is 512 “32, 767 bytes.

You can specify every connection parameter you might need in the connection string parameter. In addition, the Connection object exposes dedicated properties for a select group of parameters, including the DataSource , Database , ConnectionTimeout , and Packet Size parameters. To avoid confusion, we prefer to set all connection parameters in the connection string only.

The following example of a ConnectionString property for a SQL Server data source does not require transactional support:

 Imports System.Data.SqlClient Dim strConn As String strConn = "server=192.168.64.84;uid=apress_dev;pwd=apress_pwd;database=apress;" & _          connect timeout=300;enlist=false;" Dim sqlConn As SqlConnection = New SqlConnection(strConn) 

Much attention has been given to the fact that the .NET managed runtime environment automatically handles garbage collection. This leads some developers to erroneously assume that they do not need to close their database connections. This is not true, although developers are allowed to omit code that explicitly clears object references. However, just because this is allowed does not mean it is the best way. For optimum performance you should always explicitly clear unused object references so that they do not needlessly take up space on the stack for the finite amount of time that it takes the garbage collector to recognize that an object reference is going unused.

Closing the Connection

Connections are not implicitly released when the Connection object falls out of scope or is reclaimed by garbage collection. You must always close the connection when you are finished using it, by invoking the Connection object's Close() method. The Close() method releases the connection and returns it back to the pool if connection pooling is enabled. Otherwise , this method simply destroys the connection.

Keep in mind that different ADO.NET objects require the connection to remain open for different lengths of time. When a DataAdapter object opens a connection to fill a DataSet, the connection can be closed as soon as the DataSet has been filled. However, when the DataReader opens the connection, it must remain open throughout the lifetime of the DataReader. If two DataReader objects attempt to use the same open connection, then the second DataReader object will raise a SqlException indicating that the connection is already in use.

Now let's look at how you can optimize a database connection even further by taking advantage of connection pooling.

Connection Pooling

The .NET managed provider automatically provides connection pooling for your data access code. Connection pooling involves creating and maintaining a group (pool) of connections that can be handed out to applications that request a database connection. The time it takes to grab a connection from the pool is almost instantaneous compared to the seconds worth of delay it may take to establish a new connection over the network. The ConnectionString property includes several parameter settings that control and optimize the behavior of the connection pool. Table 3-4 provides a summary of the parameters that control connection pooling behavior.

Table 3-4: ConnectionString Parameters for Connection Pooling

NAME	DEFAULT	DESCRIPTION
Pooling	"True"	When "True," the connection is drawn from the appropriate pool or, if necessary, created and added to the appropriate pool. If "False," then connection pooling is disabled.
Connection Lifetime		When a connection is returned to the pool, its creation time is compared with the current time, and the connection is destroyed if that time span (in seconds) exceeds the value specified by Connection Lifetime . A value of zero (0) will cause pooled connections to never timeout as long as users are actively connecting to the system.
Connection Reset	"True"	Determines whether the database connection is reset when being removed from the pool. For Microsoft SQL Server version 7.0, setting to "False" avoids making an additional server round trip when obtaining a connection, but you must be aware that the connection state, such as database context, is not being reset.
Max Pool Size	100	The maximum number of connections allowed in the pool.
Min Pool Size		The minimum number of connections maintained in the pool.

To get the most out of connection pooling, keep the following important facts in mind as you develop your data access code:

• Open a connection right before you need it, not earlier.

• Close a connection as soon as you are done using it. Do not leave a connection open if it is not being used.

• Clear references to unused connection objects.

• Complete any pending transactions before closing a connection.

• You can disable connection pooling (using the Pooling parameter).

• Connections are only given from the pool if they match a prior connection string exactly. Even whitespace variations between connection strings will cause the pool to treat these as different connections.

• Pooled connections will continue to be returned from the pool for as long as the Connection Lifetime parameter will allow. Set this parameter to a higher value if you do not expect connection details to change in the short term .

• Active connections are not shared. A connection will only be given to a new requestor when the previous connection owner has called its Close() method.

Using SQL Server 2000 Trusted Connections

Earlier in this chapter, we showed how you can store connection string information in the Web.config file and then retrieve it dynamically at runtime. The Web.config file is protected from outside browsing, but it still stores its information as plain text, including passwords. Some users may feel uncomfortable with this potential security liability and may prefer an alternative approach for connecting to a database.

If your Web application supports Windows authentication and uses SQL Server 2000, then you can connect to the database without a connection string. The alternative is to request a trusted connection from SQL Server using authenticated Windows credentials. You implement trusted connections as follows :

1. The database administrator specifies Windows accounts or groups that are permitted to access the SQL Server database. Each Windows account must be mapped to an appropriate SQL Server account or database role. For example, read-only users are given a specific Windows account that maps to a SQL login account with read-only access.

2. The user signs on to the application using Windows credentials and then makes a request to the database.

3. Windows will not open a trusted connection unless the user is currently authenticated.

4. SQL Server matches the authenticated Windows account against the list of accounts to which it permits access. Once a match is made, SQL Server will process the database request.

A trusted connection (also known as integrated security ) allows a client to connect to a SQL Server 2000 database without having to specify security credentials, such as the name and password. You still need to specify a connection string, but it omits the security credentials. The following is one example of a connection string, as it appears in the Web.config file:

 <configuration>          <appSettings>                   <add key=" ConnectionString" value=" server=192.168.1.1;                        Integrated Security=" SSPI";database=dev;"  />          </appSettings> </configuration> 

Internet Information Server (IIS) automatically uses a machine-level Windows account to impersonate anonymous Web users. For secure operations you can extend this system to require that all users authenticate themselves using a domain-level Windows account. The simplest approach is to set up a limited number of Windows accounts that map to specific types of database operations. For example, you could specify one account for read-only operations and one account for read/write operations. A certain amount of application logic may be required if you support multiple Windows accounts for different operations and then need to dynamically switch users between read-only access vs. read/write access.

Having a limited number of accounts not only simplifies the administrative work, but it also helps overcome an important limitation of trusted connections with respect to connection pooling. Namely, connection pools only work for individual accounts and will not work across multiple, separate accounts. If you want your trusted users to pull connections from the same pool, then they will need to be authenticated under the same domain-level Windows account. In addition, this domain account must include both IIS and SQL Server in the same domain or in a trusted domain.

Windows authentication is actually the default authentication mode for SQL Server 2000. Mixed-mode authentication is a hybrid approach that uses either SQL login credentials or Windows authentication credentials. In mixed-mode authentication, the SQL login credentials will be used if they are supplied and if Windows authentication has not been specifically requested via the connection string.

Using the Command Class

The Command class executes stored procedures and Transact -SQL statements. It provides several dedicated methods for executing different types of queries:

• ExecuteReader(): This is optimized for retrieval queries that will return one or more records. This method returns the resultset as a DataReader object, which provides a forward-only data stream.

• ExecuteScalar(): This is optimized for queries that return scalar values. It returns the resultset as a singleton value. This method will return only the first field value in the first row in the event that multiple fields and/or records are returned. Because .NET enforces strict data typing rules, you must cast the scalar value to the appropriate data type before working with it.

• ExecuteNonQuery(): This is optimized for Transact-SQL statements. It returns the number of rows affected for UPDATE , INSERT , and DELETE operations; otherwise, it returns -1.

• ExecuteXmlReader(): This is optimized for SQL Server stored procedures that return the resultset as XML, using the FOR XML clause. This method returns a forward-only XML data stream that may be accessed using an XmlReader object.

The Command class provides an overloaded New() constructor for initializing the object. It may include a Connection object reference directly as follows:

 Dim sqlConn As SqlConnection = New SqlConnection(  <Connection String>  ) Dim sqlCmd As SqlCommand = New SqlCommand(cmdText, sqlConn) 

The cmdText parameter is the query to be executed: either the name of the stored procedure or the Transact-SQL string. The Command class requires more initialization information than you can add in the New() constructor. Optimally, you should always specify the CommandType property, which indicates what kind of query you will be executing. For example, Listing 3-1 shows how to use the SqlCommand object's SqlCommand.ExecuteReader() method to execute a query and return a SqlDataReader object.

Listing 3-1: Using the Command Object's ExecuteReader() Method

 Dim sqlDR As SqlDataReader Dim sqlConn As SqlConnection = New SqlConnection(  <Connection String>  ) Dim sqlCmd As SqlCommand = New SqlCommand("CustOrderHist", sqlConn) Try     'Specify the stored procedure and connection     With sqlCmd         .CommandType = CommandType.StoredProcedure         .CommandBehavior = CommandBehavior.CloseConnection         .CommandTimeout = 60     End With     sqlDR = sqlCmd.ExecuteReader() Finally End Try 

 

This code includes the CommandBehavior enumeration, which indicates how the Command object should return a resultset and how the Command object should behave once the query has been executed and the return resultset is no longer needed. For example, a value of CloseConnection indicates that the connection should be automatically closed once the associated data object has been closed (in this case, the DataReader object). You should always set this property, especially when you are retrieving an object such as the DataReader, which should never leave a connection open. Table 3-5 summarizes the members in the CommandBehavior enumeration.

Table 3-5: CommandBehavior Enumeration Members

MEMBER	DESCRIPTION
CloseConnection	When the command is executed, the associated Connection object is closed when the associated DataReader object is closed.
SchemaOnly	The query returns schema (column) information only.
SequentialAccess	Provides a way for the DataReader to handle rows that contain columns with large binary values. SequentialAccess enables the DataReader to load each row of data as a binary stream. You can then use the GetBytes() or GetChars() method to specify a byte location to start the read operation and a limited buffer size for the data being returned. SequentialAccess allows you to choose any starting read position in the row. However, once you have read past a location in the returned stream of data, data at or before that location can no longer be read from the DataReader.
SingleResult	The query returns a single result set.
SingleRow	The query is expected to return a single row. You can still return multiple resultsets, but each one will only contain a single row.
Default	Use this setting when you expect the query to affect the state of the database, such that you cannot determine in advance what to do with the Command and Connection objects following completion of the query. Default sets no CommandBehavior flags, so ExecuteReader(CommandBehavior.Default) is functionally equivalent to calling ExecuteReader() .

Listing 3-1 also includes the CommandTimeout property, which it sets to a value of 60 seconds. This property sets the query timeout ”that is, the amount of time that the Command object will wait before aborting a query. It is important to set this property, especially when you anticipate variable delays in query processing time. If you do not set this property, then the command object will wait indefinitely until the database returns a result, and this can be confusing for the user, who may assume that the application has become unresponsive. Conversely, do not set the CommandTimeout property to an unreasonably low value; otherwise, the user may frequently encounter timeout exceptions, when in reality, you are not allowing enough time for the query to reasonably finish processing. Finally, do not get confused between the Connection object's ConnectionTimeout property and the Command object's CommandTimeout property. The former specifies the amount of time to allow a database connection to be established. The latter specifies the query timeout only.

Some stored procedures return output parameters in place of a resultset. If the Command object's CommandType property is set to CommandType.StoredProcedure , then the Parameters property allows you to access the input and output parameters and return values from the stored procedure. These parameters are accessible independent of the Execute() method called. However, in the case of ExecuteReader() , the output parameters and return values are not accessible until the DataReader is closed.

Output parameters are tricky to work with because you are required to specify the name and type of each output parameter in advance in order to capture their values. This makes code harder to create and harder to maintain, especially if the underlying stored procedure ever changes. If possible, avoid using output parameters in favor of using the ExecuteScalar() and ExecuteNonQuery() methods.

Using the ExecuteXmlReader() Method

To take advantage of SQL Server 2000's XML functionality, the SqlCommand object exposes the ExecuteXmlReader() method for retrieving an XML data stream. The ExecuteXmlReader() method returns a System.Xml.XmlReader object populated with the results of the SQL statement specified for a SqlCommand.

 Dim myXR As System.Xml.XmlReader myXR = sqlCmd.ExecuteXmlReader() ' The SqlCommand object is already initialized 

You can only use the ExecuteXmlReader() method for SQL Server stored procedures that return XML data using the FOR XML clause. The XmlReader provides forward-only, read-only access to the returned stream of XML data.

The XmlReader has the following advantages:

• The XmlReader is a streaming reader that provides fast access to an XML data stream. The XmlReader does not have to retrieve and cache all of the data before exposing it to the caller. This design is especially optimal for large returned XML documents.

• The XmlReader has methods and properties that can read and navigate the elements, attributes, and contents of the XML stream.

The XmlReader has the following disadvantages:

• The XmlReader maintains an open connection to the database until it is closed and the connection can be closed and released. The XmlReader's active connection is not available to the connection pool as long as the XmlReader remains open.

• The XmlReader requires additional processing on the database server to process the FOR XML clause and return data as XML.

Note

The sample project that accompanies this chapter contains complete code samples for each of the Command object's Execute*() methods. Please refer to the ExecSP* functions in the DataAccess component of the AspNetChap3 sample project.

Using the DataReader Class

All .NET data providers support the DataReader object, which is created by the Command object's ExecuteReader() method. The DataReader provides an unbuffered sequential stream of data for fast, efficient access to a resultset. The DataReader is a good choice for retrieving large amounts of data because the data is not cached in memory.

The DataReader object has the following advantages:

• A DataReader requires the lowest amount of memory and resource overhead, especially compared with the DataSet object. Lower overhead translates to increased application performance. By reading directly from the database, you can bypass storing data in a cached DataSet object, which requires memory overhead. Always use the DataReader object when you intend to use a resultset only once.

• The DataReader is optimized for read-only access, which is a common operation on data-driven Web sites.

• The DataReader provides schema information in addition to data. You can use the GetSchemaTable() method of the DataReader to obtain the column metadata.

• You can bind the DataReader to a wide range of ASP.NET Web controls.

The DataReader has the following disadvantages:

• The DataReader retains an active database connection for as long as it remains open. Minimize the amount of time you use an expensive Connection object. Open the connection right before the ExecuteReader() method, read the data off the DataReader, close the DataReader, and then immediately close the database connection.

• The DataReader requires an active database connection for access. It cannot be disconnected, cached, or serialized.

• The DataReader may cause contention in the connection pool if active connections are not used efficiently and closed in a timely manner. Contention is the state where the maximum number of requested connections exceeds the number of connections in the pool. Contention minimizes the overall responsiveness and scalability of the calling application.

• The DataReader is limited to forward-only, read-only data access. The DataReader cannot update records in the database, nor can it support complex operations such as sorting or directly accessing specific records in a resultset. (The latter example has one exception: DataReader objects may be opened using SequentialAccess, where they retrieve each record as a binary stream. Even so, data access is still forward only).

• The DataReader does not return data from multiple, non-joined database tables or from different data sources.

Accessing Data Within a DataReader

The DataReader object provides a Read() method for accessing each record of data in the resultset. Within each record (or row) you can access the fields either by column name or by ordinal reference, as shown here:

 Dim strResult As String While (sqlDR.Read())         strResult = sqlDR.GetString(0) & ": "& sqlDR("CustomerName") End While 

The DataReader object provides a set of typed accessor methods for accessing fields with known data types. If you know the underlying data type for a column value, then the typed accessor method lets you access this value in its native data type. For example, the GetDateTime() , GetFloat() , GetInt32() , and GetString() methods offer the best performance because they eliminate data type conversions when retrieving a column value.

The following code shows how you can retrieve a native integer field, then immediately cast the value to a string. Explicit casting is more efficient than assigning the integer field value to a string variable directly:

 Dim intResult As Int32 Dim strResult As String While (sqlDR.Read())      intResult = sqlDR.GetSqlInt32(0)      strResult = CType(intResult, String) ' Cast the integer to a string End While 

The SqlDataReader object (in contrast to the OleDbDataReader object) actually exposes two sets of type accessor methods. The first is a set of generic data access methods provided directly by the .NET Framework. These methods include GetString() and GetBoolean() , and they do not account for specific SQL Server data types. The second set of data access methods is provided by the Data.SqlClient namespace directly. These methods include GetSqlDouble() and GetSqlMoney() , and they account for specific SQL Server data types. These specialized data access methods allow you to work with these data types safely in the .NET managed environment.

Note

SQL Server and the .NET Framework use different typing systems.You may lose data if you attempt to map a SQL Server data type to a .NET data type. For this reason, the .NET Framework exposes a SqlDbType enumeration that allows managed code to reference SQL Server data types directly.

The .NET Framework's System.Data.SqlTypes namespace provides constructs for handling type conversions between SQL Server and the .NET Framework. It provides structures for mapping SQL Server data types to .NET. It also provides an enumeration for setting the SQL Server data type of input and output parameters that will be used when calling SQL Server stored procedures from .NET managed code.

Consider the Orders table in the Northwind database, which contains an integer field for the Order ID. The SQL Server data type for this field is int . You can declare a variable to reference this field's values using the SqlInt32 structure from the Data.SqlTypes namespace:

 Imports System.Data.SqlTypes Dim intOrderID As SqlInt32 

Now let's say you want to call a stored procedure that includes the Order ID as an input parameter. This is where you use the enumeration to assign the int SQL Server data types to the input parameter. For example:

 Dim myParameter As New SqlParameter("@OrderID", SqlDbType.Int) 

As if this was not enough, the SqlDataReader class also provides dedicated typed accessor methods that automatically convert the SQL Server data type to the appropriate .NET Framework data type. For example, you can capture a SQL Server int field from the DataReader using the GetSqlInt32() accessor method. This method in turn returns a data type represented in .NET by the SqlInt32 structure. For example:

 Imports System.Data.SqlTypes Dim intOrderID As SqlInt32 intOrderID = sqlDataReader.GetSqlInt32(0) 

Typed accessor methods promote type safety and prevent type conversion errors. You should always take advantage of the typed accessor methods when using the SqlDataReader object.

Note

For a complete list of typed accessor methods, refer to the .NET Framework Developer's Guide at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconmappingnetdataproviderdatatypestonetframeworkdatatypes.asp.

Note

For information on the System.Data.SqlTypes namespace, refer to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemDataSqlTypes.asp.

Data Binding Using a DataReader

The DataReader also supports binding to a data-oriented Web control in ASP.NET. Data controls can be single-value (Label), multi-value (ListBox), or multi-record (DataGrid). For multivalue and multirecord cases, the control will automatically loop through the data source during binding, reading each row in turn to retrieve the required data. The following code demonstrates how to bind a DataReader to a DataGrid Web control. Note that this code uses the wrapper functions that are included in the sample project:

 Dim sqlDR As SqlClient.SqlDataReader Dim DataGrid As System.Web.UI.WebControls.DataGrid Try      objDB = New Apress.Database(strConn)      arrParams = New String() {"@CustomerID", SqlDbType.Char, "ALFKI"}      sqlDR = objDB.ExecSPReturnDR("CustOrderHist", arrParams)      ' Bind the DataReader to the DataGrid      DataGrid1.DataSource = sqlDR      DataGrid1.DataBind() Finally End Try 

We will not discuss data binding in any detail because there is little to say in terms of optimizing this process. Data binding in ASP.NET is a faster, more efficient process than it has been in the past, but keep in mind that bound controls can generate a lot of output bytes during rendering. Be judicious about how much data you bind and, where possible, always disable the view state for a bound control. This is especially true for DataGrid controls, which can generate lengthy view state records. Always disable view state for controls that do not need to post their contents back to the server. This is almost always true for DataGrids that only display data and that do not support direct updates in the grid.

Using the DataAdapter Class

The DataAdapter class is the bridge between a disconnected DataSet object and a data source. Specifically, it has two main purposes:

• To retrieve data from a data source and populate the tables within a DataSet

• To resolve data modifications made in the DataSet with the data source

The DataAdapter uses the Connection object to connect to the data source. It uses the Command object to retrieve data from, and resolve data to, the data source. The DataAdapter actually provides four properties to assign Command objects: one property each for SELECT , UPDATE , INSERT , and DELETE commands. Table 3-6 defines these properties.

Table 3-6: DataAdapter Properties

PROPERTY	DESCRIPTION
SelectCommand	An auto-generated or user-defined Command object that retrieves data from a data source
InsertCommand	An auto-generated or user-defined Command object that inserts data from a data source
UpdateCommand	An auto-generated or user-defined Command object that updates data from a data source
DeleteCommand	An auto-generated or user-defined Command object that deletes data from a data source

The DataAdapter provides two main methods for selecting and updating data, as summarized in Table 3-7.

Table 3-7: DataAdapter Methods

METHOD	DESCRIPTION
Fill()	Populates a DataSet with the results from the SelectCommand
Update()	Resolves the changes made in the DataSet and executes the appropriate command (InsertCommand, UpdateCommand, DeleteCommand) on the data source

The DataAdapter Fill() method implicitly uses the SelectCommand property to retrieve the column names, data types, and data values to create and populate the tables in the DataSet. The following code shows how to populate a DataSet with a DataAdapter:

 ' Create and initialize a new DataAdapter object Dim sqlAdapt As SqlDataAdapter = New SqlDataAdapter() sqlAdapt.SelectCommand = sqlCmd ' Open the Connection object m_sqlConn.Open() sqlAdapt.Fill(sqlDS) m_sqlConn.Close() sqlAdapt = Nothing ' sqlAdapter is no longer needed 

(This code assumes that the Connection and Command objects have already been created and initialized, as shown in earlier sections). An alternative approach to this listing is to create the DataAdapter by passing the Command object into the New() method of the DataAdapter without explicitly setting the SelectCommand property:

 Dim sqlAdapt As SqlDataAdapter = New SqlDataAdapter(sqlCmd) 

The Command properties are auto-generated using the CommandBuilder class. Listing 3-2, which was taken from the UpdateCustomerProfile() function in the sample project's DataAccess class, shows how to do this. In this example, the DataAdapter Update() method call would have failed had the UpdateCommand property not been auto-generated using the SqlCommandBuilder class.

Listing 3-2: Updating the Database Using the SqlCommandBuilder Class

 Try      Dim sqlAdapt As SqlDataAdapter = New SqlDataAdapter(sqlCmd)      Dim custCB As SqlCommandBuilder = New SqlCommandBuilder(sqlAdapt)      ' Open the Connection object and populate the DataSet      m_sqlConn.Open()      sqlAdapt.Fill(sqlDS, "Customers")      m_sqlConn.Close()      ' Code to modify data in DataSet here      Call UpdateData(sqlDS, arrParams)      'Update method would have failed without the SqlCommandBuilder      sqlAdapt.Update(sqlDS, "Customers")      m_sqlConn.Close() Finally End Try 

 

You can also specifically define the Command properties associated with the Update() method at design-time as well as associate them with a set of stored procedures. This will provide some level of performance advantage compared to setting these properties at runtime.