Understanding ADO.NET

Connecting to a database and manipulating the data in a relational database are important tasks performed in many applications. The .NET Framework programmers use ADO.NET to do database programming. ADO.NET is an integral part of the .NET Framework. It is a set of classes that exposes data access services to the .NET programmer, and it provides a rich set of components for creating distributed, data-sharing applications. With ADO.NET you can create a database; select, insert, and delete data in a table; execute a stored procedure; change the structure of a table; and so on. In addition to working with the structure and data in a relational database, you can use ADO.NET to manipulate XML documents.

ADO.NET is a broad topic. A complete discussion of ADO.NET requires a book of its own and therefore cannot be presented in this chapter alone. This chapter discusses the architecture of ADO.NET and the basic features you can use to access and manipulate data. You will find a discussion of the object models as well as examples you can use to do various database tasks.

After the overview of the ADO.NET architecture, the following sections specifically focus on the two main components in ADO.NET: the .NET data providers (in "Introducing the .NET Data Providers") and the DataSet and accompanying objects (in "Working with DataSets").

Introducing ADO.NET Architecture

There are two central components in ADO.NET: the data providers and the System.Data.DataSet object. The object model for the .NET data providers is similar to those in ActiveX Data Objects (ADO) that you use to access and manipulate data in relational databases in classic ASP. The .NET data providers connect to and query a data source. Basically, you can send any SQL statement to the data source using a .NET data provider, which means in many cases working with .NET data providers alone is sufficient.

However, in ADO.NET you can also use the DataSet object to work with a disconnected set of data. The DataSet object gives you more flexibility and the capability to bind data to Web server controls. It is not equivalent to the recordset object in ADO. In fact, it is more like a disconnected data container that resembles a database that can hold data tables and maintain relationships between any two tables.

Figure 6-1 shows the architecture of ADO.NET.

Figure 6-1: ADO.NET architecture

The .NET Framework 1.1 class library provides eight namespaces that contain types for ADO.NET:

• System.Data

• System.Data.Common

• System.Data.OleDb

• System.Data.SqlClient

• System.Data.SqlTypes

• System.Data.Odbc

• System.Data.OracleClient

• System.Data.SqlCe

Note

The System.Data.Odbc, System.Data.OracleClient, and System.Data.SqlCe namespaces were not available in the .NET Framework 1.0.

The most important members of these namespaces are discussed in detail throughout the following sections. The two main components of ADO.NET are discussed in the sections "Introducing the .NET Data Providers" and "Working with DataSets".

Introducing the .NET Data Providers

There are five data providers in ADO.NET:

OLE DB .NET data provider: This is the data provider for OLE DB data sources, including Microsoft SQL Server version 6.5 and earlier. Types you can use to access and manipulate data in OLE DB data sources are located in the System.Data.OleDb namespace.

SQL Server .NET data provider: This is the data provider for Microsoft SQL Server version 7.0 and later. This data provider is described by the collection of types in the System.Data.SqlClient namespace. This provider uses its own protocol to communicate with SQL Server and does not support the use of an ODBC Data Source Name (DSN).

ODBC .NET data provider: This is the data provider for ODBC databases. You can find types that you use to access and manipulate data in ODBC data sources in the System.Data.Odbc namespace.

Oracle .NET data provider: This is the data provider for Oracle databases. You can find types to help you access and manipulate data in Oracle databases in the System.Data.OracleClient namespace.

SQL Server CE .NET data provider: This is the data provider for SQL Server CE. The types for accessing and manipulating data in SQL Server CE databases are available in the System.Data.SqlServerCe namespace.

All the five data provider namespaces contain equivalent classes that provide similar functionality.

The four most important objects in a .NET data provider are the connection object, the command object, the data adapter object, and the data reader object. All the data provider namespaces have classes that represent those objects.

A type name in the System.Data.SqlClient namespace starts with Sql, and in the System.Data.OleDb namespace, type names start with OleDb. In the Oracle data provider they start with Oracle, in SQL Server CE with SqlCe, and in the ODBC data provider with Odbc. For example, the connection object in the System.Data.SqlClient namespace is represented by the SqlConnection class, whereas in the System.Data.OleDb namespace the same object is represented by the OleDbConnection class. These classes are similar and share most properties and methods.

Table 6-1 lists the important members of the data provider namespaces. The following sections discuss in detail the connection, command, data adapter, and data reader classes. For other classes you should refer to the .NET Framework reference.

Table 6-1: Important Members of the Data Provider Namespaces

SQL SERVER	OLE DB	ODBC	ORACLE	SQL SERVER CE	OBJECT REPRESENTED
SqlCommand	OleDbCommand	OdbcCommand	OracleCommand	SqlCeCommand	Command object
SqlConnection	OleDbConnection	OdbcConnection	OracleConnection	SqlCeConnection	Connection object
SqlDataAdapter	OleDbDataAdapter	OdbcDataAdapter	OracleDataAdapter	SqlCeDataAdapter	Data adapter object
SqlDataReader	OleDbDataReader	OdbcDataReader	OracleDataReader	SqlCeDataReader	Data reader object

The Connection Object

The connection object in ADO.NET represents a connection to a data source and supports the notion of transactions. You will first learn about the connection object model—in other words, the properties, methods, and events of the connection object. Then, you will see how you can build connection strings to access data sources in the five .NET data providers.

The Connection Object's Properties

The following are the common properties of the connection classes:

• ConnectionString: The string used to connect and open a data source.

• ConnectionTimeout: The number of seconds to wait when attempting to establish a connection before terminating the effort and throwing an exception. This property is not available in the System.Data.OracleClient.OracleConnection class.

• Database: The current database or the database to which to connect. This property is not available in the System.Data.OracleClient.OracleConnection class.

• DataSource: In the OleDbConnection class, this property represents the path and filename of the data source. In the SqlConnection and OracleConnection classes, this is the name of the instance of database server to which to connect.

• ServerVersion: The version of the server to which the client is connected. This property is not available in the System.Data.SqlServerCe.SqlCeConnection class.

• State: The state of the connection. Its value is a bitwise combination of the members of the System.Data.ConnectionState enumeration: Broken, Closed, Connecting, Executing, Fetching, and Open.

The OleDbConnection class has one property not available in other connection classes: Provider. This property is a string containing the name of the OLE DB provider.

The Connection Object's Methods

The following are the common methods of the connection classes:

• BeginTransaction: Begins a transaction. This method returns a transaction object.

• ChangeDatabase: Changes the current database for the open connection. This property is not available in the System.Data.OracleClient.OracleConnection class.

• Close: Closes the connection. If connection pooling is enabled, the connection object is returned to the pool. Closing a connection rolls back any pending transaction.

• CreateCommand: Creates and returns a command object associated with the connection.

• Dispose: Destroys the connection object.

• Open: Opens a database connection.

Of these methods, Open and Close are the most frequently used when working with the connection object. You will also use the BeginTransaction method to begin a transaction. However, there are no methods for committing and rolling back a transaction. For more information on how to commit or roll back a transaction, see "Working with Transactions" later in this chapter.

The Connection Object's Events

The connection classes have two common events:

