This tool, I've created takes the data of the IP ranges for each country shared by http://www.nirsoft.net/countryip/index.html 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 http://www.nirsoft.net/
  • 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 https://github.com/YouVisio/country-by-ip. 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:

CREATE NONCLUSTERED INDEX [IX_country_by_ip_FromIp_ToIp] 
    ON [dbo].[yv_country_by_ip]([FromIp], [ToIp]) INCLUDE ([Country])

Share this post:   digg     Stumble Upon     del.icio.us     E-mail

Please post your comments:

Name:  
Email (optional): Your email address will not be posted.
Comments: HTML will be ignored, URLs will be converted to hyperlinks  
Enter the text you see in the box: