VB - Connecting to an Oracle Database without using a TNS Entry

Introduction

Connecting to an Oracle database isn't too much of a problem, there's examples everywhere on the web along the following lines..

Dim conn As New OracleConnection()
Dim connstr as String, dataSource as String, userId as String, password as String
dataSource = "dev10g"
userId = "jsmith"
password = "letmein"
connstr = "Data Source=" + dataSource + "; User Id=" + userId + "; Password=" + password + ";"
conn.ConnectionString = connstr
Try
  conn.Open()
Catch ex As Exception
  ' Database connection failed
  conn.Dispose() 'Dispose of the connection
  Exit Sub
End Try

This works ok, but it's not very portable unless everyone that wants to use it sets themselves a TNS entry called "dev10g" in their tnsnames.ora file.

What if we wanted to define the host and port number in the config, and then connect without using TNS?

Connecting to Oracle Directly

In practice all we need to do is alter the connection string to provide the information that the tnsnames.ora would have sent. So the code above doesnt change much.

Dim conn As New OracleConnection()
Dim connstr As String
Dim dbServer As String, dbPort As String, dbServiceName As String
Dim userId As String, password As String

dbServer = "lordv01"
dbPort = "1521"
dbServiceName = "dev10g"
userId = "jsmith"
password = "letmein"
dconnstr = "Data Source=" + dbServer + ":" + dbPort + "/" + dbServiceName + ";User ID=" + userId + ";Password=" + password
conn.ConnectionString = connstr
Try
  conn.Open()
  Catch ex As OracleException
  conn.Dispose() 'Dispose of the connection
  Exit Sub
End Try

It took me a bit of poking around to find the right syntax, so hopefully someone will find this useful.