Lesson 3: Accessing Data

Lesson 3: Accessing Data

Visual Studio .NET has many built-in wizards and designers to help you shape your data-access architecture rapidly and efficiently. With minimal actual coding, you can implement robust data access for your application. However, the ADO.NET object model is fully available through code to implement customized features or to fine-tune your program. In this lesson, you will learn how to connect to a database with ADO.NET and retrieve data to your application. You will learn to use the visual designers provided by Visual Studio .NET and direct code access.

After this lesson, you will be able to

• Establish a connection to a database

• Create and configure a DataAdapter

• Create a Command object that uses SQL strings

• Create a Command object that accesses stored procedures on the database

• Create a typed DataSet

• Fill a DataSet with a DataAdapter

• Use a DataReader to programmatically access data

Estimated lesson time: 60 minutes

Connecting to a Database

You can implement a database connection in many different ways. The easiest way is to create a connection at design time using the graphical tools in Visual Studio .NET.

Current data connections are managed in the Server Explorer window. This window is normally docked to the left pane of the integrated development environment (IDE) and shares the same space as the Toolbox. If the Toolbox is visible, you can view the Server Explorer by clicking the Server Explorer tab at the bottom of the Toolbox, or by choosing Server Explorer from the View menu. The Server Explorer is shown in Figure 6.1.

Server Explorer displays data connections currently available to Visual Studio .NET as child nodes of the Data Connections node. If you want to add one of these child nodes to your project, all you need to do is drag the connection from the Server Explorer window to the designer. A new Connection object of the appropriate type is created and automatically configured to connect to your database.

Figure 6-1. The Server Explorer.

To add a connection from Server Explorer

Drag the node that represents a database from the Server Explorer window to the designer.

You can also create a new connection in the Server Explorer by right-clicking the Data Connections node and choosing Add Connection. This launches the Data Link Properties dialog box, as shown in Figure 6.2.

Figure 6-2. The Data Link Properties dialog box.

The Data Link Properties dialog box is a visual way to configure your data connection. The Providers tab allows you to choose the database provider that you will use in your connection. The Connection tab allows you to configure the specific properties of your connections, and the Advanced tab configures properties not normally needed for every connection. The All tab allows you to view and edit settings for all the properties. Once you have configured your new connection, click OK to close the Data Link Properties window. The new connection appears in the Server Explorer window and can be dragged to your designer.

To create a new connection in Server Explorer

1. In the Server Explorer, right-click Data Connections and choose Add Connection to open the Data Link Properties window.

2. On the Provider tab, choose the appropriate provider for your data connection.

3. On the Connection tab, choose the database you want to connect. Depending on the provider, you also might have to set properties such as the server name or password settings.

4. After you have entered the appropriate information, click the Test Connection button to verify that the connection is functional.

5. Drag the new connection from the Server Explorer, and drop it on the designer surface. A new connection object of the appropriate type is created and correctly configured.

You also can create a database connection manually by dragging a Connection object from the Toolbox to the designer or by declaring and instantiating the object in your code. If you create a connection in code, you will have to manually set the ConnectionString property. The following code example demonstrates how to create a Connection object in code and set the ConnectionString property:

Visual Basic .NET

' Declares and instantiates a new OleDbConnection object Dim myConnection As New OleDbConnection() ' Sets the connection string to indicate a Microsoft Access ' database at the specified path myConnection.ConnectionString = _ "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "DataSource=C:\Northwind\Northwind.mdb"

Visual C#

// Declares and instantiates a new OleDbConnection object OleDbConnection myConnection = new OleDbConnection(); // Sets the connection string to indicate a Microsoft Access // database at the specified path myConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;DataSource=" + "C:\\Northwind\\Northwind.mdb";

NOTE
Depending on your database provider and configuration, you might have to supply additional parameters in the connection string. Whenever possible, it is recommended that you implement connections using the visual tools supplied by Visual Studio .NET.

To create a new connection in code

1. Declare and instantiate the appropriate type of Connection object.

2. Set the ConnectionString property.

To create a new connection in the designer

1. Drag the appropriate type of Connection object from the Data tab of the Toolbox to the designer.

2. In the Properties window, set the ConnectionString property.

Using Data Commands

A Command object contains a reference to a database stored procedure or SQL statement and can execute that statement across an active data connection. A Command object contains all the information it requires to execute the command, including a reference to the active connection, the specification for the command itself, and any parameters required by the command.

As with other classes in data providers, there are two types of Command classes. The OleDbCommand class is designed to interact with a wide variety of database types. The SqlCommand class is designed to interact specifically with SQL Server 7 or later.

Because they only require an active connection and do not need to interact with a DataAdapter, Command objects provide a fast and efficient way to interact with a database. Command objects can be used to perform the following actions:

• Execute commands that do not return records such as INSERT, UPDATE, and DELETE.

