Want to use an Array vs SqlDataReader to collect computer names from DB and then process them

Currently, I am using a SqlDataReader  to read in values from (DB Name = DMS; Table = Nodes, Col 1 = NodeID(PK, bigint, not null), Col 2 = Name(varchar(50), not null), Col 3 = PingStatus(varchar(50)), null) and Col 4 = IPAddress(char(15), null).
I want to be able to convert the routine below using an Array vs. a SqlDataReader.
I would appreciate it if an expert could show me how to implement pulling those computer names
that are stored in the Name column in my Nodes table, store them in an Array and a routine how to iterate through each of the computer names stored in the Array.
Thank you Experts,
Wally
// retrieves endpoint list from db
            SqlConnection conn = new SqlConnection(SettingsManager.ConnectionString);   
            
            try
            {
                conn.Open();
                Console.WriteLine("DB Connection has been opened\n");
            }
            catch
            {
                Console.WriteLine("Error opening connection\n");
                return;
            }
 
            // create sql command
            SqlCommand cmd = new SqlCommand("usp_GetNodes", conn);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
 
            // configure input parameters
            SqlParameter param = new SqlParameter();
            param = cmd.Parameters.Add("@Endpoints", System.Data.SqlDbType.VarChar, 75);
            param.Direction = System.Data.ParameterDirection.Input;
            SqlDataReader rdr = null;
            rdr = cmd.ExecuteReader();
            if (rdr.HasRows)
            {
                 while (rdr.Read())
                 {
                         string hostname = rdr[0].ToString();
                            
                            IPHostEntry IPHost = Dns.GetHostEntry(hostname);
                            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();

Open in new window

wally_davisAsked:
Who is Participating?
 
daveamourConnect With a Mentor Commented:
Ok give this a whirl.
It's far from perfect as I would have seperate bits of code fro data access etc but it's a big step up from your last version and heading in the rigth direction.
I would also normally have the Node class in a seperate physical file but it was easy to have it all in one for copying and pasting purposes etc.
Let me know if this was the kind of thing you were looking for.
Noe also that I used a collection called a Generic List rather than an array.
There are many colletion classes in .net and this is the most appropriate one for this scenario I think.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
 
namespace PingStatus
{
    class Program
    {
        static List<NetworkNode> nodeList = new List<NetworkNode>();
        
        static void Main(string[] args)
        {
            string connectionString = "Data Source=MyServer;Initial Catalog=MyDatabase;User Id=MyUsername;Password=MyPassword;";
 
            using (SqlConnection databaseConnection = new SqlConnection(connectionString))
            {
                using (SqlCommand databaseCommand = new SqlCommand("Select * From Nodes", databaseConnection))
                {
                    databaseConnection.Open();
                    
                    using (SqlDataReader reader = databaseCommand.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            NetworkNode node = new NetworkNode();
 
                            node.NodeID = reader.GetInt64(0);
                            node.NodeName = reader.GetString(1);
                            node.Status = (NetworkNode.PingStatus)Enum.Parse(typeof(NetworkNode.PingStatus), reader.GetString(2));
                            node.IpAddress = reader.GetString(3);
 
                            nodeList.Add(node);
                        }
                    }
                }
            }
 
            if (nodeList.Count > 0)
            {
                foreach (NetworkNode node in nodeList)
                {
                    Console.WriteLine();
                    Console.WriteLine("NodeID: " + node.NodeID.ToString());
                    Console.WriteLine("NodeName: " + node.NodeName);
                    Console.WriteLine("Status: " + node.Status);
                    Console.WriteLine("IP Address: " + node.IpAddress);
                    Console.WriteLine("________________________________");
                }
            }
            else
            {
                Console.WriteLine("No nodes :(");
            }
 
            Console.Read();
        }
    }
 
    public class NetworkNode
    {
        public enum PingStatus
        {
            Reachable,
            TimedOut,
            DestinationUnreachable,
            NoDNSRecord
        }
 
        private Int64 nodeID;
        private string nodeName;
        private PingStatus status;
        private string ipAddress;
 
        public Int64 NodeID
        {
            get
            {
                return nodeID;
            }
 
            set
            {
                nodeID = value;
            }
        }
 
        public string NodeName
        {
            get
            {
                return nodeName;
            }
 
            set
            {
                nodeName = value;
            }
        }
 
        public PingStatus Status
        {
            get
            {
                return status;
            }
 
            set
            {
                status = value;
            }
        }
 
        public string IpAddress
        {
            get
            {
                return ipAddress;
            }
 
            set
            {
                ipAddress = value;
            }
        }
    }
}

Open in new window

0
 
daveamourCommented:
Hi Wally
I see more questions on your pinging app.
I can certainly help but not quite clear on your question.
Are you saying that you want a collection - eg ArrayList of custom objects - eg computer objects with properties like IP address which you want to load from your databse and then iterate over them?  Does that sound right?
0
 
wally_davisAuthor Commented:
Thats exactly correct Dave. The problem I'm encountering with my current applications (I'll paste the code below) is when it hits my Try/Catch Statement when a "DNS Record" cant be found, it fails on this statement --> "IPHostEntry IPHost = Dns.GetHostEntry(hostname);" and then when it hits the cmd.ExecuteReader, it fails and says the Data Reader is already opened and must be close. Well, I can't close the Reader because that closes out the looping process to process each workstations so.....
Yes, I want to pull the workstations out of the Nodes table, load them (somehow) into an array and then iterate the ping process over that collection.
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
wally_davisAuthor Commented:
Sorry, here's the current code below:

