Profile 简介与自定义

小灰灰 2021-11-01 09:14 665阅读 0赞

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:

![Figure1.jpg][]

**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:

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:

![figure2.jpg][]

**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 Changes**

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:

![figure3.jpg][]

**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:

![figure4.jpg][]

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

[Download code samples][]

## Authors ##

<table> 
 <tbody> 
  <tr> 
   <td><img src="http://www.theserverside.net/tt/images/spacer.gif" alt="spacer.gif"></td> 
   <td>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. </td> 
  </tr> 
 </tbody> 
</table>

转载于:https://www.cnblogs.com/seandotnet/archive/2006/12/12/589701.html

[Figure1.jpg]: /images/20211101/6734762ffb334bb3b301f3455cecc609.png
[figure2.jpg]: /images/20211101/b95bfbe53d5547a1bfd37aa56e6f8a4c.png
[figure3.jpg]: /images/20211101/285a6d6119a549fc8723014cafb95824.png
[figure4.jpg]: /images/20211101/79e8245ef3534729982d158f18abd56c.png
[Download code samples]: http://www.theserverside.net/tt/articles/content/CreatingProfileProvider/ADOGuy.Profile.zip