Introduction

   

---

The ADO.NET Object Model

In generic terms, the data provider objects manage the database for your application, and the DataSet objects manage the data model for the application. In this section, I'll drill into these objects in turn , starting with the data provider objects. I'll use the SQL Server data provider objects for examples, but keep in mind that the basic ADO.NET syntax is the same no matter what data source you're using.

Data Providers and Their Objects

You should know about five main data provider objects:

• Connection

• Command

• Parameter

• DataReader

• DataAdapter

These are the generic names for the classes defined in System.Data.Common. In fact, you'll seldom (if ever) write code that uses these generic objects. Each data provider has implementations of these objects with specific names .

A data provider is a namespace that implements these five classes (and some other classes and enumerations) for use with a particular database. For example, you can work with data stored in a SQL Server database by using the SQL Server data provider, which is implemented in the System.Data.SqlClient namespace. In this namespace, the object names are as follows :

• SqlConnection

• SqlCommand

• SqlParameter

• SqlDataReader

• SqlDataAdapter

But the SQL Server data provider is not the only alternative for retrieving data in ADO.NET. The .NET Framework also ships with the OLE DB data provider, implemented in the System.Data.OleDb namespace. In this namespace, the corresponding object names are as follows:

• OleDbConnection

• OleDbCommand

• OleDbParameter

• OleDbDataReader

• OleDbDataAdapter

EXAM TIP

Not All OLE DBs Are Equal Although from the name it seems that the OLE DB data provider should work with any existing OLE DB provider, that's not the case. It's only designed to work with the SQL Server, Jet 4.0, and Oracle OLE DB providers. Other providers might work but are not supported.

Although the .NET Framework 1.0 only includes two data providers, other alternatives exist. For example, Microsoft released both an ODBC data provider and an Oracle data provider as add-ons to the .NET Framework 1.0 and included them in the .NET Framework 1.1. Third parties are also planning to release other providers. You should expect to see many more data providers in the coming months, just as there were many ODBC and OLE DB drivers in earlier data access models.

I'll use the SQL Server data provider objects in all my examples, but you should keep in mind that the techniques you learn to work with objects from this namespace will also work with objects from other data provider namespaces.

The SqlConnection Object

The SqlConnection object represents a single persistent connection to a SQL Server data source. ADO.NET automatically handles connection pooling, which contributes to better application performance by eliminating the overhead involved in constantly creating and destroying connections. When you call the Close method of a SqlConnection object, it is returned to a connection pool. Connections in a pool are not immediately destroyed by ADO.NET. Instead, they're available for reuse if another part of your application requests a SqlConnection that matches in details a previously closed SqlConnection.

Table A.1 shows the most important members of the SqlConnection object.

Table A.1. SqlConnection Object Members

Member	Type	Description
BeginTransaction	Method	Starts a new transaction on this SqlConnection
Close	Method	Returns the SqlConnection to the connection pool
ConnectionString	Property	Specifies the server to be used by this SqlConnection
CreateCommand	Method	Returns a new SqlCommand object that executes via this SqlConnection
Open	Method	Opens the SqlConnection

There are several ways to create SqlConnection objects in Visual Studio .NET applications. Step By Step A.1 shows how you can use drag and drop to build a SqlConnection object.

STEP BY STEP

A.1 Creating a SqlConnection from Server Explorer

1. Create a new Visual Basic .NET Windows application named 310AppA. Add a new form to the project.

2. Hover your mouse over the Server Explorer tab until Server Explorer opens. Right-click on the Data Connections node and select Add Connection.

3. Fill in the Data Link Properties dialog box as shown in Figure A.2. You might need to supply a server name, username, and password, depending on your network configuration. Click OK.

   Figure A.2. Creating a new data connection.

   

4. Drag the newly-created server node from Server Explorer and drop it on the form. This will create an object named SqlConnection1 in the component tray.

5. Place a Button control named btnConnect and a TextBox control named txtConnectionString on the form.

6. Double-click the Button control to open the form's module. Enter this statement at the top of the module:

    Imports System.Data.SqlClient 

7. Enter this code to handle the Click event of the Button control:

    Private Sub btnConnect_Click(_  ByVal sender As System.Object, _  ByVal e As System.EventArgs) Handles btnConnect.Click     SqlConnection1.Open()     txtConnectionString.Text = _      SqlConnection1.ConnectionString     SqlConnection1.Close() End Sub 