using System;
using System.Data.SqlClient;
using System.Net;
using System.Net.NetworkInformation;
using System.Diagnostics.CodeAnalysis;
 
namespace RmEndpointStatus
{
    class PingEndpoints
    {
        
        static void Main()
        {
            // retrieves endpoint list from db
            SqlConnection conn = new SqlConnection(SettingsManager.ConnectionString);   
            
            try
            {
                conn.Open();
                Console.WriteLine("DB Connection has been opened\n");
            }
            catch
            {
                Console.WriteLine("Error opening connection\n");
                return;
            }
 
            // create sql command
            SqlCommand cmd = new SqlCommand("usp_GetNodes", conn);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
 
            // configure input parameters
            SqlParameter param = new SqlParameter();
            param = cmd.Parameters.Add("@Endpoints", System.Data.SqlDbType.VarChar, 75);
            param.Direction = System.Data.ParameterDirection.Input;
            SqlDataReader rdr = null;
            rdr = cmd.ExecuteReader();
            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();
                        break;
                    }
 
                    //Code here if can get hostname
 
                    Host(hostname);
                    IPHostEntry IPHost1 = Dns.GetHostEntry(hostname);
 
                    foreach (IPAddress address in IPHost1.AddressList)
                    {
                        Ping pSend = new Ping();
                        PingReply pReply = pSend.Send(address);
 
                        //If the Host has an IP Address
                        if (address != null)
                        {
                            Console.WriteLine(String.Format("Host IP Address for {0} is {1}", hostname, address));
                            PingHosts(address, hostname);
                        }
                        // Ping timed out
                        else if (pReply.Status == IPStatus.TimedOut)
                        {
                            // setting up connection object
                            SqlConnection sqlConn = new SqlConnection(SettingsManager.ConnectionString);
                            conn.Open();
 
                            // setting up 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");
                            param.Direction = System.Data.ParameterDirection.Input;
                            param2.Direction = System.Data.ParameterDirection.Input;
                            param3.Direction = System.Data.ParameterDirection.Input;
                            param.Value = "Timed Out";
                            param2.Value = hostname;
                            param3.Value = Convert.ToString(address);
 
                            // execute the query
                            cmd.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();
                        }
                        else if (pReply.Status == IPStatus.DestinationHostUnreachable)
                        {
                            // setting up connection object
                            SqlConnection sqlConn = new SqlConnection(SettingsManager.ConnectionString);
                            conn.Open();
 
                            // setting up 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");
                            param.Direction = System.Data.ParameterDirection.Input;
                            param2.Direction = System.Data.ParameterDirection.Input;
                            param3.Direction = System.Data.ParameterDirection.Input;
                            param.Value = "Host Unreachable";
                            param2.Value = hostname;
                            param3.Value = Convert.ToString(address);
 
                            // execute the query
                            cmd.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();
                        }
                        else
                        {
                            // do something
                        }
                    }
                }
            }
 
        }
 
        public static string Host(string hostname)
        {
            return hostname;
        }
 
        // 
        public static void PingHosts(IPAddress address, string hostname)
        {
            Ping pSend = new Ping();
            PingReply pReply = pSend.Send(address);
 
            //pSend.Send(address);
 
            if (pReply.Status == IPStatus.Success)
            {
                // setting up connection object
                SqlConnection conn = new SqlConnection(SettingsManager.ConnectionString);
                conn.Open();
 
                // setting up command objects
                SqlCommand cmd = new SqlCommand("usp_PingStatus", conn);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
 
                // creating output parameter (as objects)
                // & adding to parameter collection
                SqlParameter param = new SqlParameter();
                SqlParameter param2 = new SqlParameter();
                SqlParameter param3 = new SqlParameter();
                param = cmd.Parameters.Add("@Status", System.Data.SqlDbType.VarChar, 50, "PingStatus");
                param2 = cmd.Parameters.Add("@Endpoint", System.Data.SqlDbType.VarChar, 75, "Name");
                param3 = cmd.Parameters.Add("@IPAddress", System.Data.SqlDbType.Char, 15, "IPAddress");
                param.Direction = System.Data.ParameterDirection.Input;
                param2.Direction = System.Data.ParameterDirection.Input;
                param3.Direction = System.Data.ParameterDirection.Input;
                param.Value = "Reachable";
                param2.Value = hostname;
                param3.Value = Convert.ToString(address);
 
                //EPStatus.HandleStatus(PingReply pReply.Status);
 
                // execute the query
                cmd.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();
            }
            else 
            {
                // If we need to add another condition
                // otherwise remove If Else selection
                // statements completely
            }
        }
 
    }
}

