Creating an Audit Record Application with ASP.NET | 4 | WebReference

Creating an Audit Record Application with ASP.NET | 4


[prev]

Writing View Employee Functionality

Before an audit can occur, we need the ability to view specific employee records. We will view and record audits from the same page, recordaudit.aspx, but separate the implementation. Let's tackle the view functionality first. From the solution explorer, do the following:

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

In this file, we'll write our GetEmployeeView, which is viewable from the project downloads. As you can see from this file, we do the following:

  • Create a connection object and pass in our configuration settings.
  • Create a command object and pass in our stored procedure and connection object.
  • Create a parameter, GUID, and pass in the appropriate data type and size.
  • Set the GUID's parameter value to the passed in variable from the query string, which came from our home page and add that to the command object's parameters collection.

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:

  • Open a connection to the database.
  • Create a data reader object and set it to the command object's execute reader method.
  • If we can read data from the object, we:
    • Set appropriate variable values from our reader object.
    • Otherwise, we catch and throw an exception.

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 Recordaudit.aspx.cs

Since you have written the functionality needed to display individual employees, let's add the ability to call it. From the solution explorer, do the following:

  • Left click the plus (+) sign next to recordaudit.aspx.
  • Double click recordaudit.aspx.cs.

Next, add the region named declarations and methods from the project downloads file. As you can see from the project downloads, we do the following:

  • Add a namespace for our class, which is RecordHistory.classes.
  • Create a region named declarations. Inside, we do the following:
    1. Create a new instance of both of our classes, and name the object appropriately.
  • Inside our page load event, we do the following:
    1. We check the query string to see if it's null.
    2. If it IS NOT, we:
      • Check for a post back with a negation operator.
    3. If it IS, we:
      • Show an error message by toggling our placeholder(s).
    4. If it IS NOT a POSTBACK, we:
      • Create a new instance of a GUID object, and pass in our query string.
    5. If the GUID object IS NOT NULL:
      • We call LoadData and pass in our GUID object.
    6. In our Load Data method, we:
      • Pass in our GUID object using a data type of GUID in the method signature.
      • Call our method GetEmployeeView and pass in our GUID object.
      • Set a session object, named ID, and set it to the ID variable value from our record history object (rh).

Four important notes about our load data method:

  1. Even though we don't show our ID, which is the primary key from our parent table, we'll use this ID when auditing our child table, RecordHistory_Audit.
  2. We view individual employees using a GUID, instead of a primary key. This way, we don't give away our database structure as easily to a hacker, and it also serves as a way to keep hackers or anyone else from easily cycling through primary keys to view other employees without following our interface.
  3. Our post back check ensures when we perform the audit, we don't reload the employee information again on page load.
  4. We use a session object to retain the value of the primary key that's returned from our query. Without it, we lose the value on post back, and would be forced to issue another query just for the ID of the record we want.

Save your file(s).

A Quick Note About Sessions

Many times you will read about careful implementations of session objects. The reason for this mainly stems from the fact that session objects are stored on the web server. If you have many session objects being created in an application, and the default time limit is 20 minutes, one begins to understand why their use needs to be carefully examined and their implementation carried out wisely. You other option would be a cookie-based implementation, which resides on the client, and are more widely used in larger applications to limit server resources being used.

Writing Audit Functionality

In order to record when an update (audit) occurs on an employee, we need the ability to capture specific information in our RecordHistory_Audit table, such as: the record index (primary key) from our parent table, the employee that's being updated, user completing the audit, as well as the date time. Let's write this functionality by first opening RecordHistoryDataAction.cs from the solution by following these steps:

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

With the file open, add the following namespaces: 

using System.Data;
using System.Configuration;
using System.Data.SqlClient;
using System.Security.Principal;
namespace RecordHistory.classes
{
public class RecordHistoryDataActions
    {
}

As you can see, you added four. 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; the third allows us to connect to SQL Server; and the fourth allows us to record which user audited the record based on their windows login credentials. 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 InsertAuditRecord that will insert the audit to our child table, which can be found from the project downloads file.

As you can see from the code archive, you created a method named InsertAuditRecord 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 SQL parameters for each of our variables used in the insertion
  • Set the values of our SQL parameters to the appropriate variable values, including:
    • On User, we set the value to the current user's name based on the windows identity
    • On Change date, we set the value to current date time from the server
    • On Change reason, we set the value to a literal string of our choosing
  • Added the parameters to the command object's collection

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
  • Call execute non query from our command object
  • 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.

Some important notes regarding recording our audit: there are several ways to record user information to meet your application needs. A few things should be obvious however: you don't want the user auditing the information to be able to fake their credentials, change the date or the time, or have access to the database to change what the table is recording from the application. What you decide to record after these essentials are covered is entirely up to specifications set forth by business analysts or the product owners.

Save your file(s).

Open Recordaudit.aspx.cs

Since we have written the audit functionality, we simply need to call it from our code-behind file. The functionality is shown from the project downloads file. From the solution, we do the following:

  • Left click the plus (+) sign next to recordaudit.aspx.
  • Double click recordaudit.aspx.cs.

In our submit click event, we do the following:

  • Set our rhd object, which was instantiated from RecordHistoryDataAction.cs, to the appropriate server-side form controls.
  • Set our Audit_ID variable from our object to our session object, which holds the primary key value from our parent table. This is how we're establishing the primary/foreign key relationship.
  • Call InsertAuditRecord.
  • Set our placeholder controls to their appropriate visibility.
  • Remove our session object's value.

It should be noted, we remove the session object's value after each audit has occurred to ensure we don't insert the wrong primary key from our parent table, to our child table.

Save your file, and run the project. You should now see a listing of all employees available from our parent table. Clicking on an employee link should take you to our record audit page, showing that employee information. You should be able to change the employee information, click submit, and then see a success message that provides a link back to our home page. You can check the database to ensure the audit was recorded by doing the following:

  • Open Microsoft SQL Server 2008 from the desktop.

From Microsoft SQL Server 2008, do the following:

  • Left click to select mwd_RecordHistory.
  • Left click on New Query.

Type the following in the query window:

use mwd_RecordHistory
select * from RecordHistory_Audit

You should see the newly inserted record from our previous operation.

Summary

In this article you learned how to implement an audit history of employee records that we updated. Furthermore, you learned the following:

  • Restoring a database from a backup.
  • Creating an ASP.NET application.
  • Separating data handling operations into the following:
    • Reading data
    • Inserting data
  • Understanding considerations for what information should be stored in an audit, and protecting this information from being tampered
  • How to work with session objects and understanding when to use them

Take the knowledge gained in this article and expand your auditing system to meet any needs you may have. If you have questions, please contact me.

Code Download

  • Audit history application source code

    • About the Author

    Ryan Butler is the founder of Midwest Web Design. His skill sets include HTML, CSS, Flash, JavaScript, ASP.NET, PHP and database technologies.

    Original: Jan. 25, 2011


    [prev]