Intro
The aim of this guide is to demonstrate how to import IP2Proxy data (PX11) in CSV form into SQLITE and then query the data using C#.
First of all, you will need to download the IP2Proxy PX11 CSV file.
Download commercial version at https://ip2location.com/download?code=PX11
Extract out the IP2PROXY-IP-PROXYTYPE-COUNTRY-REGION-CITY-ISP-DOMAIN-USAGETYPE-ASN-LASTSEEN-THREAT-RESIDENTIAL-PROVIDER.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 PX11 CSV data into the table
- Query the proxy information from the table given a specific IPv4 address
using System;
using System.Data.SQLite;
namespace TestSQLitePX11
{
class Program
{
static readonly string DBName = "PX11.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 `ip2proxy_px11`(
`ip_from` INTEGER,
`ip_to` INTEGER,
`proxy_type` TEXT,
`country_code` CHAR(2),
`country_name` TEXT,
`region_name` TEXT,
`city_name` TEXT,
`isp` TEXT,
`domain` TEXT,
`usage_type` TEXT,
`asn` TEXT,
`as` TEXT,
`last_seen` INTEGER,
`threat` TEXT,
`provider` TEXT,
PRIMARY KEY (`ip_from`, `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 `ip2proxy_px11`(`ip_from`, `ip_to`, `proxy_type`, `country_code`, `country_name`, `region_name`, `city_name`, `isp`, `domain`, `usage_type`, `asn`, `as`, `last_seen`, `threat`, `provider`) VALUES (@ip_from, @ip_to, @proxy_type, @country_code, @country_name, @region_name, @city_name, @isp, @domain, @usage_type, @asn, @as, @last_seen, @threat, @provider)";
using (SQLiteCommand Command = new SQLiteCommand(Conn))
{
using (var transaction = Conn.BeginTransaction())
{
string Line;
string[] Cols;
using (System.IO.StreamReader file = new System.IO.StreamReader(@"IP2PROXY-IP-PROXYTYPE-COUNTRY-REGION-CITY-ISP-DOMAIN-USAGETYPE-ASN-LASTSEEN-THREAT-RESIDENTIAL-PROVIDER.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("@proxy_type", Cols[2]);
Command.Parameters.AddWithValue("@country_code", Cols[3]);
Command.Parameters.AddWithValue("@country_name", Cols[4]);
Command.Parameters.AddWithValue("@region_name", Cols[5]);
Command.Parameters.AddWithValue("@city_name", Cols[6]);
Command.Parameters.AddWithValue("@isp", Cols[7]);
Command.Parameters.AddWithValue("@domain", Cols[8]);
Command.Parameters.AddWithValue("@usage_type", Cols[9]);
Command.Parameters.AddWithValue("@asn", Cols[10]);
Command.Parameters.AddWithValue("@as", Cols[11]);
Command.Parameters.AddWithValue("@last_seen", Cols[12]);
Command.Parameters.AddWithValue("@threat", Cols[13]);
Command.Parameters.AddWithValue("@provider", Cols[14].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 `ip2proxy_px11` WHERE @ipnum >= `ip_from` AND @ipnum <= `ip_to` ORDER BY `ip_from`, `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 TestSQLitePX11.exe which you can run in the command prompt.
To create the database and table, you run the following command:
TestSQLitePX11.exe create
To import the data, you run the following command:
TestSQLitePX11.exe import
To query the proxy data, you run the following command:
TestSQLitePX11.exe query 8.8.8.8
You can replace the 8.8.8.8 with any IPv4 address you wish to query.