First, you need to add a table to the database that will store unexpected application errors. Execute the SQL statement in Listing 4-1 against the Northwind database in the Query Analyzer.
Listing 4-1: Creating the application_errors Table
CREATE TABLE application_errors ( error_id int IDENTITY (1, 1) PRIMARY KEY NOT NULL, username varchar(50) NOT NULL, error_message varchar(200) NOT NULL, error_time smalldatetime NOT NULL, stack_trace varchar(4000) NULL)
Note | I come from the database design camp that advocates using a surrogate primary key on every table. I also like to make each of the primary keys an identity field with a seed of one and an increment of one. There are many data architects that advocate using natural keys. It is an age-old argument that I will not even try to argue or answer here! |
Note | Surrogate keys are artificial keys that have no relationship to the data within the table. Natural keys are keys created from the data within the table. Natural keys are usually multiple column keys that can be unwieldy. Surrogate keys are mandatory in dimensional database design (the type of model used for data warehouses). |
Next you need to create the stored procedure through which information will be saved to this table. Execute the SQL in Listing 4-2 to create the stored procedure.
Listing 4-2: The usp_application_errors_save Stored Procedure
CREATE PROCEDURE usp_application_errors_save @user_name varchar(50), @error_message varchar(200), @error_time smalldatetime, @stack_trace varchar(4000) AS INSERT INTO application_errors (username, error_message, error_time, stack_trace) VALUES (@user_name, @error_message, @error_time, @stack_trace)
You now have the ability to save your application errors in the database. It is time to actually create the classes responsible for capturing and saving errors.