Importing IP2Location data into Microsoft SQL Server 2008 R2 and querying with VB.NET (IPv6)

The aim of this guide is to demonstrate how to import IP2Location data (DB24) in csv form into Microsoft SQL Server 2008 R2 and then query the data in VB.NET.

First of all, you will need to download the IP2Location DB24 csv file.
Download commercial version at http://ip2location.com/download?code=DB24IPV6

Extract out the IPV6-COUNTRY-REGION-CITY-LATITUDE-LONGITUDE-ZIPCODE-TIMEZONE-ISP-DOMAIN-NETSPEED-AREACODE-WEATHER-MOBILE-ELEVATION-USAGETYPE.CSV file from the downloaded zipped file and save it to C:\ folder.

Important Note

We will not cover installation of MSSQL and .NET in this guide. We will assume you have already setup MSSQL and you have .NET 4.0 on your Windows platform.

Creating the table in the database

Run the below SQL command to create a new table in the database for storing the IP2Location data.

CREATE DATABASE ip2location
GO

USE ip2location
GO

CREATE TABLE [dbo].[ip2location_db24](
      [ip_from] [char](39) NOT NULL,
      [ip_to] [char](39) NOT NULL,
      [country_code] [nvarchar](2) NOT NULL,
      [country_name] [nvarchar](64) NOT NULL,
      [region_name] [nvarchar](128) NOT NULL,
      [city_name] [nvarchar](128) NOT NULL,
      [latitude] [float] NOT NULL,
      [longitude] [float] NOT NULL,
      [zip_code] [nvarchar](30) NOT NULL,
      [time_zone] [nvarchar](8) NOT NULL,
      [isp] [nvarchar](256) NOT NULL,
      [domain] [nvarchar](128) NOT NULL,
      [net_speed] [nvarchar](8) NOT NULL,
      [idd_code] [nvarchar](5) NOT NULL,
      [area_code] [nvarchar](30) NOT NULL,
      [weather_station_code] [nvarchar](10) NOT NULL,
      [weather_station_name] [nvarchar](128) NOT NULL,
      [mcc] [nvarchar](256) NOT NULL,
      [mnc] [nvarchar](256) NOT NULL,
      [mobile_brand] [nvarchar](128) NOT NULL,
      [elevation] [real] NOT NULL,
      [usage_type] [nvarchar](11) NOT NULL
) ON [PRIMARY]

Importing the csv data into MSSQL

Before import, you will need to create a FMT file to control how the import will load the data into the table.

Copy and paste the code below into a text file called DB24.FMT then save it to the same folder that you have saved the CSV file.

10.0
23
1 SQLCHAR 0 1 "\"" 0 first_double_quote  Latin1_General_CI_AI
2 SQLCHAR 0 39 "\",\"" 1 ip_from ""
3 SQLCHAR 0 39 "\",\"" 2 ip_to ""
4 SQLCHAR 0 2 "\",\"" 3 country_code Latin1_General_CI_AI
5 SQLCHAR 0 64 "\",\"" 4 country_name Latin1_General_CI_AI
6 SQLCHAR 0 128 "\",\"" 5 region_name Latin1_General_CI_AI
7 SQLCHAR 0 128 "\",\"" 6 city_name Latin1_General_CI_AI
8 SQLCHAR 0 20 "\",\"" 7 latitude ""
9 SQLCHAR 0 20 "\",\"" 8 longitude ""
10 SQLCHAR 0 30 "\",\"" 9 zip_code Latin1_General_CI_AI
11 SQLCHAR 0 8 "\",\"" 10 time_zone Latin1_General_CI_AI
12 SQLCHAR 0 256 "\",\"" 11 isp Latin1_General_CI_AI
13 SQLCHAR 0 128 "\",\"" 12 domain Latin1_General_CI_AI
14 SQLCHAR 0 8 "\",\"" 13 net_speed Latin1_General_CI_AI
15 SQLCHAR 0 5 "\",\"" 14 idd_code Latin1_General_CI_AI
16 SQLCHAR 0 30 "\",\"" 15 area_code Latin1_General_CI_AI
17 SQLCHAR 0 10 "\",\"" 16 weather_station_code Latin1_General_CI_AI
18 SQLCHAR 0 128 "\",\"" 17 weather_station_name Latin1_General_CI_AI
19 SQLCHAR 0 256 "\",\"" 18 mcc Latin1_General_CI_AI
20 SQLCHAR 0 256 "\",\"" 19 mnc Latin1_General_CI_AI
21 SQLCHAR 0 128 "\",\"" 20 mobile_brand Latin1_General_CI_AI
22 SQLCHAR 0 20 "\",\"" 21 elevation Latin1_General_CI_AI
23 SQLCHAR 0 11 "\"\r\n" 22 usage_type Latin1_General_CI_AI

