Searching Users with ASP.NET Membership Database

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).

Advertisements

0 Responses to “Searching Users with ASP.NET Membership Database”



  1. Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




Blog Stats

  • 3,965 hits
January 2008
S M T W T F S
« Dec   Feb »
 12345
6789101112
13141516171819
20212223242526
2728293031  

%d bloggers like this: