Using generic database providers with C#

This snippet of code shows how to use the generic System.Data.Common types to connect to any database, using a string to define which database provider you want to use. The magic happens with the 'Activator.CreateInstance' below, where C# has the ability to create a class from a string. This example below is Oracle centric, but to change it to work with Sql server you'd simply change the connection string and the provider, which are both strings hence easily configurable in a text file for your application!

using System;
using System.Data.Common;

namespace OdpTest
{
  class Program
  {
    static void Main(string[] args)
    {
      string connectionString =
        "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)" +
        "(HOST=MYHOST)(PORT=1527))(CONNECT_DATA=(SID=MYSERVICE)));" + 
        "User Id=MYUSER;Password=MYPASS;";
      string provider =
        "Oracle.DataAccess.Client.OracleConnection, Oracle.DataAccess";

      using (DbConnection conn = (DbConnection)Activator.
        CreateInstance(Type.GetType(provider), connectionString))
      {
        conn.Open();
        string sql =
          "select distinct owner from sys.all_objects order by owner";
        using (DbCommand comm = conn.CreateCommand())
        {
          comm.CommandText = sql;
          using (DbDataReader rdr = comm.ExecuteReader())
          {
            while (rdr.Read())
            {
              string owner = rdr.GetString(0);
              Console.WriteLine("{0}", owner);
            }
          }
        }
      }
    }
  }
}

Chris Hulbert

(Comp Sci, Hons - UTS)

Software Developer (Freelancer / Contractor) in Australia.

I have worked at places such as Google, Cochlear, Assembly Payments, News Corp, Fox Sports, NineMSN, FetchTV, Coles, Woolworths, Trust Bank, and Westpac, among others. If you're looking for help developing an iOS app, drop me a line!

Get in touch:
[email protected]
github.com/chrishulbert
linkedin

---

Please subscribe to my mailing list:

 Subscribe via RSS