• Execute commands that return a single value.

• Execute Database Definition Language (DDL) commands, such as CREATE TABLE, or ALTER.

• Work with a DataAdapter to return a DataSet.

• Return a result set directly through an instance of a DataReader object. This provides the fastest way to access data and is useful when read-only data is required.

• Return a result set as an XML stream. This method is only available with the SqlCommand class.

• Return a result set from multiple tables or command statements.

Creating and Configuring a Data Command

You can create a data command in three ways:

• By dragging a stored procedure (a query that is stored on the database server itself) from the Server Explorer window to the designer.

• By dragging an appropriate Command from the Toolbox Data tab to the designer and configuring it in the Properties window.

• By declaring and instantiating the appropriate type of Command object in code and configuring it manually.

Dragging a stored procedure to the designer is the most straightforward way to create a data command. Any stored procedure on a database can be used to create a Command object. When a stored procedure is dragged onto the designer, a Command object of the appropriate type is created automatically. The new command references the stored procedure and can be used immediately to execute the stored procedure as is no additional configuration is necessary.

To create a Command object that references an existing stored procedure

Drag the stored procedure from the Server Explorer to the designer. An appropriately configured instance of a Command object is created.

Creating a Command object in the designer is almost as easy as dragging a stored procedure. You can create a Command object by dragging either an SqlCommand or an OleDbCommand from the Toolbox Data tab to the designer. This creates an instance of the Command object you selected. Once created, the Command object must be configured by setting the Connection, CommandType, and CommandText properties.

The CommandType property determines what kind of command is contained by the CommandText property. There are three possible values for the CommandType property, which are:

• Text.

  A value of Text indicates that the value contained in the CommandText property will be parsed as an SQL text command. When this is the case, the CommandText property must be set to a valid SQL expression. A Command object can contain multiple SQL statements separated by semicolons (;) if batched SQL commands are supported by the target database. If a Command object contains more than one SQL statement, the statements execute sequentially when the command executes.

• StoredProcedure.

  If the CommandType property is set to StoredProcedure, the value contained in the CommandText property must contain the name of an existing stored procedure on the database. Executing this command causes the stored procedure of the same name to execute.

• TableDirect.

  A CommandType of TableDirect indicates that the name of a table or tables must be indicated by the CommandText property. Executing this command returns all of the columns and all of the rows of the table or tables named in the CommandText property.

The Connection property must be set to an active connection of the appropriate type (that is, a SqlCommand must have a SqlConnection as its connection, and an OleDbCommand must have an OleDbConnection).

Executing Commands

Each type of Command object has three methods to execute the command it represents:

• ExecuteNonQuery

• ExecuteScalar

• ExecuteReader

The SqlCommand class exposes an additional method, ExecuteXmlReader, for executing the command it represents.

Each of these methods executes the data command represented by the Command object. The difference between these methods lies in the values they return. ExecuteNonQuery is the simplest method, as it executes the data command, but returns no value. Thus, ExecuteNonQuery is the method typically used to call SQL commands or stored procedures of the INSERT, UPDATE, or DELETE types. Additionally, this is the only way in ADO.NET to execute DDL commands such as CREATE or ALTER. The ExecuteScalar method returns the first column of the first row of data returned by the command, no matter how many rows the command actually selects. ExecuteReader returns a DataReader object that can iterate through a result set in a forward-only, read-only manner without involving a DataAdapter. This is the fastest and often the most efficient way to retrieve data when you do not need to update or otherwise manipulate the database itself. The SqlCommand class exposes one additional method, which is ExecuteXmlReader. This class returns an XmlReader object that iterates through the result set and provides data in a forward-only, read-only manner, formatted as XML.

Parameters

Data commands frequently make use of parameters. Often, the values of some elements of a data command are unknown until run time. Consider an application that tracks inventory for a bookstore. It might contain a function that looks up books by title. This functionality can be implemented by querying the database with a SQL statement similar to the following code example:

SELECT * FROM Books WHERE (Title LIKE [value])

At design time, you know that you want the application to find all the books with a title similar to a value supplied by the user at run time. Because you do not know in advance what value the user will supply, you must employ a mechanism for supplying the value to the statement at run time.

Parameters are values that fill placeholders left in the command text. Each parameter is an instance of the OleDbParameter or SqlParameter class, as appropriate. Parameters are stored in the Command object s Parameters property, and at run time the values are read from the property and placed into the SQL statement or supplied to the stored procedure.

Command objects provide a Parameters collection that exposes a collection of Parameter objects of the appropriate type. Some of the properties exposed by the Parameter objects are as follows:

• DbType (This property is not visible in the designer.)

• Direction

• OleDbType (OleDbParameters only)

• ParameterName

• Precision

• Scale

• Size

• SourceColumn

• SourceVersion

• SQLType (SQLParameters only)

• Value

In OleDbParameters, the DbType and OleDbType properties are related. The DbType property represents the type of parameter, as represented in the common type system (CTS). However, because not all databases are CTS compliant, the OleDbType property represents the type of parameter as it exists in the database. The Parameter object performs all necessary conversions of the application type to the database type. Because the two properties are related, changing the value of one changes the value of the other to a supporting type. In SqlParameter objects, the DbType property and the SqlType property share a similar relationship, wherein the SqlType property specifies the SQL database type represented by the parameter.

The Direction property specifies whether the parameter is for input or output. The possible values for this property are Input, Output, InputOutput, or ReturnValue, which indicates the parameter is to contain a return value from a stored procedure or function.

In code, you can refer to members of the Parameters collection by their index or by their name. The ParameterName property specifies the name that can be used as a key to specify the parameter in code. The following code example demonstrates two different ways to set the value of the first parameter in the collection, which is named myParameter:

Visual Basic .NET

' This line sets the value by referring to the index of the parameter OleDbCommand1.Parameters(0).Value = "Hello World" ' This line sets the value by referring to the name of the parameter OleDbCommand1.Parameters("myParameter").Value = "Goodbye for now"

Visual C#

// This line sets the value by referring to the index of the parameter OleDbCommand1.Parameters[0].Value = "Hello World"; // This line sets the value by referring to the name of the parameter OleDbCommand1.Parameters["myParameter"].Value = "Goodbye for now";

The Precision, Scale, and Size properties all affect the size and accuracy of the parameters. Precision and Scale are used with numeric and decimal parameters. Respectively, they represent the maximum number of digits of the Value property and the number of decimal places that Value resolves to. Size is used with binary and string parameters and represents the maximum size of data in the column.

SourceColumn and SourceVersion are used when the parameter is bound to a column in a DataTable. The SourceColumn property specifies the column used to look up or map values, and the SourceVersion property specifies which version of the column to use when it is being edited.

The Value property contains the value represented by the parameter.

When the CommandType property of the Command object is set to Text, you must specify a placeholder for any parameters that will be inserted into the SQL statement. With OleDbCommand objects, this placeholder takes the form of a question mark (?). For example:

SELECT EmpId, Title, FirstName, LastName FROM Employees WHERE (Title = ?)

In this example, the question mark indicates where the parameter will be inserted. You also can specify multiple parameters, as follows:

SELECT EmpId, Title, FirstName, LastName FROM Employees WHERE (FirstName = ?) AND (LastName = ?)

When the command text requires multiple parameters, the parameters are inserted in the order that they appear in the Parameters collection.

When using a SqlCommand object, you must use named parameters. Placeholders for a named parameter are created by preceding the name of your parameter (as specified by the ParameterName property) with an @ symbol. For example, the following SQL statement specifies a named parameter named Title:

SELECT EmpId, Title, FirstName, LastName FROM Employees WHERE (Title = @Title)

The following procedures describe how to use a Command object to execute different kinds of commands.

To use a Command object to execute a nonquery command

This procedure is used to execute INSERT, UPDATE, and DELETE commands as well as DDL commands such as CREATE TABLE and ALTER.

1. Set the CommandType property to StoredProcedure if specifying a stored procedure or Text if specifying a SQL string.

2. Set the CommandText property to the name of the stored procedure or the desired SQL string, as appropriate.

3. Specify any parameters and their appropriate values.

4. Call the Command.ExecuteNonQuery method. An example of this method follows:

   Visual Basic .NET

   ' This command is identical whether you are using the OleDbCommand ' class or the SqlCommand class myCommand.ExecuteNonQuery()

   Visual C#

   // This command is identical whether you are using the OleDbCommand // class or the SqlCommand class myCommand.ExecuteNonQuery();

To use a Command object to return a single value

1. Set the CommandType property to StoredProcedure if specifying a stored procedure or Text if specifying a SQL string.

2. Set the CommandText property to the name of the stored procedure or the desired SQL string, as appropriate.

3. Specify any parameters and their appropriate values.

4. Call the Command.ExecuteScalar method. An example of this method follows:

   Visual Basic .NET

   ' This command is identical whether you are using the OleDbCommand ' class or the SqlCommand class Dim O As Object O = myCommand.ExecuteScalar()

   Visual C#

   // This command is identical whether you are using the OleDbCommand // class or the SqlCommand class Object O; O = myCommand.ExecuteNonScalar();

Using DataReaders

Executing nonquery or scalar-returning commands with a Command object is straightforward. To use a Command object with queries that return more than one value, however, you must use the ExecuteReader method to return a DataReader.

