Data Operations on Sets of Rows
Priya Dhawan
Microsoft Developer Network
Revised February 2002
Summary: This article covers data operations on sets of rows using ADO .NET. (17 printed pages)
Download Bdadotnet_beta2.msi.
Contents
Introduction
Read Operation
Using the ADO .NET DataReader Object
Using the ADO .NET SqlCommand Object and XmlReader
Using the ADO .NET DataAdapter Object
Write Operations
Using the ADO .NET DataAdapter Object
Inserts
Updates
Deletes
Conclusion
Introduction
ADO .NET provides multiple features that allow access to sets of data rows. This article demonstrates techniques for accessing sets of rows from a data source and operations to commit modifications back to the data source. Code samples throughout the article make use of the SQL managed provider to connect to a SQL Server database, although they also apply to the ADO managed provider.
Read Operation
Output from a query that returns a set of rows can be returned using the ADO .NET DataReader object, XmlReader object, or DataSet object.
Using the ADO .NET DataReader Object
The DataReader object provides a simple mechanism for retrieving data. The DataReader object allows application code to read data one record at a time, forward only.
You can use the Read method of the DataReader object to read multiple rows in a result set. This method advances the pointer to the next row.
Because the pointer is not positioned at the first row in the result set when the data is retrieved, you need to explicitly call the Read method on the DataReader object before you start iterating through the DataReader object.
In the following code example, we retrieve all the orders from the Orders table in the database and read it through the DataReader object:
Dim sqlDataRdr As SQLDataReader
Dim sqlConn As SQLConnection
Dim sqlCmd As SQLCommand
Try
' Create new Database Connection
sqlConn = New SQLConnection(myConnString)
' Create new Command Object
sqlCmd = New SQLCommand()
' Set the Command to call a stored procedure returning order headers
With sqlCmd
.CommandType = CommandType.StoredProcedure
.CommandText = "GetOrderHeaders"
.Connection = sqlConn
End With
' Open the database connection
SQLConn.Open()
' Execute the command and retrieve the result set
sqlDataRdr = sqlCmd.ExecuteReader()
' Iterate through each row in the result set
While (sqlDataRdr.Read)
' Do something with the row
outString = sqlDataRdr.Item("OrderId").ToString() & "," & _
sqlDataRdr.Item("CustomerId").ToString())
End While
Catch
' Handle the exception
…
Finally
' Close the DataReader
sqlDataRdr.Close()
'Close the connection
sqlConn.Close()
End Try
Note See Example 1 in the BDAdotNetData3.vb sample code (see top of article for download).
Using the ADO .NET SqlCommand Object and XmlReader
Microsoft SQL Server 2000 supports XML capabilities natively. The results of SELECT statements can now be returned as XML through the use of FOR XML clause. To retrieve results as XML directly from the SQL Server 2000, we can make use of the ExecuteXmlReader method of the SQLCommand object. ExecuteXmlReader returns a System.Xml.XmlReader object containing XML returned from the SQL Server 2000.
In the following example, we call the ExecuteXmlReader method on the SQLCommand object to execute a SELECT statement, which uses FOR XML clause to return results as XML.
Dim sqlConn As SqlConnection
Dim sqlCmd As SqlCommand
Dim xmlRdr As XmlReader
Try
' Create a new connection object
sqlConn = New SqlConnection(myConnString)
' Create a new command object
sqlCmd = New SqlCommand()
' Specify the command to be exceuted
With sqlCmd
.CommandType = CommandType.Text
.CommandText = _
"Select * from Orders where OrderId < 10 For XML Auto "
.Connection = sqlConn
End With
' Open the connection
sqlConn.Open()
' Execute the command and retrieve the row in the DataReader
xmlRdr = sqlCmd.ExecuteXmlReader()
' Move to the Root Element
xmlRdr.MoveToContent()
' Do something with data
outXML = xmlRdr.ReadOuterXml
' Move to the Next Element
xmlRdr.MoveToElement()
' Read the OrderId Attribute of the Current Element
xmlRdr.MoveToAttribute("OrderId")
…
Catch e As Exception
' Catch Exception here
Finally
sqlConn.Close()
End Try
Note See Example 2 in the BDAdotNetData3.vb sample code (see top of article for download).
Using the ADO .NET DataAdapter Object
The DataAdapter object acts as a mapping layer between the data source and the DataSet object. It retrieves data from the data source, populates the DataSet object, and sends the changes back to the data source.
In the next example, we retrieve all the orders from the Orders table in the database. The data is retrieved in a table in the DataSet object:
Dim sqlDA As SqlDataAdapter
Dim resultDS As DataSet
Dim i As Integer
Try
' Create a new SQLDataAdapter object
sqlDA = New SqlDataAdapter()
' Create a new DataSet object
resultDS = New DataSet()
With sqlDA
' Add a SelectCommand object
.SelectCommand = New SqlCommand()
' Specify the Select Command
With .SelectCommand
.CommandType = CommandType.StoredProcedure
.CommandText = "GetOrderHeaders"
.Connection = New SqlConnection(myConnString)
End With
' Populate the DataSet with the returned data
.Fill(resultDS, "Orders")
End With
' Retrieve rows from the DataSet
For i = 0 To resultDS.Tables("Orders").Rows.Count - 1
' Do something with the row
outString = resultDS.Tables("Orders").Rows(i). _
Item("OrderId").ToString() & "," & _
resultDataSet.Tables("Orders").Rows(i). _
Item("CustomerId").ToString()
Next
Catch
' Handle the exception
…
Finally
' Cleanup Code here
End Try
Note See Example 3 in the BDAdotNetData3.vb sample code (see top of article for download).
Note that because the Orders table was not in the DataSet object prior to invoking the Fill method, the SQLDataAdapter object will automatically create the table for the DataSet object and populate it with the returned data. If you build any table prior to executing the Fill method, the SQLDataAdapter object will simply fill the existing table.
Write Operations
The ADO .NET DataAdapter object and DataSet object allow retrieving, manipulating, and updating the data in the database. You can update the table in the DataSet object by inserting new rows, updating existing rows, or deleting existing rows. The DataAdapter object can then be used to send the updates made to the DataSet table to the database.
Using the ADO .NET DataAdapter Object
The Update method of the DataAdapter object submits changes cached in the DataSet object to the data source. The DataAdapter object uses InsertCommand to submit new rows, UpdateCommand to submit modified rows, and DeleteCommand to remove rows from the database. When you call the Update method, the DataAdapter object examines the modified rows and determines which Command object to execute in order to submit the pending change for that row.
Before calling the Update method of the DataAdapter, you must set the InsertCommand, UpdateCommand, or DeleteCommand properties, depending on what changes were made to the data in the DataSet. For example, if rows have been deleted from the DataSet, you must set the DeleteCommand property. You can take advantage of the CommandBuilder object to automatically generate the Insert, Update, and Delete commands. If you specify the InsertCommand, UpdateCommand, or DeleteCommand properties of the DataAdapter object, the Update method executes respective Insert, Update, or Delete commands for each of the inserted, updated, or deleted rows in the DataSet object. Otherwise, the CommandBuilder generates the SQL Commands required to reconcile changes with the database, based on the SelectCommand property of the DataAdapter. Hence, the SelectCommand property of the DataAdapter must be set in order for the CommandBuilder to generate the Insert, Update, and Delete commands.
The preferred way is to specify your own InsertCommand, DeleteCommand, or UpdateCommand completely, because it allows you to explicitly control how the update is done and has better performance than does the auto-generated case.
Inserts
You can insert new rows in the DataSet object and reconcile the changes with the data source. When you don't specify the InsertCommand property, the CommandBuilder object auto-generates the Insert command for the DataAdapter to insert the new rows in the database.
Auto-generated Insert command
In the following code example, we retrieve all the orders from the Orders table in the database. The data is retrieved in a table in the DataSet object. We add a few rows to the DataSet object and reconcile the changes with the database using the Update method of the SQLDataAdapter object:
Dim sqlDA As SqlDataAdapter
Dim resultDS As DataSet
Dim sqlCmdBldr As SqlCommandBuilder
Dim workRow As DataRow
Try
…
' Create a new SQLCommandBuilder object to automatically generate
' Update statements
sqlCmdBldr = New SqlCommandBuilder(sqlDA)
'Create a new SQLCommand object for SelectCommand
sqlDA.SelectCommand = New SqlCommand()
With sqlDA
' Add a SelectCommand object
.SelectCommand = New SqlCommand()
' Specify the Select Command
With .SelectCommand
.CommandType = CommandType.StoredProcedure
.CommandText = "GetOrderHeaders"
.Connection = New SqlConnection(myConnString)
End With
' Populate the DataSet with the returned data
.Fill(resultDS, "Orders")
End With
Note that because the Orders table was not in the DataSet object prior to invoking the FillDataSet method, the SQLDataAdapter object will automatically create the table for the DataSet object and populate it with the returned data. If you build any table prior to executing the FillDataSet method, the SQLDataAdapter object will simply fill the existing table:
' Set the default values for columns
resultDS.Tables("Orders").Columns("ShippingHandling"). _
DefaultValue = 0
resultDS.Tables("Orders").Columns("Tax").DefaultValue = 0
resultDS.Tables("Orders").Columns("SubTotal").DefaultValue = 0
' Create a new Row
workRow = resultDS.Tables("Orders").NewRow
' Fill in order data
workRow.Item("CustomerId") = 1
workRow.Item("OrderDate") = Now()
…
' Add the row to the DataSet
resultDS.Tables("Orders").Rows.Add(workRow)
' Create another new Row
workRow = resultDS.Tables("Orders").NewRow
' Fill in order data
workRow.Item("CustomerId") = 1
workRow.Item("OrderDate") = Now()
…
' Add the row to the DataSet
resultDS.Tables("Orders").Rows.Add(workRow)
' Reconcile changes with the data source
sqlDA.Update(resultDS, "Orders")
Catch
' Handle the exception
…
Finally
' Cleanup code here
End Try
Note See Example 4 in the BDAdotNetData3.vb sample code (see top of article for download).
Because the InsertCommand property of the SQLDataAdapter object has not been specified, the CommanBuilder object auto-generates the statement when the Update method is called, depending on the Select command we specified.
Using the InsertCommand property
To specify your own INSERT statement, which should be executed when the Update method is called on the DataAdapter object, set the InsertCommand property. The DataAdapter object will execute the specified Insert command to insert the rows in the database. You can use a parameterized Insert query or a stored procedure to set the InsertCommand property.
When using the SQL managed provider, you have to use named parameters in the parameterized query. There is a difference when using parameters with the ADO managed provider, where you use positional parameters with parameter marker as '?'.
You need to set the SourceColumn property for all the parameters. The SourceColumn property tells the DataAdapter object which column in the table is going to provide its value. Set it to the actual parameter name:
Dim sqlConn As SqlConnection
Dim sqlDA As SqlDataAdapter
Dim resultDS As DataSet
Try
' Create a new connection
sqlConn = New SqlConnection(Common.getConnectionString)
' Create a new DataAdapter object
sqlDA = New SqlDataAdapter()
' Create a new DataSet object
resultDS = New DataSet()
With sqlDA
' Add a SelectCommand object
.SelectCommand = New SqlCommand()
' Specify the Select Command
With .SelectCommand
.CommandType = CommandType.StoredProcedure
.CommandText = "GetOrderHeaders"
.Connection = sqlConn
End With
' Add a InsertCommand object
.InsertCommand = New SqlCommand()
' Specify the Insert Command
With .InsertCommand
.CommandType = CommandType.Text
' Parameterized Insert Query with named parameters
.CommandText = "Insert Into Orders " & _
"(CustomerId, OrderDate, " & _
"ShipToName, ShipToAddressId, OrderStatus) " & _
"Values (@CustomerId, @OrderDate, " & _
"@ShipToName, @ShipToAddressId, @OrderStatus)"
' Define parameters in the parameterized Insert statement
.Parameters.Add _
(New SqlParameter("@CustomerId", SqlDbType.Int))
' Set the SourceColumn property
.Parameters("@CustomerId").SourceColumn = "CustomerId"
.Parameters.Add _
(New SqlParameter("@OrderDate", SqlDbType.DateTime))
' Set the SourceColumn property
.Parameters("@OrderDate").SourceColumn = "OrderDate"
…
…
.Connection = sqlConn
End With
' Populate the DataSet with the returned data
.Fill(resultDS, "Orders")
End With
…
Note See Example 5 in the BDAdotNetData3.vb sample code (see top of article for download).
Follow the same steps to add new rows to the table (as described in the section on using the auto-generated Insert command):
'Reconcile changes with the data source sqlDA.Update(resultDS, "Orders") Catch e As Exception ' Catch Exception here Finally End Try
Updates
You can update the rows in the DataSet object and reconcile the changes with the data source. When you don't specify the UpdateCommand property, the CommandBuilder object auto-generates the Update command for the DataAdapter to update the rows in the database.
Auto-generated Update command
In the following code example, we retrieve all the orders from the Orders table in the database. The data is retrieved in a table in the DataSet object. We update a few rows in the DataSet object and reconcile the changes with the database using the Update method of the SQLDataAdapter object:
Dim sqlDA As SqlDataAdapter
Dim sqlCmdBldr As SqlCommandBuilder
Dim resultDS As DataSet
Dim colItemName As String
Try
' Create a new DataAdapter object
sqlDA = New SqlDataAdapter()
' Create a new DataSet object
resultDS = New DataSet()
' Create a new CommandBuilder object to automatically generate Update
' statements
sqlCmdBldr = New SqlCommandBuilder(sqlDA)
' Specify the Select command
With sqlDA
' Add a SelectCommand object
.SelectCommand = New SqlCommand()
' Specify the Select command
With .SelectCommand
.CommandType = CommandType.StoredProcedure
.CommandText = "GetOrderHeaders"
.Connection = New SqlConnection(myConnString)
End With
' Populate the DataSet with the returned data
.Fill(resultDS, "Orders")
End With
' Update last row in the table
' Change the ShipToName to a new Value
resultDS.Tables("Orders").Rows(resultDS.Tables("Orders"). _
Rows.Count - 1).Item("ShipToName") = _
"ResidentBDAdotNetData3Example5"
' Change the Order Status to a new Value
resultDS.Tables("Orders").Rows(resultDS.Tables("Orders"). _
Rows.Count - 1).Item("OrderStatus") = 401
' Update next-to-last row in the table
' Change the ShipToName to a new Value
resultDS.Tables("Orders").Rows(resultDS.Tables("Orders"). _
Rows.Count - 2).Item("ShipToName") = _
"ResidentBDAdotNetData3Example5"
' Change the Order Status to a new Value
resultDS.Tables("Orders").Rows(resultDS.Tables("Orders"). _
Rows.Count - 2).Item("OrderStatus") = 40
' Reconcile changes with the data source
sqlDA.Update(resultDS, "Orders")
Catch E As Exception
' Handle Exception
Finally
End Try
Note See Example 6 in the BDAdotNetData3.vb sample code (see top of article for download).
Using the UpdateCommand property
To specify your own UPDATE statement, which should be executed when the Update method is called on the SQLDataAdapter object, set the UpdateCommand property. You can use a parameterized Update query or stored procedure for this purpose.
There are two important properties that need to be set for each of the parameters defined in the parameterized query. These properties are SourceColumn and SourceVersion, which are discussed later:
Dim sqlConn As SqlConnection
Dim sqlDA As SqlDataAdapter
Dim resultDS As DataSet
Try
' Create a new connection
sqlConn = New SqlConnection(myConnString)
' Create a new DataAdapter object
sqlDA = New SqlDataAdapter()
' Create a new DataSet object
resultDS = New DataSet()
With sqlDA
' Add a SelectCommand object
.SelectCommand = New SqlCommand()
' Specify the Select Command
With .SelectCommand
.CommandType = CommandType.StoredProcedure
.CommandText = "GetOrderHeaders"
.Connection = sqlConn
End With
With .UpdateCommand
.CommandType = CommandType.Text
' Parameterized Update Query with named parameters
.CommandText = "Update Orders Set " & _
"OrderStatus=@OrderStatus, " & _
"ShipToName=@ShipToName "
"Where OrderId=@OrderId"
.Connection = sqlConn
' Define parameters in the parameterized Update statement
.Parameters.Add(New SqlParameter("@OrderId", SqlDbType.Int))
' Set the SourceColumn property
.Parameters("@OrderId").SourceColumn = "OrderId"
.Parameters.Add(New SqlParameter("@OrderStatus", SqlDbType.Int))
' Set the SourceColumn property
.Parameters("@OrderStatus").SourceColumn = "OrderStatus"
' Set the SourceVersion property
.Parameters("@OrderStatus").SourceVersion = _
DataRowVersion.Current
…
End With
' Populate the DataSet with the returned data
.Fill(resultDS, "Orders")
End With
'Update some rows in the DataSet
Follow the same steps described in the section on using the auto-generated Insert command to update rows:
' Reconcile changes with the data source
sqlDA.Update(resultDS, "Orders")
Catch e As Exception
' Catch Exception here
Finally
End Try
Note See Example 7 in the BDAdotNetData3.vb sample code (see top of article for download).
SourceColumn tells the DataAdapter object which column in the table is going to provide its value. To set this, assign the name of the column in the table. UpdateCommand uses the SourceVersion property during the update to determine whether the original or current value is used for a parameter value. This allows primary keys to be updated. InsertCommand and DeleteCommand ignore this property.
If you are changing the primary key from say 5 to 15, you must be able to say, "Update the row whose primary key is 5 to have a primary key of 15." So, for this, if your parameterized query is something like:
"Update myTable Set colPrimary=@colPrimary Where colPrimary=@oldcolPrimary"
you set the SourceVersion property of the @oldcolPrimary parameter to DataRowVersion.Original.
Deletes
You can delete some rows in the DataSet object and reconcile the changes with the data source. If you don't set the DeleteCommand property, the CommandBuilder object will auto-generate the Delete command to delete rows from the table in the database.
Auto-generated Delete command
In the following code, we retrieve all the orders from the Orders table in the database. The data is retrieved in a table in the DataSet object. We delete a few rows from the DataSet object and reconcile the changes with the database using the Update method of the SQLDataAdapter object:
Dim sqlDA As SqlDataAdapter
Dim resultDS As DataSet
Dim sqlCmdBldr As SqlCommandBuilder
Try
' Create a new SQLDataAdapter object
sqlDA = New SqlDataAdapter()
' Create a new DataSet object
resultDS = New DataSet()
' Create a new SQLCommandBuilder object to automatically
' generate Update statements
sqlCmdBldr = New SqlCommandBuilder(sqlDA)
With sqlDA
' Add a SelectCommand object
.SelectCommand = New SqlCommand()
' Specify the Select command
With .SelectCommand
.CommandType = CommandType.StoredProcedure
.CommandText = "GetOrderHeaders"
.Connection = sqlConn = New SqlConnection(myConnString)
End With
' Populate the DataSet with the returned data
.Fill(resultDS, "Orders")
End With
' Delete last two rows from the Orders table in the DataSet
resultDS.Tables("Orders").Rows _
(resultDS.Tables("Orders").Rows.Count - 1).Delete()
resultDS.Tables("Orders").Rows _
(resultDS.Tables("Orders").Rows.Count - 2).Delete()
' Reconcile the changes with the Database
sqlDA.Update(resultDS, "Orders")
Catch e As Exception
' Catch Exception here
Finally
End Try
Note See Example 8 in the BDAdotNetData3.vb sample code (see top of article for download).
Using the DeleteCommand property
To specify your own DELETE statement, which should be executed when the Update method is called on the SQLDataAdapter object, set the DeleteCommand property. You can use a parameterized Delete query or stored procedure for this purpose.
You need to set the SourceColumn property for all the parameters. SourceColumn tells the DataAdapter object which column in the table is going to provide its value. Set it to the actual parameter name:
Dim sqlConn As SqlConnection
Dim sqlCmd As SqlCommand
Dim sqlDA As SqlDataAdapter
Dim resultDS As DataSet
Try
' Create a new connection object
sqlConn = New SqlConnection(Common.getConnectionString)
' Create a new SQLDataAdapter object
sqlDA = New SqlDataAdapter()
' Create a new DataSet object
resultDS = New DataSet()
With sqlDA
' Specify the Select command
With .SelectCommand
.CommandType = CommandType.StoredProcedure
.CommandText = "GetOrderHeaders"
.Connection = sqlConn
End With
'Specify the DeleteCommand
With .DeleteCommand
.CommandType = CommandType.Text
' Parameterized Delete Query with named parameters
.CommandText = "Delete from Orders Where OrderId=@OrderId"
.Connection = New SqlConnection(myConnString)
' Define parameters in the parameterized Delete statement
.Parameters.Add(New SqlParameter("@OrderId", SqlDbType.Int))
' Set the SourceColumn property
.Parameters("@OrderId").SourceColumn = "OrderId"
End With
' Populate the DataSet with the returned data
.Fill(resultDS, "Orders")
End With
' Delete last two rows from the Orders table in the DataSet
resultDS.Tables("Orders").Rows _
(resultDS.Tables("Orders").Rows.Count - 1).Delete()
resultDS.Tables("Orders").Rows _
(resultDS.Tables("Orders").Rows.Count - 2).Delete()
' Reconcile the changes with the Database
sqlDA.Update(resultDS, "Orders")
Catch e As Exception
Finally
' Close the connection
sqlConn.Close()
End Try
Note See Example 9 in the BDAdotNetData3.vb sample code (see top of article for download).
Conclusion
ADO .NET provides a choice of features that allow access to sets of data rows. The .NET Framework provides the DataReader, DataSet, and DataAdapter objects for reading sets of data rows. If the data source is SQL Server 2000, returning a set of data rows as XML into an XmlReader is another viable option.