Open in new window

0
 
daveamourCommented:
Ok I'm not going to bother too much looking at your code but concentrate on the following:
1 Create a custom class which encapsulates a Node
2 Look at code to retrieve all of this data from the database in one go and popualate a collection of these custom node objects
3 Look at how to iterate over them and access their properties
You should then be able to do the rest.
Does that sound ok?
0
 
wally_davisAuthor Commented:
That sounds terrific. I could really use the help so I can better understand how an array works collecting and processing data from a database. Thanks!
0
 
daveamourCommented:
Ok first we need to look at what data we need for each node.
Do we just need:
NodeID
Name
PingStatus
IPAddressIs there anything else we need?
0
 
wally_davisAuthor Commented:
NodeID we don't need. That's just the unique (PK) ID for each record.
These are the only ones you will need:
Name = some computer name from the collection of computers in array.
PingStatus = Reachable or Timed out or Destination Unreachable or No DNS Record or etc.
IP Address = ip addr (that is, if there is an existing DNS record)
0
 
daveamourCommented:
Ok well we will keep the ID, it will be useful.
Give me half an hour...
0
 
wally_davisAuthor Commented:
sounds good. thanks Dave.
0
 
daveamourCommented:
PS the NodeID should be used when you do your database update as this is should be unique hence that's why I left that in there.
0
 
wally_davisAuthor Commented:
Hi Dave,
My only concern here is that it looks like we're still reading through each of the fields which has been my problem with respect to the statement --> "IPHostEntry IPHost = Dns.GetHostEntry(hostname);
" in the Try portion of the Try/Catch statement. I only need to read in the values from the Names column and then update the IPAddress and PingStatus fields based on the Ping Success (or failure).
So I apologize if I wasn't specific. What needs to happen is I need to pull in all of the Workstation names from the Names column, store them in an array and then iterate through all those names stored in the Array collection. Right now we have 507,000 Workstation names in this Column on the Nodes table.
If I need to clarify further please let me know. I definitely appreciate the work you've done so far.
0
 
wally_davisAuthor Commented:
Sorry Dave, I noticed that the reader reads through first and then performs a foreach on the collection.
My apologies. I believe I know what needs to happen from here but will update you as soon as I go to implement this which will be in a couple of hours.
0
 
daveamourCommented:
Hi Wally
Ok see how it goes.
I don't know how it will perform with that many records but give it a go and we will see.
Now that I know you have that many rows then we maybe should code with memory and performance in mind but see how it goes first.
If I remember I will test tomorrow with 500,000 rows of test data but all my code and database is at work and I'm home now.
Let me know how it goes anyway.
0
 
wally_davisAuthor Commented:
Dave, update. So far so good. It takes about 1 minute, 32 seconds to read/loop through 508,907 records.
I'll start adding the If nodeList.Count and foreach (NetworkNode node in nodeList) routines and give you what should be the final update. Nice work on this code!!
0
 