• InfoMessage: This event is triggered when an information message is added.

• StateChange: This event is raised when the connection state changes.

Building OLE DB Connection Strings

Building a connection string is sometimes the trickiest part of working with the connection object. Oftentimes a connection object failure is simply caused by an incorrect piece of information in the connection string.

A connection string for a .NET OLE DB data provider must match the format of an OLE DB connection string. The Provider value must not be "MSDASQL" because the OLE DB .NET data provider does not support the OLE DB Provider for ODBC (MSDASQL).

The following are two examples of valid OLE DB connection strings:

 Provider=SQLOLEDB;Data Source=localhost;Initial Catalog=MyDb; Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Data\MyDb.mdb 

Building SQL Server Connection Strings

An SQL Server connection string comprises a set of name-value pairs specifying details on the SQL Server instance and the user credential. Table 6-2 describes the list of names that you can use in an SQL Server .NET data provider connection string.

Table 6-2: Valid Names for Values Within an SQL Server Connection String

NAME	DEFAULT VALUE	DESCRIPTION
Application Name		The name of the application.
AttachDBFilename or Extended Properties or Initial File Name		The full primary filename of an attachable database. The database name must be specified with the keyword database.
Connect Timeout or Connection Timeout	15	The number of seconds to wait for a connection to the server before terminating the effort and throwing an exception.
Connection Lifetime	0	This name-value pair is used in a clustered configuration to force load balancing between a running server and a server just started. The connection lifetime specifies the maximum number of seconds the creation time of a connection can differ from the time the connection is returned to the pool. The connection will be destroyed if the difference between the time it is returned to the pool and the creation time exceeds the value of the connection lifetime.
Connection Reset	True	If the value is True, the connection is reset when being removed from the pool. Setting this name to False avoids an additional server round-trip when obtaining a connection.
Current Language		The SQL Server Language record name.
Data Source or Server or Address or Addr or Network Address		The name or address of the SQL Server instance to connect.
Enlist	True	Setting this name to True enlists the connection in the creation thread's current transaction context automatically.
Initial Catalog or Database		The database name.
Integrated Security or Trusted_Connection	False	Indicates whether the connection is a secure connection. In addition to False and True, you can also use sspi, which has the same effect as True.
Max Pool Size	100	The maximum number of connections permitted in the pool when connection pooling is enabled.
Network Library or Net	dbmssocn	The network library for establishing a connection to an SQL Server instance. The default value specifies TCP/IP.
Packet Size	8192	The size (in bytes) of the network packet used to communicate with an SQL Server instance.
Password or Pwd		The user's password used to connect to the SQL Server.
Persist Security Info	False	If the value is False, sensitive information is not returned as part of the connection is open or has ever been in an open state.
Pooling	True	Indicates whether connection pooling is true.
User ID		The login account to get access to the SQL Server instance.
Workstation ID	The local machine name	The name of the machine connecting to the SQL Server instance.

The following is an example of a valid connection string for the SQL Server .NET data provider:

 User ID=udin;Password=17Agt45;Initial Catalog=MySQLDb Data Source=localhost;Connection Timeout=20 

Constructing a Connection Object

All the connection classes have two constructors: a no-argument constructor and a constructor that accepts a connection string. The signatures of both constructors are as follows:

 Public Sub New() Public Sub New(ByVal connectionString As String) 

The no-argument constructor is easier to use. However, using this constructor requires you to set the ConnectionString property of the connection object at a later stage. The following code constructs an SqlConnection object using the no-argument constructor:

 Dim connectionString As String = "User ID=udin;" & _   "Password=17Agt45;Initial Catalog=MySQLDb;" & _   "Data Source=localhost;Connection Timeout=20 ' Use the no-argument constructor Dim connection As New SqlConnection() connection.ConnectionString = connectionString 

The following code uses the alternative constructor to create an instance of the SqlConnection class, resulting in more compact code:

 Dim connectionString As String = "User ID=udin;" & _   "Password=17Agt45;Initial Catalog=MySQLDb;" & _   "Data Source=localhost;Connection Timeout=20 ' Use the alternative constructor Dim connection As New SqlConnection(connectionString) 

Unless you have a valid reason to construct a connection object using the no-argument constructor, using the alternative constructor is preferable.

The Command Object

The command object represents an SQL statement or stored procedure that can be executed on the server. All command classes share most of the properties and methods.

The Command Object's Properties

The following are the common properties of the command classes:

• CommandText: The SQL statement or the name of the stored procedure to execute at the database server.

• CommandTimeout: The number of seconds the command object will wait before terminating an attempt to execute a command and throwing an exception. This property is not available in the System.Data.OracleClient.OracleCommand class.

• CommandType: The type of the command. The value of this property determines how the value of the CommandText property is interpreted. The value of the CommandType property can be one of the members of the System.Data.CommandType enumeration: StoredProcedure, TableDirect, and Text.

• Connection: The connection object used by the command object. The type of the property value is either System.Data.OleDb.OleDbConnection or System.Data.SqlClient.SqlConnection.

• DesignTimeVisible: A value indicating whether the command object should be visible in a customized Windows Forms Designer control. This property is not available in the System.Data.SqlServerCe.SqlCeCommand class.

• Parameters: Represents the collection of parameters of this command object.

• Transaction: The transaction in which the command object executes.

• UpdatedRowSource: This property determines how the results from executing the command text are applied to the DataRow when used by the Update method of the data adapter object.

The Command Object's Methods

The following are the common methods of the command classes:

• Cancel: Cancels the execution of the command object.

• CreateParameter: Creates a parameter object.

• ExecuteNonQuery: Executes an update, insert, or delete SQL statement and returns the number of records affected by the execution.

• ExecuteReader: Executes a select SQL statement and returns the result as a DataReader object.

• ExecuteScalar: Executes the command object's query that returns a single value. This is more efficient than the ExecuteReader method.

• Prepare: Compiles the command on the data source.

• ResetCommandTimeout: Assigns the CommandTimeout property with its original value. This property is not available in the System.Data.OracleClient.OracleCommand and System.Data.SqlServerCe.SqlCeCommand classes.

Constructing the Command Object

All the command classes have four constructors whose signatures are as follows:

 Public Sub New() Public Sub New(ByVal commandText As String) Public Sub New( _   ByVal commandText As String, _   ByVal connection As xxxConnection _ ) Public Sub New( _   ByVal commandText As String, _   ByVal connection As xxxConnection, _   ByVal transaction As xxxTransaction _ ) 

where xxx is Sql, OleDb, Odbc, Oracle, or SqlCe.

The code that shows how to construct an OleDbCommand object is as follows:

 Dim sql As String = "SELECT * FROM Products" ' Instantiate a new connection object ' by passing the connection string Dim connection As New OleDbConnection() ' connectionString is a valid connection string connection.ConnectionString = connectionString connection.Open() ' Create a Command object Dim command As New OleDbCommand() command.Connection = connection command.CommandText = sql 