8. Set the form as the startup object for the project.

9. Run the project and click the button. The code will connect to the SQL Server database on the local computer and echo the connection string to the TextBox control.

You can also create a SqlConnection directly in code. Step By Step A.2 demonstrates this technique.

STEP BY STEP

A.2 Creating a SqlConnection in Code

1. Add a new form to your Visual Basic .NET project.

2. Place a Button control named btnConnect and a TextBox control named txtConnectionString on the form.

3. Double-click the Button control to open the form's module. Enter this statement at the top of the module:

    Imports System.Data.SqlClient 

4. Enter this code to handle the Click event of the Button control:

    Private Sub btnConnect_Click(_  ByVal sender As System.Object, _  ByVal e As System.EventArgs) Handles btnConnect.Click     Dim cnn As SqlConnection = New SqlConnection()     cnn.ConnectionString = "Data Source=(local);" & _      "Initial Catalog=Northwind;" & _      "Integrated Security=SSPI"     cnn.Open()     txtConnectionString.Text = cnn.ConnectionString     cnn.Close() End Sub 

5. Set the form as the startup object for the project.

6. Run the project and click the button. The code will connect to the SQL Server database on the local computer and echo the connection string to the TextBox control.

EXAM TIP

The Parts of a Connection String A connection string has three parts. First is the Data Source, which is the name of the server to which you'd like to connect. You can use "(local)" as a shortcut name for the SQL Server instance running on the same computer as this code. Second is the Initial Catalog, which is the name of the database on the server to use. Third is authentication information. This can either be "Integrated Security=SSPI" to use Windows authentication, or " User ID= username ;Password= password " to use SQL Server authentication. Other optional parameters exist, but these three are the most important.

The SqlCommand and SqlParameter Objects

The SqlCommand and SqlParameter objects work together to retrieve data from a data source after you've made a connection. The SqlCommand represents something that can be executed. This could be an ad hoc query string or a stored procedure name. The SqlParameter object represents a single parameter to a stored procedure.

Table A.2 shows the most important members of the SqlCommand object.

Table A.2. SqlCommand Object Members

Member	Type	Description
CommandText	Property	Statement to be executed by the SqlCommand
CommandType	Property	Enumeration indicating what type of command this SqlCommand represents
Connection	Property	SqlConnection through which this SqlCommand executes
CreateParameter	Method	Creates a new SqlParameter for this SqlCommand
ExecuteNonQuery	Method	Executes a SqlCommand that does not return a resultset
ExecuteReader	Method	Executes a SqlCommand and places the results in a SqlDataReader
ExecuteScalar	Method	Executes a SqlCommand and returns the first column of the first row of the resultset
ExecuteXmlReader	Method	Executes a SqlCommand and places the results in an XmlReader object
Parameters	Property	Collection of SqlParameter objects for this SqlCommand

Step By Step A.3 gives an example of using the ExecuteScalar method, which provides you with an easy way to retrieve a single value (such as an aggregation) from a database.

STEP BY STEP

A.3 Using the ExecuteScalar Method

1. Add a new form to your Visual Basic .NET project.

2. Place a Button control named btnCount , a TextBox control named txtCountry , and a Label control named lblResults on the form. Figure A.3 shows the design of this form.

   Figure A.3. Designing a form to demonstrate the ExecuteScalar method.

   

3. Double-click the Button control to open the form's module. Enter this statement at the top of the module:

    Imports System.Data.SqlClient 

4. Enter this code to handle the Click event of the Button control:

    Private Sub btnCount_Click(_  ByVal sender As System.Object, _  ByVal e As System.EventArgs) Handles btnCount.Click     ' Connect to the database     Dim cnn As SqlConnection = New SqlConnection()     cnn.ConnectionString = "Data Source=(local);" & _      "Initial Catalog=Northwind;" _      "Integrated Security=SSPI"     ' Create a new ad hoc query to count     ' customers in the selected country     Dim cmd As SqlCommand = cnn.CreateCommand     cmd.CommandType = CommandType.Text     cmd.CommandText = _      "SELECT COUNT(*) FROM Customers " & _      "WHERE Country = '" & _      txtCountry.Text & "'"     ' Use ExecuteScalar to return results     cnn.Open()     lblResults.Text = "There are " & _      cmd.ExecuteScalar() & _      " customers in " & txtCountry.Text     cnn.Close() End Sub 

