Creating an Audit Record Application with ASP.NET | 3

[next]

Often times in development, you need the ability to store audit transactions within an application. Reasons for this are varied and include: health care related applications use it to determine who viewed patient data, which help the product meet government regulations. When businesses use applications that have multiple users updating, recording or deleting sensitive information generally want auditing procedures in place to prevent fraud or inaccurate information from entering the system. Bank applications use it to audit balances recorded by accountants. Obviously, having the ability to keep audits of these transactions becomes very helpful over the course of time.

In the following article, we'll learn how to implement audit recording through an ASP.NET application. Specifically, we'll learn: how to effectively record the audit from a table in SQL, how to record the date and time, as well as the user who audited the information. If you would like to learn how to create and implement an application such as this, please do follow along.

Download Source Code

Readers can follow along or implement the application by downloading the source code for the application.

Requirements

To follow along, you will need access to the following software/tools:

Microsoft Visual Studio 2008
Microsoft SQL Server 2008

Create the Database from the Backup

Open SQL Server: Start→Programs→Microsoft SQL Server 2008, and connect to your database server, whether it's local or remote. After connecting to your database, create a table by following these steps:

Right click on Databases and select New Database.
In the New Database window, type mwd_RecordHistory in the database name text field and then click OK.
Right click on your database and select Tasks→Restore→Database.
In the restore database window, select From device as shown below:

Click here for larger image
Figure 1. Be Sure to Click From device Caption
Left click the ellipses as shown below:

Click here for larger image
Figure 2. Left Click the Ellipses
In specify backup back up, left click the Add button.
Select mwd_RecordHistory.bak.
Click OK.
Click OK again.
Left click the check box for the backup you just selected in Step 2.
From the restore database window, left click Options on the left side.
Left click the first check box to overwrite the existing database and click OK.
From the restore database window, left click OK.

Take a few minutes and explore the database table RecordHistory. If you open the table, you'll notice the following:

Two tables, RecordHistory and RecordHistory_Audit.
Three stored procedures:

spRecordHistoryShowGrid
spRecordHistoryUpdate
spRecordHistoryViewEmployee

A few notes regarding our tables: the first one retains original records, while the second stores our audits.

A Note About Stored Procedures

In databases, you can think of them as blocks of SQL code that have been executed against the database, in other words, cached. This is great for multiple reasons including:

Because the SQL code is executed against the database, it's already indexed on the database and knows the tables it needs, which means performance is enhanced.
Because we're calling the function in your application, you aren't executing a SQL query from your application, which is faster than embedding SQL.
If you need to change the query, you change it in the saved SQL file rather than in the business layer.

Create the Record History Application with Visual Studio

Open Visual Studio (Start→Programs→Microsoft Visual Studio 2008) and from the main menu, follow these steps:

File→New→Project
In the New Projectswindow, left click Web on the left side and left click ASP.NET Web Application on the right side.
In the name text field, type RecordHistory.
In the location text field, choose a directory and click OK.

Modify the web.config File

From the solution project, you need to modify your configuration file for your database connection. Double click web.config to open the file, and then add the following right after <appSettings/>:

<connectionStrings>  
<add name="mwd" connectionString="Data Source=A12B3TM1;Initial Catalog=mwd_RecordHistory;Integrated Security=SSPI;"/>  
 </connectionStrings>

As you can see from the code above, you added a connection string with a key named mwd, followed by your connection details. Modify this with your own settings. When you're finished, save the file.

Create a Classes and Style Folder

From the solution, let's create our classes' folder as an example:

Right click on the solution and select Add→New Folder.
Type classes and press enter.

From the classes' folder, create two additional files by following these steps:

Right click on the folder, and select Add→New Item.
In Add New Item window, select Class file from the right side.
In the name text field, type RecordHistoryData and click OK.

Repeat this process, but when you reach the last step, type RecordHistoryDataActions in the name text field. You will now have two classes, RecordHistoryData and RecordHistoryDataActions.

Before finishing, create a folder for style. For your style sheet, follow these steps:

From the solution, right click and select Add→New Item.
In the New Item window, select stylesheet from the right side.

In the name text field, type style.css and click OK.

