Intro
The aim of this guide is to demonstrate how to import IP2Location data (DB26 IPv6) in CSV form into SQLITE and then query the data using C#.
First of all, you will need to download the IP2Location DB26 IPv6 CSV file.
Download commercial version at https://ip2location.com/download?code=DB26IPV6
Extract out the IPV6-COUNTRY-REGION-CITY-LATITUDE-LONGITUDE-ZIPCODE-TIMEZONE-ISP-DOMAIN-NETSPEED-AREACODE-WEATHER-MOBILE-ELEVATION-USAGETYPE-ADDRESSTYPE-CATEGORY-DISTRICT-ASN.CSV file from the downloaded zipped file. You will need to copy it to the folder where your Windows executable is stored. The sample code below will create the exe file in the bin folder so that’s where we will store our CSV file.
Pre-requisite
You need Microsoft Visual Studio to compile our sample code. You will also need to install the SQLITE Core package from NuGet in your Visual Studio.
C# sample code to create, import & query SQLITE
Below is our example code in C# to create a console program that can perform the following actions:
- Create the SQLITE database and table
- Import the DB26 IPv6 CSV data into the table
- Query the geolocation information from the table given a specific IPv4 or IPv6 address
using System;
using System.Data.SQLite;
using System.Numerics;
using System.Net;
using System.Net.Sockets;
using System.Collections.Generic;
namespace TestSQLiteDB26_IPv6
{
class Program
{
static readonly string DBName = "DB26_IPv6.sqlite3";
static string ConnectionString;
static int Main(string[] args)
{
ConnectionString = @"Data Source=" + DBName + "; Version=3;";
if (args.Length == 0)
{
System.Console.WriteLine("Please enter one of the following: \"create\" or \"import\" or \"query <ip address>\".");
return 1;
}
switch (args[0])
{
case "create":
DoCreate();
break;
case "import":
DoImport();
break;
case "query":
if (args.Length != 2)
{
System.Console.WriteLine("Please enter \"query <ip address>\".");
return 1;
}
else
{
DoQuery(args[1]);
}
break;
}
return 0;
}
private static void DoCreate()
{
SQLiteConnection.CreateFile(DBName);
using (SQLiteConnection Conn = new SQLiteConnection(ConnectionString))
{
Conn.Open();
string Sql = @"PRAGMA journal_mode = MEMORY;
PRAGMA synchronous = OFF;
PRAGMA foreign_keys = OFF;
PRAGMA ignore_check_constraints = OFF;
PRAGMA auto_vacuum = NONE;
PRAGMA secure_delete = OFF;
BEGIN TRANSACTION;
CREATE TABLE `ip2location_db26_ipv6`(
`ip_from` CHAR(39),
`ip_to` CHAR(39),
`country_code` CHAR(2),
`country_name` TEXT,
`region_name` TEXT,
`city_name` TEXT,
`latitude` DOUBLE,
`longitude` DOUBLE,
`zip_code` TEXT,
`time_zone` TEXT,
`isp` TEXT,
`domain` TEXT,
`net_speed` TEXT,
`idd_code` TEXT,
`area_code` TEXT,
`weather_station_code` TEXT,
`weather_station_name` TEXT,
`mcc` TEXT,
`mnc` TEXT,
`mobile_brand` TEXT,
`elevation` INTEGER,
`usage_type` TEXT,
`address_type` CHAR(1),
`category` TEXT,
`district` TEXT,
`asn` TEXT,
`as` TEXT,
PRIMARY KEY(`ip_to`)
);
COMMIT;
PRAGMA ignore_check_constraints = ON;
PRAGMA foreign_keys = ON;
PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
";
using (SQLiteCommand Command = new SQLiteCommand(Sql, Conn))
{
Command.ExecuteNonQuery();
}
Conn.Close();
}
}
private static void DoImport()
{
using (SQLiteConnection Conn = new SQLiteConnection(ConnectionString))
{
Conn.Open();
string Sql = "INSERT INTO `ip2location_db26_ipv6`(`ip_from`, `ip_to`, `country_code`, `country_name`, `region_name`, `city_name`, `latitude`, `longitude`, `zip_code`, `time_zone`, `isp`, `domain`, `net_speed`, `idd_code`, `area_code`, `weather_station_code`, `weather_station_name`, `mcc`, `mnc`, `mobile_brand`, `elevation`, `usage_type`, `address_type`, `category`, `district`, `asn`, `as`) VALUES (@ip_from, @ip_to, @country_code, @country_name, @region_name, @city_name, @latitude, @longitude, @zip_code, @time_zone, @isp, @domain, @net_speed, @idd_code, @area_code, @weather_station_code, @weather_station_name, @mcc, @mnc, @mobile_brand, @elevation, @usage_type, @address_type, @category, @district, @asn, @as)";
using (SQLiteCommand Command = new SQLiteCommand(Conn))
{
using (var transaction = Conn.BeginTransaction())
{
string Line;
string[] Cols;
using (System.IO.StreamReader file = new System.IO.StreamReader(@"IPV6-COUNTRY-REGION-CITY-LATITUDE-LONGITUDE-ZIPCODE-TIMEZONE-ISP-DOMAIN-NETSPEED-AREACODE-WEATHER-MOBILE-ELEVATION-USAGETYPE-ADDRESSTYPE-CATEGORY-DISTRICT-ASN.CSV"))
{
while ((Line = file.ReadLine()) != null)
{
Cols = Line.Split(new[] { "\",\"" }, StringSplitOptions.None);
Command.CommandText = Sql;
Command.Prepare();
Command.Parameters.AddWithValue("@ip_from", Cols[0].TrimStart(new[] { '"' }).PadLeft(39, '0'));
Command.Parameters.AddWithValue("@ip_to", Cols[1].PadLeft(39, '0'));
Command.Parameters.AddWithValue("@country_code", Cols[2]);
Command.Parameters.AddWithValue("@country_name", Cols[3]);
Command.Parameters.AddWithValue("@region_name", Cols[4]);
Command.Parameters.AddWithValue("@city_name", Cols[5]);
Command.Parameters.AddWithValue("@latitude", Cols[6]);
Command.Parameters.AddWithValue("@longitude", Cols[7]);
Command.Parameters.AddWithValue("@zip_code", Cols[8]);
Command.Parameters.AddWithValue("@time_zone", Cols[9]);
Command.Parameters.AddWithValue("@isp", Cols[10]);
Command.Parameters.AddWithValue("@domain", Cols[11]);
Command.Parameters.AddWithValue("@net_speed", Cols[12]);
Command.Parameters.AddWithValue("@idd_code", Cols[13]);
Command.Parameters.AddWithValue("@area_code", Cols[14]);
Command.Parameters.AddWithValue("@weather_station_code", Cols[15]);
Command.Parameters.AddWithValue("@weather_station_name", Cols[16]);
Command.Parameters.AddWithValue("@mcc", Cols[17]);
Command.Parameters.AddWithValue("@mnc", Cols[18]);
Command.Parameters.AddWithValue("@mobile_brand", Cols[19]);
Command.Parameters.AddWithValue("@elevation", Cols[20]);
Command.Parameters.AddWithValue("@usage_type", Cols[21]);
Command.Parameters.AddWithValue("@address_type", Cols[22]);
Command.Parameters.AddWithValue("@category", Cols[23]);
Command.Parameters.AddWithValue("@district", Cols[24]);
Command.Parameters.AddWithValue("@asn", Cols[25]);
Command.Parameters.AddWithValue("@as", Cols[26].TrimEnd(new[] { '"' }));
Command.ExecuteNonQuery();
}
file.Close();
}
transaction.Commit();
}
}
Conn.Close();
}
}
private static void DoQuery(string MyIP)
{
using (SQLiteConnection Conn = new SQLiteConnection(ConnectionString))
{
if (IPAddress.TryParse(MyIP, out IPAddress address))
{
string Sql = "SELECT * FROM `ip2location_db26_ipv6` WHERE @ipnum <= `ip_to` ORDER BY `ip_to` LIMIT 1";
BigInteger Ipnum = IPNo(ref address);
Conn.Open();
using (SQLiteCommand Command = new SQLiteCommand(Conn))
{
Command.CommandText = Sql;
Command.Prepare();
Command.Parameters.AddWithValue("@ipnum", Ipnum.ToString().PadLeft(39, '0'));
using (SQLiteDataReader reader = Command.ExecuteReader())
{
while (reader.Read())
{
for (int x = 0; x < reader.FieldCount; x++)
{
Console.WriteLine(reader.GetOriginalName(x) + " = " + reader.GetValue(x));
}
}
}
}
Conn.Close();
}
}
}
private static void LittleEndian(ref byte[] byteArr)
{
if (System.BitConverter.IsLittleEndian)
{
List<byte> byteList = new List<byte>(byteArr);
byteList.Reverse();
byteArr = byteList.ToArray();
}
}
private static BigInteger IPNo(ref IPAddress ipAddress)
{
try
{
byte[] addrBytes = ipAddress.GetAddressBytes();
LittleEndian(ref addrBytes);
BigInteger final;
if (addrBytes.Length > 8)
{
// IPv6
final = System.BitConverter.ToUInt64(addrBytes, 8);
final <<= 64;
final += System.BitConverter.ToUInt64(addrBytes, 0);
}
else
// IPv4
final = System.BitConverter.ToUInt32(addrBytes, 0);
return final;
}
catch (Exception)
{
return 0;
}
}
}
}
After compiling the code, you should get a Windows executable called TestSQLiteDB26_IPv6.exe which you can run in the command prompt.
To create the database and table, you run the following command:
TestSQLiteDB26_IPv6.exe create
To import the data, you run the following command:
TestSQLiteDB26_IPv6.exe import
To query the geolocation data, you run the following command:
TestSQLiteDB26_IPv6.exe query 8.8.8.8
You can replace the 8.8.8.8 with any IPv4 or IPv6 address you wish to query.