public override void Initialize(string name, NameValueCollection config)
        {
            if (config == null)
            {
                throw new ArgumentNullException("config");
            }
            if (name == null || name.Length < 1)
            {
                name = "SqlCeProfileProvider";
            }
            if (string.IsNullOrEmpty(config["description"]))
            {
                config.Remove("description");
                config.Add("description", "SqlCeProfileProvider");
            }

            base.Initialize(name, config);

            //
            // Initialize SqlCeConnection.
            //

            ConnectionStringSettings ConnectionStringSettings =
                ConfigurationManager.ConnectionStrings[config["connectionStringName"]];

            if (ConnectionStringSettings == null || string.IsNullOrWhiteSpace(ConnectionStringSettings.ConnectionString))
            {
                throw new ProviderException("Connection string cannot be blank.");
            }

            connectionString = ConnectionStringSettings.ConnectionString;

            ApplicationName = GetConfigValue(config["applicationName"], System.Web.Hosting.HostingEnvironment.ApplicationVirtualPath);

            SqlCeMembershipUtils.CreateDatabaseIfRequired(connectionString, ApplicationName);

            pApplicationId = SqlCeMembershipUtils.GetApplicationId(connectionString, ApplicationName);

            config.Remove("connectionStringName");
            config.Remove("applicationName");
            if (config.Count > 0)
            {
                string attribUnrecognized = config.GetKey(0);
                if (!String.IsNullOrEmpty(attribUnrecognized))
                {
                    throw new ProviderException(SR.GetString(SR.Provider_unrecognized_attribute, attribUnrecognized));
                }
            }
        }
        //
        // System.Configuration.Provider.ProviderBase.Initialize Method
        //

        public override void Initialize(string name, NameValueCollection config)
        {
            //
            // Initialize values from web.config.
            //

            if (config == null)
            {
                throw new ArgumentNullException("config");
            }

            if (name == null || name.Length == 0)
            {
                name = "SqlCeRoleProvider";
            }

            if (String.IsNullOrEmpty(config["description"]))
            {
                config.Remove("description");
                config.Add("description", "SqlCe Role provider");
            }

            // Initialize the abstract base class.
            base.Initialize(name, config);


            if (string.IsNullOrWhiteSpace(config["applicationName"]))
            {
                pApplicationName = System.Web.Hosting.HostingEnvironment.ApplicationVirtualPath;
            }
            else
            {
                pApplicationName = config["applicationName"];
            }


            if (config["writeExceptionsToEventLog"] != null)
            {
                if (config["writeExceptionsToEventLog"].ToUpperInvariant() == "TRUE")
                {
                    pWriteExceptionsToEventLog = true;
                }
            }


            //
            // Initialize SqlCeConnection.
            //

            pConnectionStringSettings = ConfigurationManager.
                                        ConnectionStrings[config["connectionStringName"]];

            if (pConnectionStringSettings == null || string.IsNullOrWhiteSpace(pConnectionStringSettings.ConnectionString))
            {
                throw new ProviderException("Connection string cannot be blank.");
            }

            connectionString = pConnectionStringSettings.ConnectionString;

            SqlCeMembershipUtils.CreateDatabaseIfRequired(connectionString, ApplicationName);
            pApplicationId = SqlCeMembershipUtils.GetApplicationId(connectionString, pApplicationName);
        }
        private void GetPropertyValuesFromDatabase(string userName, SettingsPropertyValueCollection svc)
        {
            HttpContext context = HttpContext.Current;

            string[] names  = null;
            string   values = null;

            byte[] buf   = null;
            string sName = null;

            if (context != null)
            {
                sName = (context.Request.IsAuthenticated ? context.User.Identity.Name : context.Request.AnonymousID);
            }

            using (SqlCeConnection conn = new SqlCeConnection(connectionString))
            {
                Guid userId;

                using (SqlCeCommand cmd = new SqlCeCommand("SELECT ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName", conn))
                {
                    conn.Open();

                    cmd.Parameters.Clear();
                    cmd.Parameters.Add(CreateInputParam("@ApplicationId", SqlDbType.UniqueIdentifier, SqlCeMembershipUtils.GetApplicationId(conn.ConnectionString, ApplicationName)));
                    cmd.Parameters.Add(CreateInputParam("@UserName", SqlDbType.NVarChar, userName));
                    cmd.CommandText = "SELECT UserId FROM aspnet_Users WHERE ApplicationId = @ApplicationId AND LoweredUserName = LOWER(@UserName)";

                    object o = cmd.ExecuteScalar();
                    if (o != null && o is Guid)
                    {
                        userId = (Guid)o;
                    }
                    else
                    {
                        return;
                    }

                    cmd.Parameters.Clear();
                    cmd.Parameters.Add(CreateInputParam("@UserId", SqlDbType.UniqueIdentifier, userId));
                    cmd.CommandText = "SELECT PropertyNames, PropertyValuesString, PropertyValuesBinary FROM aspnet_Profile WHERE UserId = @UserId";

                    using (SqlCeDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
                    {
                        cmd.Parameters.Clear();
                        cmd.Parameters.Add(CreateInputParam("@CurrentTimeUtc", SqlDbType.DateTime, DateTime.UtcNow));
                        cmd.Parameters.Add(CreateInputParam("@UserId", SqlDbType.UniqueIdentifier, userId));
                        cmd.CommandText = "UPDATE aspnet_Users SET LastActivityDate = @CurrentTimeUtc WHERE UserId = @UserId";

                        cmd.ExecuteNonQuery();

                        if (reader.Read())
                        {
                            names  = reader.GetString(0).Split(':');
                            values = reader.GetString(1);

                            int size = (int)reader.GetBytes(2, 0, null, 0, 0);

                            buf = new byte[size];
                            reader.GetBytes(2, 0, buf, 0, size);
                        }
                        ParseDataFromDB(names, values, buf, svc);
                    }
                }
            }
        }
        private ProfileInfoCollection GetProfilesForQuery(SqlCeParameter[] args, ProfileAuthenticationOption authenticationOption, int pageIndex, int pageSize, out int totalRecords)
        {
            if (pageIndex < 0)
            {
                throw new ArgumentException("PageIndex must be greater than -1", "pageIndex");
            }
            if (pageSize < 1)
            {
                throw new ArgumentException("PageSize must be greater than 0", "pageSize");
            }

            long lowerBound = pageIndex * pageSize;
            long upperBound = (long)pageSize - 1 + lowerBound;

            if (upperBound > Int32.MaxValue)
            {
                throw new ArgumentException("The combination of pageIndex and pageSize cannot exceed the maximum value of System.Int32.", "pageIndex and pageSize");
            }

            SqlCeDataReader reader = null;

            totalRecords = 0;

            try
            {
                using (SqlCeConnection conn = new SqlCeConnection(connectionString))
                {
                    conn.Open();

                    using (SqlCeCommand cmd = new SqlCeCommand(
                               @"
							SELECT
								aspnet_Users.UserName,
								aspnet_Users.IsAnonymous,
								aspnet_Users.LastActivityDate,
								aspnet_Profile.LastUpdatedDate,
								DATALENGTH(aspnet_Profile.PropertyNames) + DATALENGTH(aspnet_Profile.PropertyValuesString) + DATALENGTH(aspnet_Profile.PropertyValuesBinary)
							FROM
								aspnet_Users,
								aspnet_Profile
							WHERE
								aspnet_Users.UserId = aspnet_Profile.UserId
								AND ApplicationId = @ApplicationId
								AND
									(
									@ProfileAuthOptions = 2
									OR (@ProfileAuthOptions = 0 AND IsAnonymous = 1)
									OR (@ProfileAuthOptions = 1 AND IsAnonymous = 0)
									)"
                               , conn))
                    {
                        cmd.Parameters.Add(CreateInputParam("@ApplicationId", SqlDbType.UniqueIdentifier, SqlCeMembershipUtils.GetApplicationId(conn.ConnectionString, ApplicationName)));
                        cmd.Parameters.Add(CreateInputParam("@ProfileAuthOptions", SqlDbType.Int, (int)authenticationOption));
                        cmd.Parameters.Add(CreateInputParam("@PageLowerBound", SqlDbType.Int, lowerBound));
                        cmd.Parameters.Add(CreateInputParam("@PageSize", SqlDbType.Int, pageSize));

                        foreach (SqlCeParameter arg in args)
                        {
                            cmd.Parameters.Add(arg);
                            switch (arg.ParameterName)
                            {
                            case "@InactiveSinceDate":
                                cmd.CommandText += " AND (@InactiveSinceDate IS NULL OR aspnet_Users.LastActivityDate <= @InactiveSinceDate)";
                                break;

                            case "@UserNameToMatch":
                                cmd.CommandText += " AND (@UserNameToMatch IS NULL OR LoweredUserName LIKE LOWER(@UserNameToMatch))";
                                break;
                            }
                        }

                        // append paging
                        cmd.CommandText += " ORDER BY aspnet_Users.Username	OFFSET @PageLowerBound ROWS FETCH NEXT @PageSize ROWS ONLY";

                        reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess);

                        ProfileInfoCollection profiles = new ProfileInfoCollection();

                        while (reader.Read())
                        {
                            string   username;
                            DateTime dtLastActivity, dtLastUpdated;
                            bool     isAnon;

                            username       = reader.GetString(0);
                            isAnon         = reader.GetBoolean(1);
                            dtLastActivity = DateTime.SpecifyKind(reader.GetDateTime(2), DateTimeKind.Utc);
                            dtLastUpdated  = DateTime.SpecifyKind(reader.GetDateTime(3), DateTimeKind.Utc);
                            int size = reader.GetInt32(4);
                            profiles.Add(new ProfileInfo(username, isAnon, dtLastActivity, dtLastUpdated, size));
                        }

                        totalRecords = profiles.Count;
                        if (reader.NextResult())
                        {
                            if (reader.Read())
                            {
                                totalRecords = reader.GetInt32(0);
                            }
                        }

                        return(profiles);
                    }
                }
            }
            catch
            {
                throw;
            }
        }
        public override void SetPropertyValues(SettingsContext sc, SettingsPropertyValueCollection properties)
        {
            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, true, properties, userIsAuthenticated);
            if (names.Length == 0)
            {
                return;
            }

            Guid userId = Guid.Empty;

            using (SqlCeConnection conn = new SqlCeConnection(connectionString))
            {
                using (SqlCeCommand cmd = new SqlCeCommand("SELECT Count(UserId) FROM aspnet_Users WHERE ApplicationId = @ApplicationId AND LoweredUserName = LOWER(@UserName)", conn))
                {
                    conn.Open();

                    cmd.Parameters.Add(CreateInputParam("@ApplicationId", SqlDbType.UniqueIdentifier, SqlCeMembershipUtils.GetApplicationId(conn.ConnectionString, ApplicationName)));
                    cmd.Parameters.Add(CreateInputParam("@UserName", SqlDbType.NVarChar, username));

                    int userCount = (int)cmd.ExecuteScalar();
                    if (userCount.Equals(0))
                    {
                        // create user: pinched from SqlCeMembershipProvider.CreateUser method
                        userId = Guid.NewGuid();

                        using (SqlCeCommand cmd2 = new SqlCeCommand(
                                   @"INSERT INTO [aspnet_Users]
									([ApplicationId]
									,[UserId]
									,[UserName]
									,[LoweredUserName]
									,[IsAnonymous]
									,[LastActivityDate])
							VALUES
									(@ApplicationId
									,@UserId
									,@UserName
									,@LoweredUserName
									,@IsAnonymous
									,@LastActivityDate)
							"                            , conn))
                        {
                            cmd2.Parameters.Add("@ApplicationId", SqlDbType.UniqueIdentifier).Value = SqlCeMembershipUtils.GetApplicationId(conn.ConnectionString, ApplicationName);
                            cmd2.Parameters.Add("@UserId", SqlDbType.UniqueIdentifier).Value        = (Guid)userId;
                            cmd2.Parameters.Add("@UserName", SqlDbType.NVarChar, 256).Value         = username;
                            cmd2.Parameters.Add("@LoweredUserName", SqlDbType.NVarChar, 256).Value  = username.ToLowerInvariant();
                            cmd2.Parameters.Add("@IsAnonymous", SqlDbType.Bit).Value           = !userIsAuthenticated;
                            cmd2.Parameters.Add("@LastActivityDate", SqlDbType.DateTime).Value = DateTime.UtcNow;

                            int rowsAffected = cmd2.ExecuteNonQuery();

                            if (!rowsAffected.Equals(1))
                            {
                                // oops!
                            }
                        }
                    }
                    else if (userCount > 1)
                    {
                        throw new Exception(string.Format("Duplicate user records found for username '{0}' and application '{1}'", username, ApplicationName));
                    }

                    cmd.CommandText = "SELECT UserId FROM aspnet_Users WHERE ApplicationId = @ApplicationId AND LoweredUserName = LOWER(@UserName)";
                    userId          = (Guid)cmd.ExecuteScalar();
                }

                using (SqlCeCommand cmd = new SqlCeCommand("UPDATE aspnet_Users SET LastActivityDate=@CurrentTimeUtc WHERE UserId = @UserId", conn))
                {
                    cmd.Parameters.Add(CreateInputParam("@UserId", SqlDbType.UniqueIdentifier, userId));
                    cmd.Parameters.Add(CreateInputParam("@CurrentTimeUtc", SqlDbType.DateTime, DateTime.UtcNow));
                    cmd.ExecuteNonQuery();
                }

                int profileCount = 0;

                using (SqlCeCommand cmd = new SqlCeCommand("SELECT COUNT(UserId) FROM aspnet_Profile WHERE UserId = @UserId", conn))
                {
                    cmd.Parameters.Add(CreateInputParam("@UserId", SqlDbType.UniqueIdentifier, userId));

                    object o = cmd.ExecuteScalar();
                    profileCount = (int)o;
                }

                switch (profileCount)
                {
                case 0:
                    using (SqlCeCommand cmd = new SqlCeCommand("INSERT INTO aspnet_Profile(UserId, PropertyNames, PropertyValuesString, PropertyValuesBinary, LastUpdatedDate) VALUES (@UserId, @PropertyNames, @PropertyValuesString, @PropertyValuesBinary, @CurrentTimeUtc)", conn))
                    {
                        cmd.Parameters.Add(CreateInputParam("@UserId", SqlDbType.UniqueIdentifier, userId));
                        cmd.Parameters.Add(CreateInputParam("@PropertyNames", SqlDbType.NText, names));
                        cmd.Parameters.Add(CreateInputParam("@PropertyValuesString", SqlDbType.NText, values));
                        cmd.Parameters.Add(CreateInputParam("@PropertyValuesBinary", SqlDbType.Image, buf));
                        cmd.Parameters.Add(CreateInputParam("@CurrentTimeUtc", SqlDbType.DateTime, DateTime.UtcNow));
                        cmd.ExecuteNonQuery();
                    }
                    break;

                case 1:
                    using (SqlCeCommand cmd = new SqlCeCommand("UPDATE aspnet_Profile SET PropertyNames=@PropertyNames, PropertyValuesString = @PropertyValuesString, PropertyValuesBinary = @PropertyValuesBinary, LastUpdatedDate=@CurrentTimeUtc WHERE UserId = @UserId", conn))
                    {
                        cmd.Parameters.Add(CreateInputParam("@UserId", SqlDbType.UniqueIdentifier, userId));
                        cmd.Parameters.Add(CreateInputParam("@PropertyNames", SqlDbType.NText, names));
                        cmd.Parameters.Add(CreateInputParam("@PropertyValuesString", SqlDbType.NText, values));
                        cmd.Parameters.Add(CreateInputParam("@PropertyValuesBinary", SqlDbType.Image, buf));
                        cmd.Parameters.Add(CreateInputParam("@CurrentTimeUtc", SqlDbType.DateTime, DateTime.UtcNow));
                        cmd.ExecuteNonQuery();
                    }
                    break;
                }
            }
        }
        public override int GetNumberOfInactiveProfiles(ProfileAuthenticationOption authenticationOption, DateTime userInactiveSinceDate)
        {
            using (SqlCeConnection conn = new SqlCeConnection(connectionString))
            {
                conn.Open();

                SqlCeDataReader reader;

                List <string> userids = new List <string>();

                using (SqlCeCommand cmd = new SqlCeCommand("SELECT UserId FROM aspnet_Users u WHERE ApplicationId = @ApplicationId AND LastActivityDate <= @InactiveSinceDate AND (@ProfileAuthOptions = 2 OR (@ProfileAuthOptions = 0 AND IsAnonymous = 1) OR (@ProfileAuthOptions = 1 AND IsAnonymous = 0))", conn))
                {
                    cmd.Parameters.Add(CreateInputParam("@ApplicationId", SqlDbType.UniqueIdentifier, SqlCeMembershipUtils.GetApplicationId(conn.ConnectionString, ApplicationName)));
                    cmd.Parameters.Add(CreateInputParam("@ProfileAuthOptions", SqlDbType.Int, (int)authenticationOption));
                    cmd.Parameters.Add(CreateInputParam("@InactiveSinceDate", SqlDbType.DateTime, userInactiveSinceDate.ToUniversalTime()));

                    reader = cmd.ExecuteReader();

                    while (reader.Read())
                    {
                        userids.Add("'" + ((Guid)reader["UserId"]).ToString() + "'");
                    }
                }

                using (SqlCeCommand cmd = new SqlCeCommand())
                {
                    cmd.Connection  = conn;
                    cmd.CommandText = "SELECT COUNT(UserId) FROM aspnet_Profile WHERE UserId IN (" + string.Join(",", userids.ToArray()) + ")";

                    object o = cmd.ExecuteScalar();
                    if (o == null || !(o is int))
                    {
                        return(0);
                    }
                    return((int)o);
                }
            }
        }