Display Visitor's Local Time Using C-Sharp and MySQL Database

In this tutorial, we demonstrate you on how to display visitor's local time based on their IP address 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 at Display visitors local time using C sharp and include into your C Sharp project.

Retrieving country and timezone data from MySQL Database

//Retrieve data from MySQL Database
        protected static string Get_Data(string query)
        {
			//Statement to connect with MySQL Database, replace lower case with actual MySQL configuration 
            string db_con_string = "SERVER=server;DATABASE=database;UID=username;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);
							//Assigning data from database into a string
                            object timezone = dt.Rows[0]["time_zone"];
							object country_code = dt.Rows[0]["country_code"];
                            return Convert.ToString(timezone) + "," + Convert.ToString(country_code);
                        }
                    }
                }
            }
 

Converting IP address to IP number

			protected 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] ;
        }

Retrieving UTC time

			 protected string utc()
        {
            int utc_h = DateTime.Now.Hour - 8;
            int min = DateTime.Now.Minute;
            if (min < 10)
            {
                string minute = "0" + min;
                return utc_h + ":" + minute;
            }
            else
            {
                return utc_h + ":" + DateTime.Now.Minute;
            }
        }

Retrieving client time

			 protected string client(string time_zone)
        {
            string utc_time = utc();
            string[] separator = { ":" };
            string[] time = time_zone.Split(separator, StringSplitOptions.None);
            string[] utc_h = utc_time.Split(separator, StringSplitOptions.None);
            int client_h = Convert.ToInt32(utc_h[0]) + (Convert.ToInt32(time[0]));

            if (client_h >= 24)
            {
                client_h = client_h - 24;
            }
            else if (client_h < 0)
            {
                client_h = client_h + 24;
            }
			
			if (DateTime.Now.Minute < 10)
			{
				return client_h + ":0" + DateTime.Now.Minute;
			}
			else
			{
				return client_h + ":" + DateTime.Now.Minute;
			}
        }

 

Download demo project : Display visitors local time.

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 at Display visitors local time using C sharp and include into your C Sharp project.

Retrieving country and timezone data from MySQL Database

//Retrieve data from MySQL Database
        protected static string Get_Data(string query)
        {
			//Statement to connect with MySQL Database, replace lower case with actual MySQL configuration 
            string db_con_string = "SERVER=server;DATABASE=database;UID=username;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);
							//Assigning data from database into a string
                            object timezone = dt.Rows[0]["time_zone"];
							object country_code = dt.Rows[0]["country_code"];
                            return Convert.ToString(timezone) + "," + Convert.ToString(country_code);
                        }
                    }
                }
            }
        }
 

Converting IP address to IP number

			protected 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;
        }

Retrieving UTC time

			 protected string utc()
        {
            int utc_h = DateTime.Now.Hour - 8;
            int min = DateTime.Now.Minute;
            if (min < 10)
            {
                string minute = "0" + min;
                return utc_h + ":" + minute;
            }
            else
            {
                return utc_h + ":" + DateTime.Now.Minute;
            }
        }

Retrieving client time

			 protected string client(string time_zone)
        {
            string utc_time = utc();
            string[] separator = { ":" };
            string[] time = time_zone.Split(separator, StringSplitOptions.None);
            string[] utc_h = utc_time.Split(separator, StringSplitOptions.None);
            int client_h = Convert.ToInt32(utc_h[0]) + (Convert.ToInt32(time[0]));

            if (client_h >= 24)
            {
                client_h = client_h - 24;
            }
            else if (client_h < 0)
            {
                client_h = client_h + 24;
            }
			
			if (DateTime.Now.Minute < 10)
			{
				return client_h + ":0" + DateTime.Now.Minute;
			}
			else
			{
				return client_h + ":" + DateTime.Now.Minute;
			}
        }

 

Download demo project : Display visitors local time.


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.