To construct an SqlCommand object, replace OleDbConnection with SqlConnection and OleDbCommand with SqlCommand, as demonstrated in the following code:

 Dim sql As String = "SELECT * FROM Products" ' Instantiate a new connection object ' by passing the connection string Dim connection As New SqlConnection() ' connectionString is a valid connection string connection.ConnectionString = connectionString connection.Open() ' Create a Command object Dim command As New SqlCommand() command.Connection = connection command.CommandText = sql 

A more compact way to construct a command object is to use the constructor that accepts an SQL statement and an open connection object. You can rewrite the previous code as follows to produce an OleDbCommand object:

 Dim sql As String = "SELECT * FROM Products" ' Instantiate a new connection object ' by passing the connection string Dim connection As New OleDbConnection() ' connectionString is a valid connection string connection.ConnectionString = connectionString connection.Open() ' Create a Command object Dim command As New OleDbCommand(sql, connection) 

And the following is the equivalent to instantiate the SqlCommand class by using the constructor that accepts an SQL statement and an open connection object:

 Dim sql As String = "SELECT * FROM Products" ' Instantiate a new connection object ' by passing the connection string Dim connection As New SqlConnection() ' connectionString is a valid connection string connection.ConnectionString = connectionString connection.Open() ' Create a Command object Dim command As New SqlCommand(sql, connection) 

Modifying Data Using the Command Object

You can modify data in a database using a connection object and a command object. The following three sections show how to insert a new record, update records, and delete records in a database table. Even though the examples use an OLE DB .NET data provider, you can use them to modify data in other .NET data providers by changing the connection string and the types of the connection and command objects.

Inserting a New Record

The following example inserts a new record into a Products table. The SQL statement passes values for the ProductName column and the Price column:

 Dim connectionString As String = _   "Provider=Microsoft.Jet.OLEDB.4.0;" & _   "Data Source=C:\db\MyDB.mdb" Dim sql As String = "INSERT INTO Products " & _   "(ProductName, Price) VALUES ('groggrog', 123)" ' Instantiate a new connection object ' by passing the connection string Dim connection As New OleDbConnection(connectionString) connection.Open() ' Create a Command object Dim command As New OleDbCommand(sql, connection) ' Execute the SQL statement Dim recordsAffected As Integer = command.ExecuteNonQuery() 

The ExecuteNonQuery method in the last line of the code should return 1 as the number of records affected.

Updating Records

The following is an example of how to update records in a Products table. All prices in the table are increased by 10 percent by multiplying the old prices with 1.1:

 Dim connectionString As String = _   "Provider=Microsoft.Jet.OLEDB.4.0;" & _   "Data Source=C:\db\MyDB.mdb" Dim sql As String = "UPDATE Products" & _   " SET Price=Price * 1.1" ' Instantiate a new connection object ' by passing the connection string Dim connection As New OleDbConnection(connectionString) connection.Open() ' Create a Command object Dim command As New OleDbCommand(sql, connection) ' Execute the SQL statement Dim recordsAffected As Integer = command.ExecuteNonQuery() 

Deleting Records

The following is an example of how to delete records in a products table:

 Dim connectionString As String = _   "Provider=Microsoft.Jet.OLEDB.4.0;" & _   "Data Source=C:\db\MyDB.mdb" Dim sql As String = "DELETE FROM Products" & _   " WHERE ProductId = 19" ' Instantiate a new connection object ' by passing the connection string Dim connection As New OleDbConnection(connectionString) connection.Open() ' Create a Command object Dim command As New OleDbCommand(sql, connection) ' Execute the SQL statement Dim recordsAffected As Integer = command.ExecuteNonQuery() 

The Data Reader Object

The data reader object represents a stream of data rows from a data source. The stream can only be read in a forward direction. You can get a data reader object from the ExecuteReader method of the command object.

The Data Reader Object's Properties

The following are the common properties of the data reader classes:

• Depth: Returns the depth of nesting for the current row.

• FieldCount: Returns the current row's number of columns.

• IsClosed: Indicates whether the data reader object is closed.

• Item: Returns the value of a column in the current row in its native format. You can either pass the column position or the column name.

• RecordsAffected: Returns the number of columns affected by the execution of the SQL statement. For a select SQL statement, this property returns −1.

The Data Reader Object's Methods

The following are the methods of the data reader classes:

• Close: Closes the data reader object.

• GetXXX: Returns the value of the specified column. XXX represents the data type returned by this method. For example, the GetByte method returns the value of the specified column as a byte. Other methods include GetBoolean, GetDouble, GetFloat, GetInt32, GetInt64, GetString, and so on.

• GetDataTypeName: Returns the name of the data type of the column specified by a zero-based ordinal.

• GetFieldType: Returns a System.Type object representing the type of the column specified by a zero-based ordinal.

• GetName: Returns the name of the column specified by a zero-based ordinal.

• GetOrdinal: Returns the ordinal of the column whose name is passed as the method argument.

• GetSchemaTable: Returns a System.Data.DataTable object representing the column metadata of the data reader object.

• GetValue: Returns the value of the specified column in its native format. This method returns an object of type System.Object.

• GetValues: Retrieves all the columns in the current row. The method accepts an array of System.Object objects as its argument and populates the array with all the values in the current row. This method returns an integer specifying the size of the array.

• IsDBNull: Indicates whether the column contains nonexistent or missing values.

• NextResult: This method is used only when reading the results of batch SQL statements. It advances the data reader object to the next result.

• Read: Moves to the next record and returns True if there are more rows. Otherwise, it returns False. As an example of how to use a data reader object, consider the code in Listing 6-1.

However, note that Listings 6-1 to 6-19 use the Access database MyDb.mdb that is included in the downloadable file for this chapter. You must save this file in the parent directory of the virtual directory of your ASP.NET testing application. You access the database using the following connection string:

 Dim connectionString As String = _   "Provider=Microsoft.Jet.OLEDB.4.0;" & _   "Data Source=" & Request.PhysicalApplicationPath & _   "..\MyDb.mdb" 

where Request.PhysicalApplicationPath returns the physical application path.

Listing 6-1: Using a Data Reader Object

 <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.OleDb" %> <html> <head> <title>Search Result</title> <script language="VB" runat="server"> Sub Page_Load(sender As Object, e As EventArgs)   Dim s As New StringBuilder(2048)   Dim connectionString As String = _     "Provider=Microsoft.Jet.OLEDB.4.0;" & _     "Data Source=" & Request.PhysicalApplicationPath & _     "..\MyDb.mdb"   ' Instantiate a new connection object   ' by passing the connection string   Dim connection As New OleDbConnection(connectionString)   connection.Open()   ' Create a Command object   Dim command As New OleDbCommand(sql, connection)   ' Instantiate a DataReader object   ' using the OleDbCommand class's ExecuteReader method   Dim dataReader As OleDbDataReader = command.ExecuteReader()   ' Loop through the DataReader   Do While dataReader.Read()     s.Append("<br>").Append(dataReader.GetString(2))   Loop   message.Text = s.ToString() End Sub </script> </head> <body> <asp:Label  runat="server"/> </body> </html> 

The Page_Load method first constructs a connection object to connect to an OLE DB data provider whose data source is an Access database in MyDb.mdb, located in the parent directory of the application's virtual root. The command text used to retrieve data is assigned the following SQL statement:

 SELECT * FROM Products 