Double click your style.css from the solution explorer and add the code from the style.css file in the project downloads. As you can see from the code archive, you added the following: a container for your form, and basic positioning for your labels. Save your style sheet before moving forward.

Create Default.aspx and Recordaudit.aspx

From the solution explorer, create two additional files by following these steps:

Right click on the folder, and select Add→New Item.
In Add New Item window, select Web Form from the right side.
In the name text field, type default.aspx and click OK.

Repeat this process, but when you reach the last step, type recordaudit.aspx in the name text field. You will now have two web forms, default.aspx and recordaudit.aspx.

Working with Default.aspx

Our default page, serves as our home page, which lists all employees that are available. From looking at the project downloads, you will notice the following:

An ASPX page with a reference to a style sheet
A layout container that contains a repeater control, which holds a hyperlink server control

It should be noted, the important to the link is the NavigateUrl attribute. We first pass in our column from our database table, GUID (also our class level variable), followed by a link to our recordaudit.aspx page, and append on a query string of guid, followed by a bracket that serve to parse through each GUID available from our database table.

This file works with RecordHistoryData.cs to display employee names, which we'll cover next.

Save your file(s).

Displaying Employee Names

To display employee names from our home page, we need to write a data handling method that will query our database for records in our parent table, RecordHistory. We can either do this in the code-behind file for our home page, or, we can do this from our class file, RecordHistoryData.cs. Generally, the latter option is preferred, since you are abstracting code that would otherwise be tightly coupled to the home page. From the solution explorer, do the following:

Left click the plus (+) sign next to classes.
Double click RecordHistoryData.cs.

With the file open, add the following namespaces:

using System.Data;
using System.Configuration;
using System.Data.SqlClient;
namespace RecordHistory.classes
{
    public class RecordHistoryData
    {
}

As you can see, you added three. The first allows us to work with stored procedures in ADO.NET; the second allows us to reference a connection key from your configuration file; and the last allows us to connect to SQL Server. Next, let's add your declarations, which you can get from the project downloads file.

As you can see from the code archive, you create a region named declarations, and inside you create public variables (with appropriate data types for each column from your database table) with getters and setters. Next, let's add our method that will list all employees on our homepage, which can be found from the project downloads file.

As you can see from the code archive, you created a static method named GetEmployees and did the following:

Created a connection object and passed in your configuration key
Created a command object and passed in your stored procedure and connection object
Created a generic list object, typed as our class

If for any reason you can't open a connection to the database or execute the query, you wrap this inside a try/catch block, which handles exceptions, and do the following:

Call the open method of your connection object.
Create a data reader object, and set it to the command object's execute reader method.
Because we want all employees listed, we iterate through our reader object using a while loop and do the following:
- Create a new instance of our RecordHistoryData object.
- Set each of our variables used to display our employee names from our reader object.
- Add our record history object to our list object each time through the iteration.
If an exception occurs, catch it and throw the exception to the screen.

Lastly, you call the dispose method of your command object and the close method of your connection object to clear resources.

Save your file(s).

Open Default.aspx.cs

Since you have written the functionality needed to display employees, let's add the ability to call it. From the solution explorer, left click the plus (+) sign next to default.aspx and double click default.aspx.cs. Next, add the region named declarations and methods from the project downloads file.

As you can see from the code archive, you do the following:

Add a namespace for our class, which is RecordHistory.classes.
Create a region named declarations.
In the page load event, we create a generic list object typed as our class, and then call the appropriate method.
We then set the data source property of our repeater control to our list object and then data bind to our control to display the names.

Save your file(s) and run the project. Your home page should show two employees.

Exploring Recordaudit.aspx

Let's take a moment and look over this page, since this is where our auditing occurs. From the solution, do the following:

Double click recordaudit.aspx.

Inside this file, you will notice the following:

A reference to our style sheet, which controls the layout of our form.
Three placeholder controls, which do the following:
- First placeholder shows our initial form, which contains fields to edit existing employee information.
- Second placeholder shows our success message, which will be visible after a successful update, or in our case, audit.
- Third placeholder shows a failure message, which will be visible if our query string is empty.
One server-side button control, which does the following:
- Server-side event, which handles update (audit) functionality