Generic Database Connection C#

Question

Im working on this application that will transfer data from a database another application. But I am trying to get the database functionality working.

I need to be able to connect any type of database (Oracle, SQL, etc...) to the app because my user base uses different databases.

I was following the tutorial on http://www.splinter.com.au/using-generic-database-providers-with-c/ and I'm having trouble connecting my test MySQL database to it.

Here is my sample code:

static void Main(string[] args)
{
    string connection = "SERVER=localhost;UID=root;PASSWORD=toor";
    string provider = "MySql.Data.MySqlClient";

    using (DbConnection conn = (DbConnection)Activator.CreateInstance(Type.GetType(provider), connection))
    {
        conn.Open();
        string sql = "SHOW DATABASES";
        using(DbCommand comm = conn.CreateCommand())
        {
            comm.CommandText = sql;
            using(DbDataReader reader = comm.ExecuteReader())
            {
                while(reader.Read())
                {
                    string owner = reader.GetString(0);
                    Console.WriteLine("{0}", owner);
                }
            }
        }
    }
    Console.ReadLine();
}

The error I'm getting is ArgumentNullException and I can see that in the debugger that conn is null. I'm not sure why it's not working though. I've used that connection string and provider in an app that uses the MySQL library. Could it be that my connection string needs to be changed? If so, what do I change it to?

Also, I don't want to add a Database to the connection string, that is chosen later.

C.Evenhuis · Accepted Answer · 2014-02-11 15:09:38Z

3

I guess Type.GetType(provider) returns null because you're specifying the type's Fullname

"MySql.Data.MySqlClient"

instead of its AssemblyQualifiedName

"MySql.Data.MySqlClient, MySql.Data"

Specifying a type name without an assembly name only works if the type is in the currently executing assembly or mscorlib.

If you are trying to load the assembly from the GAC you'll have to include the Version, Culture and PublicKeyToken parameters too, ie:

"MySql.Data.MySqlClient.MySqlConnection, MySql.Data, Version=6.5.4.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d"

This depends on the version of the driver.

edited Feb 11, 2014 at 15:09

answered Feb 11, 2014 at 15:03

C.Evenhuis

26.4k2 gold badges60 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

user Over a year ago

I tried that and I still get ArgumentNullException. But you are correct, the Type is null.

C.Evenhuis Over a year ago

Did you place the MySql.Data.dll in the same folder as the executable? If you are trying to load it from the GAC you'll even have to be more specific, complete with Version, Culture and PublicKeyToken.

user Over a year ago

Oh, I didn't know you had to do that. Does that mean I'll need to place dlls for every database the user might use? I suppose I could try to restrict it to just a few types.

C.Evenhuis Over a year ago

@user yes, I'm afraid so. You could switch to ODBC, in which case the responsibility of finding the correct drivers shifts towards the system admin :) (ODBC is slower, and requires you to change all your queries, though)

user Over a year ago

It's still giving me that error, even with the .dll in the bin\Debug folder.

|

Collectives™ on Stack Overflow

Generic Database Connection C#

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related