5. Set the form as the startup object for the project.

6. Run the project. Enter a country name such as France and click the button. The code will connect to the SQL Server database on the local computer and fill in the Label control's text with a string that includes the results of the ad hoc query.

The SqlDataReader Object

The SqlDataReader object is designed to be the fastest possible way to retrieve a resultset from a database. SqlDataReader objects can only be constructed by calling the ExecuteReader method of a Command object (see Step By Step A.4). The resultset contained in a SqlDataReader is forward-only, read-only.

That is, you can only read the rows in the resultset sequentially from start to finish, and you can't modify any of the data. This behavior, of simply delivering data as quickly as possible, results in the SqlDataReader being called a "firehose" data connection.

STEP BY STEP

A.4 Using a SqlDataReader

1. Add a new form to your Visual Basic .NET project.

2. Place a Button control named btnGetCustomers and a ListBox control named lbCustomers on the form.

3. Double-click the Button control to open the form's module. Enter this statement at the top of the module:

    Imports System.Data.SqlClient 

4. Enter this code to handle the Click event of the Button control:

    Private Sub btnGetCustomers_Click(_  ByVal sender As System.Object, _  ByVal e As System.EventArgs) _  Handles btnGetCustomers.Click     ' Connect to the database     Dim cnn As SqlConnection = New SqlConnection()     cnn.ConnectionString = "Data Source=(local);" & _      "Initial Catalog=Northwind;" & _      "Integrated Security=SSPI"     ' Create a new query to retrieve customer names     Dim cmd As SqlCommand = cnn.CreateCommand     cmd.CommandType = CommandType.Text     cmd.CommandText = _      "SELECT CompanyName FROM Customers " & _      "ORDER BY CompanyName"     ' Dump the data to the user interface     cnn.Open()     Dim dr As SqlDataReader = cmd.ExecuteReader     Do While dr.Read()         lbCustomers.Items.Add(dr.GetString(0))     Loop     ' Clean up     dr.Close()     cnn.Close() End Sub 

5. Set the form as the startup object for the project.

6. Run the project and click the button. The code will connect to the SQL Server database on the local computer and fill the ListBox control with a list of customers from the database.

EXAM TIP

Stored Procedures for Speed You could improve the performance of this code even more by using a stored procedure instead of an ad hoc query to deliver the customer names.

You can think of SqlDataReader as a data structure that can contain one row of data at a time. Each call to the SqlDataReader.Read method loads the next row of data into this structure. When there are no more rows to load, the Read method returns False, which indicates that you've reached the end of the data. To retrieve individual columns of data from the current row, SqlDataReader provides a series of methods (such as the GetString method used in the preceding code) that take a column number and return the data from that column. There's also a GetValue method for use with any column, but the typed methods are faster.

Table A.3 shows the most important members of the SqlDataReader object. You don't need to memorize all the data methods (others aren't even shown in this table), but you should understand the pattern that they represent.

Table A.3. SqlDataReader Object Members

Member	Type	Description
Close	Method	Closes the SqlDataReader
GetBoolean	Method	Gets a Boolean value from the specified column
GetByte	Method	Gets a byte value from the specified column
GetChar	Method	Gets a character value from the specified column
GetDateTime	Method	Gets a date/time value from the specified column
GetDecimal	Method	Gets a decimal value from the specified column
GetDouble	Method	Gets a double value from the specified column
GetFloat	Method	Gets a float value from the specified column
GetGuid	Method	Gets a GUID value from the specified column
GetInt16	Method	Gets a 16-bit integer value from the specified column
GetInt32	Method	Gets a 32-bit integer value from the specified column
GetInt64	Method	Gets a 64-bit integer value from the specified column
GetString	Method	Gets a string value from the specified column
GetValue	Method	Gets a value from the specified column
GetValues	Method	Gets an entire row of data and places it in an array of objects
IsDbNull	Method	Indicates whether a specified column contains a Null value
Read	Method	Loads the next row of data into the SqlDataReader

WARNING

Close Your SqlDataReaders! The SqlDataReader makes exclusive use of its SqlConnection object as long as it is open. You won't be able to execute any other SqlCommand objects on that connection as long as the SqlDataReader is open. Always call SqlDataReader.Close as soon as you're done retrieving data.

