If you are looking for a tutorial on how to search for members by display name in Community Server 2008, look no further. After many hours of working on it, I have finally figured out the magic combination and I'm willing to share it with the world. This article is intended for developers with ASP.Net and SQL experience.

Step 1: Add fn_GetProfileElement to your database

This is a database function that I've previously blogged about. Just copy the code from my Getting Profile Properties from ASP.Net and Community Server post and run it in Query Analyzer. We'll be using this function in the next step.

Step 1: Update cs_vw_Users_FullUser

cs_vw_Users_FullUser is the view that is used to query for the list of users. We'll want to add the users display name to this view.

The display name is stored in the PropertyValuesString field in the aspnet_Profile table. This field can contain a lot of different pieces of data. The table contains another field called PropertyNames that is used to find the value. The users display name is stored in a field called commonName.

Here is the code to recreate the view:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER View [dbo].[cs_vw_Users_FullUser]
as
SELECT
U.AppUserToken,
U.Email,
U.ForceLogin,
U.IsAnonymous,
U.LastAction,
U.LastActivity,
U.MembershipID,
U.UserAccountStatus,
U.UserID,
U.UserName,
U.CreateDate,
UP.AllowSitePartnersToContact,
UP.AllowSiteToContact,
UP.EnableAvatar,
UP.EnableDisplayInMemberList,
UP.EnableDisplayUnreadThreadsOnly,
UP.EnableEmail,
UP.EnableHtmlEmail,
UP.EnableOnlineStatus,
UP.EnablePrivateMessages,
UP.EnableThreadTracking,
UP.EnableFavoriteSharing,
UP.IsAvatarApproved,
UP.IsIgnored,
UP.ModerationLevel,
UP.Points as UserPoints,
UP.PostRank,
UP.PostSortOrder,
UP.PropertyNames as UserPropertyNames,
UP.PropertyValues as UserPropertyValues,
UP.PublicToken,
UP.SettingsID,
UP.TimeZone,
UP.TotalPosts,
dbo.fn_GetProfileElement('commonName', AP.PropertyNames, AP.PropertyValuesString) as DisplayName
FROM
cs_Users U (nolock)
INNER JOIN cs_UserProfile UP (nolock) ON U.UserID = UP.UserID
LEFT JOIN aspnet_Profile AP (nolock) ON U.MembershipID = AP.UserID

Step 3: Create a custom SqlCommonDataProvider

We need to customize the SqlCommonDataProvider in order to change the fields the search uses. You will want to create this class in a separate project. I attempted to do this in the App_Code directory in the web project but couldn't get past this error:

Critical Error: DataProvider

The dataprovider class "CommonDataProvider" could not be loaded.

There might be some way to get past this error and keep the code in the web project, but I found that creating a separate assembly worked.

Once you've got your class, you will want to copy the code in SqlGenerator to it. Community Server didn't exactly make mods easy, they use many non-overridable methods. All the methods in SqlGenerator are static and this is the code you need to modify which is why we are copying it.

In BuildMemberQuery, look for this line...

sb.Append(" and ( UserName LIKE @SearchText OR Email LIKE @SearchText ) ");

and change it to this...

sb.Append(" and ( DisplayName LIKE @SearchText OR UserName LIKE @SearchText OR Email LIKE @SearchText ) ");

Now override GetUserList, copy the code from the base method, and call your new BuildMemberList instead of the SqlGenerator version.

command.Parameters.Add("@sqlPopulate", SqlDbType.NText).Value = BuildMemberQuery(query, this.databaseOwner, false);
command.Parameters.Add("@sqlPopulateCount", SqlDbType.NText).Value = BuildMemberQuery(query, this.databaseOwner, true);

Compile the assembly into the web projects bin directory.

Step 4: Update communityserver.config

Now that we've got our new CommonDataProvider, we need to tell Community Server to use it. The setting for this is in communityserver.config which is in the root of the web project. Look for CommonDataProvider and replace the node with this (use your assembly name of course)...

<add
name = "CommonDataProvider"
type = "MyNamespace.MySqlCommonDataProvider, MyCustomAssembly"
connectionStringName = "SiteSqlServer"    
databaseOwnerStringName = "SiteSqlServerOwner"
/>

You can download the project and config file for this from my downloads section.

Download - Custom Community Server Common Data Provider (sorry, this is no longer available)

Step 5: Upload and enjoy

If you've followed all the steps, you should now be able to search for users based on their common or display name. If you are having difficulty, make sure you check out the sample project in the downloads section.

Apparently Mono 2.0 has been released today. I'm a bit surprised that I only saw a single article on the Redmond Developer website about it.

For those that don't know, Mono is a version of .Net that will run on Windows, Linux, and Mac. Although Mono provides their own C# and VB.Net compiler, it can also run code compiled by Visual Studio without any changes, though there are some compatibility issues so not all code will work (such as an pinvoke calls).

The 2.0 release of Mono is essentially .Net 3.5 minus 3.0 (no support for WPF, WCF, or WF). A couple of the nice new features in this version include support for WinForms and Xml document processing which apparently was missing in the previous release of Mono.

If you're interested in learning more about Mono or downloading it, here's a link to their website - Mono. I have not personally used Mono, but it's nice to know that the option is available.

If you are interested in getting the extended attributes from Community Server or ASP.Net membership, I've created a SQL Server database function that might help. I got the idea from another database function that's floating around the Internet (sorry, I don't know who to credit), but I had a little difficulty getting that one to work (I had the wrong version of another dependent function) so I rewrote it without the dependency.

Here's an example of using it (the code for the function is below the example). This example will return a list of all the users in Community Server along with some profile information.

SELECT  u.UserID, 
u.UserName,  
dbo.fn_GetProfileElement('MyCsProperty', up.PropertyNames, up.PropertyValues) AS MyCsProperty,
dbo.fn_GetProfileElement('MyAspProperty', p.PropertyNames, p.PropertyValuesString) AS MyAspProperty
FROM cs_users u
LEFT JOIN cs_UserProfile up ON (up.UserID = u.UserID)
LEFT JOIN aspnet_Profile p ON (p.UserID = u.MembershipID)

CREATE FUNCTION [dbo].[fn_GetProfileElement]
(
-- Add the parameters for the function here
@name NVARCHAR(100),
@keys NVARCHAR(4000),
@values NVARCHAR(4000)
)
RETURNS NVARCHAR(4000)
AS
BEGIN
-- If input is invalid, return null.
IF  @name IS NULL
OR LEN(@name) = 0
OR @keys IS NULL
OR LEN(@keys) = 0
OR @values IS NULL
OR LEN(@values) = 0
RETURN NULL
-- locate FieldName in Fields
DECLARE @pos AS INTEGER
DECLARE @endPos As INTEGER
DECLARE @valueStart AS INTEGER
DECLARE @valueLength AS INTEGER
-- Find the starting position of the key.
SET @pos = CHARINDEX(@name + ':S:', @keys, 0)
IF @pos = 0 RETURN NULL
-- Find the starting position of the value.
SET @pos = @pos + LEN(@name) + LEN(':S:')
SET @endPos = CHARINDEX(':', @keys, @pos)
SET @valueStart = CAST(SUBSTRING(@keys, @pos, @endPos - @pos) AS INT)
-- Find the length of the value.
SET @pos = @endPos + LEN(':')
SET @endPos = CHARINDEX(':', @keys, @pos)
SET @valueLength = CAST(SUBSTRING(@keys, @pos, @endPos - @pos) AS INT)
RETURN SUBSTRING(@values, @valueStart + 1, @valueLength)
END