MARS in Action

MARS in Action

ADO.NET 2.0 provides a great new feature called MARS. We discussed how useful this is in the first sections of this chapter, and here you'll see an example of it in action.

Listing 3.1 demonstrates how useful MARS can be when dealing with related tables (though this is not, of course, the only way it can be used). The sample Northwind database we're using here contains an Orders table and a related Order Details table. The example fetches the first five rows from the Orders table, displays a couple of column values, and then—while this rowset is open over the connection—executes another command that returns a rowset of the related Order Details rows for the current order.

The code starts in the usual way by creating a single Connection and then two Command instances. The first Command selects the first five rows from the Orders table. The second selects the details of each line on an order, using a parameter named @orderid that will be set to the ID of the current order when this statement is executed. A parameter that corresponds to the @orderid parameter in the SQL statement is then added to the Command object's Parameters collection.

An Example of MARS in Action

' get connection string and specify SQL statements
Dim sConnectString As String = "your-connection-string"
Dim sOrderSQL As String = "SELECT TOP 5 OrderID, ShipName FROM Orders"
Dim sOrderLineSQL As String = "SELECT OrderID, Quantity, " _
           & "[Order Details].UnitPrice As Price, ProductName " _
           & "FROM [Order Details] JOIN Products " _
           & "ON [Order Details].ProductID = Products.ProductID " _
           & "WHERE OrderID = @orderid"

' create connection and two commands
Dim oConn As New SqlConnection(sConnectString)
Dim oCmdOrder As New SqlCommand(sOrderSQL, oConn)
Dim oCmdOrderLine As New SqlCommand(sOrderLineSQL, oConn)

' add parameter to second command to use for selecting order
oCmdOrderLine.Parameters.Add("@orderid", SqlDbType.Char, 5)
...

Fetching the Orders Rows

In Listing 3.2 the code continues by declaring a couple of DataReader variables and executing the command to get the Orders rowset. The While construct then starts by reading the first row and displaying the order ID and customer name.

Executing the Command to Get the Orders Rowset

...
' declare two DataReader variables and open connection
Dim oDROrder, oDROrderLine As SqlDataReader
oConn.Open()

' get rowset from Orders table
oDROrder = oCmdOrder.ExecuteReader(CommandBehavior.CloseConnection)

' iterate through orders
While oDROrder.Read()

  ' display order ID and name
  lblResult.Text &= oDROrder("OrderID").ToString() & " " _
                 & oDROrder("ShipName") & "<br />"
  ...

Fetching the Order Details Rows

Having discovered the ID of the current order, the code in Listing 3.3 now sets the value of the @orderid parameter and executes the second command to fetch the matching rows from the Order Details table. It iterates through this rowset, displaying the product name and quantity, and then closes this DataReader. However, the first DataReader is still open against the Orders table, so when the While loop ends, the code goes back and reads the next Orders row, then repeats the process.

Executing the Command to Get the Order Details Rowset

  ...
  ' set parameter for second command to value of order ID
  oCmdOrderLine.Parameters("@orderid").Value = oDROrder("OrderID")

  ' get list of lines for this order from Order Details table
  oDROrderLine = oCmdOrderLine.ExecuteReader()

  ' iterate through order lines displaying details
  While oDROrderLine.Read()
    lblResult.Text &= oDROrderLine("Quantity") & " " _
                   & oDROrderLine("ProductName") & " at " _
                   & oDROrderLine("Price") & "<br />"
  End While

  ' close DataReader for Order Details table
  oDROrderLine.Close()
  lblResult.Text &= "<br />"

End While

' close DataReader for Orders table
oDROrder.Close()

Once all the Orders rows have been read and the related Order Details rows for each one fetched and displayed, this DataReader is closed. Because the CloseConnection option was specified when this DataReader was opened, the connection to the database is closed automatically at this point.