dcsimg
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


advertisement
 

Microsoft ASP.NET 2.0 Membership API Extended : Page 3

Working with big applications requires extending the Microsoft ASP.NET 2.0 Membership API to handle more detailed member records.


advertisement
Set up the Database
This section discusses the table I added to hold the values of the custom properties and the accompanying stored procedures that the new ExtendedMembershipProvider uses.

In this article, I'll use Microsoft SQL Server 2000. To benefit from the Membership API, you need to create a new empty database, and then install the application services database used by many new database-related features in ASP.NET 2.0, such as the Membership API. To do this, use the following procedure:

  1. Go to Microsoft SQL Server Enterprise Manager and create a new database called ExtendedMemberShipDb.
  2. Type the following code at a command prompt to install the application services database:
   {Drive Letter}:\Windows\Microsoft.NET\
   Framework\{Version Number}\aspnet_regsql.exe
Use the aspnet_regsql.exe utility to install the application service's tables into the current database. Once the executable runs, install the required tables, stored procedures, and user-defined functions to the ExtendedMemberShipDb database created above.

For more information on how to do the above process, you can check this step-by-step post on my blog: "Install Application Services Database on Microsoft SQL Server 2000."

Now that you have the database installed, create a new table called UserInfo to hold the values for the custom properties that you added.

This table contains the following columns:

  • A UserId of type UniqueIdentifier and designated as the primary key of the UserInfo table. It is also the primary key of this table.
  • FirstName of type VarChar and size 50
  • LastName of type VarChar and size 50
  • DateOfBirth of type DateTime
This table stores the extended data related to the member in the application. Note that the UserId is a primary key, of type UniqueIdentifier. UserId is the same key used as a primary key for the member's record in the default Membership API tables. By using the same key, you can relate the UserInfo table to all the other member management tables.

Now it is time to create the stored procedures that you will use in the SqlMembershipProvider (you will implement SqlMembershipProvider later in this article).

The stored procedures are:

  • aspnet_ExtendedMemberShip_CreateUser
  • aspnet_ExtendedMembership_DeleteUser
  • aspnet_ExtendedMembership_GetAllUsers
  • aspnet_ExtendedMembership_GetUserByUserId
  • aspnet_ExtendedMembership_GetUsersByUserIds
  • aspnet_ExtendedMembership_UpdateUser
The CreateUser stored procedure shown below checks whether the member to be added is already found in the table-if not found, CreateUser will insert the record.

   CREATE PROCEDURE aspnet_ExtendedMembership_CreateUser
   (
      @UserId UNIQUEIDENTIFIER,
      @FirstName VARCHAR(50),
      @LastName VARCHAR(50),
      @DateOfBirth DATETIME
   )
   AS
   BEGIN
   -- @ErrorCode to handle problems during adding a new record
   DECLARE @ErrorCode INT
   SELECT @ErrorCode = 0
   IF( EXISTS( SELECT UserId FROM 
       dbo.aspnet_ExtendedMembership_UserInfo
         WHERE @UserId = UserId ) )
        GOTO Cleanup
   INSERT INTO dbo.aspnet_ExtendedMembership_UserInfo
     (UserId, FirstName, LastName, DateOfBirth)
   VALUES   
     (@UserId, @FirstName, @LastName, @DateOfBirth)
   SELECT @ErrorCode = @@ERROR
   IF( @ErrorCode <> 0 )
      GOTO Cleanup
   RETURN 0
   Cleanup:
      SELECT @ErrorCode = -1
      RETURN @ErrorCode
   END
   GO
The same logic follows in the following DeleteUser stored procedure; that is, if the record is already in the table, delete it.

   CREATE PROCEDURE aspnet_ExtendedMembership_DeleteUser
   (
      @UserId UNIQUEIDENTIFIER,
      @NumTablesDeletedFrom INT OUTPUT
   )
   AS
   BEGIN
   -- @ErrorCode to handle problems during deleting a record
   DECLARE @ErrorCode INT
   DECLARE @RowCount INT
   SELECT @ErrorCode = 0
   SELECT @RowCount = 0
   SELECT @NumTablesDeletedFrom = 0
   IF (@UserId IS NULL)
      GOTO Cleanup
   IF (EXISTS (SELECT UserId
           FROM dbo.aspnet_ExtendedMembership_UserInfo
           WHERE @UserId = UserId))
   BEGIN
   DELETE 
     FROM aspnet_ExtendedMembership_UserInfo
     WHERE UserId = @UserId
   SELECT @ErrorCode = @@ERROR, @RowCount = @@ROWCOUNT
   IF( @ErrorCode <> 0 )
      GOTO Cleanup
      IF (@RowCount <> 0)
           SELECT   @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1
      END
      ELSE
        GOTO Cleanup
      RETURN 0
   Cleanup:
      SELECT @NumTablesDeletedFrom = 0
      SELECT @ErrorCode = -1
      RETURN @ErrorCode
   END
   GO
I will not cover all the stored procedures here because they all follow the same logic shown above. You can look at all of those stored procedures in the downloadable code accompanying this article.

Now that you have created the table and stored procedures, I'll show you how to develop the ExtendedMembershipProvider.



Thanks for your registration, follow us on our social networks to keep up-to-date