locked
Need help with ADO.NET issue RRS feed

  • Question

  • Hi all,

    I'm having a problem with ADO and I need some help. I'm trying to connect to a database on a group of machines and sometimes, that database is unavailable for whatever reason. The application bombs out when I just want it to skip that server and go on to the next. Code is below. Any help or insight is appreciated.

    const string usage = "Usage: check_fss_cluster.exe [bayXXX]";

    if (args.Length < 1 || args.Length == 1 && (args[0] == "?" || args[0] == "/?" || args[0] == "-?" || args[0].ToLower() == "help"))

    {

    Console.WriteLine(usage);

    }

    else

    {

    try

    {

    _cluster = args[0];

    SqlConnection Conn, Conn2; //Conn3;

    SqlDataReader drPrimaryServer, drGetPrmCount; //drGetServerPartner;

    Conn = new SqlConnection("Data Source=" + _cluster + "-smdb1;Initial Catalog=satchmomanagementdatabase;Integrated Security=SSPI;");

    string getprimaryserverstring = "select s.servername from tbl_server s, tbl_database d where s.serverid = d.serverid and role = 1";

    try

    {

    Conn.Open();

    }

    catch (Exception ex)

    {

    Console.WriteLine(ex.Message);

    }

    SqlCommand getprimaryserver_cmd = new SqlCommand(getprimaryserverstring, Conn);

    drPrimaryServer = getprimaryserver_cmd.ExecuteReader();

    Console.WriteLine("Hostname\tFSS_Count\tTime");

    while (drPrimaryServer.Read())

    {

    int clusternumber = int.Parse(_cluster.Substring(3, 3));

    //store primary server hostnames in an array

    host_array = new string[drPrimaryServer.FieldCount];

    int fieldCount = drPrimaryServer.GetValues(host_array);

    for (int i = 0; i < fieldCount; i++)

    {

    TextWriter tw = new StreamWriter("e:\\home\\debrown\\fss\\check_fss_cluster.txt");

    Console.Write(host_arrayIdea);

    tw.Write(host_arrayIdea);

    Console.Write("\t");

    tw.Write("\t");

    tw.Close();

    }

    foreach (string hostname in host_array)

    {

    Conn2 = new SqlConnection("Data Source=" + hostname + ";Initial Catalog=Satchmo;Integrated Security=SSPI;");

    string gettblprmcount = "select count(puid) from tbl_partiallyredundantmessage with (nolock)";

    try

    {

    Conn2.Open();

    }

    catch (Exception ex)

    {

    Console.WriteLine(ex.Message);

    }

    SqlCommand gettblprmcount_cmd = new SqlCommand(gettblprmcount, Conn2);

    drGetPrmCount = gettblprmcount_cmd.ExecuteReader();

    while (drGetPrmCount.Read())

    {

    //store prm count in an array

    prm_array = new object[drGetPrmCount.FieldCount];

    int fieldCount2 = drGetPrmCount.GetValues(prm_array);

    for (int j = 0; j < fieldCount2; j++)

    {

    TextWriter tw2 = new StreamWriter("e:\\home\\debrown\\fss\\check_fss_cluster.txt");

    Console.Write(prm_array[j].ToString().PadLeft(8, ' ') + "\t");

    tw2.Write(prm_array[j].ToString().PadLeft(8, ' ') + "\t");

    Console.WriteLine(dtString);

    tw2.WriteLine(dtString);

    tw2.WriteLine();

    tw2.Close();

    }

    Console.WriteLine();

    }

    }

    }

    drPrimaryServer.Close();

    Conn.Close();

    }

    catch (Exception ex)

    {

    Console.WriteLine(ex.Message.ToString());

    Console.WriteLine(ex.StackTrace.ToString());

    return;

    }

    }

    }

    }

    Thursday, February 22, 2007 12:55 AM

