By now, most developers know that SQL Server and Microsoft .NET are a dynamic duo. The Microsoft .NET Framework is on its way to becoming an integral part of several important Microsoft server products, including SQL Server. Even now, developers can create SQL Server solutions through Visual Studio .NET. But how easy is creating simple, everyday applications with this powerful pair? This article presents a simple membership application that's based on ASP.NET and a SQL Server database. The SQL Server database maintains the members in several related tables that keep identification, contact, and expiration information. The database also offers several stored procedures and a view for processing the membership data. ASP.NET manages the UI and provides basic functionality, such as Web connectivity, by referencing SQL Server objects. ASP.NET also tracks visitor login status and grants permissions to view pages or perform tasks based on that status. This article uses Visual Basic .NET (VB.NET) code to demonstrate ASP.NET techniques, but you can use any other .NET language.
Application Roadmap
The MemberApp.zip file, which you can download at InstantDoc ID 40888, contains a .sql script to create the database tables, stored procedures, and view that hold, manipulate, and expose membership data as well as two folders for the ASP.NET application. One folder contains the Web site pages, and the other folder holds the solution file that Visual Studio .NET opens to gain access to the folder of Web site pages. The SqlMagMemberApp Web application performs four basic functions for a membership site. First, it lets users log in and identify themselves as group members. After login, the application tracks this information throughout a Web session. Second, users can examine members-only content. If a user attempts to view a page before logging in as a valid member, the application rejects the attempt to link to a page. Third, the application lets members view fellow members' contact information. Finally, group members can update their own passwords and contact information.
The sample membership application includes six Web pages that demonstrate its basic functions. Figure 1 shows the relationships between these pages. The Default.aspx page is a Web form containing four LinkButton controls. Users can click these controls to go to one of four other pages: Login.aspx, Page1OfContent.htm, CurrentMembers.aspx, and EditMyMemberInfo.aspx. The code behind the Login.aspx page accepts member ID and password values. If the values exist in the SQL Server database, the code records the site visitor's status as logged in. If the member ID or password value doesn't exist in the SQL Server database, control passes to the application's sixth page, NotLoggedIn.htm. You can use an .htm file in an ASP.NET application when you don't need server-side code behind a page.
The Default.aspx page dynamically changes its content after a site visitor logs in. When the page initially opens in a Web session, the top LinkButton control prompts the user to log in, and the page offers a link to Login.aspx. After a successful login, the code behind the page changes the Text property for the top LinkButton control and removes its link to Login.aspx. Other pages in the application also adjust their displays in response to the interaction between user input and database contents.
Technology Overview
The architecture of this ASP.NET application rests on SQL Server technology. While the user interacts with the application through a browser, the code behind the Web pages reaches back through ASP.NET and IIS to stored procedures and views in the SqlMagMemberApp database. These database objects, in turn, depend on three interrelated tables that organize content about members.
The database diagram in Figure 2 shows the three tables containing the information that the SqlMagMemberApp database tracks. The core table, IDInfo, contains columns for membership ID values (MemberID) and passwords (MemberPW). The two other database tables, ContactInfo and ExpiryDates, relate to the IDInfo table through the foreign keys on MemberID column values. The ContactInfo table stores contact information such as email addresses and phone numbers. The application uses the smalldatetime column values from the ExpiryDates table to disable functionality for membership accounts that have expired.
Aside from these tables, the SqlMagMemberApp database contains four stored procedures and one view to implement the application functions. The IsValidMember stored procedure assesses whether the member ID and password values that users enter on a Web page exist in the IDInfo table. The ASP.NET code that invokes IsValidMember accepts a return status value from the stored procedure and responds differentially based on that value. The EditMyPassword stored procedure lets a user update the password associated with that user's MemberID column value. The ShowMyRow and EditMyRow stored procedures respectively return and update the column values for the row in the ContactInfo table that corresponds to the MemberID for the site visitor in the current Web session. The CurrentMembers view returns rows for all members whose ExpiryDate column value is equal to or greater than the current date. This expiration date rule applies to the stored procedures as well, so for example, the IsValidMember stored procedure doesn't return the status value for a member if the expiration date for the member has passed.
Prev. page
[1]
2
3
next page 
