?
Solved

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

Posted on 2009-02-23
31
Medium Priority
?
362 Views
Last Modified: 2013-12-17
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

0
Comment
Question by:wally_davis
  • 16
  • 15
31 Comments
 
LVL 19

Expert Comment

by:daveamour
ID: 23719541
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
 

Author Comment

by:wally_davis
ID: 23721637
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
 

Author Comment

by:wally_davis
ID: 23721644
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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 19

Expert Comment

by:daveamour
ID: 23721700
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
 

Author Comment

by:wally_davis
ID: 23721753
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
 
LVL 19

Expert Comment

by:daveamour
ID: 23721786
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
 

Author Comment

by:wally_davis
ID: 23721902
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
 
LVL 19

Expert Comment

by:daveamour
ID: 23721935
Ok well we will keep the ID, it will be useful.
Give me half an hour...
0
 

Author Comment

by:wally_davis
ID: 23722198
sounds good. thanks Dave.
0
 
LVL 19

Accepted Solution

by:
daveamour earned 2000 total points
ID: 23722289
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
 
LVL 19

Expert Comment

by:daveamour
ID: 23722299
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
 

Author Comment

by:wally_davis
ID: 23722853
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
 

Author Comment

by:wally_davis
ID: 23723220
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
 
LVL 19

Expert Comment

by:daveamour
ID: 23724000
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
 

Author Comment

by:wally_davis
ID: 23725792
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
 

Author Comment

by:wally_davis
ID: 23726637
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
 
LVL 19

Assisted Solution

by:daveamour
daveamour earned 2000 total points
ID: 23730706
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
 

Author Comment

by:wally_davis
ID: 23734197
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
 

Author Comment

by:wally_davis
ID: 23734213
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
 
LVL 19

Expert Comment

by:daveamour
ID: 23734226
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
 
LVL 19

Expert Comment

by:daveamour
ID: 23734231
Don't panic I will fix it, trust me!
0
 

Author Comment

by:wally_davis
ID: 23734481
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
 
LVL 19

Expert Comment

by:daveamour
ID: 23734628
Sure I can give it a review but are you saying it is actually working now?
0
 
LVL 19

Assisted Solution

by:daveamour
daveamour earned 2000 total points
ID: 23734938
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
 

Author Comment

by:wally_davis
ID: 23735272
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
 
LVL 19

Expert Comment

by:daveamour
ID: 23738015
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
 

Author Comment

by:wally_davis
ID: 23739193
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
 
LVL 19

Expert Comment

by:daveamour
ID: 23742013
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
 

Author Comment

by:wally_davis
ID: 23745090
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
 

Author Closing Comment

by:wally_davis
ID: 31618461
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
 
LVL 19

Expert Comment

by:daveamour
ID: 23745239
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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The object model of .Net can be overwhelming at times – so overwhelming that quite trivial tasks often take hours of research. In this case, the task at hand was to populate the datagrid from SQL Server database in Visual Studio 2008 Windows applica…
In real business world data are crucial and sometimes data are shared among different information systems. Hence, an agreeable file transfer protocol need to be established.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Suggested Courses
Course of the Month14 days, 21 hours left to enroll

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question