Page 335 - DCAP505_MODERN_PROGRAMMING_TOOLS_AND_TECHNIQUES_II

Unit 13: ADO.Net

You then produce a header for your output, using connection and command properties (Database Notes
and CommandText, respectively) to get the database name and query text.
‘Display output header
Console.WriteLine(“This program demonstrates the use “ & _
“of the SQL Server Data Provider.” & ControlChars.NewLine)
Console.WriteLine(“Querying database {0} with query {1}” & _
ControlChars.NewLine, Conn.Database, Cmd.CommandText)

Console.WriteLine(“FirstName” & ControlChars.Tab & “LastName”)
You retrieve all the rows in the result set by calling the data reader’s Read method, which
returns true if there are more rows and false otherwise. Note that the data reader is positioned
immediately before the first row prior to the first call to Read.

‘Process The Result Set
While (Reader.Read())
Console.WriteLine(Reader(“FirstName”).PadLeft(9) & _
ControlChars.Tab & Reader(1))
End While
You access each row’s columns with the data reader’s indexer (here, the SqlDataReader.Item
property), which is overloaded to accept either a column name or a zero-based integer index.
We’re using both to demonstrate the indexer’s use, but using column numbers is more efficient
than using column names.
Next you handle any exceptions, quite simplistically, but at least you’re developing a good habit.

Catch ex As Exception
Console.WriteLine(“Error: {0}”, ex)
Finally, in a Finally block, you close the data reader and the connection by calling their Close
methods. As a general rule, you should close things in a Finally block to be sure they get closed
no matter what happens within the Try block.

Finally
‘Close Connection
Reader.Close()
Conn.Close()
End Try
End Sub

End Module
Technically, closing the connection also closes the data reader, but closing both (in the previous
order) is another good habit. A connection with an open data reader can’t be used for any other
purpose until the data reader has been closed.

Self Assessment

Fill in the blanks:
6. The OLE DB data provider belongs to the ……………………….. namespace.

7. The OLE DB data provider’s connection class is named…………………….

LOVELY PROFESSIONAL UNIVERSITY 329