예제 #1
0
        private int GetCountUserState(string path, DateTime userInactiveSinceDate, string userName)
        {
            MySQLPersonalizationConnectionHelper connection = new MySQLPersonalizationConnectionHelper(connectionString);

            try
            {
                MySqlCommand cmd = new MySqlCommand();
                connection.OpenConnection(true);
                return(PersonalizationProviderProcedures.myaspnet_PersonalizationAdministration_GetCountOfState(
                           false, ApplicationName, ApplicationId, path,
                           userName, userInactiveSinceDate, connection));
            }
            catch (Exception ex)
            {
                if (writeExceptionsToEventLog)
                {
                    WriteToEventLog(ex, "MySQLPersonalizationProvider - GetCountUserState");
                }
                throw;
            }
            finally
            {
                connection.CloseConnection();
            }
        }
예제 #2
0
        private int ResetUserState(ResetUserStateMode mode, DateTime userInactiveSinceDate, string[] paths, string[] usernames)
        {
            var connection = new MySQLPersonalizationConnectionHelper(connectionString);

            connection.OpenConnection(true);

            try
            {
                if (ResetUserStateMode.PerInactiveDate == mode)
                {
                    return(PersonalizationProviderProcedures.my_aspnet_PersonalizationAdministration_ResetUserState(ApplicationId, userInactiveSinceDate.ToUniversalTime(), null, null, connection));
                }
                if (ResetUserStateMode.PerPaths == mode)
                {
                    return(PersonalizationProviderProcedures.my_aspnet_PersonalizationAdministration_ResetUserState(ApplicationId, userInactiveSinceDate, null, paths, connection));
                }
                else
                {
                    return(PersonalizationProviderProcedures.my_aspnet_PersonalizationAdministration_ResetUserState(ApplicationId, userInactiveSinceDate, usernames, paths, connection));
                }
            }
            catch (Exception ex)
            {
                if (writeExceptionsToEventLog)
                {
                    WriteToEventLog(ex, "MySQLPersonalizationProvider - ResetUserState");
                }
                throw;
            }
            finally
            {
                connection.CloseConnection();
            }
        }
예제 #3
0
        /// <summary>
        /// Deletes personalization state corresponding to a specified user and a specified page from the database.
        /// </summary>
        /// <param name="webPartManager">The web part manager.</param>
        /// <param name="path">The path indicating where to save the data.</param>
        /// <param name="userName">The user name.</param>
        protected override void ResetPersonalizationBlob(WebPartManager webPartManager, string path, string userName)
        {
            MySQLPersonalizationConnectionHelper connection = new MySQLPersonalizationConnectionHelper(connectionString);

            connection.OpenConnection(true);
            try
            {
                if (string.IsNullOrEmpty(userName))
                {
                    PersonalizationProviderProcedures.my_aspnet_PersonalizationAllUsers_ResetPageSettings(ApplicationId, path, connection);
                }
                else
                {
                    PersonalizationProviderProcedures.my_aspnet_PersonalizationPerUser_ResetPageSettings(ApplicationId, userName, path, DateTime.UtcNow, connection);
                }
            }
            catch (Exception ex)
            {
                if (writeExceptionsToEventLog)
                {
                    WriteToEventLog(ex, "MySQLPersonalizationProvider - ResetPersonalizationBlob");
                }
                throw;
            }
            finally
            {
                connection.CloseConnection();
            }
        }
예제 #4
0
        /// <summary>
        /// Retrieves personalization state as opaque blobs from the data source.
        /// </summary>
        /// <param name="webPartManager">The web part manager.</param>
        /// <param name="path">The path indicating where to save the data.</param>
        /// <param name="userName">The user name.</param>
        /// <param name="sharedDataBlob">A byte array containing the user shared data to loaded.</param>
        /// <param name="userDataBlob">A byte array containing the user data to be loaded.</param>
        /// <remarks>Retrieves both shared and user personalization state corresponding to a specified user and a specified page.</remarks>
        protected override void LoadPersonalizationBlobs(WebPartManager webPartManager, string path, string userName, ref Byte[] sharedDataBlob, ref Byte[] userDataBlob)
        {
            sharedDataBlob = null;
            userDataBlob   = null;
            MySQLPersonalizationConnectionHelper connection = new MySQLPersonalizationConnectionHelper(connectionString);

            connection.OpenConnection(true);
            try
            {
                sharedDataBlob = PersonalizationProviderProcedures.my_aspnet_PersonalizationAllUsers_GetPageSettings(
                    ApplicationId, path, connection);
                if (!String.IsNullOrEmpty(userName))
                {
                    userDataBlob = PersonalizationProviderProcedures.my_aspnet_PersonalizationPerUser_GetPageSettings(
                        ApplicationId, path, userName, DateTime.UtcNow, connection);
                }

                connection.CloseConnection();
            }
            catch (Exception ex)
            {
                if (writeExceptionsToEventLog)
                {
                    WriteToEventLog(ex, "MySQLPersonalizationProvider - LoadPersonazalitionBlobs");
                }
                throw;
            }
            finally
            {
                connection.CloseConnection();
            }
        }
예제 #5
0
        /// <summary>
        /// Writes personalization state corresponding to a specified user and a specified page as an opaque blob to the database.
        /// </summary>
        /// <param name="webPartManager">The web part manager.</param>
        /// <param name="path">The path indicating where to save the data.</param>
        /// <param name="userName">The user name.</param>
        /// <param name="dataBlob">A byte array containing the data to be saved.</param>
        /// <remarks>If userName is <c>null</c>, then the personalization state is shared state and is not keyed by user name.</remarks>
        protected override void SavePersonalizationBlob(WebPartManager webPartManager, string path, string userName, Byte[] dataBlob)
        {
            MySQLPersonalizationConnectionHelper connection = new MySQLPersonalizationConnectionHelper(connectionString);

            try
            {
                MySqlCommand cmd = new MySqlCommand();
                connection.OpenConnection(true);
                if (!string.IsNullOrEmpty(userName))
                {
                    PersonalizationProviderProcedures.my_aspnet_PersonalizationPerUser_SetPageSettings(ApplicationId, userName, path, dataBlob, DateTime.UtcNow, connection);
                }
                else
                {
                    PersonalizationProviderProcedures.my_aspnet_PersonalizationAllUsers_SetPageSettings(ApplicationId, path, dataBlob, DateTime.UtcNow, connection);
                }
            }
            catch (Exception ex)
            {
                if (writeExceptionsToEventLog)
                {
                    WriteToEventLog(ex, "MySQLPersonalizationProvider - SavePersonalizationBlob");
                }
                throw;
            }
        }
예제 #6
0
        /// <summary>
        /// Deletes personalization state corresponding to the specified users and specified pages from the database.
        /// </summary>
        /// <param name="scope">The personalization scope.</param>
        /// <param name="paths">The paths indicating where to save the data.</param>
        /// <param name="usernames">The user names.</param>
        /// <returns></returns>
        public override int ResetState(PersonalizationScope scope, string[] paths, string[] usernames)
        {
            bool hasPaths = !(paths == null || paths.Length == 0);
            bool hasUsers = !(usernames == null || usernames.Length == 0);

            var count = 0;

            var connection = new MySQLPersonalizationConnectionHelper(connectionString);

            connection.OpenConnection(true);

            if (scope == PersonalizationScope.Shared)
            {
                try
                {
                    if (paths == null) // reset all state
                    {
                        return(PersonalizationProviderProcedures.my_aspnet_PersonalizationAdministration_DeleteAllState(true, ApplicationId, connection));
                    }
                    else
                    {
                        return(PersonalizationProviderProcedures.my_aspnet_PersonalizationAdministration_ResetSharedState(ApplicationId, paths, connection));
                    }
                }
                catch (Exception ex)
                {
                    if (writeExceptionsToEventLog)
                    {
                        WriteToEventLog(ex, "MySQLPersonalizationProvider - ResetState");
                    }
                    throw;
                }
                finally
                {
                    connection.CloseConnection();
                }
            }
            else
            {
                DateTime dateTime = Convert.ToDateTime("2038-01-19 03:14:07.999999"); // MySQL TimeStamp MaxValue

                if (!hasPaths && !hasUsers)
                {
                    count = PersonalizationProviderProcedures.my_aspnet_PersonalizationAdministration_DeleteAllState(true, ApplicationId, connection);
                }
                else if (!hasUsers)
                {
                    count = ResetUserState(ResetUserStateMode.PerPaths, dateTime, paths, null);
                }
                else
                {
                    count = ResetUserState(ResetUserStateMode.PerUsers, dateTime, paths, usernames);
                }
            }
            return(count);
        }
예제 #7
0
        private PersonalizationStateInfoCollection FindSharedState(string path, int pageIndex, int pageSize, out int totalRecords)
        {
            MySQLPersonalizationConnectionHelper connection = new MySQLPersonalizationConnectionHelper(connectionString);

            try
            {
                MySqlCommand cmd = new MySqlCommand();
                connection.OpenConnection(true);
                totalRecords = PersonalizationProviderProcedures.myaspnet_PersonalizationAdministration_FindState(true, ApplicationId, ApplicationName, pageIndex, pageSize,
                                                                                                                  path, null, DateTime.MinValue, connection, ref cmd);

                PersonalizationStateInfoCollection sharedStateInfoCollection = new PersonalizationStateInfoCollection();

                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        string   pathQuery       = reader.GetString("Path");
                        DateTime lastUpdatedDate = (reader.IsDBNull(1)) ? DateTime.MinValue :
                                                   DateTime.SpecifyKind(reader.GetDateTime(1), DateTimeKind.Utc);
                        int size           = (reader.IsDBNull(2)) ? 0 : reader.GetInt32("SharedDataLength");
                        int userDataLength = (reader.IsDBNull(3)) ? 0 : reader.GetInt32("UserDataLength");
                        int userCount      = (reader.IsDBNull(4)) ? 0 : reader.GetInt32("UserCount");

                        sharedStateInfoCollection.Add(new SharedPersonalizationStateInfo(
                                                          pathQuery, lastUpdatedDate, size, userDataLength, userCount));
                    }
                }
                connection.CloseConnection();
                return(sharedStateInfoCollection);
            }
            catch (Exception ex)
            {
                if (writeExceptionsToEventLog)
                {
                    WriteToEventLog(ex, "MySQLPersonalizationProvider - FindSharedState");
                }
                throw;
            }
            finally
            {
                connection.CloseConnection();
            }
        }