A DataReader is a lightweight object that provides read-only, forward-only data in a fast and efficient manner. To expose the values directly to program logic, you can use the DataReader to iterate through the records returned in a result set. Using a DataReader rather than a DataAdapter to fill a DataSet is more efficient, but it is also more limited. This is because the data provided is read-only; no updates can be performed with a DataReader. Also, the data access is forward-only; once a record has been read, it cannot be returned to. Additionally, a DataReader is a connected data-access structure requiring exclusive use of an active connection for the entire time it is in existence.

Creating a DataReader

A DataReader cannot be created explicitly. Rather, you must instantiate a Data Reader by making a call to a Command object s ExecuteReader command. Like other members of the different data providers, each DataProvider has its own class of DataReader. An OleDbCommand object returns an OleDbDataReader, while a SqlCommand object returns a SqlDataReader. For example:

Visual Basic .NET

' This example assumes the existence of an OleDbCommand object ' and a SqlCommand object named myOleDbCommand and mySqlCommand ' respectively Dim myOleDbReader As System.Data.OleDb.OleDbDataReader Dim mySqlReader As System.Data.SqlClient.SqlDataReader ' This call creates a new OleDbReader and assigns it to the variable myOleDbReader = myOleDbCommand.ExecuteReader() ' This call creates a new SqlReader and assigns it to the variable mySqlReader = mySqlCommand.ExecuteReader()

Visual C#

// This example assumes the existence of an OleDbCommand object // and a SqlCommand object named myOleDbCommand and mySqlCommand // respectively System.Data.OleDb.OleDbDataReader myOleDbReader; System.Data.SqlClient.SqlDataReader mySqlReader; // This call creates a new OleDbReader and assigns it to the variable myOleDbReader = myOleDbCommand.ExecuteReader(); // This call creates a new SqlReader and assigns it to the variable mySqlReader = mySqlCommand.ExecuteReader();

When a Command object s ExecuteReader method is called, the Command object executes the command it represents and builds a DataReader of the appropriate type, which can be assigned to a reference variable.

Simple Data Access with the DataReader

Once you have a reference to a DataReader, you can iterate through the records and read them into memory as needed. When the DataReader is first returned, it is positioned before the first record of the result set. To make the first record available, you must call the Read method. If a record is available, the Read method advances the DataReader to the next record and returns True (true). If a record is not available, the Read method returns False (false). Thus, it is possible to use the Read method to iterate through the records with a While (while) loop, as shown in the following code example:

Visual Basic .NET

While myDataReader.Read() ' Code here will be executed once for each record returned in ' the result set End While

Visual C#