Answers

  • What i might do would be to have a single try.. catch block over the entire code portion of the
    foreach..next loop. this way if an exception occurss anywhere in the code for that connection it will skip all of the statements and go right to the next server:

    For example do not do this. When the Open method fails, the exception will be caught and then the remaining code including SqlCommand will try to execute. However with a failed open connection this will then give you another exception and kill the program.

    try

    {

    Conn2.Open();

    }

    catch (Exception ex)

    {

    Console.WriteLine(ex.Message);

    }




    Instead do something like below. This will let the exception skip all of the connection dependent code if the connection fails.


    foreach (string hostname in host_array)

    {


    try (

    Conn2 = new SqlConnection("Data Source=" + hostname + ";Initial Catalog=Satchmo;Integrated Security=SSPI;");

    string gettblprmcount = "select count(puid) from tbl_partiallyredundantmessage with (nolock)";

    Conn2.Open();


    SqlCommand gettblprmcount_cmd = new SqlCommand(gettblprmcount, Conn2);

    drGetPrmCount = gettblprmcount_cmd.ExecuteReader();

    while (drGetPrmCount.Read())

    {

    //store prm count in an array

    prm_array = new object[drGetPrmCount.FieldCount];

    int fieldCount2 = drGetPrmCount.GetValues(prm_array);

    for (int j = 0; j < fieldCount2; j++)

    {

    TextWriter tw2 = new StreamWriter("e:\\home\\debrown\\fss\\check_fss_cluster.txt");

    Console.Write(prm_array[j].ToString().PadLeft(8, ' ') + "\t");

    tw2.Write(prm_array[j].ToString().PadLeft(8, ' ') + "\t");

    Console.WriteLine(dtString);

    tw2.WriteLine(dtString);

    tw2.WriteLine();

    tw2.Close();

    }

    Console.WriteLine();

    }

    }


    drPrimaryServer.Close();

    Conn.Close();

    catch(exception e){

    //if we reach this point then some error occurred in this processing or connection

    }

    // loop to next server in list
    }


    Friday, February 23, 2007 3:39 PM

All replies

  • Hi,

    I am not sure about the problem you have but in your code, one thing is missing,

    In the following loop,
    foreach (string hostname in host_array)

    you are opening the Conn2 but not closing it.

    HTH,

    Thursday, February 22, 2007 7:14 AM
  • Try to put finally affter each catch and close Database connection in it. And we would also like to know the exact exception to give you a precise solution to your problem.

    Best Regards,

    Rizwan aka RizwanSharp

    Friday, February 23, 2007 2:54 PM
  • What i might do would be to have a single try.. catch block over the entire code portion of the
    foreach..next loop. this way if an exception occurss anywhere in the code for that connection it will skip all of the statements and go right to the next server:

    For example do not do this. When the Open method fails, the exception will be caught and then the remaining code including SqlCommand will try to execute. However with a failed open connection this will then give you another exception and kill the program.

    try

    {

    Conn2.Open();

    }

    catch (Exception ex)

    {

    Console.WriteLine(ex.Message);

    }




    Instead do something like below. This will let the exception skip all of the connection dependent code if the connection fails.


    foreach (string hostname in host_array)

    {


    try (

    Conn2 = new SqlConnection("Data Source=" + hostname + ";Initial Catalog=Satchmo;Integrated Security=SSPI;");

    string gettblprmcount = "select count(puid) from tbl_partiallyredundantmessage with (nolock)";

    Conn2.Open();


    SqlCommand gettblprmcount_cmd = new SqlCommand(gettblprmcount, Conn2);

    drGetPrmCount = gettblprmcount_cmd.ExecuteReader();

    while (drGetPrmCount.Read())

    {

    //store prm count in an array

    prm_array = new object[drGetPrmCount.FieldCount];

    int fieldCount2 = drGetPrmCount.GetValues(prm_array);

    for (int j = 0; j < fieldCount2; j++)

    {

    TextWriter tw2 = new StreamWriter("e:\\home\\debrown\\fss\\check_fss_cluster.txt");

    Console.Write(prm_array[j].ToString().PadLeft(8, ' ') + "\t");

    tw2.Write(prm_array[j].ToString().PadLeft(8, ' ') + "\t");

    Console.WriteLine(dtString);

    tw2.WriteLine(dtString);

    tw2.WriteLine();

    tw2.Close();

    }

    Console.WriteLine();

    }

    }


    drPrimaryServer.Close();

    Conn.Close();

    catch(exception e){

    //if we reach this point then some error occurred in this processing or connection

    }

    // loop to next server in list
    }


    Friday, February 23, 2007 3:39 PM
  • Thanks kbradl1. This solution worked for what I needed.
    Sunday, February 25, 2007 10:24 PM