//*****************************************************************************
// This code file is part of the Universal Provider Framework for SharePoint.
// This file was written by Adam Buenz [WSS MVP] of ARB Security Solutions, LLC
//                  http://www.sharepointsecurity.com
//
// This file and its parts is free for re-distribution, for use in both free 
// and commercial applications, however this header must remain intact for legal 
// use. The Universal Provider Framework is distributed in the hope that it will
// be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
//*****************************************************************************

//**************************************
// Current Version: 1.0.0.0 (Beta)
//**************************************

// namespace references
using System;
using System.Collections.Specialized;
using System.Configuration;
using System.Configuration.Provider;
using System.Data;
using System.Data.Common;
using System.Diagnostics;
using System.Globalization;
using System.IO;
using System.Runtime.InteropServices;
using System.Security.Permissions;
using System.Text;
using System.Web.Hosting;
using System.Web.Profile;
using System.Web.Security;

namespace Universal.SharePointProvider.Framework
{
    /// <summary>
    /// Allow various security actions using declarative security for serialization
    /// </summary>
    [SecurityPermission(SecurityAction.Assert, Flags=SecurityPermissionFlag.SerializationFormatter)]
    
    public abstract class SharePointProfileProvider : ProfileProvider
    {
        /// <summary>
        /// Constructor for the SharePointProfileProvider class
        /// </summary>
        /// <param name="defaultName"></param>
        /// <param name="defaultDescription"></param>
        /// <param name="provider"></param>
        protected SharePointProfileProvider(string defaultName, string defaultDescription, DbProviderFactory provider)
        {
            this.eventLog = "Application";
            this.exceptionMessage = "An exception has occurred with the SharePoint profile provider. Please contact your SharePoint administrator.";
            this.defaultName = defaultName;
            this.defaultDescription = defaultDescription;
            this.provider = provider;
        }

        /// <summary>
        /// Declare variables
        /// </summary>
        private string connectionString;
        private string defaultDescription;
        private string defaultName;
        private string eventLog;
        private string exceptionMessage;
        private string pApplicationName;
        private ConnectionStringSettings pConnectionStringSettings;
        private readonly DbProviderFactory provider;
        private bool pWriteExceptionsToEventLog;
        private SharePointUsersProvider usersProvider;

        public override string ApplicationName
        {
            get
            {
                return this.pApplicationName;
            }
            set
            {
                this.pApplicationName = value;
            }
        }

        protected virtual string SqlDeleteInactiveProfiles
        {
            get
            {
                return "DELETE FROM aspnet_profiles WHERE userid = (SELECT userid FROM aspnet_users WHERE LOWER(applicationname) = :applicationname AND lastactivitydate <= :lastactivitydate {0})";
            }
        }

        public bool WriteExceptionsToEventLog
        {
            get
            {
                return this.pWriteExceptionsToEventLog;
            }
            set
            {
                this.pWriteExceptionsToEventLog = value;
            }
        }

        /// <summary>
        /// General parameters
        /// </summary>
        /// <param name="parameter"></param>
        /// <param name="dbType"></param>
        /// <param name="value"></param>
        protected virtual void ApplyParameterInfo(DbParameter parameter, DbType dbType, object value)
        {
            // Set the DBtype parameter
            parameter.DbType = dbType;
            // Set the value of the parameter
            parameter.Value = value;
        }

        /// <summary>
        /// Profile provider database command to execute against the plugged data store
        /// </summary>
        /// <param name="commandText"></param>
        /// <param name="connection"></param>
        /// <returns></returns>
        private DbCommand CreateCommand(string commandText, DbConnection connection)
        {
            DbCommand profileProviderDbCommand = this.provider.CreateCommand();
            profileProviderDbCommand.Connection = connection;
            profileProviderDbCommand.CommandText = commandText;
            return profileProviderDbCommand;
        }

