C# - List of Database connectionsStrings Provider

Question

Im trying to develop a dotnet application with multiple database providers and i need to know the ConnectionString and Provider of the most used databases. Im i using System.DBCommon. This is my code:

public  class DBConnector
{

  public void ConectDatabase()
  {
      {
          string connectionString =
            "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)" +
            "(HOST=MYHOST)(PORT=1527))(CONNECT_DATA=(SID=MYSERVICE)));" +
            "User Id=MYUSER;Password=MYPASS;"; //Connection String
          string provider =
            "Oracle.DataAccess.Client.OracleConnection, Oracle.DataAccess"; //I need this  for the most used databases (Mysql, PostgreSQL, SqlServer)


          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);
                      }
                  }
              }
          }
      }
  }

I found the connectionstrings in this site https://www.connectionstrings.com/

But i need the provider too. Thanks

Answer 1

The provider name in the connection string attribute is not a class but a Namespace, e.g. System.Data.SqlClient is not a class but a namespace, under that namespace you have SqlConnection, SqlCommand etc.

You could try looking for all the classes implementing the IDbConnection interface and then create an IDbConnection based on that Type:

var types = AppDomain.CurrentDomain.GetAssemblies()
    .SelectMany(s => s.GetTypes())
    .Where(p => typeof(IDbConnection).IsAssignableFrom(p) && p.IsClass);

foreach(var dbConnection in types)
{
    Console.WriteLine(dbConnection);
}

After installing MySQL and Oracle packages this was the resulting list

• System.Data.SqlClient.SqlConnection
• System.Data.OleDb.OleDbConnection
• System.Data.Odbc.OdbcConnection
• System.Data.Common.DbConnection
• MySql.Data.MySqlClient.MySqlConnection
• Oracle.ManagedDataAccess.Client.OracleConnection

You can check the source code here https://github.com/kblok/StackOverflowExamples/blob/master/AspNetDemoProject/AspNetDemoProject/Demos/ProvidersList.aspx.cs

Answer 2

You can list the registered db providers like this:

using System;
using System.Data;
using System.Data.Common;

namespace StackOverflowExamples
{
    class AvailableDataProviders
    {
        public static void Main(string[] args)
        {
            using (DataTable providers = DbProviderFactories.GetFactoryClasses())
            {
                Console.WriteLine("Available Data Providers:");

                foreach (DataRow provider in providers.Rows)
                {
                    Console.WriteLine();
                    Console.WriteLine("Name: {0}", provider["Name"]);
                    Console.WriteLine("Description: {0}", provider["Description"]);
                    Console.WriteLine("Invariant Name: {0}", provider["InvariantName"]);
                    Console.WriteLine("AssemblyQualifiedName: {0}", provider["AssemblyQualifiedName"]);
                }
            }
        }
    }
}

There can be provider classes in your application's assemblies that are not registered by your application (in app.config or web.config) or by your machine (in machine.config) or by framework.

Your application can only use the providers that are registered in system.data -> DbProviderFactories of your application config.