Creating custom ASP.NET 2.0 profile providers

ASP.NET 2.0 is a compelling platform for most Web sites. The inclusion of many of the basic facilities that most Web sites will need like the ability to have members, keep profile information on those members and use role-based authorization, makes ASP.NET 2.0 an easy decision for new development.

Probably the most compelling of these systems is the profile system, which allows you to keep data about visitors to your Web site (anonymous or not). The default Profiling system in ASP.NET 2.0 fits into many Web site use-cases, but not all.  Luckily, none of these systems in ASP.NET 2.0 require you to meet their specific feature set. ASP.NET 2.0 uses something called a Provider Model to allow extension or replacement of their implementation to fit into Web site specific use-cases. As a developer, this means that if it doesn't fit, you don't have to throw the baby out with the bathwater. As an example, let's look at how the Profile system works in ASP.NET 2.0. In this article, I will walk you through creating your own Profile provider to extend the capabilities.

Using ASP.NET 2.0 Profiles

Out of the box, the Profile system is fairly straightforward. It supports a configurable object to store specific information about the current user of a Web site. While Profile data is often thought of as an extension of Membership data, it's actually not related. You can have Profile data for users whether they are authenticated or not.

The configuration of the Profile for a particular Web site is as simple as adding a profile section to the web.config file:

 <?xml version="1.0"?> <configuration xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0"> <system.web> <profile> <properties> <add name="BirthDate" type="DateTime"/> <add name="FavoriteNumber" type="int"/> <add name="Comment" type="string"/> <add name="FavoriteColor" type="string" defaultValue="Blue"/> <add name="FavoriteAlbums" type="System.Collections.Specialized.StringCollection" serializeAs="Xml"/> </properties> </profile> </system.web> </configuration>

In this example we are adding five properties that can be set and retrieved by users of a Web site. Notice that properties are more than just name-value strings; they can be any valid .NET data type that can be serialized. The configuration generates a type-safe wrapper for Profile data where you can set or get the data configured in the configuration file by using simple properties:

 // BirthDate is DateTime textDob.Text = Profile.BirthDate.ToShortDateString(); // FavoriteNumber is Int32 textFavNumber.Text = Profile.FavoriteNumber.ToString(); // Comment is String textComment.Text = Profile.Comment; // FavoriteColor is String dropDownFavColor.SelectedValue = Profile.FavoriteColor; // FavoriteAlbums is StringCollection foreach (string album in Profile.FavoriteAlbums) { listBoxFavAlbums.Items.FindByText(album).Selected = true; }

The static Profile class exposes each of the properties as their native .NET types. This makes the Profile class very simple to use. At the end of each page's execution, the Profile object is serialized to the database. The Profile database is deceptively simple:

Figure 1: Profile Table Schema

Once data is saved, here a single user's Profile data is contained in a single row:


 UserId: 2684E3CA-D863-4229-A36D-87A1C0A926AC PropertyNames: Comment:S:0:9:FavoriteColor:S:9:4:FavoriteNumber:S:13:1:BirthDate:S:14:81:FavoriteAlbums:S:95:241: PropertyValuesString: Hello AllCyan5<?xml version="1.0" encoding="utf-16"?> <dateTime>1969-04-24T00:00:00</dateTime><?xml version="1.0" encoding="utf-16"?> <ArrayOfString xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <string>The Wall</string> <string>Try Whistling This</string> </ArrayOfString> <strong>PropertyValuesBinary:</strong> 0x

For the single user specified in the UserId, it stores the names of the property followed by some metadata about how to retrieve it from the PropertyValuesXXXX columns. For example, the comment is in the PropertyValuesString field (notice the S ) from character 0 to 9.

For my needs, I need to find profiles that match certain criteria. For example, let's say I need to find all the users with today's birthday to send them a special e-mail. I start by looking at the ProfileManager class. The ProfileManager class has ways to find users by username, whether they are inactive or just find all users. So lets implement this behavior with the existing functionality:

 // Find the people who were born today List<ProfileCommon> FindBirthDayPeople() { // Use a generic list of people List<ProfileCommon> birthdayPeople = new List<ProfileCommon>(); // Get all profile objects ProfileInfoCollection profiles = ProfileManager.GetAllProfiles(); // Go through the profiles foreach (ProfileInfo info in profiles) { // We need to turn a ProfileInfo into a ProfileCommon // to access the properties to do the search ProfileCommon userProfile = ProfileCommon.Create(info.UserName); // If the birthday matches if (userProfile.BirthDate.Day == DateTime.Today.Day && userProfile.BirthDate.Month == DateTime.Today.Month) { // Add them to our list birthdayPeople.Add(userProfile); } } return birthdayPeople; }