The SqlDataAdapter Object

The final data provider object I'll consider, the SqlDataAdapter, provides a bridge between the data provider objects and the DataSet objects you'll learn about in the next section. You can think of the SqlDataAdapter as a two-way pipeline between the data in its native storage format and the data in a more abstract representation (the DataSet) designed for manipulation in your application.

Table A.4 shows the most important members of the SqlDataReader object.

Table A.4. SqlDataAdapter Object Members

Member	Type	Description
DeleteCommand	Property	SqlCommand used to delete rows from the data source
Fill	Method	Transfers data from the data source to a DataSet
InsertCommand	Property	SqlCommand used to insert rows into the data source
SelectCommand	Property	SqlCommand used to retrieve rows from the data source
Update	Method	Transfers data from a DataSet to the data source
UpdateCommand	Property	SqlCommand used to update rows in the data source

Later in this appendix (in the "Using Datasets" section), you'll learn more about using the SqlDataAdapter in conjunction with the DataSet to manipulate data.

The DataSet Objects

The second set of ADO.NET objects are the DataSet objects, which are all contained in the System.Data namespace. Unlike the data provider objects, there's only one set of DataSet objects. The DataSet objects represent data in an abstract form that's not tied to any particular database implementation. In this section, I'll introduce you to the DataSet and the other objects that it contains:

• DataSet

• DataTable

• DataRelation

• DataRow

• DataColumn

• DataView

The DataSet Object

The DataSet itself is a self-contained client-side memory-resident representation of relational data. A DataSet contains other objects, such as DataTables and DataRelations, that hold the actual data and information about the design of the data. The DataSet is designed to be easy to move between components. In particular, specific methods convert a DataSet to an XML file and vice versa. Because they're easily serialized, DataSets can also be passed between remoted components or Web service servers and clients .

WARNING

Microsoft-Specific Technology As a part of the .NET Framework, DataSet objects are currently Microsoft-only objects. Returning a DataSet from a Web service might make it more difficult for clients written in non-.NET platforms to use.

Table A.5 shows the most important members of the DataSet object.

Table A.5. DataSet Object Members

Member	Type	Description
AcceptChanges	Method	Marks all changes in the DataSet as having been accepted
Clear	Method	Removes all data from the DataSet
GetChanges	Method	Gets a DataSet that contains only the changed data in this DataSet
GetXml	Method	Gets an XML representation of the DataSet
GetXmlSchema	Method	Gets an XSD representation of the DataSet
Merge	Method	Merges two DataSets
ReadXml	Method	Loads the DataSet from an XML file
ReadXmlSchema	Method	Loads the DataSet's schema from an XSD file
Relations	Property	A collection of DataRelation objects
Tables	Property	A collection of DataTable objects
WriteXml	Method	Writes the DataSet to an XML file
WriteXmlSchema	Method	Writes the DataSet's schema to an XSD file

The DataTable Object

The DataTable object represents a single table within the DataSet. A single DataSet can contain many DataTable objects. Table A.6 shows the most important members of the DataTable object.

Table A.6. DataTable Object Members

Member	Type	Description
ChildRelations	Property	A collection of DataRelation objects that refer to children of the DataTable
Clear	Method	Removes all data from the DataTable
ColumnChanged	Event	Fires when the data in any row of a specified column has been changed
ColumnChanging	Event	Fires when the data in any row of a specified column is about to be changed
Columns	Property	A collection of DataColumn objects
Constraints	Property	A collection of Constraint objects
NewRow	Method	Creates a new, blank row in the DataTable
ParentRelations	Property	A collection of DataRelation objects that refer to parents of the DataTable
PrimaryKey	Property	An array of DataColumn objects that provides the primary key for the DataTable
RowChanged	Event	Fires when any data in a DataRow has been changed
RowChanging	Event	Fires when any data in a DataRow is about to be changed
RowDeleted	Event	Fires when a row has been deleted
RowDeleting	Event	Fires when a row is about to be deleted
Rows	Property	A collection of DataRow objects
Select	Method	Selects an array of DataRow objects that meets specified criteria
TableName	Property	The name of the DataTable

