The ADO.NET Object Family

The ADO.NET types are a part of the .NET Framework class library and are found in the namespaces that begin with System.Data.

Here's the 60-second overview:

  • System.Data contains generic classes that are independent of the type of data source or the mechanism used to connect to it. The most important of these is the DataSet, which contains collections of other generic classes such as DataTable, DataRow, and DataColumn. There are also a number of generic interfaces (such as IDbCommand and IDbConnection) that are implemented by all provider-specific objects.

  • System.Data.SqlClient contains the classes required to connect to a Microsoft SQL Server (7.0 or later) database using the highly efficient Tabular Data Stream (TDS) interface. In other words, these namespaces bypass several other potential layers, including OLE DB and ODBC. You'll use these classes to execute a direct command on a SQL Server database or to fill a DataSet with the results of a query.

  • System.Data.OleDb contains the classes required to connect to most OLE DB providers. (OLE DB providers are included for all kinds of data sources, including Microsoft Access, SQL Server, and Oracle.) You can use these classes to execute a direct command on a data source through an OLE DB provider or to fill a DataSet with the results of a query.

Version 1.0 of the .NET Framework includes only these two providers: one that's generic for all OLE DB providers and one that's optimized for SQL Server. However, version 1.1 of the .NET Framework and Visual Studio .NET 2003 add the following new providers:

  • System.Data.OracleClient contains the classes required to connect to an Oracle database (version 8.1.7 or later) using the optimized Oracle Call Interface (OCI).

  • System.Data.Odbc contains the classes required to connect to most ODBC drivers' providers. ODBC drivers are included for all kinds of data sources and are configured through the Data Sources icon in Control Panel.

In addition, other vendors are sure to develop their own managed providers. The best strategy for the application programmer is to use the managed provider that's customized for the data source, if it's available, and thereby bypass as many intermediate layers as possible. Keep in mind that if you install a third-party provider, it is installed (generally in the GAC) as a separate DLL assembly, and you must reference this assembly before you can use the contained types. Third-party providers also won't be placed in a System.Data namespace (because System is reserved for the .NET Framework class library).

The Data Objects

At first, it might seem that Microsoft has broken with its long-standing tradition of cross-data-source compatibility. How else can you explain a separate namespace (and potentially a separate assembly) for every type of provider?

On closer examination, ADO.NET is generic in two ways:

  • Although separate providers are required to connect to the data source, the DataSet object is generic. Therefore, a client can navigate and update a DataSet regardless of how it was created or where the information originated. All the details that are specific to the data provider can be encapsulated by a custom database component that you create.

  • The provider-specific classes all inherit from a common class (in the System.Data.Common namespace) or implement a common interface (from the System.Data namespace). Therefore, the SqlConnection object exposes virtually the same properties and methods as the OleDbConnection and OdbcConnection objects. If you need to work with these objects generically in code, you can cast them to the appropriate interface (such as IDbConnection).

In some cases, classes or members are not directly inherited or implemented from a common base class or interface. (One example is the SqlCommandBuilder and OleDbCommandBuilder classes.) In this case, you'll still find that the classes included with different providers work essentially the same and almost always include properties and methods with exactly the same names. That means that examples written with one provider are easily translatable to another. Connection strings are one of the minor discrepancies.

So what are the basic ADO.NET objects? I won't describe them one by one here, but for ADO.NET novices, a quick overview is provided in Table 3-1.

Table 3-1. ADO.NET Core Provider Classes

Function

SQL Server Provider

OLE DB Provider

Oracle Provider

ODBC Provider

Connect to a data source

SqlConnection

OleDbConnection

OracleConnection

OdbcConnection

Execute a SQL statement

SqlCommand

OleDbCommand

OracleCommand

OdbcCommand

Execute an SQL stored procedure

SqlCommand (with any number of referenced SqlParameter objects)

OleDbCommand (with any number of referenced OleDbParameter objects)

OracleCommand (with any number of referenced OracleParameter objects)

OdbcCommand (with any number of referenced OdbcParameter objects)

