//------------------------------------------------------------------------------
// <copyright file="AccessProfileProvider.cs" company="Microsoft">
//     Copyright (c) Microsoft Corporation.  All rights reserved.
// </copyright>
//------------------------------------------------------------------------------

namespace SES.SES_Providers {
    using System;
    using System.Web;
    using System.Web.Configuration;
    using System.Web.Profile;
    using System.Security.Principal;
    using System.Security.Permissions;
    using System.Globalization;
    using System.Runtime.Serialization;
    using System.ComponentModel;
    using System.Collections;
    using System.Collections.Specialized;
    using System.Data;
    using System.Data.SqlClient;
    using System.Data.SqlTypes;
    using System.Runtime.Serialization.Formatters.Binary;
    using System.IO;
    using System.Reflection;
    using System.Xml.Serialization;
    using System.Text;
    using System.Configuration.Provider;
    using System.Configuration;
    using System.Data.OleDb;
    using System.Web.Hosting;
    using System.Web.DataAccess;
    using System.Web.Util;

    public class SES_ProfileProvider : ProfileProvider {
        private string _AppName;
        private string _DatabaseSource;
        private int _ApplicationId = 0;
        private int _UserId = 0;
        private DateTime _ApplicationIDCacheDate;


        ////////////////////////////////////////////////////////////
        // Public properties
        // 在執行完 Global.asax 後會先執行這個函數
        // config 的值會是 profile/provider 的集合值 (
        public override void Initialize(string name, NameValueCollection config) {
            if (name == null || name.Length < 1)
                name = "SES_ProfileProvider";

            if (string.IsNullOrEmpty(config["description"])) {
                config.Remove("description");
                config.Add("description", "SES_Provider Profile Provider (DB: WEB_Security in MSSQL)");
            }
            base.Initialize(name, config); //重新覆寫 (ensure name and description value)
            if (config == null)
                throw new ArgumentNullException("config");

            _DatabaseSource = config["connectionStringName"]; //_DatabaseSource = "ConnString" within profile/provider
            if (_DatabaseSource == null || _DatabaseSource.Length < 1)
                throw new ProviderException("Connection name not specified");
            string temp = SES_ConnectionHelper.GetConnectionStringFromConnectionName(_DatabaseSource, true);
            if (temp == null || temp.Length < 1) { //real connectionstring
                throw new ProviderException("Connection string not found" + _DatabaseSource);
            }
            _DatabaseSource = temp; //real connectionstring
            //HandlerBase.CheckAndReadRegistryValue(ref _DatabaseSource, true);
            SES_ConnectionHelper.CheckConnectionString(_DatabaseSource);

            _AppName = config["applicationName"]; //_AppName = "SampleSite"
            if (string.IsNullOrEmpty(_AppName))
                _AppName = SecUtility.GetDefaultAppName();

            if (_AppName.Length > 255) {
                throw new ProviderException("ApplicationName exceeded max length of " + 255);
            }

            //_Description = config["description"];
            config.Remove("connectionStringName");
            config.Remove("applicationName");
            config.Remove("description");
            if (config.Count > 0) {
                string attribUnrecognized = config.GetKey(0);
                if (!String.IsNullOrEmpty(attribUnrecognized))
                    throw new ProviderException("Unrecognized attribute: " + attribUnrecognized);
            }
        }

        /////////////////////////////////////////////////////////////////////////////
        /////////////////////////////////////////////////////////////////////////////
        /////////////////////////////////////////////////////////////////////////////

        public override string ApplicationName {
            get { return _AppName; }
            set {
                if (value.Length > 255)
                    throw new ProviderException("ApplicationName exceeded max length of " + 255);
                if (_AppName != value) {
                    _ApplicationId = 0;
                    _AppName = value;
                }
            }
        }


        public int ApplicationID {
            get { return _ApplicationId;}
        }

        public int UserID {
            get { return _UserId; }
        }