This works, but does not scale well. If we have a thousand users and run this once a day fine, but we might need more searching.

This is just one example of a feature that Profile does not support that you may need in your own application. If Profile does not support what you need, are you stuck to go back to the 1.x days and implement your own system for storing and retrieving profile data? Luckily the answer is no.

ASP.NET 2.0 Provider Model

For many of the subsystems in ASP.NET 2.0 (e.g. Membership, Profile, Roles and Personalization), Microsoft has created a provider model where the service is provided by a class or set of classes. For the initial release, Microsoft has created a provider that it hopes will meet most client needs. If the implementation does not fit a particular need, we can replace their provider with our own to meet our needs.

Providers are a class with a well-known interface that is implemented to meet the needs of the sub-system. For example, a Profile provider is a class that derives from the abstract ProfileProvider class (also known as a MustInherit class to VB.NET folks). This class provides abstract methods and properties that make up the interface for a Profile provider.

To hook up a particular provider, you simply change the configuration file to add a provider and specify it as the default for your subsystem. For example, to use the SqlProfileProvider, but use your own specific configuration file:

 <profile defaultProvider="MyProfileProvider"> <providers> <add name="MyProfileProvider" connectionStringName="MyDatabase" applicationName="/" type="System.Web.Profile.SqlProfileProvider, System.Web" /> </providers> <properties> 
 </properties> </profile>

We are creating a specific provider that uses the SqlProfileProvider, but specifies what the connection string name (in the connectionStrings configuration file section). We are also telling profile which provider to use. You can have multiple providers specified (more appropriate in machine.config) and have different sites using different providers if necessary.

In the case where you want your own provider completely, you could implement your own and specify the type and assembly in the type attribute. As long as that type implements the required interface, the rest of the Profile system will perform appropriately.

Creating a New Provider: Create or Extend?

Now that we know that we need a provider with new or different capabilities than the basic providers, we need to determine whether to create our own or just extend an existing provider. Creating a provider requires that you inherit from the Profile Provider class and implement any abstract methods/properties. Figure 2 below shows a custom provider with just the abstract methods and properties defined:

Figure 2: A custom provider class

By looking at the class, it is clear to me that most of the interface is dedicated to getting profile objects, not necessarily retrieving property values. In order to allow for searchable profiles, I only need to change the way that the properties are stored and retrieved. With this in mind, it looks like extending the SqlProfileProvider would be more straightforward than actually implementing my own entirely.

Searchable Profile Provider: An Example

In this example, we are going to extend the SqlProfileProvider and override only those methods and properties that are going to change in behavior with what the standard profile does.

To create a searchable profile provider, I must first change the way the profile properties are stored so I can perform searches in the database instead of the brute force method we saw earlier. Figure 3 shows the new schema for the Profile table:

Figure 3: New Profile Schema

The important part of the schema change is that the properties are now stored per row to make searching easier. For example, properties for a user will look like this in the database:

 UserID PropertyName PropertyValue --------------------------------------------------------------------- 47BD426C-B1E6-4BF1-82FB-FB803B98CC50 FirstName Shawn 47BD426C-B1E6-4BF1-82FB-FB803B98CC50 LastName Wildermuth 47BD426C-B1E6-4BF1-82FB-FB803B98CC50 Website http://adoguy.com

