SqlPersonalizationProvider.cs source code in C# .NET

Source code for the .NET framework in C#

                        

Code:

/ FXUpdate3074 / FXUpdate3074 / 1.1 / DEVDIV / depot / DevDiv / releases / whidbey / QFE / ndp / fx / src / xsp / System / Web / UI / WebParts / SqlPersonalizationProvider.cs / 1 / SqlPersonalizationProvider.cs

                            //------------------------------------------------------------------------------ 
// 
//     Copyright (c) Microsoft Corporation.  All rights reserved.
// 
//----------------------------------------------------------------------------- 

namespace System.Web.UI.WebControls.WebParts { 
 
    using System;
    using System.Collections; 
    using System.Collections.Specialized;
    using System.Configuration.Provider;
    using System.ComponentModel;
    using System.Data; 
    using System.Data.SqlClient;
    using System.Globalization; 
    using System.Security.Permissions; 
    using System.Web;
    using System.Web.DataAccess; 
    using System.Web.Util;

    /// 
    /// The provider used to access the personalization store for WebPart pages from a SQL Server 
    /// database.
    ///  
    [AspNetHostingPermission(SecurityAction.LinkDemand, Level=AspNetHostingPermissionLevel.Minimal)] 
    [AspNetHostingPermission(SecurityAction.InheritanceDemand, Level=AspNetHostingPermissionLevel.Minimal)]
    public class SqlPersonalizationProvider : PersonalizationProvider { 

        private enum ResetUserStateMode {
            PerInactiveDate,
            PerPaths, 
            PerUsers
        } 
 
        private const int maxStringLength = 256;
 
        private string _applicationName;
        private int    _commandTimeout;
        private string _connectionString;
        private int    _SchemaVersionCheck; 

        ///  
        /// Initializes an instance of SqlPersonalizationProvider. 
        /// 
        public SqlPersonalizationProvider() { 
        }

        public override string ApplicationName {
            get { 
                if (String.IsNullOrEmpty(_applicationName)) {
                    _applicationName = SecUtility.GetDefaultAppName(); 
                } 
                return _applicationName;
            } 
            set {
                if (value != null && value.Length > maxStringLength) {
                    throw new ProviderException(SR.GetString(
                        SR.PersonalizationProvider_ApplicationNameExceedMaxLength, maxStringLength.ToString(CultureInfo.CurrentCulture))); 
                }
                _applicationName = value; 
            } 
        }
 
        /// 
        /// 
        private SqlParameter CreateParameter(string name, SqlDbType dbType, object value) {
            SqlParameter param = new SqlParameter(name, dbType); 

            param.Value = value; 
            return param; 
        }
 