wally_davisAuthor Commented:
Dave, below it the code (almost completed accept for one line of good giving me a fit).
The line of code below that I'm having a problem with is this statement --> "IPHostEntry IPHost = Dns.GetHostEntry(node);"
I get the errors, " The best overloaded method match for 'System.Net.Dns.GetHostEntry(string)' has some invalid arguments" and the second error for this same line of code "Argument '1': cannot convert from 'EndpointStatus.NetworkNode' to 'string'.
I tried setting IPHost as type string. That didn't work. I also tried "IPHostEntry IPHost = Dns.GetHostEntry(Convert.ToString(node));" and it acted like the host was unknown but that host is alive.
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Net;
using System.Net.NetworkInformation;
 
 
namespace EndpointStatus
{
    class Program
    {
        static void Main(string[] args)
        {
            List<NetworkNode> nodeList = new List<NetworkNode>();
 
            SqlConnection conn = new SqlConnection(SettingsManager.ConnectionString);
            try
            {
                conn.Open();
                Console.WriteLine("DB Connection has been opened\n");
            }
            catch
            {
                Console.WriteLine("Database connection failed");
                return;
            }
 
            SqlCommand rcdCount = new SqlCommand("SELECT Count(*) FROM Nodes", conn);
            UInt32 totalRows = Convert.ToUInt32(rcdCount.ExecuteScalar());
            SqlCommand sqlCmd = new SqlCommand("usp_GetNodes", conn);
            sqlCmd.CommandType = CommandType.StoredProcedure;
            SqlDataReader reader = null;
            reader = sqlCmd.ExecuteReader();
 
            Console.WriteLine(String.Format("There are {0} records in the database.", totalRows));
 
            if (reader.HasRows)
            {
                //int currentRow = 0;
                while (reader.Read())
                {
                    NetworkNode node = new NetworkNode();
                    node.NodeName = reader.GetString(0);
                    nodeList.Add(node);
                    Console.WriteLine(node.NodeName);
                    //currentRow++;
                    //Console.WriteLine(String.Format("{0}% Complete", currentRow * 100 / totalRows));
                }
 
                if (nodeList.Count > 0)
                {
                    foreach (NetworkNode node in nodeList)
                    {
                        try
                        {
                            IPHostEntry IPHost = Dns.GetHostEntry(node);
 
                            foreach (IPAddress address in IPHost.AddressList)
                            {
                                Ping pSend = new Ping();
                                PingReply pReply = pSend.Send(address);
                                if (address != null)
                                {
                                    Console.WriteLine(String.Format("Host IP Address for {0} is {1}", node, address));
                                    PingHosts(address, node);
                                }
                                // ping timed out
                                else if (pReply.Status == IPStatus.TimedOut)
                                {
                                    // setup connection
                                    SqlConnection sqlConn = new SqlConnection(SettingsManager.ConnectionString);
                                    conn.Open();
                                    // setup sql commands
                                    SqlCommand sqlCmd1 = new SqlCommand("usp_PingStatus", conn);
                                    sqlCmd1.CommandType = System.Data.CommandType.StoredProcedure;
 
                                    // input parameters
                                    SqlParameter param1 = new SqlParameter();
                                    SqlParameter param2 = new SqlParameter();
                                    SqlParameter param3 = new SqlParameter();
                                    param1 = sqlCmd1.Parameters.Add("@Status", System.Data.SqlDbType.VarChar, 50, "PingStatus");
                                    param2 = sqlCmd1.Parameters.Add("@Endpoint", System.Data.SqlDbType.VarChar, 75, "Name");
                                    param3 = sqlCmd1.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 = "Timed Out";
                                    param2.Value = node;
                                    param3.Value = Convert.ToString(address);
 
                                    // execute the query
                                    sqlCmd1.ExecuteNonQuery();
 
                                    Console.WriteLine(String.Format("EP Status: {0}\nEP Name: {1}\nEP Address: {2}\n", param1.Value, param2.Value, param3.Value));
                                    //Console.ReadLine();
                                    conn.Close();
                                }
                                else if (pReply.Status == IPStatus.DestinationHostUnreachable)
                                {
                                    // setup connection
                                    SqlConnection sqlConn = new SqlConnection(SettingsManager.ConnectionString);
                                    conn.Open();
 
                                    // setup sql commands
                                    SqlCommand sqlCmd2 = new SqlCommand("usp_PingStatus", conn);
                                    sqlCmd2.CommandType = System.Data.CommandType.StoredProcedure;
 
                                    // input parameters
                                    SqlParameter param1 = new SqlParameter();
                                    SqlParameter param2 = new SqlParameter();
                                    SqlParameter param3 = new SqlParameter();
                                    param1 = sqlCmd2.Parameters.Add("@Status", System.Data.SqlDbType.VarChar, 50, "PingStatus");
                                    param2 = sqlCmd2.Parameters.Add("@Endpoint", System.Data.SqlDbType.VarChar, 75, "Name");
                                    param3 = sqlCmd2.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 = "Host Unreachable";
                                    param2.Value = node;
                                    param3.Value = Convert.ToString(address);
 
                                    // execute the query
                                    sqlCmd2.ExecuteNonQuery();
 
                                    Console.WriteLine(String.Format("EP Status: {0}\nEP Name: {1}\nEP Address: {2}\n", param1.Value, param2.Value, param3.Value));
                                    //Console.ReadLine();
                                    conn.Close();
                                }
                                else
                                {
                                    // setup connection
                                    SqlConnection sqlConn = new SqlConnection(SettingsManager.ConnectionString);
                                    conn.Open();
 
                                    // setup sql commands
                                    SqlCommand sqlCmd3 = new SqlCommand("usp_PingStatus", conn);
                                    sqlCmd3.CommandType = System.Data.CommandType.StoredProcedure;
 
                                    // input parameters
                                    SqlParameter param1 = new SqlParameter();
                                    SqlParameter param2 = new SqlParameter();
                                    SqlParameter param3 = new SqlParameter();
                                    param1 = sqlCmd3.Parameters.Add("@Status", System.Data.SqlDbType.VarChar, 50, "PingStatus");
                                    param2 = sqlCmd3.Parameters.Add("@Endpoint", System.Data.SqlDbType.VarChar, 75, "Name");
                                    param3 = sqlCmd3.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 = "Unknown Status";
                                    param2.Value = node;
                                    param3.Value = "NA";
 
                                    // execute the query
                                    sqlCmd3.ExecuteNonQuery();
 
                                    Console.WriteLine(String.Format("EP Status: {0}\nEP Name: {1}\nEP Address: {2}\n", param1.Value, param2.Value, param3.Value));
                                    //Console.ReadLine();
                                    conn.Close();
                                }
                            }
                        }
                        catch (Exception ex)
                        {
                            Console.WriteLine("Error message: " + ex.Message);
                            SqlCommand sqlCmd4 = new SqlCommand("usp_PingStatus", conn);
                            sqlCmd4.CommandType = CommandType.StoredProcedure;
                            SqlParameter param1 = new SqlParameter();
                            SqlParameter param2 = new SqlParameter();
                            SqlParameter param3 = new SqlParameter();
                            param1 = sqlCmd4.Parameters.Add("@Status", System.Data.SqlDbType.VarChar, 50);
                            param2 = sqlCmd4.Parameters.Add("@Endpoint", System.Data.SqlDbType.VarChar, 75);
                            param3 = sqlCmd4.Parameters.Add("@IPAddress", System.Data.SqlDbType.Char, 15);
                            param1.Value = "No DNS Record";
                            param2.Value = node;
                            param3.Value = "NA";
 
                            // execute the query
                            sqlCmd4.ExecuteNonQuery();
                            break;
                        }
                    }
                }
            }
 
            DateTime end = System.DateTime.Now;
            Console.WriteLine("End Time = {0}", end);
        }
 