After the connection and command objects are constructed, it calls the ExecuteReader method of the command object:

 Dim dataReader As OleDbDataReader = command.ExecuteReader() 

Then, you can loop through the data reader object by using its Read method. This method returns True if there are more rows in the data reader:

 Do While dataReader.Read()   s.Append("<br>").Append(dataReader.GetString(2)) Loop 

For each record, it appends the value of the third column to the System.Text.StringBuilder s, assuming that the third column is a string.

Working with Transactions

Looking at the list of methods of the connection object, you will probably wonder why there is a method to begin a transaction (BeginTransaction), but there is no method to commit or roll back a transaction. In ADO.NET, committing and rolling back are methods of a transaction object, which is represented by the System.Data.SqlClient.SqlTransaction class, the System.Data.OleDb.OleDbTransaction class, the System.Data.Odbc.OdbcTransaction class, the System.Data.OracleClient.OracleTransaction class, or the System.Data.SqlServerCe.SqlCeTransaction class, depending on which .NET data provider you are using.

You get an instance of one of the transaction classes when you call the BeginTransaction method of a connection class. You can then call the transaction object's Commit method to commit the transaction or the Rollback method to roll back the transaction.

As an example, the following code begins a transaction and either commits it or rolls it back:

 ' Assuming connection is an active OleDbConnection object, ' and command is an OleDbCommand object ' you can call its BeginTransaction method. Dim transaction As OleDbTransaction = connection.BeginTransaction() command.Transaction = transaction Try   ' Do something with the command object here   ' .   ' .   ' .   ' Ready to commit the transaction   transaction.Commit() Catch e As Exception   ' oops? something bad happened   transaction.Rollback() End Try 

The Data Adapter Object

You have seen how you can access a database and manipulate its data using the connection object, the command object, and the data reader object. ADO.NET lets you manipulate data in a disconnected way using a DataSet object. However, the DataSet object cannot connect to the data source directly. It still needs a data provider to retrieve data from the database and push it back to the data source if there are changes to the data. The data adapter object is the object in the data provider that populates a DataSet object. It serves as a bridge between the data source and the DataSet object. Because this object works closely with the DataSet object, you will see examples after the discussion of the DataSet object in the "Working with DataSets" section.

The DataAdapter Object's Properties

The following are the properties of the data adapter object:

• DeleteCommand: The SQL statement used to delete records from the data source

• InsertCommand: The SQL statement used to insert records into the data source

• SelectCommand: The SQL statement used to select records from the data source

• UpdateCommand: The SQL statement used to update records in the data source

The DataAdapter Object's Methods

For methods in other data adapter classes, refer to the .NET Framework documentation.

Working with DataSets

Although you can access a data source and manipulate data using the objects in a .NET data provider, DataSet provides functionality to work with disconnected data and let you bind Web server controls with the data. You achieve this using the DataSet object and its corresponding objects.

The DataSet object is a data container that resembles a relational database. It can contain zero or more tables, and each table lets you access its columns and rows individually. Once populated, a DataSet object is disconnected from the data source. Therefore, each DataSet object holds a copy of the data. You can manipulate the data in the DataSet object independently. If you want this change to be reflected in the originating data source, you must use data providers to send the change back to the data source.

In addition to obtaining data from a data source, the DataSet object can be populated programmatically, as discussed in the section "Populating a DataSet Object Programmatically".

The System.Data namespace provides types that allow you to work with the DataSet object and its supporting objects. The following classes are discussed next:

• DataSet

• DataTableCollection

• DataTable

• DataColumn

• DataRow

• DataView

The System.Data.DataSet Class

The DataSet class represents a DataSet object and provides properties and methods for data manipulation. A DataSet object contains zero or more tables that can be accessed through its table collection.

In addition, methods for converting data to and from an XML document are also available. However, manipulating XML documents is not discussed in this chapter.

The DataSet Properties

The following are the properties of the DataSet class:

• CaseSensitive: Indicates whether case sensitivity applies to string comparisons within the DataSet's DataTable objects.

• DataSetName: The name of the DataSet object.

• DefaultViewManager: Returns a System.Data.DataViewManager object that allows you to create custom settings for each DataTable object in the DataSet.

• EnforceConstraints: Indicates whether constraints are enforced in update operations.

• ExtendedProperties: Returns a System.Data.PropertyCollection object containing custom user information.

• HasErrors: Indicates whether there are errors in any row in any of the tables.

• Locale: A System.Globalization.CultureInfo object that represents the locale information used to compare strings within the table.

• Namespace: The namespace of the DataSet.

• Prefix: The XML prefix used as an alias for the namespace of the DataSet.

• Relations: Returns a System.Data.DataRelationCollection containing a collection of all DataRelation objects in the DataSet.

• Site: A System.ComponentModel.ISite object for the DataSet.

• Tables: Represents the collection of all DataTable objects in the DataSet.

The DataSet Methods

The following are the more important methods of the DataSet class:

• AcceptChanges: Accepts all the changes to the DataSet object since it was loaded or the last time this method was invoked.

• BeginInit: Begins the initialization of a DataSet that is used on a form or used by another component.

• Clear: Removes all rows in all tables, thus clearing the DataSet of any data.

• Clone: Copies the DataSet structure.

• Copy: Copies both the DataSet's data and structure.

• EndInit: Ends the initialization of a DataSet.

• GetChanges: Returns a copy of the DataSet object containing all changes made since it was last loaded or since the AcceptChanges method was invoked.

• HasChanges: Indicates whether the DataSet has changes.

• HasSchemaChanged: Indicates whether the schema has changed.

• Merge: Merges this DataSet with a specified DataSet.

• RejectChanges: Cancels the changes made to the DataSet object since it was created or since the AcceptChanges method was last invoked.

For a complete list of methods in the DataSet class, refer to the .NET Framework class library.

The System.Data.DataTableCollection Class

The DataTableCollection class represents the collection of tables in a DataSet object. The most important properties and methods of the DataTableCollection class are discussed next.

The DataTableCollection Properties

The following are the properties of the DataTableCollection class:

• Item: Returns the specified DataTable.

• List: Returns a System.Collections.ArrayList object containing all the tables.

The DataTableCollection Properties

The following are some of the methods in the DataTableCollection class:

• Add: Adds a DataTable object to the collection.

• Clear: Clears the collection of all tables.

• Contains: Tests if a table whose name is specified as the argument exists in the collection.

• Remove: Removes a DataTable object from the collection.

• RemoveAt: Removes the table at the specified index.

The System.Data.DataTable Class

The DataTable class represents a database table. A DataTable object exposes its individual column and row that you can access using its Columns and _Rows properties. The following are the more important properties and methods in this class.

The DataTable Properties

The following are some of the properties of the DataTable class:

• CaseSensitive: Indicates whether case sensitivity applies to string comparisons in the table.

• Columns: Returns the DataColumn collection of the table.

• Constraints: Returns the collection of constraints in the table.

• DataSet: Returns the owner DataSet of this table.

• MinimumCapacity: The initial size of the table.

• PrimaryKey: An array of DataColumn objects representing all primary key fields in the table.

