Importing IP2Location data into SQLITE and querying with C# (IPv4)

Intro

The aim of this guide is to demonstrate how to import IP2Location data (DB11) in CSV form into SQLITE and then query the data using C#.

First of all, you will need to download the IP2Location DB11 CSV file.

Download Free LITE version at https://lite.ip2location.com/database-ip-country-region-city-latitude-longitude-zipcode-timezone

Download commercial version at https://ip2location.com/download?code=DB11CSV

Extract out the IP-COUNTRY-REGION-CITY-LATITUDE-LONGITUDE-ZIPCODE-TIMEZONE.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 DB11 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 = "DB11.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_db11`(
`ip_from` INTEGER ,
`ip_to` INTEGER ,
`country_code` CHAR(2),
`country_name` TEXT,
`region_name` TEXT,
`city_name` TEXT,
`latitude` DOUBLE,
`longitude` DOUBLE,
`zip_code` TEXT,
`time_zone` 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_db11`(`ip_from`, `ip_to`, `country_code`, `country_name`, `region_name`, `city_name`, `latitude`, `longitude`, `zip_code`, `time_zone`) VALUES (@ip_from, @ip_to, @country_code, @country_name, @region_name, @city_name, @latitude, @longitude, @zip_code, @time_zone)";

                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.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].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_db11` 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.

 

Was this article helpful?

Related Articles