        public static void PingHosts(IPAddress address, NetworkNode node)
        {
            Ping pSend = new Ping();
            PingReply pReply = pSend.Send(address);
 
            //pSend.Send(address);
 
            if (pReply.Status == IPStatus.Success)
            {
                // setting up connection object
                SqlConnection conn = new SqlConnection(SettingsManager.ConnectionString);
                conn.Open();
 
                // setting up command objects
                SqlCommand cmd = new SqlCommand("usp_PingStatus", conn);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
 
                // creating output parameter (as objects)
                // & adding to parameter collection
                SqlParameter param = new SqlParameter();
                SqlParameter param2 = new SqlParameter();
                SqlParameter param3 = new SqlParameter();
                param = cmd.Parameters.Add("@Status", System.Data.SqlDbType.VarChar, 50, "PingStatus");
                param2 = cmd.Parameters.Add("@Endpoint", System.Data.SqlDbType.VarChar, 75, "Name");
                param3 = cmd.Parameters.Add("@IPAddress", System.Data.SqlDbType.Char, 15, "IPAddress");
                param.Direction = System.Data.ParameterDirection.Input;
                param2.Direction = System.Data.ParameterDirection.Input;
                param3.Direction = System.Data.ParameterDirection.Input;
                param.Value = "Reachable";
                param2.Value = node;
                param3.Value = Convert.ToString(address);
 
                //EPStatus.HandleStatus(PingReply pReply.Status);
 
                // execute the query
                cmd.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();
            }
            else
            {
                // If we need to add another condition
                // otherwise remove If Else selection
                // statements completely
            }
        }
 
 
    }
}

Open in new window

0
 
daveamourConnect With a Mentor Commented:
Morning

If you look at the definition of the GetHostEntry method either by hovering over it, or just opening the brackets or right clicking and go to definition you will see that it expects either a string or an IPAddress.

In your code you are doing this:

IPHostEntry IPHost = Dns.GetHostEntry(node);

Where node is an instance of our custom NetworkNode class.

So if for example you want to pass in the hostname you would go:

IPHostEntry IPHost = Dns.GetHostEntry(node.NodeName);

I'm assuming you put the NetworkNode class in it's own file?

I also note that the type of status for the ping reply is of:

System.Net.NetworkInformation.IPStatus which is an enumeration much like ours.

So we should really change our NetworkNode class to match this for comparison reasons so we would have:
 
    public class NetworkNode
    {
        private Int64 nodeID;
        private string nodeName;
        private System.Net.NetworkInformation.IPStatus status;
        private string ipAddress;
 
        public Int64 NodeID
        {
            get
            {
                return nodeID;
            }
 
            set
            {
                nodeID = value;
            }
        }
 
        public string NodeName
        {
            get
            {
                return nodeName;
            }
 
            set
            {
                nodeName = value;
            }
        }
 
        public System.Net.NetworkInformation.IPStatus Status
        {
            get
            {
                return status;
            }
 
            set
            {
                status = value;
            }
        }
 
        public string IpAddress
        {
            get
            {
                return ipAddress;
            }
 
            set
            {
                ipAddress = value;
            }
        }
    }
I am still at home so have no code to run here but give me another shout after you have made these changes and we will get it working.
All your issues are caused by data type issues so have a think about this!
0
 