Retrieve data with a fast- forward read-only

SqlDataReader

OleDbDataReader

OracleDataReader

OdbcDataReader

Transfer data to a DataSet

SqlDataAdapter

OleDbDataAdapter

OracleDataAdapter

OdbcDataAdapter

Apply DataSet changes to a data source

SqlDataAdapter (and, optionally, the SqlCommandBuilder helper class)

OleDbDataAdapter (and, optionally, the OleDbCommand­Builder helper class)

OracleDataAdapter (and, optionally, the OracleCommand­Builder helper class)

OdbcDataAdapter (and, optionally, the Odbc­CommandBuilder helper class)

Manually initiate and manage a transaction

SqlTransaction

OleDbTransaction

OracleTransaction

OdbcTransaction

Catch an error from the data source (anything from invalid SQL to an error connecting)

SqlException

OleDbException

OracleException

OdbcException

Direct Data Source Interaction

The easiest form of access with ADO.NET is simple, connection-based access. With connection-based access, you have one of the following goals:

  • Execute a direct SQL Update, Delete, or Insert command.

    You'll need to use the appropriate IDbConnection and IDbCommand objects.

  • Execute a query and retrieve the results as a read-only forward-only stream.

    You'll need to use the appropriate IDbConnection, IDbCommand, and IDataReader objects.

The drawback of this strategy is that substantial data manipulation can result in substantial connection requests, potentially creating a bottleneck. For modest database usage, however, the simplicity and straightforwardness of this model outweighs most other considerations. Also, most ADO.NET providers support some form of connection pooling, which allows them to efficiently handle a large volume of short-lived requests.

The example in Listing 3-1 shows how the CustomerDB service provider class can use the ADO.NET objects to support record deletions, insertions, and updates.

Listing 3-1 Adding ADO.NET code to the CustomerDB class
 Imports System.Data.SqlClient Imports System.Data Public Class CustomerDB     Private ConnectionString As String = _      "Data Source=localhost;Initial Catalog=Store;" & _      "Integrated Security=SSPI"     ' (GetCustomer and GetCustomers methods left out.)     ' Inserts a customer record into the database.     Public Sub AddCustomer(ByVal customer As CustomerDetails)         Dim Sql As String = "INSERT INTO Customers "         Sql &= "(FullName, EmailAddress, Password) VALUES ('"         Sql &= customer.Name & "', '"         Sql &= customer.Email & "', '"         Sql &= customer.Password & "')"         ExecuteNonQuery(Sql)     End Sub     ' Updates an existing customer record in the database.     Public Sub UpdateCustomer(ByVal customer As CustomerDetails)         Dim Sql As String = "UPDATE Customers SET "         Sql &= "FullName='" & customer.Name         Sql &= "', EmailAddress='" & customer.Email         Sql &= "', Password='" & customer.Password         Sql &= "' WHERE CustomerDELETE FROM Customers WHERE Customercaller inform" exception handling pattern.             Throw New ApplicationException( _              "Exception encountered when executing command.", Err)         Finally             con.Close()         End Try     End Sub   End Class 

The CustomerDB class is organized so that a private ExecuteNonQuery method performs the database operation for all three tasks. The only difference is the supplied SQL text in this design, the methods of the CustomerDB class just generate a dynamic SQL statement that identifies the field names and values. Using the ADO.NET objects, a new connection is opened, a direct command is executed, and the connection is closed immediately.

If an exception is encountered, the class doesn't try to remedy it because it probably indicates that the database is unavailable or the SQL text is invalid. Instead, an ApplicationException is thrown to the client, with the original SqlException packaged inside. This pattern is called caller inform because it ensures that the caller is notified that an unrecoverable error took place, but it uses a higher-level exception class that the client expects. This higher-level exception class could also be a custom exception class that you've defined.

With the caller inform pattern, the ApplicationException always includes the original Exception object nested inside. This approach can help the client diagnose the problem, but it might not be suitable in a secured environment, in which specific error information could inform a malicious user about potential vulnerabilities. In this case, create the ApplicationException object without nesting the original exception inside:

 Throw New ApplicationException( _   "Exception encountered when executing command.") 

