CODEWITHSUNDEEP
c#asp.netsql-server
user1
user1

Reputation: 1065

Catch 'cannot open database' exception for a database and switch database

My question is, is it possible to catch cannot open database error? when i give a wrong database name in my connection string I get this error on the browser Cannot open database "XYZ" requested by the login

I have two connection string in web.config and if one database is not accessible, I would like to switch to the other database. I have tried

SqlConnection conn = null;
try
    {
        conn = new SqlConnection(ConfigurationManager.ConnectionStrings["PrimaryDatabase"].ConnectionString);
    }
    catch 
    {
        conn = new SqlConnection(ConfigurationManager.ConnectionStrings["BackupDatabase"].ConnectionString);
    }

But server throws an error before hitting this part. Where should i be catching this error?

Or Am i doing it wrong and should be doing something completely different?

Upvotes: 0

Views: 1945

Answers (2)

Cameron Tinker
Cameron Tinker

Reputation: 9789

I wouldn't necessarily recommend nesting try catch blocks, but since conn.Open() throws exceptions, you will want to enclose the second attempt to connect to a database in a try catch block.

SqlConnection conn = null;
try
{
    conn = new SqlConnection(ConfigurationManager.ConnectionStrings["PrimaryDatabase"].ConnectionString);
    conn.Open();
    // do stuff
    conn.Close();
}
catch (SqlException ex)
{
    try 
    {
        conn = new SqlConnection(ConfigurationManager.ConnectionStrings["BackupDatabase"].ConnectionString);
        conn.Open();
        // do stuff
        conn.Close();
    }
    catch
    {
        // log or handle error
    }
}

Upvotes: 0

sircodesalot
sircodesalot

Reputation: 11439

The code you have here doesn't actually open the connection. To do that you would need to use conn.Open(), which isn't here... which means it's outside of the try block, and therefore never going to get caught.

Make sure that your conn.Open() statement is actually inside of the try block and you should be ok.

Upvotes: 4

Related Questions