wally_davisAuthor Commented:
Dave, if you would like I can go ahead and Accept a couple solutions in here. Let me know.
Yet again though, I'm still getting this dog gone error, "There is already an open DataReader associated with this Command which must be closed first." in the Catch statement of the Try/Catch.
It is when the "IPHostEntry IPHost = Dns.GetHostEntry(node.NodeName)" statement fails to locate a DNS Record and then gets caught in the Try/Catch statement. You can look at the previous code I sent you.
Your last comment helped me tremendously and updated your code. Ya, your right, I'm need to be more cognizant of the data types I'm working with.
0
 
wally_davisAuthor Commented:
I'm about at my wits end with this DataReader error that keeps coming up. I thought the way I wrote the code and storing all the Workstations in a List Collection would have helped my situation but apparently thats not the case.
0
 
daveamourCommented:
No don't close it till it's working, I don't have much on at at work.
Can you show me your complete code?
0
 
daveamourCommented:
Don't panic I will fix it, trust me!
0
 
wally_davisAuthor Commented:
By the way Dave, I do have a separate NetworkNode.cs Class and it works great. Thanks!
I think we've finally go it working. I had to remove the "break;" from the Catch in the Try/Catch statement.
I had to add a Reader.Close() method right after reading all the workstations into the List Collections block of code. That was the other part of the problem.
Looks like I might just need to fine tune the If/Else statements to catch other types of Ping failures.
If you could take a look at the code and let me know if anything could be made better that would be great. Thank you for all your hard work. Your experience shows you know what you're doing.
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Net;
using System.Net.NetworkInformation;
 
 
namespace EndpointStatus
{
    class Program
    {
        static void Main(string[] args)
        {
            List<NetworkNode> nodeList = new List<NetworkNode>();
 
            SqlConnection conn = new SqlConnection(SettingsManager.ConnectionString);
            try
            {
                conn.Open();
                Console.WriteLine("DB Connection has been opened\n");
            }
            catch
            {
                Console.WriteLine("Database connection failed");
                return;
            }
 
            SqlCommand rcdCount = new SqlCommand("SELECT Count(*) FROM Nodes", conn);
            UInt32 totalRows = Convert.ToUInt32(rcdCount.ExecuteScalar());
            SqlCommand sqlCmd = new SqlCommand("usp_GetNodes", conn);
            sqlCmd.CommandType = CommandType.StoredProcedure;
            SqlDataReader reader = null;
            reader = sqlCmd.ExecuteReader();
 
            Console.WriteLine(String.Format("There are {0} records in the database.", totalRows));
 
            if (reader.HasRows)
            {
                //int currentRow = 0;
                while (reader.Read())
                {
                    NetworkNode node = new NetworkNode();
                    node.NodeName = reader.GetString(0);
                    nodeList.Add(node);
                    Console.WriteLine(node.NodeName);
                    //currentRow++;
                    //Console.WriteLine(String.Format("{0}% Complete", currentRow * 100 / totalRows));
                }
 
                reader.Close();
 
                if (nodeList.Count > 0)
                {
                    foreach (NetworkNode node in nodeList)
                    {
                        try
                        {
                            IPHostEntry IPHost = Dns.GetHostEntry(node.NodeName);
 
                            foreach (IPAddress address in IPHost.AddressList)
                            {
                                Ping pSend = new Ping();
                                PingReply pReply = pSend.Send(address);
                                if (address != null)
                                {
                                    Console.WriteLine(String.Format("Host IP Address for {0} is {1}", node.NodeName, address));
                                    PingHosts(address, node);
                                }
                                // ping timed out
                                else if (pReply.Status == IPStatus.TimedOut)
                                {
                                    // setup connection
                                    SqlConnection sqlConn = new SqlConnection(SettingsManager.ConnectionString);
                                    conn.Open();
                                    // setup sql commands
                                    SqlCommand sqlCmd1 = new SqlCommand("usp_PingStatus", conn);
                                    sqlCmd1.CommandType = System.Data.CommandType.StoredProcedure;
 
                                    // input parameters
                                    SqlParameter param1 = new SqlParameter();
                                    SqlParameter param2 = new SqlParameter();
                                    SqlParameter param3 = new SqlParameter();
                                    param1 = sqlCmd1.Parameters.Add("@Status", System.Data.SqlDbType.VarChar, 50, "PingStatus");
                                    param2 = sqlCmd1.Parameters.Add("@Endpoint", System.Data.SqlDbType.VarChar, 75, "Name");
                                    param3 = sqlCmd1.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 = "Timed Out";
                                    param2.Value = node.NodeName;
                                    param3.Value = Convert.ToString(address);
 
                                    // execute the query
                                    sqlCmd1.ExecuteNonQuery();
 
                                    Console.WriteLine(String.Format("EP Status: {0}\nEP Name: {1}\nEP Address: {2}\n", param1.Value, param2.Value, param3.Value));
                                    //Console.ReadLine();
                                    conn.Close();
                                }
                                else if (pReply.Status == IPStatus.DestinationHostUnreachable)
                                {
                                    // setup connection
                                    SqlConnection sqlConn = new SqlConnection(SettingsManager.ConnectionString);
                                    conn.Open();
 
                                    // setup sql commands
                                    SqlCommand sqlCmd2 = new SqlCommand("usp_PingStatus", conn);
                                    sqlCmd2.CommandType = System.Data.CommandType.StoredProcedure;
 
                                    // input parameters
                                    SqlParameter param1 = new SqlParameter();
                                    SqlParameter param2 = new SqlParameter();
                                    SqlParameter param3 = new SqlParameter();
                                    param1 = sqlCmd2.Parameters.Add("@Status", System.Data.SqlDbType.VarChar, 50, "PingStatus");
                                    param2 = sqlCmd2.Parameters.Add("@Endpoint", System.Data.SqlDbType.VarChar, 75, "Name");
                                    param3 = sqlCmd2.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 = "Host Unreachable";
                                    param2.Value = node.NodeName;
                                    param3.Value = Convert.ToString(address);
 
                                    // execute the query
                                    sqlCmd2.ExecuteNonQuery();
 
                                    Console.WriteLine(String.Format("EP Status: {0}\nEP Name: {1}\nEP Address: {2}\n", param1.Value, param2.Value, param3.Value));
                                    //Console.ReadLine();
                                    conn.Close();
                                }
                                else
                                {
                                    // setup connection
                                    SqlConnection sqlConn = new SqlConnection(SettingsManager.ConnectionString);
                                    conn.Open();
 
                                    // setup sql commands
                                    SqlCommand sqlCmd3 = new SqlCommand("usp_PingStatus", conn);
                                    sqlCmd3.CommandType = System.Data.CommandType.StoredProcedure;
 
                                    // input parameters
                                    SqlParameter param1 = new SqlParameter();
                                    SqlParameter param2 = new SqlParameter();
                                    SqlParameter param3 = new SqlParameter();
                                    param1 = sqlCmd3.Parameters.Add("@Status", System.Data.SqlDbType.VarChar, 50, "PingStatus");
                                    param2 = sqlCmd3.Parameters.Add("@Endpoint", System.Data.SqlDbType.VarChar, 75, "Name");
                                    param3 = sqlCmd3.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 = "Unknown Status";
                                    param2.Value = node.NodeName;
                                    param3.Value = "NA";
 
                                    // execute the query
                                    sqlCmd3.ExecuteNonQuery();
 
                                    Console.WriteLine(String.Format("EP Status: {0}\nEP Name: {1}\nEP Address: {2}\n", param1.Value, param2.Value, param3.Value));
                                    //Console.ReadLine();
                                    conn.Close();
                                }
                            }
                        }
                        catch (Exception ex)
                        {
                            Console.WriteLine("Error message: " + ex.Message);
                            SqlCommand sqlCmd4 = new SqlCommand("usp_PingStatus", conn);
                            sqlCmd4.CommandType = CommandType.StoredProcedure;
                            SqlParameter param1 = new SqlParameter();
                            SqlParameter param2 = new SqlParameter();
                            SqlParameter param3 = new SqlParameter();
                            param1 = sqlCmd4.Parameters.Add("@Status", System.Data.SqlDbType.VarChar, 50);
                            param2 = sqlCmd4.Parameters.Add("@Endpoint", System.Data.SqlDbType.VarChar, 75);
                            param3 = sqlCmd4.Parameters.Add("@IPAddress", System.Data.SqlDbType.Char, 15);
                            param1.Value = "No DNS Record";
                            param2.Value = node.NodeName;
                            param3.Value = "NA";
 
                            // execute the query
                            sqlCmd4.ExecuteNonQuery();
 
                            Console.WriteLine(String.Format("EP Status: {0}\nEP Name: {1}\nEP Address: {2}\n", param1.Value, param2.Value, param3.Value));
                            //break;
                        }
                    }
                }
            }
 
            //DateTime end = System.DateTime.Now;
            //Console.WriteLine("End Time = {0}", end);
        }
 
