Lesson 3: Designing a Data Access Object Model

Estimated lesson time: 45 minutes

Database developers must often decide between several data access object models. To select an appropriate data access object model, he or she must know what type of data needs to be retrieved. This lesson will cover the different models available and what considerations should be made when selecting one.

What is the ADO Object Model?

The ActiveX Data Objects (ADO) object model is a data access technology that defines the objects used to access the data. It provides a high-level interface, so you can get to your data as quickly and easily as possible. And, it lets you access data from a wide variety of data sources.

The model has evolved over the years; some developers might remember using previous data models known as Data Access Objects (DAO) or Remote Data Objects (RDO). These models relied on a complex and deep hierarchical data structure. The ADO object model is a flatter model that simplifies many of the steps involved with connecting and accessing data from a data source.

You may recall from Lesson 1 that there are two versions of ADO available. There is the ADO that is part of the MDAC and then there is ADO.NET, which is part of the .NET Framework. Both use similar concepts regarding connections and some of the other basic objects, but they use different structures and models.

For both data access methods, you must make a choice between using a connected or disconnected model. This will determine the type of data object into which you return your data. The difference between a connected and disconnected model has to do with whether a connection to the data source is maintained after the data object has been populated. Each method has distinct advantages and disadvantages; which method to use depends on the application requirements.

Using a Disconnected Model with ADO.NET

The idea of a disconnected model was driven primarily by the need for efficiency and scalability while still allowing for maximum functionality. Let's say you have an application that displays and manipulates data from a data source. If you choose a connected model, you will maintain a connection to the data source even after the data has been passed to the application. This connection will consume memory and resources on the application server. If that application is being accessed by multiple simultaneous users-say, thousands of them-this could significantly alter the ability of your application server to perform well. You will likely notice slowdowns as the number of user connections increases.

The disconnected model, which disconnects the client from the data source as soon as the data is returned, is ideal for applications that need to cache data locally for a large number of simultaneous users. In this model, the client will connect with the data source and return a snapshot of the data required. It will then disconnect from the client and only reconnect when changes need to be made or data refreshed from the data source.

The key object in ADO.NET that supports the disconnected model is the DataSet. The DataSet object is an in-memory representation of the data as it existed on the data source at the time the query was executed. It can contain data from several different tables that are linked together through relationships. This enables you to maintain data from both the parent and child tables within the same DataSet object.

The DataSet maintains a structure that is similar to the database structure. It is hierarchical and contains collections for tables, rows, columns, constraints, and relationships. The type of DataSet offered with ADO.NET is very different than the one that comes with ADO. With ADO, you cannot have multiple tables inside a single DataSet. You can return data from multiple tables, but there won't be any relationships that define how the data results relate to each other.

DataSets can be beneficial in the following situations:

To easily populate data-bound controls in Windows and Web-based applications
To process data from multiple tables and potentially different data sources while maintaining the relationships between these tables
To cache data locally for operations such as sorting or filtering
To pass data through application tiers or through an XML Web service

DataSets can be created using the Data Source Configuration Wizard or with the Dataset Designer. This can be useful when creating a typed dataset, which is a DataSet that maintains the schema information and is easier to maintain and less prone to errors.

Of course, DataSets can also be created programmatically because the DataSet object is just derived from a class in the .NET Framework. To create a DataSet, you start by creating a new instance, such as in the following code:

 //C# DataSet dsEmployees = new DataSet("Products"); 'VB Dim dsEmployees As New DataSet("Products")

To get data into your DataSet, you need a data source. This is defined by using the DataAdapter object. ADO.NET offers two data adapters: one specifically for SQL Server, and another for all other databases. If you are querying a SQL Server database, you want to use the SQLDataAdapter. In addition to the SQLDataAdapter object, you need a SQLConnection to represent the connection to the database and a SQLCommand object to represent the action taken against the database. Before you can access these objects, you need to set a reference at the top of your code file to the System.Data.SqlClient class, such as in the following code statement:

 //C# using System.Data.SqlClient; 'VB Imports System.Data.SqlClient

