Intro
The aim of this guide is to demonstrate how to import IP2Location data (DB26) in CSV form into SQLITE and then query the data using C#.
First of all, you will need to download the IP2Location DB26 CSV file.
Download commercial version at https://ip2location.com/download?code=DB26
Extract out the IP-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 CSV data into the table
- Query the geolocation information from the table given a specific IPv4 address
using System;
using System.Data.SQLite;
namespace TestSQLITE
{
class Program
{
static readonly string DBName = "DB26.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`(
`ip_from` INTEGER,
`ip_to` INTEGER,
`country_code` TEXT,
`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` TEXT,
`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`(`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(@"IP-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[] { '"' }));
Command.Parameters.AddWithValue("@ip_to", Cols[1]);
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))
{
Conn.Open();
string Sql = "SELECT * FROM `ip2location_db26` WHERE @ipnum <= `ip_to` ORDER BY `ip_to` LIMIT 1";
double Ipnum = Dot2LongIP(MyIP);
using (SQLiteCommand Command = new SQLiteCommand(Conn))
{
Command.CommandText = Sql;
Command.Prepare();
Command.Parameters.AddWithValue("@ipnum", Ipnum);
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();
}
}
public static double Dot2LongIP(string DottedIP)
{
int i;
string[] arrDec;
double num = 0;
if (DottedIP == "")
{
return 0;
}
else
{
arrDec = DottedIP.Split('.');
for (i = arrDec.Length - 1; i >= 0; i--)
{
num += ((int.Parse(arrDec[i]) % 256) * Math.Pow(256, (3 - i)));
}
return num;
}
}
}
}
After compiling the code, you should get a Windows executable called TestSQLITE.exe which you can run in the command prompt.
To create the database and table, you run the following command:
TestSQLITE.exe create
To import the data, you run the following command:
TestSQLITE.exe import
To query the geolocation data, you run the following command:
TestSQLITE.exe query 8.8.8.8
You can replace the 8.8.8.8 with any IPv4 address you wish to query.