예제 #8
0
        private PersonalizationStateInfoCollection FindUserState(string path, DateTime inactiveSinceDate, string userName, int pageIndex, int pageSize, out int totalRecords)
        {
            MySQLPersonalizationConnectionHelper connection = new MySQLPersonalizationConnectionHelper(connectionString);

            try
            {
                MySqlCommand cmd = new MySqlCommand();
                connection.OpenConnection(true);
                totalRecords = PersonalizationProviderProcedures.myaspnet_PersonalizationAdministration_FindState(false, ApplicationId, ApplicationName, pageIndex, pageSize,
                                                                                                                  path, userName, inactiveSinceDate, connection, ref cmd);

                PersonalizationStateInfoCollection stateInfoCollection = new PersonalizationStateInfoCollection();

                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        string   pathQuery        = reader.GetString("Path");
                        DateTime lastUpdatedDate  = DateTime.SpecifyKind(reader.GetDateTime("LastUpdatedDate"), DateTimeKind.Utc);
                        int      size             = reader.GetInt32("Size");
                        string   usernameQuery    = reader.GetString("name");
                        DateTime lastActivityDate = DateTime.SpecifyKind(reader.GetDateTime("LastActivityDate"), DateTimeKind.Utc);
                        stateInfoCollection.Add(new UserPersonalizationStateInfo(pathQuery, lastActivityDate, size, usernameQuery, lastActivityDate));
                    }
                }
                connection.CloseConnection();

                return(stateInfoCollection);
            }
            catch (Exception ex)
            {
                if (writeExceptionsToEventLog)
                {
                    WriteToEventLog(ex, "MySQLPersonalizationProvider - FindUserState");
                }
                throw;
            }
            finally
            {
                connection.CloseConnection();
            }
        }
    private PersonalizationStateInfoCollection FindSharedState(string path, int pageIndex, int pageSize, out int totalRecords)
    {
      MySQLPersonalizationConnectionHelper connection = new MySQLPersonalizationConnectionHelper(connectionString);

      try
      {
        MySqlCommand cmd = new MySqlCommand();
        connection.OpenConnection(true);
        totalRecords = PersonalizationProviderProcedures.myaspnet_PersonalizationAdministration_FindState(true, ApplicationId, ApplicationName, pageIndex, pageSize,
                       path, null, DateTime.MinValue, connection, ref cmd);

        PersonalizationStateInfoCollection sharedStateInfoCollection = new PersonalizationStateInfoCollection();

        using (var reader = cmd.ExecuteReader())
        {
          while (reader.Read())
          {
            string pathQuery = reader.GetString("Path");
            DateTime lastUpdatedDate = (reader.IsDBNull(1)) ? DateTime.MinValue :
                                         DateTime.SpecifyKind(reader.GetDateTime(1), DateTimeKind.Utc);
            int size = (reader.IsDBNull(2)) ? 0 : reader.GetInt32("SharedDataLength");
            int userDataLength = (reader.IsDBNull(3)) ? 0 : reader.GetInt32("UserDataLength");
            int userCount = (reader.IsDBNull(4)) ? 0 : reader.GetInt32("UserCount");

            sharedStateInfoCollection.Add(new SharedPersonalizationStateInfo(
                                    pathQuery, lastUpdatedDate, size, userDataLength, userCount));
          }
        }
        connection.CloseConnection();
        return sharedStateInfoCollection;
      }
      catch (Exception ex)
      {
        if (writeExceptionsToEventLog)
          WriteToEventLog(ex, "MySQLPersonalizationProvider - FindSharedState");        
        throw;
      }
      finally
      {
        connection.CloseConnection();
      }
    }
    /// <summary>
    /// Deletes personalization state corresponding to the specified users and specified pages from the database
    /// </summary>
    /// <param name="scope"></param>
    /// <param name="paths"></param>
    /// <param name="usernames"></param>
    /// <returns></returns>
    public override int ResetState(PersonalizationScope scope, string[] paths, string[] usernames)
    {      
     
      bool hasPaths = !(paths == null || paths.Length == 0);
      bool hasUsers = !(usernames == null || usernames.Length == 0);

      var count = 0;

      var connection = new MySQLPersonalizationConnectionHelper(connectionString);
      connection.OpenConnection(true);

      if (scope == PersonalizationScope.Shared)
      {        
        try
        {                
          if (paths == null) // reset all state
          {
            return PersonalizationProviderProcedures.my_aspnet_PersonalizationAdministration_DeleteAllState(true, ApplicationId, connection);
          }
          else
          {
            return PersonalizationProviderProcedures.my_aspnet_PersonalizationAdministration_ResetSharedState(ApplicationId, paths, connection);
          }
        }
        catch (Exception ex)
        {
          if (writeExceptionsToEventLog)
            WriteToEventLog(ex, "MySQLPersonalizationProvider - ResetState");        
          throw;
        }
        finally
        {
          connection.CloseConnection();
        }
      }
      else
      {
        if (!hasPaths && !hasUsers)
        {
          count = PersonalizationProviderProcedures.my_aspnet_PersonalizationAdministration_DeleteAllState(true, ApplicationId, connection);
        }
        else if (!hasUsers)
        {
          count = ResetUserState(ResetUserStateMode.PerPaths, DateTime.MaxValue, paths, null);
        }
        else
        {
          count = ResetUserState(ResetUserStateMode.PerUsers, DateTime.MaxValue, paths, usernames);
        }
      }
      return count;
    }
    /// <summary>
    /// Writes personalization state corresponding to a specified user and a specified page as an opaque blob to the database. 
    /// If userName is null, then the personalization state is shared state and is not keyed by user name.
    /// </summary>
    /// <param name="webPartManager"></param>
    /// <param name="path"></param>
    /// <param name="userName"></param>
    /// <param name="dataBlob"></param>
    protected override void SavePersonalizationBlob(WebPartManager webPartManager, string path, string userName, Byte[] dataBlob)
    {

      MySQLPersonalizationConnectionHelper connection = new MySQLPersonalizationConnectionHelper(connectionString);

      try
      {
        MySqlCommand cmd = new MySqlCommand();
        connection.OpenConnection(true);
        if (!string.IsNullOrEmpty(userName))
        {
            PersonalizationProviderProcedures.my_aspnet_PersonalizationPerUser_SetPageSettings(ApplicationId, userName, path, dataBlob, DateTime.UtcNow, connection);
        }
        else
        {                     
            PersonalizationProviderProcedures.my_aspnet_PersonalizationAllUsers_SetPageSettings(ApplicationId, path, dataBlob, DateTime.UtcNow, connection);          
        }
      }
      catch (Exception ex)
      {
        if (writeExceptionsToEventLog)
          WriteToEventLog(ex, "MySQLPersonalizationProvider - SavePersonalizationBlob");        
        throw;
      }     
    }
    internal static Byte[] my_aspnet_PersonalizationPerUser_GetPageSettings(long applicationId, string path, string userName, DateTime currentTimeUtc, MySQLPersonalizationConnectionHelper connection)
    {
      if (applicationId <= 0)
        return null;

      if (!connection.Opened)
        throw new Exception("Error: Connection should be open");

      //get pathid
      var cmd = new MySqlCommand("select pathId from my_aspnet_paths where applicationid = @ApplicationId and loweredpath = lower(@Path)", connection.Connection);
      cmd.Connection = connection.Connection;

      cmd.Parameters.AddWithValue("@ApplicationId", applicationId);
      cmd.Parameters.AddWithValue("@Path", path);

      var pathId = (cmd.ExecuteScalar() ?? "").ToString();

      if (string.IsNullOrEmpty(pathId))
        return null;

      cmd = new MySqlCommand("select Id from my_aspnet_users where applicationid = @ApplicationId and name =  @UserName", connection.Connection);
      cmd.Connection = connection.Connection;
      cmd.Parameters.AddWithValue("@ApplicationId", applicationId);
      cmd.Parameters.AddWithValue("@UserName", userName);


      var userId = (cmd.ExecuteScalar() ?? "").ToString();
      userId = string.IsNullOrEmpty(userId) ? "0" : userId;

      if (int.Parse(userId) == 0)
        return null;

      UpdateUserLastActiveDate(connection.Connection, int.Parse(userId), currentTimeUtc);      

      cmd = new MySqlCommand("select pagesettings from my_aspnet_personalizationperuser as peruser where peruser.pathid = @PathId and peruser.userid = @UserId");
      cmd.Connection = connection.Connection;
      cmd.Parameters.AddWithValue("@PathId", pathId);
      cmd.Parameters.AddWithValue("@UserId", userId);

      var reader = cmd.ExecuteReader();

      byte[] settings = null;
      while (reader.Read())
      {        
        int size = (int)reader.GetBytes(0, 0, null, 0, 0);
        settings = new byte[size];
        reader.GetBytes(0, 0, settings, 0, size);                
      }
      reader.Close();
      return settings;          
    }
 /// <summary>
 /// Deletes personalization state corresponding to a specified user and a specified page from the database.
 /// </summary>
 /// <param name="webPartManager"></param>
 /// <param name="path"></param>
 /// <param name="userName"></param>
 protected override void ResetPersonalizationBlob(WebPartManager webPartManager, string path,  string userName) 
 {
    MySQLPersonalizationConnectionHelper connection = new MySQLPersonalizationConnectionHelper(connectionString);
    connection.OpenConnection(true);
    try
    {
      if (string.IsNullOrEmpty(userName))
      {
        PersonalizationProviderProcedures.my_aspnet_PersonalizationAllUsers_ResetPageSettings(ApplicationId, path, connection);
      }
      else
      {
        PersonalizationProviderProcedures.my_aspnet_PersonalizationPerUser_ResetPageSettings(ApplicationId, userName, path, DateTime.UtcNow, connection);
      }
    }
    catch (Exception ex)
    {
      if (writeExceptionsToEventLog)
        WriteToEventLog(ex, "MySQLPersonalizationProvider - ResetPersonalizationBlob");        
      throw;
    }
    finally
    {
      connection.CloseConnection();       
    }
 }
 private int ResetUserState(ResetUserStateMode mode, DateTime userInactiveSinceDate, string[] paths,  string[] usernames)
 {
   var connection = new MySQLPersonalizationConnectionHelper(connectionString);
   connection.OpenConnection(true);
   
   try
   {
     if (ResetUserStateMode.PerInactiveDate == mode)
     {
       return PersonalizationProviderProcedures.my_aspnet_PersonalizationAdministration_ResetUserState(ApplicationId, userInactiveSinceDate.ToUniversalTime(), null, null, connection);        
     }
     if (ResetUserStateMode.PerPaths == mode)
     {
       return PersonalizationProviderProcedures.my_aspnet_PersonalizationAdministration_ResetUserState(ApplicationId, userInactiveSinceDate, null, paths, connection);
     }
     else
     {
       return PersonalizationProviderProcedures.my_aspnet_PersonalizationAdministration_ResetUserState(ApplicationId, userInactiveSinceDate, usernames, paths, connection);        
     }
   }
   catch (Exception ex)
   {
     if (writeExceptionsToEventLog)
       WriteToEventLog(ex, "MySQLPersonalizationProvider - ResetUserState");  
     throw;
   }
   finally
   {
     connection.CloseConnection(); 
   }
 }
    /// <summary>
    /// Saves per-user state for the specified page and the specified user in the my_aspnet_PersonalizationPerUser table.
    /// </summary>
    /// <returns></returns>
    internal static int my_aspnet_PersonalizationPerUser_SetPageSettings(long applicationId, string userName, string path, byte[] settings, DateTime currentTimeUtc, MySQLPersonalizationConnectionHelper connection)
    {
      if (applicationId <= 0)
        return 0;

      if (!connection.Opened)
        throw new Exception("Error: Connection should be open");

      var cmd = new MySqlCommand();
      cmd.Connection = connection.Connection;

      cmd.CommandText = "SELECT PathId FROM my_aspnet_paths WHERE ApplicationId = @ApplicationId AND LoweredPath = LOWER(@Path)";
      cmd.Parameters.AddWithValue("@ApplicationId", applicationId);
      cmd.Parameters.AddWithValue("@Path", path);
      var pathId = (string)cmd.ExecuteScalar();

      cmd.Parameters.Clear();

      if (pathId == null)
      {
        // create path        
        MySqlTransaction trans;
        trans = connection.Connection.BeginTransaction();

        try
        {                    
          cmd.Transaction = trans;
          cmd.CommandText = "INSERT INTO my_aspnet_paths (applicationId, pathId, path, loweredpath) values (@ApplicationId, @PathId, @Path, LOWER(@Path))";
          cmd.Parameters.AddWithValue("@ApplicationId", applicationId);
          cmd.Parameters.AddWithValue("@PathId", pathId = Guid.NewGuid().ToString());
          cmd.Parameters.AddWithValue("@Path", path);
          cmd.ExecuteNonQuery();
          trans.Commit();
        }
        catch
        {
          trans.Rollback();
          throw;
        }
      }

      cmd.Parameters.Clear();
      cmd.CommandText = "SELECT id FROM my_aspnet_users WHERE ApplicationId = @ApplicationId AND name = LOWER(@UserName)";
      cmd.Parameters.AddWithValue("@ApplicationId", applicationId);
      cmd.Parameters.AddWithValue("@UserName", userName);
      var userId = (cmd.ExecuteScalar() ?? "").ToString();

      userId = string.IsNullOrEmpty(userId) ? "0" : userId;

      // create user
      if (int.Parse(userId) == 0)
      {
        // create path        
        MySqlTransaction trans;
        trans = connection.Connection.BeginTransaction();

        try
        {
          cmd.Parameters.Clear();
          cmd.Transaction = trans;
          cmd.CommandText = "INSERT INTO my_aspnet_users (applicationId, name, isAnonymous, lastActivityDate) values (@ApplicationId, @UserName, false, @CurrentTimeUtc)";
          cmd.Parameters.AddWithValue("@ApplicationId", applicationId);
          cmd.Parameters.AddWithValue("@UserName", userName);
          cmd.Parameters.AddWithValue("@CurrentTimeUtc", DateTime.UtcNow);
          cmd.ExecuteNonQuery();
          trans.Commit();
        }
        catch
        {
          trans.Rollback();
          throw;
        }

          cmd.Parameters.Clear();
          cmd.CommandText = "SELECT Id from my_aspnet_users where applicationId = @ApplicationId and name = @UserName)";
          cmd.Parameters.AddWithValue("@ApplicationId", applicationId);
          cmd.Parameters.AddWithValue("@UserName", userName);
          userId = (string)cmd.ExecuteScalar();
   
      }
      var rows = UpdateUserLastActiveDate(connection.Connection, int.Parse(userId), DateTime.UtcNow);
      if (rows == 0)
        throw new Exception("User not found");

      cmd.Parameters.Clear();
      cmd.CommandText = "Select COUNT(*) from my_aspnet_personalizationperuser where userid = @UserId and pathId = @PathId";
      cmd.Parameters.AddWithValue("@UserId", userId);
      cmd.Parameters.AddWithValue("@PathId", pathId);
      if ((long)cmd.ExecuteScalar() > 0)
      {
        cmd.Parameters.Clear();
        cmd.CommandText = "UPDATE my_aspnet_personalizationperuser SET PageSettings = @PageSettings, LastUpdatedDate = @CurrentTimeUtc " +
                          "where userid = @UserId and pathId = @PathId";
        cmd.Parameters.AddWithValue("@UserId", userId);
        cmd.Parameters.AddWithValue("@PathId", pathId);
        cmd.Parameters.AddWithValue("@PageSettings", settings);
        cmd.Parameters.AddWithValue("@CurrentTimeUtc", DateTime.UtcNow);
        cmd.ExecuteNonQuery();
      }
      else {
        cmd.Parameters.Clear();
        cmd.CommandText = "INSERT INTO my_aspnet_personalizationperuser(applicationId, pathId, userId, pageSettings, lastUpdatedDate) VALUES(@applicationId, @PathId, @userId, @PageSettings, @LastUpdatedDate)";
        cmd.Parameters.AddWithValue("@applicationId", applicationId);
        cmd.Parameters.AddWithValue("@UserId", userId);
        cmd.Parameters.AddWithValue("@PathId", pathId);
        cmd.Parameters.AddWithValue("@PageSettings", settings);
        cmd.Parameters.AddWithValue("@LastUpdatedDate", DateTime.UtcNow);
        rows = cmd.ExecuteNonQuery();      
      }
      return rows;
    }
        internal static Byte[] my_aspnet_PersonalizationPerUser_GetPageSettings(long applicationId, string path, string userName, DateTime currentTimeUtc, MySQLPersonalizationConnectionHelper connection)
        {
            if (applicationId <= 0)
            {
                return(null);
            }

            if (!connection.Opened)
            {
                throw new Exception("Error: Connection should be open");
            }

            //get pathid
            var cmd = new MySqlCommand("select pathId from my_aspnet_paths where applicationid = @ApplicationId and loweredpath = lower(@Path)", connection.Connection);

            cmd.Connection = connection.Connection;

            cmd.Parameters.AddWithValue("@ApplicationId", applicationId);
            cmd.Parameters.AddWithValue("@Path", path);

            var pathId = (cmd.ExecuteScalar() ?? "").ToString();

            if (string.IsNullOrEmpty(pathId))
            {
                return(null);
            }

            cmd            = new MySqlCommand("select Id from my_aspnet_users where applicationid = @ApplicationId and name =  @UserName", connection.Connection);
            cmd.Connection = connection.Connection;
            cmd.Parameters.AddWithValue("@ApplicationId", applicationId);
            cmd.Parameters.AddWithValue("@UserName", userName);


            var userId = (cmd.ExecuteScalar() ?? "").ToString();

            userId = string.IsNullOrEmpty(userId) ? "0" : userId;

            if (int.Parse(userId, CultureInfo.InvariantCulture) == 0)
            {
                return(null);
            }

            UpdateUserLastActiveDate(connection.Connection, int.Parse(userId, CultureInfo.InvariantCulture), currentTimeUtc);

            cmd            = new MySqlCommand("select pagesettings from my_aspnet_personalizationperuser as peruser where peruser.pathid = @PathId and peruser.userid = @UserId");
            cmd.Connection = connection.Connection;
            cmd.Parameters.AddWithValue("@PathId", pathId);
            cmd.Parameters.AddWithValue("@UserId", userId);

            var reader = cmd.ExecuteReader();

            byte[] settings = null;
            while (reader.Read())
            {
                int size = (int)reader.GetBytes(0, 0, null, 0, 0);
                settings = new byte[size];
                reader.GetBytes(0, 0, settings, 0, size);
            }
            reader.Close();
            return(settings);
        }
        internal static void my_aspnet_PersonalizationPerUser_ResetPageSettings(long applicationId, string userName, string path, DateTime currentTimeUtc, MySQLPersonalizationConnectionHelper connection)
        {
            if (applicationId <= 0)
            {
                return;
            }

            if (!connection.Opened)
            {
                throw new Exception("Error: Connection should be open");
            }

            var cmd = new MySqlCommand("Select pathid from my_aspnet_paths as paths where paths.applicationid = @ApplicationId  and paths.loweredPath = lower(@Path)", connection.Connection);

            cmd.Parameters.AddWithValue("@ApplicationId", applicationId);
            cmd.Parameters.AddWithValue("@Path", path);
            var pathId = (string)cmd.ExecuteScalar();

            if (!string.IsNullOrEmpty(pathId))
            {
                cmd            = new MySqlCommand("select Id from my_aspnet_users where applicationid = @ApplicationId and name = @UserName", connection.Connection);
                cmd.Connection = connection.Connection;
                cmd.Parameters.AddWithValue("@ApplicationId", applicationId);
                cmd.Parameters.AddWithValue("@UserName", userName);

                var userId = (int)cmd.ExecuteScalar();
                if (userId != 0)
                {
                    var rows = UpdateUserLastActiveDate(connection.Connection, userId, currentTimeUtc);
                    if (rows != 0)
                    {
                        cmd            = new MySqlCommand("delete from my_aspnet_personalizationperuser WHERE pathId = @PathId AND userId = @UserId");
                        cmd.Connection = connection.Connection;
                        cmd.Parameters.AddWithValue("@PathId", pathId);
                        cmd.Parameters.AddWithValue("@UserId", userId);
                        cmd.ExecuteNonQuery();
                    }
                }
            }
        }
    /// <summary>
    /// Retrieves profile data from my_aspnet_PersonalizationAllUsers or my_aspnet_PersonalizationPerUser meeting several input criteria.
    /// </summary>
    internal static int myaspnet_PersonalizationAdministration_FindState(bool allUsersScope, long applicationId, string applicationName, int pageIndex, int pageSize, string path, string userName, 
                                                                        DateTime inactiveSinceDate, MySQLPersonalizationConnectionHelper connection, ref MySqlCommand findStateCommand)
    {
      // create memory table to store results

      var sql = "CREATE TEMPORARY TABLE IF NOT EXISTS pageIndexResults(" +
                "IndexId int AUTO_INCREMENT NOT NULL PRIMARY KEY, " +
                "ItemId int not null)";

      if (!connection.Opened)
        throw new Exception("Error: Connection should be open");

      var cmd = new MySqlCommand(sql, connection.Connection);
      cmd.ExecuteNonQuery();

      //make sure table is empty      
      cmd.CommandText = "TRUNCATE TABLE pageIndexResults";
      cmd.Connection = connection.Connection;
      cmd.ExecuteNonQuery();

      int pageLowerBound = pageSize * pageIndex;
      int pageUpperBound = pageSize - 1 + pageLowerBound;


      if (allUsersScope)
      {

        var query = "INSERT INTO pageIndexResults (ItemId) (" +
          "SELECT myaspnet_Paths.PathId " +
                    "FROM myaspnet_Paths, " +
                    "((SELECT aspnet_Paths.PathId " +
                    "FROM myaspnet_PersonalizationAllUsers, aspnet_Paths " +
                    "WHERE myaspnet_Paths.ApplicationId = @ApplicationId " +
                    "AND aspnet_PersonalizationAllUsers.PathId = aspnet_Paths.PathId " +
                    "AND (@Path IS NULL OR aspnet_Paths.LoweredPath LIKE @Path) " +
                    ") AS SharedDataPerPath " +
                    "FULL OUTER JOIN " +
                    "(SELECT DISTINCT aspnet_Paths.PathId " +
                    "FROM my_aspnet_personalizationperuser, my_aspnet_paths " +
                    "WHERE my_aspnet_paths.ApplicationId = @ApplicationId " +
                    "AND my_aspnet_personalizationperuser.PathId = aspnet_Paths.PathId " +
                    "AND (@Path IS NULL OR my_aspnet_paths.LoweredPath LIKE @Path) " +
                    ") AS UserDataPerPath " +
                    "ON SharedDataPerPath.PathId = UserDataPerPath.PathId " +
                    ") " +
                    "WHERE my_aspnet_Paths.PathId = SharedDataPerPath.PathId OR my_aspnet_Paths.PathId = UserDataPerPath.PathId " +
                    "ORDER BY my_aspnet_Paths.Path ASC)";

        cmd.CommandText = query;
        cmd.Parameters.AddWithValue("@ApplicationId", applicationId);
        cmd.Parameters.AddWithValue("@Path", path);
        cmd.Connection = connection.Connection;
        cmd.ExecuteNonQuery();

        cmd.CommandText = "SELECT Count(PathId) FROM pageIndexResults";
        cmd.Connection = connection.Connection;
        int totalRecords = (int)cmd.ExecuteScalar();
      
        query = "SELECT my_aspnet_Paths.Path, " +
                    "SharedDataPerPath.LastUpdatedDate, " +
                    "SharedDataPerPath.SharedDataLength, " +
                    "UserDataPerPath.UserDataLength, " +
                    "UserDataPerPath.UserCount " +
                    "FROM aspnet_Paths, " +
                    "((SELECT pageIndexResults.ItemId AS PathId, " +
                    "aspnet_PersonalizationAllUsers.LastUpdatedDate AS LastUpdatedDate, " +
                    "LENGTH(aspnet_PersonalizationAllUsers.PageSettings) AS SharedDataLength " +
                    "FROM my_aspnet_personalizationallusers, PageIndex " +
                    "WHERE my_aspnet_personalizationallusers.PathId = pageIndexResults.IndexId " +
                    "AND pageIndexResults.IndexId >= @PageLowerBound AND pageIndexResults.IndexId <= @PageUpperBound " +
                    ") AS SharedDataPerPath " +
                    "FULL OUTER JOIN " +
                    "(SELECT pageIndexResults.ItemId AS PathId, " +
                    "SUM(LENGTH(my_aspnet_personalizationperuser.PageSettings)) AS UserDataLength, " +
                    "COUNT(*) AS UserCount " +
                    "FROM my_aspnet_personalizationperuser, pageIndexResults " +
                    "WHERE my_aspnet_personalizationperuser.PathId = pageIndexResults.IndexId " +
                    "AND pageIndexResults.IndexId >= @PageLowerBound AND pageIndexResults.IndexId <= @PageUpperBound " +
                    "GROUP BY pageIndexResults.IndexId " +
                    ") AS UserDataPerPath " +
                    "ON SharedDataPerPath.PathId = UserDataPerPath.PathId " +
                    ") " +
                    "WHERE aspnet_Paths.PathId = SharedDataPerPath.PathId OR aspnet_Paths.PathId = UserDataPerPath.PathId " +
                    "ORDER BY my_aspnet_Paths.Path ASC ";

        findStateCommand.CommandText = query;
        findStateCommand.Connection = connection.Connection;
        findStateCommand.Parameters.AddWithValue("@PageLowerBound", pageLowerBound);
        findStateCommand.Parameters.AddWithValue("@PageUpperBound", pageUpperBound);

        return totalRecords;
      }
      else
      {
        var query = "INSERT INTO pageIndexResults (ItemId) (" +
                   "SELECT my_aspnet_personalizationperuser.Id " +
                   "FROM my_aspnet_personalizationperuser, my_aspnet_users, my_aspnet_paths " +
                   "WHERE my_aspnet_paths.ApplicationId = @ApplicationId " +
                   "AND my_aspnet_personalizationperuser.UserId = my_aspnet_Users.Id " +
                   "AND my_aspnet_personalizationperuser.PathId = my_aspnet_Paths.PathId " +
                   "AND (@Path IS NULL OR my_aspnet_paths.LoweredPath LIKE @Path) " +
                   "AND (@UserName IS NULL OR my_aspnet_users.name LIKE @UserName) " +
                   "AND (@InactiveSinceDate IS NULL OR my_aspnet_users.LastActivityDate <= @InactiveSinceDate) " +
                   "ORDER BY my_aspnet_paths.Path ASC, my_aspnet_users.name ASC )";
        
        cmd.CommandText = query;
        cmd.Parameters.AddWithValue("@ApplicationId", applicationId);
        cmd.Parameters.AddWithValue("@Path", path);
        cmd.Parameters.AddWithValue("@UserName", userName);
        cmd.Parameters.AddWithValue("@InactiveSinceDate", inactiveSinceDate);
        cmd.Connection = connection.Connection;
        cmd.ExecuteNonQuery();

        cmd.CommandText = "SELECT Count(IndexId) FROM pageIndexResults";
        cmd.Connection = connection.Connection;
        var totalRecords = cmd.ExecuteScalar().ToString();

        query = "SELECT my_aspnet_Paths.Path, my_aspnet_personalizationperuser.LastUpdatedDate, LENGTH(my_aspnet_personalizationperuser.PageSettings) as Size, my_aspnet_Users.Name, my_aspnet_Users.LastActivityDate " +
                    "FROM my_aspnet_personalizationperuser, my_aspnet_users, my_aspnet_paths, pageIndexResults " +
                    "WHERE my_aspnet_personalizationperuser.Id = PageIndexResults.IndexId " +
                    "AND my_aspnet_personalizationperuser.UserId = my_aspnet_users.Id " +
                    "AND my_aspnet_personalizationperuser.PathId = my_aspnet_paths.PathId " +
                    "AND pageIndexResults.ItemId >= @PageLowerBound AND PageIndexResults.ItemId <= @PageUpperBound " +
                    "ORDER BY my_aspnet_paths.Path ASC, my_aspnet_users.name ASC ";

        findStateCommand.CommandText = query;
        findStateCommand.Parameters.AddWithValue("@PageUpperBound", pageUpperBound);
        findStateCommand.Parameters.AddWithValue("@PageLowerBound", pageLowerBound);
        findStateCommand.Connection = connection.Connection;

        return int.Parse(totalRecords);
      }
    }
    internal static void my_aspnet_PersonalizationAllUsers_ResetPageSettings(long applicationId, string path, MySQLPersonalizationConnectionHelper connection)
    {

      if (applicationId <= 0)
        return;

      if (!connection.Opened)
        throw new Exception("Error: Connection should be open");

      var cmd = new MySqlCommand("Select pathid from my_aspnet_paths as paths where paths.applicationid = @ApplicationId  and paths.loweredPath = lower(@Path)", connection.Connection);
      cmd.Parameters.AddWithValue("@ApplicationId", applicationId);
      cmd.Parameters.AddWithValue("@Path", path);
      var pathId = (cmd.ExecuteScalar() ?? "").ToString();

      if (!string.IsNullOrEmpty(pathId))
      {
        cmd = new MySqlCommand("delete my_aspnet_personalizationallusers.* from my_aspnet_personalizationallusers WHERE pathId = @PathId");
        cmd.Connection = connection.Connection;
        cmd.Parameters.AddWithValue("@PathId", pathId);        
        cmd.ExecuteNonQuery();          
      }

    }
    internal static int my_aspnet_PersonalizationAdministration_ResetUserState(long applicationId, DateTime inactiveSinceDate,string[] usernames, string[] paths, MySQLPersonalizationConnectionHelper connection)
    {
      if (applicationId <= 0)
        return 0;

      if (!connection.Opened)
        throw new Exception("Error: Connection should be open");

      var rows = 0;
      var cmd = new MySqlCommand();
      if (usernames == null) usernames = new string[1] { "" };
      if (paths == null ) paths = new string[1] { "" };

      foreach (var username in usernames)
      {
        foreach (var path in paths)
        {
          var query = "DELETE peruser.* FROM (my_aspnet_personalizationperuser as peruser  " +
                            "INNER JOIN my_aspnet_users as users ON " +
                            "peruser.userid = users.id) " +
                            "INNER JOIN my_aspnet_paths as paths ON " +
                            "paths.applicationId = @ApplicationId AND " +
                            "paths.pathid = peruser.pathid AND " +
                            "(@InactiveSinceDate is null OR users.lastactivitydate <= @InactiveSinceDate) ";
          query = string.IsNullOrEmpty(username) ? query : query += " AND (@UserName is null OR users.name = @UserName)  ";
          query = string.IsNullOrEmpty(path) ? query : query +=  " AND (@Path is null OR paths.loweredpath = LOWER(@Path))";          
          cmd.CommandText = query;
          cmd.Parameters.AddWithValue("@ApplicationId", applicationId);
          cmd.Parameters.AddWithValue("@InactiveSinceDate", inactiveSinceDate);
          cmd.Parameters.AddWithValue("@Path", path);
          cmd.Parameters.AddWithValue("@UserName", username);
          cmd.Connection = connection.Connection;
          rows += cmd.ExecuteNonQuery();
        }
      }
      
      return rows;
    }
    internal static int my_aspnet_PersonalizationAdministration_ResetSharedState(long applicationId, string[] paths, MySQLPersonalizationConnectionHelper connection)
    {
     if (applicationId <= 0)
        return 0;

      if (!connection.Opened)
        throw new Exception("Error: Connection should be open");

      if (paths == null)
        return 0;

      var cmd = new MySqlCommand();
      cmd.Connection = connection.Connection;

      var rows = 0;

      foreach (var path in paths)
      {
        cmd.CommandText = "DELETE my_aspnet_personalizationallusers.* FROM my_aspnet_personalizationallusers " +
                           "INNER JOIN my_aspnet_paths as paths ON " +
                           "((paths.ApplicationId = @ApplicationId AND " +
                           "my_aspnet_personalizationallusers.PathId = paths.PathId) AND " +
                           "paths.loweredpath = LOWER(@Path))";
                           
        cmd.Parameters.AddWithValue("@ApplicationId", applicationId);
        cmd.Parameters.AddWithValue("@Path", path);
        rows += cmd.ExecuteNonQuery();       
      }

      return rows;
    }
    /// <summary>
    /// Saves shared state for the specified page in the aspnet_PersonalizationAllUsers table
    /// </summary>
    /// <returns></returns>
    internal static int my_aspnet_PersonalizationAllUsers_SetPageSettings(long applicationId, string path, byte[] settings, DateTime currentTimeUtc, MySQLPersonalizationConnectionHelper connection)
    {
      if (applicationId <= 0)
        return 0;

      if (!connection.Opened)
        throw new Exception("Error: Connection should be open");

      var cmd = new MySqlCommand();
      cmd.Connection = connection.Connection;

      cmd.CommandText = "SELECT PathId FROM my_aspnet_paths WHERE ApplicationId = @ApplicationId AND LoweredPath = LOWER(@Path)";
      cmd.Parameters.AddWithValue("@ApplicationId", applicationId);
      cmd.Parameters.AddWithValue("@Path", path);
      var pathId = (string)cmd.ExecuteScalar();
      cmd.Parameters.Clear();

      if (pathId == null)
      {
        // create path        
        MySqlTransaction trans;
        trans = connection.Connection.BeginTransaction();

        try
        {          
          cmd.Transaction = trans;
          cmd.CommandText = "INSERT INTO my_aspnet_paths (applicationId, pathId, path, loweredpath) values (@ApplicationId, @PathId, @Path, LOWER(@Path))";
          cmd.Parameters.AddWithValue("@ApplicationId", applicationId);
          cmd.Parameters.AddWithValue("@PathId", pathId = Guid.NewGuid().ToString());
          cmd.Parameters.AddWithValue("@Path", path);
          cmd.ExecuteNonQuery();
          trans.Commit();
        }
        catch
        {
          trans.Rollback();
          throw;
        }
      }
      
      cmd.CommandText = "INSERT INTO my_aspnet_personalizationallusers(PathId, PageSettings, LastUpdatedDate) VALUES (@PathId, @PageSettings, @CurrentTimeUtc)";
      cmd.CommandText += " ON DUPLICATE KEY UPDATE PageSettings=Values(PageSettings), LastUpdatedDate=Values(LastUpdatedDate)";
      cmd.Parameters.Clear();
      cmd.Parameters.AddWithValue("@PageSettings", settings);
      cmd.Parameters.AddWithValue("@PathId", pathId);
      cmd.Parameters.AddWithValue("@CurrentTimeUtc", currentTimeUtc);
      var rows = cmd.ExecuteNonQuery();
      return rows;
    }
    private PersonalizationStateInfoCollection FindUserState(string path, DateTime inactiveSinceDate, string userName, int pageIndex, int pageSize, out int totalRecords)
    {
      MySQLPersonalizationConnectionHelper connection = new MySQLPersonalizationConnectionHelper(connectionString);
      
      try
      {

        MySqlCommand cmd = new MySqlCommand();
        connection.OpenConnection(true);
        totalRecords = PersonalizationProviderProcedures.myaspnet_PersonalizationAdministration_FindState(false, ApplicationId, ApplicationName, pageIndex, pageSize, 
                       path, userName, inactiveSinceDate, connection, ref cmd);

        PersonalizationStateInfoCollection stateInfoCollection = new PersonalizationStateInfoCollection();

        using(var reader = cmd.ExecuteReader())
        {          
          while (reader.Read())
          {
            string pathQuery = reader.GetString("Path");
            DateTime lastUpdatedDate = DateTime.SpecifyKind(reader.GetDateTime("LastUpdatedDate"), DateTimeKind.Utc);
            int size = reader.GetInt32("Size");
            string usernameQuery = reader.GetString("name");
            DateTime lastActivityDate = DateTime.SpecifyKind(reader.GetDateTime("LastActivityDate"), DateTimeKind.Utc);
            stateInfoCollection.Add(new UserPersonalizationStateInfo(pathQuery, lastActivityDate, size, usernameQuery, lastActivityDate));        
          }
        }       
        connection.CloseConnection();        

        return stateInfoCollection;
      }
      catch (Exception ex)
      {
        if (writeExceptionsToEventLog)
          WriteToEventLog(ex, "MySQLPersonalizationProvider - FindUserState");        
        throw;
      }
      finally
      {
        connection.CloseConnection();
      }
    }  
        /// <summary>
        /// Retrieves profile data from my_aspnet_PersonalizationAllUsers or my_aspnet_PersonalizationPerUser meeting several input criteria.
        /// </summary>
        internal static int myaspnet_PersonalizationAdministration_FindState(bool allUsersScope, long applicationId, string applicationName, int pageIndex, int pageSize, string path, string userName,
                                                                             DateTime inactiveSinceDate, MySQLPersonalizationConnectionHelper connection, ref MySqlCommand findStateCommand)
        {
            // create memory table to store results

            var sql = "CREATE TEMPORARY TABLE IF NOT EXISTS pageIndexResults(" +
                      "IndexId int AUTO_INCREMENT NOT NULL PRIMARY KEY, " +
                      "ItemId int not null)";

            if (!connection.Opened)
            {
                throw new Exception("Error: Connection should be open");
            }

            var cmd = new MySqlCommand(sql, connection.Connection);

            cmd.ExecuteNonQuery();

            //make sure table is empty
            cmd.CommandText = "TRUNCATE TABLE pageIndexResults";
            cmd.Connection  = connection.Connection;
            cmd.ExecuteNonQuery();

            int pageLowerBound = pageSize * pageIndex;
            int pageUpperBound = pageSize - 1 + pageLowerBound;


            if (allUsersScope)
            {
                var query = "INSERT INTO pageIndexResults (ItemId) (" +
                            "SELECT myaspnet_Paths.PathId " +
                            "FROM myaspnet_Paths, " +
                            "((SELECT aspnet_Paths.PathId " +
                            "FROM myaspnet_PersonalizationAllUsers, aspnet_Paths " +
                            "WHERE myaspnet_Paths.ApplicationId = @ApplicationId " +
                            "AND aspnet_PersonalizationAllUsers.PathId = aspnet_Paths.PathId " +
                            "AND (@Path IS NULL OR aspnet_Paths.LoweredPath LIKE @Path) " +
                            ") AS SharedDataPerPath " +
                            "FULL OUTER JOIN " +
                            "(SELECT DISTINCT aspnet_Paths.PathId " +
                            "FROM my_aspnet_personalizationperuser, my_aspnet_paths " +
                            "WHERE my_aspnet_paths.ApplicationId = @ApplicationId " +
                            "AND my_aspnet_personalizationperuser.PathId = aspnet_Paths.PathId " +
                            "AND (@Path IS NULL OR my_aspnet_paths.LoweredPath LIKE @Path) " +
                            ") AS UserDataPerPath " +
                            "ON SharedDataPerPath.PathId = UserDataPerPath.PathId " +
                            ") " +
                            "WHERE my_aspnet_Paths.PathId = SharedDataPerPath.PathId OR my_aspnet_Paths.PathId = UserDataPerPath.PathId " +
                            "ORDER BY my_aspnet_Paths.Path ASC)";

                cmd.CommandText = query;
                cmd.Parameters.AddWithValue("@ApplicationId", applicationId);
                cmd.Parameters.AddWithValue("@Path", path);
                cmd.Connection = connection.Connection;
                cmd.ExecuteNonQuery();

                cmd.CommandText = "SELECT Count(PathId) FROM pageIndexResults";
                cmd.Connection  = connection.Connection;
                int totalRecords = (int)cmd.ExecuteScalar();

                query = "SELECT my_aspnet_Paths.Path, " +
                        "SharedDataPerPath.LastUpdatedDate, " +
                        "SharedDataPerPath.SharedDataLength, " +
                        "UserDataPerPath.UserDataLength, " +
                        "UserDataPerPath.UserCount " +
                        "FROM aspnet_Paths, " +
                        "((SELECT pageIndexResults.ItemId AS PathId, " +
                        "aspnet_PersonalizationAllUsers.LastUpdatedDate AS LastUpdatedDate, " +
                        "LENGTH(aspnet_PersonalizationAllUsers.PageSettings) AS SharedDataLength " +
                        "FROM my_aspnet_personalizationallusers, PageIndex " +
                        "WHERE my_aspnet_personalizationallusers.PathId = pageIndexResults.IndexId " +
                        "AND pageIndexResults.IndexId >= @PageLowerBound AND pageIndexResults.IndexId <= @PageUpperBound " +
                        ") AS SharedDataPerPath " +
                        "FULL OUTER JOIN " +
                        "(SELECT pageIndexResults.ItemId AS PathId, " +
                        "SUM(LENGTH(my_aspnet_personalizationperuser.PageSettings)) AS UserDataLength, " +
                        "COUNT(*) AS UserCount " +
                        "FROM my_aspnet_personalizationperuser, pageIndexResults " +
                        "WHERE my_aspnet_personalizationperuser.PathId = pageIndexResults.IndexId " +
                        "AND pageIndexResults.IndexId >= @PageLowerBound AND pageIndexResults.IndexId <= @PageUpperBound " +
                        "GROUP BY pageIndexResults.IndexId " +
                        ") AS UserDataPerPath " +
                        "ON SharedDataPerPath.PathId = UserDataPerPath.PathId " +
                        ") " +
                        "WHERE aspnet_Paths.PathId = SharedDataPerPath.PathId OR aspnet_Paths.PathId = UserDataPerPath.PathId " +
                        "ORDER BY my_aspnet_Paths.Path ASC ";

                findStateCommand.CommandText = query;
                findStateCommand.Connection  = connection.Connection;
                findStateCommand.Parameters.AddWithValue("@PageLowerBound", pageLowerBound);
                findStateCommand.Parameters.AddWithValue("@PageUpperBound", pageUpperBound);

                return(totalRecords);
            }
            else
            {
                var query = "INSERT INTO pageIndexResults (ItemId) (" +
                            "SELECT my_aspnet_personalizationperuser.Id " +
                            "FROM my_aspnet_personalizationperuser, my_aspnet_users, my_aspnet_paths " +
                            "WHERE my_aspnet_paths.ApplicationId = @ApplicationId " +
                            "AND my_aspnet_personalizationperuser.UserId = my_aspnet_Users.Id " +
                            "AND my_aspnet_personalizationperuser.PathId = my_aspnet_Paths.PathId " +
                            "AND (@Path IS NULL OR my_aspnet_paths.LoweredPath LIKE @Path) " +
                            "AND (@UserName IS NULL OR my_aspnet_users.name LIKE @UserName) " +
                            "AND (@InactiveSinceDate IS NULL OR my_aspnet_users.LastActivityDate <= @InactiveSinceDate) " +
                            "ORDER BY my_aspnet_paths.Path ASC, my_aspnet_users.name ASC )";

                cmd.CommandText = query;
                cmd.Parameters.AddWithValue("@ApplicationId", applicationId);
                cmd.Parameters.AddWithValue("@Path", path);
                cmd.Parameters.AddWithValue("@UserName", userName);
                cmd.Parameters.AddWithValue("@InactiveSinceDate", inactiveSinceDate);
                cmd.Connection = connection.Connection;
                cmd.ExecuteNonQuery();

                cmd.CommandText = "SELECT Count(IndexId) FROM pageIndexResults";
                cmd.Connection  = connection.Connection;
                var totalRecords = cmd.ExecuteScalar().ToString();

                query = "SELECT my_aspnet_Paths.Path, my_aspnet_personalizationperuser.LastUpdatedDate, LENGTH(my_aspnet_personalizationperuser.PageSettings) as Size, my_aspnet_Users.Name, my_aspnet_Users.LastActivityDate " +
                        "FROM my_aspnet_personalizationperuser, my_aspnet_users, my_aspnet_paths, pageIndexResults " +
                        "WHERE my_aspnet_personalizationperuser.Id = PageIndexResults.IndexId " +
                        "AND my_aspnet_personalizationperuser.UserId = my_aspnet_users.Id " +
                        "AND my_aspnet_personalizationperuser.PathId = my_aspnet_paths.PathId " +
                        "AND pageIndexResults.ItemId >= @PageLowerBound AND PageIndexResults.ItemId <= @PageUpperBound " +
                        "ORDER BY my_aspnet_paths.Path ASC, my_aspnet_users.name ASC ";

                findStateCommand.CommandText = query;
                findStateCommand.Parameters.AddWithValue("@PageUpperBound", pageUpperBound);
                findStateCommand.Parameters.AddWithValue("@PageLowerBound", pageLowerBound);
                findStateCommand.Connection = connection.Connection;

                return(int.Parse(totalRecords, CultureInfo.InvariantCulture));
            }
        }
 private int GetCountUserState(string path, DateTime userInactiveSinceDate, string userName)
 {
   MySQLPersonalizationConnectionHelper connection = new MySQLPersonalizationConnectionHelper(connectionString);      
   try
   {
     MySqlCommand cmd = new MySqlCommand();
     connection.OpenConnection(true);
     return PersonalizationProviderProcedures.myaspnet_PersonalizationAdministration_GetCountOfState(
                                              false, ApplicationName, ApplicationId, path,
                                              userName, userInactiveSinceDate, connection);
   }
   catch (Exception ex)
   {
     if (writeExceptionsToEventLog)
       WriteToEventLog(ex, "MySQLPersonalizationProvider - GetCountUserState");        
     throw;
   }
   finally 
   {
     connection.CloseConnection();      
   }
 }
    internal static void my_aspnet_PersonalizationPerUser_ResetPageSettings(long applicationId, string userName, string path, DateTime currentTimeUtc, MySQLPersonalizationConnectionHelper connection)
    {
      
      if (applicationId <= 0)
        return;

      if (!connection.Opened)
        throw new Exception("Error: Connection should be open");

      var cmd = new MySqlCommand("Select pathid from my_aspnet_paths as paths where paths.applicationid = @ApplicationId  and paths.loweredPath = lower(@Path)", connection.Connection);
      cmd.Parameters.AddWithValue("@ApplicationId", applicationId);
      cmd.Parameters.AddWithValue("@Path", path);
      var pathId = (string)cmd.ExecuteScalar();

      if (!string.IsNullOrEmpty(pathId))
      {
        cmd = new MySqlCommand("select Id from my_aspnet_users where applicationid = @ApplicationId and name = @UserName", connection.Connection);
        cmd.Connection = connection.Connection;
        cmd.Parameters.AddWithValue("@ApplicationId", applicationId);
        cmd.Parameters.AddWithValue("@UserName", userName);

        var userId = (int)cmd.ExecuteScalar();
        if (userId != 0)
        {
          var rows = UpdateUserLastActiveDate(connection.Connection, userId, currentTimeUtc);          
          if (rows != 0)
          {
            cmd = new MySqlCommand("delete from my_aspnet_personalizationperuser WHERE pathId = @PathId AND userId = @UserId");
            cmd.Connection = connection.Connection;
            cmd.Parameters.AddWithValue("@PathId", pathId);
            cmd.Parameters.AddWithValue("@UserId", userId);
            cmd.ExecuteNonQuery();          
          }
        }
      }    
    }
        internal static int myaspnet_PersonalizationAdministration_GetCountOfState(bool allUsersScope, string applicationName, long applicationId, string path, string userName, DateTime inactiveSinceDate, MySQLPersonalizationConnectionHelper connection)
        {
            if (applicationId <= 0)
            {
                return(0);
            }

            if (!connection.Opened)
            {
                throw new Exception("Error: Connection should be open");
            }

            if (allUsersScope)
            {
                MySqlCommand cmd = new MySqlCommand("Select count(*) from my_aspnet_personalizationallusers, my_aspnet_paths " +
                                                    "where my_aspnet_paths.applicationId = @ApplicationId and " +
                                                    "my_aspnet_personalizationallusers.pathid = my_aspnet_paths.pathid and " +
                                                    "(@Path is null or my_aspnet_paths.loweredpath like lower(@Path))", connection.Connection);

                cmd.Parameters.AddWithValue("@ApplicationId", applicationId);
                cmd.Parameters.AddWithValue("@Path", path);
                cmd.Connection = connection.Connection;
                var count = cmd.ExecuteScalar().ToString();
                return(int.Parse(count, CultureInfo.InvariantCulture));
            }
            else
            {
                MySqlCommand cmd = new MySqlCommand("Select count(*) from my_aspnet_personalizationperuser as peruser, my_aspnet_users as users, " +
                                                    "my_aspnet_paths as paths " +
                                                    "where paths.applicationId = @ApplicationId and " +
                                                    "peruser.userid = users.id and " +
                                                    "peruser.pathId = paths.pathId and " +
                                                    "(@Path is null or paths.loweredpath like lower(@Path) and " +
                                                    "(@UserName is null or users.name like lower(@UserName))) and " +
                                                    "(@InactiveSinceDate is null or users.lastactivitydate <= @InactiveSinceDate) ", connection.Connection);

                cmd.Parameters.AddWithValue("@ApplicationId", applicationId);
                cmd.Parameters.AddWithValue("@Path", path);
                cmd.Parameters.AddWithValue("@UserName", userName);
                cmd.Parameters.AddWithValue("@InactiveSinceDate", inactiveSinceDate);
                cmd.Connection = connection.Connection;
                var count = cmd.ExecuteScalar().ToString();
                return(int.Parse(count, CultureInfo.InvariantCulture));
            }
        }
    internal static int my_aspnet_PersonalizationAdministration_DeleteAllState(bool allUsersScope, long applicationId, MySQLPersonalizationConnectionHelper connection)
    {
      if (applicationId <= 0)
        return 0;

      if (!connection.Opened)
        throw new Exception("Error: Connection should be open");
      
      var cmd = new MySqlCommand();
      cmd.Connection = connection.Connection;

      if (allUsersScope)
      {

        cmd.CommandText = "DELETE FROM my_aspnet_personalizationallusers " +
                          "WHERE PathId IN " +
                          "(SELECT paths.PathId FROM my_aspnet_paths as paths " +
                            "WHERE paths.ApplicationId = @ApplicationId)";        
      }
      else
      { 
        cmd.CommandText = "DELETE FROM my_aspnet_personalizationperuser " +
                          "WHERE PathId IN " +
                          "(SELECT Paths.PathId FROM my_aspnet_paths Paths " +
                          "WHERE Paths.ApplicationId = @ApplicationId)";     
      }
      cmd.Parameters.AddWithValue("@ApplicationId", applicationId);
      var rows = cmd.ExecuteNonQuery();
      return rows;
    }
        internal static byte[] my_aspnet_PersonalizationAllUsers_GetPageSettings(long applicationId, string path, MySQLPersonalizationConnectionHelper connection)
        {
            if (applicationId <= 0)
            {
                return(null);
            }

            if (!connection.Opened)
            {
                throw new Exception("Error: Connection should be open");
            }

            var cmd = new MySqlCommand("Select pathid from my_aspnet_paths as paths where paths.applicationid = @ApplicationId  and paths.loweredPath = lower(@Path)", connection.Connection);

            cmd.Parameters.AddWithValue("@ApplicationId", applicationId);
            cmd.Parameters.AddWithValue("@Path", path);
            var pathId = (string)cmd.ExecuteScalar();

            if (!string.IsNullOrEmpty(pathId))
            {
                cmd.CommandText = "Select PageSettings from my_aspnet_personalizationallusers where pathId = @PathId";
                cmd.Parameters.AddWithValue("@PathId", pathId);
                cmd.Connection = connection.Connection;
                var reader = cmd.ExecuteReader();

                byte[] settings = null;
                while (reader.Read())
                {
                    int size = (int)reader.GetBytes(0, 0, null, 0, 0);
                    settings = new byte[size];
                    reader.GetBytes(0, 0, settings, 0, size);
                }
                reader.Close();
                return(settings);
            }

            return(null);
        }
        internal static int my_aspnet_PersonalizationAdministration_ResetSharedState(long applicationId, string[] paths, MySQLPersonalizationConnectionHelper connection)
        {
            if (applicationId <= 0)
            {
                return(0);
            }

            if (!connection.Opened)
            {
                throw new Exception("Error: Connection should be open");
            }

            if (paths == null)
            {
                return(0);
            }

            var cmd = new MySqlCommand();

            cmd.Connection = connection.Connection;

            var rows = 0;

            foreach (var path in paths)
            {
                cmd.CommandText = "DELETE my_aspnet_personalizationallusers.* FROM my_aspnet_personalizationallusers " +
                                  "INNER JOIN my_aspnet_paths as paths ON " +
                                  "((paths.ApplicationId = @ApplicationId AND " +
                                  "my_aspnet_personalizationallusers.PathId = paths.PathId) AND " +
                                  "paths.loweredpath = LOWER(@Path))";

                cmd.Parameters.AddWithValue("@ApplicationId", applicationId);
                cmd.Parameters.AddWithValue("@Path", path);
                rows += cmd.ExecuteNonQuery();
            }

            return(rows);
        }
        internal static void my_aspnet_PersonalizationAllUsers_ResetPageSettings(long applicationId, string path, MySQLPersonalizationConnectionHelper connection)
        {
            if (applicationId <= 0)
            {
                return;
            }

            if (!connection.Opened)
            {
                throw new Exception("Error: Connection should be open");
            }

            var cmd = new MySqlCommand("Select pathid from my_aspnet_paths as paths where paths.applicationid = @ApplicationId  and paths.loweredPath = lower(@Path)", connection.Connection);

            cmd.Parameters.AddWithValue("@ApplicationId", applicationId);
            cmd.Parameters.AddWithValue("@Path", path);
            var pathId = (cmd.ExecuteScalar() ?? "").ToString();

            if (!string.IsNullOrEmpty(pathId))
            {
                cmd            = new MySqlCommand("delete my_aspnet_personalizationallusers.* from my_aspnet_personalizationallusers WHERE pathId = @PathId");
                cmd.Connection = connection.Connection;
                cmd.Parameters.AddWithValue("@PathId", pathId);
                cmd.ExecuteNonQuery();
            }
        }
        /// <summary>
        /// Saves per-user state for the specified page and the specified user in the my_aspnet_PersonalizationPerUser table.
        /// </summary>
        /// <returns></returns>
        internal static int my_aspnet_PersonalizationPerUser_SetPageSettings(long applicationId, string userName, string path, byte[] settings, DateTime currentTimeUtc, MySQLPersonalizationConnectionHelper connection)
        {
            if (applicationId <= 0)
            {
                return(0);
            }

            if (!connection.Opened)
            {
                throw new Exception("Error: Connection should be open");
            }

            var cmd = new MySqlCommand();

            cmd.Connection = connection.Connection;

            cmd.CommandText = "SELECT PathId FROM my_aspnet_paths WHERE ApplicationId = @ApplicationId AND LoweredPath = LOWER(@Path)";
            cmd.Parameters.AddWithValue("@ApplicationId", applicationId);
            cmd.Parameters.AddWithValue("@Path", path);
            var pathId = (string)cmd.ExecuteScalar();

            cmd.Parameters.Clear();

            if (pathId == null)
            {
                // create path
                MySqlTransaction trans;
                trans = connection.Connection.BeginTransaction();

                try
                {
                    cmd.Transaction = trans;
                    cmd.CommandText = "INSERT INTO my_aspnet_paths (applicationId, pathId, path, loweredpath) values (@ApplicationId, @PathId, @Path, LOWER(@Path))";
                    cmd.Parameters.AddWithValue("@ApplicationId", applicationId);
                    cmd.Parameters.AddWithValue("@PathId", pathId = Guid.NewGuid().ToString());
                    cmd.Parameters.AddWithValue("@Path", path);
                    cmd.ExecuteNonQuery();
                    trans.Commit();
                }
                catch
                {
                    trans.Rollback();
                    throw;
                }
            }

            cmd.Parameters.Clear();
            cmd.CommandText = "SELECT id FROM my_aspnet_users WHERE ApplicationId = @ApplicationId AND name = LOWER(@UserName)";
            cmd.Parameters.AddWithValue("@ApplicationId", applicationId);
            cmd.Parameters.AddWithValue("@UserName", userName);
            var userId = (cmd.ExecuteScalar() ?? "").ToString();

            userId = string.IsNullOrEmpty(userId) ? "0" : userId;

            // create user
            if (int.Parse(userId, CultureInfo.InvariantCulture) == 0)
            {
                // create path
                MySqlTransaction trans;
                trans = connection.Connection.BeginTransaction();

                try
                {
                    cmd.Parameters.Clear();
                    cmd.Transaction = trans;
                    cmd.CommandText = "INSERT INTO my_aspnet_users (applicationId, name, isAnonymous, lastActivityDate) values (@ApplicationId, @UserName, false, @CurrentTimeUtc)";
                    cmd.Parameters.AddWithValue("@ApplicationId", applicationId);
                    cmd.Parameters.AddWithValue("@UserName", userName);
                    cmd.Parameters.AddWithValue("@CurrentTimeUtc", DateTime.UtcNow);
                    cmd.ExecuteNonQuery();
                    trans.Commit();
                }
                catch
                {
                    trans.Rollback();
                    throw;
                }

                cmd.Parameters.Clear();
                cmd.CommandText = "SELECT Id from my_aspnet_users where applicationId = @ApplicationId and name = @UserName)";
                cmd.Parameters.AddWithValue("@ApplicationId", applicationId);
                cmd.Parameters.AddWithValue("@UserName", userName);
                userId = (string)cmd.ExecuteScalar();
            }
            var rows = UpdateUserLastActiveDate(connection.Connection, int.Parse(userId, CultureInfo.InvariantCulture), DateTime.UtcNow);

            if (rows == 0)
            {
                throw new Exception("User not found");
            }

            cmd.Parameters.Clear();
            cmd.CommandText = "Select COUNT(*) from my_aspnet_personalizationperuser where userid = @UserId and pathId = @PathId";
            cmd.Parameters.AddWithValue("@UserId", userId);
            cmd.Parameters.AddWithValue("@PathId", pathId);
            if ((long)cmd.ExecuteScalar() > 0)
            {
                cmd.Parameters.Clear();
                cmd.CommandText = "UPDATE my_aspnet_personalizationperuser SET PageSettings = @PageSettings, LastUpdatedDate = @CurrentTimeUtc " +
                                  "where userid = @UserId and pathId = @PathId";
                cmd.Parameters.AddWithValue("@UserId", userId);
                cmd.Parameters.AddWithValue("@PathId", pathId);
                cmd.Parameters.AddWithValue("@PageSettings", settings);
                cmd.Parameters.AddWithValue("@CurrentTimeUtc", DateTime.UtcNow);
                cmd.ExecuteNonQuery();
            }
            else
            {
                cmd.Parameters.Clear();
                cmd.CommandText = "INSERT INTO my_aspnet_personalizationperuser(applicationId, pathId, userId, pageSettings, lastUpdatedDate) VALUES(@applicationId, @PathId, @userId, @PageSettings, @LastUpdatedDate)";
                cmd.Parameters.AddWithValue("@applicationId", applicationId);
                cmd.Parameters.AddWithValue("@UserId", userId);
                cmd.Parameters.AddWithValue("@PathId", pathId);
                cmd.Parameters.AddWithValue("@PageSettings", settings);
                cmd.Parameters.AddWithValue("@LastUpdatedDate", DateTime.UtcNow);
                rows = cmd.ExecuteNonQuery();
            }
            return(rows);
        }
        internal static int my_aspnet_PersonalizationAdministration_ResetUserState(long applicationId, DateTime inactiveSinceDate, string[] usernames, string[] paths, MySQLPersonalizationConnectionHelper connection)
        {
            if (applicationId <= 0)
            {
                return(0);
            }

            if (!connection.Opened)
            {
                throw new Exception("Error: Connection should be open");
            }

            var rows = 0;
            var cmd  = new MySqlCommand();

            if (usernames == null)
            {
                usernames = new string[1] {
                    ""
                }
            }
            ;
            if (paths == null)
            {
                paths = new string[1] {
                    ""
                }
            }
            ;

            foreach (var username in usernames)
            {
                foreach (var path in paths)
                {
                    var query = "DELETE peruser.* FROM (my_aspnet_personalizationperuser as peruser  " +
                                "INNER JOIN my_aspnet_users as users ON " +
                                "peruser.userid = users.id) " +
                                "INNER JOIN my_aspnet_paths as paths ON " +
                                "paths.applicationId = @ApplicationId AND " +
                                "paths.pathid = peruser.pathid AND " +
                                "(@InactiveSinceDate is null OR users.lastactivitydate <= @InactiveSinceDate) ";
                    query           = string.IsNullOrEmpty(username) ? query : query += " AND (@UserName is null OR users.name = @UserName)  ";
                    query           = string.IsNullOrEmpty(path) ? query : query += " AND (@Path is null OR paths.loweredpath = LOWER(@Path))";
                    cmd.CommandText = query;
                    cmd.Parameters.AddWithValue("@ApplicationId", applicationId);
                    cmd.Parameters.AddWithValue("@InactiveSinceDate", inactiveSinceDate);
                    cmd.Parameters.AddWithValue("@Path", path);
                    cmd.Parameters.AddWithValue("@UserName", username);
                    cmd.Connection = connection.Connection;
                    rows          += cmd.ExecuteNonQuery();
                }
            }

            return(rows);
        }
    internal static byte[] my_aspnet_PersonalizationAllUsers_GetPageSettings(long applicationId, string path, MySQLPersonalizationConnectionHelper connection)
    {
      if (applicationId <= 0)
        return null;

      if (!connection.Opened)
        throw new Exception("Error: Connection should be open");

      var cmd = new MySqlCommand("Select pathid from my_aspnet_paths as paths where paths.applicationid = @ApplicationId  and paths.loweredPath = lower(@Path)", connection.Connection);
      cmd.Parameters.AddWithValue("@ApplicationId", applicationId);
      cmd.Parameters.AddWithValue("@Path", path);
      var pathId = (string)cmd.ExecuteScalar();

      if (!string.IsNullOrEmpty(pathId))
      {
        cmd.CommandText = "Select PageSettings from my_aspnet_personalizationallusers where pathId = @PathId";
        cmd.Parameters.AddWithValue("@PathId", pathId);
        cmd.Connection = connection.Connection;
        var reader = cmd.ExecuteReader();

        byte[] settings = null;
        while (reader.Read())
        {
          int size = (int)reader.GetBytes(0, 0, null, 0, 0);
          settings = new byte[size];
          reader.GetBytes(0, 0, settings, 0, size);
        }
        reader.Close();
        return settings;          
      }

      return null;
    }
        internal static int my_aspnet_PersonalizationAdministration_DeleteAllState(bool allUsersScope, long applicationId, MySQLPersonalizationConnectionHelper connection)
        {
            if (applicationId <= 0)
            {
                return(0);
            }

            if (!connection.Opened)
            {
                throw new Exception("Error: Connection should be open");
            }

            var cmd = new MySqlCommand();

            cmd.Connection = connection.Connection;

            if (allUsersScope)
            {
                cmd.CommandText = "DELETE FROM my_aspnet_personalizationallusers " +
                                  "WHERE PathId IN " +
                                  "(SELECT paths.PathId FROM my_aspnet_paths as paths " +
                                  "WHERE paths.ApplicationId = @ApplicationId)";
            }
            else
            {
                cmd.CommandText = "DELETE FROM my_aspnet_personalizationperuser " +
                                  "WHERE PathId IN " +
                                  "(SELECT Paths.PathId FROM my_aspnet_paths Paths " +
                                  "WHERE Paths.ApplicationId = @ApplicationId)";
            }
            cmd.Parameters.AddWithValue("@ApplicationId", applicationId);
            var rows = cmd.ExecuteNonQuery();

            return(rows);
        }
    /// <summary>
    /// Retrieves personalization state as opaque blobs from the data source. Retrieves both shared and user personalization state corresponding to a specified user and a specified page.
    /// </summary>
    /// <param name="webPartManager"></param>
    /// <param name="path"></param>
    /// <param name="userName"></param>
    /// <param name="sharedDataBlob"></param>
    /// <param name="userDataBlob"></param>
    protected override void LoadPersonalizationBlobs(WebPartManager webPartManager, string path, string userName, ref Byte[] sharedDataBlob, ref Byte[] userDataBlob)
    {
       sharedDataBlob = null;
       userDataBlob = null;
       MySQLPersonalizationConnectionHelper connection = new MySQLPersonalizationConnectionHelper(connectionString);
       connection.OpenConnection(true);
       try
       {
         sharedDataBlob = PersonalizationProviderProcedures.my_aspnet_PersonalizationAllUsers_GetPageSettings(
                          ApplicationId, path, connection);
         if (!String.IsNullOrEmpty(userName))
         {
           userDataBlob = PersonalizationProviderProcedures.my_aspnet_PersonalizationPerUser_GetPageSettings(
                             ApplicationId, path, userName, DateTime.UtcNow, connection);
         }

         connection.CloseConnection();
       }
       catch (Exception ex)
       {
         if (writeExceptionsToEventLog)
           WriteToEventLog(ex, "MySQLPersonalizationProvider - LoadPersonazalitionBlobs");        
         throw;
       }
       finally
       {
         connection.CloseConnection();       
       }
    }
        /// <summary>
        /// Saves shared state for the specified page in the aspnet_PersonalizationAllUsers table
        /// </summary>
        /// <returns></returns>
        internal static int my_aspnet_PersonalizationAllUsers_SetPageSettings(long applicationId, string path, byte[] settings, DateTime currentTimeUtc, MySQLPersonalizationConnectionHelper connection)
        {
            if (applicationId <= 0)
            {
                return(0);
            }

            if (!connection.Opened)
            {
                throw new Exception("Error: Connection should be open");
            }

            var cmd = new MySqlCommand();

            cmd.Connection = connection.Connection;

            cmd.CommandText = "SELECT PathId FROM my_aspnet_paths WHERE ApplicationId = @ApplicationId AND LoweredPath = LOWER(@Path)";
            cmd.Parameters.AddWithValue("@ApplicationId", applicationId);
            cmd.Parameters.AddWithValue("@Path", path);
            var pathId = (string)cmd.ExecuteScalar();

            cmd.Parameters.Clear();

            if (pathId == null)
            {
                // create path
                MySqlTransaction trans;
                trans = connection.Connection.BeginTransaction();

                try
                {
                    cmd.Transaction = trans;
                    cmd.CommandText = "INSERT INTO my_aspnet_paths (applicationId, pathId, path, loweredpath) values (@ApplicationId, @PathId, @Path, LOWER(@Path))";
                    cmd.Parameters.AddWithValue("@ApplicationId", applicationId);
                    cmd.Parameters.AddWithValue("@PathId", pathId = Guid.NewGuid().ToString());
                    cmd.Parameters.AddWithValue("@Path", path);
                    cmd.ExecuteNonQuery();
                    trans.Commit();
                }
                catch
                {
                    trans.Rollback();
                    throw;
                }
            }

            cmd.CommandText  = "INSERT INTO my_aspnet_personalizationallusers(PathId, PageSettings, LastUpdatedDate) VALUES (@PathId, @PageSettings, @CurrentTimeUtc)";
            cmd.CommandText += " ON DUPLICATE KEY UPDATE PageSettings=Values(PageSettings), LastUpdatedDate=Values(LastUpdatedDate)";
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@PageSettings", settings);
            cmd.Parameters.AddWithValue("@PathId", pathId);
            cmd.Parameters.AddWithValue("@CurrentTimeUtc", currentTimeUtc);
            var rows = cmd.ExecuteNonQuery();

            return(rows);
        }
    internal static int myaspnet_PersonalizationAdministration_GetCountOfState(bool allUsersScope, string applicationName, long applicationId, string path, string userName, DateTime inactiveSinceDate, MySQLPersonalizationConnectionHelper connection)
    {
      if (applicationId <= 0)
        return 0;

       if (!connection.Opened)
        throw new Exception("Error: Connection should be open");
      
      if (allUsersScope)
      {

        MySqlCommand cmd = new MySqlCommand("Select count(*) from my_aspnet_personalizationallusers, my_aspnet_paths " +
                                             "where my_aspnet_paths.applicationId = @ApplicationId and " +
                                             "my_aspnet_personalizationallusers.pathid = my_aspnet_paths.pathid and " +
                                             "(@Path is null or my_aspnet_paths.loweredpath like lower(@Path))", connection.Connection);

        cmd.Parameters.AddWithValue("@ApplicationId", applicationId);
        cmd.Parameters.AddWithValue("@Path", path);
        cmd.Connection = connection.Connection;
        var count = cmd.ExecuteScalar().ToString();
        return int.Parse(count);
      }
      else {
        MySqlCommand cmd = new MySqlCommand("Select count(*) from my_aspnet_personalizationperuser as peruser, my_aspnet_users as users, "+
                                        "my_aspnet_paths as paths " +
                                        "where paths.applicationId = @ApplicationId and " +
                                        "peruser.userid = users.id and " +
                                        "peruser.pathId = paths.pathId and " +                                         
                                        "(@Path is null or paths.loweredpath like lower(@Path) and " +
                                        "(@UserName is null or users.name like lower(@UserName))) and " +
                                        "(@InactiveSinceDate is null or users.lastactivitydate <= @InactiveSinceDate) ", connection.Connection);

        cmd.Parameters.AddWithValue("@ApplicationId", applicationId);
        cmd.Parameters.AddWithValue("@Path", path);
        cmd.Parameters.AddWithValue("@UserName", userName);
        cmd.Parameters.AddWithValue("@InactiveSinceDate", inactiveSinceDate);
        cmd.Connection = connection.Connection;
        var count = cmd.ExecuteScalar().ToString();
        return int.Parse(count);
      }    
    }