You can then create instances of the SQLDataAdapter, SQLConnection, and SQLCommand objects, as in the following code:

 //C# SqlDataAdapter adapter = new SqlDataAdapter(); SqlConnection conn = new SqlConnection(connString); 'VB Dim adapter As New SqlDataAdapter() Dim conn As New SqlConnection(connString) Dim cmd As New SqlCommand(queryString, conn)

In this sample code, a SQLCommand object was created using a query string. This query string could have contained an actual Transact-SQL statement such as SELECT * FROM Products, or it could contain the name of a stored procedure. This will represent the action that is taken against the data source which, in this case, is a query.

Best Practices

Wrap Database Code in a Try…Catch Block

It is a good idea to wrap the portion of your code that accesses the database inside a try…catch block. You can then place any cleanup code in the Finally portion of this block. For DataAdapter and Connection objects, you can specifically call the Dispose method to ensure that all system resources are released.

If changes need to be made to the data in a DataSet, they will be made to the in-memory copy of the data. The changes will not be propagated back to the data source until the Update method for the DataAdapter object is called. The following code is an example of saving changes made to the dsProducts DataSet back to the data source defined with the DataAdapter named adapter:

 //C# adapter.Update(dsProducts.Tables[0]); 'VB adapter.Update(dsProducts.Tables(0))

Using a Connected Model with ADO.NET

ADO.NET also provides objects that maintain a connection to the data source until they are specifically closed and released. The DataReader object is part of ADO.NET's connected model, and it returns a read-only, forward-only stream of data from a data source. Because it is read-only and forward-only, it can be quite efficient for applications that only need to display data and not manipulate it or cache it locally.

Deciding whether to use a DataSet or a DataReader depends on the requirements of the application. If the application needs to cache data locally so that it can be updated, the DataSet is a good choice. If the data returned will only be read and not manipulated, a DataReader is generally the most efficient choice. This is especially true if the amount of data returned is too large to be cached in-memory.

Using a DataReader object does require that the developer specifically close the resource when it is no longer needed, but if this is done as soon as possible, the DataReader can be an efficient form of data access. As long as you do not require the functionality available with the DataSet, it is more efficient to return data to a DataReader object.

To use the DataReader object, you still need a connection to the database using either the SQLConnection or OleDbConnection objects. You can also still use the SQLCommand or OleDbCommand objects to handle the action performed against the data source. The DataReader object you select is also determined by the data source. The following DataReader objects are available:

SQLDataReader
OleDbDataReader
OdbcDataReader
OracleDataReader

You will use the ExecuteReader method of the command object to return data into the chosen DataReader object. The following code is an example of a SQLDataReader populated with data from a SQLCommand object named cmd:

 //C# SqlDataReader reader = cmd.ExecuteReader(); 'VB Dim reader as SqlDataReader = cmd.ExecuteReader()

In some cases, you might want to return data as an XML stream. In this case, you will use the XmlReader object to obtain a read-only, forward-only stream of data in an XML format. You will still use the same connection and command objects used to populate a DataReader, but in this case, the query will use the FOR XML AUTO clause to return the data as XML. You will also need to add an additional reference at the top of your code to the System.Xml namespace. For example, the following code could be used to populate an XmlReader object with XML data from the products table in the AdventureWorks database:

 //C# string queryString = "select name, productnumber, listprice " +                                  "from production.product " +                                  "FOR XML AUTO"; SqlCommand cmd = new SqlCommand(queryString, conn); XmlReader xreader = cmd.ExecuteXmlReader(); 'VB Dim queryString As String = "select name, productnumber, listprice " + _                                  "from production.product " + _                                  "FOR XML AUTO" Dim cmd As SqlCommand = New SqlCommand(queryString, conn) Dim xreader As XmlReader = cmd.ExecuteXmlReader()

Lab: Selecting a Data Access Object Model

In this lab, you will connect to a database using both the disconnected and connected models from ADO.NET. In the first exercise, you will create a Windows forms project that retrieves data from the database using a DataSet. In the second exercise, you will use the same Windows forms project from Exercise 1, and you will retrieve data in a SQLDataReader and display the results in a message box.

