SQL Server and .NET: A Dynamic Duo

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.

Exploring the Database Objects

The ASP.NET application requires users to log in before they can do anything except navigate to Default.aspx. The initial navigation to this page in a Web session presents a link to Login.aspx, which calls the IsValidMember stored procedure. Later in the article, I show you how to write the VB.NET code to invoke the stored procedure and process its return status value. But first, let's see how the database objects function.

Listing 1 shows the T-SQL code for the IsValidMember stored procedure. In the case of a successful login, the @inID and @inpassword parameters designate MemberID and MemberPW column values for a row in the IDInfo table. With one exception, the stored procedure returns a value of 1 when the @inID and @inpassword parameters appear as column values for a row in IDInfo. Otherwise (i.e., if the parameters aren't column values in IDInfo), the IsValidMember stored procedure returns 0. Even if the @inID and @inpassword parameters match the column values in an IDInfo row, the return status value can still be 0 if the ExpiryDate column value for the row's MemberID value in the ExpiryDates table is before today's date.

To implement its logic, the IsValidMember stored procedure uses a SELECT statement with an inner join between the IDInfo and ExpiryDates tables. The WHERE clause arguments test for the required matching conditions. An IF statement transfers control to the final statement, which returns a value of 1 (logged in) or 0 (not logged in).

The code behind Login.aspx doesn't rely on T-SQL syntax for invoking the IsValidMember stored procedure. Nevertheless, when debugging ASP.NET applications that call stored procedures, you might want to test a stored procedure in Query Analyzer to confirm its operation. The following code shows one approach to testing the stored procedure:

DECLARE @return_status intExec @return_status = IsValidMember 'HN1', 'HDoe1'SELECT @return_status AS 'Return Status'

The value of @return_status will be 1 whenever HN1 and HDoe1 are the MemberID and password values in a row in the IDInfo table.

The CurrentMembers view uses the same kind of SELECT statement as the IsValidMember stored procedure uses. In particular, both SELECT statements join the ExpiryDates table with another table, and the WHERE clause includes a criterion based on a comparison between the ExpiryDate column value and today's date. One distinction between the view and the stored procedure is that the view returns from the ContactInfo table a result set containing column values for FirstName, LastName, EmailAddr, and Phone. With the view's result set, you can populate a DataGrid control that shows the contact information for all current members.

Besides validating membership status, the SqlMagMemberApp application lets users modify their passwords and personal contact data. The EditMyPassword stored procedure in Listing 2 shows the T-SQL syntax that enables user-password updates. The procedure takes two input parameters. The @inID parameter designates the MemberID column value from the IDInfo table whose MemberPW column value should change. The @inpassword parameter specifies the new password. An UPDATE statement assigns the new password value. Although the stored procedure uses qualifiers (ii and ed) that denote IDInfo and ExpiryDates, respectively, for expressions in the ON and WHERE clauses, a qualifier is inappropriate for the UPDATE statement's SET clause because the column names in the SET clause must be from the table you list after the UPDATE keyword.

The ShowMyRow and EditMyRow stored procedures work together in the ASP.NET application. The ShowMyRow stored procedure provides the data to populate text boxes that show contact data for the member identified in the current ASP.NET session. The EditMyRow stored procedure takes input parameters, which the application supplies from text boxes, and updates a row in the ContactInfo table with a MemberID column value that matches the current session's member.

Callout A in Listing 3 shows the T-SQL code for the ShowMyRow stored procedure. This code has the same FROM and WHERE clause expressions as the EditMyRow stored procedure that appears at callout B of Listing 3. However, the ShowMyRow stored procedure uses a SELECT statement to return a row, while the EditMyRow stored procedure uses an UPDATE statement to modify a row. The parameter list for EditMyRow assigns a default value of NULL to @inPh if the calling routine for the stored procedure omits this argument. The sample ASP.NET application takes advantage of this feature to assign a NULL to the Phone column value when the Phone text box on the Web form page is an empty text string (""), which isn't the same as a NULL.

Reviewing the ASP.NET Code

One strength of Web applications is the ease with which users can navigate between pages. ASP.NET applications can facilitate this navigation by using hyperlinks on .htm or .html pages, but they can also programmatically control navigation between pages through the LinkButton control. On an .aspx page, this control looks like a hyperlink, but it programs like a command button in VB.NET. When a user clicks a button, a Click event for the LinkButton control sends the page from the browser session on the user's workstation to the Web server. When the page arrives at the Web server, the Page_Load event fires. ASP.NET handles the Page_Load event before control-associated events that send a page to the Web server. Therefore, you can handle a Click event for a control by using either a Page_Load event procedure or the Click event procedure.

The Default.aspx page uses the LinkButton control, its Click event, and the Page_Load event to control navigation from this default Web page. Listing 4 contains the event procedures behind the Default.aspx page. You can use Visual Studio .NET 2003 to view this code in Default.aspx.vb. You can open Default.aspx.vb from Default.aspx in the Visual Studio IDE by choosing View, Code. Callout A in Listing 4 shows the Page_Load event procedure, which performs tasks associated with the Click event for the LinkButton control (hylLogin) initially pointing at the Login.aspx page. If the value of a session variable named LoggedIn is False, the procedure configures hylLogin to point at Login.aspx. If the variable is True, the procedure removes the link from hylLogin and changes the text for the control to say that the user is already logged in.

The other three procedures in Listing 4 handle Click event procedures for three other LinkButton controls on Default.aspx. Callout B shows conditional navigation to a standard HTML Web page, Page1OfContent.htm. This type of Web page has no ASP.NET code behind it. If the user isn't logged in when clicking the link for Page 1's content, control transfers to Login.aspx to let the user log in. This redirection blocks people attempting to view Page1OfContent.htm from Default.aspx without first identifying themselves as group members. You can use alternative techniques for managing secure access to Web pages. For example, by placing the content from Page1OfContent.htm on an .aspx page, you can place code behind the page to restrict its viewing to visitors who are already logged in.

Callout C in Listing 4 shows the Click event procedures for the CurrentMembers.aspx and EditMyMemberInfo.aspx pages. These two procedures unconditionally redirect control to another Web page (which one depends on the procedure). These pages have built-in checks to verify that the user is logged in.

The Login.aspx page has two text boxes in which a user can enter ID and password values that the application compares to MemberID and MemberPW column values in the IDInfo table and a button to launch a search for the values entered in the text boxes. Listing 5 includes an excerpt of the code behind Login.aspx. The excluded content, denoted by vertical ellipses, is a section that declares and assigns values to parameters of the ADO.NET SqlCommand object cmd1. Callout A shows the ASP.NET code for opening a connection to the SqlMagMemberApp database. When running the application in your environment, change the Data Source argument in str1 so that it points at the SQL Server instance in your environment that contains the SqlMagMemberApp database. When I run this application for a group that I manage, the Data Source parameter points to an IP address for the SQL Server that contains the membership data.

Callout B in Listing 5 shows the syntax for pointing a SqlCommand object at the IsValidMember stored procedure and invoking the command object. (For more information about using the SqlCommand object, see Michael Otey's article "ADO.NET 101: SqlCommand," page 25.) The code uses the ExecuteNonQuery method for the cmd1 object because the IsValidMember stored procedure doesn't return a result set. Callout C shows the code for processing the return status value from the IsValidMember stored procedure. The @OK parameter represents the return status value. If the parameter is 1, the code sets the LoggedIn session variable to True; otherwise, the value of this session variable is False.

If a site visitor clicks the third LinkButton control on Default.aspx after logging in, the application presents the CurrentMembers.aspx page that Figure 3 shows. This page presents in a DataGrid control the list of current members in the SqlMagMemberApp database. The data comes from the CurrentMembers view in the SqlMagMemberApp database. Which members appear depends on the current date relative to the member expiration date. Figure 3 shows the DataGrid in a browser session that I ran for this article.

The code behind CurrentMembers.aspx starts by redirecting control to the NotLoggedIn.htm page if a visitor isn't already logged in; this redirection restricts the functionality on the current page to current members. Next, the code behind CurrentMembers.aspx forms a database connection based on the syntax at callout A in Listing 5. Listing 6 shows an excerpt of the code behind CurrentMembers.aspx. The code declares a SqlCommand object (cmd1) based on the CurrentMembers view and uses the SqlCommand object as the SelectCommand property setting for an ADO.NET SqlDataAdapter (dap1). This data adapter serves as a pump to populate the das1 DataSet that is the source for the DataGrid control in Figure 3.

Figure 4 shows the Design view of the EditMyMemberInfo.aspx page. The controls on this page show the MemberID and MemberPW column values for the current member and offer a text box for specifying a new password. Clicking Update Password revises the password on the form and in the SqlMagMemberApp database. A current member can update his or her contact data by revising the contents of any of the four text boxes under the Update Password button and clicking the Update Contact Info button.

Each button invokes a stored procedure that updates the SqlMagMemberApp database. A click of the Update Password button invokes the EditMyPassword stored procedure. Clicking the Update Contact Info button invokes the EditMyRow stored procedure in one of two ways. If the text box for the Phone number (txtPhone) is empty (""), the code invokes the EditMyRow stored procedure without passing a value for the @inPh parameter. This operation assigns a NULL value to the Phone column value for the row in the ContactInfo table. If txtPhone contains a nonempty string, the code uses that string as the value for @inPh when calling the EditMyRow procedure. Listing 7 shows the Click event procedure for the Update Contact Info button.

Using ASP.NET with SQL Server

As you can see, ASP.NET is much more powerful than ASP. This application, based on ASP.NET and SQL Server, illustrates how to perform two fundamental database tasks, data access and manipulation, with ASP.NET. With this application, you can manage the basic data access and manipulation tasks that you need to perform for any group membership application and many other applications. I hope this article's example can be a starting point for more advanced membership applications--or an ending point for those without the time or resources to create a more sophisticated application.