CODEWITHSUNDEEP
c#.nettry-catch
Mark Kram
Mark Kram

Reputation: 5832

Try-Catch and "Continue" - is this possible?

I have a section in my code where I am querying all SQL Server Databases on my network. I am first trying to use a SQL Login to access the SQL Server Instance but if that fails then I want to try connecting using my Windows Credentials. After that if I still can't connect then I want the code to fail and then notify the user.

So I guess what I am asking is how can I loop back from inside of a Try-Catch block to the line just above the Try-Catch block:

String conxString = @"Data Source=SQLInstance1;User ID=FOO;Password=BAR;";
bool secondTime = false;

using (SqlConnection sqlConx = new SqlConnection(conxString))
     {
         Try{
               sqlConx.Open();
               DataTable tblDatabases = sqlConx.GetSchema("Databases");
               sqlConx.Close();
               secondTime = false;
               Console.WriteLine("SQL Server found!");
         }
         Catch(System.Data.SqlClient.SqlException e){
                if (!secondTime){
                   secondTime = true;
                   conxString = @"Data Source=SQLInstance1; Integrated Security=True;";
                      //Loop back to the using statement to try again with Windows Creds
                {
                 else{
                   Console.WriteLine("SQL Server not found or credentials refused");
                 }
                   //Report Failure to connect to user

         }
         finally{
            //Reset Variable
            secondTime = false;
         }

      }

Upvotes: 5

Views: 8051

Answers (3)

Marcel Jackwerth
Marcel Jackwerth

Reputation: 54762

This blog post (albeit from 2005) shows possible solutions for your problem:

Use Goto

TryLabel:
try
{
    downloadMgr.DownLoadFile("file:///server/file", "c:\\file");
    Console.WriteLine("File successfully downloaded");
}
catch (NetworkException ex)
{
    if (ex.OkToRetry)
        goto TryLabel;
}

Write a Wrapper

public static bool Wrapper(DownloadManager downloadMgr)
{
    try
    {
        downloadMgr.DownLoadFile("file:///server/file", "c:\\file");
        return true;
    }

    catch (NetworkException ex)
    {
        Console.WriteLine("Failed to download file: {0}", ex.Message);
        return (!ex.OkToRetry);
    } 
}

static void Main(string[] args)
{
    while (!Wrapper(downloadMgr)) ;
}

Upvotes: -1

Rick Sladkey
Rick Sladkey

Reputation: 34250

You can use a for loop combined with break when you succeed:

for (int attempt = 1; attempt <= 2; attempt++)
{
    try
    {
        /* perform attempt */
        var success = TryToConnect();
        if (success)
            break;
    }
    catch (Exception e)
    {
        /* report error */
    }
}

You can also record whether you succeeded, etc. or increase the number of attempts or make the number of attempts configurable.

Upvotes: 5

Abe Miessler
Abe Miessler

Reputation: 85056

I would probably go this route:

String conxString = @"Data Source=Instance1;User ID=FOO;Password=BAR;";
//in your main function
if(!TryConnect(conxString))
{
   Console.WriteLine("SQL Creditials failed.  Trying with windows credentials...");
   conxString = "new conn string";
   TryConnect(conxString);
}
..............
//new function outside of your main function
private bool TryConnect(string connString)
{
   using (SqlConnection sqlConx = new SqlConnection(conxString))
     {
         Try{
               sqlConx.Open();
               DataTable tblDatabases = sqlConx.GetSchema("Databases");
               sqlConx.Close();
         }
         Catch(System.Data.SqlClient.SqlException e){
                return false;
         }
         return true;    
      }
}

Upvotes: 11

Related Questions