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()
If (IO.File.Exists("MyXML.xml")) Then
Dim document As XmlReader = New XmlTextReader("MyXML.xml")
While (document.Read())
Dim type = document.NodeType
If (type = XmlNodeType.Element) Then
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 Timer1.Start()
End Sub
Private Sub Timer1_Tick(sender As Object, e As System.EventArgs) Handles Timer1.Tick
Timer1.Stop() readxml() Addbuttom_dy()
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()
If IO.File.Exists("MyXML.xml") = False Then
Dim settings As New XmlWriterSettings()
settings.Indent = True
Dim XmlWrt As XmlWriter = XmlWriter.Create("MyXML.xml", settings)
With XmlWrt
.WriteStartDocument()
.WriteComment("Check Network Configration")
.WriteStartElement("DataBase_Configration")
.WriteStartElement("Server_name")
.WriteString(TextBox1.Text)
.WriteEndElement()
.WriteEndElement()
.WriteEndDocument()
.Close()
End With
MessageBox.Show("XML file saved.")
Else
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
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"
Button1.Enabled = True
stepno = 1
End If
End Try
Return Nothing
End Function
The next function shows you all used queries:
Private Function thrdstep()
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
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:
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)
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:
Dim clearcmd As SqlCommand = New SqlCommand("TRUNCATE TABLE IPs", mymPingIPDBConn)
clearcmd.ExecuteNonQuery()
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 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
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) 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
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.