CONNECTING TO AN ORACLE DATABASE IN VB.NET
Or, How I Learned to Stop Worrying And Love the Oracle
by Poopdog
I was told that my time had come. It was time to work with Oracle.
I was told to work with Oracle in VB.NET.
In fact, not only was I to work with Oracle, but I was to write a generic Oracle database connection object that ALL future applications will be relying upon.
Considering I had never even SEEN an Oracle DB, I had panic, fear, and excitement about this.
So I started at the beginning:
Here are the assumptions with the application:
1: ALL APPS WILL USE STORED PROCEDURES!
Any time data is to be written or pulled from the Oracle Database, it will be done with Stored Procedures. This will increase performance and make future code changes easier to handle. Agree or not, it doesnt matter, its what the boss wants.
So, of course, I snuck in a little tiny function at the very bottom that would allow me to do just what I'm not supposed to do. I didnt have time to fully test it, but it wont take a whole lot of work to complete.
2: APPS THAT USE THE DATA ACCESS OBJECT WILL HAVE A DB.CONFIG FILE
The db.config files are created at design time, and are simple XML files with encoded connection strings for nodes.
For our case, we needed 3 connection strings, one for each environment (dev/test/prod).
<![CDATA[<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<key environment="DEV">encrypted development db connection string</key>
<key environment="TEST"> encrypted test db connection string </key>
<key environment="PROD"> encrypted production db connection string </key>
</configuration>]]>
Inside the application associated with this db.config is the actual decryption key. Since our clients dont have the source code, this isn't a problem. The application calls the Data Access Object sending the encryption key & environment as a paramater.
This opens up the database connection, runs a stored procedure with x amount of paramaters and returns a dataset that is all nice and happy.
Here is a word document with an example of how to actually use the DataAccessObject in a practical manner.
Example Code
I have included a variable named ENCRYPTIONOBJECT. This was an internally & custom built encryption object that I am not including with this code. Any simple encryption object can be used, and it's fairly easy to see how its being used.
I do have a 'hidden' Sub at the bottom that will allow SQL Statements, but it hasnt
been completed. If you want, you can try and get it to go.
SOURCE CODE WITH HEAVY COMMENTING
<![CDATA[THESE ARE THE MINIMUM REFERENCES NEEDED FOR DataAccessObject (DAO)
Imports System
Imports System.Data
Imports System.Data.OracleClient
Imports System.Xml
Imports System.Xml.XPath
Imports ENCRYPTIONOBJECT
THIS IS THE BASE CLASS THAT CONNECTS/DISCONNECTS/AND RUNS STORED PROCEDURES
******************************
Public Class ConnectToDatabase
******************************
GLOBAL BOOLEAN THAT RETURNS "TRUE" IF DB CONNECTION IS MADE, AND "FALSE" IF NOT (DIS)CONNECTED
Public m_booDatabaseConnectionStatus As Boolean
GLOBAL DATASET OBJECT THAT HOLDS DATA RETURNED FROM STORED PROCEDURES (System.Data.OracleClient.dll)
Public m_objOracleDataSet As New DataSet
ORACLE DATABASE CONNECTION OBJECT CREATED AND USED THROUGHOUT THE DAO
Private g_objOracleDBConnection As New OracleClient.OracleConnection
CUSTOM ERROR HANDLER
Dim errorHandler As New DAOErrorManager
CREATES DATABASE CONNECTION USING TWO PARAMETERS BEING PASSED IN VIA THE HOST APPLICATION:
1: DataBaseEnvironment (DEV/TEST/PROD) - SPECIFIES WHICH XML NODE TO GET ENCRYPTED STRING FROM
2: EncryptionKey - UNENCRYPTED STRING 'PASSWORD' USED TO DECRYPT THE DB CONNECTION STRING IN THE XML NODE
*******************************************************************************************************
Public Sub CreateDataBaseConnection(ByVal DataBaseEnvironment As String, ByVal EncryptionKey As String)
*******************************************************************************************************
Try
OBJECTS NEEDED TO CREATE AND PARSE THE db.config XML FILE
Dim objXMLDocument As XmlDocument
Dim objXMLNodeList As XmlNodeList
Dim objXMLNode As XmlNode
CREATE ENCRYPTIONOBJECT (DECRYPTION/ENCRYPTION) - EncryptionKey IS PASSED IN TO ALLOW DECRYPTION
Dim objENCRYPTIONOBJECT As New ENCRYPTIONOBJECT(EncryptionKey)
'CREATE XML DOCUMENT
objXMLDocument = New XmlDocument
Try
LOAD db.config INTO XML DOCUMENT
objXMLDocument.Load("db.config")
Catch
IF ERROR OCCURS, LOG ERROR DETAILS
errorHandler.PublishError("File Not Loaded: 'db.config.' File may be missing.", "Config File Not Loaded",True, "DataAccessObject_CreateDataBaseConnection", "Failure")
End Try
Try
LOAD db.config NODES INTO AN XML NODE LIST FOR PARSING
objXMLNodeList = objXMLDocument.SelectNodes("/configuration/key")
Catch
IF ERROR OCCURS, LOG ERROR DETAILS
errorHandler.PublishError("XML File: 'db.config' Not Formatted Correctly: '/configuration/key'", "XMLStructure Error", True, "DataAccessObject_CreateDataBaseConnection", "Failure")
End Try
Try
PARSE EACH NODE IN THE XML NODE LIST (EACH NODE IN db.config)
For Each objXMLNode In objXMLNodeList
IF THE NODES 'ENV' ATTRIBUTE = THE DataBaseEvnironment PARAMETER (UPPERCASE)
If objXMLNode.Attributes.GetNamedItem("environment").Value.ToString().ToUpper() = DataBaseEnvironment.ToUpper() Then
1: DECRYPT THE VALUE OF THE NODE SET IT AS THE DB CONNECTION OBJECTS CONNECTION STRING
g_objOracleDBConnection.ConnectionString = objENCRYPTIONOBJECT.decrypt(objXMLNode.InnerXml.ToString())
EXIT THE NODE PARSING LOOP - NO NEED TO EVALUATE EXTRA NODES
Exit For
End If
THE NODES 'ENV' VARIABLE DOES NOT MATCH THE DataBaseEvnironment PARAMETER - PROCEED TO NEXT NODE
Next
Catch
IF ERROR OCCURS, LOG ERROR DETAILS
errorHandler.PublishError("Error Decrypting Connection String. Incorrect Environment or Decryption Key Given.", "Decryption Error", True, "DataAccessObject_CreateDataBaseConnection", "Failure")
End Try
Try
OPEN ORACLE DATABASE CONNECTION WITH ORCACLE DB CONNECTION OBJECT
g_objOracleDBConnection.Open()
SET PUBLIC BOOLEAN TO TRUE (INDICATING OPEN DB CONNECTION) FOR USE IN REFERENCING APPLICATION
m_booDatabaseConnectionStatus = True
Catch
IF ERROR OCCURS, LOG ERROR DETAILS
errorHandler.PublishError("Error Connecting To Database", "Database Connection Error", True, "DataAccessObject_CreateDataBaseConnection", "Failure")
End Try
Catch
IF ERROR OCCURS, LOG ERROR DETAILS
errorHandler.PublishError("Error Creating Database Connection.", "Applicaion Error", True, "DataAccessObject_CreateDataBaseConnection", "Failure")
End Try
End Sub
CLOSES DATABASE CONNECTION:
************************************
Public Sub CloseDataBaseConnection()
************************************
Try
CLOSE ORACLE DATABASE CONNECTION WITH ORCACLE DB CONNECTION OBJECT
g_objOracleDBConnection.Close()
SET PUBLIC BOOLEAN TO FALSE (INDICATING CLOSED DB CONNECTION) FOR USE IN REFERENCING APPLICATION
m_booDatabaseConnectionStatus = False
Catch
IF ERROR OCCURS, LOG ERROR DETAILS
errorHandler.PublishError("Error Disconnecting From Database.", "Database Disconnection Error.", True, "DataAccessObject_CloseDataBaseConnection", "Failure")
End Try
End Sub
EXECUTES STORED PROCEDURE ON ORACLE DATABASE USING ONE PARAMETER BEING PASSED IN VIA THE HOST APPLICATION:
1: parOracleCommand (1 TO X PARAMATERS) - THIS IS A .NET ORACLECOMMAND OBJECT
*************************************************************************
Public Sub OracleStoredProcedure(ByVal parOracleCommand As OracleCommand)
*************************************************************************
Try
CONNECT THE COMMAND OBJECT TO THE GLOBAL CONNECTION OBJECT
parOracleCommand.Connection = g_objOracleDBConnection
Catch
IF ERROR OCCURS, LOG ERROR DETAILS
errorHandler.PublishError("Database Conenction Does Not Exist", "Error Calling Stored Procedure", True, "DataAccessObject_OracleStoredProcedure", "Failure")
End Try
CREATE DATAADAPTER OBJECT AND ASSOCIATE IT TO THE COMMAND OBJECT
Dim objOracleDataAdapter As New OracleDataAdapter(parOracleCommand)
Try
USE DATAADAPTER TO RUN COMMAND OBJECT (STOREDPROCEDURE) AND POPULATE THE GLOBAL DATASET
objOracleDataAdapter.Fill(m_objOracleDataSet)
Catch ex As Exception
IF ERROR OCCURS, LOG ERROR DETAILS
errorHandler.PublishError("Error Filling DataAdapter With Returned DataSet", "Error Filling DataAdapter", True, "DataAccessObject_OracleStoredProcedure", "Failure")
End Try
End Sub
EXECUTES SQL STATEMENT ON THE ORACLE DATABASE - HERE FOR BACKUP & REFERENCE ONLY
PLEASE TRY AND PRETEND IT DOESN'T EXIST.
THE IDEA IS TO FORCE APPLICATIONS TO USE STOREDPROCEDURES ON THE DATABASE AND
NOT HARDCODE SQL IN APPLICATIONS!!!!!
*****************************************************************
Public Sub OracleSelectStatement(ByVal parSQLStatement As String)
*****************************************************************
Dim objOracleCommand As New OracleCommand
objOracleCommand.CommandText = parSQLStatement
objOracleCommand.Connection = g_objOracleDBConnection
Dim objOracleDataAdapter As New OracleDataAdapter(objOracleCommand)
Try
objOracleDataAdapter.Fill(m_objOracleDataSet)
Catch ex As Exception
Trace.Write(ex)
End Try
End Sub
End Class]]>
SIMPLY SOURCE CODE AND BASIC COMMENTING
<![CDATA[Imports System
Imports System.Data
Imports System.Data.OracleClient
Imports System.Xml
Imports System.Xml.XPath
Imports ENCRYPTIONOBJECT
Public Class ConnectToDatabase
Public m_booDatabaseConnectionStatus As Boolean 'DB Connection Status : BOOLEAN
Public m_objOracleDataSet As New DataSet 'DB DataSet to Be Retured : DATASET
Private g_objOracleDBConnection As New OracleClient.OracleConnection 'Oracle DB Connection Object : (System.Data.OracleClient.dll)
Public Sub CreateDataBaseConnection(ByVal DataBaseEnvironment As String, ByVal EncryptionKey As String)
Dim objXMLDocument As XmlDocument
Dim objXMLNodeList As XmlNodeList
Dim objXMLNode As XmlNode
'Create ENCRYPTIONOBJECT - Decryption/Encryption Key is set at this time also
Dim objENCRYPTIONOBJECT As New ENCRYPTIONOBJECT(EncryptionKey)
'Create XML Document & Node List
objXMLDocument = New XmlDocument
'Load db.config XML data
objXMLDocument.Load("db.config")
objXMLNodeList = objXMLDocument.SelectNodes("/configuration/key")
'1: Parse XML Document, looking for a match in the "environment" attribute of the node (Uppercases both strings for comparison)
'2: Returns the innerXML of the node (this is the encrypted connection string)
'3: The connection string is decrypted by the ENCRYPTIONOBJECT object (using DecryptionKey), and sets it as the OracleConnection Object's ConnectionString
For Each objXMLNode In objXMLNodeList
If objXMLNode.Attributes.GetNamedItem("environment").Value.ToString().ToUpper() = DataBaseEnvironment.ToUpper() Then
g_objOracleDBConnection.ConnectionString = objBlowfishEncryption.Decrypt(objXMLNode.InnerXml.ToString())
Exit For
End If
Next
'Attempts to open Oracle Database Connection
g_objOracleDBConnection.Open()
'Sets Public Boolean to "True" (indicating an open connection) for use in referencing application
m_booDatabaseConnectionStatus = True
End Sub
Public Sub CloseDataBaseConnection()
'Attempts to close Oracle Database Connection
g_objOracleDBConnection.Close()
'Sets Public Boolean to "False" (indicating a closed connection) for use in referencing application
m_booDatabaseConnectionStatus = False
End Sub
Public Sub OracleStoredProcedure(ByVal parOracleCommand As OracleCommand)
'Connect the Command Object to the Connection Object
parOracleCommand.Connection = g_objOracleDBConnection
'Create DataAdapter Object and associate it to the Command Object.
Dim objOracleDataAdapter As New OracleDataAdapter(parOracleCommand)
'Use DataAdapter to run Command Object (StoredProcedure) and populate the global DataSet
objOracleDataAdapter.Fill(m_objOracleDataSet)
End Sub
'NOT READY FOR DEPLOYEMENT
'Public Sub OracleSelectStatement(ByVal parSQLStatement As String)
' Dim objOracleCommand As New OracleCommand
' objOracleCommand.CommandText = parSQLStatement
' objOracleCommand.Connection = g_objOracleDBConnection
' Dim objOracleDataAdapter As New OracleDataAdapter(objOracleCommand)
' objOracleDataAdapter.Fill(m_objOracleDataSet)
'End Sub
End Class]]>
This article is ©2008 by the respective authors. Reproduction is prohibited without express permission from all contributors.