Priya Dhawan
Microsoft Developer Network
Revised February 2002
Summary: This article covers data operations that return single row entities using ADO .NET. (14 printed pages)
Download Bdadotnet_beta2.msi.
Contents
Introduction
Operations That Return Data
Getting a Single Value Using Output Parameters
Using the ADO .NET DataReader Object
Using the ADO .NET SQLCommand Object and XmlReader
Using the ADO .NET DataSet Object
Write Operations
Using the ADO .NET Command Object
Using the ADO .NET DataAdapter Object
Conclusion
Introduction
ADO .NET provides a variety of ways to execute queries returning single row data entities and to modify that data. This article discusses these techniques using the ADO .NET SQL managed provider.
Operations That Return Data
Data from SELECT statements within an SQL expression may be returned using an Output parameter on the ADO .NET Command object, a DataReader object, or a DataSet object.
Getting a Single Value
The ADO .NET Command object provides a method called ExecuteScalar to retrieve a single value from the database. This method executes a query and returns the first column of the first row in the resultset returned by the query. The resultset may contain multiple rows, which are ignored by ExecuteScalar.
The following example uses the ExecuteScalar method of the Command object to retrieve a single value from the database.
Dim sqlConn As SqlConnection
Dim sqlCmd As SqlCommand
Dim param As SqlParameter
Dim orderStatus As Integer
Try
'Create a new connection object
sqlConn = New SqlConnection(Common.getConnectionString)
'Create a new command object
sqlCmd = New SqlCommand()
'Specify the stored procedure and connection
With sqlCmd
.CommandType = CommandType.Text
.CommandText = _
"Select OrderStatus from Orders Where OrderId=@OrderId"
.Connection = sqlConn
End With
'Define and add the input parameter to the parameters collection
param = sqlCmd.Parameters.Add(New _
SqlParameter("@OrderId", SqlDbType.Int))
'Specify the parameter direction
param.Direction = ParameterDirection.Input
'Set the parameter value
param.Value = 1
'Open the connection
sqlConn.Open()
'Execute the command and get a single value
orderStatus = CInt(sqlCmd.ExecuteScalar())
Catch e As Exception
' Catch Exception here
Finally
' Close the connection
sqlConn.Close()
End Try
Note See Example 1 in the BDAdotNetData2.vb sample code (see top of article for download).
Using Output Parameters
A stored procedure with Output parameters is efficient not only as a way to execute a query but as a way to return a single row data entity. Output parameters are handled using the parameters collection of the ADO .NET Command object.
In the following example, parameters to a stored procedure are defined with the Parameters collection of the Command object. The stored procedure expects an Input parameter "@OrderId" and an Output parameter "@OrderStatus," which returns the status of the specified order:
Dim sqlConn As SQLConnection
Dim sqlCmd As SQLCommand
Dim param As SQLParameter
Try
' Create a new connection object
sqlConn = New SQLConnection(myConnString)
' Create a new command object
sqlCmd = New SQLCommand()
' Specify the stored procedure and connection
With sqlCmd
.CommandType = CommandType.StoredProcedure
.CommandText = "GetOrderStatus"
. Connection = sqlConn
End With
' Define and add the input parameter to the parameters collection
param = SQLCmd.Parameters.Add(New _ SQLParameter("@OrderId", _
SQLDBType.Int))
With param
' Specify the parameter direction
.Direction = ParameterDirection.Input
' Set the parameter value
.Value = 1
End with
' Define and add the output parameter to the parameters collection
param = SQLCmd.Parameters.Add(New _
SQLParameter("@OrderStatus", _
SQLDBType.Int))
' Specify the parameter direction
param.Direction = ParameterDirection.Output
' Open the connection
SQLConn.Open()
' Execute the command
sqlCmd. ExecuteNonQuery ()
' Do something with returned data
orderStatus = sqlCmd.Parameters("@OrderStatus").Value.ToString
…
Catch e As Exception
' Handle the exception
…
Finally
' Close the connection
sqlConn.Close()
End Try
Note See Example 2 in the BDAdotNetData2.vb sample code (see top of article for download).
Using the ADO .NET DataReader Object
A disadvantage of Output parameters is that they require corresponding application code on the database, such as stored procedures for Microsoft SQL Server™ databases or parameter queries for .mdb databases. The ADO .NET DataReader can return data from ad hoc SQL queries as well as stored procedures. The DataReader object returns a read-only, forward-only stream of data.
In the next example, the query returns a single row containing the order with OrderId 1 placed by a customer whose CustomerId is 1:
Dim sqlConn As SQLConnection
Dim sqlCmd As SQLCommand
Dim sqlDataRdr As SqlDataReader
…
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 = "Exec GetOrderHeader @OrderId=1"
.Connection = sqlConn
End With
' Open the connection
sqlConn.Open()
' Execute the command and retrieve the row in the DataReader
sqlDataRdr = sqlCmd.ExecuteReader()
' Position the pointer at the row
sqlDataRdr.Read()
' Do something with the row
outString = sqlDataRdr.Item("OrderId").ToString() & "," & _
sqlDataRdr.Item("CustomerId").ToString() & "," & _
sqlDataRdr.Item("OrderStatus").ToString() & "," & _
sqlDataRdr.Item("OrderDate").ToString()
Catch e As Exception
' Handle the exception
…
Finally
' Close the DataReader
sqlDataRdr.Close()
' Close the connection
sqlConn.Close()
End Try
Note See Example 3 in the BDAdotNetData2.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, the SQLCommand object executes 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
' Use FOR XML Clause to get results as XML
.CommandText = _
"Select * from Orders where OrderId = 1 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 the data
outXML = xmlRdr.ReadOuterXML()
…
Catch e As Exception
' Catch Exception
Finally
sqlConn.Close()
End Try
Note See Example 4 in the BDAdotNetData2.vb sample code (see top of article for download).
Using the ADO .NET DataSet Object
The DataSet object is the primary disconnected data container provided by the Microsoft® .NET Framework. It is a sophisticated cache that, together with the SQL and ADO managed providers, supports reading from and writing modified data back to a data source. Despite the fact that a single row data entity requires only a small subset of its features, the DataSet object is a useful container that should be considered even for a single row data entity.
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, the query returns a single row containing the order with OrderId 1 placed by a customer whose CustomerId is 1:
Dim resultDS As DataSet
Dim sqlDA As SqlDataAdapter
Try
'
'
' Create a new DataAdapter object
sqlDA = New SqlDataAdapter()
' Create a new DataSet
resultDS = New DataSet()
With sqlDA
' Add a SelectCommand object
.SelectCommand = New SqlCommand()
' Specify the Select Command
With .SelectCommand
.CommandType = CommandType.Text
.CommandText = "Exec GetOrderHeader @OrderId=1"
.Connection = New SqlConnection(myConnString)
End With
' Populate the DataSet with the returned data
.Fill(resultDS, "Order")
End With''""'""
' Do something with the row
colValue = _
resultDS.Tables("Order").Rows(0).Item("ShipToName").ToString()
…
Catch E As Exception
' Handle the exception
…
Finally
' …
End Try
Note See Example 5 in the BDAdotNetData2.vb sample code (see top of article for download).
Write Operations
A single row can easily be modified and sent back to the data source with the help of ADO .NET objects. The simplest technique for write operations is to use the Command object to execute either ad hoc SQL or stored procedures. The DataSet command offers more features, especially where data has been retrieved into a DataSet object.
Using the ADO .NET Command Object
Use of the ADO .NET Command object to execute ad hoc SQL or stored procedures is discussed in a companion article, Data Operations That Do Not Return Rows.
Using the ADO .NET DataAdapter Object
The DataAdapter object is used to connect to a data source, retrieve data, and populate the DataSet object with the data. The data in the DataSet object can be modified. The DataAdapter reconciles the changes in the DataSet object with the data source.
Using the DataSet 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 a new row, UpdateCommand to submit a modified row, and DeleteCommand to remove a row from the database.
If you specify the InsertCommand, UpdateCommand, or DeleteCommand properties of the DataAdapter object, the Update method executes the respective Insert, Update, or Delete. An alternative that is possibly simpler is to use the CommandBuilder object to auto-generate the Insert, Update, and Delete commands based on the SelectCommand. 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.
This article covers only the auto-generated commands used to update the data source with the changes. For specifying you own Insert, Update, and Delete commands, refer to the article Data Operations on Sets of Rows.
Auto-generated Insert command
When you call the Update method on the DataAdapter object, it auto-generates the Insert command, depending on the Select command you specified if the InsertCommand property has not been set.
In the following code example, the DataSet object gets populated with a single row containing an order. We add a single new row to the DataSet object, which is then inserted into the table in the data source:
Dim sqlDA As SqlDataAdapter
Dim resultDS As DataSet
Dim workRow As DataRow
Dim sqlCmdBldr As SqlCommandBuilder
Try
'
' Create a new DataSet
resultDS = New DataSet()
' Create a new DataAdapter object
sqlDA = New SqlDataAdapter()
' Create the CommandBuilder object to automatically generate the
' Insert, Update, and Delete Statements
sqlCmdBldr = New SqlCommandBuilder(sqlDA)
With sqlDA
' Add a SelectCommand object
.SelectCommand = New SqlCommand()
' Specify the Select command
With .SelectCommand
.CommandType = CommandType.Text
' Query on non-existing row returns empty row with metadata
.CommandText = "Exec GetOrderHeader @OrderId=-1"
.Connection = New SqlConnection(myConnString)
End With
' Populate the DataSet with the returned data
.Fill(resultDS, "Order")
End With'
' Set the default values for columns
resultDS.Tables("Order"). _
Columns("ShippingHandling").DefaultValue = 0
resultDS.Tables("Order").Columns("Tax").DefaultValue = 0
resultDS.Tables("Order").Columns("SubTotal").DefaultValue = 0
' Create a new Row
workRow = resultDS.Tables("Order").NewRow
' Fill in workrow data
workRow.Item("CustomerId") = 1
workRow.Item("OrderStatus") = 400
workRow.Item("OrderDate") = Now()
workRow.Item("ShipToName") = "ResidentBDAdotNetData2Example4"
workRow.Item("ShipToAddressId") = 1
' Add the row to the DataSet
resultDS.Tables("Order").Rows.Add(workRow)
' Reconcile changes with the data source
sqlDA.Update(resultDS, "Order")
Catch E As Exception
' Handle the exception
…
Finally
' Close Connection and other cleanup code here
End Try
Note See Example 6 in the BDAdotNetData2.vb sample code (see top of article for download).
Auto-generated Update command
When you call the Update method on the DataAdapter object, it auto-generates the Update command, depending on the Select command you specified if the UpdateCommand property has not been set.
In the following code example, the DataSet object gets populated with a single row containing an order. We update the row in the DataSet object. Finally, the DataAdapter object sends the changes back to the data source:
Dim sqlDA As SqlDataAdapter()
Dim resultDS As DataSet()
Dim sqlCmdBldr As SqlCommandBuilder
Dim colItemName As String
Try
'
' Create a new DataSet
resultDS = New DataSet()
' Create a new DataAdapter object
sqlDA = New SqlDataAdapter()
' Create the CommandBuilder object
sqlCmdBldr = New SqlCommandBuilder(sqlDA)
With sqlDA
' Add a new SelectCommand object
.SelectCommand = New SqlCommand()
' Specify the Select command
With .SelectCommand
.CommandType = CommandType.StoredProcedure
' Get last Order (as created by Example 4)
.CommandText = "GetLastOrderHeader"
.Connection = New SqlConnection(myConnString)
End With
' Populate the DataSet with the returned data
.Fill(resultDS, "Order")
End With'
' Update the Row
resultDS.Tables("Order").Rows(0).Item("ShipToName") = _
"Resident BDAdotNetData2Example5"
' Reconcile Changes
sqlDA.Update(resultDS, "Order")
Catch E As Exception
' Handle the exception
…
Finally
End Try
Note See Example 7 in the BDAdotNetData2.vb sample code (see top of article for download).
Auto-generated Delete command
When you call the Update method on the DataAdapter object, it auto-generates the Delete command, depending on the Select command you specified if the DeleteCommand property has not been set.
In the following code example, the DataSet object gets populated with a single row containing an order. We delete the row in the DataSet object. Finally, the DataAdapter object updates the database:
Dim sqlDA As SqlDataAdapter()
Dim resultDS As DataSet()
Dim sqlCmdBldr As SqlCommandBuilder
Dim workRow As DataRow
Dim colItemName As String
Try
' Create a new connection object
sqlConn = New SqlConnection(myConnString)
' Create a new DataSet
resultDS = New DataSet()
' Create a new DataAdapter object
sqlDA = New SqlDataAdapter()
' Add a SelectCommand object
sqlDA.SelectCommand = New SqlCommand()
sqlCmdBldr = New SqlCommandBuilder(sqlDA)
With sqlDA
' Add a SelectCommand object
.SelectCommand = New SqlCommand()
' Specify the Select command
With .SelectCommand
.CommandType = CommandType.StoredProcedure
' Get last Order (as created by Example 4). Delete will fail if
' this Order has details
.CommandText = "GetLastOrderHeader"
.Connection = New SqlConnection(myConnString)
End With
' Populate the DataSet with the returned data
.Fill(resultDS, "Order")
End With'
' Delete the row from the Order table in the DataSet
resultDS.Tables("Order").Rows(0).Delete()
' Reconcile changes with the data source
sqlDA.Update(resultDS, "Order")
Catch E As Exception
' Handle the exception
…
Finally
' Cleanup code here
End Try
Note See Example 8 in the BDAdotNetData2.vb sample code (see top of article for download).
Conclusion
ADO .NET provides a variety of features useful for reading and writing single row data entities. To select the most appropriate technique, consider not only the operations being performed against the data source, but also the representation of the data within the application. Input and Output parameters, the DataReader and XMLReader objects are lightweight and offer good performance, while the DataSet object combines a useful data container with advanced features.