BA 371 - VB.Net Database Communication

BA 371: Database Communications

VB.Net 2005 – DB Communications

VB.Net 2005 communicates with databases through, surprise!, predefined classes and objects.
Note!!: When programming against databases, !!!ALWAYS!!!!! check each and every operation/transaction for completion (Try/Catch blocks).
Some advise: modify your Windows explorer/View options such that you do NOT HIDE file extensions. In Windows Explorer (Tools-->Folder Options -->View Tab--> Hide extensions for known file types.)
In the following console(!) application example we connect and disconnect to an Access database located at c:foo.accdb (foo.mdb in Acces 2003) (since you most likely do not have that database, make a blank one in Access first


      Public Class Foo

	Public Shared Sub Main()
	
		'Access 2003
                'Dim ConnectString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" + _
		'"Data Source=c:\foo.mdb"

                'Access 2007
                Dim ConnectString As String = "provider=Microsoft.ACE.OLEDB.12.0;" + _
                "Data Source=c:\foo.accdb"

		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)
			Console.WriteLine()
			Console.WriteLine("Press Enter to Exit.")
			System.Console.ReadLine()
			System.Environment.Exit(1)
		End Try

		Try
			Connection.Close()
			Console.WriteLine("Connection closed...")
		Catch e As Exception
			Console.WriteLine("Problems closing..." + e.Message)
			Console.WriteLine()
			Console.WriteLine("Press Enter to Exit.")
			System.Console.ReadLine()
			System.Environment.Exit(1)
		End Try

		Console.WriteLine()
		Console.WriteLine("Press Enter to Exit.")
		System.Console.ReadLine()
		System.Environment.Exit(0)

	End Sub 'end Main
      End Class

Notice how the above code only connects/disconnects from the database; no database transactions (create, insert, update, etc.) 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 so that we can gracefully catch an unsuccessful Open attempt. Test this by trying to open a connection to a nonexisting database!!

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: You will need to set the startup object of the console application to Sub Main() before compiling.)
Note!!!! The above code hardwires the path and name of the database. Although we do it here for convenience, 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 major rule of programming: Do not 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 System-->Advanced Tab-->Environment Variables. Set a User variable vbdb to the path and name of your database.

You may need to create this variable. To create the variable select New and set the variable name to vbdb and the variable value to c:\temp\foo.mdb.

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 change to' stick' you must restart Visual Studio 2005.

Change your code as follows and retest the connections.


      Public Class Foo

	Public Shared Sub Main()
	
		'Access 2003
                'Dim ConnectString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" + _
		'"Data Source=" + Environ("vbdb")

                'Access 2007
                Dim ConnectString As String = "Provider=Microsoft.ACE.OLEDB.12.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)
			Console.WriteLine()
			Console.WriteLine("Press Enter to Exit.")
			System.Console.ReadLine()
			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
		
		Console.WriteLine()
		Console.WriteLine("Press Enter to Exit.")
		System.Console.ReadLine()
		
	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 would have to do is change the contents of the env. var; the code you will not have to touch! Try it! 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 on which you run your program.

Another way of keeping machine-specific information out of your program would be to let the application at startup read a so-called 'resource' or 'configuration file' that contains the machine-specific information.

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

In your Access database, create a table foo with a string column my_name and a data type(type text). The SQL for a query that returns all my_name entries from the foo table would be as follows:

select my_name from foo;

Try this out in Access itself by using its built-in SQL interpreter:

Start a new query in design view, then click away the "Show Table" dialog and click on the SQL icon on the left of the menu bar; type in your SQL (terminated by a semicolon!!) and hit the exclamation mark to run the SQL.

Try this before running your query through VB.Net. 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 2005 we use an OleDBCommand object to store the SQL 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, manually fill up the foo table with multiple records (three or five will do fine). Now, test in Access that the above query results in more than one my_name being returned. All of these records will be returned to the OleDbDataReader object when it uses its ExecuteReader() method.

If our program must display each of these my_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()

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

                'Access 2007
                Dim ConnectSTring as String = _
                "Provider=Microsoft.ACE.OLEDB.12.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 = nothing

		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)
			Console.WriteLine()
			Console.WriteLine("Press Enter to Exit.")
			System.Console.ReadLine()
			System.Environment.Exit(1)
		End Try

		'set the SQL statement
		SQLCommand = New System.Data.OleDb.OleDbCommand("select my_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...")
			Console.WriteLine()
			Console.WriteLine("Press Enter to Exit.")
			System.Console.ReadLine()
			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...")
			Console.WriteLine()
			
			Console.WriteLine("Press Enter to Exit.")
			System.Console.ReadLine()
			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
		
		Console.WriteLine()
		Console.WriteLine("Press Enter to Exit.")
		System.Console.ReadLine()
	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 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 of VB.Net database connectivity:

Open the connection.

Store the SQL statement on an OleDbCommand object.

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

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

When done, close the DataReader and close the Connection.

Challenge: Can you now change the program so that instead of reading from the database it writes another my_name to the foo table?