Export (0) Print
Expand All

Data Operations Returning Single Row Entities

 

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.

Show:
© 2014 Microsoft