| To add this capability to your application, you need to do two things: update your database (add new tables, modify existing ones, and create a few stored procedures) and build the capability into your ASP.NET pages. You already have a table in the database to hold user information, but your banking application requires a bit more than that. Specifically, you'll need to add a table to hold data for online transactions and one for account information. Next you'll add stored procedures to handle interactions with the database, giving you better performance than if you didn't use stored procedures. Finally, you'll update the .aspx files you created in Bonus Project 1, adding data capabilities to all three main pages (the user controls will stay the same). The Database The first thing you need to do is to build the database tables. You already have one, the users table, started in Day 8, "Beginning to Build Databases." You'll have to modify that one slightly, and then build a few new tables. Open the Banking database in Microsoft Access and open the tblUsers table in design mode. You need to add a password column so that users' identities can be verified when they try to log in to the application. You also need to add a username column so that users can have their own unique username when logging in this value must be unique. Add the two new columns to the Design view grid, and assign Text data types to both. Note Creating these two new columns will append a new field to any existing records with the value of Null. You'll have to manually enter values for each row; otherwise, those users would never be able to log in!
Next, you need to add two more vital tables to the database: one to keep track of transactions and one to keep track of account information (that is, balances). Create the first table in Design view and add the following columns: TransactionID An autonumber data type column to uniquely identify each transaction. Make this the primary key by right-clicking on this column and selecting Primary Key. DatePosted A date/time value to hold dates. Amount A currency data type to hold transaction amounts (can be negative). Payee A text column representing who the payment was made to. CheckNumber An optional number data type column to hold check numbers. UserID A number column used to link transactions with users. Save this table as tblTransactions. You now have to create a relationship between the UserID field in this table with the UserID in tblUsers. Select the Relationships item under the Tools menu. You'll see a box asking which tables you want to relate. Add both tables and you should see something similar to Figure BP2.1. Close the dialog box. Figure BP2.1. Adding relationships between tables.
Scroll through the table windows until you can see the UserID field in both. Now click on one, drag the mouse cursor to the other, and release. You should see another dialog box confirming the field names to relate. If you don't see UserID in both columns, change them so you do. Click Create. You should now see a line between the two table windows, linking the UserID fields. Close the Relationships window and save it. One table down, one to go. Create a new table in Design view with the following columns: AccountID An autonumber data type to uniquely identify each account. Make this the primary key. Balance A currency value to hold amount of money in the account. UserID A number column used to link transactions with users. Save this table as tblAccounts, and create a relationship with the tblUsers table just as you did with the tblTransactions table. Link the UserID columns. Caution Make sure that you link the tblAccounts table with the tblUsers table, and not the tblTransactions table. The application will work either way, but you want to create a true relational database by relating the correct fields. The database will be much easier to follow as well.
Next, you need to create a few simple stored procedures. Recall that stored procedures are called queries in Access. Move to the query tab and create a new procedure in the manner described in Day 14, "Using ASP.NET's Improved Caching Capabilities." Place the SQL statement in Listing BP2.1 in the query. Listing BP2.1 Returning Account Balances 1: SELECT Balance FROM tblAccounts WHERE 2: UserID = @UserID This query will be used to return the account balance when supplied with a valid UserID. Save this query as spRetrieveBalance. Next, you need to create a query that inserts a transaction into the database. Use Listing BP2.2 to do so. Listing BP2.2 Inserting Transactions 1: INSERT INTO tblTransactions (DatePosted, Amount, Payee, 2: UserID) 3: VALUES 4: (@Date, @Amount, @Payee, @UserID) Save this procedure as spInsertTransaction. Note that the check number field is left out of this statement. Because the procedure is used for online transactions, a check number is not applicable; therefore, it is left out of the procedure. Access will insert the check number automatically as its default value (which should be an empty string). You need to add a stored procedure that will validate a user's identity when supplied with the correct user name and password. Listing BP2.3 shows the code for this query. Listing BP2.3 Validating a User 1: SELECT UserID FROM tblUsers 2: WHERE UserName = @UserName 3: AND Password = @Password Save this as spValidateUser. You need another procedure that updates the account balance (when a transaction is made online). Listing BP2.4 shows this code. Save it as spUpdateBalance. Listing BP2.4 Gathering Transactions 1: UPDATE tblAccounts SET Balance = @NewBalance 2: WHERE UserID = @UserID Finally, you need one more procedure that returns all the transactions for a given user. Listing BP2.5 shows this code. Listing BP2.5 Gathering Transactions 1: SELECT TransactionID, DatePosted, Amount, Payee, 2: CheckNumber, UserID 3: FROM tblTransactions 4: WHERE UserID = @UserID Save this procedure as spGetTransactions. That's all you need, so move on to the ASP.NET pages. The ASP.NET Pages You should still have the account.aspx, bills.aspx, and login.aspx pages, as well as the various user controls you created in the previous bonus project. You're going to have to do a few things with these pages. First, you need to modify the login page so that users will be identified by the entries in the database, rather than by the hard-coded values you used last time. Then you need to update the account summary page to pull the account balance and transactions from the database. Both these values are read-only, so the users can't modify them here. Finally, update the bill payment page to allow users to enter transactions. This page will update the database in two ways: update the account balance and add a new transaction into the database. All the database operations described here will be handled with the stored procedures you created earlier. Figure BP2.2 shows a diagram of what your final application should look like. Figure BP2.2. The banking application site diagram.
First, modify login.aspx to allow user authentication. This page will handle everything in one method the event handler for the Submit button. Retrieving the values from the username and password text boxes, it will execute the spValidateUser stored procedure to determine whether you have a valid user, and then redirect or advise accordingly. Because the HTML portion of this page remains the same as the last bonus project, Listing BP2.6 only shows the code declaration block. Listing BP2.6 The Login Method 1: <%@ Page Language="VB" %> 2: <%@ Register TagPrefix="ASPNETBank" TagName="Header" src="/books/4/226/1/html/2/header.ascx" %> 3: <%@ Import Namespace="System.Data" %> 4: <%@ Import Namespace="System.Data.OleDb" %> 5: 6: <script runat="server"> 7: '******************************************************* 8: ' 9: ' Login.aspx: Logs users in 10: ' 11: '****************************************************** 12: 13: '****************************************************** 14: ' When user clicks submit button, verify that they are a 15: ' valid user. If they are, log them in, and set a cookie 16: ' with their user name, and redirect to account.aspx. 17: ' Otherwise display error message 18: '****************************************************** 19: sub Login(Sender as Object, e as EventArgs) 20: dim intId as integer = 0 21: dim Conn as new OleDbConnection("Provider=" & _ 22: "Microsoft.Jet.OLEDB.4.0;" & _ 23: "Data Source=c:\ASPNET\data\banking.mdb") 24: 25: dim objCmd as OleDbCommand = new OleDbCommand _ 26: ("spValidateUser", Conn) 27: objCmd.CommandType = CommandType.StoredProcedure 28: 29: 'set parameters for stored procedure 30: dim objParam as OleDbParameter 31: objParam = objCmd.Parameters.Add("@UserName", _ 32: OleDbType.BSTR) 33: objParam.Value = tbUserName.Text 34: 35: objParam = objCmd.Parameters.Add("@Password", _ 36: OleDbType.BSTR) 37: objParam.Value = tbPassword.Text 38: 39: try 40: objCmd.Connection.Open 41: intID = CType(objCmd.ExecuteScalar, Integer) 42: objCmd.Connection.Close 43: catch ex as OleDbException 44: lblMessage.Text = ex.Message 45: end try 46: 47: if intID <> 0 then 48: Response.Cookies("Account").Value = intID 49: Response.redirect("account.aspx") 50: else 51: lblMessage.Text = "<font color=red>Sorry, " & _ 52: "invalid username or password!</font><p>" 53: end if 54: end sub 55: </script>  | The first few lines are straightforward. You register your header user control, which displays a common header for all pages, on line 2, and then import a few namespaces on lines 3 and 4. The Login method performs all necessary functions. You instantiate OleDbConnection, OleDbCommand, and OleDbParameters on lines 21 30. You then set the parameter values to the values entered in the username and password text boxes (not shown here). |
In the try block, you open and execute your query. If the user is valid, you'll be able to retrieve her user ID from the data reader with the ExecuteScalar method. Otherwise, intId will remain at its initial value, 0. The last bit of code on lines 47 53 performs the actual validation of the user's identity. If the variable intId has a value, this user is valid; you use the Request.Cookie object to store the account ID, and redirect to the account page. If intId is 0, the credentials were invalid, so an error message is displayed. Next, you need to update account.aspx to display the dynamic data from the database. First, you want to replace the static account balance from the old project with the actual value from the database. Then you need to display the transactions for the current user in a DataGrid. There is nothing really new here you should be familiar with all these techniques. Note Before this page will display any data, you must enter values such as account balances and any transactions directly in the database.
Again, because the UI hasn't changed much, Listing BP2.7 displays only the code declaration block. Listing BP2.7 Account.aspx Displaying Account Balances and Transactions 1: <%@ Page language="VB" %> 2: <%@ Register TagPrefix="ASPNETBank" TagName="Header" src="/books/4/226/1/html/2/header.ascx" %> 3: <%@ Register TagPrefix="ASPNETBank" TagName="Menu" src="/books/4/226/1/html/2/nav.ascx" %> 4: <%@ Import Namespace="System.Data" %> 5: <%@ Import Namespace="System.Data.OleDb" %> 6: 7: <script runat="server"> 8: 9: '******************************************************* 10: ' 11: ' Account.aspx: lists account summaries and current 12: ' balances for the current user 13: ' 14: '****************************************************** 15: 16: 'declare connection object 17: dim Conn as new OleDbConnection("Provider=" & _ 18: "Microsoft.Jet.OLEDB.4.0;" & _ 19: "Data Source=C:\ASPNET\data\banking.mdb") 20: 21: '****************************************************** 22: ' When the page loads, display a welcome message in the 23: ' and the current balance of the account, as well as 24: ' any transactions 25: '****************************************************** 26: sub Page_Load(Sender as Object, e as EventArgs) 27: GetBalance() 28: GetTransactions() 29: end sub 30: 31: '****************************************************** 32: ' This function returns the balance of the account with 33: ' the specified user ID (stored in the cookie created 34: ' by forms authentication) and displays the value 35: ' in a label 36: '****************************************************** 37: sub GetBalance() 38: dim decBalance as decimal 39: dim objCmd as OleDbCommand = new OleDbCommand _ 40: ("spRetrieveBalance", Conn) 41: objCmd.CommandType = CommandType.StoredProcedure 42: 43: 'set parameters for stored procedure 44: dim objParam as OleDbParameter 45: objParam = objCmd.Parameters.Add("@UserID", _ 46: OleDbType.BSTR) 47: objParam.Direction = ParameterDirection.Input 48: objParam.Value = Request.Cookies("Account").Value 49: 50: try 51: objCmd.Connection.Open() 52: decBalance = CType(objCmd.ExecuteScalar, Decimal) 53: objCmd.Connection.Close 54: catch ex as OleDbException 55: lblMessage.Text = ex.Message 56: end try 57: 58: lblBalance.Text = "<b>$" & decBalance.ToString & _ 59: "</b>" 60: end sub61: 62: '****************************************************** 63: ' Returns all transactions for a given user id (stored 64: ' in the cookie created by forms authentication), and 65: ' displays them in a datagrid 66: '****************************************************** 67: sub GetTransactions() 68: dim objCmd as OleDbCommand = new OleDbCommand _ 69: ("spGetTransactions", Conn) 70: objCmd.CommandType = CommandType.StoredProcedure 71: dim objReader as OleDbDataReader 72: 73: 'set parameters for stored procedure 74: dim objParam as OleDbParameter 75: objParam = objCmd.Parameters.Add("@UserID", _ 76: OleDbType.BSTR) 77: objParam.Direction = ParameterDirection.Input 78: objParam.Value = Request.Cookies("Account").Value 79: 80: try 81: objCmd.Connection.Open() 82: objReader = objCmd.ExecuteReader 83: 84: dgTransactions.DataSource = objReader 85: dgTransactions.DataBind() 86: catch ex as OleDbException 87: lblMessage.Text = ex.Message 88: end try 89: end sub 90: </script> | That's quite a bit of code! Luckily, most of it is simple and easy to write. The Page Load event on line 26 simply calls the two other methods in this listing, GetBalance and GetTransactions. |
GetBalance, beginning on line 37, executes the spRetrieveBalance stored procedure, passing in the UserID parameter you stored in a cookie when authenticating the user. This is specified on line 48 by Request.Cookies("Account").Value. On line 52, you execute the query and retrieve the balance as a decimal data type. The balance is displayed on line 58. The GetTransactions method is similar, except that the spGetTransactions stored procedure is executed, which returns all the transactions for a given user. Again, the code uses the ID stored in your authentication cookie, as shown on line 78. After you execute the procedure on line 82, the data is bound to a DataGrid. There wasn't much new with this listing. You've already learned how to execute stored procedures and pass in parameters, so you should feel comfortable doing this. Figure BP2.3 shows an example output from this listing. Figure BP2.3. The account balance and transactions are displayed for the current user.
Finally, you must update the bills.aspx page to allow users to enter new transactions (in other words, to pay bills online). When a user enters a transaction, you'll check whether he has sufficient funds, subtract the amount from his balance if he does, and update the transactions table. Note that you're allowing only payments and not credits. Banking hasn't evolved to the point yet where they trust us to specify our own deposits online! This page should be fairly simple executing stored procedures and supplying parameters as you're used to. You do have to perform a few additional checks to make sure that the amount the user enters is valid. Listing BP2.8 shows the code from this page. Listing BP2.8 bills.aspx Entering Transactions 1: <%@ Page language="Vb" %> 2: <%@ Register TagPrefix="ASPNETBank" TagName="Header" src="/books/4/226/1/html/2/header.ascx" %> 3: <%@ Register TagPrefix="ASPNETBank" TagName="Menu" src="/books/4/226/1/html/2/nav.ascx" %> 4: <%@ Import Namespace="System.Data" %> 5: <%@ Import Namespace="System.Data.OleDb" %> 6: 7: <script runat="server"> 8: 9: '******************************************************* 10: ' 11: ' Bills.aspx: Allows user to pay bills online 12: ' 13: '******************************************************* 14: 'declare connection object 15: dim Conn as new OleDbConnection("Provider=" & _ 16: "Microsoft.Jet.OLEDB.4.0;" & _ 17: "Data Source=C:\ASPNET\data\banking.mdb") 18: 19: '******************************************************* 20: ' Event hander for submit button. Determines if amount 21: ' is valid, and if so, calls necessary functions to 22: ' update balance and add transaction 23: '******************************************************* 24: sub PayBill(Sender as Object, e as EventArgs) 25: dim decBalance as Decimal = GetBalance 26: dim decAmount as Decimal = tbAmount.Text 27: 28: if decAmount < 0 then 29: lblMessage.Text = "<font color=red>The " & _ 30: "transaction amount cannot be negative!" & _ 31: "</font>" 32: exit sub 33: end if 34: 35: if decAmount <= decBalance then 36: UpdateBalance(decBalance - decAmount) 37: AddTransaction(tbPayee.Text, decAmount) 38: 39: lblMessage.Text = "<font color=red>Transaction " & _ 40: "added.</font>" 41: tbAmount.Text = "" 42: tbPayee.Text = "" 43: else 44: lblMessage.Text = "<font color=red>You do not " & _ 45: "have enough funds to complete this " & _ 46: "transaction!</font>" 47: end if 48: end sub 49: 50: '******************************************************* 51: ' This function returns the balance of the account with 52: ' the specified user ID (stored in the cookie created 53: ' by forms authentication) 54: '******************************************************* 55: function GetBalance as Decimal 56: dim decBalance as decimal 57: dim objCmd as OleDbCommand = new OleDbCommand _ 58: ("spRetrieveBalance", Conn) 59: objCmd.CommandType = CommandType.StoredProcedure 60: dim objReader as OleDbDataReader 61: 62: 'set parameters for stored procedure 63: dim objParam as OleDbParameter 64: objParam = objCmd.Parameters.Add("@UserID", _ 65: OleDbType.BSTR) 66: objParam.Direction = ParameterDirection.Input 67: objParam.Value = Request.Cookies("Account").Value 68: 69: try 70: objCmd.Connection.Open() 71: decBalance = CType(objCmd.ExecuteScalar, Decimal) 72: objCmd.Connection.Close() 73: catch ex as OleDbException 74: lblMessage.Text = ex.Message 75: end try 76: 77: return decBalance 78: end function 79: 80: '******************************************************* 81: ' Adds a transaction into the database, calling 82: ' spInsertTransaction and specifying amount, payee, and 83: ' user id 84: '******************************************************* 85: sub AddTransaction(strPayee as string, decAmount _ 86: as Decimal) 87: dim objCmd as OleDbCommand = new OleDbCommand _ 88: ("spInsertTransaction", Conn) 89: objCmd.CommandType = CommandType.StoredProcedure 90: 91: 'set parameters for stored procedure 92: dim objParam as OleDbParameter 93: objParam = objCmd.Parameters.Add("@Date", _ 94: OleDbType.Date) 95: objParam.Direction = ParameterDirection.Input 96: objParam.Value = Datetime.Now 97: 98: objParam = objCmd.Parameters.Add("@Amount", _ 99: OleDbType.Decimal) 100: objParam.Direction = ParameterDirection.Input 101: objParam.Value = decAmount 102: 103: objParam = objCmd.Parameters.Add("@Payee", _ 104: OleDbType.BSTR) 105: objParam.Direction = ParameterDirection.Input 106: objParam.Value = strPayee 107: 108: objParam = objCmd.Parameters.Add("@UserID", _ 109: OleDbType.BSTR) 110: objParam.Direction = ParameterDirection.Input 111: objParam.Value = Request.Cookies("Account").Value 112: 113: try 114: objCmd.Connection.Open() 115: objCmd.ExecuteNonQuery 116: catch ex as OleDbException 117: lblMessage.Text = ex.Message 118: finally 119: objCmd.Connection.Close() 120: end try 121: end sub 122: 123: '******************************************************* 124: ' Updates the account balance 125: ' calls spUpdateBalance 126: '******************************************************* 127: sub UpdateBalance(decNewAmount as Decimal) 128: dim objCmd as OleDbCommand = new OleDbCommand _ 129: ("spUpdateBalance", Conn) 130: objCmd.CommandType = CommandType.StoredProcedure 131: 132: 'set parameters for stored procedure 133: dim objParam as OleDbParameter 134: objParam = objCmd.Parameters.Add("@NewBalance", _ 135: OleDbType.Decimal) 136: objParam.Direction = ParameterDirection.Input 137: objParam.Value = decNewAmount 138: 139: objParam = objCmd.Parameters.Add("@UserID", _ 140: OleDbType.BSTR) 141: objParam.Direction = ParameterDirection.Input 142: objParam.Value = Request.Cookies("Account").Value 143: 144: try 145: objCmd.Connection.Open() 146: objCmd.ExecuteNonQuery 147: catch ex as OleDbException 148: lblMessage.Text = ex.Message 149: finally 150: objCmd.Connection.Close() 151: end try 152: end sub 153: </script> | The PayBill method on line 24 is the event handler for the click event of the Submit button. It grabs the information from the text boxes, verifies that the amount is not negative and is not greater than the balance, and calls the appropriate methods to update the balance and add the transaction to the database. |
Following that is the GetBalance function on line 55, which is the same as the one for account.aspx, except that instead of printing the balance to the page, it returns the balance as a decimal value. The AddTransaction method on line 85 executes the spInsertTransaction stored procedure and supplies the appropriate values as parameters. Finally, the UpdateBalance method executes the spUpdateBalance stored procedure. Enter a transaction in this page, and then go back to the account summary page. You should now see a new transaction in the DataGrid, as well as a new balance. | 
