VB.NET – DB Communications

 

• VB.NET communicates with databases through, surprise!, predefined classes and objects.
• Establishing a connection with an MSAccess (not password-protected) database:
• Create a database OleDBConnection object.
• Pass it the database connection string.
• When programming against databases, !!!ALWAYS!!!!! check each and every operation/transaction for completion (try-catch blocks in VB.NET).
• Some advise: modify your Windows explorer/View options such that you do NOT HIDE file extensions.
  

 

• In the following console(!) application example we connect and disconnect to an Access database at c:\temp\foo.mdb:

Public Class Foo

 

    Public Shared Sub Main()

 

        Dim ConnectString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=c:\temp\foo.mdb"

 

        Dim Connection As New System.Data.OleDb.OleDbConnection(ConnectString)

 

        Try

            Console.WriteLine(ConnectString)

            Connection.Open()

            Console.WriteLine("Connection open...")

        Catch e As Exception

            Console.WriteLine("Problems opening..." + e.Message)

            System.Environment.Exit(1)

        End Try

 

        Try

            Connection.Close()

            Console.WriteLine("Connection closed...")

        Catch e As Exception

            Console.WriteLine("Problems closing..." + e.Message)

            System.Environment.Exit(1)

        End Try

        System.Environment.Exit(0)

    End Sub 'end Main

End Class

 

·        Notice how the above code only connects/disconnects from the database; no transactions occur.

·        Notice that the disconnect only occurs after a successful Open (do not disconnect from a database to which you are not connected).

·        Notice how the Open call sits inside a Try block.

·        Notice how a failed Open or failed Close is caught in the Catch block and how the Message part of the exception is written to the console.

• Try this out with an Access database that you generate yourself (for this exercise the database does need to contain any tables). Make sure you test both successful and unsuccessful connections.
• Note!!!! The above code hardwires the path and name of the database. This is bad(!!!) coding practice. If the database would ever be renamed or put on a different location on the system, the program could never establish a connection anymore and the code would have to be changed.
• Another golden rule of programming: Never, ever include network or machine-specific information in your code!
• One solution: let your program extract machine-specific information from a so-called environment variable:
• In Control Panel (select Classic View) select System-->Advanced-->Environment Variables. Set a User Variable vbdb to the path and name of your database.

Note: in Windows, env. vars. come in two types, 'User' (user-specific) and 'System' (system-wide). In the COB labs, you may not be allowed to set the 'System' env. vars, but you are allowed to set the 'User' env. variables.

• Since Visual Studio checks these env. vars at start-up, for the changes to stick you must restart Visual Studio.Net
  
• Change your code as follows and retest the connections.

 

Public Class Foo

 

    Public Shared Sub Main()

 

        Dim ConnectString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Environ("vbdb")

 

        Dim Connection As New System.Data.OleDb.OleDbConnection(ConnectString)

 

        Try

            Console.WriteLine(ConnectString)

            Connection.Open()

            Console.WriteLine("Connection open...")

        Catch e As Exception

            Console.WriteLine("Problems opening..." + e.Message)

            System.Environment.Exit(1)

        End Try

 

        Try

            Connection.Close()

            Console.WriteLine("Connection closed...")

        Catch e As Exception

            Console.WriteLine("Problems closing..." + e.Message)

        End Try

    End Sub

End Class

 

• Notice how the call to Environ(”vbdb”) resolves the vbdb env. var. to the path of your database. If in the future that database would ever be renamed or placed elsewhere on the system, all you have to do is change the contents of the env. var; the code you will not have to touch! Similarly, if you would take your code and database to a different machine altogether, no code changes would be necessary; just set the vbdb env. var on the machine.

• Next, let’s make an SQL query and display the results:

• Create a table foo with a string column name (type text). The SQL for a query that returns all names from the foo table would be as follows:

   select name from foo

  (Try this out in Access itself before you try to do it from VB. A little trick-of-the-trade: before including SQL calls into your program code, always!! try them out using the SQL interpreter of the database software itself. That way you can exclude SQL errors from messing up your program):

 

·        In VB.Net we use an OleDBCommand object to store the statement. The object's ExecuteReader() method can be used to execute the statement. This method returns an OleDbDataReader object which holds the results of the query.

 

·        Now fill up the foo table with multiple records (three or five will do fine). Now, the above query results in more than one name being returned to the OleDBDataReader object.

 

·        If our program must display each of these  name strings, it must grab them, one after the other, and display them to the screen. We do this grabbing using the Read() method on the OleDbDataReader object. Each time we call this method it will give us the next record returned from the database. When no more records are returned by the reader, we are done.

 

 Public Class Foo

 

    Public Shared Sub Main()

 

        Dim ConnectString As String = _

                "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Environ("vbdb")

 

        Dim Connection As System.Data.OleDb.OleDbConnection

 

        Dim SQLCommand As System.Data.OleDb.OleDbCommand

 

        Dim SQLDataReader As System.Data.OleDb.OleDbDataReader

 

        Dim result_string As String

 

        'open the connection

        Connection = New System.Data.OleDb.OleDbConnection(ConnectString)

        Try

            Console.WriteLine(ConnectString)

            Connection.Open()

            Console.WriteLine("Connection open...")

        Catch e As Exception

            Console.WriteLine("Problems opening..." + e.Message)

            System.Environment.Exit(1)

        End Try

 

        'set the SQL statement

        SQLCommand = New System.Data.OleDb.OleDbCommand("select name from foo", Connection)

 

        'execute the statement

        Try

            SQLDataReader = SQLCommand.ExecuteReader()

            'in case the execution fails, close the connection and exit

        Catch e As Exception

            Console.WriteLine("Problems executing..." + e.Message)

            Connection.Close()

            Console.WriteLine("Connection closed...")

            System.Environment.Exit(1)

        End Try

 

        'at this point we know that execution succeeded so we can start reading the

        'results. Since we do not know how many records we get returned, we'll stick

        'the read in a while() loop that keeps executing until we no longer get

        'results from the reader.

 

        Try

            Do While SQLDataReader.Read()

                result_string = SQLDataReader.GetString(0)

                Console.WriteLine(result_string)

            Loop

            SQLDataReader.Close()

        Catch e As Exception

            Console.WriteLine("Problems reading data..." + e.Message)

            Connection.Close()

            Console.WriteLine("Connection closed...")

            System.Environment.Exit(1)

        End Try

 

        'we’re done so we can close the connection

        Try

            Connection.Close()

            Console.WriteLine("Connection closed...")

        Catch e As Exception

            Console.WriteLine("Problems closing..." + e.Message)

        End Try

    End Sub

End Class

 

·        Note that the DataReader object never gets created in this code (no New  statement for it). This is because it gets created as a side effect by the ExecuteReader() method on the OleDbCommand object.

·        It is good practice to Close() your DataReader once you’re done with it (in our next and final lab we'll see the benefit of this!).

·        Also note that each of the database actions is enclosed in a try-catch block. Although there is no syntactical requirement to do so, it is very wise(!!!) to check each and every action run against a database. This gives your program opportunity to ‘bail out’ when things go wrong.

·        Summary:

o       Open the connection.

o       Store the SQL statement on an OleDbCommand object.

o       Call the ExecuteReader method on the OleDbCommand object to execute the query.

o       Call the Read() method on the OleDbDataReader object to access the results.

o       When done, close the DataReader and close the Connection.