CSCI1406

1
Lecture 23
CSCI1406 Introduction to Programming with Visual
Basic Dr Peter Yardley
2

• This lecture covers the mechanics of reading a
  database with VB.NET.
• Connecting to a database.
• Using the OleDbDataReader class to read data.

3
The Load button connects to the
database. Clicking on the Read Next Record
button displays records in sequence until the end
of the file is reached.
4
(No Transcript)
5

• Start a new Windows application.
• Create the user interface shown in the previous
  slide.
• Rename the Button controls btnLoad and btnRead.
• Rename the TextBox controls
• txtAuthorSurname, txtAuthorFirstName, txtTitle,
  txtISBN, txtPublisher.

6

• We need to do the following
• Create a connection string (giving details of the
  database)
• Create a string containing the SQL.
• Create an OleDbConnection object (this is passed
  the connection string)
• Create an OleDbCommand object (this is passed the
  SQL string and OleDbConnection object)
• Create an OleDbDataReader object
• The Open method of the OleDbConnection object
  opens the database.
• The ExecuteReader method of the OleDBDataReader
  executes the SQL.
• The GetString method of the OleDbDataReader
  objects reads fields from the file.

7
To connect to the database we need to create an
instance of the OleDbConnection class and to pass
it the information required to establish the
connection, such as the type of database and any
logon information which is required. Private
Const strConnection As String _
ProviderMicrosoft.Jet.OLEDB.4.0 _
Data SourceL\PYardley\CSCI1406\db1.mdb _
Persist Security InfoFalse This is passed to
the constructor of the OleDbConnection
object Dim objConnection As New _
System.Data.OleDb.OleDbConnection(strConnection)
This provides a connection to the database.
8

• Display the Desktop.
• Right click and select the New Text Document
  option.
• Rename this file so that it has an
  extension of .udl.
• Double click on this file to display the Data
  Link properties dialog and click on the Provider
  tag

9

• Select Microsoft Jet 4.0 OLE DB Provider
• Specify the connection aspects such as the
  database location and name on the Connection
  page.
• Finally open the .udl file with a text editor
  such as Notepad to cut the connection string
  which has been produced and paste this back into
  your application, remembering to place it in
  quotes.

10
The actual connection to the database is done
with Open method of the OleDbConnection
object objConnection.Open( )
11
To read from the database we need to create an
OleDbCommand object. The SQL statement to be
executed is created as a String and passed to the
new OleDbCommand constructor along with the name
of the OleDbConnection object Private Const
strSQL As String "SELECT FROM book"Dim
objcommand As New _ System.Data.OleDb.OleDbCom
mand(strSQL, objConnection)
12
The SQL is executed by the ExecuteReader method
of the OleDbCommand object and the result is
returned in an OleDbDataReader object Dim
objReader As System.Data.OleDb.OleDbDataReaderobj
Reader objcommand.ExecuteReader( ) To extract
the individual fields from the OleDbDataReader
object, the GetString method is used, for
example, objReader.GetString(0) is the first
field, the author surname.
13
Private Const strConnection As String _
"ProviderMicrosoft.Jet.OLEDB.4.0 " _
"Data SourceL\PYardley\CSCI1406\db1.mdbPersist
Security InfoFalse" Private Const strSQL As
String "SELECT FROM book Dim objConnection
As New _ System.Data.OleDb.OleDbConnection(strCon
nection) Dim objCommand As New System.Data.OleDb.O
leDbCommand(strSQL, _ objConnection) Dim
objReader As System.Data.OleDb.OleDbDataReader Pr
ivate Sub btnLoad_Click(ByVal sender As
System.Object, ByVal e As _ System.EventArgs)
Handles btnLoad.Click objConnection.Open()
objReader objCommand.ExecuteReader() End
Sub
14
Private Sub btnRead_Click(ByVal sender As
System.Object, _ ByVal e As System.EventArgs)
Handles btnRead.Click If objConnection.State
ConnectionState.Open Then If
objReader.Read() Then
txtAuthorSurname.Text objReader.GetString(0)
txtAuthorFirstName.Text
objReader.GetString(1)
txtTitle.Text objReader.GetString(2)
txtISBN.Text objReader.GetString(3)
txtPublisher.Text objReader.GetString(4
) Else MessageBox.Show("End of File",
"DataReader") Application.Exit()
End If End If End Sub
15
Error checking is especially important when
writing database applications e.g. Trying to
connect to a database when a connection has
already been made. Trying to read from a database
when there is no connection. Use Try and Catch
clauses e.g. Try objConnection.Open( )
Catch ex As System.Exception
MessageBox.Show("Error opening database",
"DataReader") Application.Exit( )End Try Try
Catch clauses should be used in a similar way
with methods which read data. A revised version
of the application with this and some additional
error checking is shown below
16
Private Const strConnection As String
ProviderMicrosoft.Jet.OLEDB.4.0 _ Data
SourceL\PYardley\CSCI1406\db1.mdb _
Persist Security InfoFalsePrivate Const
strSQL As String "SELECT FROM book"Dim
objConnection As New _ System.Data.OleDb.OleDbCon
nection(strConnection)Dim objcommand As New
_ System.Data.OleDb.OleDbCommand(strSQL, _
objConnection)Dim objReader As
System.Data.OleDb.OleDbDataReader
17
Private Sub btnLoad_Click(ByVal sender As
System.Object, _ ByVal e As System.EventArgs)
Handles btnLoad.Click If objConnection.State
ConnectionState.Closed Then Try
objConnection.Open( ) Catch ex As
System.Exception MessageBox.Show("Error
opening database", "DataReader")
Application.Exit( ) End Try objReader
objcommand.ExecuteReader( ) End IfEnd Sub
18
Private Sub btnRead_Click(ByVal sender As
System.Object, _ ByVal e As System.EventArgs)
Handles btnRead.Click If objConnection.State
ConnectionState.Open Then Try
If objReader.Read( ) Then
txtAuthorSurname.Text objReader.GetString(0)
txtAuthorFirstName.Text
objReader.GetString(1) txtTitle.Text
objReader.GetString(2) txtISBN.Text
objReader.GetString(3)
txtPublisher.Text objReader.GetString(4)
Else MessageBox.Show("End of File",
"DataReader") Application.Exit( )
End If Catch ex As System.Exception
MessageBox.Show("Error reading
database", "DataReader")
Application.Exit( ) End Try End
IfEnd Sub
19
The data is read only and can only be read
sequentially from the start to the end of the
database. A constant connection to the database
must be maintained. If the DataSet class is
used, a snapshot of the data is taken, and the
connection to the database may then be broken and
the data manipulated locally.