This tool, I've created takes the data of the IP ranges for each country shared by and stores it in a SQL Server database, so later you can query it.

To use it you need to add App.config file based on included App.config.sample. Add there your connection string to SQL Server database, and the desired table name. Those are the steps the tool executes:

  • Drops the table if exists
  • Creates table and all the required indices
  • Gets the CSV file for each country from
  • Turns the IP records of each CSV file into INSERT SQL statements
  • Executes the inserts into the SQL database

I tried the same code as parallel and sequential to compare performance. The results are as follow:

Country By IP

As you can see it takes more than 3 and a half times longer to run the code sequentially compared to the parallel execution. ~23 seconds for parallel vs. ~1 minute and 23 seconds for sequential.

You can find the source code at This is how then you can query your database:

SELECT TOP 1 Country FROM [dbo].[yv_country_by_ip] 
WHERE FromIp <= @TheIp AND ToIp >= @TheIp

@TheIp is BIGINT variable. If you need to convert between string IP and long variable, here are some helper functions:

long IpStringToLong(string ipStr)
    var parts = ipStr.Trim().Split('.');
    if(parts.Length < 4) throw new ArgumentException("IP not as expected '"+ipStr+"'");
    var a = Byte.Parse(parts[0]);
    var b = Byte.Parse(parts[1]);
    var c = Byte.Parse(parts[2]);
    var d = Byte.Parse(parts[3]);
    return ((long)a << (8*3)) + (b << (8*2)) + (c << (8*1)) + (d << (8*0));
string IpLongToString(long ipLong)
    var b = BitConverter.GetBytes(ipLong);
    return b[3] + "." + b[2] + "." + b[1] + "." + b[0];

One another important detail that worth mentioning is that we need to use special index with this query to avoid full scan. You can use this index:

    ON [dbo].[yv_country_by_ip]([FromIp], [ToIp]) INCLUDE ([Country])

Share this post:   digg     Stumble Upon     E-mail

Commenting temporarily disabled