while (myDataReader.Read()) { // Code here will be executed once for each record returned in // the result set }

When a record is being read by the DataReader, the values in the individual columns are exposed through the indexer or default property as an array of objects that can be accessed by their ordinal values or by the column name. For example:

Visual Basic .NET

While myDataReader.Read() Dim myObject As Object = myDataReader(3) Dim myOtherObject As Object = myDataReader("CustomerID") End While

Visual C#

while (myDataReader.Read()) { object myObject = myDataReader[3]; object myOtherObject = myDataReader["CustomerID"]; }

All of the values exposed by the DataReader in this manner are exposed as objects, although you can retrieve strongly typed data from the DataReader as well. This process is discussed later in this lesson.

After you have finished reading data with the DataReader, you must call the Close method to close the DataReader. If Close is not called, the DataReader will maintain exclusive access to the active connection and no other object can use it. You also can set the CommandBehavior property to CloseConnection when you call ExecuteReader. This causes the connection to close automatically, eliminating the need to explicitly call Close.

Visual Basic .NET

myDataReader.Close()

Visual C#

myDataReader.Close();

Accessing Columns of Data with a DataReader

The following sample code demonstrates how to iterate through the records returned in a result set and write one column of data to the console window. This example assumes the existence of an OleDbCommand object named myOleDbCommand with its Connection property set to a connection named myConnection.

Visual Basic .NET

' Opens the active connection myConnection.Open() ' Creates a DataReader and assigns it to myReader Dim myReader As System.Data.OleDb.OleDbDataReader = _ myOleDbCommand.ExecuteReader() ' Calls Read before attempting to read data While myReader.Read() ' You can access the columns either by column name or by ordinal ' number Console.WriteLine(myReader("Customers").ToString()) End While ' Always close the DataReader when you are done with it myReader.Close() ' And close the connection if not being used further myConnection.Close()

Visual C#

// Opens the active connection myConnection.Open(); // Creates a DataReader and assigns it to myReader System.Data.OleDb.OleDbDataReader myReader = myOleDbCommand.ExecuteReader(); // Calls Read before attempting to read data while (myReader.Read()) { // You can access the columns either by column name or by ordinal // number Console.WriteLine(myReader["Customers"].ToString()); } // Always close the DataReader when you are done with it myReader.Close(); // And close the connection if not being used further myConnection.Close() ;

To access data with a DataReader

1. Call your Command object s ExecuteReader method and assign the returned DataReader to an appropriately typed variable.

2. Iterate through the result set within a While (while) loop. You should perform any operations with the data while inside this loop. You must call the Data Reader object s Read method before using the data.

3. When finished, call the DataReader object s Close method to release the connection.

Retrieving Typed Data Using a DataReader

Although the data exposed by a DataReader is typed as objects, the DataReader also exposes methods to retrieve data contained in a result set. These methods are named Get, along with the name of the type to be retrieved. For example, the method to retrieve a Boolean value is GetBoolean. If you know the type of data in a given column, you can use Get methods to return strongly typed data from that column. For example:

Visual Basic .NET

Dim myBoolean As Boolean myBoolean = myDataReader.GetBoolean(3)

Visual C#

bool myBoolean; myBoolean = myDataReader.GetBoolean(3);

When using these methods, you must use the column s ordinal number; you cannot use the column name. If you only know the column name, you can look up the ordinal number with the GetOrdinal method, as follows:

Visual Basic .NET

Dim CustomerID As Integer Dim Customer As String ' Looks up the ordinal number for the column named 'CustomerID' CustomerID = myDataReader.GetOrdinal("CustomerID") ' Retrieves a string from that field and assigns it to Customer Customer = myDataReader.GetString(CustomerID)

Visual C#

int CustomerID; string Customer; // Looks up the ordinal number for the column named 'CustomerID' CustomerID = myDataReader.GetOrdinal("CustomerID"); // Retrieves a string from that field and assigns it to Customer Customer = myDataReader.GetString(CustomerID);

To retrieve typed data using a DataReader

1. If necessary, look up the column s ordinal number by calling the GetOrdinal method on the column name.

2. Call the appropriate Get method of the DataReader, specifying the ordinal number of the column to return.

Using Multiple Result Sets

If the CommandType property of your Command object is set to Text, and if this feature is supported by your database, you can return multiple result sets with a single command by providing multiple SQL commands in the CommandText property. To indicate multiple commands, separate each command with a semicolon (;). For example:

SELECT * FROM Accounts; SELECT * FROM Creditors

When multiple SQL statements are specified in the CommandText property of a Command object, the statements are executed sequentially. If more than one statement returns a result set, multiple result sets are returned by the DataReader, also sequentially.

The DataReader returns the first result set automatically. To access the next result set, you must call the NextResult method. Like the Read method, the NextResult method returns False (false) when there are no more result sets to be read. Unlike the Read method, however, the DataReader comes into existence positioned at the first result set. If the NextResult method is called before the first result set is read, the first result set is discarded, and the next result set is read. The following code example demonstrates how to use the NextResult method to loop through result sets:

Visual Basic .NET

Do While myReader.Read() ' Add code here to loop through the records of the current ' result set End While ' Switches to the next result set, or returns False if all result ' sets have been read Loop While myReader.NextResult()

Visual C#

do { while (myReader.Read()) { // Add code here to loop through the records of the current // result set } } while (myReader.NextResult());

To read multiple result sets with a DataReader

1. Set the CommandType property of your Command object to Text.

2. Specify multiple SQL statements in the CommandText property of your Command object. These should be separated by a semicolon.

3. Call the ExecuteReader method of your Command object and assign the DataReader to a variable.

4. Use the DataReader object s NextResult method to iterate through the result sets.

Executing Ad Hoc SQL Queries

At times, you might not know until run time which SQL query you want to execute. You might want to receive a search string through user input, programmatically determine columns to be returned, or even determine the appropriate table at run time. You can create, configure, and execute commands all at run time.

The first step in executing an ad hoc SQL query is to build the command string. When building the command string, you should start by creating the outline of the command string. Where required, add string variables that represent the values to be inserted at run time. Use the concatenation operator to connect the strings. For example:

Visual Basic .NET

' Assumes that aString has been declared and the value set previously ' in code Dim Cmd As String Cmd = "SELECT * FROM Employees WHERE Name = '" & aString & "'"

Visual C#

// Assumes that aString has been declared and the value set previously // in code string Cmd; Cmd = "SELECT * FROM Employees WHERE Name = '" + aString + "'";

CAUTION
In the WHERE clause of a SQL statement, string values to be passed to the database must be enclosed in single quotes (' '). If the variable includes any additional single quotes, they should be replaced by two single quotes (' '). Otherwise, the query will fail.

The Command classes of each data provider expose constructors that allow you to set the CommandText and Connection properties at instantiation. After setting these properties, all you need to do is open the connection and execute the command. The following code example demonstrates how a method might receive a search string, build an ad hoc SQL command, and execute it against the database:

Visual Basic .NET

' This example assumes an OleDb connection named myConnection. ' It also assumes Imports System.Data.OleDb Public Sub DeleteRecord(aString As String) Dim Cmd As String Cmd = "DELETE * FROM Employees WHERE Name = '" & aString & "'" ' Specifies Cmd as the command string and myConnection as the ' connection Dim myCommand As New OleDbCommand(Cmd, myConnection) ' Opens the Connection and executes the command myConnection.Open() myCommand.ExecuteNonQuery() ' Always close the connection myConnection.Close() End Sub

Visual C#

// This example assumes an OleDB connection named myConnection. // It also assumes using System.Data.OleDb public void DeleteRecord(string aString) { string Cmd; Cmd = "DELETE * FROM Employees WHERE Name = '" + aString + "'"; // Specifies Cmd as the command string and myConnection as the // connection OleDbCommand myCommand = new OleDbCommand(Cmd, myConnection); // Opens the Connection and executes the command myConnection.Open(); myCommand.ExecuteNonQuery(); // Always close the connection myConnection.Close(); }

CAUTION
When creating and executing ad hoc SQL statements, you must perform validation on any values that come from user input. Failure to validate user input can leave your application vulnerable to SQL injection attacks, which occur when a malicious user passes a damaging SQL statement as a string through unvalidated user input, resulting in the SQL statement being executed against the database. SQL injection attacks can be extremely damaging, costly, and difficult to repair. Always validate user input that is used to generate ad hoc SQL statements.

Creating and Configuring DataAdapters

DataAdapter objects provide the link between a data source and a DataSet by managing the data exchange. A DataAdapter is responsible for moving data between a data source and a DataSet. In some applications, this movement is strictly one-way. Other applications, however, might require continued querying and updating of the data source. The DataAdapter incorporates the functionality required to retrieve data, populate a DataSet, and perform updates as required.

Two primary DataAdapter objects are included with Visual Studio .NET. The SqlDataAdapter is designed to provide optimal communication with SQL Server 7 or later. TheOleDbDataAdapter provides access to any data source that is exposed by an OleDb Provider. Visual Studio .NET 2003 includes two additional DataAdapter objects: the ODBCDataAdapter and the OracleDataAdapter.

A single DataAdapter is generally used to manage data exchange between a single DataTable object in a DataSet and a single-source database table. Because DataSet objects can contain multiple tables, you should create a single DataAdapter for each table you want to add to the DataSet.

There are three ways to create a DataAdapter: You can drag database elements from the Server Explorer; you can use the new Data Adapter Configuration Wizard; or you can manually create and configure the DataAdapter in code. The following sections describe each of these approaches.

Creating a DataAdapter Using Server Explorer

The easiest way to create a DataAdapter is by using the Server Explorer window. The Data Connections node of the Server Explorer window lists each installed data connection. Each Data Connection node expands in turn to provide detail about the database it represents, including a list of available tables, views, and stored procedures. You can create a DataAdapter that represents a table by dragging a table from the Server Explorer window onto the designer. The resulting DataAdapter is of the correct type and has SelectCommand, UpdateCommand, InsertCommand, and DeleteCommand properties that are correctly configured.

NOTE
Although you can create a DataAdapter from a Data view, it is not recommended for read-write data operations because the designer frequently encounters difficulties generating the UPDATE, INSERT, and DELETE commands for operations that involve multiple tables.

You also can configure your DataAdapter to return a subset of the available columns in a table. To do this, expand the node that represents the table that contains the columns you want to select. You can then select individual columns by clicking the column entries while holding down the Ctrl key. When you have selected the columns you want to add, drag them to the designer. This creates a DataAdapter that is configured to manage only these columns.

NOTE
To automatically generate the INSERT, DELETE, and UPDATE statements, you must include the column that defines the primary key.

To create a DataAdapter in Server Explorer

1. In the Server Explorer window, expand the node that represents the connection to the database you are using in your application.

2. Expand the Tables node to display the tables present in the database.

3. Select your table. If you do not want to use the entire table, expand the node represented by the table to display the individual columns. Select columns with the mouse while holding down the Ctrl key.

4. Drag your selection to the designer. A new instance of the appropriate type of DataAdapter is created and configured.

Creating a DataAdapter with the Data Adapter Configuration Wizard

You can create a new DataAdapter by dragging the appropriate DataAdapter class from the Toolbox to the designer. This launches the Data Adapter Configuration Wizard.

To create a DataAdapter with the Data Adapter Configuration Wizard

1. From the Data tab of the Toolbox, drag the appropriate type of DataAdapter onto the designer. This launches the Data Adapter Configuration Wizard. Click Next to begin configuring your DataAdapter.

2. In the Choose Your Data Connection page, select the appropriate Data Connection from the drop-down menu. Click Next to continue.

   NOTE
   If your database is not listed in the drop-down menu, you can create a new connection by clicking the New Connection button. This opens the Data Link Properties window.

3. In the Choose a Query Type page, you are given three options to choose how you want your DataAdapter to communicate with your database. These choices are summarized in Table 6.1.

   Table 6-1. DataAdapter Database Communication Options

   Option	Description
   Use SQL statements.	This option allows you to specify a SQL SELECT statement to select the data that will be retrieved by the DataAdapter. The wizard then generates the appropriate INSERT, UPDATE, and DELETE commands.
   Create new stored procedures.	This option allows you to specify a SQL SELECT statement to create a new stored procedure. The wizard then generates new stored procedures for the INSERT, UPDATE, and DELETE commands.
   Use existing stored procedures.	This option allows you to specify existing stored procedures for the SELECT, INSERT, UPDATE, and DELETE commands.

   Depending on your data provider, some of these options might be unavailable. If an option is unavailable, it appears grayed out in this page.

   Choose the appropriate option, and click Next to continue.

4. Depending on the choice you made in Step 3, you will see one of two panes.

   • If you chose Use SQL Statements or Create New Stored Procedures, you will see the SQL Statement Generation pane. Type the appropriate SELECT command into the pane, or click the Query Builder button to build your statement with the SQL Query Builder. Appropriate INSERT, DELETE, and UPDATE commands or stored procedures will be generated.

   • If you select Use Existing Stored Procedures, you will see the Bind Commands To Existing Stored Procedures page. From each of the four drop-down menus, choose the appropriate stored procedure to perform the SELECT, UPDATE, INSERT, and DELETE commands.

   NOTE
   The SQL Query Builder is a graphical tool that can assist you in building SQL statements. If you are unfamiliar with SQL syntax, this tool can be a great benefit. See Lesson 2 of this chapter for an overview of SQL syntax.

5. Click Finish. Your DataAdapter is now configured and ready to use.

Retrieving Data Using DataAdapters

A DataSet is an in-memory representation of data that is inherently disconnected from the database. A DataSet can represent a complete set of data including multiple tables, data relationships, and constraints. Because a DataSet is a disconnected representation of data, it can include data from multiple data sources. DataAdapter objects manage all interactions between DataSet objects and databases.

The DataAdapter encapsulates the functionality required to fill a DataSet with data and to update the database. It acts as a bridge between the connection and the DataSet. The DataAdapter maintains a reference to a database connection in its Connection property, which represents the database that any data actions are executed against. The DataAdapter also exposes a SelectCommand property that contains instructions for selecting data from a data source as a Command object. Like other data provider members, there is a DataAdapter implementation for each data provider.

You can fill a DataSet with data by calling the Fill method of the DataAdapter. The Fill method executes the instructions contained within the SelectCommand property across the connection specified by the Connection property, and fills a specified DataSet with the resultant data. You must specify either a DataSet or a DataTable as the target of a Fill method. For example:

Visual Basic .NET

' This example assumes the existence of a Data Adapter named ' myDataAdapter Dim myDataSet As New DataSet() ' Executes the SelectCommand and fills myDataSet with the resultant ' data myDataAdapter.Fill(myDataSet)

Visual C#

// This example assumes the existence of a Data Adapter named // myDataAdapter DataSet myDataSet = new DataSet(); // Executes the SelectCommand and fills myDataSet with the resultant // data myDataAdapter.Fill(myDataSet);

Note that no interactions with the Connection object take place. When the Fill command is executed, the connection opens just long enough to retrieve the selected data, and then it closes again. Once the data is retrieved, it becomes disconnected from the database. It then can be manipulated by program logic independent of the database and updated as necessary.

To retrieve data with a DataAdapter

1. Create an instance of the appropriate type of DataAdapter that specifies the data you want to select.

2. Create an instance of a DataSet or DataTable object.

3. Call the Fill method of the DataAdapter, specifying the DataTable or DataSet as the target.

When working with DataAdapter objects, a single DataAdapter is generally used to manage a single table of data. If you want to load multiple tables into a single DataSet, you should use multiple DataAdapter objects. A single DataSet can be the target of multiple Fill commands. When each DataAdapter calls its Fill method, a new DataTable is created, filled with data, and added to the DataSet.

To fill a DataSet with multiple tables

1. Create an instance of a DataSet.

2. Create a DataAdapter of the appropriate type for each table you want to have represented in your DataSet.

3. Sequentially call the Fill method of each DataAdapter, specifying your DataSet as the target.

NOTE
If you add related tables to your DataSet, these relationships are not carried over by the DataAdapter. You must manually re-create the relationships in your DataSet by creating new DataRelation objects. This is discussed in Lesson 3 of this chapter.

Previewing Data

You can preview the data that will be returned by your DataAdapter objects by choosing Preview Data from the Data menu. This launches the Data Adapter Preview window shown in Figure 6.3.

Figure 6-3. The Data Adapter Preview window.

This window allows you to verify that your DataAdapter objects are correctly configured and returning the correct data. Available DataAdapter objects are shown in the Data Adapter drop-down menu. You can choose to examine any or all of them. Available DataSet objects are shown in the Target Dataset drop-down menu, where you can choose the DataSet to fill. Clicking the Fill Dataset button executes the Fill method of the DataAdapter objects you chose and displays the results in the Results grid. Parameters are displayed in the Parameters grid.

Typed DataSet Objects

Standard DataSet objects are inherently weakly typed. Every data point is exposed as an object and must be converted to the appropriate type to perform any type- specific manipulations. Working with weakly typed variables can cause type- mismatch errors, which can be difficult to debug. As an alternative to working with weakly typed variables, ADO.NET provides a typed DataSet.

As the name implies, a typed DataSet is a DataSet that implements strong typing for each member. Tables and columns of a typed DataSet are accessible through user-friendly names for the tables and columns you are working with, and data is exposed as typed instead of as objects. This provides many advantages, such as increasing the readability and maintainability of your code. Type-mismatch errors are discovered at compile time instead of at run time, saving valuable testing cycles. You can use friendly name syntax instead of collection syntax, and your typed data members can be displayed at design time in the Intellisense window. To illustrate, the code example to follow contrasts equivalent lines of code written using untyped DataSet objects and typed DataSet objects. Each example returns the OrderID column from the first record of the Orders table of the dsorders DataSet. The first example is written using untyped DataSet objects.

Visual Basic .NET

Dim myOrder As String myOrder = CType(dsOrders.Tables("Orders").Rows(0).Item("OrderID"), _ String)

Visual C#

string myOrder; myOrder = (string)dsOrders.Tables["Orders"].Rows[0]["OrderID"];

The following code example is written with a typed DataSet. Note how much easier the code is to read. Note also that explicit casts are unnecessary.

Visual Basic .NET

Dim myOrder As String myOrder = dsOrders.Orders(0).OrderID

Visual C#

string myOrder; myOrder = dsOrders.Orders[0].OrderID;

A typed DataSet is actually an instance of a new class derived from the DataSet class. The structure of the new class is based on an XML schema file (XSD file, which stands for XML Schema Definition) that defines the structure of the DataSet, including the names of the tables and columns. Because a schema file is required to create the new DataSet, you can only create a typed DataSet when you know the structure of the data with which you will be working.

You choose Generate Dataset from the Data menu to generate a typed DataSet. This displays the Generate Dataset dialog box shown in Figure 6.4.

Figure 6-4. The Generate Dataset dialog box.

The window prompts you to choose a new DataSet or to regenerate an existing DataSet. The available tables and their DataAdapter objects are listed in the box. Selecting a check box at the bottom of the dialog box allows you to add an instance of your new DataSet class to the designer.

Clicking OK in this dialog box generates an XSD file with the correct schema for your DataSet and adds it to your project. If the check box was selected, an instance of a DataSet based on this schema is added to the designer. When you fill the new DataSet, the table and column names will match the names specified in the schema, and the data will be typed to match the type of data returned by the DataAdapter objects.

To generate a new strongly typed DataSet

1. Create and configure DataAdapter objects of the appropriate type to return the data tables you want.

2. From the Data menu, choose Generate Dataset.

TIP
You can also right-click a DataAdapter in the designer and choose Generate Dataset, or click Generate Dataset in the Properties window.

1. In the Generate Dataset dialog box, choose New and give your DataSet an appropriate name.

2. From the list of available tables, select the tables to be included in your schema. If you want to add an instance of the DataSet to the designer, select that check box as well.

3. Click OK. An XSD file is generated and added to the project. If you have chosen to do so, an instance of your new class is also added to the designer.

To populate a strongly typed DataSet with data

Call the Fill method of each DataAdapter with data you want to add to the DataSet, specifying your DataSet as the target.

Lesson Summary

• The Connection object connects to a database. You can create a Connection object by dragging a connection from the Server Explorer to the designer or by creating a new Connection object.

• The Command object represents a SQL command or a reference to a stored procedure in the database. Three methods for executing database commands are shared by the OleDbCommand object and the SqlCommand object. They are:

  • ExecuteNonQuery

  • ExecuteScalar

  • ExecuteReader

• Parameters represent values required for the execution of commands represented by Command objects. The OleDbCommand object uses a question mark (?) as a placeholder for parameters in SQL statements, whereas the SqlCommand object uses named parameters.

• DataReader objects provide forward-only, read-only, connected data access and require the exclusive use of a data connection.

• DataReader objects expose methods that allow retrieval of strongly typed data.

• DataAdapter objects facilitate interaction between a database and a DataSet by managing the commands required to fill the DataSet from the database and update the database from the DataSet.

• Typed DataSet objects are instances of classes derived from the DataSet class that are based on an XML schema and expose strongly typed data and member tables, and columns with friendly names.