Check Network V1.0
4.69/5 (5 votes)
Network check v1.0
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:
- XML interfacing; Read, Write, and Update data
- SqlConnection; Using SQL Server 2012, LocalDB/V11.0, SQL Server 2008 EXPRESS
- Dynamic controls with events
- Display the connected network IPs
- 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 nameForm2 (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 FunctionNow, 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 FunctionThe 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 FunctionAnd 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 SubAnd to use this event:
Private Sub buttom_dy_click(sender As System.Object, e As System.EventArgs)
Firststep()
check_database()
End SubThe 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.