Connection Strings

Connection strings generally detail three types of information: what server to use, what database to use, and how to authenticate the connection. There also might be any number of additional vendor-specific settings.

In Listing 3-1, we use the localhost alias (which always refers to the current computer), the database named Store, and integrated security (which uses the currently logged-in Windows user to access the database).

 Private ConnectionString As String = _  "Data Source=localhost;Initial Catalog=Store;" & _  "Integrated Security=SSPI" 

If integrated security isn't supported, the connection must indicate a valid user and password combination. For a newly installed SQL Server database, the sa (system administrator) account is usually present, and often without a password.

 Private ConnectionString As String = _  "Data Source=localhost;Initial Catalog=Store;" & _  "user id=sa;password=" 

If you're using the OLE DB provider, your connection string probably resembles the preceding example, with the addition of a Provider setting that identifies the OLE DB driver (or if you're using the ODBC .NET driver, you add a Driver attribute that identifies the DSN). The following connection string can be used to connect to an Oracle database through the MSDAORA OLE DB provider:

 Private ConnectionString As String = _  "Data Source=localhost;Initial Catalog=Store;" & _  "user id=sa;password=;Provider=MSDAORA" 

If you're using a third-party data provider, you might need to consult its documentation (or the MSDN class library reference) to determine the supported connection string values. For example, most databases support the Connect Timeout setting, which sets the number of seconds to wait for a connection before throwing an exception. (The SQL Server default is 15 seconds.)

Direct Queries and Data Readers

Queries are performed in a slightly different way. The SQL Select statement is executed through the command's ExecuteReader method, which returns a data reader. You can then step through the rows returned by the data reader one at a time from start to finish, using the Read method. The GetCustomer and GetCustomers methods shown in Listing 3-2 demonstrate this technique and create CustomerDetails objects to encapsulate the retrieved details. Lookup is based on customer ID, but you could make overloaded versions of this method that enable you to search by other criteria (such as the FullName field).

Listing 3-2 Adding query methods to CustomerDB
 Public Class CustomerDB     ' (Update, insert, and delete methods omitted.)     ' Retrieves a single customer record from the database.     Public Function GetCustomer(ByVal customerID As Integer) _      As CustomerDetails         Dim Sql As String = "SELECT * FROM Customers Where CustomerCustomerID")             Customer.Name = Reader("FullName")             Customer.Email = Reader("EmailAddress")             Customer.Password = Reader("Password")         Catch Err As Exception             ' Use caller inform pattern.             Throw New ApplicationException( _              "Exception encountered when executing command.", Err)         Finally             con.Close()         End Try 
         Return Customer     End Function     ' Retrieves all customer records into an ArrayList collection.     ' Note that exposing this method allows client to invoke a     ' potentially time-consuming query.     Public Function GetCustomers() As ArrayList         Dim Sql As String = "SELECT * FROM Customers"         Dim con As New SqlConnection(ConnectionString)         Dim cmd As New SqlCommand(Sql, con)         Dim Reader As SqlDataReader         Dim Customers As New ArrayList()         Try             con.Open()             Reader = cmd.ExecuteReader()             Do While Reader.Read()                 Dim Customer As New CustomerDetails()                 Customer.ID = Reader("CustomerID")                 Customer.Name = Reader("FullName")                 Customer.Email = Reader("EmailAddress")                 Customer.Password = Reader("Password")                 Customers.Add(Customer)             Loop         Catch Err As Exception             ' Use caller inform pattern.             Throw New ApplicationException( _              "Exception encountered when executing command.", Err)         Finally             con.Close()         End Try         Return Customers     End Function   End Class 

Note that the GetCustomer method passes a special CommandBehavior.SingleRow parameter to the ExecuteReader method. This isn't required, but it does represent a little-known technique that can improve performance if you know you're retrieving only a single row. In this case, the code selects a record using its unique key number, which ensures that only a single item is returned.

Notice also that the GetCustomers method returns an ArrayList full of CustomerDetails objects. Alternatively, you can create a strongly typed custom collection class, such as the one shown in Listing 3-3.

Listing 3-3 A custom collection for CustomerDetails
 Public Class CustomerDetailsCollection     Inherits System.Collections.CollectionBase     Public Sub Add(ByVal customer As CustomerDetails)         Me.List.Add(customer)     End Sub     Public Sub Remove(ByVal Index As Integer)         ' Check to see if there is an item at the supplied index.         If Index > Count - 1 Or Index < 0 Then             Throw New System.IndexOutOfRangeException()         Else             List.RemoveAt(Index)         End If     End Sub     Public ReadOnly Property Item(ByVal Index As Integer) _      As CustomerDetails         Get             ' The appropriate item is retrieved and              ' explicitly cast to the CustomerDetails type.             Return CType(List.Item(Index), CustomerDetails)         End Get     End Property End Class 

This technique ensures that the collection can only contain Customer­Details objects, not any other type of object. The remarkable fact is that both the custom collection class and the ArrayList intrinsically support ASP.NET and Windows data binding. That means it takes just a few lines of code to display the results you retrieve without needing to iterate through each item. (As a disadvantage, it's usually harder to configure specific formatting details such as column order and size.)

Therefore, you can create a scalable database component that executes direct commands, returns custom objects, and even allows data binding, without resorting to the DataSet. Listing 3-4 shows the code for a simple form that uses the CustomerDB service provider class in this fashion.

Listing 3-4 A data bound form that uses CustomerDB
 Public Class BoundForm     Inherits System.Windows.Forms.Form     Friend WithEvents DataGrid1 As System.Windows.Forms.DataGrid     ' (Designer code omitted.)     Private Sub BoundForm_Load(ByVal sender As System.Object, _       ByVal e As System.EventArgs) Handles MyBase.Load         Dim DB As New CustomerDB()         DataGrid1.DataSource = DB.GetCustomers()     End Sub End Class 

Figure 3-1 shows the sample output that displays when this form is used with the CustomerDB component.

Figure 3-1. Data binding without a DataSet object

graphics/f03dp01.jpg

Note

In this discussion, you saw two command methods, ExecuteReader (for running queries and returning a set of rows) and ExecuteNonQuery (which returns only the number of affected rows). You can also use a third method, ExecuteScalar, which returns a single value. ExecuteScalar is ideal for using aggregate SQL functions (such as Sum, Avg, Min, Max, and Count) or for calling a stored procedure that returns a single data value.


Stored Procedures

Stored procedures miniature SQL "programs" that are stored in the database are a key part of any enterprise programming project. If you study Microsoft's platform samples (such as the IBuySpy case studies for ASP.NET), you'll see that they advocate a disciplined multilayered approach. The client Web page code interacts exclusively with a dedicated custom data component, which is similar to the CustomerDB class shown previously. The data component interacts with the data source through a layer of stored procedures, which in turn manipulate the underlying tables and records. This structured approach is shown in Figure 3-2.

Figure 3-2. The ideal structured approach to data access

graphics/f03dp02.jpg

Some key advantages to stored procedures include the following:

  • They reduce the amount of data access code.

    With stored procedures, work is offloaded to the server, simplifying your .NET code.

  • They improve encapsulation.

    By using stored procedures, you can tighten security (by allowing access to certain specific procedures instead of granting full access to the whole underlying table) and combine a batch of commands into a single logical task. You can also modify and tweak this logic to fine-tuned perfection after the application has been deployed, without needing to recompile the client or data access components.

  • They often improve performance.

    Performance improves because the database can create an optimized execution path when stored procedures are created, instead of performing the same work each time they are executed.

The last point depends on the database itself. For example, in SQL Server 2000, dynamic queries are compiled and cached much as stored procedures are. However, in many cases, stored procedures can still improve performance. One reason is that a single stored procedure can often replace dozens of differently structured (but similar) dynamic SQL queries. It's much easier to optimize a small set of stored procedures and create the optimal set of indexes than to sort through dozens of dynamic queries that are embedded into various applications. For more information about database optimization, refer to Chapter 12.

Generally, stored procedures take a little more effort because you need to work with the script-based SQL language. Listing 3-5 shows an example of a stored procedure that inserts a new customer record and then returns the automatically generated CustomerID.

Listing 3-5 A stored procedure for adding a customer record
 CREATE Procedure CustomerAdd (     @FullName   nvarchar(50),     @Email      nvarchar(50),     @Password   nvarchar(50),     @CustomerID int OUTPUT ) AS INSERT INTO Customers (     FullName,     EMailAddress,     Password ) VALUES (     @FullName,     @Email,     @Password ) SELECT     @CustomerID = @@Identity 

You can easily rewrite the AddCustomer method to use this stored procedure. As an added frill, this method now returns the unique CustomerID. (See Listing 3-6.)

Listing 3-6 Using the stored procedure in CustomerDB
 Public Class CustomerDB     ' (Other methods left out.)     Public Function AddCustomer(ByVal customer As CustomerDetails) _      As Integer         ' Create the ADO.NET Connection and Command.         Dim con As New SqlConnection(ConnectionString)         Dim com As New SqlCommand("CustomerAdd", con)         com.CommandType = CommandType.StoredProcedure         Dim Param As SqlParameter         ' Add the three input parameters.         Param = com.Parameters.Add( _          "@FullName", SqlDbType.NVarChar, 50)         Param.Value = CustomerDetails.Name         Param = com.Parameters.Add("@Email", SqlDbType.NVarChar, 50)         Param.Value = CustomerDetails.Email         Param = com.Parameters.Add("@Password", SqlDbType.NVarChar, 50)         Param.Value = CustomerDetails.Password         ' Add the output parameter.         Param = com.Parameters.Add("@CustomerID", SqlDbType.Int)         Param.Direction = ParameterDirection.Output         Try             con.Open()             cmd.ExecuteNonQuery()         Catch Err As Exception             ' Use "caller inform" exception handling pattern.             Throw New ApplicationException( _              "Exception encountered when executing command.", Err)         Finally             con.Close()         End Try         ' Return the unique ID generated by the database.         Return Param.Value     End Function End Class 

As you can see, the use of stored procedures forces your code to specify more database details (such as the data types of various parameters). By using a disciplined database component, however, you can still maintain order.

Provider-Agnostic ADO.NET Code

It is possible to create a custom data component that can transparently work with any type of ADO.NET provider, by performing all your database operations through the generic interfaces in the System.Data namespace. This approach isn't always best occasionally it might surrender some provider-specific functionality you need but it can be indispensable in projects where more than one data source stores similar tables with different information or where you plan to migrate from one database product to another.

The basic approach is to start by creating the provider-specific command object that you need to use, with a small piece of conditional logic. From that point, you can create commands, data readers, and parameters, all by relying on methods and using the generic interfaces.

Listing 3-7 shows how you can apply this technique to the GetCustomers method. Note that the class constructor accepts an enumerated value that indicates the provider type. This allows the client to decide, at run time, which provider to use. The conditional portion of logic that creates the first provider-specific object is placed in a private CreateConnection function.

Listing 3-7 CustomerDB with generic ADO.NET code
 Imports System.Data.SqlClient Imports System.Data.OleDb Imports System.Data Public Class CustomerDB     ' (Other methods omitted.)     ' Defines the supported providers types.     Public Enum ProviderType         SqlServer         OleDb     End Enum     ' The provider this class will use.     Public Provider As ProviderType     Private SqlConnectionString As String = _      "Data Source=localhost;Initial Catalog=Store;" & _      "Integrated Security=SSPI" 
     Private OleDbConnectionString As String = _      "Data Source=localhost;Initial Catalog=Store;" & _      "Integrated Security=SSPI;Provider=SQLOLEDB"     Public Sub New(provider As ProviderType)         Me.Provider = provider     End Sub     Public Function GetCustomers() As ArrayList         Dim Sql As String = "SELECT * FROM Customers"         ' The connection object is generated through a private         ' function.         Dim con = CreateConnection()         ' Interact with the command and data reader          ' using the IDbCommand and IDataReader interfaces.         Dim cmd As IDbCommand = con.CreateCommand()         Dim Reader As IDataReader         Dim Customers As New ArrayList()         Try             con.Open()             Reader = cmd.ExecuteReader()             Do While Reader.Read()                 Dim Customer As New CustomerDetails()                 Customer.ID = Reader("CustomerID")                 Customer.Name = Reader("FullName")                 Customer.Email = Reader("EmailAddress")                 Customer.Password = Reader("Password")                 Customers.Add(Customer)             Loop         Catch Err As Exception             ' Use caller inform pattern.             Throw New ApplicationException( _              "Exception encountered when executing command.", Err)         Finally             con.Close()         End Try         Return Customers     End Function     Private Function CreateConnection As IDbConnection         ' Here we determine which object and connection string to use.         ' This is the only provider-specific part of the code.         Select Case Provider             Case ProviderType.SqlServer                 Return New SqlConnection(SqlConnectionString)             Case ProviderType.OleDb                 Return New OleDbConnection(SqlConnectionString)         End Select     End Function   End Class 

A separate connection string is required for each data source. If you're using SQL Server, you can test this sample by connecting to the database through the SQL managed provider or the OLE DB provider for SQL Server (SQLOLEDB).

This approach does have some limitations many of which Microsoft architects are working to remedy in future versions of .NET. Some of the problems include objects that don't use a common interface (like the command builders) and the use of provider-specific exceptions (such as SqlException and OleDbException).

Transactions

When many developers hear the word transaction, they automatically think of COM+ services or its predecessor, the curiously named Microsoft Transaction Service (MTS). That's because one of the early successes of MTS and COM+ was a new model for transaction enlistment that made it possible to create distributed transactions that could bind different data sources into one logical operation. This service is priceless when you need to integrate legacy systems and multiple database products in an evolving system. (In Chapter 9, we'll explore this service in more detail.)

