• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 301
  • Last Modified:

When DNS Record not found it gets caught in Try...Catch...trying to figure out how to handle this.

ok, I'm opening a connection to the database, and I'm performing a cmd.ExecuteReader, then a "rdr.HasRows", then a "while rdr.Read, "
then "try
                        {
                            string hostname = rdr[0].ToString();
                            IPHostEntry IPHost = Dns.GetHostEntry(hostname);
                           // and a bunch of other stuff.
                         }"
When it hits the statement "IPHostEntry IPHost = Dns.GetHostEntry(hostname);" and there is not DNS Record found it hits the "Catch" (which I was hoping not to happen). If you look at the code below, the problem I'm faced with is if in my Try "Catch", I don't want to have to close the sqlConnection because if I do, it closes the DataSqlReader. However, if I don't close the sqlConnection and reopen it, I get the error "There is already an open DataReader associated with this Command which must be close first. How can I better handle the statement "IPHostEntry IPHost = Dns.GetHostEntry(hostname);" so it doesn't get throw to the Catch keyword OR how can I write the code in the catch so it doesn't close the reader but just writes the error to the database and allows the reader to continue.
I would appreciate any insight someone could provide to me.
Thanks Experts,
Wallace
string hostname = rdr[0].ToString();
 
                            // setup db connection
                            if (conn.State == System.Data.ConnectionState.Open)
                                conn.Close();
                            SqlConnection sqlConn = new SqlConnection(SettingsManager.ConnectionString);
                            conn.Open();
 
                            // setup command objects
                            SqlCommand sqlCmd = new SqlCommand("usp_PingStatus", conn);
                            sqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
 
                            // creating output parameter (as objects)
                            // & adding to parameter collection
                            SqlParameter param1 = new SqlParameter();
                            SqlParameter param2 = new SqlParameter();
                            SqlParameter param3 = new SqlParameter();
                            param1 = sqlCmd.Parameters.Add("@Status", System.Data.SqlDbType.VarChar, 50, "PingStatus");
                            param2 = sqlCmd.Parameters.Add("@Endpoint", System.Data.SqlDbType.VarChar, 75, "Name");
                            param3 = sqlCmd.Parameters.Add("@IPAddress", System.Data.SqlDbType.Char, 15, "IPAddress");
                            param1.Direction = System.Data.ParameterDirection.Input;
                            param2.Direction = System.Data.ParameterDirection.Input;
                            param3.Direction = System.Data.ParameterDirection.Input;
                            param1.Value = "No DNS Record";
                            param2.Value = hostname;
                            param3.Value = "NA";
 
                            // execute the query
                            sqlCmd.ExecuteNonQuery();
 
                            Console.WriteLine(String.Format("EP Status: {0}\nEP Name: {1}\nEP Address: {2}\n", param.Value, param2.Value, param3.Value));
                            //Console.ReadLine();
                            conn.Close();

Open in new window

0
wally_davis
Asked:
wally_davis
  • 5
  • 4
1 Solution
 
williamcampbellCommented:

{
string hostname = rdr[0].ToString();
IPHostEntry IPHost;
try
{ 
   IPHost = Dns.GetHostEntry(hostname);
}
catch ( Exception ce )
{
   // Ignore
}
// and a bunch of other stuff.
}
 
perfectly fine to have a try catch inside another try catch

Open in new window

0
 
wally_davisAuthor Commented:
Hi William. Here's my dilemma:
Ive got a problem when a host is unknown and I want to catch it and then update the DB. It must have something specific to do with the Try/Catch routine. If you notice below, if the IPHost fails to find / get a host entry, it will fail and the Catch statement. Once it hits the sqlCmd.ExecuteNonQuery(); it gives me
an error that says InvalidOperationException was unhandled. There is already an open DataReader associated with this Command which must be closed first. Well, thats a problem because if I close the Reader it has no workstation names with which to continue to process. This Dns.GetHostEntry(hostname) is causing me problems or the Try/Catch statement is. I'm not sure if you can run a Stored Procedure while reading data. What other approach is there in order to get this information updated in the database? Should I use an Array first to read in all the workstations and then close the reader and then proceed?

