Dynamically change connection on database

Question

I am faced to an issue i can't find the solution, perhaps someone can help.

Is it possible to dynamically change the MySQL connection used by the application when the first connection it's unavailable for stopped server ?

I have the replicated clone server that should be queried when the first MySQL connection on main server it's unavailable.

With which programming language can I get this if it is possible?

I use C# on ASPNET, Classic ASP and VBScript.

Thanks in advance for any suggestion, really appreciated.

Edit #01

string sql;
DataSet dsProducts = new DataSet();
public static OdbcConnection conn;

private static OdbcConnection _GetDataConnection()
{
    string conn1 = ConfigurationManager.ConnectionStrings["ConnectionStr1"].ConnectionString;
    string conn2 = ConfigurationManager.ConnectionStrings["ConnectionStr2"].ConnectionString;

    conn = null;

    try
    {
        conn = new OdbcConnection(conn1);
    }
    catch (OdbcException)
    {
        conn = new OdbcConnection(conn2);
    }

    HttpContext.Current.Response.Write(conn.DataSource.ToString() + "<br /><br />");
    return conn;
}

protected void Page_Load(object sender, EventArgs e)
{
    if (!Page.IsPostBack)
    {
        _GetDataConnection();

        if (conn != null && conn.State == ConnectionState.Open)
        {
            conn.Close();
            conn.Open();
        }

        ddl1.AppendDataBoundItems = true;

        sql = @String.Format(" SELECT ES FROM `adoTable`; ");

        using (conn)
        {
            using (OdbcCommand cmd =
                new OdbcCommand(sql, conn))
            {
                try
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.Connection.Open();

                    using (OdbcDataAdapter sda =
                        new OdbcDataAdapter(cmd))
                    {
                        sda.Fill(dsProducts);
                        ddl1.DataSource = dsProducts.Tables[0];
                        ddl1.DataTextField = "ES";
                        ddl1.DataValueField = "ES";
                        ddl1.DataBind();

                        if (ddl1.Items.Count > 1)
                        {
                            ddl1.Enabled = true;
                        }
                        else
                        {
                            ddl1.Enabled = false;
                        }
                    }
                }
                catch (OdbcException)
                {
                    cmd.Connection.Close();
                    cmd.Connection.Dispose();
                }
            }
        }
    }
}

Answer 1

if you have .net project you can put the two connection into the configuration file (web.config) and simply make a separate function wich return the connection , in particoular into this you are going to test wich one works and pass it through. When others queries need to be executed call the wrapped function so that will do the work for you.

For example

private static MySqlConnection _GetDataConnection()
        {
            string conn1 =ConfigurationManager.ConnectionStrings["ConnectionStr1"].ConnectionString;
            string conn2 =ConfigurationManager.ConnectionStrings["ConnectionStr2"].ConnectionString;
            MySqlConnection conn=null; 

            try
            {
              conn = new MySqlConnection(conn1);
              conn.Open();  
              Session["string_conn"] = conn1;            
            }
            catch(MySqlException)
            {
              conn = new MySqlConnection(conn2);
              Session["string_conn"] = conn2; 
            }

            return conn;
        }