• Rows: Returns the DataRow collection of the table.

• TableName: The name of the table.

Of the properties, you will use the Columns and Rows properties most often.

The DataTable Methods

The following are the more important methods of the DataTable class:

• AcceptChanges: Accepts all changes to this table since the AcceptChanges method was last invoked.

• Clear: Clears the table of any row.

• Clone: Copies the DataTable's structure.

• Copy: Copies both the DataTable's structure and data.

• NewRow: Returns a DataRow object with the same structure as the rows in the table.

• RejectChanges: Rejects changes to this table since the AcceptChanges method was last invoked.

• Select: Selects DataRow objects that match certain filter criteria.

The NewRow method is an important method you use to create a DataRow object whose structure is the same as the rows in the DataTable object. Constructing a DataRow object using the NewRow method guarantees that the DataRow is compatible with the DataTable object and can be added to the DataTable object later. Also, using the NewRow method saves you from having to set each column in the DataRow individually.

The System.Data.DataColumn Class

The DataColumn class represents a table column in a DataTable object. This class has properties that reflect those of a column of a database table. For example, it allows you to specify its data type, whether a DataColumn object can accept null values, whether a column is auto-incremented, or whether the column is read-only.

The following are the more important properties of the DataColumn class:

• AllowDBNull: Indicates whether this column can accept a null value.

• AutoIncrement: Indicates whether the value of this column is generated automatically when a row is added.

• AutoIncrementSeed: The starting value of this column if the AutoIncrement property is set to True.

• AutoIncrementStep: The increment used by a column whose AutoIncrement property is set to True.

• ColumnName: The name of this column

• DataType: The System.Type object representing the data type of this column.

• DefaultValue: The default value of this column for a new row.

• MaxLength: The maximum length of text column.

• Ordinal: The position of this column in the collection.

• ReadOnly: Indicates whether the column is read only.

• Table: The table to which the column belongs.

• Unique: Indicates whether the values in each row of this column must be unique.

The System.Data.DataRow Class

The DataRow class represents a record in a DataTable object. You will see it's more important properties and methods next.

The DataRow Properties

The following are the more important properties in the DataRow class:

• HasErrors: Indicates whether there are errors in the column collection.

• Item: Returns value in the specified column.

• RowState: The row state indicated by one of the members of the System.Data.DataRowState enumeration: Added, Deleted, Detached, Modified, and Unchanged.

The DataRow Methods

The following are the more important methods in the DataRow class:

• AcceptChanges: Accepts changes to the row since the AcceptChanges method was last invoked.

• BeginEdit: Begins an edit operation on a DataRow object.

• CancelEdit: Cancels the edit on the row.

• Delete: Deletes this row.

• EndEdit: Ends the edit on the row.

• GetChildRows: Returns the child rows of a row.

• GetParentRow: Returns the parent row of a row.

• IsNull: Indicates whether the specified column contains a null.

• RejectChanges: Rejects changes to the row since the AcceptChanges method was last invoked.

The System.Data.DataView Class

The DataView class represents a view to a DataTable object. You can use a DataView object to sort, filter, edit, and navigate data in a DataTable object. More important, however, you can bind a DataView with a server control to provide a view to the data. You will see the most important properties and methods next.

The DataView Properties

The following are the more important properties of the DataView class:

• AllowDelete: Indicates whether delete operations are allowed.

• AllowEdit: Indicates whether edit operations are allowed.

• AllowNew: Indicates whether new rows can be added using the AddNew methods.

• Count: Returns the number of rows in the DataView object after applying RowFilter and RowStateFilter.

• Item: Returns a row of data from a specified table.

• Sort: The sort column(s) and the sort order of the table.

• Table: The source data table.

The DataView Methods

The following offers some of the more important methods in the DataView class:

• AddNew: Adds a new row to the DataView.

• Close: Closes the DataView object.

• Delete: Deletes the row at the given index position.

• Find: Locates a row in the DataView by the specified primary key value.

• GetEnumerator: Returns a System.Collections.IEnumerator object for this DataView object.

• Open: Opens this DataView object.

• Reset: Resets this DataView object.

Populating a DataSet Object Programmatically

You normally use a DataSet object as a temporary data container for data from a database. However, this is not always the case. Sometimes you need to populate your DataSet programmatically. For example, you may have an algorithm that you use to populate a table, or you may have data stored in a text file.

Listing 6-2 shows how you can populate a DataSet programmatically by performing the following steps:

1. Create a DataSet object.

2. Create a DataTable object representing a table.

3. Create several DataColumn objects and add them to the DataTable object.

4. Create a DataRow object using the NewRow method of the DataTable class.

5. Assign values to the DataRow object's cells.

6. Add the DataRow object to the DataTable object.

7. Add the DataTable object to the DataSet.

Listing 6-2: Populating a DataSet Object Programmatically

 <%@ Import Namespace="System.Data" %> <html> <head> <title>Populating DataSet</title> <script language="VB" runat="server"> Sub Page_Load(sender As Object, e As EventArgs)   ' Create a DataSet object called "Warehouse"   Dim dataSet As New DataSet("Warehouse")   ' Create a DataTable object called "Products"   Dim dataTable As New DataTable("Products")   ' Create four columns called Id(Integer),   ' ProductName(String),   ' CategoryId(Integer),   ' Price(Decimal)   Dim column1 As New DataColumn("Id", _     Type.GetType("System.Int32"))   Dim column2 As New DataColumn("ProductName", _     Type.GetType("System.String"))   Dim column3 As New DataColumn("CategoryId", _     Type.GetType("System.Int32"))   Dim column4 As New DataColumn("Price", _     Type.GetType("System.Decimal"))   ' Add all columns to the table's Columns collection   ' All columns must be added before creating a new row   dataTable.Columns.Add(column1)   dataTable.Columns.Add(column2)   dataTable.Columns.Add(column3)   dataTable.Columns.Add(column4)   ' Create a new DataRow object using the NewRow   ' method of the DataTable class   ' The resulting DataRow object will have the same   ' structure as the table row   Dim row As DataRow = dataTable.NewRow()   ' Populating the four cells   row(0) = "14"   row(1) = "Pelesonic Digital Camera"   row(2) = "2"   row(3) = "429.95"   ' Add the populated row to the table's Rows collection   dataTable.Rows.Add(row)   ' Add the table to the DataSet object   dataSet.Tables.Add(dataTable)   ' Now display the data   Dim s As New StringBuilder(512)   Dim row2 As DataRow = dataSet.Tables("Products").Rows(0)   s.Append("Id:").Append(row2("Id"))   ' Another way is to reference the row cell by using the format   ' Rows(row index)(column name)   s.Append("<br>ProductName:")   s.Append(dataSet.Tables("Products").Rows(0)("ProductName"). _     ToString())   s.Append("<br>CateogryId:").Append(row2("CategoryId"))   s.Append("<br>Price:").Append(row2("Price"))   message.Text = s.ToString() End Sub </script> </head> <body> <asp:Label  runat="server"/> </body> </html> 

