IP Address Lookup in Bulk Using C-Sharp and MySQL Database

In this tutorial, we demonstrate you on how to lookup IP address in bulk using C-Sharp programming languages and IP2Location MySQL database. In this tutorial, we use the IP2Location LITE database to lookup country of origin from the visitor's IP address. Free databases are available for download at IP2Location LITE database.

 

Below are the steps to set up the database for both IPv4 and IPv6 data and the sample codes

Step 1: Download IP2Location LITE database, unzip the file follow the instruction in order to create database table. Please refer to DB11 LITE for further information.

Step 2: Download the demo project and include into your C Sharp project.

Reading IP addresses and querying in bulk

		void DisplayFileContents(HttpPostedFile file)
        {
            Stream theStream = FileUpload1.PostedFile.InputStream;
            StringBuilder display = new StringBuilder();
            using (StreamReader sr = new StreamReader(theStream))
            {
                string line;
                char[] delimiter1 = new char[] { ',' };

                while ((line = sr.ReadLine()) != null)
                {
                    string[] iplist = line.Split(delimiter1, StringSplitOptions.None);

                    foreach (string IP in iplist)
                    {
                        display.Append("IP Address : " + IP.ToString() + "\n");

                        string ip_addr = IP;

                        long ip_num = ip_to_number(ip_addr);
                        string query = "SELECT * FROM ip2location_db11 WHERE ip_to >= " + ip_num + " order by ip_to limit 1";
                        string[] data = Get_Data(query);

                        string country_code = data[0];
                        string country_name = data[1];
                        string city_name = data[2];
                        string region_name = data[3];
                        string latitude = data[4];
                        string longitude = data[5];
                        string zip_code = data[6];
                        string time_zone = data[7];

                        //Define the result file that you want to output
                        string filePath = Server.MapPath("~") + "result.csv";

                        using (StreamWriter Sw = File.AppendText(filePath))
                        {
                            string output = string.Format("\"" + IP + "\"" + "," + "\"" + country_code + "\"" + "," + "\"" + country_name + "\"" + "," + "\"" + city_name + "\"" + "," + "\"" + region_name + "\"" + "," + "\"" + latitude + "\"" + "," + "\"" + longitude + "\"" + "," + "\"" + zip_code + "\"" + "," + "\"" + time_zone + "\"");

                            Console.WriteLine(output);
                            Sw.WriteLine(output);

                        }
                        Label3.Text = "Result File Path : " + Path.GetFullPath(filePath) + "
"; } } } Label4.Text = "Please DELETE the result file if you want to upload a new file!
"; }

Retrieving data

		public static string[] Get_Data(string query)
        {
            //Database connection string, replace lower capital with MySQL settings
            string db_con_string = "SERVER=server;DATABASE=database;UID=userid;PASSWORD=password;";
            using (MySqlConnection con = new MySqlConnection(db_con_string))
            {
                using (MySqlCommand cmd = new MySqlCommand())
                {
                    cmd.CommandText = query;
                    using (MySqlDataAdapter sda = new MySqlDataAdapter())
                    {
                        cmd.Connection = con;
                        sda.SelectCommand = cmd;
                        using (DataSet ds = new DataSet())
                        {
                            DataTable dt = new DataTable();
                            sda.Fill(dt);
                            object country_code = dt.Rows[0]["country_code"];
                            object country_name = dt.Rows[0]["country_name"];
                            object region_name = dt.Rows[0]["region_name"];
                            object city_name = dt.Rows[0]["city_name"];
                            object latitude = dt.Rows[0]["latitude"];
                            object longitude = dt.Rows[0]["longitude"];
                            object zip_code = dt.Rows[0]["zip_code"];
                            object timezone = dt.Rows[0]["time_zone"];
                            string[] data = { Convert.ToString(country_code), Convert.ToString(country_name), Convert.ToString(region_name), Convert.ToString(city_name), Convert.ToString(latitude), Convert.ToString(longitude), Convert.ToString(zip_code), Convert.ToString(timezone) };
                            return data;
                        }
                    }
                }
            }
        }

Converting IP address to IP number

		public long ip_to_number(string ip_addr)
        {
            string[] ip_Sblock;
            int[] ip_block = new int[4];
            string[] separator = { "." };

            ip_Sblock = ip_addr.Split(separator, StringSplitOptions.None);
            for (int i = 0; i <= 3; i++)
            {
                ip_block[i] = Convert.ToInt32(ip_Sblock[i]);
            }

            return ip_block[0] * 16777216 + ip_block[1] * 65536 + ip_block[2] * 256 + ip_block[3];
        }

Download demo project : Lookup IP Address in Bulk.

 

Step 1: Download IP2Location LITE database, unzip the file follow the instruction in order to create database table. Please refer to DB11 LITE for further information.

Step 2: Download the demo project and include into your C Sharp project.

Reading IP addresses and querying in bulk

		   void DisplayFileContents(HttpPostedFile file)
        {
            Stream theStream = FileUpload1.PostedFile.InputStream;
            StringBuilder display = new StringBuilder();
            using (StreamReader sr = new StreamReader(theStream))
            {
                string line;
                char[] delimiter1 = new char[] { ',' };

                while ((line = sr.ReadLine()) != null)
                {
                    string[] iplist = line.Split(delimiter1, StringSplitOptions.None);

                    foreach (string IP in iplist)
                    {
                        display.Append("IP Address : " + IP.ToString() + "\n");

                        string ip_addr = IP;

                        System.Numerics.BigInteger ip_num = ip_to_number(ip_addr);
                        String ip_no = ip_num.ToString();
                        string query = "SELECT country_code, country_name , city_name , region_name, latitude,longitude,zip_code,time_zone FROM ip2location_db11 WHERE ip_to >= " + ip_no + " order by ip_to limit 1";
                        string[] data = Get_Data(query);

                        string country_code = data[0];
                        string country_name = data[1];
                        string city_name = data[2];
                        string region_name = data[3];
                        string latitude = data[4];
                        string longitude = data[5];
                        string zip_code = data[6];
                        string time_zone = data[7];

                        //Define the result file that you want to output
                        string filePath = Server.MapPath("~") + "result.csv";

                        using (StreamWriter Sw = File.AppendText(filePath))
                        {
                            string output = string.Format("\"" + IP + "\"" + "," + "\"" + country_code + "\"" + "," + "\"" + country_name + "\"" + "," + "\"" + city_name + "\"" + "," + "\"" + region_name + "\"" + "," + "\"" + latitude + "\"" + "," + "\"" + longitude + "\"" + "," + "\"" + zip_code + "\"" + "," + "\"" + time_zone + "\"");

                            Console.WriteLine(output);
                            Sw.WriteLine(output);

                        }
                        Label3.Text = "Result File Path : " + Path.GetFullPath(filePath) + "
"; } } } Label4.Text = "Please DELETE the result file if you want to upload a new file!
"; }

Retrieving data

		public static string[] Get_Data(string query)
        {
            //Database connection string, replace lower capital with MySQL settings
            string db_con_string = "SERVER=server;DATABASE=database;UID=userid;PASSWORD=password;";
            using (MySqlConnection con = new MySqlConnection(db_con_string))
            {
                using (MySqlCommand cmd = new MySqlCommand())
                {
                    cmd.CommandText = query;
                    using (MySqlDataAdapter sda = new MySqlDataAdapter())
                    {
                        cmd.Connection = con;
                        sda.SelectCommand = cmd;
                        using (DataSet ds = new DataSet())
                        {
                            DataTable dt = new DataTable();
                            sda.Fill(dt);
                            object country_code = dt.Rows[0]["country_code"];
                            object country_name = dt.Rows[0]["country_name"];
                            object region_name = dt.Rows[0]["region_name"];
                            object city_name = dt.Rows[0]["city_name"];
                            object latitude = dt.Rows[0]["latitude"];
                            object longitude = dt.Rows[0]["longitude"];
                            object zip_code = dt.Rows[0]["zip_code"];
                            object timezone = dt.Rows[0]["time_zone"];
                            string[] data = { Convert.ToString(country_code), Convert.ToString(country_name), Convert.ToString(region_name), Convert.ToString(city_name), Convert.ToString(latitude), Convert.ToString(longitude), Convert.ToString(zip_code), Convert.ToString(timezone) };
                            return data;
                        }
                    }
                }
            }
        }

Converting IP address to IP number

        System.Numerics.BigInteger ip_to_number(string ip_addr)
        {
            System.Net.IPAddress address;
            System.Numerics.BigInteger ipnum = 0;

            if (System.Net.IPAddress.TryParse(ip_addr, out address))
            {
                byte[] addrBytes = address.GetAddressBytes();

                if (System.BitConverter.IsLittleEndian)
                {
                    System.Collections.Generic.List byteList = new System.Collections.Generic.List(addrBytes);
                    byteList.Reverse();
                    addrBytes = byteList.ToArray();
                }

                if (addrBytes.Length > 8)
                {
                    //IPv6
                    ipnum = System.BitConverter.ToUInt64(addrBytes, 8);
                    ipnum <<= 64;
                    ipnum += System.BitConverter.ToUInt64(addrBytes, 0);
                }
                else
                {
                    //IPv4
                    ipnum = System.BitConverter.ToUInt32(addrBytes, 0);
                }
            }
            return ipnum;
        }

Download demo project : Lookup IP Address in Bulk.

 


Do you like this article? Share it with others by clicking the social media buttons below. We will write more articles related to this topic.