OOP: Building Reusable Components with Microsoft® Visual Basic® .NET

Author Ken Spencer, Tom Eberhard, and John Alexander Pages 528 Disk N/A Level Int/Adv Published 10/09/2002 ISBN 9780735613799 ISBN-10 0-7356-1379-6 Price(USD) $49.99 To see this book's discounted price, select a reseller below.	More Information About the Book Table of Contents Sample Chapter Index Companion Content Related Series Related Books About the Author Support: Book & CD Rate this book

Chapter 4: Implementing the Data Access Layer

• The Power of ADO.NET
• Performance
• Scalability
• XML Equals Interoperability
• Ease of Use
• Components of ADO.NET
• Connection Object
• Command Object
• DataReader Object
• DataSet Object
• The ADO.NET Data Access Object
• Why Have a Data Access Object?
• What Will the Data Access Object Do?
• Our Approach to Database Access and Usage
• Implementing the Data Access Object
• The Data Access Object as a Component
• Private Variables and Objects
• Constructors
• Properties
• The First Method: Running an SQL Statement and Returning a DataSet
• Exception Handling
• Logging Exceptions
• What Else?
• The Complete Code for the runSQLDataSet Method
• Using the Data Access Object
• Additional Questions About Data Access
• How Do We Handle Transactions?
• What About Data Concurrency?
• What About Connection Pooling?
• Can We Use Performance Counters?
• Conclusion

4  Implementing the Data Access Layer

When writing a data access component, we learn new data features of ADO.NET, write classes and methods, and continuously test and improve the code. We use performance testing to determine the best way to get things done in a test environment that duplicates large production databases. Once the component is completed, we push it into production and monitor its performance, scalability, availability, and error trapping. What we're presenting in this chapter has followed these steps. For each function, we'll explain what we did and why.

First, we'll look at ADO.NET and what it offers. We'll concentrate on the features that increase performance while allowing scalability. Then we'll design and write the data access component piece by piece. With each additional feature we'll look only at the differences between it and the features we've already discussed.

After completing our data access component, we'll discuss how to handle transactions and data concurrency, and how to use OLE DB-compliant code to access other databases such as Microsoft Access and Oracle.

After we complete the object and show you a simple two-tier example, you'll have a component that is ready to be used in n-tier environments. This approach fits within the architecture we discussed in Chapter 2. Finally, we'll summarize the features of our data access component so that you can easily reference it while you're applying it in your own applications. With all this in mind, let's get going.

The Power of ADO.NET

Performance

Scalability

Regarding performance and scalability, we need to understand that we can't have the best performance and best scalability at the same time; we must find an acceptable balance. For example, the best performance can be achieved through using the DataReader. It's wickedly fast, but since the connection has to stay open, it doesn't scale well. We need to learn which data object to use when and where.

XML Equals Interoperability

Ease of Use

LastName = myDataSet.Tables("Employee").Column s("LastName").Value

so that it becomes

LastName = Employee.LastName

Components of ADO.NET

Click to view graphic

Figure 4-1  A birds-eye view of ADO.NET

Table 4-1 shows you an overview of the most important objects in ADO.NET. The Connection, Command, DataAdapter, and DataReader objects together are called the .NET data provider. There are currently a SQL Server .NET Data Provider and an OLE DB .NET Data Provider. Each contains all four objects and works similarly. They are designed to be lightweight in order to create a minimal layer between the data source and the dataset calling code. The SQL Server .NET Data Provider is fully optimized to work with Microsoft SQL Server 7 and Microsoft SQL Server 2000, whereas the OLE DB .NET Data Provider works with generic data sources. As soon as we look at the Connection, Command, and DataReader objects, we can start with our chapter examples. We use both the SQL Server and Access Northwind example databases. We've added a few stored procedures that you can add either manually or as directed in the installation instructions in the readme.txt file. Let's look at the Connection object.

Table 4-1  The .NET Data Provider Objects

Object	Description
Connection	Connects to data source and has transaction capabilities
Command	Enables access to database commands to handle the following: return data, modify data, run stored procedures, and send and receive parameters
DataReader	High-performance, one-record-at-a-time data stream
DataAdapter	Bridge between data source and DataSet
DataSet	Disconnected, in-memory representation of data source, which contains tables as DataTable collections and relations as Relations collections

Connection Object

Dim ConnectionString as String
Dim ConnectionObject as SQLConnection
ConnectionString ="data source=local; initial catalog=Northwind;"& _ 
   "password=  ;id="
ConnectionObject = New SQLConnection(ConnectionString)

In addition to using the Connection object as the connection to the data source, we can use it for transactions. We use SQL Server's transaction capabilities predominantly within our stored procedure, but it's good to know that we have other options. Transactions are implemented in ADO.NET by declaring a variable to hold the Transaction object. After we open the connection, we use the BeginTransaction method from the Transaction object. Here is the code up to this point:

'Declare a variable with the SQLTransaction object.
Dim TransactionObject as SQLTransaction
'Open connection.
ConnectionObject.Open()
'Start transaction-based connection.
TransactionObject = ConnectionObject.BeginTransaction

The next step is to define the Command object. (We'll discuss this object in more detail in the next section.) We enroll the Command object into our current transaction:

CommandObject = New SQLCommand(SelectStatement, ConnectionObject)
'Attach command to transaction.
CommandObject.Transaction = TransactionObject

Whatever data activity we handle after enrolling the Command object is now included in the transaction. We have the choice of either committing the changes or rolling them back. Here are both cases in code:

'If we commit
TransactionObject.Commit()
ConnectionObject.Close() 
 
'If we do not want to commit
TransactionObject.Rollback()
ConnectionObject.Close()

After either action, we close the connection and our transaction is finished.

Command Object

Dim ConnectionString as String
Dim ConnectionObject as SQLConnection
Dim CommandObject as SQLCommand
Dim SelectStatement as String
ConnectionString ="data source=local; initial catalog=Northwind;"& _
   "password=  ;id="
SelectStatement ="Select LastName, FirstName From Employees"
ConnectionObject = New SQLConnection(ConnectionString)
CommandObject = New SQLCommand(SelectStatement,  ConnectionObject)

The boldface line shows us the Command object's properties we used: the SQL string to access the database and the Connection object we created previously. That's all there is to it—at least for the simple use of an SQL string and calling data.

Let's look at the first example included in this chapter's code samples. After you have followed the installation instructions in the readme.txt file, open the Chapter4DAL Project. You can open the code for this example in the SQLtoDataReader.aspx file in the DirectAccess folder or run the project and click the following link: "1. SQL Statement returning a DataReader." In this example, we use the DataReader object to get the data into a grid control. When you run this example, make sure to change the connection string with your login name and password. We also left out exception handling in the first couple of examples to make the code easier to read. (Don't worry. We'll have examples with exception handling soon.)

The SQL strings we use with the Command object can include parameters passed within the string or parameters that can be created from the Command object. This string can also include insert and update commands as well as commands that return no data at all. In general, we refrain from this approach since we prefer using stored procedures to work with our data.

More important to us is the ability of the Command object to accept stored procedures and execute them with or without parameters. To use the Command object to process stored procedrues, we have to set the Command object's property named CommandType to CommandType.StoredProcedure. Its default is CommandType.Text, with CommandType.StoredProcedure and CommandType.TableDirect as options. We don't use the TableDirect option very often since it returns the whole table. With CommandType set to StoredProcedure, the string we pass to the Command object includes the name of the stored procedure. Our second example, accessed through the link "2. StoredProcedure without parameters returning a DataReader," is contained in SPtoDataReader.aspx, which is located in the DirectAccess folder. We've included the SQL script and instructions in readme.txt to create the stored procedures in the Northwind database. If you want to add them manually, here is the code for the first stored procedure we use:

CREATE PROCEDURE dbo.spGetEmployees
AS
Select LastName, FirstName, title, Birthdate,  HireDate
From Employees

Here are the two lines to call the stored procedure without parameters:

Dim StoredProcedureName as String
StoredProcedureName = spGetEmployees
CommandObject = New SQLCommand(StoredProcedure Name, ConnectionObject)
CommandObject.CommandType = CommandType.Stored Procedure

Most of the time, when we run a stored procedure, parameters need to be passed in or out of the procedure. Many stored procedures do not return any data tables, just parameters or a return value. The Command object supports this with the Parameters collection.

Example 3, accessed by clicking the link "Stored Procedure with a parameter returning a DataReader," which is contained in the SPParamstoDataReader.aspx file in the DirectAccess folder, shows the use of the Command object when defining and adding a parameter to run the stored procedure. First, here is the stored procedure:

CREATE PROCEDURE dbo.spEmployeesbyCity
    (
    @City nVarChar(15) ="%",
    @Name nVarChar(20) = "%"
    )
AS
    Select LastName, FirstName, City
    From Employees
    Where City like @City and LastName like @N ame

Here are the lines of code that define the parameters:

ParameterObject = CommandObject.Parameters.Add ("@City", _ 
    SqlDbType.NVarChar, 15)
ParameterObject.Direction = ParameterDirection .Input
ParameterObject.Value = "London"

The first step for adding a parameter is to use the CommandObject.Parameters.Add method. This method has several parameters, including the name of the stored procedure's parameter, its data type, and its size. Its direction indicates whether this is an input or output parameter. The preceding code shows that we assign @City as the stored procedure's parameter name, with a data type of SqlDbType.NvarChar and a size of 15. It's also defined as an input parameter. Last but not least, we add the value to the parameter. When the Command object gets executed, the parameters will be passed or returned automatically. Table 4-2 lists the methods that you can call in the Command object.

Table 4-2  The Command Object Methods

Method	Description
ExecuteReader	This executes the DataReader. (This is our approach so far, but we'll offer more detail later.)
ExecuteNonQuery	An SQL statement gets executed that does not return any records. (This is good to use with stored procedures that return only parameters or nothing at all.)
ExecuteScalar	An SQL statement gets executed and returns the first row only.
ExecuteXMLReader	An SQL statement is executed and returns an XML stream.

DataReader Object

ConnectionObject.Open
DataReaderObject = _ 
    CommandObject.ExecuteReader(CommandBehavio r.CloseConnection)

When we use the DataReader, the connection needs to be opened before we execute it and after we iterate through it, but do not—and we repeat, do not—forget to close it. Open connections consume precious system resources and limit scalability. To help avoid open connections, the ExecuteReader method has a property option that tells the Command object to close the database connection automatically when the DataReader.Close method is called. (But the close method still needs to be called! See the second example for code implementation.) Here's one more thing about closing the DataReader: when you run a stored procedure that returns parameters only, the DataReader needs to be closed first. If this isn't done, you won't have access to the parameters returned.

When we bind the DataReader to the DataGrid, the DataGrid does a major job for us automatically. Normally we would have to iterate through the results one record at a time, but the DataGrid takes care of this for us.

DataAdapter Object

Looking back at Figure 4-1, we can see that so far we've taken the route on the right side of the diagram to get data to the calling code. As we discovered, this is the fire-and-forget-it way and is a one-way trip only. It doesn't allow data to be processed in return (other than passing parameters to a stored procedure that would use them for updating, inserting, or deleting data). We have also not yet worked in a disconnected fashion. This is where the DataAdapter comes in. The left side of Figure 4-1 depicts the process for handling disconnected data. The DataAdapter is our bridge from the actual data to the disconnected DataSet. This bridge is a two-way street. The DataAdapter fills the DataSets with data it passes along from the data source; actually, it passes the data it receives through the Connection and Command objects. On the way back to the data source, the DataAdapter can resolve changes made in the DataSet. Between these two processes the DataSet is totally disconnected from the actual data. But we'll talk more about that later.

When we instantiate the DataAdapter, we must pass it the SelectCommand property, which is the Command object. In the same way the Command object was used earlier to get data to the DataReader, the Command object retrieves the data from the data source. The actual passing of the data (like a baton passed in a relay race) happens when the Fill method of the DataAdapter is called. This is when the result fills the DataSet. You can see the workings of this in Example 4, the "SQL Statement returning a DataSet" link, which is implemented in the SQLtoDataSet.aspx file located in the DirectAccess folder.

DataAdapterObject = New SqlDataAdapter(CommandObject)
DataAdapterObject.Fill(DataSetObject, "Employees")

When calling the Fill method, we populate the DataSet object with a new table named Employees and the data returned from the Command object. The DataAdapter handles the creation and destruction of the connection and command objects, so there is nothing for us to do. We could explicitly create these and reuse them, but that would not fit our preferences, which you'll recall are the 3 Gs (get in, get it, get out).

If we don't name the table when using the Fill method, a default name will be assigned to it. We can access the table through the table collections index property as follows:

DataGridEmployees.DataSource = DataSetObject.T ables(0)

Since naming the table is supported and makes our code easier to read and debug, we prefer to give each table a name as shown in the third example. When you examine the code section of this example, you will also find that we changed the way we access the Connection string. Instead of having to remember its syntax or copying it back and forth, we've opted to use the ConfigurationSettings in the Web.config file. You simply add the appSettings element within Web.config, which is nothing but an XML file, and within the appSettings element, you create an <add key =. entry, as shown here:

<appSettings>
    <add key="Northwind"value="data source=localhost..." />
</appSettings>

The calling code looks like this:

ConnectionString = ConfigurationSettings.AppSettings("Northwind")

This also helps us manage all connections used within the Web application. When using Visual Basic .NET, we can create a similar file and call its content. If we feel that security is a larger issue (because although the Web.config file is protected from access through the Web, it's not protected from internal network access), we can create a ConnectionStringObject that looks at an encrypted connection string file stored on the system and pass the connection string through that object. For our examples and most applications, the web.config approach above will suffice.

In addition to the Fill method, the DataAdapter has FillSchema and Update methods. Whereas the Fill method creates a table and fills it with the result of the Command object, the FillSchema method just creates the empty shell of the table inside the DataSet. The Update method does exactly what it says: updates the actual data source with the changes passed from the DataSet.

In addition to the SelectCommand property mentioned earlier, the DataAdapter has three more properties that manage updates to the data source: InsertCommand, UpdateCommand, and DeleteCommand. We prefer to do updates to a SQL server database through stored procedures, but it's good to know that these options exist and that they're easily accessible in the ADO.NET DataAdapter.

DataSet Object

One of the ways to create tables in the DataSet object is through the DataAdapter Fill method. You've already seen this process in our last example. Examples 4 and 5 in our sample application show more variations of calling stored procedures with or without parameters. Example 4 creates the table in the DataSet without a name and shows how to fill the grid with the DataSet Table collection's index.

The DataSet object contains 0 or more tables. These are represented by the DataTable object. The DataTableCollection contains all the DataTables within the DataSet. Once we have a DataTable, it's represented by its index of the DataTable collection. DataTables themselves are represented by the DataColumnCollection and include the schema of each row in the table (like fields in a database table) and the DataRowCollection, which contains the data in the table (like data rows in a database table). Figure 4-2 shows the DataSet object and its collections and properties.

Click to view graphic

Figure 4-2  The DataSet object

The DataSet not only holds DataTables but can also handle primary keys, parent and child relations, constraints, and views, just like databases and tables in relational database systems. The DataSet can be looked upon as an in-memory copy of real tables, their relationships, and data. The only difference is that we want to work only with the data we need for our particular data functions and keep the DataSet as small as possible. When a DataTable resides in the DataSet, it creates a default view of this table. This view simply returns the whole DataTable and its data. Once we're in possession of the DataSet, we can create and manipulate other views.

Another way to create DataSet and DataTables is through code. We can actually create both of them programmatically. Web applications can make good use of programmatically created DataSets and DataTables. We might have a series of data entry pages that require the actual saving of data to take place after the last page is filled. An easy way to pass data from one page to the other—without having to connect and access the database until the entry needs to be saved—is to create a DataSet and DataTable with the required fields in the first page, passing them from page to page, adding data as you go, until the final page is reached. When the save method is invoked, a Connection object, a Command object, and a DataAdapter can then be declared and the data saved.

There's still more to the DataSet. It can create XML and XML Schemas that contain part or all of the DataTables, their schemas, and data. It can also populate itself by reading XML. When data is changed, the DataSet tracks the changes and original values and lets you accept or reject the changes. If the changes are accepted, the DataSet can create a subset of itself, containing just the changes, and resolve the update through the DataAdapter. We don't have room in this chapter to go into all the details of the DataSet. As we build the Human Resources HRnet enterprise application in later chapters, you'll see many of the useful features the DataSet provides.

The ADO.NET Data Access Object

Why Have a Data Access Object?

What Will the Data Access Object Do?

The data access object is also going to give us a consistent way of interacting with data. We'll get consistency not only within the applications but also when accessing data between ASP.NET Web Forms, Windows Forms, business classes, and even XML Web services. This is the case whether we're using Visual Basic .NET, C#, or some other language that runs under the .NET common language runtime (CLR) managed code. We're very excited about this. It might actually be possible, for a change, to remember the code for accessing data and not to have to fight with the minor code differences of the past (minor difference but major pain, I might add).

We're going to write our object specifically to access SQL Server 7 and SQL Server 2000, using the SQL Data Providers of ADO.NET. Our object will be optimized for this platform but easily transformed so that it can be used on others as well. We'll provide an example showing how simple it is to add support for other database platforms. With that said, here is the functionality we're looking for:

• Multiple constructors that are flexible for different requirements
• Simple exception handling with event logging capability
• Return of data in a variety of formats
• As a DataSet
• As a DataReader
• As XML
• As a parameter list
• As a notification about whether a function performed correctly (not to be confused with exception handling)

• Data access from SQL Server with simple SQL statements or with stored procedures, with or without the use of parameters

Our Approach to Database Access and Usage

Needing to get the highest possible performance while staying scalable and flexible and having a reasonable amount of security, we generally use stored procedures with SQL Server 7 or SQL Server 2000 exclusively. Whether we have to access data, add data, or modify data, we use stored procedures. We want to take advantage of what SQL Server does best: data access, data manipulation, transactions, and so forth. Now we know that some of you are thinking, "These guys are too inflexible, and think they know it all." This isn't so. We simply use what we've found and tested to be best. Yes, we're still going to build direct SQL string capability into the object. Why? Because we see good reasons for using it from time to time, such as building ad hoc data access in reports. You might even use SQL statements for adding data and updating data, even though we wouldn't encourage you to do so. The choice is yours, and the data access component lets you do either.

Last Updated: September 18, 2002

Top of Page