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.
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 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.
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 STEPA.1 Creating a SqlConnection from Server Explorer
|
You can also create a SqlConnection directly in code. Step By Step A.2 demonstrates this technique.
STEP BY STEPA.2 Creating a SqlConnection in Code
|
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 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.
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 STEPA.3 Using the ExecuteScalar Method
|
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 STEPA.4 Using a SqlDataReader
|
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.
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 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.
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 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 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.
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 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.
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.
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.
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 |
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.
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 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.
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 |
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.
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
|
Top |