        /// <summary>
        /// Profile provider parameter for DBCommand
        /// </summary>
        /// <param name="name"></param>
        /// <param name="dbType"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        private DbParameter CreateParameter(string name, DbType dbType, object value)
        {
            DbParameter profileProviderParameter = this.provider.CreateParameter();
            profileProviderParameter.ParameterName = name;
            this.ApplyParameterInfo(profileProviderParameter, dbType, value);
            return profileProviderParameter;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="name"></param>
        /// <param name="dbType"></param>
        /// <param name="size"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        private DbParameter CreateParameter(string name, DbType dbType, int size, object value)
        {
            DbParameter profileProviderParameter = this.provider.CreateParameter();
            profileProviderParameter.ParameterName = name;
            profileProviderParameter.Size = size;
            this.ApplyParameterInfo(profileProviderParameter, dbType, value);
            return profileProviderParameter;
        }

        /// <summary>
        /// Decoding custom profile functionality
        /// </summary>
        /// <param name="names"></param>
        /// <param name="values"></param>
        /// <param name="buf"></param>
        /// <param name="properties"></param>
        private void DecodeProfileData(string[] names, string values, byte[] buf, SettingsPropertyValueCollection properties)
        {
            if (((names != null) && (values != null)) && ((buf != null) && (properties != null)))
            {
                for (int metric = 0; metric < names.Length; metric += 4)
                {
                    string profileName = names[metric];
                    SettingsPropertyValue propertyValue = properties[profileName];
                    if (propertyValue != null)
                    {
                        int primary = int.Parse(names[metric + 2], CultureInfo.InvariantCulture);
                        int secondary = int.Parse(names[metric + 3], CultureInfo.InvariantCulture);
                        if ((secondary == -1) && !propertyValue.Property.PropertyType.IsValueType)
                        {
                            propertyValue.PropertyValue = null;
                            propertyValue.IsDirty = false;
                            propertyValue.Deserialized = true;
                        }
                        else if (((names[metric + 1] == "S") && (primary >= 0)) && ((secondary > 0) && (values.Length >= (primary + secondary))))
                        {
                            propertyValue.SerializedValue = values.Substring(primary, secondary);
                        }
                        else if (((names[metric + 1] == "B") && (primary >= 0)) && ((secondary > 0) && (buf.Length >= (primary + secondary))))
                        {
                            byte[] buffer1 = new byte[secondary];
                            Buffer.BlockCopy(buf, primary, buffer1, 0, secondary);
                            propertyValue.SerializedValue = buffer1;
                        }
                    }
                }
            }
        }

        /// <summary>
        /// Delete profiles which are inactive
        /// </summary>
        /// <param name="authenticationOption"></param>
        /// <param name="userInactiveSinceDate"></param>
        /// <returns></returns>
        public override int DeleteInactiveProfiles(ProfileAuthenticationOption authenticationOption, DateTime userInactiveSinceDate)
        {
            int profileMetric = 0;
            DbConnection deleteInActProfDbConnection = null;
            DbTransaction deleteInActProfDbTransaction = null;
            try
            {
                string inactiveProfile;
                if (deleteInActProfDbConnection == null)
                {
                    deleteInActProfDbConnection = this.provider.CreateConnection();
                    deleteInActProfDbConnection.ConnectionString = this.connectionString;
                    deleteInActProfDbConnection.Open();
                    deleteInActProfDbTransaction = deleteInActProfDbConnection.BeginTransaction(IsolationLevel.ReadCommitted);
                }
                bool flagAuth = authenticationOption != ProfileAuthenticationOption.Authenticated;
                if (authenticationOption != ProfileAuthenticationOption.All)
                {
                    inactiveProfile = string.Format(this.SqlDeleteInactiveProfiles, " AND isanonymous= :isanonymous ");
                }
                else
                {
                    inactiveProfile = string.Format(this.SqlDeleteInactiveProfiles, "");
                }
                IDbCommand deleteInActProfDbCommand = this.CreateCommand(inactiveProfile, deleteInActProfDbConnection);
                deleteInActProfDbCommand.Parameters.Add(this.CreateParameter("applicationname", DbType.String, 0xff, this.ApplicationName.ToLower()));
                deleteInActProfDbCommand.Parameters.Add(this.CreateParameter("lastactivitydate", DbType.DateTime, userInactiveSinceDate));
                if (authenticationOption != ProfileAuthenticationOption.All)
                {
                    deleteInActProfDbCommand.Parameters.Add(this.CreateParameter("isanonymous", DbType.Boolean, flagAuth));
                }
                try
                {
                    if (deleteInActProfDbCommand.ExecuteNonQuery() > 0)
                    {
                        profileMetric++;
                    }
                }
                catch (Exception exception)
                {
                    throw exception;
                }
                if (deleteInActProfDbTransaction != null)
                {
                    deleteInActProfDbTransaction.Commit();
                }
            }
            catch (Exception exception)
            {
                if (deleteInActProfDbTransaction != null)
                {
                    deleteInActProfDbTransaction.Rollback();
                }
                if (this.WriteExceptionsToEventLog)
                {
                    this.WriteToEventLog(exception, "DeleteInactiveProfiles");
                }
                throw new ProviderException(this.exceptionMessage, exception);
            }
            finally
            {
                if (deleteInActProfDbConnection != null)
                {
                    deleteInActProfDbConnection.Close();
                }
            }
            return profileMetric;
        }

        /// <summary>
        /// Delete users from profiles
        /// </summary>
        /// <param name="usernames"></param>
        /// <returns></returns>
        public override int DeleteProfiles(string[] usernames)
        {
            int profileMetric = 0;
            if (usernames == null)
            {
                throw new ArgumentNullException("usernames");
            }
            DbConnection deleteProfDbConnection = null;
            DbTransaction deleteProfDbtransaction = null;
            try
            {
                for (int userMetric = 0; userMetric < usernames.Length; userMetric++)
                {
                    string userName = usernames[userMetric];
                    if (!string.IsNullOrEmpty(userName))
                    {
                        if (deleteProfDbConnection == null)
                        {
                            deleteProfDbConnection = this.provider.CreateConnection();
                            deleteProfDbConnection.ConnectionString = this.connectionString;
                            deleteProfDbConnection.Open();
                            deleteProfDbtransaction = deleteProfDbConnection.BeginTransaction(IsolationLevel.ReadCommitted);
                        }
                        try
                        {
                            if (this.usersProvider.DeleteUser(deleteProfDbConnection, userName, SharePointUsersProvider.ProviderType.Profile))
                            {
                                profileMetric++;
                            }
                        }
                        catch (Exception exception)
                        {
                            throw exception;
                        }
                    }
                }
                if (deleteProfDbtransaction != null)
                {
                    deleteProfDbtransaction.Commit();
                }
            }
            catch (Exception exception)
            {
                if (deleteProfDbtransaction != null)
                {
                    deleteProfDbtransaction.Rollback();
                }
                if (this.WriteExceptionsToEventLog)
                {
                    this.WriteToEventLog(exception, "DeleteUser");
                }
                throw new ProviderException(this.exceptionMessage, exception);
            }
            finally
            {
                if (deleteProfDbConnection != null)
                {
                    deleteProfDbConnection.Close();
                }
            }
            return profileMetric;
        }

        /// <summary>
        /// Delete profiles out of the custom profile data store
        /// </summary>
        /// <param name="profiles"></param>
        /// <returns></returns>
        public override int DeleteProfiles(ProfileInfoCollection profiles)
        {
            if (profiles == null)
            {
                throw new ArgumentNullException("profiles");
            }
            string[] profileArray = new string[profiles.Count];
            int profileMetric = 0;
            foreach (ProfileInfo profileData in profiles)
            {
                profileArray[profileMetric++] = profileData.UserName;
            }
            return this.DeleteProfiles(profileArray);
        }

        private void EncodeProfileData(ref string allNames, ref string allValues, ref byte[] buf, SettingsPropertyValueCollection properties, bool userIsAuthenticated)
        {
            StringBuilder primaryProfileBuilder = new StringBuilder();
            StringBuilder secondaryProfileBuilder = new StringBuilder();
            MemoryStream primaryStream = new MemoryStream();
            try
            {
                foreach (SettingsPropertyValue profileValue in properties)
                {
                    if ((userIsAuthenticated || ((bool) profileValue.Property.Attributes["AllowAnonymous"])) && (profileValue.IsDirty || !profileValue.UsingDefaultValue))
                    {
                        int primaryProfileMetric = 0;
                        int secondaryProfileMetric = 0;
                        string profileData = null;
                        if (profileValue.Deserialized && (profileValue.PropertyValue == null))
                        {
                            primaryProfileMetric = -1;
                        }
                        else
                        {
                            object profileObject = profileValue.SerializedValue;
                            if (profileObject == null)
                            {
                                primaryProfileMetric = -1;
                            }
                            else if (profileObject is string)
                            {
                                profileData = (string) profileObject;
                                primaryProfileMetric = profileData.Length;
                                secondaryProfileMetric = secondaryProfileBuilder.Length;
                            }
                            else
                            {
                                byte[] profileBuffer = (byte[]) profileObject;
                                secondaryProfileMetric = (int) primaryStream.Position;
                                primaryStream.Write(profileBuffer, 0, profileBuffer.Length);
                                primaryStream.Position = secondaryProfileMetric + profileBuffer.Length;
                                primaryProfileMetric = profileBuffer.Length;
                            }
                        }
                        primaryProfileBuilder.Append(profileValue.Name + ":" + ((profileData != null) ? "S" : "B") + ":" + secondaryProfileMetric.ToString(CultureInfo.InvariantCulture) + ":" + primaryProfileMetric.ToString(CultureInfo.InvariantCulture) + ":");
                        if (profileData != null)
                        {
                            secondaryProfileBuilder.Append(profileData);
                        }
                    }
                }
                buf = primaryStream.ToArray();
            }
            finally
            {
                if (primaryStream != null)
                {
                    primaryStream.Close();
                }
            }
            allNames = primaryProfileBuilder.ToString();
            allValues = secondaryProfileBuilder.ToString();
        }

        /// <summary>
        /// Find all the profiles which are inactive based on passing in the username
        /// </summary>
        /// <param name="authenticationOption"></param>
        /// <param name="usernameToMatch"></param>
        /// <param name="userInactiveSinceDate"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="totalRecords"></param>
        /// <returns></returns>
        public override ProfileInfoCollection FindInactiveProfilesByUserName(ProfileAuthenticationOption authenticationOption, string usernameToMatch, DateTime userInactiveSinceDate, int pageIndex, int pageSize, out int totalRecords)
        {
            ProfileInfoCollection profileCollection = null;
            DbConnection findInActProfByNameDbConnection = this.provider.CreateConnection();
            findInActProfByNameDbConnection.ConnectionString = this.connectionString;
            try
            {
                findInActProfByNameDbConnection.Open();
                string textQuery = "SELECT u.username, u.lastactivitydate, p.lastupdateddate FROM aspnet_profiles p, aspnet_users u WHERE p.userid = u.userid AND LOWER(u.applicationname) = :applicationname AND u.lastactivitydate <= :lastactivitydate AND LOWER(u.username) LIKE :username";
                bool flagAuth = authenticationOption != ProfileAuthenticationOption.Authenticated;
                if (authenticationOption != ProfileAuthenticationOption.All)
                {
                    textQuery = textQuery + " AND isanonymous= :isanonymous ";
                }
                IDbCommand findInActProfByNameDbCommand = this.CreateCommand(textQuery, findInActProfByNameDbConnection);
                findInActProfByNameDbCommand.Parameters.Add(this.CreateParameter("applicationname", DbType.String, 0xff, this.ApplicationName.ToLower()));
                findInActProfByNameDbCommand.Parameters.Add(this.CreateParameter("lastactivitydate", DbType.DateTime, userInactiveSinceDate));
                findInActProfByNameDbCommand.Parameters.Add(this.CreateParameter("username", DbType.String, 0xff, usernameToMatch.ToLower()));
                if (authenticationOption != ProfileAuthenticationOption.All)
                {
                    findInActProfByNameDbCommand.Parameters.Add(this.CreateParameter("isanonymous", DbType.Boolean, flagAuth));
                }
                IDataReader findInActProfByNameDbReader = findInActProfByNameDbCommand.ExecuteReader();
                profileCollection = new ProfileInfoCollection();
                while (findInActProfByNameDbReader.Read())
                {
                    string userName = (string) findInActProfByNameDbReader.GetValue(0);
                    DateTime lastActivity = (DateTime) findInActProfByNameDbReader.GetValue(1);
                    DateTime lastUpdate = (DateTime) findInActProfByNameDbReader.GetValue(2);
                    profileCollection.Add(new ProfileInfo(userName, false, lastActivity, lastUpdate, -1));
                }
                findInActProfByNameDbReader.Close();
            }
            catch (Exception exception)
            {
                if (this.WriteExceptionsToEventLog)
                {
                    this.WriteToEventLog(exception, "FindInactiveProfilesByUserName");
                }
                throw new ProviderException(this.exceptionMessage, exception);
            }
            finally
            {
                findInActProfByNameDbConnection.Close();
            }
            totalRecords = profileCollection.Count;
            return profileCollection;
        }

        /// <summary>
        /// Find all the profiles by passing in the username
        /// </summary>
        /// <param name="authenticationOption"></param>
        /// <param name="usernameToMatch"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="totalRecords"></param>
        /// <returns></returns>
        public override ProfileInfoCollection FindProfilesByUserName(ProfileAuthenticationOption authenticationOption, string usernameToMatch, int pageIndex, int pageSize, out int totalRecords)
        {
            ProfileInfoCollection profileCollection = null;
            DbConnection findProfbyNameDbConnection = this.provider.CreateConnection();
            findProfbyNameDbConnection.ConnectionString = this.connectionString;
            try
            {
                findProfbyNameDbConnection.Open();
                string textQuery = "SELECT u.username, u.lastactivitydate, p.lastupdateddate FROM aspnet_profiles p, aspnet_users u WHERE p.userid = u.userid AND LOWER(u.applicationname) = :applicationname AND LOWER(u.username) LIKE :username ";
                bool flagAuth = authenticationOption != ProfileAuthenticationOption.Authenticated;
                if (authenticationOption != ProfileAuthenticationOption.All)
                {
                    textQuery = textQuery + " AND isanonymous= :isanonymous ";
                }
                IDbCommand findInActProfByNameDbCommand = this.CreateCommand(textQuery, findProfbyNameDbConnection);
                findInActProfByNameDbCommand.Parameters.Add(this.CreateParameter("applicationname", DbType.String, 0xff, this.ApplicationName.ToLower()));
                findInActProfByNameDbCommand.Parameters.Add(this.CreateParameter("username", DbType.String, 0xff, usernameToMatch.ToLower()));
                if (authenticationOption != ProfileAuthenticationOption.All)
                {
                    findInActProfByNameDbCommand.Parameters.Add(this.CreateParameter("isanonymous", DbType.Boolean, flagAuth));
                }
                IDataReader findInActProfByNameDbReader = findInActProfByNameDbCommand.ExecuteReader();
                profileCollection = new ProfileInfoCollection();
                while (findInActProfByNameDbReader.Read())
                {
                    string userName = (string) findInActProfByNameDbReader.GetValue(0);
                    DateTime lastActivity = (DateTime) findInActProfByNameDbReader.GetValue(1);
                    DateTime lastUpdate = (DateTime) findInActProfByNameDbReader.GetValue(2);
                    profileCollection.Add(new ProfileInfo(userName, false, lastActivity, lastUpdate, -1));
                }
                findInActProfByNameDbReader.Close();
            }
            catch (Exception exception)
            {
                if (this.WriteExceptionsToEventLog)
                {
                    this.WriteToEventLog(exception, "FindProfilesByUserName");
                }
                throw new ProviderException(this.exceptionMessage, exception);
            }
            finally
            {
                findProfbyNameDbConnection.Close();
            }
            if (profileCollection != null)
            {
                totalRecords = profileCollection.Count;
                return profileCollection;
            }
            totalRecords = 0;
            return profileCollection;
        }

        /// <summary>
        /// Get all profiles which are inactive
        /// </summary>
        /// <param name="authenticationOption"></param>
        /// <param name="userInactiveSinceDate"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="totalRecords"></param>
        /// <returns></returns>
        public override ProfileInfoCollection GetAllInactiveProfiles(ProfileAuthenticationOption authenticationOption, DateTime userInactiveSinceDate, int pageIndex, int pageSize, out int totalRecords)
        {
            ProfileInfoCollection profileCollection = null;
            DbConnection allInActProfDbConnection = this.provider.CreateConnection();
            allInActProfDbConnection.ConnectionString = this.connectionString;
            try
            {
                allInActProfDbConnection.Open();
                string textQuery = "SELECT u.username, u.lastactivitydate, p.lastupdateddate FROM aspnet_profiles p, aspnet_users u WHERE p.userid = u.userid AND LOWER(u.applicationname) = :applicationname AND u.lastactivitydate <= :lastactivitydate ";
                bool flagAuth = authenticationOption != ProfileAuthenticationOption.Authenticated;
                if (authenticationOption != ProfileAuthenticationOption.All)
                {
                    textQuery = textQuery + " AND isanonymous= :isanonymous ";
                }
                IDbCommand allInActProfDbCommand = this.CreateCommand(textQuery, allInActProfDbConnection);
                allInActProfDbCommand.Parameters.Add(this.CreateParameter("applicationname", DbType.String, 0xff, this.ApplicationName.ToLower()));
                allInActProfDbCommand.Parameters.Add(this.CreateParameter("lastactivitydate", DbType.DateTime, userInactiveSinceDate));
                if (authenticationOption != ProfileAuthenticationOption.All)
                {
                    allInActProfDbCommand.Parameters.Add(this.CreateParameter("isanonymous", DbType.Boolean, flagAuth));
                }
                IDataReader allInActProfDbReader = allInActProfDbCommand.ExecuteReader();
                profileCollection = new ProfileInfoCollection();
                while (allInActProfDbReader.Read())
                {
                    string userName = (string) allInActProfDbReader.GetValue(0);
                    DateTime lastActivity = (DateTime) allInActProfDbReader.GetValue(1);
                    DateTime lastUpdate = (DateTime) allInActProfDbReader.GetValue(2);
                    profileCollection.Add(new ProfileInfo(userName, false, lastActivity, lastUpdate, -1));
                }
                allInActProfDbReader.Close();
            }
            catch (Exception exception)
            {
                if (this.WriteExceptionsToEventLog)
                {
                    this.WriteToEventLog(exception, "GetAllInactiveProfiles");
                }
                throw new ProviderException(this.exceptionMessage, exception);
            }
            finally
            {
                allInActProfDbConnection.Close();
            }
            if (profileCollection != null)
            {
                totalRecords = profileCollection.Count;
                return profileCollection;
            }
            totalRecords = 0;
            return profileCollection;
        }

        /// <summary>
        /// Get all profiles in the custom profile data store
        /// </summary>
        /// <param name="authenticationOption"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="totalRecords"></param>
        /// <returns></returns>
        public override ProfileInfoCollection GetAllProfiles(ProfileAuthenticationOption authenticationOption, int pageIndex, int pageSize, out int totalRecords)
        {
            ProfileInfoCollection profileCollection = null;
            DbConnection getAllProfDbConnection = this.provider.CreateConnection();
            getAllProfDbConnection.ConnectionString = this.connectionString;
            try
            {
                getAllProfDbConnection.Open();
                string textQuery = "SELECT u.username, u.lastactivitydate, p.lastupdateddate FROM aspnet_profiles p, aspnet_users u WHERE p.userid = u.userid AND LOWER(u.applicationname) = :applicationname";
                bool flagAuth = authenticationOption != ProfileAuthenticationOption.Authenticated;
                if (authenticationOption != ProfileAuthenticationOption.All)
                {
                    textQuery = textQuery + " AND isanonymous= :isanonymous ";
                }
                IDbCommand getAllProfDbCommand = this.CreateCommand(textQuery, getAllProfDbConnection);
                getAllProfDbCommand.Parameters.Add(this.CreateParameter("applicationname", DbType.String, 0xff, this.ApplicationName.ToLower()));
                if (authenticationOption != ProfileAuthenticationOption.All)
                {
                    getAllProfDbCommand.Parameters.Add(this.CreateParameter("isanonymous", DbType.Boolean, flagAuth));
                }
                IDataReader getAllProfDbReader = getAllProfDbCommand.ExecuteReader();
                profileCollection = new ProfileInfoCollection();
                while (getAllProfDbReader.Read())
                {
                    string userName = (string) getAllProfDbReader.GetValue(0);
                    DateTime lastActivity = (DateTime) getAllProfDbReader.GetValue(1);
                    DateTime lastUpdate = (DateTime) getAllProfDbReader.GetValue(2);
                    profileCollection.Add(new ProfileInfo(userName, false, lastActivity, lastUpdate, -1));
                }
                getAllProfDbReader.Close();
            }
            catch (Exception exception)
            {
                if (this.WriteExceptionsToEventLog)
                {
                    this.WriteToEventLog(exception, "GetAllProfiles");
                }
                throw new ProviderException(this.exceptionMessage, exception);
            }
            finally
            {
                getAllProfDbConnection.Close();
            }
            if (profileCollection != null)
            {
                totalRecords = profileCollection.Count;
                return profileCollection;
            }
            totalRecords = 0;
            return profileCollection;
        }

        /// <summary>
        /// Get a metric of all of the inactive profiles
        /// </summary>
        /// <param name="authenticationOption"></param>
        /// <param name="userInactiveSinceDate"></param>
        /// <returns></returns>
        public override int GetNumberOfInactiveProfiles(ProfileAuthenticationOption authenticationOption, DateTime userInactiveSinceDate)
        {
            int profileMetric = 0;
            DbConnection numInActProfDbConnection = this.provider.CreateConnection();
            numInActProfDbConnection.ConnectionString = this.connectionString;
            try
            {
                numInActProfDbConnection.Open();
                string textQuery = "SELECT COUNT(*) FROM aspnet_profiles p, aspnet_users u WHERE p.userid = u.userid AND LOWER(u.applicationname) = :applicationname AND lastactivitydate <= :lastactivitydate ";
                bool flagAuth = authenticationOption != ProfileAuthenticationOption.Authenticated;
                if (authenticationOption != ProfileAuthenticationOption.All)
                {
                    textQuery = textQuery + " AND isanonymous= :isanonymous ";
                }
                IDbCommand numInActProfDbCommand = this.CreateCommand(textQuery, numInActProfDbConnection);
                numInActProfDbCommand.Parameters.Add(this.CreateParameter("applicationname", DbType.String, 0xff, this.ApplicationName.ToLower()));
                numInActProfDbCommand.Parameters.Add(this.CreateParameter("lastactivitydate", DbType.DateTime, userInactiveSinceDate));
                if (authenticationOption != ProfileAuthenticationOption.All)
                {
                    numInActProfDbCommand.Parameters.Add(this.CreateParameter("isanonymous", DbType.Boolean, flagAuth));
                }
                IDataReader numInActProfDbReader = numInActProfDbCommand.ExecuteReader();
                if (numInActProfDbReader.Read())
                {
                    profileMetric = numInActProfDbReader.GetInt32(0);
                }
                numInActProfDbReader.Close();
            }
            catch (Exception exception)
            {
                if (this.WriteExceptionsToEventLog)
                {
                    this.WriteToEventLog(exception, "SetPropertyValues");
                }
                throw new ProviderException(this.exceptionMessage, exception);
            }
            finally
            {
                numInActProfDbConnection.Close();
            }
            return profileMetric;
        }

        public override SettingsPropertyValueCollection GetPropertyValues(SettingsContext context, SettingsPropertyCollection properties)
        {
            SettingsPropertyValueCollection profValueCollection = new SettingsPropertyValueCollection();
            if (properties.Count != 0)
            {
                foreach (SettingsProperty property in properties)
                {
                    if (property.SerializeAs == SettingsSerializeAs.ProviderSpecific)
                    {
                        if (property.PropertyType.IsPrimitive || (property.PropertyType == typeof(string)))
                        {
                            property.SerializeAs = SettingsSerializeAs.String;
                        }
                        else
                        {
                            property.SerializeAs = SettingsSerializeAs.Xml;
                        }
                    }
                    profValueCollection.Add(new SettingsPropertyValue(property));
                }
                string userName = (string) context["UserName"];
                if (string.IsNullOrEmpty(userName))
                {
                    return profValueCollection;
                }
                string[] propArray = null;
                string propString = null;
                byte[] propBuffer = null;
                DbConnection propertyConnection = this.provider.CreateConnection();
                propertyConnection.ConnectionString = this.connectionString;
                try
                {
                    propertyConnection.Open();
                    DbTransaction propertyTransaction = propertyConnection.BeginTransaction(IsolationLevel.ReadCommitted);
                    try
                    {
                        bool dummeyFlag;
                        object userObject = this.usersProvider.GetMembershipByUserName(propertyConnection, "username", DbType.String, userName.ToLower(), out dummeyFlag, false);
                        if (userObject == null)
                        {
                            propertyTransaction.Commit();
                            return profValueCollection;
                        }
                        Guid userID = (Guid) userObject;
                        IDbCommand propertyCommand = this.CreateCommand("SELECT propertynames, propertyvaluesstring, propertyvaluesbinary FROM aspnet_profiles WHERE userid = :userid", propertyConnection);
                        propertyCommand.Parameters.Add(this.CreateParameter("userid", DbType.Guid, userID));
                        IDataReader propertyReader = propertyCommand.ExecuteReader();
                        if (propertyReader.Read())
                        {
                            byte[] primaryBuffer = propertyReader.GetValue(0) as byte[];
                            byte[] secondaryBuffer = propertyReader.GetValue(1) as byte[];
                            propBuffer = propertyReader.GetValue(2) as byte[];
                            if (propBuffer == null)
                            {
                                propBuffer = new byte[0];
                            }
                            propArray = Encoding.Unicode.GetString(primaryBuffer).Split(new char[] { ':' });
                            propString = Encoding.Unicode.GetString(secondaryBuffer);
                        }
                        propertyReader.Close();
                        propertyTransaction.Commit();
                    }
                    catch (Exception exception)
                    {
                        propertyTransaction.Rollback();
                        throw exception;
                    }
                }
                catch (Exception exception)
                {
                    if (this.WriteExceptionsToEventLog)
                    {
                        this.WriteToEventLog(exception, "GetPropertyValues");
                    }
                    throw new ProviderException(this.exceptionMessage, exception);
                }
                finally
                {
                    propertyConnection.Close();
                }
                this.DecodeProfileData(propArray, propString, propBuffer, profValueCollection);
            }
            return profValueCollection;
        }

        internal abstract SharePointUsersProvider GetUsersProvider();

        /// <summary>
        /// Primary initialization method
        /// </summary>
        /// <param name="name"></param>
        /// <param name="config"></param>
        public override void Initialize(string name, NameValueCollection config)
        {
            if (config == null)
            {
                throw new ArgumentNullException("config");
            }
            if (string.IsNullOrEmpty(name))
            {
                name = this.defaultName;
            }
            if (string.IsNullOrEmpty(config["description"]))
            {
                config.Remove("description");
                config.Add("description", this.defaultDescription);
            }
            base.Initialize(name, config);
            this.pApplicationName = config["ApplicationName"];
            if (string.IsNullOrEmpty(this.pApplicationName))
            {
                this.pApplicationName = HostingEnvironment.ApplicationVirtualPath;
            }
            config.Remove("ApplicationName");
            this.pConnectionStringSettings = ConfigurationManager.ConnectionStrings[config["connectionStringName"]];
            config.Remove("connectionStringName");
            if (this.pConnectionStringSettings == null)
            {
                this.connectionString = "";
            }
            else
            {
                this.connectionString = this.pConnectionStringSettings.ConnectionString.Trim();
            }
            if (config.Count > 0)
            {
                string UnrecognizeAtt = config.GetKey(0);
                if (!string.IsNullOrEmpty(UnrecognizeAtt))
                {
                    throw new ProviderException("Arbitrary unrecognizable attribute related to the SharePoint profile provider: " + UnrecognizeAtt);
                }
            }
            this.usersProvider = this.GetUsersProvider();
        }

        /// <summary>
        /// Determine whether the profile exists
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="userId"></param>
        /// <returns></returns>
        private bool ProfileExists(DbConnection conn, Guid userId)
        {
            IDbCommand profExistsDbCommand = this.CreateCommand("select COUNT(*) from aspnet_profiles WHERE userid = :userid", conn);
            profExistsDbCommand.Parameters.Add(this.CreateParameter("userid", DbType.Guid, userId));
            bool flagCount = false;
            IDataReader profExistsDbReader = null;
            try
            {
                profExistsDbReader = profExistsDbCommand.ExecuteReader();
                if (profExistsDbReader.Read())
                {
                    flagCount = profExistsDbReader.GetInt32(0) > 0;
                }
            }
            finally
            {
                if (profExistsDbReader != null)
                {
                    profExistsDbReader.Close();
                }
            }
            return flagCount;
        }

        /// <summary>
        /// Read fields values
        /// </summary>
        /// <param name="reader"></param>
        /// <param name="i"></param>
        /// <param name="dbType"></param>
        /// <returns></returns>
        protected virtual object ReadField(IDataReader reader, int i, DbType dbType)
        {
            return reader.GetValue(i);
        }

        public override void SetPropertyValues(SettingsContext context, SettingsPropertyValueCollection properties)
        {
            string userName = (string) context["UserName"];
            bool flagAuth = (bool) context["IsAuthenticated"];
            if (!string.IsNullOrEmpty(userName) && (properties.Count != 0))
            {
                string connectionText = string.Empty;
                string valueText = string.Empty;
                byte[] propBuffer = null;
                this.EncodeProfileData(ref connectionText, ref valueText, ref propBuffer, properties, flagAuth);
                if (connectionText != string.Empty)
                { 
                    DbConnection setPropValuesDbConnection = this.provider.CreateConnection();
                    setPropValuesDbConnection.ConnectionString = this.connectionString;
                    try
                    {
                        setPropValuesDbConnection.Open();
                        DbTransaction setPropValuesDbTransaction = setPropValuesDbConnection.BeginTransaction(IsolationLevel.ReadCommitted);
                        try
                        {
                            MembershipCreateStatus creationStatus;
                            Guid userID = (Guid) this.usersProvider.CreateUser(setPropValuesDbConnection, !flagAuth, userName, null, false, out creationStatus);
                            switch (creationStatus)
                            {
                                case MembershipCreateStatus.InvalidUserName:
                                    throw new ProviderException("Invalid user name");

                                case MembershipCreateStatus.DuplicateUserName:
                                    throw new ProviderException("Duplicate user name");
                            }
                            if (!this.ProfileExists(setPropValuesDbConnection, userID))
                            {
                                IDbCommand setPropValuesDbCommand = this.CreateCommand("INSERT INTO aspnet_profiles (userid, propertynames, propertyvaluesstring, propertyvaluesbinary, lastupdateddate) VALUES (:userid, :propertynames, :propertyvaluesstring, :propertyvaluesbinary, :lastupdateddate)", setPropValuesDbConnection);
                                setPropValuesDbCommand.Parameters.Add(this.CreateParameter("userid", DbType.Guid, userID));
                                setPropValuesDbCommand.Parameters.Add(this.CreateParameter("propertynames", DbType.Binary, Encoding.Unicode.GetBytes(connectionText)));
                                setPropValuesDbCommand.Parameters.Add(this.CreateParameter("propertyvaluesstring", DbType.Binary, Encoding.Unicode.GetBytes(valueText)));
                                setPropValuesDbCommand.Parameters.Add(this.CreateParameter("propertyvaluesbinary", DbType.Binary, propBuffer));
                                setPropValuesDbCommand.Parameters.Add(this.CreateParameter("lastupdateddate", DbType.DateTime, DateTime.Now));
                                setPropValuesDbCommand.ExecuteNonQuery();
                            }
                            else
                            {
                                IDbCommand setPropValuesDbCommand = this.CreateCommand("UPDATE aspnet_profiles SET propertynames = :propertynames, propertyvaluesstring = :propertyvaluesstring, propertyvaluesbinary = :propertyvaluesbinary, lastupdateddate = :lastupdateddate WHERE userid = :userid", setPropValuesDbConnection);
                                setPropValuesDbCommand.Parameters.Add(this.CreateParameter("propertynames", DbType.Binary, Encoding.Unicode.GetBytes(connectionText)));
                                setPropValuesDbCommand.Parameters.Add(this.CreateParameter("propertyvaluesstring", DbType.Binary, Encoding.Unicode.GetBytes(valueText)));
                                setPropValuesDbCommand.Parameters.Add(this.CreateParameter("propertyvaluesbinary", DbType.Binary, propBuffer));
                                setPropValuesDbCommand.Parameters.Add(this.CreateParameter("lastupdateddate", DbType.DateTime, DateTime.Now));
                                setPropValuesDbCommand.Parameters.Add(this.CreateParameter("userid", DbType.Guid, userID));
                                setPropValuesDbCommand.ExecuteNonQuery();
                            }
                            setPropValuesDbTransaction.Commit();
                        }
                        catch (Exception exception)
                        {
                            setPropValuesDbTransaction.Rollback();
                            throw exception;
                        }
                    }
                    catch (Exception exception)
                    {
                        if (this.WriteExceptionsToEventLog)
                        {
                            this.WriteToEventLog(exception, "SetPropertyValues");
                        }
                        throw new ProviderException(this.exceptionMessage, exception);
                    }
                    finally
                    {
                        setPropValuesDbConnection.Close();
                    }
                }
            }
        }

        /// <summary>
        /// Write exceptions to the event log
        /// </summary>
        /// <param name="e"></param>
        /// <param name="action"></param>
        private void WriteToEventLog(Exception e, string action)
        {
            EventLog log = new EventLog();
            log.Source = this.defaultName;
            log.Log = this.eventLog;
            string message = "An exception occurred communicating with the SharePoint profile store.\n\n";
            message = message + "Action: " + action + "\n\n";
            message = message + "Exception: " + e.ToString();
            log.WriteEntry(message);
        }
    }
}