As you can see, you can manipulate a DataTable as either a collection of DataColumn objects or a collection of DataRow objects. The DataTable also provides events that you can use to monitor data changes. For example, you might bind a DataTable to a DataGrid and use these events to track the user's operations on the data within the DataGrid.

The DataRelation Object

As I mentioned previously, the DataSet can represent an entire relational database. The DataRelation object stores information on the relations between DataTables within a DataSet. Table A.7 shows the most important members of the DataRelation object.

Table A.7. DataRelation Object Members

Member	Type	Description
ChildColumns	Property	Collection of DataColumn objects that defines the foreign key side of the relation
ChildKeyConstraint	Property	Returns a ForeignKeyConstraint object for the relation
ChildTable	Property	DataTable from the foreign key side of the relation
ParentColumns	Property	Collection of DataColumn objects that defines the primary key side of the relation
ParentKeyConstraint	Property	Returns a PrimaryKeyConstraint object for the relation
ParentTable	Property	DataTable from the primary key side of the relation
RelationName	Property	Name of the DataRelation

The DataRow Object

Continuing down the object hierarchy from the DataSet past the DataTable, you come to the DataRow. As you can guess by now, the DataRow represents a single row of data. When you're selecting, inserting, updating, or deleting data in a DataSet, you'll normally work with DataRow objects.

Table A.8 shows the most important members of the DataRow object.

Table A.8. DataRow Object Members

Member	Type	Description
BeginEdit	Method	Starts editing the DataRow
CancelEdit	Method	Discards an edit in progress
Delete	Method	Deletes the DataRow from its parent DataTable
EndEdit	Method	Ends an edit in progress, saving the changes
Item	Property	Returns the data from a particular column in the DataRow
IsNull	Method	Returns True if a specified column contains a Null value
RowState	Property	Returns information on the current state of a DataRow (for example, whether it has been changed since it was last saved to the database)

The DataColumn Object

The DataTable also contains a collection of DataColumn objects. A DataColumn represents a single column in the DataTable. By manipulating the DataColumn objects, you can determine and even change the structure of the DataTable.

Table A.9 shows the most important members of the DataColumn object.

Table A.9. DataColumn Object Members

Member	Type	Description
AllowDbNull	Property	Indicates whether the DataColumn can contain Null values
AutoIncrement	Property	Indicates whether the DataColumn is an identity column
ColumnName	Property	Name of the DataColumn
DataType	Property	Data type of the DataColumn
DefaultValue	Property	Default value of this DataColumn for new rows of data
MaxLength	Property	Maximum length of a text DataColumn
Unique	Property	Indicates whether values in the DataColumn must be unique across all rows in the DataTable

The DataView Object

Finally, the DataView object represents a view of the data contained in a DataTable. A DataView might contain every DataRow from the DataTable, or it might be filtered to contain only specific rows. Filtering can be done by SQL expressions (returning, for example, only rows for customers in France) or by row state (returning, for example, only rows that have been modified).

Table A.10 shows the most important members of the DataView object.

Table A.10. DataView Object Members

Member	Type	Description
AddNew	Method	Adds a new row to the DataView
AllowDelete	Property	Indicates whether deletions can be performed through this DataView
AllowEdit	Property	Indicates whether updates can be performed through this DataView
AllowNew	Property	Indicates whether insertions can be performed through this DataView
Count	Property	Number of rows in this DataView
Delete	Method	Deletes a row from this DataView
Find	Method	Searches for a row in the DataView
FindRows	Method	Returns an array of rows matching a filter expression
Item	Property	Returns a DataRowView object representing a particular row in the DataView
Sort	Method	Sorts the data in a DataView

REVIEW BREAK

• The ADO.NET object model includes both database-specific data provider classes and database-independent DataSet classes.

• Data providers contain implementations of the Connection, Command, Parameter, DataReader, and DataAdapter objects optimized for a particular database product.

• The SqlConnection object represents a connection to a SQL Server database.

• The SqlCommand object represents a command that can be executed.

• The SqlParameter object represents a parameter of a stored procedure.

• The SqlDataReader object provides a fast way to retrieve a resultset from a command.

• The SqlDataAdapter object implements a two-way pipeline between the database and the data model.

• The DataSet represents an entire relational database in memory. It's composed of DataTable, DataRelation, DataRow, and DataColumn objects.

• The DataView object provides a filtered row of the data from a DataTable.

---

Top