The only method in Listing 6-2 is the Page_Load method, which gets invoked when the page is loaded. The first thing the method does is to create a DataSet object called Warehouse, containing a DataTable object named Products:

 ' Create a DataSet object called "Warehouse" Dim dataSet As New DataSet("Warehouse") ' Create a DataTable object called "Products" Dim dataTable As New DataTable("Products") 

Then, you construct four DataColumn objects to be added to the Products DataTable. The DataColumn objects have the following names and data types: Id (Integer), ProductName (String), CategoryId (Integer), and Price (Decimal).

You construct a DataColumn object by using its constructor that accepts a column name and a data type. A data type has the type of System.Type and can be constructed using the GetType method of the System.Type class by passing the type name. For example, you instantiate a DataColumn object named Id with an integer data type using the following code:

 Dim column1 As New DataColumn("Id", _   Type.GetType("System.Int32")) 

The following code creates four DataColumn objects for the Products table:

 ' Create four columns called Id(Integer), ' ProductName(String), ' CategoryId(Integer), ' Price(Decimal) Dim column1 As New DataColumn("Id", _   Type.GetType("System.Int32")) Dim column2 As New DataColumn("ProductName", _   Type.GetType("System.String")) Dim column3 As New DataColumn("CategoryId", _   Type.GetType("System.Int32")) Dim column4 As New DataColumn("Price", _   Type.GetType("System.Decimal")) ' Add all columns to the table's Columns collection ' All columns must be added before creating a new row 

Once you have the DataColumn objects, you can add them to the DataTable by calling the Add method of the Columns collection, as in the following code:

 dataTable.Columns.Add(column1) dataTable.Columns.Add(column2) dataTable.Columns.Add(column3) dataTable.Columns.Add(column4) 

The next step is to create a DataRow object for each row you want to add to the DataTable object. You can easily create a DataRow object with the same structure as the rows in the Products table by calling the NewRow method of the DataTable class:

 ' Create a new DataRow object using the NewRow ' method of the DataTable class ' The resulting DataRow object will have the same ' structure as the table row Dim row As DataRow = dataTable.NewRow() 

Once you have a DataRow object, you can populate data by referring to each data cell using an index number. Index number 0 represents the first column in the row. Therefore, the following code assigns values to all the four columns in the row:

 ' Populating the four cells row(0) = "14" row(1) = "Pelesonic Digital Camera" row(2) = "2" row(3) = "429.95" 

Next, you can add the DataRow object to the Products table using the Add method of the Rows collection, and add the table to the DataSet object using the Add method of the Tables collection:

 ' Add the populated row to the table's Rows collection dataTable.Rows.Add(row) ' Add the table to the DataSet object dataSet.Tables.Add(dataTable) 

The next lines of the Page_Load method display the data in the browser. A StringBuilder object composes the HTML tags and assigns the string to the Text property of the Label control named message:

 ' Now display the data Dim s As New StringBuilder(512) Dim row2 As DataRow = dataSet.Tables("Products").Rows(0) s.Append("Id:").Append(row2("Id")) ' Another way is to reference the row cell by using the format ' Rows(row index)(column name) s.Append("<br>ProductName:") s.Append(dataSet.Tables("Products").Rows(0)("ProductName"). _   ToString()) s.Append("<br>CateogryId:").Append(row2("CategoryId")) s.Append("<br>Price:").Append(row2("Price")) message.Text = s.ToString() 

If you run the code in a Web browser, you should see the following result:

 Id:14 ProductName:Tomtom Chocolate CateogryId:2 Price:4.95 

Populating DataSet with Data from a Database

Listing 6-3 grabs data from the Products table in an Access database and displays the data using a Label control.

Listing 6-3: Populating a DataSet with Data from a Database

 <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.OleDb" %> <html> <head> <title>Populating DataSet</title> <script language="VB" runat="server"> Sub Page_Load(sender As Object, e As EventArgs)   ' -------- Populate DataSet ------------   Dim connectionString As String = _     "Provider=Microsoft.Jet.OLEDB.4.0;" & _     "Data Source=" & Request.PhysicalApplicationPath & _     "..\MyDb.mdb"   Dim sql As String = "SELECT * FROM Products WHERE ProductId<10"   ' Instantiate a new connection object   ' by passing the connection string   Dim connection As New OleDbConnection(connectionString)   Dim dataAdapter As New OleDbDataAdapter(sql, connection)   Dim dataSet As New DataSet()   dataAdapter.Fill(dataSet, "Products")   ' -------- Now display the data ------------   Dim s As New StringBuilder(1024)   Dim i, j As Integer 'counters   Dim dataTable As DataTable = dataSet.Tables("Products")   Dim dataRowCollection As DataRowCollection = dataTable.Rows   Dim rowCount As Integer = dataRowCollection.Count   s.Append("<table border=1><tr>")   Dim dataColumnCollection As DataColumnCollection = dataTable.Columns   Dim columnCount As Integer = dataColumnCollection.Count   For i = 0 To columnCount - 1     Dim column As DataCOlumn = dataColumnCollection(i)     Dim columnName As String = column.ColumnName     s.Append("<td>").Append(columnName)     s.Append(" (")     s.Append(column.DataType.ToString())     s.Append(")")     s.Append("</td>")   Next i   s.Append("</tr>")   For i = 0 To rowCount - 1     Dim row As DataRow = dataRowCollection(i)     s.Append("<tr>")     For j = 0 To columnCount - 1       Dim cell As Object = row.Item(j)       s.Append("<td>").Append(cell.ToString()).Append("</td>")     Next j     s.Append("</tr>")   Next i   message.Text = s.ToString() End Sub </script> </head> <body> <asp:Label  runat="server"/> </body> </html> 

To populate a DataSet with data from a database, you use a data adapter object's Fill method. However, first you need to create an instance of a data adapter. In this example, you use an OleDbDataAdapter object that you construct by passing an SQL statement and a connection object:

 Dim connectionString As String = _   "Provider=Microsoft.Jet.OLEDB.4.0;" & _   "Data Source=" & Request.PhysicalApplicationPath & _   "..\MyDb.mdb" Dim sql As String = "SELECT * FROM Products WHERE ProductId<10" ' Instantiate a new connection object ' by passing the connection string Dim connection As New OleDbConnection(connectionString) Dim dataAdapter As New OleDbDataAdapter(sql, connection) Dim dataSet As New DataSet() dataAdapter.Fill(dataSet, "Products") 

The Fill method populates the DataSet object called dataSet with the content of the Products table. Now, displaying the data is no different from the previous code. First, you need to obtain the Products table and assign it to a DataTable object variable called dataTable:

 Dim dataTable As DataTable = dataSet.Tables("Products") 

Next, you can obtain the row collection of the table from the Rows property and the number of rows from the Count property:

 Dim dataRowCollection As DataRowCollection = dataTable.Rows Dim rowCount As Integer = dataRowCollection.Count 

The number of rows is important when you iterate the row collection to display the values in each row.

The next lines of code display the table in an HTML table. The data displayed includes the column information. The column collection is obtained from the Columns property of the DataTable object, and the number of columns from the Count property of the column collection:

 Dim dataColumnCollection As DataColumnCollection = dataTable.Columns Dim columnCount As Integer = dataColumnCollection.Count 

