What would be the most convenient way to connect Visual Studio 2005 (C#) to Oracle8?

Question

I'm looking for best practices for establishing connections between Oracle 8 and Visual Studio 2005 applications. The target would be a Windows Forms application written in C# that hits the database once a second to monitor tables looking for their last inserted record. I'm considering using "Application settings" to store the connection string there, but I'd love to hear from you guys. Thanks in advance!

This is a very rudimentary draft:

using System.Data;
using System.Data.OracleClient;

        try
        {
            StringBuilder str = new StringBuilder();
            string ora = Properties.Settings.Default.OracleConnectionString;

            OracleConnection con = new OracleConnection(ora);
            OracleCommand cmd = new OracleCommand();

            cmd.Connection = con;
            cmd.CommandText = "SELECT timestamp FROM jde_out WHERE rownum = 1";
            cmd.CommandType = CommandType.Text;

            con.Open();
            OracleDataReader rdr = cmd.ExecuteReader();
            rdr.Read();

            str.AppendLine(cmd.ExecuteScalar().ToString());
            this.lblJDEtime.Text = str.ToString();
            rdr.Close();
            con.Close();
        }
        catch (OracleException err)
        {
            MessageBox.Show("Exception caught:\n\n" + err.ToString());
        }

I've just updated the code needed to perform the connection. Changed the Exception type to the more specific OracleException. Added the connection string via Properties.Settings.

Answer 1

Based on my experience with Oracle 10g....

I recommend using the Oracle data provider (ODP.Net) and not using the Microsoft for Oracle data provider based on my experience with Oracle 10g. Microsoft's has not been updated in years and does not support everything in Oracle 10g, so I would definitely check into that for Oracle 8.

Following Microsoft guidance on connection string in the app.config file, you should store it like:

<?xml version='1.0' encoding='utf-8'?>
  <configuration>
    <connectionStrings>
      <clear />
      <add name="Name" 
       providerName="System.Data.ProviderName" 
       connectionString="Valid Connection String;" />
    </connectionStrings>
  </configuration>

I've also worked on apps with the connection information stored in application settings, which worked fine for our application.

Answer 2

I would warn against building an architecture like you're suggesting. Opening a new connection to the database to poll for changes every second is an expensive operation. It gets worse if you're trying to scale this Windows Form application beyond a few users.

Instead, you should look into the built-in Oracle packages that can support monitoring such activity.

EDIT: "WHERE ROWNUM=1" from your example isn't useful here. Oracle isn't guaranteed to order the results in any specific way according to your query.

Your issue is really an architectural problem. Polling database tables as you suggest is NOT a good way to do application health monitoring. At a bare minimum, you're better off having a component in the middle tier, or even the database, periodically write to a log file, Windows Event Log, table, etc. that essentially says "I'm still listening" and only poll THAT singular source.

Answer 3

Here's a sample from our environment:

<add key="ODP.NET.ConnectionString" value="Password=abcdefg;Persist Security Info=True;User ID=abc123;Data Source=blah;"/>

Where the username and password are set appropriately, and the Data Source is the entry from Oracle's TNSNAMES.ORA we're connecting to.

Answer 4

ConnectionStrings.com has pretty much every type of connection string you'll ever want including Oracle.

Answer 5

@Loscas AFAIK ODP.NET data provider is supported only on Oracle 9i up. MS Oracle data provider works on 8.1.7 and up.

@BQ

Opening a new connection to the database to poll for changes every second is an expensive operation.

Actually the connection is not opened on con.Open(). It is just taken from the pool of open connections. con.Close() returns the connection to the pool. So, there is really nothing wrong with this connection handling.

Answer 6

If you want to monitor tables, have you consider using triggers? An example might be:

• Your application listens on a TCP port
• You install a trigger on an Oracle table
• The trigger calls some Oracle Java code that ...
• ... sends a simple message to your app's TCP port
• The message could just be a notification and you could then read from the table. Alternatively, you could send the entire SQL that affected the table as the message payload.

This might work better.

Answer 7

Regarding the connection string, since you are using System.Data.OracleClient, I would also recommend formatting the Oracle connection string to make it independent of tnsname.ora:

string connectionString = @"
SERVER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP
(HOST=OracleHost)(PORT=OraclePort))
(CONNECT_DATA=(SERVICE_NAME=OracleServiceName)))
;uid=UserID;pwd=Password;";

You can store it in your App.config file like so:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <appSettings>
  <add key="My.Database.Connection" value="SERVER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP
    (HOST=OracleHost)(PORT=OraclePort))
    (CONNECT_DATA=(SERVICE_NAME=OracleServiceName)))
    ;uid=UserID;pwd=Password;" />
  </appSettings>
</configuration>

and then access it with:

connectionString = ConfigurationSettings.AppSettings["My.Database.Connection"];

FYI, here is a blog post by me on this subject: Quick and dirty connection to Oracle using ADO.NET