Database Storage with the ASP.NET Web Matrix

For those of you that haven’t read my article introducing the ASP.NET Web Matrix Project, it is a self-contained, fully functional, free application that brings almost everything a budding ASP.NET developer could want to the table.  It provides intelligent toolboxes and code wizards that can help you create the pages you want with a minimum of manual coding in a well designed and aesthetically pleasing environment.

This article aims to show just how easy it is to create dynamic pages that can write information to and retrieve information from databases.  One thing the Web Matrix doesn’t come with is an integrated database engine; however, you can download the Microsoft SQL Server Desktop Engine for free, which integrates extremely well with the Web Matrix. Or, if you have Microsoft Access on your machine, this is configured for use with the application during install.  For the purpose of this article, I’m assuming you’ve downloaded and installed the SQL Server described above.

Create a database and table

First then, you can create the database and table that will store the usernames and passwords of your visitors.  To do this, click on the Data tab on the right-hand side of the application and select the Add Database Connection button.  This will open the Add New Project dialogue box, where you can select the SQL Server database.  In the Connect to Database dialog box, click the link at the bottom entitled Create a new database.  You’ll be prompted to choose a name for the new database, so enter something relevant, like "members."  The connection will now show in the Data panel.

To add a table to the database, in which to actually store the information, expand the new connection and highlight the tables folder.  Click the New Database Object button and enter a name for the table.  Again, use a name that makes sense, such as "logins."  The table interface will now be displayed.  We’ll keep it basic for this exercise and just create two columns -- one to store the usernames, and one to store the associated passwords.  The first column has been created for you; highlight it and click into the Name field.  Enter Username and then click into the DataType field.  A pointer will appear, allowing you to expand the selection.  Choose char.  Now click into the Size field and set a limit of 20 characters.  Finally, scroll down to the bottom and set the IsUniqueKey option to true, specifying that the table cannot contain duplicate Usernames.

Click the New button to add another column, set the Name to Password and set the rest of the properties as above.

Click the cross button in the top right-hand corner of the table interface to close the window.  You will get a message advising that no primary key has been set, but choose to save the table anyway.  This means that you will not be able to edit the data in the table, but for this basic example, you don’t need to.  Your new table will now appear under the tables folder of the database connection.

Setting up a registration page

Now let’s set up a registration page that will let users enter their username and password into a form that will insert their details into the logins table.

Choose the New File button, highlight the ASP.NET Page template from the General Selection, and change the filename to something appropriate.  When the blank page loads, the Web Controls toolbox will be open by default.  From this toolbox, drag a label and a textbox onto the page.  Select the label, and in the properties pane on the right-hand side click into the Text value field.  Set the text to Username: and press return.

Now select the textbox, and this time in the Properties pane, select the ID field value and enter txtUsername.  Using a prefix denoting the control type of the element and a meaningful name are standard practice in object oriented programming.  On this type of page it really makes little difference what the working elements of the page are called, but if you created a page with twenty text boxes on it, how useful would the ID TextBox15 be?  With the textbox still selected, press the right arrow key on the keyboard and then press return to get to the next line.

Drag another label and textbox onto the page and set the Text and ID values to Password and txtPassword, then repeat the process once more and set the values to Verify Password: and txtPassword respectively.  It is standard practice on a registration page to check that the visitor knows for sure what the password is being set to.  When the two password boxes are different, you need to tell the visitor somehow without running the database INSERT function.  Add another label directly after the verify password text box and remove the default Text value.  Set the ID to lblPasswordError and chose a foreground colour of red.  This label will appear enclosed in square brackets on the design page to show that it has no text value.

Go to a new line again and drag two button controls onto the page; set the ID of the first button to btnSubmit and the Text to Submit.  Now set the ID to btnClear and the Text to Clear.

Coding the page

This is all the page needs visually, the rest will be code run behind the scenes.  Switch to the Code view using the tabs at the bottom of the document window.  The Code Wizards toolbox will appear.  From here, drag the INSERT Data Method onto the page to open the INSERT Data Code Wizard dialog box.  Your database, unless you have more than one configured connection, will appear in the Select a Database box, so click the next button.  Make sure your logins table is selected in the next window and simply click Next again (neither of the columns in the right-hand panel need to be ticked).  Now just choose a name for your method, like NewUser, and click Finish.

Your code page should now contain the block of code needed to write data to your table.  Before this can happen however, you need to define a way of calling the function and telling it what data to write.  Go back to the Design view of the document page and double click the submit button, which will create a new onclick event handler for the button, and add some more code to the Code view.

Switch back to the Code view and in between the new code that has been added type:

If txtPassword.Text <> txtPassword2.Text Then
      lblPasswordError.Text = "* Verify password must match
password"
Else
      NewUser(txtUsername.Text, txtPassword.Text)
End if

This is all that is needed to check that the two password fields match and if so, call the function and pass it the contents of each text box as a parameter.  When the password fields do not match, the text property of the label following the verify password box is set to the error message.  This needs to be done because, unfortunately, the MessageBox.Show VB.NET method does not work with ASP.NET.