Having the column count, you can then retrieve the name and data type of each column. Each column is assigned to a DataColumn object reference column. The column name of each column comes from the ColumnName of the DataColumn object. The following code fragment iterates the column collection in a For loop:

 For i = 0 To columnCount - 1   Dim column As DataColumn = dataColumnCollection(i)   Dim columnName As String = column.ColumnName   s.Append("<td>").Append(columnName)   s.Append(" (")   s.Append(column.DataType.ToString())   s.Append(")")   s.Append("</td>") Next i 

Each column is presented in the following format:

 column name (data type) 

Next, you use two For loops to iterate each cell in the table. You obtain a data cell using the Item property of the DataRow object:

 For i = 0 To rowCount - 1   Dim row As DataRow = dataRowCollection(i)   s.Append("<tr>")   For j = 0 To columnCount - 1     Dim cell As Object = row.Item(j)     s.Append("<td>").Append(cell.ToString()).Append("</td>")   Next j   s.Append("</tr>") Next I 

Invoking the page from a Web browser gives you Figure 6-2.

Figure 6-2: Populating a DataSet

Modifying Data in a DataSet

Modifying data in a DataSet object is an involved process because the DataSet object is disconnected from its data source and the changes to the DataSet object must be sent back to the data source. You send changes back to the data source through a data adapter object. As the following examples show, inserting rows or updating and deleting them requires you to do the following steps:

1. Populate a DataSet object using the Fill method of the data adapter class.

2. Build an insert, an update, or a delete command for a corresponding action. Inserting a record requires you to build an insert command for the data adapter object, and you need to build an update command if you want to update record(s) in the data source. By the same token, you need a delete command for the data adapter if you want to delete a record or a number of records in the data source. These commands are merely the correct SQL statements for the intended operations.

3. Manipulate the data in the DataSet object.

4. Call the Update method of the data adapter passing the modified data.

As you can see, data changes must be reflected in the command in step 2 as well as in the DataSet itself in step 3. Therefore, data modification that needs to be passed back to the data source can really be a pain. However, the next sections show how you can alleviate this a bit.

The following are three examples of how to insert a record, update a record, and delete a record.

Adding a Record

Listing 6-4 offers code that inserts a record into the Products table. The record contains the product name and the price for the new record.

Listing 6-4: Adding a Record into a Table

 <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.OleDb" %> <html> <head> <title>Adding a new record</title> <script language="VB" runat="server"> Sub Page_Load(sender As Object, e As EventArgs)   ' -------- Populate DataSet ------------   Dim connectionString As String = _     "Provider=Microsoft.Jet.OLEDB.4.0;" & _     "Data Source=" & Request.PhysicalApplicationPath & _     "..\MyDb.mdb"   Dim sql As String = "SELECT * FROM Products"   ' Instantiate a new connection object   Dim connection As New OleDbConnection(connectionString)   Dim dataAdapter As New OleDbDataAdapter(sql, connection)   ' Add a new row, setting two fields: ProductName and Price   Dim productName As String = "Pelesonic T3X "   Dim price As Decimal = 512.95   ' Build the insert command   Dim insertCommand = "INSERT INTO Products (Name, Price)" & _     " VALUES ('" & productName & "'," & price.ToString() & ")"   dataAdapter.InsertCommand = New OleDbCommand(insertCommand, connection)   connection.Open()   Dim dataSet As New DataSet()   dataAdapter.Fill(dataSet, "Products")   connection.Close()   ' Construct a new DataRow object for the new row   Dim row As DataRow = dataSet.Tables("Products").NewRow()   ' Assign values for the ProductName and Price fields   row("Name") = productName   row("Price") = price   ' Add a new row to the table   dataSet.Tables("Products").Rows.Add(row)   connection.Open()   ' Get the added row(s)   Dim addedRows As DataRow() = dataSet.Tables("Products"). _     Select(Nothing, Nothing, DataViewRowState.Added)   dataAdapter.Update(addedRows)   connection.Close() End Sub </script> </head> <body> <asp:Label  runat="server"/> </body> </html> 

You start the process in the Page_Load method by constructing an OleDbConnection object and building an insert command for the data adapter:

 ' -------- Populate DataSet ------------ Dim connectionString As String = _   "Provider=Microsoft.Jet.OLEDB.4.0;" & _   "Data Source=C:\db\MyDb.mdb" Dim sql As String = "SELECT * FROM Products" ' Instantiate a new connection object Dim connection As New OleDbConnection(connectionString) Dim dataAdapter As New OleDbDataAdapter(sql, connection) ' Add a new row, setting two fields: ProductName and Price Dim productName As String = "Vegemite Crackers" Dim price As Decimal = 12.95 ' Build the insert command Dim insertCommand = "INSERT INTO Products (ProductName, Price)" & _   " VALUES ('" & productName & "'," & price.ToString() & ")" dataAdapter.InsertCommand = New OleDbCommand(insertCommand, connection) connection.Open() 

The next step is to populate the DataSet object using the Fill method of the data adapter object:

 Dim dataSet As New DataSet() dataAdapter.Fill(dataSet, "Products") connection.Close() 

Afterward, you construct a DataRow object using the NewRow method of the DataTable class, guaranteeing that the resulting DataRow object has the same structure as any other row in the table:

 ' Construct a new DataRow object for the new row Dim row As DataRow = dataSet.Tables("Products").NewRow() 

You can then populate the cells in the DataRow object and add the row to the table:

 ' Assign values for the ProductName and Price fields row("ProductName") = productName row("Price") = price ' Add a new row to the table dataSet.Tables("Products").Rows.Add(row) connection.Open() 

And now, this is the important part: You use the Select method of the DataTable class to retrieve all the newly added rows. Note how the DataViewRowState enumeration supplies the record state as the third argument. The enumeration member Added indicates you are only interested in the records that were added to the table:

 ' Get the added row(s) Dim addedRows As DataRow() = dataSet.Tables("Products"). _   Select(Nothing, Nothing, DataViewRowState.Added) 

Finally, you use the Update method of the data adapter to update the changes to the DataSet object in the data source:

 dataAdapter.Update(addedRows) 

Updating Records

Listing 6-5 is similar to the code in Listing 6-4. This time, instead of inserting a record into a table, you change the values of the rows in the Products table.

Listing 6-5: Updating Records

 <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.OleDb" %> <html> <head> <title>Updating Records in DataSet</title> <script language="VB" runat="server"> Sub Page_Load(sender As Object, e As EventArgs)   ' -------- Populate DataSet ------------   Dim connectionString As String = _     "Provider=Microsoft.Jet.OLEDB.4.0;" & _     "Data Source=" & Request.PhysicalApplicationPath & _     "..\MyDb.mdb"   Dim sql As String = "SELECT * FROM Products"   ' Instantiate a new connection object   Dim connection As New OleDbConnection(connectionString)   Dim dataAdapter As New OleDbDataAdapter(sql, connection)   ' Change the record with ProductId = 1, set ProductName="Pelesonic XP"   Dim productName As String = "Pelesonic XP"   Dim productId As Integer = 1   Dim updateCommand = "UPDATE Products SET Name='" & productName & _     "' WHERE ProductProducts")   connection.Close()   ' Change the data   Dim row As DataRow = dataSet.Tables("Products").Rows(0)   row("Name") = productName   connection.Open()   ' Get the modified row(s)   Dim modifiedRows As DataRow() = dataSet.Tables("Products"). _     Select(Nothing, Nothing, DataViewRowState.ModifiedCurrent)   dataAdapter.Update(modifiedRows)   connection.Close() End Sub </script> </head> <body> <asp:Label  runat="server"/> </body> </html> 

