Import DB-IP.com database data to Microsoft Sql Server with ZORAN.DB.IP.Importer tool

Recently I started working on my side-hobby-project on the linktotweet.com web site, and one of the functionality I want to offer to site members is the possibility to track the statistics, and more precisely the location of their visitors. In other words, I need to translate the IP address to an actual location.

While this is almost a trivial task for whoever is using the Google Analytics (like I do) that tracks the visitors, implementing it yourself is actually not such a trivial thing. Ok, it is not that complex too

IP to Location database

The first thing to find out is where to get this IP to Location data from!
Mainly there are two possibilities.

1. Use a third party web service that will translate the ip to a given location
2. Implement your own solution. Meaning, hosting the data on own servers.

I chose to go with the second approach. While there are really many providers around, I wanted to use the most precise and free database. After many days of searching and trying out the solution, I came to the DB-IP.com provider. DB-IP.com is offering some free databases among their commercial versions… My choice was the “IP address to city”, which is really enough for the first version of the site.

Importing tool

While on the DB-IP.com there is a version of the code written in PHP that would import the database I wanted to create something on my own, and I’ve created a command line tool ZORAN.DB.IP.Importer and have published it on git hub as a free download.

How to run the application?

1. Download the free database from this location http://www.db-ip.com/db/download/city. The database comes in the CSV format.
2. Setup the Connection String in the ZORAN.DB-IP-Importer.exe.config file to point to the Microsoft SQL Server database. This is done by changing the value of the connection string named “DB_IP”
3. Once again, in the file ZORAN.DB-IP-Importer.exe.config, enter the path to the CSV file by changing the value of the AppSettings value “db-ip-file-path”
4. Run the two provided scripts dbip_city.sql and dbip_city_stage.sql against your database. This will create two tables (staging and the “live” table). Obviously this should be done only once , the first time you use the application.
5. Run the application.

Yes, but how does it work?

In order to avoid any complex management of the inserts and updates of the already existing data, I’ve chosen the approach of the staging table and the “live” table.

The importing process operations are as follows:

1. Read the CSV file
2. Truncate the data from the “Staging” table
3. Load the data from the CSV file to the “Staging” table
4. In a transaction, move all the data from the “staging” table to the “live” table.

Licence

http://www.apache.org/licenses/LICENSE-2.0

External tools

This project uses the external library CsvHelper which binaries are in this repository. For more info please visit the official site at https://github.com/JoshClose/CsvHelper

Downloads

Download the first version of the compiled application from ZORAN.DB-IP-Importer_v1.0.0.zip

If you think you need any help with the tool please let me know.

About the author:

My name is Zoran Maksimovic. I'm a passionate programmer and interested in everything about Software Development, Object-Oriented Design and Software Architecture. Feel free to contact me or to know more about me in the about section.