The SqlProfileProvider allows you to store profile
information in a SQL Server database. You can choose to create the
profile tables in any database. However, you can't change any of the
other database schema details, which means you're locked into specific
table names, column names, and serialization format.
From start to finish, you need to perform the following steps to use profiles:
Enable authentication for a portion of your website.
Configure the profile provider. (This step is optional if you're using SQL Server Express. Profiles are enabled by default.)
Create the profile tables. (This step isn't required if you're using SQL Server Express.)
Define some profile properties.
Use the profile properties in your web page code.
You'll tackle these steps in the following sections.
1. Enabling Authentication
Because profiles are
stored in a user-specific record, you need to authenticate the current
user before you can read or write profile information. You can use any
type of authentication system, including Windows-based authentication
and forms-based authentication. The profile system doesn't care—it
simply stores the user-specific information in a record that's
identified based on the user name. Seeing as every authentication system
identifies users uniquely by user name, any authentication system will
work.
The following web.config file uses Windows authentication:
<configuration>
<system.web>
<authentication mode="Windows"/>
<authorization>
<deny users="?"/>
</authorization>
...
</system.web>
</configuration>
Because this example uses
Windows authentication, you don't need to create a record for each user.
Instead, you'll use the existing Windows user accounts that are defined
on the web server. This approach also saves you from creating a login
page, because the browser handles the login process.
If you decide to use forms
authentication instead, you'll need to decide whether you want to
perform the authentication using your own custom user list or in combination with the membership features.
In most cases, the membership and profiles features are used in
conjunction—after all, if you're using the profiles feature to store
user-specific information automatically, why not also store the list of
user credentials (user names and passwords) automatically in the same
database?
Once you've
chosen your authentication system (and taken care of any other chores
that may be necessary, such as creating a user list and generating your
login page), you're ready to use profiles. Remember, profiles store
user-specific information, so the user needs to be authenticated before
their profile is available. In the web.config file shown previously, an
authorization rule ensures this by denying all anonymous users.
2. Using SQL Server Express
When using SQL Server
Express, ASP.NET stores profile information in an automatically
generated database file named aspnetdb.mdf. If this file doesn't exist,
it's created the first time you use any membership or profiles features,
and it's placed in the App_Data subdirectory of your web application.
The best part is that you don't need to go through any additional
configuration steps, because ASP.NET is configured, by default, to use
SQL Server with profiles.
3. Using the Full Version of SQL Server
This automatic-database creation
feature relies on SQL Server Express. If you're using a non-Express
version of SQL Server, you must create the database you need manually
and explicitly configure the profiles feature in the web.config file.
By default, the connection
string that is used with profiles is named LocalSqlServer. You can edit
this connection string directly in the machine.config file. However, if
you just need to tweak a single application, it's better to adjust the
web.config file for your web application.
To do so, you need to remove
all the existing connection strings using the <clear> element in
your web application's web.config file. Then, add the LocalSqlServer
connection string again—but this time with the right value:
<configuration>
<connectionStrings>
<clear />
<add name="LocalSqlServer" providerName="System.Data.SqlClient"
connectionString="Data Source=localhost;Integrated Security=SSPI;
Initial Catalog=aspnetdb" />
</connectionStrings>
...
</configuration>
You'll then need to create
the aspnetdb database using the aspnet_regsql.exe command-line utility.
This is the same tool that allows you to generate databases for other
ASP.NET features, such as SQL Server–based session state, membership,
roles, database cache dependencies, and Web Parts personalization. You
can find the aspnet_regsql.exe tool in the c:\Windows\Microsoft.NET\
Framework\[Version] folder (where [Version] is the version of ASP.NET
that's installed, like v4.0.30319).
To create the tables, views,
and stored procedures required for profiles, you use the -A p
command-line option. The other details you may need to supply include
the server location (-S), database name (-d), and authentication
information for connecting to the database (use -U and -P to supply a
password and user name, or use -E to use the current Windows account).
If you leave out the server location and database name,
aspnet_regsql.exe uses the default instance on the current computer and
creates a database named aspnetdb.
The easiest way to use
aspnet_regsql is to open the Visual Studio command prompt. To do so,
open the Start menu and choose All Programs => Microsoft Visual Studio 2010 => Visual Studio Tools =>
Visual Studio Command Prompt. The following example creates a database
named aspnetdb in the SQL Server database server on the current
computer. It adds all the ASP.NET tables, including the ones used for
membership, role-based authentication, and profiles:
aspnet_regsql.exe -S (local) -E -A all
If you want to use a different
database, you must specify the database name using the -d parameter.
Either way, you should use a new, blank database that doesn't include
any other custom tables. That's because aspnet_regsql.exe creates
several tables for profiles (see Table 21-1 in the next section), and you shouldn't risk confusing them with business data.
NOTE
This command line uses the -A all
option to create tables for all of ASP.NET's database features,
including profiles and membership. You can also choose to add tables for
just one feature at a time.
4. The Profile Databases
Whether you use
aspnet_regsql to create the profile databases on your own or you use SQL
Server Express and let ASP.NET create them automatically, you'll wind
up with the same tables. Table 1 briefly describes them. (The rather unexciting views aren't included.)
The contents aren't of much interest, because ASP.NET manages
them automatically. All the information you store in a profile is
combined into one record and quietly placed in a field named
PropertyValuesString in a table named aspnet_Profile.
Table 1. Database Tables Used for Profiles
| Table Name | Description |
|---|
| aspnet_Applications | Lists
all the web applications that have records in this database. It's
possible for several ASP.NET applications to use the same aspnetdb
database. In this case, you have the option of separating the profile
information so it's distinct for each application (by giving each
application a different application name when you register the profile
provider) or sharing it (by giving each application the same application
name). |
| aspnet_Profile | Stores
the user-specific profile information. Each record contains the
complete profile information for a single user. The PropertyNames field
lists the property names, and the PropertyValuesString and
PropertyValuesBinary fields list all the property data, although you'll
need to do some work if you want to parse this information for use in
other non-ASP.NET programs. Each record also includes the last update
date and time (LastUpdatedDate). |
| aspnet_SchemaVersions | Lists
the supported schemas for storing profile information. In the future,
this could allow new versions of ASP.NET to provide new ways of storing
profile information without breaking support for old profile databases
that are still in use. |
| aspnet_Users | Lists
user names and maps them to one of the applications in
aspnet_Applications. Also records the last request date and time
(LastActivityDate) and whether the record was generated automatically
for an anonymous user (IsAnonymous). |
Figure 1 shows the relationships between the most important profile tables.
5. Defining Profile Properties
Before you can store
any profile information, you need to specifically define what you want
to store. You do this by adding the <properties> element inside
the <profile> section of the web.config file. Inside the
<properties> element, you place one <add> tag for each
user-specific piece of information you want to store. At a minimum, the
<add> element supplies the name for the property, like this:
<configuration>
<system.web>
...
<profile>
<properties>
<add name="FirstName"/>
<add name="LastName"/>
</properties>
</profile>
</system.web>
...
</configuration>
Usually, you'll also supply
the data type. (If you don't, the property is treated as a string.) You
can specify any serializable .NET data type, as shown here:
<add name="FirstName" type="System.String"/>
<add name="LastName" type="System.String"/>
<add name="DateOfBirth" type="System.DateTime"/>
You can set a few more property attributes to create the more advanced properties shown in Table 2.
Table 2. Profile Property Attributes
| Attribute (for the <add> Element) | Description |
|---|
| name | The name of the property. |
| type | The fully qualified class name that represents the data type for this property. By default, this is System.String. |
| serializeAs | The
format to use when serializing this value (String, Binary, Xml, or
ProviderSpecific). You'll look more closely at the serialization model
in the section "Profile Serialization." |
| readOnly | A
Boolean value that determines whether a value is changeable. If true,
the property can be read but not changed. (Attempting to change the
property will cause a compile-time error.) By default, this is false. |
| defaultValue | A
default value that will be used if the profile doesn't exist or doesn't
include this particular piece of information. The default value has no
effect on serialization—if you set a profile property, ASP.NET will
commit the current values to the database, even if they match the
default values. |
| allowAnonymous | A
Boolean value that indicates whether this property can be used with the
anonymous profiles feature . By default,
this is false. |
| provider | The
profile provider that should be used to manage just this property. By
default, all properties are managed using the provider specified in the
<profile> element, but you can assign different properties to
different providers. |
