In my previous post, we have successfully bind the Membership.GetAllUsers() method to a databound control such as the GridView to enable paging and sorting. This time, I am building a mechanism that will allow me to search for users in the ASP.NET Membership database. By default, ASP.NET Membership supports searching by specific matches: email (Membership.FindUsersByEmail()), user id (aka provider user key), and by username (Membership.FindUsersByName()). There’s no built-in mechanism wherein you can query for users that contain specific characters — say you want to get users that starts with a specific letter or contains certain characters.
Examining closely the ASP.NET Membership database (either created by the WSAT or configured using the aspnet_regsql.exe), it has stored procedures that performs various queries to the ASP.NET membership. I tweaked one query to get the result that I wanted and stored into a new stored procedure and named it “Membership_QueryUsersByLetter”. This stored procedure should reside on the same database of your ASP.NET Membership.
CREATE PROCEDURE [dbo].[Membership_QueryUsersByLetter]
@SearchKey nvarchar(256)
AS
BEGIN
SELECT u.UserName, m.Email, m.PasswordQuestion,
m.Comment, m.IsApproved,
m.CreateDate,
m.LastLoginDate,
u.LastActivityDate,
m.LastPasswordChangedDate,
u.UserId, m.IsLockedOut,
m.LastLockoutDate
FROM
dbo.aspnet_Membership m, dbo.aspnet_Users u
WHERE
u.UserId = m.UserId AND u.UserName LIKE '%' + @SearchKey + '%'
ORDER BY
u.UserName
END
This query assumes that the ASP.NET membership database is configured for one application only. A sample application that you can use for this one can be downloaded here. Rename the file (searchusers.odt) to a zip extension file to extract files. Note however that you need to modify the connection string to match your current machine configuration to point to an ASP.NET membership database (or ASP.NET Membership configured database).
BuLOnG 