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.
Introduction
Probably the most compelling of these system 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 website 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.
What is a Provider?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.
Database Schema ChangesTo 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 ClassNow 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 wierd, 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.
Conclusion
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.
Authors
 | 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. Shawn has spent over twenty 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. |
 Related LinksRelated LinksRelated Links | ||||||
|---|---|---|---|---|---|---|
| ||||||
|
1147
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