        public string DataSource {
            get { return _DatabaseSource; }
        }


        ////////////////////////////////////////////////////////////
        ////////////////////////////////////////////////////////////
        // 取得 profile/property 的值 (prop 為每一個項目單位)\
        // override SettingsPropertyValueCollection
        // 在執行完 Global.asax 後會先執行這個函數
        ///////////////////////////////////////////////////////////
        public override SettingsPropertyValueCollection GetPropertyValues(SettingsContext sc, SettingsPropertyCollection properties) {
            //SettingsPropertyValueCollection
            //-> 包含設定屬性值的集合，可將 SettingsProperty 物件對應到 SettingsPropertyValue 物件。
            SettingsPropertyValueCollection svc = new SettingsPropertyValueCollection();
             
            // properties -> {System.Configuration.SettingsPropertyCollection}
            // SettingsProperty -> 在內部會當做類別使用，此類別表示與個別組態屬性有關的中繼資料
            // 如果沒有 properties 則回傳一個新物件 svc {SettingsPropertyValueCollection}
            if (properties.Count < 1)
                return svc;

            string username = (string)sc["UserName"]; //SettingsContext -> 提供提供者在保存設定時可以使用的內容資訊。 
            foreach (SettingsProperty prop in properties) {
                if (prop.SerializeAs == SettingsSerializeAs.ProviderSpecific)//設定提供者明確瞭解屬性或其型別，並選取適當的序列化機制。通常用於自訂序列化。
                    if (prop.PropertyType.IsPrimitive || prop.PropertyType == typeof(string))
                        prop.SerializeAs = SettingsSerializeAs.String;
                    else
                        prop.SerializeAs = SettingsSerializeAs.Xml;
                svc.Add(new SettingsPropertyValue(prop));
            }

            if (!String.IsNullOrEmpty(username)) {
                GetPropertyValuesFromDatabase(username, svc);
            }
            return svc;
        }


        private static void ParseDataFromDB(string[] names, string values, byte[] buf, SettingsPropertyValueCollection properties) {
            if (names == null || values == null || buf == null || properties == null)
                return;
            try {
                for (int iter = 0; iter < names.Length / 4; iter++) {
                    string name = names[iter * 4];
                    SettingsPropertyValue pp = properties[name];

                    if (pp == null) // property not found
                        continue;

                    int startPos = Int32.Parse(names[iter * 4 + 2], CultureInfo.InvariantCulture);
                    int length = Int32.Parse(names[iter * 4 + 3], CultureInfo.InvariantCulture);

                    if (length == -1 && !pp.Property.PropertyType.IsValueType) // Null Value
                    {
                        pp.PropertyValue = null;
                        pp.IsDirty = false;
                        pp.Deserialized = true;
                    }
                    /*
                     * names[0] - "lang"
                     * names[1] - "S"
                     * names[2] - "0"
                     * names[3] - "5"
                     * names[4] - ""
                     */
                    if (names[iter * 4 + 1] == "S" && startPos >= 0 && length > 0 && values.Length >= startPos + length) {
                        pp.PropertyValue = Deserialize(pp, values.Substring(startPos, length));
                    }

                    if (names[iter * 4 + 1] == "B" && startPos >= 0 && length > 0 && buf.Length >= startPos + length) {
                        byte[] buf2 = new byte[length];

                        Buffer.BlockCopy(buf, startPos, buf2, 0, length);
                        pp.PropertyValue = Deserialize(pp, buf2);
                    }
                }
            }
            catch { // Eat exceptions
            }
        }