Run the below SQL command to import the IP2Location data into the table.

BULK INSERT [ip2location].[dbo].[ip2location_db24]
FROM 'C:\IPV6-COUNTRY-REGION-CITY-LATITUDE-LONGITUDE-ZIPCODE-TIMEZONE-ISP-DOMAIN-NETSPEED-AREACODE-WEATHER-MOBILE-ELEVATION-USAGETYPE.CSV'
WITH
(
	FORMATFILE = 'C:\DB24.FMT'
)

Padding the IP From and IP To fields to enable sorting as string

Due to the fact that SQL Server 2008 R2 does not support a number with more than 38 digits, we have to store the IP From and IP To fields as zero padded strings to enable sorting.

Run the below SQL command to pad the 2 fields in the table.

update ip2location_db24 set 
ip_from = (SUBSTRING(REPLICATE('0', 39), 1, 39 - LEN(ip_from)) + ip_from),
ip_to = (SUBSTRING(REPLICATE('0', 39), 1, 39 - LEN(ip_to)) + ip_to);

Create index to speed up queries

Run the below SQL command to create a clustered index on the IP To field.

CREATE UNIQUE CLUSTERED INDEX [ip_to_index] ON [dbo].[ip2location_db24]
(
	[ip_to] ASC
) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

Querying the IP2Location data from VB.NET winform

In Visual Studio 2010 (or later), create a blank new windows form project which will create a Form1.vb file.

Paste the following VB.NET code into the Form1.vb file and run the code:

Imports System.Data.SqlClient
Imports System.Numerics
Imports System.Net
Imports System.Text

Public Class Form1

    Private Sub Form1_Load(sender As Object, e As System.EventArgs) Handles Me.Load
        Dim ip As String = "2404:6800:4001:c01::67"

        IP2Location(ip)
    End Sub

    Private Sub IP2Location(ByVal myip As String)
        Dim address As IPAddress = Nothing

        If IPAddress.TryParse(myip, address) Then
            Dim addrBytes() As Byte = address.GetAddressBytes()
            LittleEndian(addrBytes)

            Dim ipno As BigInteger = BigInteger.Zero

            If addrBytes.Length > 8 Then
                ipno = BitConverter.ToUInt64(addrBytes, 8)
                ipno <<= 64
                ipno += BitConverter.ToUInt64(addrBytes, 0)

                Dim sql As String = "SELECT TOP 1 * FROM ip2location_db24 WHERE ip_to >= '" + ipno.ToString().PadLeft(39, "0") + "'"
                Dim conn = New SqlConnection("Server=yourserver;Database=yourdatabase;User Id=youruserid;Password=yourpassword;")
                Dim comm = New SqlCommand(sql, conn)

                Dim reader As SqlDataReader
                comm.Connection.Open()

                reader = comm.ExecuteReader(CommandBehavior.CloseConnection)
                Dim x As Integer = 0
                Dim sb = New StringBuilder(250)

                If reader.HasRows Then
                    If reader.Read() Then
                        For x = 0 To reader.FieldCount() - 1
                            sb.Append(reader.GetName(x) & ": " & reader.GetValue(x) & vbNewLine)
                        Next
                    End If
                End If
                reader.Close()

                MsgBox(sb.ToString())
            End If
        End If
    End Sub

    Private Sub LittleEndian(ByRef byteArr() As Byte)
        If BitConverter.IsLittleEndian Then
            Dim byteList As New List(Of Byte)(byteArr)
            byteList.Reverse()
            byteArr = byteList.ToArray()
        End If
    End Sub

End Class

Related Products

IP2Location .NET Component
IP2Location .NET Component


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.