        public static void PingHosts(IPAddress address, NetworkNode node)
        {
            Ping pSend = new Ping();
            PingReply pReply = pSend.Send(address);
 
            //pSend.Send(address);
 
            if (pReply.Status == IPStatus.Success)
            {
                // setting up connection object
                SqlConnection conn = new SqlConnection(SettingsManager.ConnectionString);
                conn.Open();
 
                // setting up command objects
                SqlCommand cmd = new SqlCommand("usp_PingStatus", conn);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
 
                // creating output parameter (as objects)
                // & adding to parameter collection
                SqlParameter param = new SqlParameter();
                SqlParameter param2 = new SqlParameter();
                SqlParameter param3 = new SqlParameter();
                param = cmd.Parameters.Add("@Status", System.Data.SqlDbType.VarChar, 50, "PingStatus");
                param2 = cmd.Parameters.Add("@Endpoint", System.Data.SqlDbType.VarChar, 75, "Name");
                param3 = cmd.Parameters.Add("@IPAddress", System.Data.SqlDbType.Char, 15, "IPAddress");
                param.Direction = System.Data.ParameterDirection.Input;
                param2.Direction = System.Data.ParameterDirection.Input;
                param3.Direction = System.Data.ParameterDirection.Input;
                param.Value = "Reachable";
                param2.Value = node.NodeName;
                param3.Value = Convert.ToString(address);
 
                //EPStatus.HandleStatus(PingReply pReply.Status);
 
                // execute the query
                cmd.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();
            }
            else
            {
                // If we need to add another condition
                // otherwise remove If Else selection
                // statements completely
            }
        }
 
 
    }
}

