Sogeti Phoenix Blogs

ScopeIdentity.zip (1.70 kb)

Ever wonder how that magical tag called ‘[Serializable]’ placed at the top of a class enables it to be deconstructed into XML that can be tranmitted through firewalls or persisted in a files or database tables? Not to get too technical but attribute classes are embedded into the tagged classes’ metadata at compilation and can be reflected on to perform any function you desire. 

The following example provides a real world scenario where custom attributes can enhance one’s  coding experience.  I can’t tell you how many times I have to use stored procedures that have a million parameters each having the capacity to be null.  Think of a search procedure that has ten criteria.  If I was coding this with the SqlCommand class, I would have to write a method that creates each SqlParameter and populates it with DBNull or some value. There are over ninety nine combinations in this scenario.  That really cuts into my ebay time.

If I wrote an attribute that could turn a class into a SqlCommand with populated values and could be serialized in session or view state and reduce my time coding, life would be wonderful.  In addition, if I wrote a tool that generated the code for the attributed class, my evil plan to do no work would be complete!

I have attached a solution that contains all the code mentioned above.

AtrributeSolution.zip (51.43 kb)

While I'm developing, I'm always making changes to code and data, and not necessarily keeping very good or accurate records in the heat of the moment about what I'm changing.  Source code is easy to resolve using modern version control tools, because I can always run a diff to see the changes that I made.  But what do I do about databases?  If I ran some ALTER statements in the database, what happens if I accidentally closed the database session and lost my changes, or if I want to go back and review what I did at the end of an iteration?  What do I do then?

Fortunately, using a trick with triggers in SQL server, I can let SQL Server keep track of the changes that I make in my database.  In my databases, the first thing that I usually create is a table for storing DDL changes to my databases and a trigger that automatically populates the table when DDL statements are executed.  By using the table and trigger, I can go back at any point and review all of the changes that were made to the database.

The table that I usually define is below:

   1: CREATE TABLE dbo.DatabaseModification (

   2:     DatabaseModificationId INT NOT NULL IDENTITY(1,1) CONSTRAINT

   3:         PKNC_DatabaseModification PRIMARY KEY NONCLUSTERED WITH

   4:         FILLFACTOR = 90 ON "default",

   5:     EventType NVARCHAR(256) NOT NULL,

   6:     PostTime DATETIME NOT NULL,

   7:     Spid INT NOT NULL,

   8:     ServerName NVARCHAR(256) NOT NULL,

   9:     LoginName NVARCHAR(256) NOT NULL,

  10:     UserName NVARCHAR(256) NOT NULL,

  11:     DatabaseName NVARCHAR(256) NOT NULL,

  12:     SchemaName NVARCHAR(256) NULL,

  13:     ObjectName NVARCHAR(256) NULL,

  14:     ObjectType NVARCHAR(256) NULL,

  15:     TSqlCommand NVARCHAR(MAX) NOT NULL,

  16:     Note NVARCHAR(MAX) NOT NULL DEFAULT N''

  17: ) ON "default";

Most of these fields are populated automatically from information gathered by the trigger.  The Note field was added by me.  This field allows me to annotate the changes in the database while I'm reviewing the database changes.  More and more often, I find that I'm adding this table to my database definitions and including the table in the production databases.  On my applications, I usually also create an administrator user interface for this table because I think that it's handy for a system administrator of an application to be able to look at what changes occurred to a database if something breaks or goes wrong for no apparent reason.  The administrative UI also allows the administrator to edit the contents of the Note field.

Here's the source code for the trigger that populates this table:

   1: CREATE TRIGGER trgDatabaseModificationInsert ON DATABASE FOR

   2:     DDL_DATABASE_LEVEL_EVENTS

   3: AS

   4:     SET NOCOUNT ON;

   5:     

   6:     DECLARE @data AS XML;

   7:     SET @data = EVENTDATA();

   8:     INSERT INTO dbo._zsDatabaseModification (EventType, PostTime, 

   9:         Spid, ServerName, LoginName, UserName, DatabaseName, 

  10:         SchemaName, ObjectName, ObjectType, TSqlCommand) VALUES (

  11:         @data.value('(/EVENT_INSTANCE/EventType)[1]', 

  12:             'NVARCHAR(256)'),

  13:         @data.value('(/EVENT_INSTANCE/PostTime)[1]', 'DATETIME'),

  14:         @data.value('(/EVENT_INSTANCE/SPID)[1]', 'INT'),

  15:         @data.value('(/EVENT_INSTANCE/ServerName)[1]', 

  16:             'NVARCHAR(256)'),

  17:         @data.value('(/EVENT_INSTANCE/LoginName)[1]', 

  18:             'NVARCHAR(256)'),

  19:         @data.value('(/EVENT_INSTANCE/UserName)[1]', 

  20:             'NVARCHAR(256)'),

  21:         @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 

  22:             'NVARCHAR(256)'),

  23:         @data.value('(/EVENT_INSTANCE/SchemaName)[1]', 

  24:             'NVARCHAR(256)'),

  25:         @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 

  26:             'NVARCHAR(256)'),

  27:         @data.value('(/EVENT_INSTANCE/ObjectType)[1]', 

  28:             'NVARCHAR(256)'),

  29:         @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 

  30:             'NVARCHAR(MAX)'));

  31:     

  32:     SET NOCOUNT OFF;

  33: GO

By creating this trigger for DDL_DATABASE_LEVEL_EVENTS, any DDL statements that would modify the database by creating, altering, or deleting database objects will get logged in the DatabaseModification table.