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

Using [this little trick described here](http://stackoverflow.com/a/10480011/205233) should help you create at least some of the complete connections strings for providers installed on your machine. You could also check the machine config - it contains all providers DbCommon can target. I know it's not a complete solution so I only comment, not answer. — Filburt, May 07 '17 at 21:25

hardkoded · Answer 1 · 2017-05-08T13:14:16.043

2

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

edited May 08 '17 at 13:14

answered May 07 '17 at 21:41

hardkoded

18,915
3
52
64

thanks, i now can get the names of the providers :) . How can i add new providers (mysql, postgreSQL or oracle). I install Mysql.data.MySqlClient dll (With nuget) but i cannot see the provider :( – Eduardo Daniel Morón Arce May 07 '17 at 21:59
@EduardoDanielMorónArce You may fall for 64 vs 32 bit pitfall - if you only install the 32 bit driver, your 64 bit application cannot "see" it (and vice versa). As mentioned in my other comment, check your machine.config to see registered providers DbCommon will be able to use. – Filburt May 07 '17 at 22:12
@EduardoDanielMorónArce I've installed MySQL and Oracle packages and I got them in my list :/ – hardkoded May 08 '17 at 00:03
@kblok Could you share the links please? :) – Eduardo Daniel Morón Arce May 08 '17 at 01:07

score 1 · Answer 2 · answered Nov 13 '17 at 12:08

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.

C# - List of Database connectionsStrings Provider

2 Answers2