Overload Database Connection Class?

I use the class below in my code-behind to connect to my database. How would I (overload?) this class to choose between multiple connection strings (databases)?

Or should I have a separate class for each connection?

public class DataClass
{
  SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["DatabaseName"].ToString());

  public DataClass()
  {

  }

  public DataSet ds(SqlCommand cmd)
  {
    cmd.Connection = cn;
    SqlDataAdapter da = new SqlDataAdapter(cmd);
    DataSet ds = new DataSet();
    da.Fill(ds);
    return ds;
  }

// etc

Thank you for your inpu开发者_StackOverflow中文版t.

---

Make use of the DbProviderFactory, and get the provider strings from your web.config.

Been a while since i"ve used them (I use EF or NHibernate most of the time now) , but iirc it's something like this:

string providerName = ConfigurationManager.ConnectionStrings["myconnectionname"].ProviderName;
DbProviderFactory provider =
    DbProviderFactories.GetFactory(providerName);

IDbConnection conn = provider.CreateConnection();
IDbCommand command = provider.CreateCommand();

etc.

---

You need to use DbProviderFactory and DbConnection, have a look into

---

This sounds like a dependency your DataClass should take on an IDbConnection

Add an IDbConnection parameter to the constructor of your data class, and just pass in the appropriate connection object for the database that your method should access.

Your new code may look like:

public class DataClass {

        private IDbConnection cn;

        public DataClass() 
           : this(new SqlConnection(ConfigurationManager.ConnectionStrings["DatabaseName"].ToString()) {}

        public DataClass(IDbConnection conn) {
           cn = conn;
        }


        public DataSet ds(SqlCommand cmd)
        {
        cmd.Connection = cn;
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        da.Fill(ds);
        return ds;
        }
     }

---

You can create DataFactory class, where you would have enum DataStores and GetConenctionString Method. I am using Enterprice library to connect to database.

 public class DataFactory
{
    public enum DataStores
    { 
        ReadOnly=1,
        ReadWrite=2,
        ReadWriteProTest=3

    }
    public static string GetConnectionString(DataStores dataStore)
    {
        Database currentDatabase = null;
        switch (dataStore)
        {
            case (DataStores.ReadOnly):
                currentDatabase = DatabaseFactory.CreateDatabase("ReadOnlyDB");
                break;
            case (DataStores.ReadWrite):
                currentDatabase = DatabaseFactory.CreateDatabase("ReadWriteDB");
                break;
            case (DataStores.ReadWriteProTest):
                currentDatabase = DatabaseFactory.CreateDatabase("ReadWriteProdTest");
                break;
            default:
                currentDatabase = DatabaseFactory.CreateDatabase("ReadOnlyDB");
                break;

        }

        return currentDatabase.ConnectionString;
    }
}

In your Web.Config you specify values for different connection strings:

<connectionStrings>
    <add name="ReadWriteDB"  connectionString="Your connection string"/>
              <add name="ReadWriteProdTest" connectionString="Your connection string"/>
              <add name="ReadOnlyDB" connectionString="Your connection string"/>

---

I did this for little thing where I work, they are still using ADO.Net and we basically want to remove it but we are slowly converting the app and as a small workaround to make things smoother while abstracting it away (I realize it isn't very DRY but it does it's job very well for the time being). ConnectionConstant is an enum of the possible connection strings and the ConnectionstringManager gets the appropriate connectionstring using a switch case

using System;
using System.Collections.Generic;
using System.Data;
using System.Web;
using System.Data.SqlClient;

namespace xxx.Data    {
    public static class SqlConnectionManager
    {
        public static int ExecuteNonQuery(ConnectionConstant connectionConstant, string sql, params SqlParam[] sqlParameters)
        {
            try
            {
                var connectionString = ConnectionStringManager.GetConnectionString(connectionConstant);

                int result;
            using (var sqlConnection = new SqlConnection(connectionString))
            {
                using (var sqlCommand = new SqlCommand())
                {
                    sqlCommand.Connection = sqlConnection;
                    sqlCommand.CommandText = sql;
                    foreach (var parameter in sqlParameters)
                    {
                        sqlCommand.Parameters.Add(parameter.ParameterName, parameter.SqlDbType).Value = (parameter.Value).ToString() == "" ? DBNull.Value : parameter.Value;
                    }

                    sqlConnection.Open();

                    result = sqlCommand.ExecuteNonQuery();
                }
            }

            return result;
        }
        catch (SqlException sqlException)
        {
            throw;
        }
        catch (Exception ex)
        {
            throw;
        }
    }

    public static DataTable ExecuteReader(ConnectionConstant connectionConstant, string sql, params SqlParam[] sqlParameters)
    {
        try
        {
            var connectionString = ConnectionStringManager.GetConnectionString(connectionConstant);

            var result = new DataTable();
            using (var sqlConnection = new SqlConnection(connectionString))
            {
                using (var sqlCommand = new SqlCommand())
                {
                    sqlCommand.Connection = sqlConnection;
                    sqlCommand.CommandText = sql;
                    foreach (var parameter in sqlParameters)
                    {
                        sqlCommand.Parameters.Add(parameter.ParameterName, parameter.SqlDbType).Value = (parameter.Value).ToString() == "" ? DBNull.Value : parameter.Value;
                    }

                    sqlConnection.Open();

                    using(var dataReader = sqlCommand.ExecuteReader(CommandBehavior.CloseConnection))
                    {
                        result.Load(dataReader);
                    }
                }
            }
            return result;
        }
        catch (SqlException sqlException)
        {
            throw;
        }
        catch (Exception ex)
        {
            throw;
        }
    }

    public static string ExecuteScalar(ConnectionConstant connectionConstant, string sql, params  SqlParam[] sqlParameters)
    {
        try
        {
            var connectionString = ConnectionStringManager.GetConnectionString(connectionConstant);

            object result = null;
            using (var sqlConnection = new SqlConnection(connectionString))
            {
                using (var sqlCommand = new SqlCommand())
                {
                    sqlCommand.Connection = sqlConnection;
                    sqlCommand.CommandText = sql;
                    foreach (var parameter in sqlParameters)
                    {
                        sqlCommand.Parameters.Add(parameter.ParameterName, parameter.SqlDbType).Value = (parameter.Value).ToString() == "" ? DBNull.Value : parameter.Value;
                    }

                    sqlConnection.Open();

                    result = sqlCommand.ExecuteScalar();
                }
            }
            if (result != null)
                return result.ToString();
            else
                return string.Empty;
        }
        catch (SqlException sqlException)
        {
            throw;
        }
        catch (Exception ex)
        {
            throw;
        }
    }
}
}