, is how to log the calls to the database portion of our application. This is necessary so that we will be able to monitor the SQL statements that our application is executing at run time. This helps in easing up a lot of problems when troubleshooting production code.In my case, I have no problem doing this in classic ASP since I've been doing this for years. But when it comes to .NET using ASP.NET and ADO.NET
, I need to recode this functionality. One reason for the recoding of existing ones is that we want to take advantage of the features being implemented by the new technology, in our case .NET.In the case of .NET, and in relation to this problem, I've come up with a solution, a nifty one but far from perfect. Later on I hope to improve on this.
When doing the logging of every SQL calls to our application, I've come up with the ff goals:
1. The data access class should be isolated in a layer on its own, a Data Access Layer (DAL).
2. The DAL should use the Typed Dataset feature of ADO.NET. The reason for this is to take advantage of the typed dataset designer in VS2005.
3. I need to log every calls to the Select, Insert, Update, and Delete functions of the typed dataset.
4. Most importantly, the logging code should not be deleted or recreated when we recreate/edit the typed dataset in the dataset designer. One thing I don't want is to repeat myself especially if there are ways to prevent it.
In the beginning I thought of the following possible steps:
1. Once I've created my typed dataset, I'll create a new file that will contain my partial class for the typed TableAdapter. For example, if my typed table adapter is CustomerInfoTableAdapter, this should also be the same name in my new class file. The namespace should also be the same. This ensures that I'm simply extending the existing CustomerInfoTableAdapter instead of accidentally creating a new one.
2. I'll then create a class that derives from the System.Attribute class.
3. This custom attribute class should be able to intercept calls to any function decorated with the DataObjectMethodAttribute. The reason for this is that, when you look at the automatically-generated target functions in the typed dataset, all of them make use of this DataObjectMethodAttribute attribute. If we can intercept those functions, we're set.
3. Then, finally I'll decorate the CustomerInfoTableAdapter class with this new attribute class, and we should be done.
Ideally, that's what I want.
After trying to do this in a few hours, I've decided to stop pursuing this approach. Because to properly implement this, I think, we need to be able to make use of the facility of the .NET framework's ContextBoundObject class. This class derives from MarshallByRefObject and it's main purpose is to help us do this very sort of thing: to provide a simple interception mechanism such that we can trap every function call made to an object that make use of this feature. (See Don Box's book "Essential .NET: Volume 1" for the exact steps).
A few limitation of this feature very early on shows itself while I'm trying to make use of it:
1. Since ContextBoundObject is a class that we need to inherit from in the class whose functions we need to intercept (in this case the CustomerInfoTableAdapter class)...
2) and since the CustomerInfoTableAdapter already inherits from the System.ComponentModel.Component class (see your typed dataset's implementation of the typed table adapter), we therefore can not assign CustomerInfoTableAdapter to inherit from ContextBoundObject since .NET does not allow multiple class inheritance.
With this limitation and the short time that I have, my goal is therefore kaput. And I have to find another solution.
This is the solution I've implemented:
1. Create a partial class for CustomerInfoTableAdapter, as stated in Step 1 above.
2. Create a new constructor for this class, but include a parameter for this constructor since an existing parameterless constructor already exists in the designer-generated table adapter class. The parameter may or may not be used inside the constructor. In this new constructor, forward the call to the other constructor. This ensures that we properly initialize the object.
3. Iterate through all the Command objects in the CommandCollection property of your typed table adapter. For each Command object, handle it's StateCompleted event. The StateCompleted event is the event that fires after executing the statement. Note though that the Command objects inside the CommandCollection property only refers to the SELECT command objects for the adapter; therefore you need to handle the other Command objects for the INSERT, UPDATE, and DELETE.
4. To handle these other functions/commands, call the Adapter property of your typed adapter. This will properly initialize the actual adapter in your typed table adapter.
5. Once the Adapter property is called and assigned to a local variable, you can extract the individual commands contained in the InsertCommand, DeleteCommand, and UpdateCommand properties of the typed table adapter. Check them if they are not null, and if not null, you can now handle their StateCompleted event.
6. In the event handler(s) for the StatementCompleted, you can now log the respective properties you want to record. BTW, you can have only one event handler for all the events of all the Command objects. They have the same arguments anyway, and since your purpose is simply to record the SQL statements.
7. In your client code, call the new constructor with the extra parameter.
The code will be something like these:
namespace DataAccessLayer.CustomerInfoTableAdapters
{
public partial class CustomerInfoTableAdapter
{
public CustomerInfoTableAdapter(bool log)
: this() //forward to the other constructor
{
SqlCommand[] _cmds = this.CommandCollection; //calls to CommandCollection will
//initialize the commands
foreach (SqlCommand cmd in _cmds)
{
cmd.StatementCompleted += Command_StatementCompleted;
}
SqlDataAdapter da = this.Adapter; //calls to Adapter will initialize the _adapter field
if (da != null)
{
SqlCommand insCmd = da.InsertCommand;
SqlCommand delCmd = da.DeleteCommand;
SqlCommand updCmd = da.UpdateCommand;
if (insCmd != null) insCmd.StatementCompleted += Command_StatementCompleted;
if (delCmd != null) delCmd.StatementCompleted += Command_StatementCompleted;
if (updCmd != null) updCmd.StatementCompleted += Command_StatementCompleted;
}
}
void Command_StatementCompleted(object sender, System.Data.StatementCompletedEventArgs e)
{
SqlCommand cmd = sender as SqlCommand;
if (cmd != null)
{
object container = cmd.Container;
if (container != null)
{
Console.WriteLine(string.Format("[Command_StatementCompleted]: Container's Type: {0};", container.GetType().ToString()));
}
Console.WriteLine("===============================================================");
foreach (SqlParameter parm in cmd.Parameters)
{
Console.WriteLine(string.Format("[Command_StatementCompleted]:"
+ " ParameterName: {0}; "
+ " Size: {1}; "
+ " Value: {2}; "
+ " SourceColumn: {3}; "
+ " IsNullable: {4}; "
+ " Direction: {5}; "
+ " DbType: {6}; "
+ " SqlDbType: {7}; "
, parm.ParameterName
, parm.Size
, parm.Value
, parm.SourceColumn
, parm.IsNullable
, parm.Direction
, parm.DbType
, parm.SqlDbType
));
}
Console.WriteLine(string.Format("[Command_StatementCompleted]: Text: {0}; RecordCount: {1}", cmd.CommandText, e.RecordCount));
}
}
}
}
From there, you can implement your own logging mechanism: you can write to the Event log, to a text file, etc. What is more important is you have a solution for intercepting those pesky SQL calls.
No comments:
Post a Comment