MySQL DataCOnnection and ADO.NET

Question

I want to use mysqlconnection, command and datareader or sqlconnection, command and datareader based on a condition. I want to use same variables for both type of connection

Ex:

Currently i have
dim _c as New mySqlConnection()
dim _cmd as New mysqlCommand()
dim _reader as New mysqlreader()

_reader = _cmd.executereader()
'loop through _reader
 -----------------

How can i change first three to use sql/mysql based on select case. Is this possible?

Answer 1

Write a method that returns the datareader. Place your select case in the method, regardless of the conditions you use to build the query, the data reader will be returned.

Make sure you close the data reader when you are done.

Answer 2

Yes, it is possible. ADO.NET has a provider API for that. You have to do something like this:

string providerName;

if (true/*add your logic here*/)
   providerName = "System.Data.SqlClient";
else
   providerName = "MySql.Data.MySqlClient"

DbProviderFactory factory = DbProviderFactories.GetFactory(providerName);

DbConnection connection = factory.CreateConnection();
DbCommand command = factory.CreateCommand();

// ... and so on