However, you won't use COM+ distributed transactions when designing a new system. Instead, you'll rely on ordinary SQL transactions that are initiated and governed at the database level. This approach offers better performance and easier programming.

The best way to create a transaction is to encapsulate it entirely within a stored procedure. In that case, you won't see it in your client code. Listing 3-8 shows the basic structure used to initiate a transaction in SQL Server.

Listing 3-8 A transaction in stored procedure code
 BEGIN TRANSACTION    < SQL code here > IF (@@ERROR > 0)    ROLLBACK TRANSACTION ELSE   COMMIT TRANSACTION 

You can also initiate the same type of transaction programmatically through .NET code. The disadvantage with this approach is that it requires the good behavior of the client. In other words, if a client forgets to initiate a transaction, it has the ability to perform an inconsistent update.

ADO.NET has provider-specific IDbTransaction objects that manage client-initiated transactions. You don't create this object directly; instead, you call the BeginTransaction method of the connection object. You can then enlist multiple commands and commit or roll back the transaction using the IDbTransaction methods when complete. Listing 3-9 shows a client-initiated transaction.

Listing 3-9 A client-initiated transaction
 Dim Transaction As SqlTransaction = con.BeginTransaction() ' Enlist two commands (cmdA and cmdB) in the transaction. ' They will now succeed or fail as a unit. cmdA.Transaction = Transaction cmdB.Transaction = Transaction ' Execute the commands. cmdA.ExecuteNonQuery() cmdB.ExecuteNonQuery() ' Commit the transaction. Transaction.Commit() 


Microsoft. NET Distributed Applications(c) Integrating XML Web Services and. NET Remoting
MicrosoftВ® .NET Distributed Applications: Integrating XML Web Services and .NET Remoting (Pro-Developer)
ISBN: 0735619336
EAN: 2147483647
Year: 2005
Pages: 174

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