Open in new window

0
 
daveamourCommented:
Sure I can give it a review but are you saying it is actually working now?
0
 
daveamourConnect With a Mentor Commented:
PS regarding closing the reader.
Look at my data access code.  Do you knwo what the using { } statements do?

            using (SqlConnection databaseConnection = new SqlConnection(connectionString))
            {
                using (SqlCommand databaseCommand = new SqlCommand("Select * From Nodes", databaseConnection))
                {
                    databaseConnection.Open();
                    
                    using (SqlDataReader reader = databaseCommand.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            NetworkNode node = new NetworkNode();
 
                            node.NodeID = reader.GetInt64(0);
                            node.NodeName = reader.GetString(1);
                            node.Status = (NetworkNode.PingStatus)Enum.Parse(typeof(NetworkNode.PingStatus), reader.GetString(2));
                            node.IpAddress = reader.GetString(3);
 
                            nodeList.Add(node);
                        }
                    }
                }
            }

Open in new window

0
 
wally_davisAuthor Commented:
Yes, its working great. I stepped through 41 workstations. Statuses are "No DNS Record", "Reachable", "Timed Out", "Destination Host Unreachable", "unknown".

No, I see the using in some codes and had trouble getting it to work yesterday but I can update it and test that. What exactly does "using" { } statements do?
0
 
daveamourCommented:
Well there are many classes in .net which have a method called Dispose()
You can and should call Dispose on object which implement Dispose as soon as you are finished with them.
Dispose is where resources are cleaned up.  In the case of a reader then calling Dispose will amongst other things close the reader.
So you can do things like this
Thing myThing = new Thing()
//Do some work with thing
thing.Dispose()
There is a shorthand way of doing this which looks like this:
using (Thing myThing = new Thing())
{
    //Do some work with thing
}
When there is no need to call Dispose - it will be called automatically when the code gets to the closing bracket.
Hope this makes sense.
 
0
 
wally_davisAuthor Commented:
The explanation on "Using" makes quite a bit of sense. I think I would prefer using this approach rather than having to dispose (cleanup) of everthing.
One more question for you. I would like to make this application you've been helping me on Multithreaded.
Will this effort become a long mundane task or is it possible to accomplish this feat with someone like myself that only has around 6 months experience?
I'll go ahead and accept your Solution when I hear back from you. Many, many thanks for your assistance Dave. I've learned quite a bit.
0
 
daveamourCommented:
You want to make it multi threaded to speed it up I assume?
It may be a little challenging but I'd be willing to help if you wanted to open a new question.
First though I think your code needs a big tidy up so that it has more objects/classes and methods which are encapsulated.  This makes the app easier to understand, fix when it breaks and modify when you want to change it.  Happy to help you do that too if you like.
You only have 6 months professional experience in coding?  I assumed you were some kind of network engineer who was dabbling in coding to see what nodes were alive etc.  Is that the case?
Can I also assume you are in America judging by the times you appear.
If you are interested I do programming tuition using things like logmein, remote desktop and voice comms over IP to do remote tuition:
http://www.audacs.co.uk/ProgrammingTuition.aspx
Cheers
Dave
0
 
wally_davisAuthor Commented:
Morning Dave, and thanks for your reply. Yes, I live in Phoenix, Arizona, USA
Yes, I now need multithreading to increase the speed. I made one change to the ping.Send(address) and added a timeout value to change that method to ping.Send(address, 400). That definitely helped the DNS/Ping returns a little bit quicker. Can I assume that the standard echo send/reply time is 1000 ms? I changed it to 400 because I notice most timeouts never go above 300ms and if they do, well then the pc is possibly off line, being rebooted, etc.
I'm going to open a new question and respond to your last thread and accept your Solution.
0
 
wally_davisAuthor Commented:
Dave, thank you for taking the time to teach me how to improve/build better code. Your solution worked perfect. I appreicate your time and patience. Sincerest Regards, Wallace.
0
 
daveamourCommented:
Hi
Thanks for the points.
Not sure about the ping time - all depends on latency.  It shoudl generally be quite fast but all depends on network traffic, server usage etc. Much better to have a higher ping threshold and use threading to improve performance.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.