The difference from the code in Listing 6-4 is that you do not call the NewRow method to construct a new row. Instead, you assign the first row in the table to the row object reference and change the value of the Name column:

 ' Change the data   Dim row As DataRow = dataSet.Tables("Products").Rows(0)   row("Name") = productName   connection.Open() 

Then, you obtain an array of DataRow objects using the Select method. This time the third argument to this method is the ModifiedCurrent member of the DataViewRowState enumeration:

 ' Get the modified row(s) Dim modifiedRows As DataRow() = dataSet.Tables("Products"). _   Select(Nothing, Nothing, DataViewRowState.ModifiedCurrent) 

Like the previous example, you can then update the change in the data source by calling the Update method of the data adapter:

 dataAdapter.Update(modifiedRows) 

Deleting Records

You also delete records using similar code. Listing 6-6 presents the code that deletes a record from the Products table.

Listing 6-6: Deleting Records

 <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.OleDb" %> <html> <head> <title>Deleting Records in DataSet</title> <script language="VB" runat="server"> Sub Page_Load(sender As Object, e As EventArgs)   Dim connectionString As String = _     "Provider=Microsoft.Jet.OLEDB.4.0;" & _     "Data Source=" & Request.PhysicalApplicationPath & _     "..\MyDb.mdb"   Dim sql As String = "SELECT * FROM Products"   ' Instantiate a new connection object   Dim connection As New OleDbConnection(connectionString)   Dim dataAdapter As New OleDbDataAdapter(sql, connection)   ' Delete the record with ProductId = 3   Dim productId As Integer = 3   Dim deleteCommand = "DELETE FROM Products WHERE ProductProducts")   connection.Close()   ' Change the data   Dim row As DataRow = dataSet.Tables("Products").Rows(0)   row.Delete()   connection.Open()   ' Get the deleted row(s)   Dim deletedRows As DataRow() = dataSet.Tables("Products"). _     Select(Nothing, Nothing, DataViewRowState.Deleted)   dataAdapter.Update(deletedRows)   connection.Close() End Sub </script> </head> <body> <asp:Label  runat="server"/> </body> </html> 

Once you reference the first row in the Products table, you call the Delete method to delete the row:

 ' Change the data Dim row As DataRow = dataSet.Tables("Products").Rows(0) row.Delete() connection.Open() 

As in the previous examples, you filter the DataTable to obtain all deleted records. This time you use the DataViewRowState enumeration member Deleted to indicate you want all the deleted records:

 ' Get the deleted row(s) Dim deletedRows As DataRow() = dataSet.Tables("Products"). _   Select(Nothing, Nothing, DataViewRowState.Deleted) 

Finally, the Update method updates the change in the data source:

 dataAdapter.Update(deletedRows) 

Modifying Data in a DataSet Using Parameters

You have seen that you can only modify data after constructing an insert/update/delete command and modifying the DataSet object itself. Every change requires you to construct a different command for the data adapter. Using parameters can ease the process involved.

Listing 6-7 uses parameters to construct a generic update command and uses it to modify two rows.

Listing 6-7: Using Parameters to Modify Data

 <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.OleDb" %> <html> <head> <title>Updating Records with Parameters</title> <script language="VB" runat="server"> Sub Page_Load(sender As Object, e As EventArgs)   ' -------- Populate DataSet ------------   Dim connectionString As String = _     "Provider=Microsoft.Jet.OLEDB.4.0;" & _     "Data Source=" & Request.PhysicalApplicationPath & _     "..\MyDb.mdb"   Dim sql As String = "SELECT * FROM Products"   ' Instantiate a new connection object   Dim connection As New OleDbConnection(connectionString)   Dim updateCommand As String = "UPDATE Products SET Name=?" & _     " WHERE ProductId=?"   Dim dataAdapter As New OleDbDataAdapter(sql, connection)   dataAdapter.UpdateCommand = New OleDbCommand(updateCommand, connection)   dataAdapter.UpdateCommand.Parameters.Add( _     "@ProductName", OleDbType.VarChar, 15, "Name")   Dim param As OleDbParameter = _     dataAdapter.UpdateCommand.Parameters.Add("@ProductId", OleDbType.Integer)   param.SourceColumn = "ProductId"   param.SourceVersion = DataRowVersion.Original   connection.Open()   Dim dataSet As New DataSet()   dataAdapter.Fill(dataSet, "Products")   connection.Close()   ' Change the data   Dim row1 As DataRow = dataSet.Tables("Products").Rows(0)   row1("Name") = "Product 1"   Dim row2 As DataRow = dataSet.Tables("Products").Rows(1)   row2("Name") = "Product 2"   connection.Open()   ' Get the modified row(s)   Dim modifiedRows As DataRow() = dataSet.Tables("Products"). _     Select(Nothing, Nothing, DataViewRowState.ModifiedCurrent)   dataAdapter.Update(modifiedRows)   connection.Close() End Sub </script> </head> <body> <asp:Label  runat="server"/> </body> </html> 

Updating Data with the CommandBuilder Object

Using parameters in data modification code can make the process less involved. However, there is an even easier way to modify data: using the CommandBuilder object. With this approach, there is no need for you to construct an insert/update/ delete command for the data adapter. The CommandBuilder object does this for you. The catch is you have to have a primary key for the table to be modified.

Listing 6-8 shows how to use a CommandBuilder object to modify data.

Listing 6-8: Using the CommandBuilder Object

 <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.OleDb" %> <html> <head> <title>Using Command Builder</title> <script language="VB" runat="server"> Sub Page_Load(sender As Object, e As EventArgs)   ' -------- Populate DataSet ------------   Dim connectionString As String = _     "Provider=Microsoft.Jet.OLEDB.4.0;" & _     "Data Source=" & Request.PhysicalApplicationPath & _     "..\MyDb.mdb"   Dim sql As String = "SELECT * FROM Products"   ' Instantiate a new connection object   Dim connection As New OleDbConnection(connectionString)   Dim dataAdapter As New OleDbDataAdapter()   dataAdapter.SelectCommand = New OleDbCommand(sql, connection)   Dim commandBuilder As OleDbCommandBuilder = _     New OleDbCommandBuilder(dataAdapter)   connection.Open()   Dim dataSet As New DataSet()   dataAdapter.Fill(dataSet, "Products")   Dim row As DataRow = dataSet.Tables("Products").Rows(0)   row("Name") = "Toroid"   'Without the CommandBuilder, the following line would fail   dataAdapter.Update(dataSet, "Products")   connection.Close() End Sub </script> </head> <body> <asp:Label  runat="server"/> </body> </html>