Check Network V1.0

• Download source - 1.3 MB

Introduction

At first, I found the best way to learn something new in programming is to try to make your application more friendly to the user. It will force you to learn more techniques to solve problems.

This application was developed to solve a problem in my work field, we needed a way to check if some TCP/IP devices are connected to our network so that our client could see the status on our SCADA system "Vijio Citect" which can connect with SQL server.

Background

This tip doesn't solve a major problem, it contains solutions for basics problems like the following:

1. XML interfacing; Read, Write, and Update data
2. SqlConnection; Using SQL Server 2012, LocalDB/V11.0, SQL Server 2008 EXPRESS
3. Dynamic controls with events
4. Display the connected network IPs
5. Check if some IPs are connected by network

Using the Code

1. XML Interfacing

In Network Check V1.0 Xml file is used to save the configuration of the application which is the server name of the SQL server so that can be editable for any user according to SQL instance name and PC name.

On loading the main form, I need to read the SQL instance name from the XML file and put it in the GlobalVariables.defultservername (class contain global variables to be public for all forms):

    Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        readxml()
    End Sub

Private Function readxml()
        'check if file myxml.xml is existing
        If (IO.File.Exists("MyXML.xml")) Then

            'create a new xmltextreader object
            'this is the object that we will loop and will be used to read the xml file
            Dim document As XmlReader = New XmlTextReader("MyXML.xml")

            'loop through the xml file
            While (document.Read())

                Dim type = document.NodeType

                'if node type was element
                If (type = XmlNodeType.Element) Then

                    'if the loop found a <Server_name> tag
                    If (document.Name = "Server_name") Then

                        GlobalVariables.defultservername = document.ReadInnerXml.ToString()
                        document.Close()
                    End If
                End If
            End While
        Else
            MessageBox.Show("The filename you selected was not found.")
        End If
        Return Nothing
    End Function 

But, how the XML file is created or contains the SQL instance name that lead us to Form2 (wizfrm) after 500 ms from loading form2.

 Private Sub Form2_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        Me.Text = "Add IPs Wizard"
        Timer1.Interval = 500 'ms
        Timer1.Start()

    End Sub 
       Private Sub Timer1_Tick(sender As Object, e As System.EventArgs) Handles Timer1.Tick

        Timer1.Stop() 'need it one time only
        readxml() 'read the SQL instance name and put it in text1.text
        Addbuttom_dy() 'Adding button dynamically 

    End Sub 

This button is responsible for checking the SQL instance name:

    Private Sub buttom_dy_click(sender As System.Object, e As System.EventArgs)

        Firststep()
        check_database()


    End Sub
 Private Function Firststep()
        'first let's check if there is a file MyXML.xml into our application folder
        'if there wasn't a file something like that, then let's create a new one.

        If IO.File.Exists("MyXML.xml") = False Then

            'declare our xmlwritersettings object
            Dim settings As New XmlWriterSettings()

            'lets tell to our xmlwritersettings that it must use indention for our XML
            settings.Indent = True

            'lets create the MyXML.xml document, the first parameter was the Path/filename of XML file
            ' the second parameter was our XML settings
            Dim XmlWrt As XmlWriter = XmlWriter.Create("MyXML.xml", settings)

            With XmlWrt

                ' Write the XML declaration.
                .WriteStartDocument()

                ' Write a comment.
                .WriteComment("Check Network Configration")

                ' Write the root element.
                .WriteStartElement("DataBase_Configration")

                ' Start our first Server name.
                .WriteStartElement("Server_name")
                .WriteString(TextBox1.Text)

                ' The end of this Server name.
                .WriteEndElement()

                .WriteEndElement()
                ' Close the XmlTextWriter.
                .WriteEndDocument()
                .Close()

            End With

            MessageBox.Show("XML file saved.")

        Else 'file found

            Dim doc As XmlDocument = New XmlDocument()
            doc.Load("MyXML.xml")
            doc.DocumentElement("Server_name").FirstChild.InnerText = TextBox1.Text
            doc.Save("MyXML.xml")
            '--------------------------------------------------------------------

        End If

        Return Nothing
    End Function

Now, we can read, write, create and modify XML files.

2. SqlConnection

In this section, I will show you how to use the most common queries used in SQLConnection.

Firstly, after reading the XML file, I will check the connection with the stored SQL instance name, the best way to check if your instance name right is to try to connect to the master database in the SQL server.

      Private Function check_database()

        GlobalVariables.defultservername = TextBox1.Text
        'trying to connect to the master database 
        Dim myConn As SqlConnection = New SqlConnection("Server=" & _
        GlobalVariables.defultservername & ";Database=master;Integrated Security=SSPI")
        Try

            myConn.Open()
        Catch ex As Exception
            Label4.Text = "Connection error in your server name"
            Label1.BackColor = Color.Red
            Button1.Enabled = False
        Finally
            If (myConn.State = ConnectionState.Open) Then
                myConn.Close()

                Label4.Text = "Your SQL Server is ready"
                'Label1.BackColor = Color.Green
                Button1.Enabled = True
                stepno = 1 ' to tell the app that your are ready to the next step

            End If

        End Try
        Return Nothing
    End Function