When making this change in a database, I can expect that the aspnet_regsql.exe tool will be run first to make up the standard database. Then my change script for the table can be made that includes both the new table definition, but also the foreign key to the user table:

 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[aspnet_Profile]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[aspnet_Profile] GO CREATE TABLE [dbo].[aspnet_Profile] ( [UserId] [uniqueidentifier] NOT NULL , [PropertyName] [nvarchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [PropertyValueString] [nvarchar] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [PropertyValueBinary] [image] NULL , [LastUpdatedDate] [datetime] NOT NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO ALTER TABLE [dbo].[aspnet_Profile] WITH NOCHECK ADD CONSTRAINT [PK__aspnet_Profile__239E4DCF] PRIMARY KEY CLUSTERED ( [UserId], [PropertyName] ) ON [PRIMARY] GO ALTER TABLE [dbo].[aspnet_Profile] ADD CONSTRAINT [FK__aspnet_Pr__UserI__24927208] FOREIGN KEY ( [UserId] ) REFERENCES [dbo].[aspnet_Users] ( [UserId] ) GO

Next we need stored procedures for storing and retrieving data from the database. The stored procedure to get properties from the database looks like this:

 ALTER PROCEDURE dbo.aspnet_Profile_GetProperties @ApplicationName NVARCHAR(256), @UserName NVARCHAR(256), @TimeZoneAdjustment INT AS BEGIN DECLARE @ApplicationId UNIQUEIDENTIFIER SELECT @ApplicationId = NULL SELECT @ApplicationId = ApplicationId FROM dbo.aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName IF (@ApplicationId IS NULL) RETURN DECLARE @UserId UNIQUEIDENTIFIER SELECT @UserId = NULL DECLARE @DateTimeNowUTC DATETIME EXEC dbo.aspnet_GetUtcDate @TimeZoneAdjustment, @DateTimeNowUTC OUTPUT SELECT @UserId = UserId FROM dbo.aspnet_Users WHERE ApplicationId = @ApplicationId AND LoweredUserName = LOWER(@UserName) IF (@UserId IS NULL) RETURN SELECT PropertyName, PropertyValueString, PropertyValueBinary FROM dbo.aspnet_Profile WHERE UserId = @UserId IF (@@ROWCOUNT > 0) BEGIN UPDATE dbo.aspnet_Users SET LastActivityDate=@DateTimeNowUTC WHERE UserId = @UserId END END

The signature for the stored procedure includes parameters for the application name, user name and a time-zone adjustment. The application name is used to allow for a username to be used across several sites in the same database. The time-zone adjustment is a number that the system uses to convert local time to GMT time. This is important because the stored procedure updates the LastActivityDate on the User object whenever data is retrieved. The LastActivityDate allows the system to store that date as GMT time instead of local time.

Once the application ID is retrieved by using the application name, we retrieve the right user id for the application. Assuming all went well at this point, we do a fairly simple query to find all properties for that user. This is the data we are actually returning to the provider.

Much in the same way as the retrieval of properties above, setting properties is fairly straightforward:

 ALTER PROCEDURE dbo.aspnet_Profile_SetProperties @ApplicationName NVARCHAR(256), @PropertyName NVARCHAR(256), @PropertyValueString NVARCHAR(3000), @PropertyValueBinary IMAGE, @UserName NVARCHAR(256), @IsUserAnonymous BIT, @TimeZoneAdjustment INT AS BEGIN DECLARE @ApplicationId UNIQUEIDENTIFIER SELECT @ApplicationId = NULL DECLARE @ErrorCode INT SET @ErrorCode = 0 DECLARE @TranStarted BIT SET @TranStarted = 0 IF( @@TRANCOUNT = 0 ) BEGIN BEGIN TRANSACTION SET @TranStarted = 1 END ELSE SET @TranStarted = 0 EXEC dbo.aspnet_Applications_CreateApplication @ApplicationName, @ApplicationId OUTPUT IF( @@ERROR <> 0 ) BEGIN SET @ErrorCode = -1 GOTO Cleanup END DECLARE @DateTimeNowUTC DATETIME EXEC dbo.aspnet_GetUtcDate @TimeZoneAdjustment, @DateTimeNowUTC OUTPUT DECLARE @UserId UNIQUEIDENTIFIER DECLARE @LastActivityDate DATETIME SELECT @UserId = NULL SELECT @LastActivityDate = @DateTimeNowUTC SELECT @UserId = UserId FROM dbo.aspnet_Users WHERE ApplicationId = @ApplicationId AND LoweredUserName = LOWER(@UserName) IF (@UserId IS NULL) EXEC dbo.aspnet_Users_CreateUser @ApplicationId, @UserName, @IsUserAnonymous, @LastActivityDate, @UserId OUTPUT IF( @@ERROR <> 0 ) BEGIN SET @ErrorCode = -1 GOTO Cleanup END IF (EXISTS( SELECT * FROM dbo.aspnet_Profile WHERE UserId = @UserId AND PropertyName = @PropertyName)) UPDATE dbo.aspnet_Profile SET PropertyName=@PropertyName, PropertyValueString = @PropertyValueString, PropertyValueBinary = @PropertyValueBinary, LastUpdatedDate=@DateTimeNowUTC WHERE UserId = @UserId AND PropertyName = @PropertyName ELSE INSERT INTO dbo.aspnet_Profile(UserId, PropertyName, PropertyValueString, PropertyValueBinary, LastUpdatedDate) VALUES (@UserId, @PropertyName, @PropertyValueString, @PropertyValueBinary, @DateTimeNowUTC) IF( @@ERROR <> 0 ) BEGIN SET @ErrorCode = -1 GOTO Cleanup END UPDATE dbo.aspnet_Users SET LastActivityDate=@DateTimeNowUTC WHERE UserId = @UserId IF( @@ERROR <> 0 ) BEGIN SET @ErrorCode = -1 GOTO Cleanup END IF( @TranStarted = 1 ) BEGIN SET @TranStarted = 0 COMMIT TRANSACTION END RETURN 0 Cleanup: IF( @TranStarted = 1 ) BEGIN SET @TranStarted = 0 ROLLBACK TRANSACTION END RETURN @ErrorCode END

This stored procedure takes in all the data for a single property. The stored procedure then creates the user and application if they do not exist yet. Lastly it determines if this is a new or updatable control, then INSERTS or UPDATES as appropriately.

Implementing the Class

Now that we have a place to store our properties, let's look at how we should implement the provider. Our new provider will extend the SqlProfileProvider so we first need to subclass it:

 <public class SearchableProfileProvider : SqlProfileProvider {}

Our class needs to get information like the connection string from the configuration file. As part of a Profile provider, its Initialize method is called before any work is done:

 public override void Initialize(string name, NameValueCollection config) { // We must get these configuration sections first since the base // class will remove them from the configuration (not sure why) string configurationStringName = config["connectionStringName"]; string timeout = config["commandTimeout"]; // Call the base class to allow all other functions to work that we // didn't override. base.Initialize(name, config); // Get the connection String from the config file ConnectionStringSettings connStringSettings = ConfigurationManager.ConnectionStrings[configurationStringName]; if (connStringSettings != null) { _connectionString = connStringSettings.ConnectionString; } // Get the timeout value if (!string.IsNullOrEmpty(timeout)) { _commandTimeout = Convert.ToInt32(timeout); } }

By overriding the Initialize method, we can take the config parameter to get the configuration options. Specifically, we need to retrieve the name of the connection string and the timeout of the command. The SqlProfileProvider use these two pieces of information, but they keep them as private fields so we need to get our own copy of these for our provider.

Next we need to override the methods that get and set the properties. This is the largest piece of the work we need to do since we are changing the way that the standard provider does these things. To simplify access to the stored procedures, I add a component to the project and drag-n-drop SqlCommand objects for each of our stored procedures as seen in Figure 4:

Figure 4: Component Surface for Command Objects

This allows us to use the designer to quickly create the command object we need to get and set the properties. I call this class DataAccess and it is used through out the rest of the code in this article.

To start the guts of the work, let's start by implementing the SetPropertyValues method to store our values in Profile. We want to override this method since the base class'implementation is what we are trying to replace:

 public override void SetPropertyValues(SettingsContext sc, SettingsPropertyValueCollection properties)

This method takes a SettingsContext object and a list of settings for the individual properties to save. The SettingsContext object is used to get the name of the current user, and the properties are used to hold the metadata about the data to save.

 // Determine If Anonymous HttpContext ctx = HttpContext.Current; bool isAnonymous = true; if (HttpContext.Current != null && HttpContext.Current.Request != null && HttpContext.Current.Request.IsAuthenticated) { isAnonymous = false; } // Get the user from the Context string userName = (string)sc["UserName"]; // Get an instance of our DataAccess component DataAccess db = new DataAccess(); // Shared Parameters db.SetPropertiesCommand.Parameters["@ApplicationName"].Value = this.ApplicationName; db.SetPropertiesCommand.Parameters["@UserName"].Value = userName; db.SetPropertiesCommand.Parameters["@IsUserAnonymous"].Value = isAnonymous; db.SetPropertiesCommand.Parameters["@TimeZoneAdjustment"].Value = this.ComputeTimeZoneVariance();

The first part of the method is to simply setup the standard parameters of the stored procedure. Of note is the ComputeTimeZoneVariance method which simply computes the offset from GMT. Then we open the connection to the database to make the changes:

 // Get the connection we're going to use using (SqlConnection conn = new SqlConnection(_connectionString)) { try { // Go through each value to find the ones that need updating foreach (SettingsPropertyValue value in properties) { // Only save if necessary if (value.IsDirty) { if (conn.State == ConnectionState.Closed) { // Set the connection to our Command db.SetPropertiesCommand.Connection = conn; conn.Open(); } // Set the Parameters SetPropertyParameters(db.SetPropertiesCommand, value); // Execute the command int result = db.SetPropertiesCommand.ExecuteNonQuery(); // If no rows were affected, then fail if (result == 0) { throw new InvalidProgramException("Updating the database failed"); } } } } finally { // If anything happened weird, close the connection. The using command // will dispose of the connection. if (conn != null) conn.Close(); } }

We do this by going through each of the property values we were sent and update the database if the property has been changed (e.g. IsDirty). Before we call out to the database, we call a method called SetPropertyParameters to fill in the parameters of the stored procedure. Creating the parameters is called out because we may need to serialize or format the property before we send it to the database. The SetPropertyParameters method looks like so:

 void SetPropertyParameters(SqlCommand cmd, SettingsPropertyValue value) { // Set the property name cmd.Parameters["@PropertyName"].Value = value.Property.Name; // Get the Serialized Version value.SerializedValue = GetSerializedPropertyValue(value.Property, value.PropertyValue); // Store in the parameters if (value.SerializedValue is string && ((string)value.SerializedValue).Length <= 3000) { cmd.Parameters["@PropertyValueString"].Value = value.SerializedValue; cmd.Parameters["@PropertyValueBinary"].Value = DBNull.Value; } else { cmd.Parameters["@PropertyValueString"].Value = DBNull.Value; cmd.Parameters["@PropertyValueBinary"].Value = value.SerializedValue; } }

First we set the property name and serialize the value into the property's SerializedValue property. Once it is serialized, we can determine whether we can store it in the string or the image field in the database. This method simply takes the SerializeAs property that was defined in the .config file and attempts to convert it to a version that can be stored in the database. This method is very straightforward and you can view the source code to see how it exactly works. Now that we can save our data into the database, we need a way of retrieving it from the database. This is done in the GetPropertyValues method.

Overriding the GetPropertyValues property requires that we create a SettingsPropertyValueCollection object to contain all of the properties for this particular user:

 public override SettingsPropertyValueCollection GetPropertyValues(SettingsContext sc, SettingsPropertyCollection properties)

Inside the GetPropertyValues method, first go through the expected properties as passed in as a SettingsPropertyCollection to create our expected property list. This list ( returnCollection) will be initialized with default values so that if the database does not contain a value yet.

 // Create the Collection to return SettingsPropertyValueCollection returnCollection = new SettingsPropertyValueCollection(); // If there are properties to retrieve if (properties.Count != 0) { // Go through each property to determine how to store/retrieve it foreach (SettingsProperty property in properties) { // If it's Provider specific, try and see if it can be // Stored as a string if (property.SerializeAs == SettingsSerializeAs.ProviderSpecific) { // If it is a primitive type or a string, then just store as string if (property.PropertyType.IsPrimitive || (property.PropertyType == typeof(string))) { property.SerializeAs = SettingsSerializeAs.String; } else // Else serialize it as XML { property.SerializeAs = SettingsSerializeAs.Xml; } } // Set it as use default to be overridden if the value as been found SettingsPropertyValue propValue = new SettingsPropertyValue(property); propValue.IsDirty = false; // Add it to the collection to return returnCollection.Add(propValue); } // … } }

Next we actually retrieve the data from the database by using a datareader to load the data (also utilizing the DataAccess class mentioned above):

 void GetPropertyValuesFromDatabase(string userName, SettingsPropertyValueCollection returnCollection) { // Get an instance of our DataAccess component DataAccess db = new DataAccess(); // Get the connection we're going to use using (SqlConnection conn = new SqlConnection(_connectionString)) { // Set the connection to our Command db.GetPropertiesCommand.Connection = conn; // Try and open the connection and get the results try { conn.Open(); // Set the Parameters db.GetPropertiesCommand.Parameters["@ApplicationName"].Value = this.ApplicationName; db.GetPropertiesCommand.Parameters["@UserName"].Value = userName; db.GetPropertiesCommand.Parameters["@TimeZoneAdjustment"].Value = this.ComputeTimeZoneVariance(); // Get a DataReader for the results we need using (SqlDataReader rdr = db.GetPropertiesCommand.ExecuteReader(CommandBehavior.CloseConnection)) { while (rdr.Read()) { string propertyName = rdr.GetString(rdr.GetOrdinal("PropertyName")); SettingsPropertyValue value = returnCollection[propertyName]; if (value != null) { FillPropertyValue(value, rdr); } } } } finally { // If anything happened weird, close the connection. The using command // will dispose of the connection. if (conn != null) conn.Close(); } } }

This code executes the data reader and attempts to store each property using a private method called FillPropertyValue. The reason filling the property is broken out into its own method is that it needs to be responsible for converting the data from the database into the .NET types. As we saw in the SetPropertyValues method above, we serialized the profile properties to store them in the database. The FillPropertyValue method reverses this operation. Like GetSerializedValue above, the code is straightforward in FillPropertyValue and I will leave it as an exercise for the reader to see the source code to understand how the serialization works.

We can now read and write our properties. Our Profile provider is now on-par with the original provider. We now need to add search capabilities into our provider. For this provider I only needed simplified searching so I created an enumeration to allow us to define the types of searches we wanted to allow:

 public enum SearchOperator { Invalid = 0, Equals = 1, NotEqual = 2, Like = 3, LessThan = 4, GreaterThan = 5, FullText = 6 }

Inside the stored procedure, I use these search operator values to determine how to do the search. This is the stored procedure:

 ALTER PROCEDURE dbo.aspnet_Profile_FindProfiles ( @applicationName NVARCHAR(256), @propertyName NVARCHAR(256), @operatorType INT, @propertyValue NVARCHAR(3000) -- Can't search through binary fields in this version ) AS BEGIN DECLARE @applicationId UNIQUEIDENTIFIER SELECT @applicationId = NULL SELECT @applicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@applicationName) = LoweredApplicationName IF (@applicationId IS NULL) RETURN IF @operatorType = 1 -- Equal BEGIN SELECT u.UserName, u.IsAnonymous, u.LastActivityDate, p.LastUpdatedDate FROM aspnet_Profile p LEFT JOIN aspnet_Users u ON u.UserId = p.UserId AND u.ApplicationId = @applicationId WHERE p.PropertyName = @propertyName AND p.PropertyValueString = @propertyValue END IF @operatorType = 2 -- NotEqual BEGIN SELECT u.UserName, u.IsAnonymous, u.LastActivityDate, p.LastUpdatedDate FROM aspnet_Profile p LEFT JOIN aspnet_Users u ON u.UserId = p.UserId AND u.ApplicationId = @applicationId WHERE p.PropertyName = @propertyName AND p.PropertyValueString <> @propertyValue END IF @operatorType = 3 -- Like BEGIN SELECT u.UserName, u.IsAnonymous, u.LastActivityDate, p.LastUpdatedDate FROM aspnet_Profile p LEFT JOIN aspnet_Users u ON u.UserId = p.UserId AND u.ApplicationId = @applicationId WHERE p.PropertyName = @propertyName AND p.PropertyValueString LIKE '%' + @propertyValue + '%' END IF @operatorType = 4 -- LessThan BEGIN SELECT u.UserName, u.IsAnonymous, u.LastActivityDate, p.LastUpdatedDate FROM aspnet_Profile p LEFT JOIN aspnet_Users u ON u.UserId = p.UserId AND u.ApplicationId = @applicationId WHERE p.PropertyName = @propertyName AND p.PropertyValueString < @propertyValue END IF @operatorType = 5 -- GreaterThan BEGIN SELECT u.UserName, u.IsAnonymous, u.LastActivityDate, p.LastUpdatedDate FROM aspnet_Profile p LEFT JOIN aspnet_Users u ON u.UserId = p.UserId AND u.ApplicationId = @applicationId WHERE p.PropertyName = @propertyName AND p.PropertyValueString > @propertyValue END /* ENABLE IF FullText is enabled IF @operatorType = 6 -- FullText BEGIN SELECT u.UserName, u.IsAnonymous, u.LastActivityDate, p.LastUpdatedDate FROM aspnet_Profile p LEFT JOIN aspnet_Users u ON u.UserId = p.UserId AND u.ApplicationId = @applicationId WHERE p.PropertyName = @propertyName AND CONTAINS(p.PropertyValueString, @propertyValue) END */ END

The stored procedure simply does a search and uses if statements to determine which type of search to perform. Note that in the SQL scripts, the full text option is commented out. Feel free to comment it back in if you have enabled full-text indexing in your database and have added a full-text index on the aspnet_Profile table.

Now that we have a stored procedure for searching, creating a method on our provider to do this is relatively simple:

 public ProfileInfoCollection FindUsersByPropertyValue(SettingsProperty property, SearchOperator oper, object value) { ProfileInfoCollection retVal = new ProfileInfoCollection(); // Get an instance of our DataAccess component DataAccess db = new DataAccess(); // Get the connection we're going to use using (SqlConnection conn = new SqlConnection(_connectionString)) { // Set the connection to our Command db.FindProfilesCommand.Connection = conn; // Try and open the connection and get the results try { // Set the Parameters db.FindProfilesCommand.Parameters["@applicationName"].Value = this.ApplicationName; db.FindProfilesCommand.Parameters["@propertyName"].Value = property.Name; db.FindProfilesCommand.Parameters["@operatorType"].Value = (int)oper; // Serialize the Value for the Database object propValue = this.GetSerializedPropertyValue(property, value); if (!(propValue is string) || ((string)propValue).Length > 3000) { throw new InvalidOperationException("Cannot search for binary values or strings greater than 3,000 bytes in length. Serialization may increase the perceived size to over 3,000 for complex types"); } // Store the property value since we've verified it as a string db.FindProfilesCommand.Parameters["@propertyValue"].Value = propValue; // Open the database conn.Open(); // Get a DataReader for the results we need using (SqlDataReader rdr = db.FindProfilesCommand.ExecuteReader(CommandBehavior.CloseConnection)) { while (rdr.Read()) { // Not sure how important size of profile is, // we're using a zero size for now. retVal.Add(new ProfileInfo(rdr.GetString(0), rdr.GetBoolean(1), rdr.GetDateTime(2), rdr.GetDateTime(3), 0)); } } } finally { if (conn != null) conn.Close(); } } return retVal; }

This method uses the DataAccess class again to execute the stored procedure to perform our search. We use the GetSerializedValue method to get the version of the property we want to search for and execute the stored procedure. Note that we are accepting a SettingsProperty object ( property). It is used to get the metadata about how to serialize the property that the user is searching for. We then execute the stored procedure to get a data reader to load the data into memory.

Notice that we are not creating Profile objects, but are creating ProfileInfo objects instead. We are doing this because all the other GetXXXX() methods on providers return info objects, so we are following that pattern. ProfileInfo objects are lightweight, read-only objects that contain key pieces of information about a profile and can be used to create full profile objects if necessary. We can use our new search functionality from code like so:

 // Cast the current provider to our provider SearchableProfileProvider provider = (SearchableProfileProvider)ProfileManager.Provider; // Setup our Search criteria ProfileInfoCollection profiles = null; SettingsProperty property = Profile.Properties["DateOfBirth"]; object todaysDate = (object)System.DateTime.Today; // Perform the search profiles = provider.FindUsersByPropertyValue(property, SearchableProfileProvider.SearchOperator.Equals, todaysDate); // Show the found profiles birthdays.DataSource = profiles; birthdays.DataTextField = "UserName"; birthdays.DataBind();

An instance of the current Profile provider is kept as a property of the ProfileManager. To use the search criteria we must cast it to our provider type since the search method is a new method. Then we setup the search and execute it.


While writing your own provider for any of the new ASP.NET 2.0 services is not trivial, it is certainly within the skill set of most ASP.NET developers. Luckily since Microsoft is using a provider model for these services we can override or extend the capabilities of well known providers to add our own functionality to meet our specific project needs.

Download code samples

About the author

Shawn Wildermuth is the author of Pragmatic ADO.NET in the .NET series for Addison-Wesley. He is a Microsoft C# MVP, an MCSD and an independent consultant. He often speaks across the country as part of the INETA Speakers Bureau and is the founder of ADOGuy.com. Wildermuth has spent more than 20 years developing data-driven applications in the varied fields of accounting, real estate, Internet, data warehousing and medicine. His articles can be found in several journals, including MSDN Magazine, Windows 2000 Magazine, ONDotnet.com, InformIT.com and Intel.com.

This was first published in December 2007

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.