I have a DLL to establish an Oracle connection, how do I execute a query against that connection in my referencing code?

Question

I'm writing a DLL to handle Oracle database connections. We have several databases all with unique passwords. I want to create this DLL to generally make managing those connections much easier.

I have written the DLL as so:

using System;
using Oracle.DataAccess.Client;

namespace Sabs.DLLs.DBConnectLib
{

    public class DbConnectLib:IDisposable
    {
        public string ConString { get; set; }
        public OracleConnection OraConn { get; set; }

        /// <summary>
        /// Constructor sets the connection string and opens the database connection.
        /// </summary>
        /// <param name="databaseName">The name of the database to connect to.</param>
        public DbConnectLib(string databaseName)
        {
            // Get the connection string
            IConnectionString cs = new ConnectionString();
            ConString = cs.GetConnectionString(databaseName);

            // Open the database connection.
            OraConn = new OracleConnection(ConString);
            try
            {
                OraConn.Open();
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        /// <summary>
        /// Closes the database connection.
        /// </summary>
        public void Dispose()
        {
            try
            {
                OraConn.Close();
            }
            catch (Exception e)
            {
                throw e;
            }
        }
    }
}

Nice and simple, it just gets the connection string and opens the connection in the constructor. I used IDisposable to close the connection so that I can leverage using statements.

Now I'm trying to execute a query in the code that references this DLL, like so:

using System;
using Oracle.DataAccess.Client;
using Sabs.DLLs.DBConnectLib;

namespace DBConnectLib_Tester
{
    class Program
    {

        static void Main(string[] args)
        {
            using (var dbConn = new DbConnectLib("pamp"))
            {
                const string SQL = "select distinct owner from sys.all_objects order by owner";

                var cmd = new OracleCommand {Connection = dbConn};
                cmd.CommandText = SQL;

                using (OracleDataReader rdr = cmd.ExecuteReader())
                {
                    while (rdr.Read())
                    {
                        Console.WriteLine(rdr.GetString(0));
                    }
                }

            }
        }

    }
}

This line var cmd = new OracleCommand {Connection = dbConn}; gives this error "Cannot implicitly convert type 'Sabs.DLLs.DBConnectLib.DbConnectLib' to 'Oracle.DataAccess.Client.OracleConnection'" which completely makes sense.

My question is, can I make this work? Is there a different approach I should take? Hopefully I am just missing something obvious, because I like how simplistic this approach is.

Answer 1

You are trying to reference the entire lib object when you need to point to the property that returns the oracle connection object. This should fix it.

var cmd = new OracleCommand { Connection = dbConn.OraConn};