        ////////////////////////////////////////////////////////////
        // 取得 Profile 的相關值 (該資料表的值來自 web.config 的 profile 設定值)
        ////////////////////////////////////////////////////////////
        private void GetPropertyValuesFromDatabase(string username, SettingsPropertyValueCollection svc) {
            try {
                // get connection ( SqlConnection 加上 CreateDateTime --> SES_ConnectionHolder class)
                SES_ConnectionHolder holder = SES_ConnectionHelper.GetConnection(_DatabaseSource, true);
                string[] names = null;
                string values = null;
                SqlDataReader reader = null;
                ////////////////////////////////////////////////////////////
                // Step 1: Get Values from DB
                try {
                    int appId = GetApplicationId(holder);
                    // 取得使用者的 profile (語言...)
                    // 與 SetPropertyValues 不同, 它並不會自動新增使用者至資料庫
                    _UserId = SES_ConnectionHelper.GetUserID(holder.Connection, appId, username, false);

                    if (_UserId != 0) { // User exists?
                        SqlCommand cmd = new SqlCommand(@"SELECT PropertyNames, PropertyValuesString " +
                                                            @"FROM Profile " +
                                                            @"WHERE UserId = @UserId",
                                                            holder.Connection);
                        cmd.Parameters.Add(new SqlParameter("@UserId", _UserId));
                        reader = cmd.ExecuteReader();
                        if (reader.Read()) {
                            names = reader.GetString(0).Split(':');
                            values = reader.GetString(1);
                            reader.Close();
                        }
                        try { // Not a critical part -- don't throw exceptions here
                            cmd = new SqlCommand(@"UPDATE Users SET LastActivityDate=@LastActivityDate WHERE UserId = @UserId", holder.Connection);
                            cmd.Parameters.Add(CreateDateTimeSqlDbParameter("@LastActivityDate", DateTime.Now));
                            cmd.Parameters.Add(new SqlParameter("@UserId", _UserId));
                            cmd.ExecuteNonQuery();
                        }
                        catch{  }
                    }
                }
                catch (Exception e) {
                    throw SES_ConnectionHelper.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(Exception ex){
                System.Diagnostics.Debug.Print(ex.Message);
                throw;
            }
        }

        private static void PrepareDataForSaving(ref string allNames, ref string allValues, ref byte[] buf, bool binarySupported, SettingsPropertyValueCollection properties, bool userIsAuthenticated) {
            StringBuilder names = new StringBuilder();
            StringBuilder values = new StringBuilder();

            MemoryStream ms = (binarySupported ? new System.IO.MemoryStream() : null);
            try {
                try {
                    bool anyItemsToSave = false;

                    foreach (SettingsPropertyValue pp in properties) {
                        if (pp.IsDirty) {
                            if (!userIsAuthenticated) {
                                bool allowAnonymous = (bool)pp.Property.Attributes["AllowAnonymous"];
                                if (!allowAnonymous)
                                    continue;
                            }
                            anyItemsToSave = true;
                            break;
                        }
                    }

                    if (!anyItemsToSave)
                        return;

                    foreach (SettingsPropertyValue pp in properties) {
                        if (!userIsAuthenticated) {
                            bool allowAnonymous = (bool)pp.Property.Attributes["AllowAnonymous"];
                            if (!allowAnonymous)
                                continue;
                        }

                        if (!pp.IsDirty && pp.UsingDefaultValue) // Not fetched from DB and not written to
                            continue;

                        int len = 0, startPos = 0;
                        string propValue = null;

                        if (pp.Deserialized && pp.PropertyValue == null) { // is value null?
                            len = -1;
                        }
                        else {
                            object sVal = SerializePropertyValue(pp);

                            if (sVal == null) {
                                len = -1;
                            }
                            else {
                                if (!(sVal is string) && !binarySupported) {
                                    sVal = Convert.ToBase64String((byte[])sVal);
                                }

                                if (sVal is string) {
                                    propValue = (string)sVal;
                                    len = propValue.Length;
                                    startPos = values.Length;
                                }
                                else {
                                    byte[] b2 = (byte[])sVal;
                                    startPos = (int)ms.Position;
                                    ms.Write(b2, 0, b2.Length);
                                    ms.Position = startPos + b2.Length;
                                    len = b2.Length;
                                }
                            }
                        }

                        names.Append(pp.Name + ":" + ((propValue != null) ? "S" : "B") +
                                     ":" + startPos.ToString(CultureInfo.InvariantCulture) + ":" + len.ToString(CultureInfo.InvariantCulture) + ":");
                        if (propValue != null)
                            values.Append(propValue);
                    }

                    if (binarySupported) {
                        buf = ms.ToArray();
                    }
                }
                finally {
                    if (ms != null)
                        ms.Close();
                }
            }
            catch {
                throw;
            }
            allNames = names.ToString();
            allValues = values.ToString();
        }

        ////////////////////////////////////////////////////////////
        // 更新(or insert) user's Profile
        // update LastTime (LastUpdatedDate, LastActivityDate) into Profile and Users tables
        ////////////////////////////////////////////////////////////
        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
                SES_ConnectionHolder holder = SES_ConnectionHelper.GetConnection(_DatabaseSource, true);
                bool fBeginTransCalled = false;
                try {
                    SqlCommand cmd = new SqlCommand("BEGIN TRANSACTION", holder.Connection);
                    cmd.ExecuteNonQuery();
                    fBeginTransCalled = true;

                    int appId = GetApplicationId(holder);
                    // 設定使用者的 profile (語言...)
                    // 與 GetPropertyValuesFromDatabase 不同, 它會自動新增使用者至資料庫 (Anonymous)
                    _UserId = SES_ConnectionHelper.GetUserID(holder.Connection, appId, username, true, !userIsAuthenticated);

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


        /////////////////////////////////////////////////////////////////////////////
        /////////////////////////////////////////////////////////////////////////////
        private int GetApplicationId(SES_ConnectionHolder holder) {
            if (_ApplicationId != 0 && holder.CreateDate < _ApplicationIDCacheDate) // Already cached?
                return _ApplicationId;
            string appName = _AppName;
            if (appName.Length > 255)
                appName = appName.Substring(0, 255);
            // 取得這個 application name 在資料庫的 application id ,沒有則會存入再取得
            _ApplicationId = SES_ConnectionHelper.GetApplicationID(holder.Connection, appName, true);
            _ApplicationIDCacheDate = DateTime.Now;
            if (_ApplicationId != 0)
                return _ApplicationId;
            throw new ProviderException("Could not get ApplicationId");
        }

        /////////////////////////////////////////////////////////////////////////////
        /////////////////////////////////////////////////////////////////////////////
        /////////////////////////////////////////////////////////////////////////////
        /////////////////////////////////////////////////////////////////////////////
        // 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 {
                SES_ConnectionHolder holder = SES_ConnectionHelper.GetConnection(_DatabaseSource, true);
                bool fBeginTransCalled = false;
                int numDeleted = 0;
                try {
                    SqlCommand cmd = new SqlCommand("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 SqlCommand("COMMIT TRANSACTION", holder.Connection);
                    cmd.ExecuteNonQuery();
                    fBeginTransCalled = false;
                }
                catch (Exception e) {
                    throw SES_ConnectionHelper.GetBetterException(e, holder);
                    throw e;
                }
                finally {
                    if (fBeginTransCalled) {
                        try {
                            SqlCommand command = new SqlCommand("ROLLBACK TRANSACTION", holder.Connection);
                            command.ExecuteNonQuery();
                        }
                        catch { }
                    }
                    holder.Close();
                }
                return numDeleted;
            }
            catch {
                throw;
            }
        }

        /////////////////////////////////////////////////////////////////////////////
        /////////////////////////////////////////////////////////////////////////////
        public override int DeleteProfiles(string[] usernames) {
            SecUtility.CheckArrayParameter(ref usernames,
                                            true,
                                            true,
                                            true,
                                            255,
                                            "usernames");
            try {
                SES_ConnectionHolder holder = SES_ConnectionHelper.GetConnection(_DatabaseSource, true);
                int numDeleted = 0;
                bool fBeginTransCalled = false;
                try {
                    SqlCommand cmd = new SqlCommand("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 SqlCommand("COMMIT TRANSACTION", holder.Connection);
                    cmd.ExecuteNonQuery();
                    fBeginTransCalled = false;
                }
                catch (Exception e) {
                    throw SES_ConnectionHelper.GetBetterException(e, holder);
                    throw e;
                }
                finally {
                    if (fBeginTransCalled) {
                        try {
                            SqlCommand command = new SqlCommand("ROLLBACK TRANSACTION", holder.Connection);
                            command.ExecuteNonQuery();
                        }
                        catch { }
                    }
                    holder.Close();
                }
                return numDeleted;
            }
            catch {
                throw;
            }
        }

        /////////////////////////////////////////////////////////////////////////////
        /////////////////////////////////////////////////////////////////////////////
        public override int DeleteInactiveProfiles(ProfileAuthenticationOption authenticationOption, DateTime userInactiveSinceDate) {
            try {
                SES_ConnectionHolder holder = SES_ConnectionHelper.GetConnection(_DatabaseSource, 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 + ")";
                    SqlCommand cmd = new SqlCommand(sqlQuery, holder.Connection);
                    cmd.Parameters.Add(new SqlParameter("@AppId", GetApplicationId(holder)));
                    cmd.Parameters.Add(CreateDateTimeSqlDbParameter("@LastActivityDate", userInactiveSinceDate));
                    return cmd.ExecuteNonQuery();
                }
                catch (Exception e) {
                    throw SES_ConnectionHelper.GetBetterException(e, holder);
                    throw e;
                }
                finally {
                    holder.Close();
                }
            }
            catch {
                throw;
            }

        }

        /////////////////////////////////////////////////////////////////////////////
        /////////////////////////////////////////////////////////////////////////////
        public override int GetNumberOfInactiveProfiles(ProfileAuthenticationOption authenticationOption, DateTime userInactiveSinceDate) {
            try {
                SES_ConnectionHolder holder = SES_ConnectionHelper.GetConnection(_DatabaseSource, 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);

                    SqlCommand cmd = new SqlCommand(sqlQuery, holder.Connection);
                    cmd.Parameters.Add(new SqlParameter("@AppId", GetApplicationId(holder)));
                    cmd.Parameters.Add(CreateDateTimeSqlDbParameter("@LastActivityDate", userInactiveSinceDate));
                    return (int)cmd.ExecuteScalar();
                }
                catch (Exception e) {
                    throw SES_ConnectionHelper.GetBetterException(e, holder);
                    throw e;
                }
                finally {
                    holder.Close();
                }
            }
            catch {
                throw;
            }
        }

        /////////////////////////////////////////////////////////////////////////////
        /////////////////////////////////////////////////////////////////////////////
        public override ProfileInfoCollection GetAllProfiles(ProfileAuthenticationOption authenticationOption, int pageIndex, int pageSize, out int totalRecords) {
            string sqlQuery = @"SELECT u.UserName, u.IsAnonymous, u.LastActivityDate, p.LastUpdatedDate, LEN(p.PropertyNames) + LEN(p.PropertyValuesString) " +
                              @"FROM aspnet_Users u, aspnet_Profile p " +
                              @"WHERE ApplicationId = @AppId AND u.UserId = p.UserId " +
                                    GetClauseForAuthenticationOptions(authenticationOption);
            SqlParameter[] args = new SqlParameter[0];
            return GetProfilesForQuery(sqlQuery, args, pageIndex, pageSize, out totalRecords);
        }


        /////////////////////////////////////////////////////////////////////////////
        /////////////////////////////////////////////////////////////////////////////
        public override ProfileInfoCollection GetAllInactiveProfiles(ProfileAuthenticationOption authenticationOption, DateTime userInactiveSinceDate, int pageIndex, int pageSize, out int totalRecords) {
            string sqlQuery = @"SELECT u.UserName, u.IsAnonymous, u.LastActivityDate, p.LastUpdatedDate, LEN(p.PropertyNames) + LEN(p.PropertyValuesString) " +
                              @"FROM aspnet_Users u, aspnet_Profile p " +
                              @"WHERE ApplicationId = @AppId AND u.UserId = p.UserId AND u.LastActivityDate <= @LastActivityDate" +
                                   GetClauseForAuthenticationOptions(authenticationOption);

            SqlParameter[] args = new SqlParameter[1];
            args[0] = CreateDateTimeSqlDbParameter("@LastActivityDate", userInactiveSinceDate);
            return GetProfilesForQuery(sqlQuery, args, pageIndex, pageSize, out totalRecords);
        }
        /////////////////////////////////////////////////////////////////////////////
        /////////////////////////////////////////////////////////////////////////////
        public override ProfileInfoCollection FindProfilesByUserName(ProfileAuthenticationOption authenticationOption, string usernameToMatch, int pageIndex, int pageSize, out int totalRecords) {
            SecUtility.CheckParameter(ref usernameToMatch, true, true, false, 255, "username");

            string sqlQuery = @"SELECT u.UserName, u.IsAnonymous, u.LastActivityDate, p.LastUpdatedDate, LEN(p.PropertyNames) + LEN(p.PropertyValuesString) " +
                              @"FROM aspnet_Users u, aspnet_Profile p " +
                              @"WHERE ApplicationId = @AppId AND u.UserId = p.UserId AND u.UserName LIKE @UserName" +
                                  GetClauseForAuthenticationOptions(authenticationOption);
            SqlParameter[] args = new SqlParameter[1];
            args[0] = new SqlParameter("@UserName", usernameToMatch);
            return GetProfilesForQuery(sqlQuery, args, pageIndex, pageSize, out totalRecords);
        }

        /////////////////////////////////////////////////////////////////////////////
        /////////////////////////////////////////////////////////////////////////////
        public override ProfileInfoCollection FindInactiveProfilesByUserName(ProfileAuthenticationOption authenticationOption, string usernameToMatch, DateTime userInactiveSinceDate, int pageIndex, int pageSize, out int totalRecords) {
            SecUtility.CheckParameter(ref usernameToMatch, true, true, false, 255, "usernameToMatch");
            string sqlQuery = @"SELECT u.UserName, u.IsAnonymous, u.LastActivityDate, p.LastUpdatedDate, LEN(p.PropertyNames) + LEN(p.PropertyValuesString) " +
                              @"FROM aspnet_Users u, aspnet_Profile p " +
                              @"WHERE ApplicationId = @AppId AND u.UserId = p.UserId AND u.UserName like @UserName AND u.LastActivityDate <= @LastActivityDate" +
                                     GetClauseForAuthenticationOptions(authenticationOption);
            SqlParameter[] args = new SqlParameter[2];
            args[0] = new SqlParameter("@UserName", usernameToMatch);
            args[1] = CreateDateTimeSqlDbParameter("@LastActivityDate", userInactiveSinceDate);
            return GetProfilesForQuery(sqlQuery, args, pageIndex, pageSize, out totalRecords);
        }

        /////////////////////////////////////////////////////////////////////////////
        /////////////////////////////////////////////////////////////////////////////
        // Private methods

        /////////////////////////////////////////////////////////////////////////////
        /////////////////////////////////////////////////////////////////////////////
        private ProfileInfoCollection GetProfilesForQuery(string sqlQuery, SqlParameter[] 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 {
                SES_ConnectionHolder holder = SES_ConnectionHelper.GetConnection(_DatabaseSource, true);
                ProfileInfoCollection profiles = new ProfileInfoCollection();
                SqlDataReader reader = null;
                try {
                    SqlCommand cmd = new SqlCommand(sqlQuery, holder.Connection);
                    cmd.Parameters.Add(new SqlParameter("@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 SES_ConnectionHelper.GetBetterException(e, holder);
                    throw e;
                }
                finally {
                    if (reader != null)
                        reader.Close();
                    holder.Close();
                }
            }
            catch {
                throw;
            }

        }

        /////////////////////////////////////////////////////////////////////////////
        /////////////////////////////////////////////////////////////////////////////
        private bool DeleteProfile(SES_ConnectionHolder holder, string username, int appId) {
            SecUtility.CheckParameter(ref username, true, true, true, 255, "username");

            int userId = SES_ConnectionHelper.GetUserID(holder.Connection, appId, username, false);
            if (userId == 0)
                return false;
            SqlCommand cmd = new SqlCommand(@"DELETE FROM aspnet_Profile WHERE UserId = @UserId", holder.Connection);
            cmd.Parameters.Add(new SqlParameter("@UserId", userId));
            return (cmd.ExecuteNonQuery() != 0);
        }
        /////////////////////////////////////////////////////////////////////////////
        /////////////////////////////////////////////////////////////////////////////
        static private string GetClauseForAuthenticationOptions(ProfileAuthenticationOption authenticationOption) {
            switch (authenticationOption) {
                case ProfileAuthenticationOption.Anonymous:
                    return " AND IsAnonymous=Yes ";

                case ProfileAuthenticationOption.Authenticated:
                    return " AND IsAnonymous=No ";

                case ProfileAuthenticationOption.All:
                    return " ";
            }
            return " ";
        }
        private SqlParameter CreateDateTimeSqlDbParameter(string parameterName, DateTime dt) {
            SqlParameter p = new SqlParameter(parameterName, SqlDbType.DateTime);// SqlDbType.Timestamp);
            p.Direction = ParameterDirection.Input;
            p.Value = SES_ConnectionHelper.RoundToSeconds_S(dt);
            return p;
        }

        private static string ConvertObjectToString(object propValue, Type type, SettingsSerializeAs serializeAs, bool throwOnError) {
            if (serializeAs == SettingsSerializeAs.ProviderSpecific) {
                if (type == typeof(string) || type.IsPrimitive)
                    serializeAs = SettingsSerializeAs.String;
                else
                    serializeAs = SettingsSerializeAs.Xml;
            }

            try {
                switch (serializeAs) {
                    case SettingsSerializeAs.String:
                        TypeConverter converter = TypeDescriptor.GetConverter(type);
                        if (converter != null && converter.CanConvertTo(typeof(String)) && converter.CanConvertFrom(typeof(String)))
                            return converter.ConvertToString(propValue);
                        throw new ArgumentException("Unable to convert type " + type.ToString() + " to string", "type");
                    case SettingsSerializeAs.Binary:
                        MemoryStream ms = new System.IO.MemoryStream();
                        try {
                            BinaryFormatter bf = new BinaryFormatter();
                            bf.Serialize(ms, propValue);
                            byte[] buffer = ms.ToArray();
                            return Convert.ToBase64String(buffer);
                        }
                        finally {
                            ms.Close();
                        }

                    case SettingsSerializeAs.Xml:
                        XmlSerializer xs = new XmlSerializer(type);
                        StringWriter sw = new StringWriter(CultureInfo.InvariantCulture);

                        xs.Serialize(sw, propValue);
                        return sw.ToString();
                }
            }
            catch (Exception) {
                if (throwOnError)
                    throw;
            }
            return null;
        }
        private static object SerializePropertyValue(SettingsPropertyValue prop) {
            object val = prop.PropertyValue;
            if (val == null)
                return null;

            if (prop.Property.SerializeAs != SettingsSerializeAs.Binary)
                return ConvertObjectToString(val, prop.Property.PropertyType, prop.Property.SerializeAs, prop.Property.ThrowOnErrorSerializing);

            MemoryStream ms = new System.IO.MemoryStream();
            try {
                BinaryFormatter bf = new BinaryFormatter();
                bf.Serialize(ms, val);
                return ms.ToArray();
            }
            finally {
                ms.Close();
            }
        }

        private static object Deserialize(SettingsPropertyValue prop, object obj) {
            object val = null;

            //////////////////////////////////////////////
            /// Step 1: Try creating from Serailized value
            if (obj != null) {
                try {
                    if (obj is string) {
                        val = GetObjectFromString(prop.Property.PropertyType, prop.Property.SerializeAs, (string)obj);
                    }
                    else {
                        MemoryStream ms = new System.IO.MemoryStream((byte[])obj);
                        try {
                            val = (new BinaryFormatter()).Deserialize(ms);
                        }
                        finally {
                            ms.Close();
                        }
                    }
                }
                catch {
                }

                if (val != null && !prop.Property.PropertyType.IsAssignableFrom(val.GetType())) // is it the correct type
                    val = null;
            }

            //////////////////////////////////////////////
            /// Step 2: Try creating from default value
            if (val == null) {
                if (prop.Property.DefaultValue == null || prop.Property.DefaultValue.ToString() == "[null]") {
                    if (prop.Property.PropertyType.IsValueType)
                        return Activator.CreateInstance(prop.Property.PropertyType);
                    else
                        return null;
                }
                if (!(prop.Property.DefaultValue is string)) {
                    val = prop.Property.DefaultValue;
                }
                else {
                    try {
                        val = GetObjectFromString(prop.Property.PropertyType, prop.Property.SerializeAs, (string)prop.Property.DefaultValue);
                    }
                    catch (Exception e) {
                        throw new ArgumentException("Could not create from default value for property: " + prop.Property.Name, e.Message);
                    }
                }
                if (val != null && !prop.Property.PropertyType.IsAssignableFrom(val.GetType())) // is it the correct type
                    throw new ArgumentException("Could not create from default value for property: " + prop.Property.Name);
            }

            //////////////////////////////////////////////
            /// Step 3: Create a new one by calling the parameterless constructor
            if (val == null) {
                if (prop.Property.PropertyType == typeof(string)) {
                    val = "";
                }
                else {
                    try {
                        val = Activator.CreateInstance(prop.Property.PropertyType);
                    }
                    catch { }
                }
            }
            return val;
        }

        private static object GetObjectFromString(Type type, SettingsSerializeAs serializeAs, string attValue) {
            // Deal with string types
            if (type == typeof(string) && (attValue == null || attValue.Length < 1 || serializeAs == SettingsSerializeAs.String))
                return attValue;

            // Return null if there is nothing to convert
            if (attValue == null || attValue.Length < 1)
                return null;

            // Convert based on the serialized type
            switch (serializeAs) {
                case SettingsSerializeAs.Binary:
                    byte[] buf = Convert.FromBase64String(attValue);
                    MemoryStream ms = null;
                    try {
                        ms = new System.IO.MemoryStream(buf);
                        return (new BinaryFormatter()).Deserialize(ms);
                    }
                    finally {
                        if (ms != null)
                            ms.Close();
                    }

                case SettingsSerializeAs.Xml:
                    StringReader sr = new StringReader(attValue);
                    XmlSerializer xs = new XmlSerializer(type);
                    return xs.Deserialize(sr);

                case SettingsSerializeAs.String:
                    TypeConverter converter = TypeDescriptor.GetConverter(type);
                    if (converter != null && converter.CanConvertTo(typeof(String)) && converter.CanConvertFrom(typeof(String)))
                        return converter.ConvertFromString(attValue);
                    throw new ArgumentException("Unable to convert type: " + type.ToString() + " from string", "type");

                default:
                    return null;
            }
        }

    }
}
