private int GetUserId(string email, string password)
        {
            string          ConnectionString = ConfigurationManager.ConnectionStrings["Mysql"].ConnectionString;
            MySqlConnection connection       = new MySqlConnection(ConnectionString);
            MySqlCommand    sql;

            connection.Open();
            int userId = 0;

            try
            {
                sql             = connection.CreateCommand();
                sql.CommandText = "Select UserId,FullName,Role,Email From Users WHERE Email='" + email + "'and Password='******'";
                MySqlDataReader reader = sql.ExecuteReader();

                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        userId = Convert.ToInt32(reader["UserId"]);
                    }
                    return(userId);
                }
                else
                {
                    return(0);
                }
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (connection.State == ConnectionState.Open)
                {
                    connection.Close();
                }
            }
        }
        public Response AddUser(Registration register)
        {
            string          ConnectionString = ConfigurationManager.ConnectionStrings["Mysql"].ConnectionString;
            MySqlConnection connection       = new MySqlConnection(ConnectionString);
            MySqlCommand    sql;

            connection.Open();
            try
            {
                sql             = connection.CreateCommand();
                sql.CommandText = "INSERT INTO Users(FullName,Email,Password,Role,Cell_Number,IDNumber,Province,City,Street,ZipCode,isActive,YearDOB,DateMonth,onMobile,isDetails,registerXP,mobileXP) Values('" + register.FullName + "','" + register.Email + "','" + SaveMD5.HashPassword
                                      (register.Password) + "','" + register.Role + "','Unknown','Unknown','Unknown','Unknown','Unknown','Unknown',1,'Unknown','Unknown',0,0,25,25)";
                int row = sql.ExecuteNonQuery();
                if (row > 0)
                {
                    int userId = GetUserId(register.Email, register.Password);
                    AddRewards(userId);
                }
                return(new Response {
                    status = "200", message = "OK"
                });
            }
            catch (Exception ex)
            {
                return(new Response {
                    status = "401", message = ex.Message.ToString()
                });
            }
            finally
            {
                if (connection.State == ConnectionState.Open)
                {
                    connection.Close();
                }
            }
        }
        public List <LoggedIn> Login(Login user)
        {
            List <LoggedIn> loggedIns        = new List <LoggedIn>();
            string          ConnectionString = ConfigurationManager.ConnectionStrings["Mysql"].ConnectionString;
            MySqlConnection connection       = new MySqlConnection(ConnectionString);
            MySqlCommand    sql;

            connection.Open();
            try
            {
                sql             = connection.CreateCommand();
                sql.CommandText = "Select UserId,FullName,Role,Email From Users WHERE Email='" + user.Email + "'and Password='******'";
                LoggedIn        logged = null;
                MySqlDataReader reader = sql.ExecuteReader();
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        logged          = new LoggedIn();
                        logged.id       = Convert.ToInt32(reader["UserId"]);
                        logged.usertype = reader["Role"].ToString();
                        logged.surname  = reader["FullName"].ToString();
                        logged.email    = reader["Email"].ToString();
                        loggedIns.Add(logged);
                    }
                    return(loggedIns);
                }
                else
                {
                    return(new List <LoggedIn>());
                }
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (connection.State == ConnectionState.Open)
                {
                    connection.Close();
                }
            }
        }