BA 371 - VB.Net Database Communication

BA 371: Database Communications

VB.Net 2008 – DB Communications

VB.Net 2008 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 a Miscrosoft SQL 2008 database of the same name as your ONID login name located on the database server cob-tfs08(Check if the database is present. If you dont find a database with your ONID name, talk to your instructor.)
- Open START>All Programs>Microsoft Software>Microsoft SQL Server 2008>SQL Server Management Studio
- A "Connect To Server" dialogue box shows up. Ensure "Server Type" is set to "Database Engine". Set "Server name" to "cob-tfs08". Use "Windows Authentication". Now click on Connect.
- Drill down the "Databases" menu item in Object Explorer Window to find your database


   Public Class Foo

	Public Shared Sub Main()
	
	   'Microsoft SQL Server 2008 
          'Replace "your database" with the name of your database in the following statement
		Dim ConnectString As String = "Provider=SQLOLEDB.1;" + _
	   	   "Data Source=cob-tfs08; Initial Catalog = your database;" + _
         "Integrated Security = SSPI;"

          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 your MS SQL database (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 two User variables. One for the databse server and another for the database name.

You may need to create these variables. To create variables select New and set the variable name to dbserver and the variable value to cob-tfs08. Repeat for the other user variable.Set variable vbdb to value 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 change to' stick' you must restart Visual Studio 2008.

Change your code as follows and retest the connections.


            Public Class Foo

	Public Shared Sub Main()
	
		'Microsoft SQL 2008
                Dim ConnectString As String = "Provider=SQLOLEDB.1;" + _
		"Data Source=" + Environ("dbserver") + ";" + " Initial Catalog = " + Environ("vbdb") + ";" + _
		"Integrated Security = SSPI;"

		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 MS SQL database, create a table foo with a string column my_name and a data type(varchar(50)).
- Drill down to the "Tables" menu item from your database in the Object Explorer. Right click on "Tables" and select "New table" from the menu. A tabbed window named "COB-TFS08...dbo.Table_1" will open for you to edit.Enter "my_name" under "Column Name". Enter "varchar(50)" in the "Data Type" column. Click on save button, and enter "foo" as the name of the table in the "Choose Name" dialogue box.
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 MS SQL 2008 itself by executing the query from MS SQL Server Management Studio :

Right click on the foo database in the Object Explorer window
Select "New Query" option form the menu. A window where you can type the SQL query appears on the right.
Type in your SQL(terminated by a semicolon!!)and execute it by clicking on the button with a red exclamation followed by Execute.

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 2008 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). To do this : Right click on the foo table in the Object Explorer pane.Select "Edit Top 200 Rows" menu item. A editable "COB...dbo.foo" window will open with the first entry under "my_name" set to NULL. Now, test in MS SQL 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()

		'Microsoft SQL 2008
                Dim ConnectString As String = "Provider=SQLOLEDB.1;" + _
		"Data Source=" + Environ("dbserver") + ";" + " Initial Catalog = " + Environ("vbdb") + ";" + _
		"Integrated Security = SSPI;"

		Dim Connection As New System.Data.OleDb.OleDbConnection(ConnectString)
		
		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?