Searching in User Profile Properties

Introduction:

Using ASP.Net 2005 login controls saves time and effort and produces a professional user management on your ASP.NET web site. User Profile is responsible for extending user properties; but there was a problem faced me that searching is not supported in user profile properties. If you want to search in profile properties you should customize profile provider or complete reading this article.

Profile Information Storage:

Default Profile Provider usually stores profile data in aspnet_Profile table in the following format:

 

PropertyNames: FullName:S:0:4:City:S:4:7:NickName:S:11:8:Mobile:S:19:4:

 

PropertyValuesString: AmroCairoooAmrooooo1212

 

As you can notice the PropertyNames describes PropertyValuesString for Example: FullName:S:0:4: means that the FullName Property which is String starts at index 0 and has length of 4. If you apply that you will get FullName = Amro From that principle I worked to generate a view that displays profile properties each as a column and thus we can search in that view.

Workout Summary:

1. Configure Default Profile in web.config.
2. Generate Assembly for SQL Server 2005 that gets start and length of each profile property.
3. Create the view using generated Assembly.

Default Profile Configuration:

 

<profile defaultProvider="DefaultProfileProvider">

    <providers>

        <add name="DefaultProfileProvider" type= "System.Web.Profile.SqlProfileProvider"connectionStringName="ConStr"/>

    </providers>

    <properties>

        <add name="NickName"/>

        <add name="City"/>

        <add name="Mobile"/>

        <add name="FullName"/>

    </properties>

</profile>

 

Generate SQLSERVER Add-on:

 

'using Microsoft.SqlServer.Server' enables you to write compiled code using any DOTNET language that can be plugged on SQLSERVER 2005 as an alternative for T-SQL or VB-Script in stored procedure. I have created a class library that contains two functions to get start index and length for specific property name.

 

[SqlFunction]

public static SqlInt32 GetProfilePropertyStart(SqlString propertyNames, SqlString propertyName)

{

    string pattern = propertyName.Value.ToLower() + "\\:[A-Za-z]*\\:\\d{1,3}\\:\\d{1,3}";

    Regex regex = new Regex(pattern);

    Match match = regex.Match(propertyNames.Value.ToLower());

    if (match.Success)

    {

        string prop = match.Groups[0].Value;

        pattern = "\\d{1,3}\\:\\d{1,3}";

        regex = new Regex(pattern);

        match = regex.Match(prop);

 

        int start = int.Parse(match.Groups[0].Value.Split(':')[0]);

        return (SqlInt32)start;

    }

    else

    {

        return -1;

    }

}

 

GetProfilePropertyLength is the same concept.

 

After that compile the DLL and easily you can plug it onto SQL Server 2005 by adding it to the following path [DBName]/Programmability/assemblies.

 

While you have the DLL already attached to your DB but you still can't use it yet!. You have to create SQL Function to warp it. For GetProfilePropertyStart the SQL function should be:

 

 

Figure 1:

 

Now we have everything ready to use our add on functions and create the view.

 

Create User Profile View:

 

 

Figure 2:

 

And now if you opened the view you can see each of the profile properties is a separate column and thus you can query the profile properties as you love.