if (rdr.HasRows)
            {
                while (rdr.Read())
                {
                    string hostname = rdr.GetString(0);
                    try
                    {
                        IPHostEntry IPHost = Dns.GetHostEntry(hostname);
                        //ping
                    }
                    catch
                    {
                        SqlCommand sqlCmd = new SqlCommand("usp_PingStatus", conn);
                        sqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
                        SqlParameter param1 = new SqlParameter();
                        SqlParameter param2 = new SqlParameter();
                        SqlParameter param3 = new SqlParameter();
                        param1 = sqlCmd.Parameters.Add("@Status", System.Data.SqlDbType.VarChar, 50);
                        param2 = sqlCmd.Parameters.Add("@Endpoint", System.Data.SqlDbType.VarChar, 75);
                        param3 = sqlCmd.Parameters.Add("@IPAddress", System.Data.SqlDbType.Char, 15);
                        param1.Direction = System.Data.ParameterDirection.Input;
                        param2.Direction = System.Data.ParameterDirection.Input;
                        param3.Direction = System.Data.ParameterDirection.Input;
                        param1.Value = "No DNS Record";
                        param2.Value = hostname;
                        param3.Value = "NA";
 
                        // execute the query
                        sqlCmd.ExecuteNonQuery(); ß FAILS RIGHT HERE WITH MESSAGE ABOVE
                        break;
                    }

Open in new window

0
 
williamcampbellCommented:
Yeah its bad form to do processing in a catch statement.... see fix below

What is supposed to happen when you don't have a Host Name?



              while (rdr.Read())
                {
                    string hostname = rdr.GetString(0);
                    bool iphostexists = false;
                    try
                    {
                        IPHostEntry IPHost = Dns.GetHostEntry(hostname);
                        iphostexists = true;
                        //ping
                    }
                    catch
                    {
                    }
 
                    if ( iphostexists == true )
                    {
                        SqlCommand sqlCmd = new SqlCommand("usp_PingStatus", conn);
                        sqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
                        SqlParameter param1 = new SqlParameter();
                        SqlParameter param2 = new SqlParameter();
                        SqlParameter param3 = new SqlParameter();
                        param1 = sqlCmd.Parameters.Add("@Status", System.Data.SqlDbType.VarChar, 50);
                        param2 = sqlCmd.Parameters.Add("@Endpoint", System.Data.SqlDbType.VarChar, 75);
                        param3 = sqlCmd.Parameters.Add("@IPAddress", System.Data.SqlDbType.Char, 15);
                        param1.Direction = System.Data.ParameterDirection.Input;
                        param2.Direction = System.Data.ParameterDirection.Input;
                        param3.Direction = System.Data.ParameterDirection.Input;
                        param1.Value = "No DNS Record";
                        param2.Value = hostname;
                        param3.Value = "NA";
 
                        // execute the query
                        sqlCmd.ExecuteNonQuery(); ß FAILS RIGHT HERE WITH MESSAGE ABOVE
                        break;
                    }

Open in new window

0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
wally_davisAuthor Commented:
That's just it, I was trying to use the Try/Catch statement to actually update the Record with the data in the Catch statement if the "IPHostEntry IPHost = Dns.GetHostEntry(hostname);" statement failed. But when I close out the SqlConnection and reopen the SqlConnection, it drops the SqlDataReader. So I guess my question is if it would fail in the Try/Catch, how could I possibly process it in the Catch statement w/o closing the SqlConnection and SqlDataReader and not lose the connection to the db and the current record(s) it is reading in?

The only reason I'm using the Try/Catch is because this statement here --> IPHostEntry IPHost = Dns.GetHostEntry(hostname); <---. I mean, if I can't get around this is it possible to use an Array?
I've never used an Array before and was wondering how I could implement an Array to read computers in from my Nodes Table (Column = Name(varchar(75), not null)) and then iterate through the Array to update the db?
0
 
williamcampbellCommented:
Something like below
             System.Collections.Generic.Dictionary<string, string> hosts = new System.Collections.Generic.Dictionary<string,string> ();
             while (rdr.Read())
             {
                 string hostname = rdr.GetString(0);
                 bool iphostexists = false;
                 try
                 {
                     IPHostEntry IPHost = Dns.GetHostEntry(hostname);
                     iphostexists = true;
                     //ping
                     hosts.Add(hostname, "Ping 256ms"); //message
                 }
                 catch
                 {
                     hosts.Add(hostname, "Bad DNS"); // hostname
                 }
             }
             
             foreach (KeyValuePair<string,string> host in hosts)
             {
                 // Do SQL Query stuff
                 param1.Value = host.Value; // message
                 param2.Value = host.Key; // hostname
                 param3.Value = "NA";
 
                 // execute the query
                 sqlCmd.ExecuteNonQuery();
             }

Open in new window

0
 
wally_davisAuthor Commented:
Hi William,
I implemented the code you suggested. It worked great until once again, I got to the "sqlCmd.ExecuteNonQuery" method and it threw this error: "There is already an open DataReader associated with this Command which must be closed first."
(Looks like I might have to use an Array collection to perform iteration).

I'm going to include the Catch portion of the statement and then down to where it fails as noted above, down below:
catch
                    {
                       hosts.Add(hostname, "No DNS Record"); // hostname
                    }
 
                    //Code here if can get hostname
                    foreach (KeyValuePair<string, string> host in hosts)
                    {
                        SqlCommand sqlCmd = new SqlCommand("usp_PingStatus", conn);
                        sqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
                        SqlParameter param1 = new SqlParameter();
                        SqlParameter param2 = new SqlParameter();
                        SqlParameter param3 = new SqlParameter();
                        param1 = sqlCmd.Parameters.Add("@Status", System.Data.SqlDbType.VarChar, 50);
                        param2 = sqlCmd.Parameters.Add("@Endpoint", System.Data.SqlDbType.VarChar, 75);
                        param3 = sqlCmd.Parameters.Add("@IPAddress", System.Data.SqlDbType.Char, 15);
                        param1.Direction = System.Data.ParameterDirection.Input;
                        param2.Direction = System.Data.ParameterDirection.Input;
                        param3.Direction = System.Data.ParameterDirection.Input;
                        param1.Value = host.Value; //message
                        param2.Value = host.Key; //hostname
                        param3.Value = "NA";
 
                        // execute the query
                        sqlCmd.ExecuteNonQuery(); <-- ERRORS OUT HERE !!
                        break;
                    }

Open in new window

0
 
wally_davisAuthor Commented:
I had to rearrange the code and use a node list to perform a collection to perform a foreach on the collection of workstations and that seemed to resolve the problem. Too much code to go through and add here.
0
 
williamcampbellCommented:
My suggestions led to the final answer ...
0
 
williamcampbellCommented:
3) Comment  02.23.2009 at 04:19PM PST, ID: 23717184
0
 
WhackAModCommented:
Whoops
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now