Exercise 1: Retrieve Data Using a Dataset

In this exercise, you will create a new Windows forms project named Lab 3 and add it to the existing solution created in previous lessons. You will connect to the AdventureWorks database, retrieve product data for all products, populate a DataSet, and then use that data to populate a dataGridView control.

Open Microsoft SQL Server Management Studio, and connect to the local instance of SQL Server.

Click New Query, and type the following SQL statement to create a new stored procedure named GetProductListing:

 USE [AdventureWorks] GO /****** Object: StoredProcedure [dbo].[GetProductListing] ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[GetProductListing] AS select name, productnumber, listprice from production.product GO

Open Microsoft Visual Studio 2005. If you completed the Lesson 1 lab or the Lesson 2 lab, you can skip to step 6.
On the File menu, select New, Project.
In the New Project dialog box, expand the Other Project Types node, and select Visual Studio Solutions. Type TK442Chapter1 for the name of your blank solution, and place it in a directory of your choosing. A new solution file is created, and you can now add multiple projects to this solution. You will add one project for each lab included in this chapter.
On the File menu, select Add, New Project. Select Windows Application as the template, and type Lab3 as the project name. Set the language by selecting Visual Basic, Visual C#, or Visual J# from the language drop-down list box. By default, Visual Studio will select the language specified when it was first configured.
From the Toolbox, drag a button control onto the Form1 design surface. Use the following properties for this control:
- Name = btnDataset
- Text = "Open Dataset"
From the Toolbox, drag a dataViewGrid control onto the Form1 design surface. Leave the properties with the default values.

Right-click the Form1 file from Solution Explorer, and select View Code. Paste the following code into the code window, modifying connection strings to match your environment:

 //C# private void btnDataset_Click(object sender, EventArgs e) {      DataSet dsProducts = new DataSet("Products");      SqlDataAdapter adapter = new SqlDataAdapter();      //Initiate the connection to SQL Server     String connString = @"server=.\SQL2005STD;" +                             "Integrated Security=SSPI;" +                             "Database=AdventureWorks";     SqlConnection conn = new SqlConnection(connString);     //Define the query that will be executed     SqlCommand cmd = new SqlCommand("GetProductListing", conn);     try     {        //Populate the adapter with results of the query        adapter.SelectCommand = cmd;        adapter.Fill(dsProducts);        //Set the datasource for the dataViewGrid control on the form        dataGridView1.DataSource = dsProducts.Tables[0];     }     catch (Exception ex)     {         MessageBox.Show(ex.Message);     }     finally     {         if (adapter != null)         {           adapter.Dispose();           adapter = null;         }         if (cmd != null)         {           cmd.Dispose();           cmd = null;         }         if (conn != null)         {           if (conn.State == ConnectionState.Open)           {              conn.Close();           }           conn = null;         }     } } 'VB Private Sub btnDataset_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDataset.Click      Dim dsProducts As New DataSet("Products")     Dim adapter As New SqlDataAdapter()        'Initiate the connection to SQL Server        Dim connString As String = "server=.\SQL2005STD;" & _                                    "Integrated Security=SSPI;" & _                                    "Database=AdventureWorks"        Dim conn As New SqlConnection(connString)        'Define the query that will be executed        Dim cmd As New SqlCommand("GetProductListing", conn)        Try            'Populate the adapter with results of the query            adapter.SelectCommand = cmd            adapter.Fill(dsProducts)            'Set the datasource for the dataViewGrid control on the form            DataGridView1.DataSource = dsProducts.Tables(0)        Catch ex As Exception            MessageBox.Show(ex.Message)        Finally            If Not (adapter Is Nothing) Then                adapter.Dispose()                adapter = Nothing            End If            If Not (cmd Is Nothing) Then                cmd.Dispose()                cmd = Nothing            End If            If Not (conn Is Nothing) Then                If (conn.State = ConnectionState.Open) Then                    conn.Close()                End If                conn = Nothing            End If        End Try    End Sub

On the File menu, select Save All to save the Lab3 project.
Right-click the Lab3 project from Solution Explorer, and select Set As Startup Project.
Press Ctrl+F5 to build the project without debugging. Ensure that the project builds successfully. Form1 should appear after the project compiles and executes. You should see an Open Dataset button; click this button. The data results are displayed in the dataViewGrid control.

The completed code examples, in both Visual Basic and C#, are available in the \Labs\Chapter 01 folder on the companion CD.

Exercise 2: Retrieve Data Using a DataReader

In this exercise, you will add a button to the Windows forms project named Lab 3 that was created in Exercise 1. You will connect to the AdventureWorks database, retrieve product data for products with a shipment level less than 50, populate a SQLDataReader, and then loop through the results and display them in a message box.

Open Microsoft Visual Studio 2005.
Open the project, created in Exercise 1, named Lab 3.
From the Toolbox, drag a button control onto the Form1 design surface. Use the following properties for this control:
- Name = btnDataReader
- Text = "Open DataReader"

Right-click the Form1 file from Solution Explorer, and select View Code. Paste the following code into the code window, modifying connection strings to match your environment:

 //C# private void btnDataReader_Click(object sender, EventArgs e)      {          //Initiate the connection to SQL Server          String connString = @"server=.\SQL2005STD;" +                              "Integrated Security=SSPI;" +                              "Database=AdventureWorks";          SqlConnection conn = new SqlConnection(connString);          conn.Open();          //Define the query that will be executed          string queryString = "select name, productnumber, listprice " +                               "from production.product " +                               "where safetystocklevel < 50";          SqlCommand cmd = new SqlCommand(queryString, conn);          SqlDataReader reader = null;          try          {              //Execute the command and return a datareader              reader = cmd.ExecuteReader();              //See if the object has data and if it does              //format a string with the results and display              //it as a message box              String ret = "";              if (reader.HasRows)              {                  do                  {                      while (reader.Read())                      ret += reader.GetString(0) + " - " +                             reader.GetString(1) + " (" +                             reader.GetDecimal(2) + ")" +                             "\n";                  }                  while (reader.NextResult());              }              else              {                  MessageBox.Show("No rows returned.");              }              MessageBox.Show(ret);          }          catch (Exception ex)          {              MessageBox.Show(ex.Message);          }          finally          {              if (reader != null)              {                  reader.Close();                  reader = null;              }              if (conn != null)              {                  if (conn.State == ConnectionState.Open)                  {                      conn.Close();                  }                  conn = null;              }          }      } 'VB Private Sub btnDataReader_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDataReader.Click         'Initiate the connection to SQL Server         Dim connString As String = "server=.\SQL2005STD;" & _                                     "Integrated Security=SSPI;" & _                                     "Database=AdventureWorks"         Dim conn As New SqlConnection(connString)         conn.Open()       'Define the query that will be executed        Dim queryString As String = "select name, productnumber, listprice " + _                                    "from production.product " + _                                    "where safetystocklevel < 50"        Dim cmd As New SqlCommand(queryString, conn)        Dim reader As SqlDataReader        Try            'Execute the command and return a datareader            reader = cmd.ExecuteReader()            'See if the object has data and if it does            'format a string with the results and display            'it as a message box            Dim ret As String = ""            If reader.HasRows Then                Do While reader.Read()                    ret += reader.GetString(0) + " - " + _                           reader.GetString(1) + " (" + _                           Convert.ToString(reader.GetDecimal(2)) + ")" + _                           Chr(10) + Chr(13)                Loop            Else                MessageBox.Show("No rows returned.")            End If            MessageBox.Show(ret)        Catch ex As Exception            MessageBox.Show(ex.Message)        Finally            If Not (reader Is Nothing) Then                reader.Close()            End If            If Not (conn Is Nothing) Then                If (conn.State = ConnectionState.Open) Then                    conn.Close()                End If                conn = Nothing            End If        End Try    End Sub

On the File menu, select Save All to save the Lab3 project.
Right-click the Lab3 project from Solution Explorer, and select Set As Startup Project.
Press Ctrl+F5 to build the project without debugging. Ensure that the project builds successfully. Form1 should appear after the project compiles and executes. You should see an Open DataReader button; click this button. The data results are displayed in a message box.