/////////////////////////////////////////////////////////////////////////////
 /////////////////////////////////////////////////////////////////////////////
 public override int GetNumberOfInactiveProfiles(ProfileAuthenticationOption authenticationOption, DateTime userInactiveSinceDate)
 {
     try
     {
         AccessConnectionHolder holder = AccessConnectionHelper.GetConnection(_DatabaseFileName, true);
         try
         {
             string sqlQuery = @"SELECT COUNT(*) FROM aspnet_Users u, aspnet_Profile p " +
                               @"WHERE ApplicationId = @AppId AND LastActivityDate <= @LastActivityDate AND u.UserId = p.UserId" + GetClauseForAuthenticationOptions(authenticationOption);
             OleDbCommand cmd = new OleDbCommand(sqlQuery, holder.Connection);
             cmd.Parameters.Add(new OleDbParameter("@AppId", GetApplicationId(holder)));
             cmd.Parameters.Add(CreateDateTimeOleDbParameter("@LastActivityDate", userInactiveSinceDate));
             return((int)cmd.ExecuteScalar());
         }
         catch (Exception e)
         {
             throw AccessConnectionHelper.GetBetterException(e, holder);
         }
         finally
         {
             holder.Close();
         }
     }
     catch
     {
         throw;
     }
 }
 /////////////////////////////////////////////////////////////////////////////
 /////////////////////////////////////////////////////////////////////////////
 public override int DeleteInactiveProfiles(ProfileAuthenticationOption authenticationOption, DateTime userInactiveSinceDate)
 {
     try
     {
         AccessConnectionHolder holder = AccessConnectionHelper.GetConnection(_DatabaseFileName, true);
         try
         {
             string inClause = @"SELECT UserId FROM aspnet_Users " +
                               @"WHERE ApplicationId = @AppId AND LastActivityDate <= @LastActivityDate " + GetClauseForAuthenticationOptions(authenticationOption);
             string       sqlQuery = @"DELETE FROM aspnet_Profile WHERE UserId IN (" + inClause + ")";
             OleDbCommand cmd      = new OleDbCommand(sqlQuery, holder.Connection);
             cmd.Parameters.Add(new OleDbParameter("@AppId", GetApplicationId(holder)));
             cmd.Parameters.Add(CreateDateTimeOleDbParameter("@LastActivityDate", userInactiveSinceDate));
             return(cmd.ExecuteNonQuery());
         }
         catch (Exception e)
         {
             throw AccessConnectionHelper.GetBetterException(e, holder);
         }
         finally
         {
             holder.Close();
         }
     }
     catch
     {
         throw;
     }
 }
        //////////////////////////////////////////////////////////////////////
        //////////////////////////////////////////////////////////////////////
        //////////////////////////////////////////////////////////////////////

        public override string[] GetRolesForUser(string username)
        {
            SecUtility.CheckParameter(ref username, true, false, true, 255, "username");
            if (username.Length < 1)
            {
                return(new string[0]);
            }

            AccessConnectionHolder holder     = AccessConnectionHelper.GetConnection(_DatabaseFileName, true);
            OleDbConnection        connection = holder.Connection;
            OleDbDataReader        reader     = null;

            try
            {
                try
                {
                    int appId  = GetApplicationId(holder);
                    int userId = AccessConnectionHelper.GetUserID(connection, appId, username, false);
                    if (userId == 0)
                    {
                        return(new string[0]);
                    }
                    OleDbCommand     command;
                    StringCollection sc = new StringCollection();
                    String[]         strReturn;


                    command = new OleDbCommand(@"SELECT RoleName FROM aspnet_UsersInRoles ur, aspnet_Roles r " +
                                               @"WHERE ur.UserId = @UserId AND ur.RoleId = r.RoleId " +
                                               @"ORDER BY RoleName",
                                               connection);
                    command.Parameters.Add(new OleDbParameter("@UserId", userId));
                    reader = command.ExecuteReader(CommandBehavior.SequentialAccess);
                    while (reader.Read())
                    {
                        sc.Add(reader.GetString(0));
                    }
                    strReturn = new String[sc.Count];
                    sc.CopyTo(strReturn, 0);
                    return(strReturn);
                }
                catch (Exception e)
                {
                    throw AccessConnectionHelper.GetBetterException(e, holder);
                }
                finally
                {
                    if (reader != null)
                    {
                        reader.Close();
                    }
                    holder.Close();
                }
            }
            catch
            {
                throw;
            }
        }
        ////////////////////////////////////////////////////////////
        ////////////////////////////////////////////////////////////
        private void GetPropertyValuesFromDatabase(string username, SettingsPropertyValueCollection svc)
        {
            try
            {
                AccessConnectionHolder holder = AccessConnectionHelper.GetConnection(_DatabaseFileName, true);
                string[]        names         = null;
                string          values        = null;
                OleDbDataReader reader        = null;
                ////////////////////////////////////////////////////////////
                // Step 1: Get Values from DB
                try
                {
                    int appId  = GetApplicationId(holder);
                    int userId = AccessConnectionHelper.GetUserID(holder.Connection, appId, username, false);

                    if (userId != 0)
                    { // User exists?
                        OleDbCommand cmd = new OleDbCommand(@"SELECT PropertyNames, PropertyValuesString " +
                                                            @"FROM aspnet_Profile " +
                                                            @"WHERE UserId = @UserId", holder.Connection);
                        cmd.Parameters.Add(new OleDbParameter("@UserId", userId));
                        reader = cmd.ExecuteReader();
                        if (reader.Read())
                        {
                            names  = reader.GetString(0).Split(':');
                            values = reader.GetString(1);
                        }
                        try
                        { // Not a critical part -- don't throw exceptions here
                            cmd = new OleDbCommand(@"UPDATE aspnet_Users SET LastActivityDate=@LastActivityDate WHERE UserId = @UserId", holder.Connection);
                            cmd.Parameters.Add(CreateDateTimeOleDbParameter("@LastActivityDate", DateTime.Now));
                            cmd.Parameters.Add(new OleDbParameter("@UserId", userId));
                            cmd.ExecuteNonQuery();
                        }
                        catch { }
                    }
                }
                catch (Exception e)
                {
                    throw AccessConnectionHelper.GetBetterException(e, holder);
                }
                finally
                {
                    if (reader != null)
                    {
                        reader.Close();
                    }
                    holder.Close();
                }
                if (names != null && names.Length > 0)
                {
                    ParseDataFromDB(names, values, new byte[0], svc);
                }
            }
            catch
            {
                throw;
            }
        }
        //////////////////////////////////////////////////////////////////////
        //////////////////////////////////////////////////////////////////////
        //////////////////////////////////////////////////////////////////////

        public override bool IsUserInRole(string username, string roleName)
        {
            SecUtility.CheckParameter(ref username, true, false, true, 255, "username");
            if (username.Length < 1)
            {
                return(false);
            }
            SecUtility.CheckParameter(ref roleName, true, true, true, 255, "roleName");

            AccessConnectionHolder holder     = AccessConnectionHelper.GetConnection(_DatabaseFileName, true);
            OleDbConnection        connection = holder.Connection;

            try
            {
                try
                {
                    int appId  = GetApplicationId(holder);
                    int userId = AccessConnectionHelper.GetUserID(connection, appId, username, false);
                    int roleId = GetRoleId(connection, appId, roleName);

                    OleDbCommand command;

                    if (userId == 0)
                    {
                        return(false);
                    }

                    if (roleId == 0)
                    {
                        return(false);
                    }

                    command = new OleDbCommand(@"SELECT UserId FROM aspnet_UsersInRoles WHERE UserId = @UserId AND RoleId = @RoleId", connection);
                    command.Parameters.Add(new OleDbParameter("@UserId", userId));
                    command.Parameters.Add(new OleDbParameter("@RoleId", roleId));

                    object result = command.ExecuteScalar();

                    if (result == null || !(result is int) || ((int)result) != userId)
                    {
                        return(false);
                    }
                    return(true);
                }
                catch (Exception e)
                {
                    throw AccessConnectionHelper.GetBetterException(e, holder);
                }
                finally
                {
                    holder.Close();
                }
            }
            catch
            {
                throw;
            }
        }
        //////////////////////////////////////////////////////////////////////
        //////////////////////////////////////////////////////////////////////
        //////////////////////////////////////////////////////////////////////
        public override string[] FindUsersInRole(string roleName, string usernameToMatch)
        {
            SecUtility.CheckParameter(ref roleName, true, true, true, 255, "roleName");
            SecUtility.CheckParameter(ref usernameToMatch, true, true, false, 255, "usernameToMatch");

            StringCollection sc = new StringCollection();

            AccessConnectionHolder holder     = AccessConnectionHelper.GetConnection(_DatabaseFileName, true);
            OleDbDataReader        reader     = null;
            OleDbConnection        connection = holder.Connection;

            try
            {
                try
                {
                    int appId  = GetApplicationId(holder);
                    int roleId = GetRoleId(connection, appId, roleName);

                    OleDbCommand command;

                    if (roleId == 0)
                    {
                        throw new ProviderException("Role not found " + roleName);
                    }

                    command = new OleDbCommand(@"SELECT UserName " +
                                               @"FROM aspnet_UsersInRoles ur, aspnet_Users u " +
                                               @"WHERE ur.RoleId = @RoleId AND ur.UserId = u.UserId AND u.UserName LIKE '%'+@UserNameToMatch+'%' " +
                                               @"ORDER BY UserName", connection);
                    command.Parameters.Add(new OleDbParameter("@RoleId", roleId));
                    command.Parameters.Add(new OleDbParameter("@UserNameToMatch", usernameToMatch));
                    reader = command.ExecuteReader(CommandBehavior.SequentialAccess);
                    while (reader.Read())
                    {
                        sc.Add((string)reader.GetString(0));
                    }
                }
                catch (Exception e)
                {
                    throw AccessConnectionHelper.GetBetterException(e, holder);
                }
                finally
                {
                    if (reader != null)
                    {
                        reader.Close();
                    }
                    holder.Close();
                }
            }
            catch
            {
                throw;
            }
            string[] allUsers = new string[sc.Count];
            sc.CopyTo(allUsers, 0);
            return(allUsers);
        }
        private int GetCountOfSharedState(string path)
        {
            string getSharedStateCount =
                "SELECT COUNT(*)" +
                " FROM aspnet_PagePersonalizationAllUsers AllUsers, aspnet_Paths Paths" +
                " WHERE AllUsers.PathId = Paths.PathId AND Paths.ApplicationId = @ApplicationId";

            AccessConnectionHolder connectionHolder = null;
            OleDbConnection        connection       = null;
            int count = 0;


            try
            {
                try
                {
                    connectionHolder = GetConnectionHolder();
                    connection       = connectionHolder.Connection;

                    OleDbCommand command = new OleDbCommand();
                    command.Connection = connection;
                    OleDbParameterCollection parameters = command.Parameters;

                    int appId = GetApplicationID(connectionHolder);
                    parameters.AddWithValue("ApplicationId", appId);

                    if (path != null)
                    {
                        getSharedStateCount += " AND Paths.Path LIKE '%'+@Path+'%'";
                        OleDbParameter parameter = parameters.Add("Path", OleDbType.WChar);
                        parameter.Value = path;
                    }
                    command.CommandText = getSharedStateCount;

                    object result = command.ExecuteScalar();
                    if ((result != null) && (result is Int32))
                    {
                        count = (Int32)result;
                    }
                }
                finally
                {
                    if (connectionHolder != null)
                    {
                        connectionHolder.Close();
                        connectionHolder = null;
                    }
                }
            }
            catch
            {
                throw;
            }

            return(count);
        }
        protected override void LoadPersonalizationBlobs(WebPartManager webPartManager, string path, string userName, ref byte[] sharedDataBlob, ref byte[] userDataBlob)
        {
            sharedDataBlob = null;
            userDataBlob   = null;

            AccessConnectionHolder connectionHolder = null;
            OleDbConnection        connection       = null;


            try
            {
                try
                {
                    connectionHolder = GetConnectionHolder();
                    connection       = connectionHolder.Connection;

                    int applicationID = GetApplicationID(connectionHolder);
                    if (applicationID != 0)
                    {
                        int pathID = AccessConnectionHelper.GetPathID(connection, applicationID, path);
                        if (pathID != 0)
                        {
                            string sharedDataValue = LoadPersonalizationBlob(connection, pathID);
                            sharedDataBlob = Deserialize(sharedDataValue);

                            if (userName != null)
                            {
                                int userID = AccessConnectionHelper.GetUserID(connection, applicationID, userName);
                                if (userID != 0)
                                {
                                    string userDataValue = LoadPersonalizationBlob(connection, pathID, userID);
                                    userDataBlob = Deserialize(userDataValue);
                                }
                            }
                        }
                    }
                }
                finally
                {
                    if (connectionHolder != null)
                    {
                        connectionHolder.Close();
                        connectionHolder = null;
                    }
                }
            }
            catch
            {
                throw;
            }
        }
        protected override void SavePersonalizationBlob(WebPartManager webPartManager, string path, string userName, byte[] dataBlob)
        {
            AccessConnectionHolder connectionHolder = null;
            OleDbConnection        connection       = null;


            try
            {
                try
                {
                    string blobValue = Serialize(dataBlob);

                    connectionHolder = GetConnectionHolder();
                    connection       = connectionHolder.Connection;

                    int applicationID = GetApplicationID(connectionHolder);
                    if (applicationID != 0)
                    {
                        int pathID = AccessConnectionHelper.GetPathID(connection, applicationID, path, /* createIfNeeded */ true);

                        if (pathID != 0)
                        {
                            if (String.IsNullOrEmpty(userName))
                            {
                                SavePersonalizationBlob(connection, pathID, blobValue);
                            }
                            else
                            {
                                int userID = AccessConnectionHelper.GetUserID(connection, applicationID, userName, /* createIfNeeded */ true);
                                if (userID != 0)
                                {
                                    SavePersonalizationBlob(connection, pathID, userID, blobValue);
                                }
                            }
                        }
                    }
                }
                finally
                {
                    if (connectionHolder != null)
                    {
                        connectionHolder.Close();
                        connectionHolder = null;
                    }
                }
            }
            catch
            {
                throw;
            }
        }
        protected override void ResetPersonalizationBlob(WebPartManager webPartManager, string path, string userName)
        {
            AccessConnectionHolder connectionHolder = null;
            OleDbConnection        connection       = null;


            try
            {
                try
                {
                    connectionHolder = GetConnectionHolder();
                    connection       = connectionHolder.Connection;

                    int applicationID = GetApplicationID(connectionHolder);
                    if (applicationID != 0)
                    {
                        int pathID = AccessConnectionHelper.GetPathID(connection, applicationID, path);

                        if (pathID != 0)
                        {
                            if (String.IsNullOrEmpty(userName))
                            {
                                ResetPersonalizationBlob(connection, pathID);
                            }
                            else
                            {
                                int userID = AccessConnectionHelper.GetUserID(connection, applicationID, userName);
                                if (userID != 0)
                                {
                                    ResetPersonalizationBlob(connection, pathID, userID);
                                }
                            }
                        }
                    }
                }
                finally
                {
                    if (connectionHolder != null)
                    {
                        connectionHolder.Close();
                        connectionHolder = null;
                    }
                }
            }
            catch
            {
                throw;
            }
        }
 /////////////////////////////////////////////////////////////////////////////
 /////////////////////////////////////////////////////////////////////////////
 public override int DeleteProfiles(string[] usernames)
 {
     SecUtility.CheckArrayParameter(ref usernames, true, true, true, 255, "usernames");
     try
     {
         AccessConnectionHolder holder = AccessConnectionHelper.GetConnection(_DatabaseFileName, true);
         int  numDeleted        = 0;
         bool fBeginTransCalled = false;
         try
         {
             OleDbCommand cmd = new OleDbCommand("BEGIN TRANSACTION", holder.Connection);
             cmd.ExecuteNonQuery();
             fBeginTransCalled = true;
             int appId = GetApplicationId(holder);
             foreach (string username in usernames)
             {
                 if (DeleteProfile(holder, username, appId))
                 {
                     numDeleted++;
                 }
             }
             cmd = new OleDbCommand("COMMIT TRANSACTION", holder.Connection);
             cmd.ExecuteNonQuery();
             fBeginTransCalled = false;
         }
         catch (Exception e)
         {
             throw AccessConnectionHelper.GetBetterException(e, holder);
         }
         finally
         {
             if (fBeginTransCalled)
             {
                 try
                 {
                     OleDbCommand command = new OleDbCommand("ROLLBACK TRANSACTION", holder.Connection);
                     command.ExecuteNonQuery();
                 }
                 catch { }
             }
             holder.Close();
         }
         return(numDeleted);
     }
     catch
     {
         throw;
     }
 }
        //////////////////////////////////////////////////////////////////////
        //////////////////////////////////////////////////////////////////////
        //////////////////////////////////////////////////////////////////////

        public override string[] GetAllRoles()
        {
            AccessConnectionHolder holder     = AccessConnectionHelper.GetConnection(_DatabaseFileName, true);
            OleDbConnection        connection = holder.Connection;
            OleDbDataReader        reader     = null;

            try
            {
                try
                {
                    int              appId = GetApplicationId(holder);
                    OleDbCommand     command;
                    StringCollection sc        = new StringCollection();
                    String[]         strReturn = null;

                    command = new OleDbCommand(@"SELECT RoleName FROM aspnet_Roles WHERE ApplicationId = @AppId ORDER BY RoleName", connection);
                    command.Parameters.Add(new OleDbParameter("@AppId", appId));
                    reader = command.ExecuteReader(CommandBehavior.SequentialAccess);
                    while (reader.Read())
                    {
                        sc.Add(reader.GetString(0));
                    }
                    strReturn = new String[sc.Count];
                    sc.CopyTo(strReturn, 0);
                    return(strReturn);
                }
                catch (Exception e)
                {
                    throw AccessConnectionHelper.GetBetterException(e, holder);
                }
                finally
                {
                    if (reader != null)
                    {
                        reader.Close();
                    }
                    holder.Close();
                }
            }
            catch
            {
                throw;
            }
        }
        private int ResetAllState(string getStateCountQuery, string deleteStateQuery)
        {
            AccessConnectionHolder connectionHolder = null;
            OleDbConnection        connection       = null;
            int count = 0;


            try
            {
                try
                {
                    connectionHolder = GetConnectionHolder();
                    connection       = connectionHolder.Connection;

                    // Get the count of records that would be deleted
                    OleDbCommand command      = new OleDbCommand(getStateCountQuery, connection);
                    object       lookupResult = command.ExecuteScalar();
                    if ((lookupResult != null) && (lookupResult is Int32))
                    {
                        count = (Int32)lookupResult;
                    }

                    // Do the actual deletion
                    command.CommandText = deleteStateQuery;
                    command.ExecuteNonQuery();
                }
                finally
                {
                    if (connectionHolder != null)
                    {
                        connectionHolder.Close();
                        connectionHolder = null;
                    }
                }
            }
            catch
            {
                throw;
            }

            return(count);
        }
        //////////////////////////////////////////////////////////////////////
        //////////////////////////////////////////////////////////////////////
        //////////////////////////////////////////////////////////////////////

        public override bool RoleExists(string roleName)
        {
            try
            {
                SecUtility.CheckParameter(ref roleName, true, true, true, 255, "roleName");
            }
            catch
            {
                return(false);
            }
            AccessConnectionHolder holder     = AccessConnectionHelper.GetConnection(_DatabaseFileName, true);
            OleDbConnection        connection = holder.Connection;

            try
            {
                try
                {
                    int appId  = GetApplicationId(holder);
                    int roleId = GetRoleId(connection, appId, roleName);

                    return(roleId != 0);
                }
                catch (Exception e)
                {
                    throw AccessConnectionHelper.GetBetterException(e, holder);
                }
                finally
                {
                    holder.Close();
                }
            }
            catch
            {
                throw;
            }
        }
        private PersonalizationStateInfoCollection FindSharedState(string path,
                                                                   int pageIndex,
                                                                   int pageSize,
                                                                   out int totalRecords)
        {
            const string findSharedState =
                "SELECT Paths.Path, AllUsers.LastUpdatedDate, LEN(AllUsers.PageSettings)" +
                " FROM aspnet_PagePersonalizationAllUsers AllUsers, aspnet_Paths Paths" +
                " WHERE AllUsers.PathId = Paths.PathId AND Paths.ApplicationId = @ApplicationId";
            const string orderBy       = " ORDER BY Paths.Path ASC";
            const string findUserState =
                "SELECT SUM(LEN(PerUser.PageSettings)), COUNT(*)" +
                " FROM aspnet_PagePersonalizationPerUser PerUser, aspnet_Paths Paths" +
                " WHERE PerUser.PathId = Paths.PathId" +
                " AND Paths.ApplicationId = @ApplicationId" +
                " AND Paths.Path LIKE '%'+@Path+'%'";

            AccessConnectionHolder connectionHolder = null;
            OleDbConnection        connection       = null;
            OleDbDataReader        reader           = null;

            totalRecords = 0;


            try
            {
                try
                {
                    connectionHolder = GetConnectionHolder();
                    connection       = connectionHolder.Connection;
                    OleDbCommand             command    = new OleDbCommand(findSharedState, connection);
                    OleDbParameterCollection parameters = command.Parameters;
                    OleDbParameter           parameter;

                    int appId = GetApplicationID(connectionHolder);
                    parameters.AddWithValue("ApplicationId", appId);

                    if (path != null)
                    {
                        command.CommandText += " AND Paths.Path LIKE '%'+@Path+'%'";
                        parameter            = parameters.Add("Path", OleDbType.WChar);
                        parameter.Value      = path;
                    }

                    command.CommandText += orderBy;
                    reader = command.ExecuteReader(CommandBehavior.SequentialAccess);
                    PersonalizationStateInfoCollection stateInfoCollection = new PersonalizationStateInfoCollection();
                    long recordCount = 0;
                    long lBound      = pageIndex * pageSize;
                    long uBound      = lBound + pageSize;

                    while (reader.Read())
                    {
                        recordCount++;
                        if (recordCount <= lBound || recordCount > uBound)
                        {
                            continue;
                        }
                        string   returnedPath    = reader.GetString(0);
                        DateTime lastUpdatedDate = reader.GetDateTime(1);
                        int      size            = reader.GetInt32(2);

                        // Create temp info since we need to retrieve the corresponding personalization size and count later
                        stateInfoCollection.Add(new SharedPersonalizationStateInfo(returnedPath, lastUpdatedDate, size, -1, -1));
                    }
                    totalRecords = (int)recordCount;

                    // We need to close the reader in order to make other queries
                    reader.Close();
                    command    = new OleDbCommand(findUserState, connection);
                    parameters = command.Parameters;

                    parameters.AddWithValue("ApplicationId", appId);
                    parameter = parameters.Add("Path", OleDbType.WChar);
                    PersonalizationStateInfoCollection sharedStateInfoCollection = new PersonalizationStateInfoCollection();

                    foreach (PersonalizationStateInfo stateInfo in stateInfoCollection)
                    {
                        parameter.Value = stateInfo.Path;

                        reader = command.ExecuteReader(CommandBehavior.SequentialAccess);
                        reader.Read();
                        int sizeOfPersonalizations  = Convert.ToInt32(reader.GetValue(0), CultureInfo.InvariantCulture);
                        int countOfPersonalizations = reader.GetInt32(1);
                        reader.Close();
                        sharedStateInfoCollection.Add(new SharedPersonalizationStateInfo(
                                                          stateInfo.Path, stateInfo.LastUpdatedDate,
                                                          stateInfo.Size, sizeOfPersonalizations, countOfPersonalizations));
                    }

                    return(sharedStateInfoCollection);
                }
                finally
                {
                    if (connectionHolder != null)
                    {
                        connectionHolder.Close();
                        connectionHolder = null;
                    }

                    if (reader != null)
                    {
                        reader.Close();
                    }
                }
            }
            catch
            {
                throw;
            }
        }
        private PersonalizationStateInfoCollection FindUserState(string path,
                                                                 DateTime inactiveSinceDate,
                                                                 string username,
                                                                 int pageIndex,
                                                                 int pageSize,
                                                                 out int totalRecords)
        {
            const string findUserState =
                "SELECT Paths.Path, PerUser.LastUpdatedDate, LEN(PerUser.PageSettings), Users.UserName, Users.LastActivityDate" +
                " FROM aspnet_PagePersonalizationPerUser PerUser, aspnet_Users Users, aspnet_Paths Paths" +
                " WHERE PerUser.UserId = Users.UserId AND PerUser.PathId = Paths.PathId" +
                " AND Paths.ApplicationId = @ApplicationId";
            const string orderBy = " ORDER BY Paths.Path ASC, Users.UserName ASC";

            AccessConnectionHolder connectionHolder = null;
            OleDbConnection        connection       = null;
            OleDbDataReader        reader           = null;

            totalRecords = 0;


            try
            {
                try
                {
                    OleDbParameter parameter;
                    connectionHolder = GetConnectionHolder();
                    connection       = connectionHolder.Connection;
                    OleDbCommand command = new OleDbCommand();
                    command.Connection = connection;
                    OleDbParameterCollection parameters = command.Parameters;

                    int appId = GetApplicationID(connectionHolder);
                    parameters.AddWithValue("ApplicationId", appId);

                    command.CommandText = findUserState;
                    if (inactiveSinceDate != DateTime.MinValue)
                    {
                        command.CommandText += " AND Users.LastActivityDate <= @InactiveSinceDate";

                        // Note: OleDb provider does not handle datetime that has non-
                        // zero millisecond, so it needs to be rounded up.
                        parameter       = parameters.Add("InactiveSinceDate", OleDbType.DBTimeStamp);
                        parameter.Value = new DateTime(inactiveSinceDate.Year, inactiveSinceDate.Month, inactiveSinceDate.Day,
                                                       inactiveSinceDate.Hour, inactiveSinceDate.Minute, inactiveSinceDate.Second);
                    }

                    if (path != null)
                    {
                        command.CommandText += " AND Paths.Path LIKE '%'+@Path+'%'";
                        parameter            = parameters.Add("Path", OleDbType.WChar);
                        parameter.Value      = path;
                    }

                    if (username != null)
                    {
                        command.CommandText += " AND Users.UserName LIKE '%'+@UserName+'%'";
                        parameter            = parameters.Add("UserName", OleDbType.WChar);
                        parameter.Value      = username;
                    }

                    command.CommandText += orderBy;
                    reader = command.ExecuteReader(CommandBehavior.SequentialAccess);
                    PersonalizationStateInfoCollection stateInfoCollection = new PersonalizationStateInfoCollection();
                    long recordCount = 0;
                    long lBound      = pageIndex * pageSize;
                    long uBound      = lBound + pageSize;

                    while (reader.Read())
                    {
                        recordCount++;
                        if (recordCount <= lBound || recordCount > uBound)
                        {
                            continue;
                        }

                        string   returnedPath     = reader.GetString(0);
                        DateTime lastUpdatedDate  = reader.GetDateTime(1);
                        int      size             = reader.GetInt32(2);
                        string   returnedUsername = reader.GetString(3);
                        DateTime lastActivityDate = reader.GetDateTime(4);
                        stateInfoCollection.Add(new UserPersonalizationStateInfo(
                                                    returnedPath, lastUpdatedDate,
                                                    size, returnedUsername, lastActivityDate));
                    }
                    totalRecords = (int)recordCount;
                    return(stateInfoCollection);
                }
                finally
                {
                    if (connectionHolder != null)
                    {
                        connectionHolder.Close();
                        connectionHolder = null;
                    }

                    if (reader != null)
                    {
                        reader.Close();
                    }
                }
            }
            catch
            {
                throw;
            }
        }
        private int GetCountOfUserState(string path, DateTime inactiveSinceDate, string username)
        {
            string getUserStateCount =
                "SELECT COUNT(*)" +
                " FROM aspnet_PagePersonalizationPerUser PerUser, aspnet_Users Users, aspnet_Paths Paths" +
                " WHERE PerUser.UserId = Users.UserId AND PerUser.PathId = Paths.PathId" +
                " AND Paths.ApplicationId = @ApplicationId";

            AccessConnectionHolder connectionHolder = null;
            OleDbConnection        connection       = null;
            int count = 0;


            try
            {
                try
                {
                    OleDbParameter parameter;
                    connectionHolder = GetConnectionHolder();
                    connection       = connectionHolder.Connection;
                    OleDbCommand command = new OleDbCommand();
                    command.Connection = connection;
                    OleDbParameterCollection parameters = command.Parameters;

                    int appId = GetApplicationID(connectionHolder);
                    parameter = parameters.AddWithValue("ApplicationId", appId);

                    if (path != null)
                    {
                        getUserStateCount += " AND Paths.Path LIKE '%'+@Path+'%'";
                        parameter          = parameters.Add("Path", OleDbType.WChar);
                        parameter.Value    = path;
                    }

                    if (username != null)
                    {
                        getUserStateCount += " AND Users.UserName LIKE '%'+@UserName+'%'";
                        parameter          = parameters.Add("UserName", OleDbType.WChar);
                        parameter.Value    = username;
                    }

                    if (inactiveSinceDate != DateTime.MinValue)
                    {
                        getUserStateCount += " AND Users.LastActivityDate <= @InactiveSinceDate";

                        // Note: OleDb provider does not handle datetime that has non-
                        // zero millisecond, so it needs to be rounded up.
                        parameter       = parameters.Add("InactiveSinceDate", OleDbType.DBTimeStamp);
                        parameter.Value = new DateTime(inactiveSinceDate.Year, inactiveSinceDate.Month, inactiveSinceDate.Day,
                                                       inactiveSinceDate.Hour, inactiveSinceDate.Minute, inactiveSinceDate.Second);
                    }

                    command.CommandText = getUserStateCount;

                    object result = command.ExecuteScalar();
                    if ((result != null) && (result is Int32))
                    {
                        count = (Int32)result;
                    }
                }
                finally
                {
                    if (connectionHolder != null)
                    {
                        connectionHolder.Close();
                        connectionHolder = null;
                    }
                }
            }
            catch
            {
                throw;
            }

            return(count);
        }
        //////////////////////////////////////////////////////////////////////
        //////////////////////////////////////////////////////////////////////
        //////////////////////////////////////////////////////////////////////

        public override void RemoveUsersFromRoles(string[] usernames, string[] roleNames)
        {
            SecUtility.CheckArrayParameter(ref roleNames, true, true, true, 255, "roleNames");
            SecUtility.CheckArrayParameter(ref usernames, true, true, true, 255, "usernames");

            AccessConnectionHolder holder     = AccessConnectionHelper.GetConnection(_DatabaseFileName, true);
            OleDbConnection        connection = holder.Connection;
            bool fBeginTransCalled            = false;

            try
            {
                try
                {
                    int   appId   = GetApplicationId(holder);
                    int[] userIds = new int[usernames.Length];
                    int[] roleIds = new int[roleNames.Length];

                    OleDbCommand command;
                    command = new OleDbCommand("BEGIN TRANSACTION", connection);
                    command.ExecuteNonQuery();
                    fBeginTransCalled = true;

                    for (int iterU = 0; iterU < usernames.Length; iterU++)
                    {
                        userIds[iterU] = AccessConnectionHelper.GetUserID(connection, appId, usernames[iterU], false);
                        if (userIds[iterU] == 0)
                        {
                            throw new ProviderException("User not found: " + usernames[iterU]);
                        }
                    }
                    for (int iterR = 0; iterR < roleNames.Length; iterR++)
                    {
                        roleIds[iterR] = GetRoleId(connection, appId, roleNames[iterR]);
                        if (roleIds[iterR] == 0)
                        {
                            throw new ProviderException("Role not found: " + roleNames[iterR]);
                        }
                    }
                    for (int iterU = 0; iterU < usernames.Length; iterU++)
                    {
                        for (int iterR = 0; iterR < roleNames.Length; iterR++)
                        {
                            command = new OleDbCommand(@"SELECT UserId FROM aspnet_UsersInRoles WHERE UserId = @UserId AND RoleId = @RoleId", connection);
                            command.Parameters.Add(new OleDbParameter("@UserId", userIds[iterU]));
                            command.Parameters.Add(new OleDbParameter("@RoleId", roleIds[iterR]));

                            object result = command.ExecuteScalar();
                            if (result == null || !(result is int) || ((int)result) != userIds[iterU])
                            { // doesn't exist!
                                throw new ProviderException("The user " + usernames[iterU] + " is already not in role " + roleNames[iterR]);
                            }
                        }
                    }

                    for (int iterU = 0; iterU < usernames.Length; iterU++)
                    {
                        for (int iterR = 0; iterR < roleNames.Length; iterR++)
                        {
                            command = new OleDbCommand(@"DELETE FROM aspnet_UsersInRoles WHERE UserId = @UserId AND RoleId = @RoleId", connection);
                            command.Parameters.Add(new OleDbParameter("@UserId", userIds[iterU]));
                            command.Parameters.Add(new OleDbParameter("@RoleId", roleIds[iterR]));
                            if (command.ExecuteNonQuery() != 1)
                            {
                                throw new ProviderException("Unknown failure");
                            }
                        }
                    }
                    command = new OleDbCommand("COMMIT TRANSACTION", connection);
                    command.ExecuteNonQuery();
                }
                catch (Exception e)
                {
                    try
                    {
                        if (fBeginTransCalled)
                        {
                            OleDbCommand command = new OleDbCommand("ROLLBACK TRANSACTION", connection);
                            command.ExecuteNonQuery();
                        }
                    }
                    catch { }
                    throw AccessConnectionHelper.GetBetterException(e, holder);
                }
                finally
                {
                    holder.Close();
                }
            }
            catch
            {
                throw;
            }
        }
        //////////////////////////////////////////////////////////////////////
        //////////////////////////////////////////////////////////////////////
        //////////////////////////////////////////////////////////////////////

        public override bool DeleteRole(string roleName, bool throwOnPopulatedRole)
        {
            SecUtility.CheckParameter(ref roleName, true, true, true, 255, "roleName");
            AccessConnectionHolder holder     = AccessConnectionHelper.GetConnection(_DatabaseFileName, true);
            OleDbConnection        connection = holder.Connection;
            bool fBeginTransCalled            = false;

            try
            {
                try
                {
                    int          appId = GetApplicationId(holder);
                    OleDbCommand command;
                    int          roleId = GetRoleId(connection, appId, roleName);

                    if (roleId == 0)
                    {
                        return(false);
                    }

                    if (throwOnPopulatedRole)
                    {
                        command = new OleDbCommand(@"SELECT COUNT(*) " +
                                                   @"FROM aspnet_UsersInRoles ur, aspnet_Users u " +
                                                   @"WHERE ur.RoleId = @RoleId AND ur.UserId = u.UserId", connection);

                        command.Parameters.Add(new OleDbParameter("@RoleId", roleId));
                        object num = command.ExecuteScalar();
                        if (!(num is int) || ((int)num) != 0)
                        {
                            throw new ProviderException("Role is not empty");
                        }
                    }

                    command = new OleDbCommand("BEGIN TRANSACTION", connection);
                    command.ExecuteNonQuery();
                    fBeginTransCalled = true;
                    command           = new OleDbCommand(@"DELETE FROM aspnet_Roles WHERE RoleId = @RoleId", connection);
                    command.Parameters.Add(new OleDbParameter("@RoleId", roleId));
                    int returnValue = command.ExecuteNonQuery();
                    command = new OleDbCommand("COMMIT TRANSACTION", connection);
                    command.ExecuteNonQuery();
                    fBeginTransCalled = false;

                    return(returnValue == 1);
                }
                catch (Exception e)
                {
                    if (fBeginTransCalled)
                    {
                        try
                        {
                            OleDbCommand command = new OleDbCommand("ROLLBACK TRANSACTION", connection);
                            command.ExecuteNonQuery();
                        }
                        catch { }
                    }
                    throw AccessConnectionHelper.GetBetterException(e, holder);
                }
                finally
                {
                    holder.Close();
                }
            }
            catch
            {
                throw;
            }
        }
        /////////////////////////////////////////////////////////////////////////////
        /////////////////////////////////////////////////////////////////////////////
        /////////////////////////////////////////////////////////////////////////////
        /////////////////////////////////////////////////////////////////////////////
        // Mangement APIs from ProfileProvider class

        public override int DeleteProfiles(ProfileInfoCollection profiles)
        {
            if (profiles == null)
            {
                throw new ArgumentNullException("profiles");
            }
            if (profiles.Count < 1)
            {
                throw new ArgumentException("Profiles collection is empty", "profiles");
            }
            foreach (ProfileInfo pi in profiles)
            {
                string username = pi.UserName;
                SecUtility.CheckParameter(ref username, true, true, true, 255, "UserName");
            }
            try
            {
                AccessConnectionHolder holder = AccessConnectionHelper.GetConnection(_DatabaseFileName, true);
                bool fBeginTransCalled        = false;
                int  numDeleted = 0;
                try
                {
                    OleDbCommand cmd = new OleDbCommand("BEGIN TRANSACTION", holder.Connection);
                    cmd.ExecuteNonQuery();
                    fBeginTransCalled = true;
                    int appId = GetApplicationId(holder);
                    foreach (ProfileInfo profile in profiles)
                    {
                        if (DeleteProfile(holder, profile.UserName.Trim(), appId))
                        {
                            numDeleted++;
                        }
                    }
                    cmd = new OleDbCommand("COMMIT TRANSACTION", holder.Connection);
                    cmd.ExecuteNonQuery();
                    fBeginTransCalled = false;
                }
                catch (Exception e)
                {
                    throw AccessConnectionHelper.GetBetterException(e, holder);
                }
                finally
                {
                    if (fBeginTransCalled)
                    {
                        try
                        {
                            OleDbCommand command = new OleDbCommand("ROLLBACK TRANSACTION", holder.Connection);
                            command.ExecuteNonQuery();
                        }
                        catch { }
                    }
                    holder.Close();
                }
                return(numDeleted);
            }
            catch
            {
                throw;
            }
        }
        //////////////////////////////////////////////////////////////////////
        //////////////////////////////////////////////////////////////////////
        //////////////////////////////////////////////////////////////////////

        public override void CreateRole(string roleName)
        {
            SecUtility.CheckParameter(ref roleName, true, true, true, 255, "roleName");

            AccessConnectionHolder holder     = AccessConnectionHelper.GetConnection(_DatabaseFileName, true);
            OleDbConnection        connection = holder.Connection;
            bool fBeginTransCalled            = false;

            try
            {
                try
                {
                    int          appId = GetApplicationId(holder);
                    OleDbCommand command;
                    int          roleId = GetRoleId(connection, appId, roleName);

                    if (roleId != 0)
                    {
                        throw new ProviderException("Provider role already exists: " + roleName);
                    }

                    command = new OleDbCommand("BEGIN TRANSACTION", connection);
                    command.ExecuteNonQuery();
                    fBeginTransCalled = true;
                    command           = new OleDbCommand(@"INSERT INTO aspnet_Roles (ApplicationId, RoleName) VALUES (@AppId, @RName)", connection);
                    command.Parameters.Add(new OleDbParameter("@AppId", appId));
                    command.Parameters.Add(new OleDbParameter("@RName", roleName));
                    int returnValue = command.ExecuteNonQuery();
                    command = new OleDbCommand("COMMIT TRANSACTION", connection);
                    command.ExecuteNonQuery();
                    fBeginTransCalled = false;

                    if (returnValue == 1)
                    {
                        return;
                    }
                    throw new ProviderException("Unknown provider failure");
                }
                catch (Exception e)
                {
                    if (fBeginTransCalled)
                    {
                        try
                        {
                            OleDbCommand command = new OleDbCommand("ROLLBACK TRANSACTION", connection);
                            command.ExecuteNonQuery();
                        }
                        catch { }
                    }
                    throw AccessConnectionHelper.GetBetterException(e, holder);
                }
                finally
                {
                    holder.Close();
                }
            }
            catch
            {
                throw;
            }
        }
        private int ResetUserStatePerUsers(string path, string[] usernames)
        {
            int count = 0;
            AccessConnectionHolder connectionHolder = null;
            OleDbConnection        connection       = null;
            bool beginTransCalled = false;

            try
            {
                try
                {
                    connectionHolder = GetConnectionHolder();
                    connection       = connectionHolder.Connection;

                    int applicationID = GetApplicationID(connectionHolder);
                    if (applicationID != 0)
                    {
                        OleDbCommand   command            = new OleDbCommand(null, connection);
                        OleDbParameter userIdParam        = command.Parameters.Add(new OleDbParameter("@UserId", OleDbType.Integer));
                        string         fromAndWhereClause = " FROM aspnet_PagePersonalizationPerUser WHERE UserId = @UserId";
                        if (!String.IsNullOrEmpty(path))
                        {
                            int pathId = AccessConnectionHelper.GetPathID(connection, applicationID, path);
                            fromAndWhereClause += " AND PathId = @PathId";
                            command.Parameters.Add(new OleDbParameter("@PathId", pathId));
                        }

                        string selectCommandText = "SELECT COUNT(*)" + fromAndWhereClause;
                        string deleteCommandText = "DELETE" + fromAndWhereClause;

                        OleDbCommand transCommand = new OleDbCommand("BEGIN TRANSACTION", connection);
                        transCommand.ExecuteNonQuery();
                        beginTransCalled = true;

                        foreach (string username in usernames)
                        {
                            command.CommandText = selectCommandText;
                            userIdParam.Value   = AccessConnectionHelper.GetUserID(connection, applicationID, username);
                            int numOfRecords = (int)command.ExecuteScalar();
                            if (numOfRecords > 0)
                            {
                                command.CommandText = deleteCommandText;
                                command.ExecuteNonQuery();
                                count += numOfRecords;
                            }
                        }

                        transCommand.CommandText = "COMMIT TRANSACTION";
                        transCommand.ExecuteNonQuery();
                    }
                }
                catch
                {
                    try
                    {
                        if (beginTransCalled)
                        {
                            OleDbCommand rollbackCommand = new OleDbCommand("ROLLBACK TRANSACTION", connection);
                            rollbackCommand.ExecuteNonQuery();
                        }
                    }
                    catch
                    {
                    }
                    throw;
                }
                finally
                {
                    if (connectionHolder != null)
                    {
                        connectionHolder.Close();
                        connectionHolder = null;
                    }
                }
            }
            catch
            {
                throw;
            }

            return(count);
        }
        private int ResetStatePerPaths(string tableName, string[] paths)
        {
            if (paths == null || paths.Length == 0)
            {
                return(0);
            }

            int count = 0;
            AccessConnectionHolder connectionHolder = null;
            OleDbConnection        connection       = null;
            bool beginTransCalled = false;

            try
            {
                try
                {
                    connectionHolder = GetConnectionHolder();
                    connection       = connectionHolder.Connection;

                    int applicationID = GetApplicationID(connectionHolder);
                    if (applicationID != 0)
                    {
                        string         fromAndWhereClause = " FROM " + tableName + " WHERE PathId = @PathId";
                        string         selectCommandText  = "SELECT COUNT(*)" + fromAndWhereClause;
                        string         deleteCommandText  = "DELETE" + fromAndWhereClause;
                        OleDbCommand   command            = new OleDbCommand(null, connection);
                        OleDbParameter pathParam          = command.Parameters.Add(new OleDbParameter("@PathId", OleDbType.Integer));

                        OleDbCommand transCommand = new OleDbCommand("BEGIN TRANSACTION", connection);
                        transCommand.ExecuteNonQuery();
                        beginTransCalled = true;

                        foreach (string path in paths)
                        {
                            command.CommandText = selectCommandText;
                            pathParam.Value     = AccessConnectionHelper.GetPathID(connection, applicationID, path);
                            int numOfRecords = (int)command.ExecuteScalar();
                            if (numOfRecords > 0)
                            {
                                command.CommandText = deleteCommandText;
                                command.ExecuteNonQuery();
                                count += numOfRecords;
                            }
                        }

                        transCommand.CommandText = "COMMIT TRANSACTION";
                        transCommand.ExecuteNonQuery();
                    }
                }
                catch
                {
                    try
                    {
                        if (beginTransCalled)
                        {
                            OleDbCommand rollbackCommand = new OleDbCommand("ROLLBACK TRANSACTION", connection);
                            rollbackCommand.ExecuteNonQuery();
                        }
                    }
                    catch
                    {
                    }
                    throw;
                }
                finally
                {
                    if (connectionHolder != null)
                    {
                        connectionHolder.Close();
                        connectionHolder = null;
                    }
                }
            }
            catch
            {
                throw;
            }

            return(count);
        }
        /////////////////////////////////////////////////////////////////////////////
        /////////////////////////////////////////////////////////////////////////////
        // Private methods

        /////////////////////////////////////////////////////////////////////////////
        /////////////////////////////////////////////////////////////////////////////
        private ProfileInfoCollection GetProfilesForQuery(string sqlQuery, OleDbParameter[] args, int pageIndex, int pageSize, out int totalRecords)
        {
            if (pageIndex < 0)
            {
                throw new ArgumentException("Page index must be non-negative", "pageIndex");
            }
            if (pageSize < 1)
            {
                throw new ArgumentException("Page size must be positive", "pageSize");
            }

            long lBound = (long)pageIndex * pageSize;
            long uBound = lBound + pageSize - 1;

            if (uBound > System.Int32.MaxValue)
            {
                throw new ArgumentException("pageIndex*pageSize too large");
            }
            try
            {
                AccessConnectionHolder holder   = AccessConnectionHelper.GetConnection(_DatabaseFileName, true);
                ProfileInfoCollection  profiles = new ProfileInfoCollection();
                OleDbDataReader        reader   = null;
                try
                {
                    OleDbCommand cmd = new OleDbCommand(sqlQuery, holder.Connection);
                    cmd.Parameters.Add(new OleDbParameter("@AppId", GetApplicationId(holder)));
                    int len = args.Length;
                    for (int iter = 0; iter < len; iter++)
                    {
                        cmd.Parameters.Add(args[iter]);
                    }
                    reader       = cmd.ExecuteReader(CommandBehavior.SequentialAccess);
                    totalRecords = 0;
                    while (reader.Read())
                    {
                        totalRecords++;
                        if (totalRecords - 1 < lBound || totalRecords - 1 > uBound)
                        {
                            continue;
                        }
                        string   username;
                        DateTime dtLastActivity, dtLastUpdated;
                        bool     isAnon;
                        username       = reader.GetString(0);
                        isAnon         = reader.GetBoolean(1);
                        dtLastActivity = reader.GetDateTime(2);
                        dtLastUpdated  = reader.GetDateTime(3);
                        int size = reader.GetInt32(4);
                        profiles.Add(new ProfileInfo(username, isAnon, dtLastActivity, dtLastUpdated, size));
                    }
                    return(profiles);
                }
                catch (Exception e)
                {
                    throw AccessConnectionHelper.GetBetterException(e, holder);
                }
                finally
                {
                    if (reader != null)
                    {
                        reader.Close();
                    }
                    holder.Close();
                }
            }
            catch
            {
                throw;
            }
        }
        public override int ResetUserState(string path, DateTime userInactiveSinceDate)
        {
            path = PersonalizationProviderHelper.CheckAndTrimString(path, "path", false, MaxStringLength);

            AccessConnectionHolder connectionHolder = null;
            OleDbConnection        connection       = null;
            int count = 0;


            try
            {
                try
                {
                    connectionHolder = GetConnectionHolder();
                    connection       = connectionHolder.Connection;

                    // Special note: OleDbProvider requires the parameters to be added
                    // in the same order as appearing in the query text.

                    string getDeleteUserStateCount =
                        "SELECT COUNT(*)" +
                        " FROM aspnet_PagePersonalizationPerUser PerUser, aspnet_Users Users, aspnet_Paths Paths" +
                        " WHERE PerUser.UserId = Users.UserId AND PerUser.PathId = Paths.PathId" +
                        " AND Paths.ApplicationId = @ApplicationId" +
                        " AND Users.LastActivityDate <= @InactiveSinceDate";

                    string deleteUserState =
                        "DELETE FROM aspnet_PagePersonalizationPerUser" +
                        " WHERE Id IN (SELECT PerUser.Id " +
                        " FROM aspnet_PagePersonalizationPerUser PerUser, aspnet_Users Users, aspnet_Paths Paths" +
                        " WHERE PerUser.UserId = Users.UserId AND PerUser.PathId = Paths.PathId" +
                        " AND Paths.ApplicationId = @ApplicationId" +
                        " AND Users.LastActivityDate <= @InactiveSinceDate";

                    // Get the count of records that would be deleted
                    OleDbCommand command = new OleDbCommand();
                    command.Connection = connection;
                    OleDbParameterCollection parameters = command.Parameters;
                    OleDbParameter           parameter;

                    int appId = GetApplicationID(connectionHolder);
                    parameters.AddWithValue("ApplicationId", appId);

                    // Note: OleDb provider does not handle datetime that has non-
                    // zero millisecond, so it needs to be rounded up.
                    parameter       = parameters.Add("InactiveSinceDate", OleDbType.DBTimeStamp);
                    parameter.Value = new DateTime(userInactiveSinceDate.Year, userInactiveSinceDate.Month, userInactiveSinceDate.Day,
                                                   userInactiveSinceDate.Hour, userInactiveSinceDate.Minute, userInactiveSinceDate.Second);

                    if (path != null)
                    {
                        const string pathParamQueryText = " AND Paths.Path = @Path";
                        getDeleteUserStateCount += pathParamQueryText;
                        deleteUserState         += pathParamQueryText;
                        parameters.AddWithValue("Path", path);
                    }
                    deleteUserState += ")";

                    command.CommandText = getDeleteUserStateCount;

                    object lookupResult = command.ExecuteScalar();
                    if ((lookupResult != null) && (lookupResult is Int32))
                    {
                        count = (Int32)lookupResult;
                        if (count > 0)
                        {
                            // Do the actual deletion
                            command.CommandText = deleteUserState;
                            command.ExecuteNonQuery();
                        }
                    }
                }
                finally
                {
                    if (connectionHolder != null)
                    {
                        connectionHolder.Close();
                        connectionHolder = null;
                    }
                }
            }
            catch
            {
                throw;
            }

            return(count);
        }
        ////////////////////////////////////////////////////////////
        ////////////////////////////////////////////////////////////
        ////////////////////////////////////////////////////////////

        public override void SetPropertyValues(SettingsContext sc, SettingsPropertyValueCollection properties)
        {
            try
            {
                string username            = (string)sc["UserName"];
                bool   userIsAuthenticated = (bool)sc["IsAuthenticated"];
                if (username == null || username.Length < 1 || properties.Count < 1)
                {
                    return;
                }

                string names  = String.Empty;
                string values = String.Empty;
                byte[] buf    = null;
                PrepareDataForSaving(ref names, ref values, ref buf, false, properties, userIsAuthenticated);
                if (names.Length == 0)
                {
                    return;
                }

                ////////////////////////////////////////////////////////////
                // Step 2: Store strings in DB
                AccessConnectionHolder holder = AccessConnectionHelper.GetConnection(_DatabaseFileName, true);
                bool fBeginTransCalled        = false;
                try
                {
                    OleDbCommand cmd = new OleDbCommand("BEGIN TRANSACTION", holder.Connection);
                    cmd.ExecuteNonQuery();
                    fBeginTransCalled = true;

                    int appId  = GetApplicationId(holder);
                    int userId = AccessConnectionHelper.GetUserID(holder.Connection, appId, username, true, !userIsAuthenticated);

                    if (userId == 0)
                    { // User not creatable
                        return;
                    }
                    cmd = new OleDbCommand(@"SELECT UserId FROM aspnet_Profile WHERE UserId = @UserId", holder.Connection);
                    cmd.Parameters.Add(new OleDbParameter("@UserId", userId));
                    object result = cmd.ExecuteScalar();
                    if (result != null && (result is int) && ((int)result) == userId)
                    {
                        cmd = new OleDbCommand(@"UPDATE aspnet_Profile SET PropertyNames = @PropertyNames, PropertyValuesString = @PropertyValuesString, LastUpdatedDate = @LastUpdatedDate WHERE UserId = @UserId", holder.Connection);
                        cmd.Parameters.Add(new OleDbParameter("@PropertyNames", names));
                        cmd.Parameters.Add(new OleDbParameter("@PropertyValuesString", values));
                        cmd.Parameters.Add(CreateDateTimeOleDbParameter("@LastUpdatedDate", DateTime.Now));
                        cmd.Parameters.Add(new OleDbParameter("@UserId", userId));
                    }
                    else
                    {
                        cmd = new OleDbCommand(@"INSERT INTO aspnet_Profile (UserId, PropertyNames, PropertyValuesString, LastUpdatedDate) VALUES (@UserId, @PropertyNames, @PropertyValuesString, @LastUpdatedDate)", holder.Connection);
                        cmd.Parameters.Add(new OleDbParameter("@UserId", userId));
                        cmd.Parameters.Add(new OleDbParameter("@PropertyNames", names));
                        cmd.Parameters.Add(new OleDbParameter("@PropertyValuesString", values));
                        cmd.Parameters.Add(CreateDateTimeOleDbParameter("@LastUpdatedDate", DateTime.Now));
                    }
                    cmd.ExecuteNonQuery();
                    try
                    { // Not a critical part -- don't throw exceptions here
                        cmd = new OleDbCommand(@"UPDATE aspnet_Users SET LastActivityDate=@LastActivityDate WHERE UserId = @UserId", holder.Connection);
                        cmd.Parameters.Add(CreateDateTimeOleDbParameter("@LastActivityDate", DateTime.Now));
                        cmd.Parameters.Add(new OleDbParameter("@UserId", userId));
                        cmd.ExecuteNonQuery();
                    }
                    catch { }
                    cmd = new OleDbCommand("COMMIT TRANSACTION", holder.Connection);
                    cmd.ExecuteNonQuery();
                    fBeginTransCalled = false;
                }
                catch (Exception e)
                {
                    throw AccessConnectionHelper.GetBetterException(e, holder);
                }
                finally
                {
                    if (fBeginTransCalled)
                    {
                        try
                        {
                            OleDbCommand command = new OleDbCommand("ROLLBACK TRANSACTION", holder.Connection);
                            command.ExecuteNonQuery();
                        }
                        catch { }
                    }
                    holder.Close();
                }
            }
            catch
            {
                throw;
            }
        }