IP Address Lookup in Bulk Using VB.NET and MySQL Database

In this tutorial, we demonstrate you on how to lookup IP address in bulk using VB.NET programming languages and IP2Location MySQL database. In this tutorial, we use the IP2Location LITE database to lookup country of origin from the visitor's IP address. Free databases are available for download at IP2Location LITE database.

 

Below are the steps to set up the database for both IPv4 and IPv6 data and the sample codes

Step 1: Download IP2Location LITE database, unzip the file follow the instruction in order to create database table. Please refer to DB11 LITE for further information.

Step 2: Download the demo project and include into your VB.NET project.

Reading IP addresses and querying in bulk

   
    Protected Sub DisplayFileContents(ByVal file As HttpPostedFile)
        Dim theStream As Stream = FileUpload1.PostedFile.InputStream
        Dim Display As StringBuilder = New StringBuilder()
        Using sr As New StreamReader(theStream)
            Dim line As String
            Dim Delimiter1() As Char = New Char() {","}
            While (1)
                line = sr.ReadLine()
                If line <> Nothing Then
                    Dim iplist() As String = line.Split(Delimiter1, StringSplitOptions.None)
                    For Each IP As String In iplist
                        Display.Append("IP Address : " + IP.ToString() + "\n")

                        Dim ip_addr As String = IP

                        Dim ip_num As Long = ip_to_number(ip_addr)
                        Dim query As String = "SELECT * FROM ip2location_db11_ipv4 WHERE ip_to >= " & ip_num & " order by ip_to limit 1"
                        Dim data() As String = Get_Data(query)

                        Dim country_code As String = data(0)
                        Dim country_name As String = data(1)
                        Dim city_name As String = data(2)
                        Dim region_name As String = data(3)
                        Dim latitude As String = data(4)
                        Dim longitude As String = data(5)
                        Dim zip_code As String = data(6)
                        Dim time_zone As String = data(7)

                        'Define the result file that you want to output
                        Dim filePath As String = Server.MapPath("~") + "result.csv"

                        Using Sw As StreamWriter = System.IO.File.AppendText(filePath)
                            Dim output As String = String.Format("""" + IP + """" + "," + """" + country_code + """" + "," + """" + country_name + """" + "," + """" + city_name + """" + "," + """" + region_name + """" + "," + """" + latitude + """" + "," + """" + longitude + """" + "," + """" + zip_code + """" + "," + """" + time_zone + """")
                            Console.WriteLine(output)
                            Sw.WriteLine(output)
                        End Using
                        Label3.Text = "Result File Path : " + Path.GetFullPath(filePath) + "
" Next Else Exit While End If End While End Using Label4.Text = "Please DELETE the result file if you want to upload a new file!
" End Sub

Retrieving data

    Public Function Get_Data(ByVal query As String) As String()
        'Database connection string, replace lower capital with MySQL settings
        Dim db_con_string As String = "SERVER=server;DATABASE=database;UID=userid;PASSWORD=password;"
        Using con As New MySqlConnection(db_con_string)
            Using cmd As New MySqlCommand()
                cmd.CommandText = query
                Using sda As New MySqlDataAdapter()
                    cmd.Connection = con
                    sda.SelectCommand = cmd
                    Using ds As New DataSet()
                        Dim dt As New DataTable()
                        sda.Fill(dt)
                        Dim country_code As Object = dt.Rows(0)("country_code")
                        Dim country_name As Object = dt.Rows(0)("country_name")
                        Dim region_name As Object = dt.Rows(0)("region_name")
                        Dim city_name As Object = dt.Rows(0)("city_name")
                        Dim latitude As Object = dt.Rows(0)("latitude")
                        Dim longitude As Object = dt.Rows(0)("longitude")
                        Dim zip_code As Object = dt.Rows(0)("zip_code")
                        Dim timezone As Object = dt.Rows(0)("time_zone")
                        Dim data() As String = {Convert.ToString(country_code), Convert.ToString(country_name), Convert.ToString(region_name), Convert.ToString(city_name), Convert.ToString(latitude), Convert.ToString(longitude), Convert.ToString(zip_code), Convert.ToString(timezone)}
                        Return data
                    End Using
                End Using
            End Using
        End Using
    End Function

Converting IP address to IP number

    Public Function ip_to_number(ByVal ip_addr As String)
        Dim ip_block() As String
        Dim ip_num As Long

        ip_block = Split(ip_addr, ".")
        ip_num = (ip_block(0) * (256 ^ 3)) + (ip_block(1) * (256 ^ 2)) + (ip_block(2) * 256) + ip_block(3)

        Return ip_num
    End Function

Download demo project : Lookup IP Address in Bulk.

 

Step 1: Download IP2Location LITE database, unzip the file follow the instruction in order to create database table. Please refer to DB11 LITE for further information.

Step 2: Download the demo project and include into your C Sharp project.

Reading IP addresses and querying in bulk

   
    Protected Sub DisplayFileContents(ByVal file As HttpPostedFile)
        Dim theStream As Stream = FileUpload1.PostedFile.InputStream
        Dim Display As StringBuilder = New StringBuilder()
        Using sr As New StreamReader(theStream)
            Dim line As String
            Dim Delimiter1() As Char = New Char() {","}
            While (1)
                line = sr.ReadLine()
                If line <> Nothing Then
                    Dim iplist() As String = line.Split(Delimiter1, StringSplitOptions.None)
                    For Each IP As String In iplist
                        Display.Append("IP Address : " + IP.ToString() + "\n")

                        Dim ip_addr As String = IP

                        Dim ip_num As System.Numerics.BigInteger = ip_to_number(ip_addr)
                        Dim ip_no As String = ip_num.ToString()
                        Dim query As String = "SELECT country_code,country_name,city_name,region_name,latitude,longitude,zip_code,time_zone FROM ip2location_db11 WHERE ip_to >= " & ip_no & " order by ip_to limit 1"
                        Dim data() As String = Get_Data(query)

                        Dim country_code As String = data(0)
                        Dim country_name As String = data(1)
                        Dim city_name As String = data(2)
                        Dim region_name As String = data(3)
                        Dim latitude As String = data(4)
                        Dim longitude As String = data(5)
                        Dim zip_code As String = data(6)
                        Dim time_zone As String = data(7)

                        'Define the result file that you want to output
                        Dim filePath As String = Server.MapPath("~") + "result.csv"

                        Using Sw As StreamWriter = System.IO.File.AppendText(filePath)
                            Dim output As String = String.Format("""" + IP + """" + "," + """" + country_code + """" + "," + """" + country_name + """" + "," + """" + city_name + """" + "," + """" + region_name + """" + "," + """" + latitude + """" + "," + """" + longitude + """" + "," + """" + zip_code + """" + "," + """" + time_zone + """")
                            Console.WriteLine(output)
                            Sw.WriteLine(output)
                        End Using
                        Label3.Text = "Result File Path : " + Path.GetFullPath(filePath) + "
" Next Else Exit While End If End While End Using Label4.Text = "Please DELETE the result file if you want to upload a new file!
" End Sub

Retrieving data

             Public Function Get_Data(ByVal query As String) As String()
               'Database connection string, replace lower capital with MySQL settings
               Dim db_con_string As String = "SERVER=server;DATABASE=database;UID=userid;PASSWORD=password;"
               Using con As New MySqlConnection(db_con_string)
                   Using cmd As New MySqlCommand()
                       cmd.CommandText = query
                       Using sda As New MySqlDataAdapter()
                           cmd.Connection = con
                           sda.SelectCommand = cmd
                           Using ds As New DataSet()
                               Dim dt As New DataTable()
                               sda.Fill(dt)
                               Dim country_code As Object = dt.Rows(0)("country_code")
                               Dim country_name As Object = dt.Rows(0)("country_name")
                               Dim region_name As Object = dt.Rows(0)("region_name")
                               Dim city_name As Object = dt.Rows(0)("city_name")
                               Dim latitude As Object = dt.Rows(0)("latitude")
                               Dim longitude As Object = dt.Rows(0)("longitude")
                               Dim zip_code As Object = dt.Rows(0)("zip_code")
                               Dim timezone As Object = dt.Rows(0)("time_zone")
                               Dim data() As String = {Convert.ToString(country_code), Convert.ToString(country_name), Convert.ToString(region_name), Convert.ToString(city_name), Convert.ToString(latitude), Convert.ToString(longitude), Convert.ToString(zip_code), Convert.ToString(timezone)}
                               Return data
                           End Using
                       End Using
                   End Using
               End Using
           End Function

Converting IP address to IP number

    Public Function ip_to_number(ByVal ip_addr As String) As System.Numerics.BigInteger
        Dim address As System.Net.IPAddress
        Dim ipnum As System.Numerics.BigInteger

        If System.Net.IPAddress.TryParse(ip_addr, address) Then
            Dim addrBytes() As Byte = address.GetAddressBytes()

            If System.BitConverter.IsLittleEndian Then
                Dim byteList As New System.Collections.Generic.List(Of Byte)(addrBytes)
                byteList.Reverse()
                addrBytes = byteList.ToArray()
            End If

            If addrBytes.Length > 8 Then
                'IPv6
                ipnum = System.BitConverter.ToUInt64(addrBytes, 8)
                ipnum <<= 64
                ipnum += System.BitConverter.ToUInt64(addrBytes, 0)
            Else
                'IPv4
                ipnum = System.BitConverter.ToUInt32(addrBytes, 0)
            End If
        End If
        Return ipnum
    End Function

Download demo project : Lookup IP Address in Bulk.

 


Do you like this article? Share it with others by clicking the social media buttons below. We will write more articles related to this topic.