To create functionality for the clear button, switch back to the Design view and double click the clear button.  In between the lines of new code added to the Code view, add:

txtUsername.Text = ""
txtPassword.Text = ""
txtPassword2.Text = ""

To test the page so far, click the play button at the top of the Web Matrix.  Assuming all is well, if you enter a username and password into the text boxes and click the submit button, the text entered will be written to your database table.  To verify this, go back to the Web Matrix and double click the table in the Data Pane. 

Once thing you should remind yourself of at this point is that we set the IsUniqueKey of the username column of our data table to true, therefore, if you try to enter the same username on the form twice, you’ll get a nasty looking error page.  Users should not be subject to this kind of abuse, so you’re going to want something that will present a proper error message to the user.  You can use the same display method for the error as the verify password message above.

Error message

Go back to the Web Matrix and drag another label onto the page so that it sits next to the Username textbox.  Make the text red and remove the Text value once again, and this time, set the ID to lblUsernameError.

Now in order to catch the exception thrown by SQL when a pre-existing value is passed to the table, you just need to add two lines of code to the INSERT function.  Near the bottom of the function is the Try structure that attempts to write to the database.  Add the following statement directly below the rowsAffected = dbCommand.ExecuteNonQuery:

Catch e as Exception
   lblUsernameError.Text = "* This username has already been
taken"

Save the file and run it again, attempting to add a duplicate username.  Your red error message should be displayed on the page. 

Once either of these messages has been displayed, they remain on the page even after a refresh.  A simple way to stop this is to add the following code to the top of the btnSubmit_Click sub:

lblUsernameError.Text = ""
lblPasswordError.Text = ""

Now if the problem is corrected by the user, and the form is resubmitted successfully, the red error messages disappear.  For the sake of completeness, you can also add at this point a confirmation message when the operation is a success.  In the Design view of the Web Matrix, drag a final label to below the two buttons; set the fore color to green this time and the ID to lblSuccess.  Remove the Text value.

Now head back to the INSERT function and find the Finally statement of the Try code block.  Below the connection close call, add this method:

lblSuccess.Text = "Thank you for registering, yada, yada, yada"

Run the page again and a successful submission will result in the green message.

Password security

A security measure often used when setting passwords is to enforce a minimum password length as the longer a password is, the longer it will take to crack it.  Minimum length password enforcing can easily be worked into the existing page with just one more label and an extension of the If control structure on the code page. 

In design view, drag a label control next to the first password box, set the ID to lblPasswordLengthError, the fore color to red and the Text value to nothing.  Switch to the Code view and change the btnSubmit_Click sub as follows:

Sub btnSubmit_Click(sender As Object, e As EventArgs)
    lblUsernameError.Text = ""
    lblPasswordError.Text = ""
    lblPasswordLengthError.Text = ""

    if txtPassword.Text.Length < 7 then
      lblPasswordLengthError.Text = "* Your password is not secure, please ensure
it is 7 characters or more"
    Else If txtPassword.Text <> txtPassword2.Text Then
      lblPasswordError.Text = "* Verify password must match password"
    Else
      NewUser(txtUsername.Text, txtPassword.Text)
    End if
End Sub    

Now passwords will need to be seven characters or more before the database will accept them. 

It would be nice if we lived in a world where clear-text passwords could be stored safely in a database.  Unfortunately however, we don’t.  Using a hashing algorithm to encrypt the password before storing it in the database table is a common method of secure password storage.  This is not unbeatable however, as a hacker may still be able to brute-force a hashed password if they can find this out by exploiting the database somehow.  This is much more time consuming and will be an effective deterrent. 

A minor change must be made to the Code view.  You need to define a hashing method and write a method that will convert the contents of the password textbox into the hash before storing it.  Change the btnSubmit_Click sub to the following:

Sub btnSubmit_Click(sender As Object, e As EventArgs)

    lblUsernameError.Text = ""
    lblPasswordError.Text = ""
    lblPasswordLengthError.Text = ""

    if txtPassword.Text.Length < 7 then
      lblPasswordLengthError.Text = "* Your password is not
secure, please ensure it is 7 characters or more"
    else if txtPassword.Text <> txtPassword2.Text Then
      lblPasswordError.Text = "* Verify password must match
password"
    Else
      Dim hashMethod as string
      hashMethod = "MD5"
      Dim encryptPassword as string
      encryptPassword = FormsAuthentication.HashPasswordForStoringInConfigFile
(txtPassword.Text, hashMethod)
     

      NewUser(txtUsername.Text, encryptPassword)
    End if

End Sub

The finished Design view should now appear like this:

Test the page and enter a username and password; now go to the Data Pane in the Web Matrix and view the table.  The entered password show now be encrypted.  If this failed, you may have forgotten to increase the maximum size of the Password column in the table.

So there you go, using the Web Matrix as your IDE can result in clean code and increased productivity.  If you’re not using it already, maybe it’s something you should consider.