        private PersonalizationStateInfoCollection FindSharedState(string path,
                                                                   int pageIndex,
                                                                   int pageSize,
                                                                   out int totalRecords) { 
            SqlConnectionHolder connectionHolder = null;
            SqlConnection connection = null; 
            SqlDataReader reader = null; 
            totalRecords = 0;
 
            // Extra try-catch block to prevent elevation of privilege attack via exception filter
            try {
                try {
                    connectionHolder = GetConnectionHolder(); 
                    connection = connectionHolder.Connection;
                    Debug.Assert(connection != null); 
 
                    CheckSchemaVersion( connection );
 
                    SqlCommand command = new SqlCommand("dbo.aspnet_PersonalizationAdministration_FindState", connection);
                    SetCommandTypeAndTimeout(command);
                    SqlParameterCollection parameters = command.Parameters;
 
                    SqlParameter parameter = parameters.Add(new SqlParameter("AllUsersScope", SqlDbType.Bit));
                    parameter.Value = true; 
 
                    parameters.AddWithValue("ApplicationName", ApplicationName);
                    parameters.AddWithValue("PageIndex", pageIndex); 
                    parameters.AddWithValue("PageSize", pageSize);

                    SqlParameter returnValue = new SqlParameter("@ReturnValue", SqlDbType.Int);
                    returnValue.Direction = ParameterDirection.ReturnValue; 
                    parameters.Add(returnValue);
 
                    parameter = parameters.Add("Path", SqlDbType.NVarChar); 
                    if (path != null) {
                        parameter.Value = path; 
                    }

                    parameter = parameters.Add("UserName", SqlDbType.NVarChar);
                    parameter = parameters.Add("InactiveSinceDate", SqlDbType.DateTime); 

                    reader = command.ExecuteReader(CommandBehavior.SequentialAccess); 
                    PersonalizationStateInfoCollection sharedStateInfoCollection = new PersonalizationStateInfoCollection(); 

                    if (reader != null) { 
                        if (reader.HasRows) {
                            while(reader.Read()) {
                                string returnedPath = reader.GetString(0);
 
                                // Data can be null if there is no data associated with the path
                                DateTime lastUpdatedDate = (reader.IsDBNull(1)) ? DateTime.MinValue : 
                                                                DateTime.SpecifyKind(reader.GetDateTime(1), DateTimeKind.Utc); 
                                int size = (reader.IsDBNull(2)) ? 0 : reader.GetInt32(2);
                                int userDataSize = (reader.IsDBNull(3)) ? 0 : reader.GetInt32(3); 
                                int userCount = (reader.IsDBNull(4)) ? 0 : reader.GetInt32(4);
                                sharedStateInfoCollection.Add(new SharedPersonalizationStateInfo(
                                    returnedPath, lastUpdatedDate, size, userDataSize, userCount));
                            } 
                        }
 
                        // The reader needs to be closed so return value can be accessed 
                        // See MSDN doc for SqlParameter.Direction for details.
                        reader.Close(); 
                        reader = null;
                    }

                    // Set the total count at the end after all operations pass 
                    if (returnValue.Value != null && returnValue.Value is int) {
                        totalRecords = (int)returnValue.Value; 
                    } 

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

        public override PersonalizationStateInfoCollection FindState(PersonalizationScope scope, 
                                                                     PersonalizationStateQuery query,
                                                                     int pageIndex, int pageSize,
                                                                     out int totalRecords) {
            PersonalizationProviderHelper.CheckPersonalizationScope(scope); 
            PersonalizationProviderHelper.CheckPageIndexAndSize(pageIndex, pageSize);
 
            if (scope == PersonalizationScope.Shared) { 
                string pathToMatch = null;
                if (query != null) { 
                    pathToMatch = StringUtil.CheckAndTrimString(query.PathToMatch, "query.PathToMatch", false, maxStringLength);
                }
                return FindSharedState(pathToMatch, pageIndex, pageSize, out totalRecords);
            } 
            else {
                string pathToMatch = null; 
                DateTime inactiveSinceDate = PersonalizationAdministration.DefaultInactiveSinceDate; 
                string usernameToMatch = null;
                if (query != null) { 
                    pathToMatch = StringUtil.CheckAndTrimString(query.PathToMatch, "query.PathToMatch", false, maxStringLength);
                    inactiveSinceDate = query.UserInactiveSinceDate;
                    usernameToMatch = StringUtil.CheckAndTrimString(query.UsernameToMatch, "query.UsernameToMatch", false, maxStringLength);
                } 

                return FindUserState(pathToMatch, inactiveSinceDate, usernameToMatch, 
                                     pageIndex, pageSize, out totalRecords); 
            }
        } 

        private PersonalizationStateInfoCollection FindUserState(string path,
                                                                 DateTime inactiveSinceDate,
                                                                 string username, 
                                                                 int pageIndex,
                                                                 int pageSize, 
                                                                 out int totalRecords) { 
            SqlConnectionHolder connectionHolder = null;
            SqlConnection connection = null; 
            SqlDataReader reader = null;
            totalRecords = 0;

            // Extra try-catch block to prevent elevation of privilege attack via exception filter 
            try {
                try { 
                    connectionHolder = GetConnectionHolder(); 
                    connection = connectionHolder.Connection;
                    Debug.Assert(connection != null); 

                    CheckSchemaVersion( connection );

                    SqlCommand command = new SqlCommand("dbo.aspnet_PersonalizationAdministration_FindState", connection); 
                    SetCommandTypeAndTimeout(command);
                    SqlParameterCollection parameters = command.Parameters; 
 
                    SqlParameter parameter = parameters.Add(new SqlParameter("AllUsersScope", SqlDbType.Bit));
                    parameter.Value = false; 

                    parameters.AddWithValue("ApplicationName", ApplicationName);
                    parameters.AddWithValue("PageIndex", pageIndex);
                    parameters.AddWithValue("PageSize", pageSize); 

                    SqlParameter returnValue = new SqlParameter("@ReturnValue", SqlDbType.Int); 
                    returnValue.Direction = ParameterDirection.ReturnValue; 
                    parameters.Add(returnValue);
 
                    parameter = parameters.Add("Path", SqlDbType.NVarChar);
                    if (path != null) {
                        parameter.Value = path;
                    } 

                    parameter = parameters.Add("UserName", SqlDbType.NVarChar); 
                    if (username != null) { 
                        parameter.Value = username;
                    } 

                    parameter = parameters.Add("InactiveSinceDate", SqlDbType.DateTime);
                    if (inactiveSinceDate != PersonalizationAdministration.DefaultInactiveSinceDate) {
                        parameter.Value = inactiveSinceDate.ToUniversalTime(); 
                    }
 
                    reader = command.ExecuteReader(CommandBehavior.SequentialAccess); 
                    PersonalizationStateInfoCollection stateInfoCollection = new PersonalizationStateInfoCollection();
 
                    if (reader != null) {
                        if (reader.HasRows) {
                            while(reader.Read()) {
                                string returnedPath = reader.GetString(0); 
                                DateTime lastUpdatedDate = DateTime.SpecifyKind(reader.GetDateTime(1), DateTimeKind.Utc);
                                int size = reader.GetInt32(2); 
                                string returnedUsername = reader.GetString(3); 
                                DateTime lastActivityDate = DateTime.SpecifyKind(reader.GetDateTime(4), DateTimeKind.Utc);
                                stateInfoCollection.Add(new UserPersonalizationStateInfo( 
                                                                returnedPath, lastUpdatedDate,
                                                                size, returnedUsername, lastActivityDate));
                            }
                        } 

                        // The reader needs to be closed so return value can be accessed 
                        // See MSDN doc for SqlParameter.Direction for details. 
                        reader.Close();
                        reader = null; 
                    }

                    // Set the total count at the end after all operations pass
                    if (returnValue.Value != null && returnValue.Value is int) { 
                        totalRecords = (int)returnValue.Value;
                    } 
 
                    return stateInfoCollection;
                } 
                finally {
                    if (reader != null) {
                        reader.Close();
                    } 

                    if (connectionHolder != null) { 
                        connectionHolder.Close(); 
                        connectionHolder = null;
                    } 
                }
            }
            catch {
                throw; 
            }
        } 
 
        /// 
        ///  
        private SqlConnectionHolder GetConnectionHolder() {
            SqlConnection connection = null;
            SqlConnectionHolder connectionHolder = SqlConnectionHelper.GetConnection(_connectionString, true);
 
            if (connectionHolder != null) {
                connection = connectionHolder.Connection; 
            } 
            if (connection == null) {
                throw new ProviderException(SR.GetString(SR.PersonalizationProvider_CantAccess, Name)); 
            }

            return connectionHolder;
        } 

        private int GetCountOfSharedState(string path) { 
            SqlConnectionHolder connectionHolder = null; 
            SqlConnection connection = null;
            int count = 0; 

            // Extra try-catch block to prevent elevation of privilege attack via exception filter
            try {
                try { 
                    connectionHolder = GetConnectionHolder();
                    connection = connectionHolder.Connection; 
                    Debug.Assert(connection != null); 

                    CheckSchemaVersion( connection ); 

                    SqlCommand command = new SqlCommand("dbo.aspnet_PersonalizationAdministration_GetCountOfState", connection);
                    SetCommandTypeAndTimeout(command);
                    SqlParameterCollection parameters = command.Parameters; 

                    SqlParameter parameter = parameters.Add(new SqlParameter("Count", SqlDbType.Int)); 
                    parameter.Direction = ParameterDirection.Output; 

                    parameter = parameters.Add(new SqlParameter("AllUsersScope", SqlDbType.Bit)); 
                    parameter.Value = true;

                    parameters.AddWithValue("ApplicationName", ApplicationName);
 
                    parameter = parameters.Add("Path", SqlDbType.NVarChar);
                    if (path != null) { 
                        parameter.Value = path; 
                    }
 
                    parameter = parameters.Add("UserName", SqlDbType.NVarChar);
                    parameter = parameters.Add("InactiveSinceDate", SqlDbType.DateTime);

                    command.ExecuteNonQuery(); 
                    parameter = command.Parameters[0];
                    if (parameter != null && parameter.Value != null && parameter.Value is Int32) { 
                        count = (Int32) parameter.Value; 
                    }
                } 
                finally {
                    if (connectionHolder != null) {
                        connectionHolder.Close();
                        connectionHolder = null; 
                    }
                } 
            } 
            catch {
                throw; 
            }

            return count;
        } 

        public override int GetCountOfState(PersonalizationScope scope, PersonalizationStateQuery query) { 
            PersonalizationProviderHelper.CheckPersonalizationScope(scope); 
            if (scope == PersonalizationScope.Shared) {
                string pathToMatch = null; 
                if (query != null) {
                    pathToMatch = StringUtil.CheckAndTrimString(query.PathToMatch, "query.PathToMatch", false, maxStringLength);
                }
                return GetCountOfSharedState(pathToMatch); 
            }
            else { 
                string pathToMatch = null; 
                DateTime userInactiveSinceDate = PersonalizationAdministration.DefaultInactiveSinceDate;
                string usernameToMatch = null; 
                if (query != null) {
                    pathToMatch = StringUtil.CheckAndTrimString(query.PathToMatch, "query.PathToMatch", false, maxStringLength);
                    userInactiveSinceDate = query.UserInactiveSinceDate;
                    usernameToMatch = StringUtil.CheckAndTrimString(query.UsernameToMatch, "query.UsernameToMatch", false, maxStringLength); 
                }
                return GetCountOfUserState(pathToMatch, userInactiveSinceDate, usernameToMatch); 
            } 
        }
 
        private int GetCountOfUserState(string path, DateTime inactiveSinceDate, string username) {
            SqlConnectionHolder connectionHolder = null;
            SqlConnection connection = null;
            int count = 0; 

            // Extra try-catch block to prevent elevation of privilege attack via exception filter 
            try { 
                try {
                    connectionHolder = GetConnectionHolder(); 
                    connection = connectionHolder.Connection;
                    Debug.Assert(connection != null);

                    CheckSchemaVersion( connection ); 

                    SqlCommand command = new SqlCommand("dbo.aspnet_PersonalizationAdministration_GetCountOfState", connection); 
                    SetCommandTypeAndTimeout(command); 
                    SqlParameterCollection parameters = command.Parameters;
 
                    SqlParameter parameter = parameters.Add(new SqlParameter("Count", SqlDbType.Int));
                    parameter.Direction = ParameterDirection.Output;

                    parameter = parameters.Add(new SqlParameter("AllUsersScope", SqlDbType.Bit)); 
                    parameter.Value = false;
 
                    parameters.AddWithValue("ApplicationName", ApplicationName); 

                    parameter = parameters.Add("Path", SqlDbType.NVarChar); 
                    if (path != null) {
                        parameter.Value = path;
                    }
 
                    parameter = parameters.Add("UserName", SqlDbType.NVarChar);
                    if (username != null) { 
                        parameter.Value = username; 
                    }
 
                    parameter = parameters.Add("InactiveSinceDate", SqlDbType.DateTime);
                    if (inactiveSinceDate != PersonalizationAdministration.DefaultInactiveSinceDate) {
                        parameter.Value = inactiveSinceDate.ToUniversalTime();
                    } 

                    command.ExecuteNonQuery(); 
                    parameter = command.Parameters[0]; 
                    if (parameter != null && parameter.Value != null && parameter.Value is Int32) {
                        count = (Int32) parameter.Value; 
                    }
                }
                finally {
                    if (connectionHolder != null) { 
                        connectionHolder.Close();
                        connectionHolder = null; 
                    } 
                }
            } 
            catch {
                throw;
            }
 
            return count;
        } 
 
        public override void Initialize(string name, NameValueCollection configSettings) {
            HttpRuntime.CheckAspNetHostingPermission(AspNetHostingPermissionLevel.Low, SR.Feature_not_supported_at_this_level); 

            // configSettings cannot be null because there are required settings needed below
            if (configSettings == null) {
                throw new ArgumentNullException("configSettings"); 
            }
 
            if (String.IsNullOrEmpty(name)) { 
                name = "SqlPersonalizationProvider";
            } 

            // description will be set from the base class' Initialize method
            if (string.IsNullOrEmpty(configSettings["description"])) {
                configSettings.Remove("description"); 
                configSettings.Add("description", SR.GetString(SR.SqlPersonalizationProvider_Description));
            } 
            base.Initialize(name, configSettings); 

            _SchemaVersionCheck = 0; 

            // If not available, the default value is set in the get accessor of ApplicationName
            _applicationName = configSettings["applicationName"];
            if (_applicationName != null) { 
                configSettings.Remove("applicationName");
 
                if (_applicationName.Length > maxStringLength) { 
                    throw new ProviderException(SR.GetString(
                        SR.PersonalizationProvider_ApplicationNameExceedMaxLength, maxStringLength.ToString(CultureInfo.CurrentCulture))); 
                }
            }

            string connectionStringName = configSettings["connectionStringName"]; 
            if (String.IsNullOrEmpty(connectionStringName)) {
                throw new ProviderException(SR.GetString(SR.PersonalizationProvider_NoConnection)); 
            } 
            configSettings.Remove("connectionStringName");
 
            string connectionString = SqlConnectionHelper.GetConnectionString(connectionStringName, true, true);
            if (String.IsNullOrEmpty(connectionString)) {
                throw new ProviderException(SR.GetString(SR.PersonalizationProvider_BadConnection, connectionStringName));
            } 
            _connectionString = connectionString;
 
            _commandTimeout = SecUtility.GetIntValue(configSettings, "commandTimeout", -1, true, 0); 
            configSettings.Remove("commandTimeout");
 
            if (configSettings.Count > 0) {
                string invalidAttributeName = configSettings.GetKey(0);
                throw new ProviderException(SR.GetString(SR.PersonalizationProvider_UnknownProp, invalidAttributeName, name));
            } 
        }
 
        private void CheckSchemaVersion( SqlConnection connection ) 
        {
            string[] features = { "Personalization" }; 
            string   version  = "1";

            SecUtility.CheckSchemaVersion( this,
                                           connection, 
                                           features,
                                           version, 
                                           ref _SchemaVersionCheck ); 
        }
 
        /// 
        /// 
        private byte[] LoadPersonalizationBlob(SqlConnection connection, string path, string userName) {
            Debug.Assert(connection != null); 
            Debug.Assert(!String.IsNullOrEmpty(path));
 
            SqlCommand command; 

            if (userName != null) { 
                command = new SqlCommand("dbo.aspnet_PersonalizationPerUser_GetPageSettings", connection);
            }
            else {
                command = new SqlCommand("dbo.aspnet_PersonalizationAllUsers_GetPageSettings", connection); 
            }
 
            SetCommandTypeAndTimeout(command); 
            command.Parameters.Add(CreateParameter("@ApplicationName", SqlDbType.NVarChar, this.ApplicationName));
            command.Parameters.Add(CreateParameter("@Path", SqlDbType.NVarChar, path)); 
            if (userName != null) {
                command.Parameters.Add(CreateParameter("@UserName", SqlDbType.NVarChar, userName));
                command.Parameters.Add(CreateParameter("@CurrentTimeUtc", SqlDbType.DateTime, DateTime.UtcNow));
            } 

            SqlDataReader reader = null; 
            try { 
                reader = command.ExecuteReader(CommandBehavior.SingleRow);
                if (reader.Read()) { 
                    int length = (int)reader.GetBytes(0, 0, null, 0, 0);
                    byte[] state = new byte[length];

                    reader.GetBytes(0, 0, state, 0, length); 
                    return state;
                } 
            } 
            finally {
                if (reader != null) { 
                    reader.Close();
                }
            }
 
            return null;
        } 
 
        /// 
        protected override void LoadPersonalizationBlobs(WebPartManager webPartManager, string path, string userName, ref byte[] sharedDataBlob, ref byte[] userDataBlob) { 
            sharedDataBlob = null;
            userDataBlob = null;

            SqlConnectionHolder connectionHolder = null; 
            SqlConnection connection = null;
 
            // Extra try-catch block to prevent elevation of privilege attack via exception filter 
            try {
                try { 
                    connectionHolder = GetConnectionHolder();
                    connection = connectionHolder.Connection;

                    CheckSchemaVersion( connection ); 

                    sharedDataBlob = LoadPersonalizationBlob(connection, path, null); 
                    if (!String.IsNullOrEmpty(userName)) { 
                        userDataBlob = LoadPersonalizationBlob(connection, path, userName);
                    } 
                }
                finally {
                    if (connectionHolder != null) {
                        connectionHolder.Close(); 
                        connectionHolder = null;
                    } 
                } 
            }
            catch { 
                throw;
            }
        }
 
        /// 
        ///  
        private void ResetPersonalizationState(SqlConnection connection, string path, string userName) { 
            Debug.Assert(connection != null);
            Debug.Assert(!String.IsNullOrEmpty(path)); 

            SqlCommand command;

            if (userName != null) { 
                command = new SqlCommand("dbo.aspnet_PersonalizationPerUser_ResetPageSettings", connection);
            } 
            else { 
                command = new SqlCommand("dbo.aspnet_PersonalizationAllUsers_ResetPageSettings", connection);
            } 

            SetCommandTypeAndTimeout(command);
            command.Parameters.Add(CreateParameter("@ApplicationName", SqlDbType.NVarChar, ApplicationName));
            command.Parameters.Add(CreateParameter("@Path", SqlDbType.NVarChar, path)); 
            if (userName != null) {
                command.Parameters.Add(CreateParameter("@UserName", SqlDbType.NVarChar, userName)); 
                command.Parameters.Add(CreateParameter("@CurrentTimeUtc", SqlDbType.DateTime, DateTime.UtcNow)); 
            }
 
            command.ExecuteNonQuery();
        }

        ///  
        protected override void ResetPersonalizationBlob(WebPartManager webPartManager, string path, string userName) {
            SqlConnectionHolder connectionHolder = null; 
            SqlConnection connection = null; 

            // Extra try-catch block to prevent elevation of privilege attack via exception filter 
            try {
                try {
                    connectionHolder = GetConnectionHolder();
                    connection = connectionHolder.Connection; 

                    CheckSchemaVersion( connection ); 
 
                    ResetPersonalizationState(connection, path, userName);
                } 
                finally {
                    if (connectionHolder != null) {
                        connectionHolder.Close();
                        connectionHolder = null; 
                    }
                } 
            } 
            catch {
                throw; 
            }
        }

        private int ResetAllState(PersonalizationScope scope) { 
            SqlConnectionHolder connectionHolder = null;
            SqlConnection connection = null; 
            int count = 0; 

            // Extra try-catch block to prevent elevation of privilege attack via exception filter 
            try {
                try {
                    connectionHolder = GetConnectionHolder();
                    connection = connectionHolder.Connection; 
                    Debug.Assert(connection != null);
 
                    CheckSchemaVersion( connection ); 

                    SqlCommand command = new SqlCommand("dbo.aspnet_PersonalizationAdministration_DeleteAllState", connection); 
                    SetCommandTypeAndTimeout(command);
                    SqlParameterCollection parameters = command.Parameters;

                    SqlParameter parameter = parameters.Add(new SqlParameter("AllUsersScope", SqlDbType.Bit)); 
                    parameter.Value = (scope == PersonalizationScope.Shared);
 
                    parameters.AddWithValue("ApplicationName", ApplicationName); 

                    parameter = parameters.Add(new SqlParameter("Count", SqlDbType.Int)); 
                    parameter.Direction = ParameterDirection.Output;

                    command.ExecuteNonQuery();
                    parameter = command.Parameters[2]; 
                    if (parameter != null && parameter.Value != null && parameter.Value is Int32) {
                        count = (Int32) parameter.Value; 
                    } 
                }
                finally { 
                    if (connectionHolder != null) {
                        connectionHolder.Close();
                        connectionHolder = null;
                    } 
                }
            } 
            catch { 
                throw;
            } 

            return count;
        }
 
        private int ResetSharedState(string[] paths) {
            int resultCount = 0; 
 
            if (paths == null) {
                resultCount = ResetAllState(PersonalizationScope.Shared); 
            }
            else {
                SqlConnectionHolder connectionHolder = null;
                SqlConnection connection = null; 

                // Extra try-catch block to prevent elevation of privilege attack via exception filter 
                try { 
                    bool beginTranCalled = false;
                    try { 
                        connectionHolder = GetConnectionHolder();
                        connection = connectionHolder.Connection;
                        Debug.Assert(connection != null);
 
                        CheckSchemaVersion( connection );
 
                        SqlCommand command = new SqlCommand("dbo.aspnet_PersonalizationAdministration_ResetSharedState", connection); 
                        SetCommandTypeAndTimeout(command);
                        SqlParameterCollection parameters = command.Parameters; 

                        SqlParameter parameter = parameters.Add(new SqlParameter("Count", SqlDbType.Int));
                        parameter.Direction = ParameterDirection.Output;
 
                        parameters.AddWithValue("ApplicationName", ApplicationName);
 
                        parameter = parameters.Add("Path", SqlDbType.NVarChar); 
                        foreach (string path in paths) {
                            if (!beginTranCalled && paths.Length > 1) { 
                                (new SqlCommand("BEGIN TRANSACTION", connection)).ExecuteNonQuery();
                                beginTranCalled = true;
                            }
 
                            parameter.Value = path;
                            command.ExecuteNonQuery(); 
                            SqlParameter countParam = command.Parameters[0]; 
                            if (countParam != null && countParam.Value != null && countParam.Value is Int32) {
                                resultCount += (Int32) countParam.Value; 
                            }
                        }

                        if (beginTranCalled) { 
                            (new SqlCommand("COMMIT TRANSACTION", connection)).ExecuteNonQuery();
                            beginTranCalled = false; 
                        } 
                    }
                    catch { 
                        if (beginTranCalled) {
                            (new SqlCommand("ROLLBACK TRANSACTION", connection)).ExecuteNonQuery();
                            beginTranCalled = false;
                        } 
                        throw;
                    } 
                    finally { 
                       if (connectionHolder != null) {
                            connectionHolder.Close(); 
                            connectionHolder = null;
                        }
                    }
                } 
                catch {
                    throw; 
                } 
            }
 
            return resultCount;
        }

        public override int ResetUserState(string path, DateTime userInactiveSinceDate) { 
            path = StringUtil.CheckAndTrimString(path, "path", false, maxStringLength);
            string [] paths = (path == null) ? null : new string [] {path}; 
            return ResetUserState(ResetUserStateMode.PerInactiveDate, 
                                  userInactiveSinceDate, paths, null);
        } 

        public override int ResetState(PersonalizationScope scope, string[] paths, string[] usernames) {
            PersonalizationProviderHelper.CheckPersonalizationScope(scope);
            paths = PersonalizationProviderHelper.CheckAndTrimNonEmptyStringEntries(paths, "paths", false, false, maxStringLength); 
            usernames = PersonalizationProviderHelper.CheckAndTrimNonEmptyStringEntries(usernames, "usernames", false, true, maxStringLength);
 
            if (scope == PersonalizationScope.Shared) { 
                PersonalizationProviderHelper.CheckUsernamesInSharedScope(usernames);
                return ResetSharedState(paths); 
            }
            else {
                PersonalizationProviderHelper.CheckOnlyOnePathWithUsers(paths, usernames);
                return ResetUserState(paths, usernames); 
            }
        } 
 
        private int ResetUserState(string[] paths, string[] usernames) {
            int count = 0; 
            bool hasPaths = !(paths == null || paths.Length == 0);
            bool hasUsernames = !(usernames == null || usernames.Length == 0);

            if (!hasPaths && !hasUsernames) { 
                count = ResetAllState(PersonalizationScope.User);
            } 
            else if (!hasUsernames) { 
                count = ResetUserState(ResetUserStateMode.PerPaths,
                                       PersonalizationAdministration.DefaultInactiveSinceDate, 
                                       paths, usernames);
            }
            else {
                count = ResetUserState(ResetUserStateMode.PerUsers, 
                                       PersonalizationAdministration.DefaultInactiveSinceDate,
                                       paths, usernames); 
            } 

            return count; 
        }

        private int ResetUserState(ResetUserStateMode mode,
                                   DateTime userInactiveSinceDate, 
                                   string[] paths,
                                   string[] usernames) { 
            SqlConnectionHolder connectionHolder = null; 
            SqlConnection connection = null;
            int count = 0; 

            // Extra try-catch block to prevent elevation of privilege attack via exception filter
            try {
                bool beginTranCalled = false; 
                try {
                    connectionHolder = GetConnectionHolder(); 
                    connection = connectionHolder.Connection; 
                    Debug.Assert(connection != null);
 
                    CheckSchemaVersion( connection );

                    SqlCommand command = new SqlCommand("dbo.aspnet_PersonalizationAdministration_ResetUserState", connection);
                    SetCommandTypeAndTimeout(command); 
                    SqlParameterCollection parameters = command.Parameters;
 
                    SqlParameter parameter = parameters.Add(new SqlParameter("Count", SqlDbType.Int)); 
                    parameter.Direction = ParameterDirection.Output;
 
                    parameters.AddWithValue("ApplicationName", ApplicationName);

                    string firstPath = (paths != null && paths.Length > 0) ? paths[0] : null;
 
                    if (mode == ResetUserStateMode.PerInactiveDate) {
                        if (userInactiveSinceDate != PersonalizationAdministration.DefaultInactiveSinceDate) { 
                            // Special 

                            parameter = parameters.Add("InactiveSinceDate", SqlDbType.DateTime); 
                            parameter.Value = userInactiveSinceDate.ToUniversalTime();
                        }

                        if (firstPath != null) { 
                            parameters.AddWithValue("Path", firstPath);
                        } 
 
                        command.ExecuteNonQuery();
                        SqlParameter countParam = command.Parameters[0]; 
                        if (countParam != null && countParam.Value != null && countParam.Value is Int32) {
                            count = (Int32) countParam.Value;
                        }
                    } 
                    else if (mode == ResetUserStateMode.PerPaths) {
                        Debug.Assert(paths != null); 
                        parameter = parameters.Add("Path", SqlDbType.NVarChar); 
                        foreach (string path in paths) {
                            if (!beginTranCalled && paths.Length > 1) { 
                                (new SqlCommand("BEGIN TRANSACTION", connection)).ExecuteNonQuery();
                                beginTranCalled = true;
                            }
 
                            parameter.Value = path;
                            command.ExecuteNonQuery(); 
                            SqlParameter countParam = command.Parameters[0]; 
                            if (countParam != null && countParam.Value != null && countParam.Value is Int32) {
                                count += (Int32) countParam.Value; 
                            }
                        }
                    }
                    else { 
                        Debug.Assert(mode == ResetUserStateMode.PerUsers);
                        if (firstPath != null) { 
                            parameters.AddWithValue("Path", firstPath); 
                        }
 
                        parameter = parameters.Add("UserName", SqlDbType.NVarChar);
                        foreach (string user in usernames) {
                            if (!beginTranCalled && usernames.Length > 1) {
                                (new SqlCommand("BEGIN TRANSACTION", connection)).ExecuteNonQuery(); 
                                beginTranCalled = true;
                            } 
 
                            parameter.Value = user;
                            command.ExecuteNonQuery(); 
                            SqlParameter countParam = command.Parameters[0];
                            if (countParam != null && countParam.Value != null && countParam.Value is Int32) {
                                count += (Int32) countParam.Value;
                            } 
                        }
                    } 
 
                    if (beginTranCalled) {
                        (new SqlCommand("COMMIT TRANSACTION", connection)).ExecuteNonQuery(); 
                        beginTranCalled = false;
                    }
                }
                catch { 
                    if (beginTranCalled) {
                        (new SqlCommand("ROLLBACK TRANSACTION", connection)).ExecuteNonQuery(); 
                        beginTranCalled = false; 
                    }
                    throw; 
                }
                finally {
                    if (connectionHolder != null) {
                        connectionHolder.Close(); 
                        connectionHolder = null;
                    } 
                } 
            }
            catch { 
                throw;
            }

            return count; 
        }
 
        ///  
        /// 
        private void SavePersonalizationState(SqlConnection connection, string path, string userName, byte[] state) { 
            Debug.Assert(connection != null);
            Debug.Assert(!String.IsNullOrEmpty(path));
            Debug.Assert((state != null) && (state.Length != 0));
 
            SqlCommand command;
 
            if (userName != null) { 
                command = new SqlCommand("dbo.aspnet_PersonalizationPerUser_SetPageSettings", connection);
            } 
            else {
                command = new SqlCommand("dbo.aspnet_PersonalizationAllUsers_SetPageSettings", connection);
            }
 
            SetCommandTypeAndTimeout(command);
            command.Parameters.Add(CreateParameter("@ApplicationName", SqlDbType.NVarChar, ApplicationName)); 
            command.Parameters.Add(CreateParameter("@Path", SqlDbType.NVarChar, path)); 
            command.Parameters.Add(CreateParameter("@PageSettings", SqlDbType.Image, state));
            command.Parameters.Add(CreateParameter("@CurrentTimeUtc", SqlDbType.DateTime, DateTime.UtcNow)); 
            if (userName != null) {
                command.Parameters.Add(CreateParameter("@UserName", SqlDbType.NVarChar, userName));
            }
 
            command.ExecuteNonQuery();
        } 
 
        /// 
        protected override void SavePersonalizationBlob(WebPartManager webPartManager, string path, string userName, byte[] dataBlob) { 
            SqlConnectionHolder connectionHolder = null;
            SqlConnection connection = null;

            // Extra try-catch block to prevent elevation of privilege attack via exception filter 
            try {
                try { 
                    connectionHolder = GetConnectionHolder(); 
                    connection = connectionHolder.Connection;
 
                    CheckSchemaVersion( connection );

                    SavePersonalizationState(connection, path, userName, dataBlob);
                } 
                finally {
                    if (connectionHolder != null) { 
                        connectionHolder.Close(); 
                        connectionHolder = null;
                    } 
                }
            }
            catch {
                throw; 
            }
        } 
 
        private void SetCommandTypeAndTimeout(SqlCommand command) {
            command.CommandType = CommandType.StoredProcedure; 
            if (_commandTimeout != -1) {
                command.CommandTimeout = _commandTimeout;
            }
        } 
    }
} 

// File provided for Reference Use Only by Microsoft Corporation (c) 2007.
// Copyright (c) Microsoft Corporation. All rights reserved.
//------------------------------------------------------------------------------ 
// 
//     Copyright (c) Microsoft Corporation.  All rights reserved.
// 
//----------------------------------------------------------------------------- 

namespace System.Web.UI.WebControls.WebParts { 
 
    using System;
    using System.Collections; 
    using System.Collections.Specialized;
    using System.Configuration.Provider;
    using System.ComponentModel;
    using System.Data; 
    using System.Data.SqlClient;
    using System.Globalization; 
    using System.Security.Permissions; 
    using System.Web;
    using System.Web.DataAccess; 
    using System.Web.Util;

    /// 
    /// The provider used to access the personalization store for WebPart pages from a SQL Server 
    /// database.
    ///  
    [AspNetHostingPermission(SecurityAction.LinkDemand, Level=AspNetHostingPermissionLevel.Minimal)] 
    [AspNetHostingPermission(SecurityAction.InheritanceDemand, Level=AspNetHostingPermissionLevel.Minimal)]
    public class SqlPersonalizationProvider : PersonalizationProvider { 

        private enum ResetUserStateMode {
            PerInactiveDate,
            PerPaths, 
            PerUsers
        } 
 
        private const int maxStringLength = 256;
 
        private string _applicationName;
        private int    _commandTimeout;
        private string _connectionString;
        private int    _SchemaVersionCheck; 

        ///  
        /// Initializes an instance of SqlPersonalizationProvider. 
        /// 
        public SqlPersonalizationProvider() { 
        }

        public override string ApplicationName {
            get { 
                if (String.IsNullOrEmpty(_applicationName)) {
                    _applicationName = SecUtility.GetDefaultAppName(); 
                } 
                return _applicationName;
            } 
            set {
                if (value != null && value.Length > maxStringLength) {
                    throw new ProviderException(SR.GetString(
                        SR.PersonalizationProvider_ApplicationNameExceedMaxLength, maxStringLength.ToString(CultureInfo.CurrentCulture))); 
                }
                _applicationName = value; 
            } 
        }
 
        /// 
        /// 
        private SqlParameter CreateParameter(string name, SqlDbType dbType, object value) {
            SqlParameter param = new SqlParameter(name, dbType); 

            param.Value = value; 
            return param; 
        }
 
        private PersonalizationStateInfoCollection FindSharedState(string path,
                                                                   int pageIndex,
                                                                   int pageSize,
                                                                   out int totalRecords) { 
            SqlConnectionHolder connectionHolder = null;
            SqlConnection connection = null; 
            SqlDataReader reader = null; 
            totalRecords = 0;
 
            // Extra try-catch block to prevent elevation of privilege attack via exception filter
            try {
                try {
                    connectionHolder = GetConnectionHolder(); 
                    connection = connectionHolder.Connection;
                    Debug.Assert(connection != null); 
 
                    CheckSchemaVersion( connection );
 
                    SqlCommand command = new SqlCommand("dbo.aspnet_PersonalizationAdministration_FindState", connection);
                    SetCommandTypeAndTimeout(command);
                    SqlParameterCollection parameters = command.Parameters;
 
                    SqlParameter parameter = parameters.Add(new SqlParameter("AllUsersScope", SqlDbType.Bit));
                    parameter.Value = true; 
 
                    parameters.AddWithValue("ApplicationName", ApplicationName);
                    parameters.AddWithValue("PageIndex", pageIndex); 
                    parameters.AddWithValue("PageSize", pageSize);

                    SqlParameter returnValue = new SqlParameter("@ReturnValue", SqlDbType.Int);
                    returnValue.Direction = ParameterDirection.ReturnValue; 
                    parameters.Add(returnValue);
 
                    parameter = parameters.Add("Path", SqlDbType.NVarChar); 
                    if (path != null) {
                        parameter.Value = path; 
                    }

                    parameter = parameters.Add("UserName", SqlDbType.NVarChar);
                    parameter = parameters.Add("InactiveSinceDate", SqlDbType.DateTime); 

                    reader = command.ExecuteReader(CommandBehavior.SequentialAccess); 
                    PersonalizationStateInfoCollection sharedStateInfoCollection = new PersonalizationStateInfoCollection(); 

                    if (reader != null) { 
                        if (reader.HasRows) {
                            while(reader.Read()) {
                                string returnedPath = reader.GetString(0);
 
                                // Data can be null if there is no data associated with the path
                                DateTime lastUpdatedDate = (reader.IsDBNull(1)) ? DateTime.MinValue : 
                                                                DateTime.SpecifyKind(reader.GetDateTime(1), DateTimeKind.Utc); 
                                int size = (reader.IsDBNull(2)) ? 0 : reader.GetInt32(2);
                                int userDataSize = (reader.IsDBNull(3)) ? 0 : reader.GetInt32(3); 
                                int userCount = (reader.IsDBNull(4)) ? 0 : reader.GetInt32(4);
                                sharedStateInfoCollection.Add(new SharedPersonalizationStateInfo(
                                    returnedPath, lastUpdatedDate, size, userDataSize, userCount));
                            } 
                        }
 
                        // The reader needs to be closed so return value can be accessed 
                        // See MSDN doc for SqlParameter.Direction for details.
                        reader.Close(); 
                        reader = null;
                    }

                    // Set the total count at the end after all operations pass 
                    if (returnValue.Value != null && returnValue.Value is int) {
                        totalRecords = (int)returnValue.Value; 
                    } 

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

        public override PersonalizationStateInfoCollection FindState(PersonalizationScope scope, 
                                                                     PersonalizationStateQuery query,
                                                                     int pageIndex, int pageSize,
                                                                     out int totalRecords) {
            PersonalizationProviderHelper.CheckPersonalizationScope(scope); 
            PersonalizationProviderHelper.CheckPageIndexAndSize(pageIndex, pageSize);
 
            if (scope == PersonalizationScope.Shared) { 
                string pathToMatch = null;
                if (query != null) { 
                    pathToMatch = StringUtil.CheckAndTrimString(query.PathToMatch, "query.PathToMatch", false, maxStringLength);
                }
                return FindSharedState(pathToMatch, pageIndex, pageSize, out totalRecords);
            } 
            else {
                string pathToMatch = null; 
                DateTime inactiveSinceDate = PersonalizationAdministration.DefaultInactiveSinceDate; 
                string usernameToMatch = null;
                if (query != null) { 
                    pathToMatch = StringUtil.CheckAndTrimString(query.PathToMatch, "query.PathToMatch", false, maxStringLength);
                    inactiveSinceDate = query.UserInactiveSinceDate;
                    usernameToMatch = StringUtil.CheckAndTrimString(query.UsernameToMatch, "query.UsernameToMatch", false, maxStringLength);
                } 

                return FindUserState(pathToMatch, inactiveSinceDate, usernameToMatch, 
                                     pageIndex, pageSize, out totalRecords); 
            }
        } 

        private PersonalizationStateInfoCollection FindUserState(string path,
                                                                 DateTime inactiveSinceDate,
                                                                 string username, 
                                                                 int pageIndex,
                                                                 int pageSize, 
                                                                 out int totalRecords) { 
            SqlConnectionHolder connectionHolder = null;
            SqlConnection connection = null; 
            SqlDataReader reader = null;
            totalRecords = 0;

            // Extra try-catch block to prevent elevation of privilege attack via exception filter 
            try {
                try { 
                    connectionHolder = GetConnectionHolder(); 
                    connection = connectionHolder.Connection;
                    Debug.Assert(connection != null); 

                    CheckSchemaVersion( connection );

                    SqlCommand command = new SqlCommand("dbo.aspnet_PersonalizationAdministration_FindState", connection); 
                    SetCommandTypeAndTimeout(command);
                    SqlParameterCollection parameters = command.Parameters; 
 
                    SqlParameter parameter = parameters.Add(new SqlParameter("AllUsersScope", SqlDbType.Bit));
                    parameter.Value = false; 

                    parameters.AddWithValue("ApplicationName", ApplicationName);
                    parameters.AddWithValue("PageIndex", pageIndex);
                    parameters.AddWithValue("PageSize", pageSize); 

                    SqlParameter returnValue = new SqlParameter("@ReturnValue", SqlDbType.Int); 
                    returnValue.Direction = ParameterDirection.ReturnValue; 
                    parameters.Add(returnValue);
 
                    parameter = parameters.Add("Path", SqlDbType.NVarChar);
                    if (path != null) {
                        parameter.Value = path;
                    } 

                    parameter = parameters.Add("UserName", SqlDbType.NVarChar); 
                    if (username != null) { 
                        parameter.Value = username;
                    } 

                    parameter = parameters.Add("InactiveSinceDate", SqlDbType.DateTime);
                    if (inactiveSinceDate != PersonalizationAdministration.DefaultInactiveSinceDate) {
                        parameter.Value = inactiveSinceDate.ToUniversalTime(); 
                    }
 
                    reader = command.ExecuteReader(CommandBehavior.SequentialAccess); 
                    PersonalizationStateInfoCollection stateInfoCollection = new PersonalizationStateInfoCollection();
 
                    if (reader != null) {
                        if (reader.HasRows) {
                            while(reader.Read()) {
                                string returnedPath = reader.GetString(0); 
                                DateTime lastUpdatedDate = DateTime.SpecifyKind(reader.GetDateTime(1), DateTimeKind.Utc);
                                int size = reader.GetInt32(2); 
                                string returnedUsername = reader.GetString(3); 
                                DateTime lastActivityDate = DateTime.SpecifyKind(reader.GetDateTime(4), DateTimeKind.Utc);
                                stateInfoCollection.Add(new UserPersonalizationStateInfo( 
                                                                returnedPath, lastUpdatedDate,
                                                                size, returnedUsername, lastActivityDate));
                            }
                        } 

                        // The reader needs to be closed so return value can be accessed 
                        // See MSDN doc for SqlParameter.Direction for details. 
                        reader.Close();
                        reader = null; 
                    }

                    // Set the total count at the end after all operations pass
                    if (returnValue.Value != null && returnValue.Value is int) { 
                        totalRecords = (int)returnValue.Value;
                    } 
 
                    return stateInfoCollection;
                } 
                finally {
                    if (reader != null) {
                        reader.Close();
                    } 

                    if (connectionHolder != null) { 
                        connectionHolder.Close(); 
                        connectionHolder = null;
                    } 
                }
            }
            catch {
                throw; 
            }
        } 
 
        /// 
        ///  
        private SqlConnectionHolder GetConnectionHolder() {
            SqlConnection connection = null;
            SqlConnectionHolder connectionHolder = SqlConnectionHelper.GetConnection(_connectionString, true);
 
            if (connectionHolder != null) {
                connection = connectionHolder.Connection; 
            } 
            if (connection == null) {
                throw new ProviderException(SR.GetString(SR.PersonalizationProvider_CantAccess, Name)); 
            }

            return connectionHolder;
        } 

        private int GetCountOfSharedState(string path) { 
            SqlConnectionHolder connectionHolder = null; 
            SqlConnection connection = null;
            int count = 0; 

            // Extra try-catch block to prevent elevation of privilege attack via exception filter
            try {
                try { 
                    connectionHolder = GetConnectionHolder();
                    connection = connectionHolder.Connection; 
                    Debug.Assert(connection != null); 

                    CheckSchemaVersion( connection ); 

                    SqlCommand command = new SqlCommand("dbo.aspnet_PersonalizationAdministration_GetCountOfState", connection);
                    SetCommandTypeAndTimeout(command);
                    SqlParameterCollection parameters = command.Parameters; 

                    SqlParameter parameter = parameters.Add(new SqlParameter("Count", SqlDbType.Int)); 
                    parameter.Direction = ParameterDirection.Output; 

                    parameter = parameters.Add(new SqlParameter("AllUsersScope", SqlDbType.Bit)); 
                    parameter.Value = true;

                    parameters.AddWithValue("ApplicationName", ApplicationName);
 
                    parameter = parameters.Add("Path", SqlDbType.NVarChar);
                    if (path != null) { 
                        parameter.Value = path; 
                    }
 
                    parameter = parameters.Add("UserName", SqlDbType.NVarChar);
                    parameter = parameters.Add("InactiveSinceDate", SqlDbType.DateTime);

                    command.ExecuteNonQuery(); 
                    parameter = command.Parameters[0];
                    if (parameter != null && parameter.Value != null && parameter.Value is Int32) { 
                        count = (Int32) parameter.Value; 
                    }
                } 
                finally {
                    if (connectionHolder != null) {
                        connectionHolder.Close();
                        connectionHolder = null; 
                    }
                } 
            } 
            catch {
                throw; 
            }

            return count;
        } 

        public override int GetCountOfState(PersonalizationScope scope, PersonalizationStateQuery query) { 
            PersonalizationProviderHelper.CheckPersonalizationScope(scope); 
            if (scope == PersonalizationScope.Shared) {
                string pathToMatch = null; 
                if (query != null) {
                    pathToMatch = StringUtil.CheckAndTrimString(query.PathToMatch, "query.PathToMatch", false, maxStringLength);
                }
                return GetCountOfSharedState(pathToMatch); 
            }
            else { 
                string pathToMatch = null; 
                DateTime userInactiveSinceDate = PersonalizationAdministration.DefaultInactiveSinceDate;
                string usernameToMatch = null; 
                if (query != null) {
                    pathToMatch = StringUtil.CheckAndTrimString(query.PathToMatch, "query.PathToMatch", false, maxStringLength);
                    userInactiveSinceDate = query.UserInactiveSinceDate;
                    usernameToMatch = StringUtil.CheckAndTrimString(query.UsernameToMatch, "query.UsernameToMatch", false, maxStringLength); 
                }
                return GetCountOfUserState(pathToMatch, userInactiveSinceDate, usernameToMatch); 
            } 
        }
 
        private int GetCountOfUserState(string path, DateTime inactiveSinceDate, string username) {
            SqlConnectionHolder connectionHolder = null;
            SqlConnection connection = null;
            int count = 0; 

            // Extra try-catch block to prevent elevation of privilege attack via exception filter 
            try { 
                try {
                    connectionHolder = GetConnectionHolder(); 
                    connection = connectionHolder.Connection;
                    Debug.Assert(connection != null);

                    CheckSchemaVersion( connection ); 

                    SqlCommand command = new SqlCommand("dbo.aspnet_PersonalizationAdministration_GetCountOfState", connection); 
                    SetCommandTypeAndTimeout(command); 
                    SqlParameterCollection parameters = command.Parameters;
 
                    SqlParameter parameter = parameters.Add(new SqlParameter("Count", SqlDbType.Int));
                    parameter.Direction = ParameterDirection.Output;

                    parameter = parameters.Add(new SqlParameter("AllUsersScope", SqlDbType.Bit)); 
                    parameter.Value = false;
 
                    parameters.AddWithValue("ApplicationName", ApplicationName); 

                    parameter = parameters.Add("Path", SqlDbType.NVarChar); 
                    if (path != null) {
                        parameter.Value = path;
                    }
 
                    parameter = parameters.Add("UserName", SqlDbType.NVarChar);
                    if (username != null) { 
                        parameter.Value = username; 
                    }
 
                    parameter = parameters.Add("InactiveSinceDate", SqlDbType.DateTime);
                    if (inactiveSinceDate != PersonalizationAdministration.DefaultInactiveSinceDate) {
                        parameter.Value = inactiveSinceDate.ToUniversalTime();
                    } 

                    command.ExecuteNonQuery(); 
                    parameter = command.Parameters[0]; 
                    if (parameter != null && parameter.Value != null && parameter.Value is Int32) {
                        count = (Int32) parameter.Value; 
                    }
                }
                finally {
                    if (connectionHolder != null) { 
                        connectionHolder.Close();
                        connectionHolder = null; 
                    } 
                }
            } 
            catch {
                throw;
            }
 
            return count;
        } 
 
        public override void Initialize(string name, NameValueCollection configSettings) {
            HttpRuntime.CheckAspNetHostingPermission(AspNetHostingPermissionLevel.Low, SR.Feature_not_supported_at_this_level); 

            // configSettings cannot be null because there are required settings needed below
            if (configSettings == null) {
                throw new ArgumentNullException("configSettings"); 
            }
 
            if (String.IsNullOrEmpty(name)) { 
                name = "SqlPersonalizationProvider";
            } 

            // description will be set from the base class' Initialize method
            if (string.IsNullOrEmpty(configSettings["description"])) {
                configSettings.Remove("description"); 
                configSettings.Add("description", SR.GetString(SR.SqlPersonalizationProvider_Description));
            } 
            base.Initialize(name, configSettings); 

            _SchemaVersionCheck = 0; 

            // If not available, the default value is set in the get accessor of ApplicationName
            _applicationName = configSettings["applicationName"];
            if (_applicationName != null) { 
                configSettings.Remove("applicationName");
 
                if (_applicationName.Length > maxStringLength) { 
                    throw new ProviderException(SR.GetString(
                        SR.PersonalizationProvider_ApplicationNameExceedMaxLength, maxStringLength.ToString(CultureInfo.CurrentCulture))); 
                }
            }

            string connectionStringName = configSettings["connectionStringName"]; 
            if (String.IsNullOrEmpty(connectionStringName)) {
                throw new ProviderException(SR.GetString(SR.PersonalizationProvider_NoConnection)); 
            } 
            configSettings.Remove("connectionStringName");
 
            string connectionString = SqlConnectionHelper.GetConnectionString(connectionStringName, true, true);
            if (String.IsNullOrEmpty(connectionString)) {
                throw new ProviderException(SR.GetString(SR.PersonalizationProvider_BadConnection, connectionStringName));
            } 
            _connectionString = connectionString;
 
            _commandTimeout = SecUtility.GetIntValue(configSettings, "commandTimeout", -1, true, 0); 
            configSettings.Remove("commandTimeout");
 
            if (configSettings.Count > 0) {
                string invalidAttributeName = configSettings.GetKey(0);
                throw new ProviderException(SR.GetString(SR.PersonalizationProvider_UnknownProp, invalidAttributeName, name));
            } 
        }
 
        private void CheckSchemaVersion( SqlConnection connection ) 
        {
            string[] features = { "Personalization" }; 
            string   version  = "1";

            SecUtility.CheckSchemaVersion( this,
                                           connection, 
                                           features,
                                           version, 
                                           ref _SchemaVersionCheck ); 
        }
 
        /// 
        /// 
        private byte[] LoadPersonalizationBlob(SqlConnection connection, string path, string userName) {
            Debug.Assert(connection != null); 
            Debug.Assert(!String.IsNullOrEmpty(path));
 
            SqlCommand command; 

            if (userName != null) { 
                command = new SqlCommand("dbo.aspnet_PersonalizationPerUser_GetPageSettings", connection);
            }
            else {
                command = new SqlCommand("dbo.aspnet_PersonalizationAllUsers_GetPageSettings", connection); 
            }
 
            SetCommandTypeAndTimeout(command); 
            command.Parameters.Add(CreateParameter("@ApplicationName", SqlDbType.NVarChar, this.ApplicationName));
            command.Parameters.Add(CreateParameter("@Path", SqlDbType.NVarChar, path)); 
            if (userName != null) {
                command.Parameters.Add(CreateParameter("@UserName", SqlDbType.NVarChar, userName));
                command.Parameters.Add(CreateParameter("@CurrentTimeUtc", SqlDbType.DateTime, DateTime.UtcNow));
            } 

            SqlDataReader reader = null; 
            try { 
                reader = command.ExecuteReader(CommandBehavior.SingleRow);
                if (reader.Read()) { 
                    int length = (int)reader.GetBytes(0, 0, null, 0, 0);
                    byte[] state = new byte[length];

                    reader.GetBytes(0, 0, state, 0, length); 
                    return state;
                } 
            } 
            finally {
                if (reader != null) { 
                    reader.Close();
                }
            }
 
            return null;
        } 
 
        /// 
        protected override void LoadPersonalizationBlobs(WebPartManager webPartManager, string path, string userName, ref byte[] sharedDataBlob, ref byte[] userDataBlob) { 
            sharedDataBlob = null;
            userDataBlob = null;

            SqlConnectionHolder connectionHolder = null; 
            SqlConnection connection = null;
 
            // Extra try-catch block to prevent elevation of privilege attack via exception filter 
            try {
                try { 
                    connectionHolder = GetConnectionHolder();
                    connection = connectionHolder.Connection;

                    CheckSchemaVersion( connection ); 

                    sharedDataBlob = LoadPersonalizationBlob(connection, path, null); 
                    if (!String.IsNullOrEmpty(userName)) { 
                        userDataBlob = LoadPersonalizationBlob(connection, path, userName);
                    } 
                }
                finally {
                    if (connectionHolder != null) {
                        connectionHolder.Close(); 
                        connectionHolder = null;
                    } 
                } 
            }
            catch { 
                throw;
            }
        }
 
        /// 
        ///  
        private void ResetPersonalizationState(SqlConnection connection, string path, string userName) { 
            Debug.Assert(connection != null);
            Debug.Assert(!String.IsNullOrEmpty(path)); 

            SqlCommand command;

            if (userName != null) { 
                command = new SqlCommand("dbo.aspnet_PersonalizationPerUser_ResetPageSettings", connection);
            } 
            else { 
                command = new SqlCommand("dbo.aspnet_PersonalizationAllUsers_ResetPageSettings", connection);
            } 

            SetCommandTypeAndTimeout(command);
            command.Parameters.Add(CreateParameter("@ApplicationName", SqlDbType.NVarChar, ApplicationName));
            command.Parameters.Add(CreateParameter("@Path", SqlDbType.NVarChar, path)); 
            if (userName != null) {
                command.Parameters.Add(CreateParameter("@UserName", SqlDbType.NVarChar, userName)); 
                command.Parameters.Add(CreateParameter("@CurrentTimeUtc", SqlDbType.DateTime, DateTime.UtcNow)); 
            }
 
            command.ExecuteNonQuery();
        }

        ///  
        protected override void ResetPersonalizationBlob(WebPartManager webPartManager, string path, string userName) {
            SqlConnectionHolder connectionHolder = null; 
            SqlConnection connection = null; 

            // Extra try-catch block to prevent elevation of privilege attack via exception filter 
            try {
                try {
                    connectionHolder = GetConnectionHolder();
                    connection = connectionHolder.Connection; 

                    CheckSchemaVersion( connection ); 
 
                    ResetPersonalizationState(connection, path, userName);
                } 
                finally {
                    if (connectionHolder != null) {
                        connectionHolder.Close();
                        connectionHolder = null; 
                    }
                } 
            } 
            catch {
                throw; 
            }
        }

        private int ResetAllState(PersonalizationScope scope) { 
            SqlConnectionHolder connectionHolder = null;
            SqlConnection connection = null; 
            int count = 0; 

            // Extra try-catch block to prevent elevation of privilege attack via exception filter 
            try {
                try {
                    connectionHolder = GetConnectionHolder();
                    connection = connectionHolder.Connection; 
                    Debug.Assert(connection != null);
 
                    CheckSchemaVersion( connection ); 

                    SqlCommand command = new SqlCommand("dbo.aspnet_PersonalizationAdministration_DeleteAllState", connection); 
                    SetCommandTypeAndTimeout(command);
                    SqlParameterCollection parameters = command.Parameters;

                    SqlParameter parameter = parameters.Add(new SqlParameter("AllUsersScope", SqlDbType.Bit)); 
                    parameter.Value = (scope == PersonalizationScope.Shared);
 
                    parameters.AddWithValue("ApplicationName", ApplicationName); 

                    parameter = parameters.Add(new SqlParameter("Count", SqlDbType.Int)); 
                    parameter.Direction = ParameterDirection.Output;

                    command.ExecuteNonQuery();
                    parameter = command.Parameters[2]; 
                    if (parameter != null && parameter.Value != null && parameter.Value is Int32) {
                        count = (Int32) parameter.Value; 
                    } 
                }
                finally { 
                    if (connectionHolder != null) {
                        connectionHolder.Close();
                        connectionHolder = null;
                    } 
                }
            } 
            catch { 
                throw;
            } 

            return count;
        }
 
        private int ResetSharedState(string[] paths) {
            int resultCount = 0; 
 
            if (paths == null) {
                resultCount = ResetAllState(PersonalizationScope.Shared); 
            }
            else {
                SqlConnectionHolder connectionHolder = null;
                SqlConnection connection = null; 

                // Extra try-catch block to prevent elevation of privilege attack via exception filter 
                try { 
                    bool beginTranCalled = false;
                    try { 
                        connectionHolder = GetConnectionHolder();
                        connection = connectionHolder.Connection;
                        Debug.Assert(connection != null);
 
                        CheckSchemaVersion( connection );
 
                        SqlCommand command = new SqlCommand("dbo.aspnet_PersonalizationAdministration_ResetSharedState", connection); 
                        SetCommandTypeAndTimeout(command);
                        SqlParameterCollection parameters = command.Parameters; 

                        SqlParameter parameter = parameters.Add(new SqlParameter("Count", SqlDbType.Int));
                        parameter.Direction = ParameterDirection.Output;
 
                        parameters.AddWithValue("ApplicationName", ApplicationName);
 
                        parameter = parameters.Add("Path", SqlDbType.NVarChar); 
                        foreach (string path in paths) {
                            if (!beginTranCalled && paths.Length > 1) { 
                                (new SqlCommand("BEGIN TRANSACTION", connection)).ExecuteNonQuery();
                                beginTranCalled = true;
                            }
 
                            parameter.Value = path;
                            command.ExecuteNonQuery(); 
                            SqlParameter countParam = command.Parameters[0]; 
                            if (countParam != null && countParam.Value != null && countParam.Value is Int32) {
                                resultCount += (Int32) countParam.Value; 
                            }
                        }

                        if (beginTranCalled) { 
                            (new SqlCommand("COMMIT TRANSACTION", connection)).ExecuteNonQuery();
                            beginTranCalled = false; 
                        } 
                    }
                    catch { 
                        if (beginTranCalled) {
                            (new SqlCommand("ROLLBACK TRANSACTION", connection)).ExecuteNonQuery();
                            beginTranCalled = false;
                        } 
                        throw;
                    } 
                    finally { 
                       if (connectionHolder != null) {
                            connectionHolder.Close(); 
                            connectionHolder = null;
                        }
                    }
                } 
                catch {
                    throw; 
                } 
            }
 
            return resultCount;
        }

        public override int ResetUserState(string path, DateTime userInactiveSinceDate) { 
            path = StringUtil.CheckAndTrimString(path, "path", false, maxStringLength);
            string [] paths = (path == null) ? null : new string [] {path}; 
            return ResetUserState(ResetUserStateMode.PerInactiveDate, 
                                  userInactiveSinceDate, paths, null);
        } 

        public override int ResetState(PersonalizationScope scope, string[] paths, string[] usernames) {
            PersonalizationProviderHelper.CheckPersonalizationScope(scope);
            paths = PersonalizationProviderHelper.CheckAndTrimNonEmptyStringEntries(paths, "paths", false, false, maxStringLength); 
            usernames = PersonalizationProviderHelper.CheckAndTrimNonEmptyStringEntries(usernames, "usernames", false, true, maxStringLength);
 
            if (scope == PersonalizationScope.Shared) { 
                PersonalizationProviderHelper.CheckUsernamesInSharedScope(usernames);
                return ResetSharedState(paths); 
            }
            else {
                PersonalizationProviderHelper.CheckOnlyOnePathWithUsers(paths, usernames);
                return ResetUserState(paths, usernames); 
            }
        } 
 
        private int ResetUserState(string[] paths, string[] usernames) {
            int count = 0; 
            bool hasPaths = !(paths == null || paths.Length == 0);
            bool hasUsernames = !(usernames == null || usernames.Length == 0);

            if (!hasPaths && !hasUsernames) { 
                count = ResetAllState(PersonalizationScope.User);
            } 
            else if (!hasUsernames) { 
                count = ResetUserState(ResetUserStateMode.PerPaths,
                                       PersonalizationAdministration.DefaultInactiveSinceDate, 
                                       paths, usernames);
            }
            else {
                count = ResetUserState(ResetUserStateMode.PerUsers, 
                                       PersonalizationAdministration.DefaultInactiveSinceDate,
                                       paths, usernames); 
            } 

            return count; 
        }

        private int ResetUserState(ResetUserStateMode mode,
                                   DateTime userInactiveSinceDate, 
                                   string[] paths,
                                   string[] usernames) { 
            SqlConnectionHolder connectionHolder = null; 
            SqlConnection connection = null;
            int count = 0; 

            // Extra try-catch block to prevent elevation of privilege attack via exception filter
            try {
                bool beginTranCalled = false; 
                try {
                    connectionHolder = GetConnectionHolder(); 
                    connection = connectionHolder.Connection; 
                    Debug.Assert(connection != null);
 
                    CheckSchemaVersion( connection );

                    SqlCommand command = new SqlCommand("dbo.aspnet_PersonalizationAdministration_ResetUserState", connection);
                    SetCommandTypeAndTimeout(command); 
                    SqlParameterCollection parameters = command.Parameters;
 
                    SqlParameter parameter = parameters.Add(new SqlParameter("Count", SqlDbType.Int)); 
                    parameter.Direction = ParameterDirection.Output;
 
                    parameters.AddWithValue("ApplicationName", ApplicationName);

                    string firstPath = (paths != null && paths.Length > 0) ? paths[0] : null;
 
                    if (mode == ResetUserStateMode.PerInactiveDate) {
                        if (userInactiveSinceDate != PersonalizationAdministration.DefaultInactiveSinceDate) { 
                            // Special 

                            parameter = parameters.Add("InactiveSinceDate", SqlDbType.DateTime); 
                            parameter.Value = userInactiveSinceDate.ToUniversalTime();
                        }

                        if (firstPath != null) { 
                            parameters.AddWithValue("Path", firstPath);
                        } 
 
                        command.ExecuteNonQuery();
                        SqlParameter countParam = command.Parameters[0]; 
                        if (countParam != null && countParam.Value != null && countParam.Value is Int32) {
                            count = (Int32) countParam.Value;
                        }
                    } 
                    else if (mode == ResetUserStateMode.PerPaths) {
                        Debug.Assert(paths != null); 
                        parameter = parameters.Add("Path", SqlDbType.NVarChar); 
                        foreach (string path in paths) {
                            if (!beginTranCalled && paths.Length > 1) { 
                                (new SqlCommand("BEGIN TRANSACTION", connection)).ExecuteNonQuery();
                                beginTranCalled = true;
                            }
 
                            parameter.Value = path;
                            command.ExecuteNonQuery(); 
                            SqlParameter countParam = command.Parameters[0]; 
                            if (countParam != null && countParam.Value != null && countParam.Value is Int32) {
                                count += (Int32) countParam.Value; 
                            }
                        }
                    }
                    else { 
                        Debug.Assert(mode == ResetUserStateMode.PerUsers);
                        if (firstPath != null) { 
                            parameters.AddWithValue("Path", firstPath); 
                        }
 
                        parameter = parameters.Add("UserName", SqlDbType.NVarChar);
                        foreach (string user in usernames) {
                            if (!beginTranCalled && usernames.Length > 1) {
                                (new SqlCommand("BEGIN TRANSACTION", connection)).ExecuteNonQuery(); 
                                beginTranCalled = true;
                            } 
 
                            parameter.Value = user;
                            command.ExecuteNonQuery(); 
                            SqlParameter countParam = command.Parameters[0];
                            if (countParam != null && countParam.Value != null && countParam.Value is Int32) {
                                count += (Int32) countParam.Value;
                            } 
                        }
                    } 
 
                    if (beginTranCalled) {
                        (new SqlCommand("COMMIT TRANSACTION", connection)).ExecuteNonQuery(); 
                        beginTranCalled = false;
                    }
                }
                catch { 
                    if (beginTranCalled) {
                        (new SqlCommand("ROLLBACK TRANSACTION", connection)).ExecuteNonQuery(); 
                        beginTranCalled = false; 
                    }
                    throw; 
                }
                finally {
                    if (connectionHolder != null) {
                        connectionHolder.Close(); 
                        connectionHolder = null;
                    } 
                } 
            }
            catch { 
                throw;
            }

            return count; 
        }
 
        ///  
        /// 
        private void SavePersonalizationState(SqlConnection connection, string path, string userName, byte[] state) { 
            Debug.Assert(connection != null);
            Debug.Assert(!String.IsNullOrEmpty(path));
            Debug.Assert((state != null) && (state.Length != 0));
 
            SqlCommand command;
 
            if (userName != null) { 
                command = new SqlCommand("dbo.aspnet_PersonalizationPerUser_SetPageSettings", connection);
            } 
            else {
                command = new SqlCommand("dbo.aspnet_PersonalizationAllUsers_SetPageSettings", connection);
            }
 
            SetCommandTypeAndTimeout(command);
            command.Parameters.Add(CreateParameter("@ApplicationName", SqlDbType.NVarChar, ApplicationName)); 
            command.Parameters.Add(CreateParameter("@Path", SqlDbType.NVarChar, path)); 
            command.Parameters.Add(CreateParameter("@PageSettings", SqlDbType.Image, state));
            command.Parameters.Add(CreateParameter("@CurrentTimeUtc", SqlDbType.DateTime, DateTime.UtcNow)); 
            if (userName != null) {
                command.Parameters.Add(CreateParameter("@UserName", SqlDbType.NVarChar, userName));
            }
 
            command.ExecuteNonQuery();
        } 
 
        /// 
        protected override void SavePersonalizationBlob(WebPartManager webPartManager, string path, string userName, byte[] dataBlob) { 
            SqlConnectionHolder connectionHolder = null;
            SqlConnection connection = null;

            // Extra try-catch block to prevent elevation of privilege attack via exception filter 
            try {
                try { 
                    connectionHolder = GetConnectionHolder(); 
                    connection = connectionHolder.Connection;
 
                    CheckSchemaVersion( connection );

                    SavePersonalizationState(connection, path, userName, dataBlob);
                } 
                finally {
                    if (connectionHolder != null) { 
                        connectionHolder.Close(); 
                        connectionHolder = null;
                    } 
                }
            }
            catch {
                throw; 
            }
        } 
 
        private void SetCommandTypeAndTimeout(SqlCommand command) {
            command.CommandType = CommandType.StoredProcedure; 
            if (_commandTimeout != -1) {
                command.CommandTimeout = _commandTimeout;
            }
        } 
    }
} 

// File provided for Reference Use Only by Microsoft Corporation (c) 2007.
// Copyright (c) Microsoft Corporation. All rights reserved.
                        

Link Menu

Network programming in C#, Network Programming in VB.NET, Network Programming in .NET
This book is available now!
Buy at Amazon US or
Buy at Amazon UK