While the table based provider should work well for sites that just need to store Profile data in an easily query-able manner, some websites may need to perform more complex data mappings in order to work with the Profile feature. The SqlStoredProcedureProfileProvider allows you to map individual Profile properties to stored procedure parameters. You need to write a get and a set stored procedure that the provider calls. Using a stored procedure though gives you maximum flexibility when mapping Profile properties though. Inside of your stored procedure you can carry out additional business logic as well as complex mappings to arbitrary table structures.
For simplicity the current sample provider only supports the GetPropertyValues and SetPropertyValues overrides from ProviderBase. These are the two methods that are necessary to get the runtime aspect of the Profile feature working. Currently the sample stored procedure provider just throws a NotSupportedException from all of the other administrative methods defined on ProviderBase.
The provider configuration for the SqlStoredProcedureProfileProvider is similar to the configuration used for the table based provider. You configure the SqlStoredProcedureProfileProvider with the following two configuration entries:
<connectionStrings>
<add name="TestDatabase"
connectionString="points at your DB” />
</connectionStrings>
The provider requires a connection string
<profile defaultProvider=" StoredProcedureProfileProvider">
<providers>
<add name=" StoredProcedureProfileProvider"
type="Microsoft.Samples.SqlStoredProcedureProfileProvider"
connectionStringName="TestDatabase"
setProcedure="setCustomProfileData"
readProcedure="getCustomProfileData"
applicationName="DatabaseProfileProviderTest"/>
</providers>
<profile>
Sample provider configuration
Since the provider works with a database just like the SqlProfileProvider, the provider requires a connection string to be defined in the <connectionStrings /> section. The provider configuration element references this connection string with the “connectionStringName” attribute. As with the SqlProfileProvider, the SqlStoredProcedureProfileProvider also has an “applicationName” attribute. Since the sample SqlStoredProcedureProfileProvider should integrate well with the existing SQL providers from other features like Membership and Role Manager, it exposes “applicationName” so that it can store Profile data for many different ASP.NET applications in a single table. However, since it is up to you to write the get and set stored procedures that work with this provider, you could potentially ignore the application name when it is passed to your custom procedures. The sample stored procedures shown a little later should make this concept clearer.
The new attribute “setProcedure” tells the provider which stored procedure in SQL Server it should call when saving Profile data to the database. The new attribute “readProcedure” tells the provider which stored procedure in SQL Server it should call when retrieving Profile data from the database. In the sample above the provider has been configured to read Profile data by calling the getCustomProfileData stored procedure and write Profile data by calling the setCustomProfileData stored procedure.
To make use of the provider you need to associate it with one or more Profile properties. In the sample configuration above, the “defaultProvider” attribute on the <profile /> element has been set to an instance of the table based provider. As a result, any Profile properties that do not explicitly define a “provider” attribute will automatically store and retrieve their data using the SqlStoredProcedureProfileProvider.
The last set of required configuration information is on the individual Profile properties. Since the SqlStoredProcedureProfileProvider maps each Profile property to parameters on a stored procedure, it needs additional information.
<properties>
<add name="FirstName"
type="string"
defaultValue="[null]"
customProviderData=" FirstName;nvarchar;50"
/>
<add name="LastName"
type="string"
defaultValue="[null]"
customProviderData=" LastName;nvarchar;50"
/>
<add name="Age"
type="int"
customProviderData=" Age;int;1"
/>
</properties>
Sample profile properties mapped to the stored procedure provider
The SqlStoredProcedureProfileProvider requires that the customProviderData attribute contain three pieces of information separated by a semi-colon: the stored procedure parameter name, the ADO.NET database type, and the size (i.e. length) that should be used with the ADO.NET parameter.
The sample Profile property configuration shown above defines the following behavior when used with the SqlStoredProcedureProfileProvider.
| |
1. |
The FirstName Profile property is defined to be a string type on the auto-generated Profile type. When it is stored and retrieved in the database by the SqlStoredProcedureProfileProvider though, the provider maps it to a stored procedure parameter called “FirstName”. Furthermore the provider will tell ADO.NET that the parameter is an nvarchar that can only be 50 characters long. From the earlier provider definition there were two stored procedures defined. As a result the SqlStoredProcedureProfileProvider will read the first name out of the “FirstName” stored procedure parameter (an ouptput parameter) on a stored procedure called getCustomProfileData. The provider will pass the first name into the “FirstName” parameter of the stored procedure setCustomProfileData when it saves Profile data back to the database.
|
| |
2. |
The LastName Profile property is also defined as a string type on the auto-generated Profile type. When it is stored and retrieved in the database by the SqlStoredProcedureProfileProvider, the provider maps it to a stored procedure parameter called “LastName”. The provider will create a parameter in ADO.NET of type nvarchar, and as with the first name, this parameter will only allow a maximum of 50 characters.
|
| |
3. |
The Age Profile property is defined as an integer type on the auto-generated Profile type. When it is stored and retrieved in the database by the SqlStoredProcedureProfileProvider, the provider maps it to a stored procedure parameter called “Age”. The provider will create an ADO.NET parameter of type integer. Unlike the first and last name though, the size information is just stubbed out with the value “1”. This is because for primitive types like integers, ADO.NET ignores the size parameter.
|
As with the table based provider example, the two string Profile properties also define a defaultValue of “[null]”. The SqlStoredProcedureProfileProvider follows the same nullability behavior as the SqlTableProfileProvider. The stored procedure provider interprets a null output parameter value as meaning that no data exists for the corresponding Profile property. As a result the Profile feature looks at the defaultValue attribute on a Profile property to determine the specific .NET value that should be returned. In the case of the integer property, since this is a value type, the Profile feature will automatically return zero (the default value for an integer as defined by the .NET Framework) in the case that a null value was found in the database for the Age output parameter.
The .NET Framework type to ADO.NET data type mappings listed earlier for the table based provider also should work with the SqlStoredProcedureProfileProvider
With the sample provider configuration and Profile configuration shown above you need to setup two stored procedures in the database to work with the SqlStoredProcedureProfileProvider. Although you can include any custom logic you need inside of the stored procedures, you must include a few required parameters in addition to the parameters used for storing Profile property data.
An example of a get stored procedure that works with the previous configuration is shown below:
create procedure getCustomProfileData
@ApplicationName nvarchar(256),
@UserName nvarchar(256),
@FirstName nvarchar(50) OUTPUT,
@LastName nvarchar(50) OUTPUT,
@Age int OUTPUT
as
declare @ApplicationId uniqueidentifier
set @ApplicationId = NULL
--Get the appid
exec dbo.aspnet_Applications_CreateApplication
@ApplicationName, @ApplicationId OUTPUT
--Return data for the requested user in the application
select @FirstName = FirstName,
@LastName = LastName,
@Age = Age
from dbo.ProfileTable_1 pt,
dbo.vw_aspnet_Users u
where u.ApplicationId = @ApplicationId
and u.UserName = @UserName
and u.UserId = pt.UserId
go
Custom stored procedure for retrieving Profile data
The stored procedure defines three OUTPUT stored procedure parameters that correspond to the Profile properties FirstName, LastName, and Age. The SqlStoredProcedureProfileProvider expects that each mapped Profile property will have its value returned as an OUTPUT parameter from the custom read stored procedure. In addition the stored procedure defines two required input parameters: Username and ApplicationName. It is up to you to decide how you use this information to map a user’s Profile data in your database.
Since this sample stored procedure is simply wrapping the custom Profile table discussed earlier for the SqlTableProfileProvider, the stored procedure converts Username and ApplicationName into a GUID user identifier which is then used to lookup the data from the table. Also note that this stored procedure automatically creates a row in the ASP.NET aspnet_Applications table since the sample code is mirroring the automatic application creation behavior of the SqlProfileProvider. This is not required in your custom stored procedures – if you want this behavior you can use the same coding approach shown above. If application name has no real relationship to your existing data, then your custom stored procedures can ignore it.
An example of a set stored procedure that works with the previous configuration is shown below:
create procedure setCustomProfileData
@ApplicationName nvarchar(256),
@UserName nvarchar(256),
@IsUserAnonymous bit,
@FirstName nvarchar(50),
@LastName nvarchar(50),
@Age int
as
declare @ApplicationId uniqueidentifier
set @ApplicationId = NULL
declare @CurrentUtcDate datetime
set @CurrentUtcDate = getutcdate()
--Get the appid
exec dbo.aspnet_Applications_CreateApplication
@ApplicationName, @ApplicationId OUTPUT
--Create user if needed
declare @UserId uniqueidentifier
select @UserId = UserId
from dbo.vw_aspnet_Users
where ApplicationId = @ApplicationId
and LoweredUserName = LOWER(@UserName)
if(@UserId IS NULL)
exec dbo.aspnet_Users_CreateUser
@ApplicationId, @UserName, @IsUserAnonymous,
@CurrentUtcDate, @UserId OUTPUT
--Either insert a new row of data, or update a pre-existing row
if exists
(select 1 from dbo.ProfileTable_1 where UserId = @UserId)
BEGIN
update dbo.ProfileTable_1
set FirstName = @FirstName,
LastName = @LastName,
Age = @Age,
LastUpdatedDate = @CurrentUtcDate
where UserId = @UserId
END
else
BEGIN
insert dbo.ProfileTable_1 (
UserId, FirstName, LastName, Age, LastUpdatedDate)
values (
@UserId, @FirstName, @LastName, @Age, @CurrentUtcDate)
END
Go
Custom stored procedure for saving Profile data
The set stored procedure defines three input stored procedure parameters that correspond to the Profile properties FirstName, LastName, and Age. The SqlStoredProcedureProfileProvider expects that each mapped Profile property will have a corresponding input parameter that matches the dataytype and size configured in the customProviderData attribute. In addition the stored procedure defines two additional required input parameters: Username and ApplicationName. As with the read stored procedure, it is up to you to decide how you use this information to map a user’s Profile data in your database.
Since this sample stored procedure is simply wrapping the custom Profile table discussed earlier for the SqlTableProfileProvider, the stored procedure converts Username and ApplicationName into a GUID user identifier which is then used to lookup the data from the table. The stored procedure automatically creates a row in the ASP.NET aspnet_Applications table if needed as part of this process. Since this is a set stored procedure, it also automatically creates a row in the aspnet_Users tables if necessary (e.g. you are using Windows authentication so no user record exists in the ASP.NET application services tables yet). This behavior mirrors the automatic user creation behavior of the SqlProfileProvider. As with the read stored procedure, this is not required in your custom stored procedures if you have some other logic for determining users in your database.
Because there is only one set stored procedure configured for the SqlStoredProcedureProfileProvider, the stored procedure must properly handle the insert case (the very first time Profile data is created for a user) versus the update case (the Profile data already exists, so it only needs to be updated). The sample procedure does this by inserting a row in the database if one does not already exist. Otherwise the stored procedure updates the data.
The SqlStoredProcedureProfileProvider requires execute rights on the custom read and set stored procedures as shown below.
grant EXECUTE on dbo.getCustomProfileData to [YOURMACHINENAME\ASPNET]
go
grant EXECUTE on dbo.setCustomProfileData to [YOURMACHINENAME\ASPNET]
go
Required security grants for the stored procedures
As with the table based provider, you will need to choose the appropriate account identity – usually one of ASPNET (IIS5), NETWORK SERVICE (IIS6), a custom identity (if using a fixed identity in the <identity /> element), or a SQL Server user account. If you follow the coding approach shown above for the get and set stored procedures, you should also run the grant statements shown earlier for the SqlTableProfileProvider which grant execute access to the aspnet_Applications_CreateApplication and aspnet_Users_CreateUser stored procedures.
At this point you have completed all of the necessary configuration and database setup required to get the SqlStoredProcedureProfileProvider working. You can use the Profile feature inside of your pages, and the SqlStoredProcedureProfileProvider will take care of moving data between the Profile properties and the database.