Getting data out of an Oracle database and into your VB.NET program!

The client has data, it's in a database, and he wants it presented in a nicer format. Sounds familiar? It should, because that's 80% of what you're going to run into in your life as a developer! So, how the heck do we get data from there to here?

So here's what you want to import:

Imports Microsoft.VisualBasic
Imports Oracle.DataAccess.Client


Then you want to declare a connection string:

Dim connectionString As String = "User Id=userIDHere;Password=passwordHere;Data Source=schemaNameHere;"

Then, open an Oracle connection using your connection string as a parameter:

Dim conn As New OracleConnection(connectionString)
conn.Open()

Now that you have a connection open, you can send it a request. Create a query string and then an OracleCommand object.

Dim query As String = "select * from tableName where condition = value"
Dim command as OracleCommand = New OracleCommand(query)
command.Connection = conn
command.CommandType = CommandType.Text

Don't forget that when you're going to access the database, it may throw an exception at you. So give it the ole college TRY:



Try
     Dim reader as OracleDataReader = command.ExecuteReader()
     While (reader.Read())
          Dim stringValue as string = CType(reader.GetValue(0), String)
          Dim intValue as Integer = CType(reader.GetValue(1), Integer)
     End While
Catch ex as Exception
     'Send a message to the user
Finally
     ' Dispose OracleCommand object
     command.Dispose()
     ' Close and Dispose OracleConnection object
     conn.Close()
     conn.Dispose()
End Try

So there's a quick overview of opening an Oracle database and retrieving some database from it. One more note, if you want to insert, update, or delete from the database, use the ExecuteNonQuery() command in lieu of the ExecuteReader() command, as such:

Dim status As Integer = cmd.ExecuteNonQuery()


So there you have it, opening an Oracle database from VB.NET. Questions? Comments? Leave them below and I'll get back to you as soon as possible.