Browse DevX
Sign up for e-mail newsletters from DevX


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.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

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