The next function shows you all used queries:

 Private Function thrdstep() ' sql 
     
           'to create a database you must connect to the master database of your server
        Dim mymasterConn As SqlConnection = New SqlConnection("Server=" _
        & GlobalVariables.defultservername & ";Database=master;Integrated Security=SSPI")
        Dim mycreatedbCmd As SqlCommand = New SqlCommand("CREATE DATABASE PingIPDB ", mymasterConn)
        Dim exists As Byte = 0 
           'check if the database exists 

        Dim mycheckdbcmd As SqlCommand = New SqlCommand("SELECT case when exists _
        (select 1 from sys.Databases where Name = @DbName) then 1 else 0 end as DbExists", mymasterConn)
        mycheckdbcmd.Parameters.AddWithValue("@DbName", "PingIPDB")


        mymasterConn.Open()
        exists = CByte(mycheckdbcmd.ExecuteScalar())

        If exists = 1 Then
            If (mymasterConn.State = ConnectionState.Open) Then
                mymasterConn.Close()

            End If

            GoTo databasefound

        Else
            mycreatedbCmd.ExecuteNonQuery()

            If (mymasterConn.State = ConnectionState.Open) Then
                mymasterConn.Close()

            End If

        End If

databasefound:
        ' now we can create our table if not exists
        Dim mymPingIPDBConn As SqlConnection = New SqlConnection("Server=" _
        & GlobalVariables.defultservername & ";Database=PingIPDB;Integrated Security=SSPI")
        Dim mychecktablecmd As SqlCommand = New SqlCommand("select case when exists_
        ((select * from information_schema.tables where table_name = 'IPs')) then 1 else 0 end", mymPingIPDBConn)
       
  'create the table 
 Dim mycreatetablecmd As SqlCommand = New SqlCommand("CREATE TABLE IPs _
 (Id int IDENTITY, Compname VARCHAR(30), Compip VARCHAR(20), Status int)", mymPingIPDBConn)

        mymPingIPDBConn.Open()
        exists = CByte(mychecktablecmd.ExecuteScalar())

        If exists = 1 Then

            GoTo tablefound
        Else

            mycreatetablecmd.ExecuteNonQuery()
        End If


tablefound:  'still opened
        'clear table before insert important so not to inserting more than the expected rows 

        Dim clearcmd As SqlCommand = New SqlCommand("TRUNCATE TABLE IPs", mymPingIPDBConn)
        clearcmd.ExecuteNonQuery()


         ' now iam inserting the values of the array
        Dim inscmd As SqlCommand = New SqlCommand("INSERT INTO IPs(Compname, Compip) _
        VALUES('" & data_sql(0, 0) & "', '" & data_sql(0, 1) & "')", mymPingIPDBConn)
        Dim inscmd1 As SqlCommand = New SqlCommand("INSERT INTO IPs(Compname, Compip) _
        VALUES('" & data_sql(1, 0) & "', '" & data_sql(1, 1) & "')", mymPingIPDBConn)
        Dim inscmd2 As SqlCommand = New SqlCommand("INSERT INTO IPs(Compname, Compip) _
        VALUES('" & data_sql(2, 0) & "', '" & data_sql(2, 1) & "')", mymPingIPDBConn)

        inscmd.ExecuteNonQuery()
        inscmd1.ExecuteNonQuery()
        inscmd2.ExecuteNonQuery()


        If (mymPingIPDBConn.State = ConnectionState.Open) Then
            mymPingIPDBConn.Close()

        End If

        Return Nothing

    End Function 

3. Dynamic Controls with Events

Sure you can create your controls in runtime. This way is very user friendly.

     Private Function Addbuttom_dy()

        buttom_dy.Text = "Check Database"
        buttom_dy.Visible = True
        buttom_dy.Location = New Point(381, 174)
        Me.Controls.Add(buttom_dy)
        Return Nothing

    End Function

And to assign an event handler to this control:

    Private Sub Form2_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        AddHandler buttom_dy.Click, AddressOf buttom_dy_click
    End Sub

And to use this event:

    Private Sub buttom_dy_click(sender As System.Object, e As System.EventArgs)

        Firststep()
        check_database()

    End Sub

The project contain a lot of dynamic controls. You can check it.

4. Display the Connected Network IPs

This feature is useful to know the range of the IPs your device connected to.

 Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        GetIPv4Address()
    End Sub 
    Private Function GetIPv4Address() As String ' get all ips connected on locl host
        GetIPv4Address = String.Empty
        Dim strHostName As String = System.Net.Dns.GetHostName()
        Dim iphe As System.Net.IPHostEntry = System.Net.Dns.GetHostEntry(strHostName)
        Dim lablearr(,) As TextBox
        Dim index As Integer
        'to get all ips
        For Each ipheal As System.Net.IPAddress In iphe.AddressList
            If ipheal.AddressFamily = System.Net.Sockets.AddressFamily.InterNetwork Then
                index = index + 1
            End If
        Next
        '------------------------
        ReDim lablearr(index - 1, 0) 'to obtain the number of ur ips
        index = 0
        For Each ipheal As System.Net.IPAddress In iphe.AddressList
            If ipheal.AddressFamily = System.Net.Sockets.AddressFamily.InterNetwork Then
                GetIPv4Address = ipheal.ToString()
                lablearr(index, 0) = New TextBox
                lablearr(index, 0).Text = GetIPv4Address
                lablearr(index, 0).Visible = True
                lablearr(index, 0).ReadOnly = True
                lablearr(index, 0).BorderStyle = BorderStyle.None
                lablearr(index, 0).Location = New Point(12, 35 + index * 21)
                SplitContainer1.Panel1.Controls.Add(lablearr(index, 0))
                index = index + 1

            End If
        Next

    End Function 

5. Check If Some IPs Are Connected By Network

Read the IP from SQL table, then ping on that IP, then insert the status of that IP on the same table on the Status column.

     Private Sub Timer1_Tick(sender As Object, e As System.EventArgs) Handles Timer1.Tick

        Dim mymPingIPDBConn As SqlConnection = New SqlConnection("Server=" _
        & GlobalVariables.defultservername & ";Database=PingIPDB;Integrated Security=SSPI")
        Dim selcmd As SqlCommand = New SqlCommand("select * from IPs", mymPingIPDBConn)

        Dim rdr As SqlDataReader
        Dim x As Integer = 0
        Dim y As Integer = 0
        Try
            mymPingIPDBConn.Open()

            selcmd.ExecuteNonQuery()
            rdr = selcmd.ExecuteReader()

            While (rdr.Read())

                data_frmsql(x, y) = rdr("Compname")
                data_frmsql(x, y + 1) = rdr("Compip")
                x = x + 1

            End While
            x = 0
        Catch ex As Exception
            ' MsgBox(ex.Message)

        End Try

        If (mymPingIPDBConn.State = ConnectionState.Open) Then
            mymPingIPDBConn.Close()

        End If

        For x = 0 To 2

            Dim IPadd As IPAddress = IPAddress.Parse(data_frmsql(x, y + 1))

            Select Case IPadd.AddressFamily
                Case System.Net.Sockets.AddressFamily.InterNetwork


                    Dim index As Integer = IPadd.ToString.IndexOf("0")
                    Dim index1 As Integer = IPadd.ToString.IndexOf("224")
                    Dim index2 As Integer = IPadd.ToString.IndexOf("225")
                    If index = 0 Or index1 = 0 Or index2 = 0 Then

                    Else
                        
                        Dim myping As Ping = New Ping
                        Dim pingreply As PingReply
                        Dim options As PingOptions = New PingOptions
                        options.DontFragment = True
                        options.Ttl = 2

                        Try
                            pingreply = myping.Send(IPadd.ToString, 200)

                            If pingreply.Status = IPStatus.Success Then
                                Debug.WriteLine(data_frmsql(x, y) + "is connected")
                                GoTo connected

                            Else
                                Debug.WriteLine(data_frmsql(x, y) + "is disconnected")
                                GoTo disconnected

                            End If

                        Catch ex As Exception
                            Debug.WriteLine(data_frmsql(x, y) + "xxxxxxxxxxxxxxxx")
                            GoTo disconnected

                        End Try
                    End If


                Case Else
                    Debug.WriteLine("not ether")
            End Select

connected:
            '-------
            Dim updatecmd As SqlCommand = New SqlCommand("UPDATE IPS set Status = 1 _
            WHERE Compip ='" & data_frmsql(x, y + 1) & "'", mymPingIPDBConn)

            Try
                mymPingIPDBConn.Open()
                updatecmd.ExecuteNonQuery()

            Catch ex As Exception

            End Try
            If (mymPingIPDBConn.State = ConnectionState.Open) Then
                mymPingIPDBConn.Close()
            End If

            GoTo Finish
disconnected:
            '----------
            Dim updatecmd2 As SqlCommand = New SqlCommand("UPDATE  IPS set [Status] = 0 _
            WHERE Compip='" & data_frmsql(x, y + 1) & "'", mymPingIPDBConn)

            Try
                mymPingIPDBConn.Open()
                updatecmd2.ExecuteNonQuery()

            Catch ex As Exception

            End Try
            If (mymPingIPDBConn.State = ConnectionState.Open) Then
                mymPingIPDBConn.Close()
            End If

            GoTo finish

finish:
        Next

    End Sub 

Points of Interest

This app lets me go through good experience for an intermediate level. The greatest part is the wizard form which makes me validate some information to pass it to the following steps.

Note: There are some bugs that I am working on, so feel free to check it.