Audit information with SQL

Time comes when you have a database running, fully deployed and working, and a few years later something appears to be going wrong but you are not sure what or what is happening.

You start looking at possible options, and one of the first that springs to mind is performing an audit on any changed information, wouldn't it be nice to be able to audit information so that you can see what an when things are changing?

One way of doing this is to use triggers with inside of SQL Server, this does come at a high cost, but does provide you with the information you require.

First we need to have a Audit table

CREATE TABLE [dbo].[Audit](

    [AuditID] [int] IDENTITY(1,1) NOT NULL,

    [Type] [char](1) NULL,

    [TableName] [varchar](128) NULL,

    [PrimaryKeyField] [varchar](1000) NULL,

    [PrimaryKeyValue] [varchar](1000) NULL,

    [FieldName] [varchar](128) NULL,

    [OldValue] [varchar](1000) NULL,

    [NewValue] [varchar](1000) NULL,

    [UpdateDate] [datetime] NULL DEFAULT (getdate()),

    [UserName] [varchar](128) NULL

) ON [PRIMARY]

Okay now that is done, then we need to decide on what triggers we want, we could place a trigger on all the tables, like this:

DECLARE @sql varchar(8000), @TABLE_NAME sysname
SET NOCOUNT ON

SELECT @TABLE_NAME= MIN(TABLE_NAME)
FROM INFORMATION_SCHEMA.Tables
WHERE
TABLE_TYPE= 'BASE TABLE'
AND TABLE_NAME!= 'sysdiagrams'
AND TABLE_NAME!= 'Audit'

WHILE @TABLE_NAME IS NOT NULL
 BEGIN
EXEC('IF OBJECT_ID (''' + @TABLE_NAME+ '_ChangeTracking'', ''TR'') IS NOT NULL DROP TRIGGER ' + @TABLE_NAME+ '_ChangeTracking')
SELECT @sql =
'
create trigger ' + @TABLE_NAME+ '_ChangeTracking on ' + @TABLE_NAME+ ' for insert, update, delete
as

declare @bit int ,
@field int ,
@maxfield int ,
@char int ,
@fieldname varchar(128) ,
@TableName varchar(128) ,
@PKCols varchar(1000) ,
@sql varchar(2000),
@UpdateDate varchar(21) ,
@UserName varchar(128) ,
@Type char(1) ,
@PKFieldSelect varchar(1000),
@PKValueSelect varchar(1000)

select @TableName = ''' + @TABLE_NAME+ '''

-- date and user
select @UserName = system_user ,
@UpdateDate = convert(varchar(8), getdate(), 112) + '' '' + convert(varchar(12), getdate(), 114)

-- Action
if exists (select * from inserted)
if exists (select * from deleted)
select @Type = ''U''
else
select @Type = ''I''
else
select @Type = ''D''

-- get list of columns
select * into #ins from inserted
select * into #del from deleted

-- Get primary key columns for full outer join
select @PKCols = coalesce(@PKCols + '' and'', '' on'') + '' i.'' + c.COLUMN_NAME + '' = d.'' + c.COLUMN_NAME
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where pk.TABLE_NAME = @TableName
and CONSTRAINT_TYPE = ''PRIMARY KEY''
and c.TABLE_NAME = pk.TABLE_NAME
and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

-- Get primary key fields select for insert
select @PKFieldSelect = coalesce(@PKFieldSelect+''+'','''') + '''''''' + COLUMN_NAME + ''''''''
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where pk.TABLE_NAME = @TableName
and CONSTRAINT_TYPE = ''PRIMARY KEY''
and c.TABLE_NAME = pk.TABLE_NAME
and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

select
@PKValueSelect = coalesce(@PKValueSelect+''+'','''') +
''convert(varchar(100), coalesce(i.'' + COLUMN_NAME + '',d.'' +
COLUMN_NAME + ''))''
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,  
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c 
where  pk.TABLE_NAME = @TableName 
and CONSTRAINT_TYPE = ''PRIMARY KEY'' 
and c.TABLE_NAME = pk.TABLE_NAME 
and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

if @PKCols is null
begin
raiserror(''no PK on table %s'', 16, -1, @TableName)
return
end

select @field = 0, @maxfield = max(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName
while @field < @maxfield
begin
select
@field = min(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where
TABLE_NAME = @TableName and ORDINAL_POSITION > @field
select @bit = (@field - 1 )% 8 + 1
select @bit = power(2,@bit - 1)
select @char = ((@field - 1) / 8) + 1
if substring(COLUMNS_UPDATED(),@char, 1) & @bit > 0 or @Type in (''I'',''D'')
begin
select @fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION = @field
select
@sql = ''insert Audit (Type, TableName, PrimaryKeyField,
PrimaryKeyValue, FieldName, OldValue, NewValue, UpdateDate, UserName)''
select @sql = @sql + '' select '''''' + @Type + ''''''''
select @sql = @sql + '','''''' + @TableName + ''''''''
select @sql = @sql + '','' + @PKFieldSelect
select @sql = @sql + '','' + @PKValueSelect
select @sql = @sql + '','''''' + @fieldname + ''''''''
select @sql = @sql + '',convert(varchar(1000),d.'' + @fieldname + '')''
select @sql = @sql + '',convert(varchar(1000),i.'' + @fieldname + '')''
select @sql = @sql + '','''''' + @UpdateDate + ''''''''
select @sql = @sql + '','''''' + @UserName + ''''''''
select @sql = @sql + '' from #ins i full outer join #del d''
select @sql = @sql + @PKCols
select @sql = @sql + '' where i.'' + @fieldname + '' <> d.'' + @fieldname
select @sql = @sql + '' or (i.'' + @fieldname + '' is null and  d.'' + @fieldname + '' is not null)''
select @sql = @sql + '' or (i.'' + @fieldname + '' is not null and  d.'' + @fieldname + '' is null)''
exec (@sql)
end
end
'
SELECT @sql
EXEC(@sql)
SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables
WHERE TABLE_NAME> @TABLE_NAME
AND TABLE_TYPE= 'BASE TABLE'
AND TABLE_NAME!= 'sysdiagrams'
AND TABLE_NAME!= 'Audit'
END

or just a selected table

 

DECLARE @TABLE varchar(1000)

SET @TABLE = 'table name to audit'

DECLARE @sql varchar(8000), @TABLE_NAME sysname
SET NOCOUNT ON

SELECT @TABLE_NAME= MIN(TABLE_NAME)
FROM INFORMATION_SCHEMA.Tables
WHERE
TABLE_TYPE= 'BASE TABLE'
AND Table_Name = @TABLE

WHILE @TABLE_NAME IS NOT NULL
 BEGIN
EXEC('IF OBJECT_ID (''' + @TABLE_NAME+ '_ChangeTracking'', ''TR'') IS NOT NULL DROP TRIGGER ' + @TABLE_NAME+ '_ChangeTracking')
SELECT @sql =
'
create trigger ' + @TABLE_NAME+ '_ChangeTracking on ' + @TABLE_NAME+ ' for insert, update, delete
as

declare @bit int ,
@field int ,
@maxfield int ,
@char int ,
@fieldname varchar(128) ,
@TableName varchar(128) ,
@PKCols varchar(1000) ,
@sql varchar(2000),
@UpdateDate varchar(21) ,
@UserName varchar(128) ,
@Type char(1) ,
@PKFieldSelect varchar(1000),
@PKValueSelect varchar(1000)

select @TableName = ''' + @TABLE_NAME+ '''

-- date and user
select @UserName = system_user ,
@UpdateDate = convert(varchar(8), getdate(), 112) + '' '' + convert(varchar(12), getdate(), 114)

-- Action
if exists (select * from inserted)
if exists (select * from deleted)
select @Type = ''U''
else
select @Type = ''I''
else
select @Type = ''D''

-- get list of columns
select * into #ins from inserted
select * into #del from deleted

-- Get primary key columns for full outer join
select @PKCols = coalesce(@PKCols + '' and'', '' on'') + '' i.'' + c.COLUMN_NAME + '' = d.'' + c.COLUMN_NAME
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where pk.TABLE_NAME = @TableName
and CONSTRAINT_TYPE = ''PRIMARY KEY''
and c.TABLE_NAME = pk.TABLE_NAME
and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

-- Get primary key fields select for insert
select @PKFieldSelect = coalesce(@PKFieldSelect+''+'','''') + '''''''' + COLUMN_NAME + ''''''''
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where pk.TABLE_NAME = @TableName
and CONSTRAINT_TYPE = ''PRIMARY KEY''
and c.TABLE_NAME = pk.TABLE_NAME
and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

select
@PKValueSelect = coalesce(@PKValueSelect+''+'','''') +
''convert(varchar(100), coalesce(i.'' + COLUMN_NAME + '',d.'' +
COLUMN_NAME + ''))''
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,  
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c 
where  pk.TABLE_NAME = @TableName 
and CONSTRAINT_TYPE = ''PRIMARY KEY'' 
and c.TABLE_NAME = pk.TABLE_NAME 
and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

if @PKCols is null
begin
raiserror(''no PK on table %s'', 16, -1, @TableName)
return
end

select @field = 0, @maxfield = max(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName
while @field < @maxfield
begin
select
@field = min(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where
TABLE_NAME = @TableName and ORDINAL_POSITION > @field
select @bit = (@field - 1 )% 8 + 1
select @bit = power(2,@bit - 1)
select @char = ((@field - 1) / 8) + 1
if substring(COLUMNS_UPDATED(),@char, 1) & @bit > 0 or @Type in (''I'',''D'')
begin
select @fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION = @field
select
@sql = ''insert Audit (Type, TableName, PrimaryKeyField,
PrimaryKeyValue, FieldName, OldValue, NewValue, UpdateDate, UserName)''
select @sql = @sql + '' select '''''' + @Type + ''''''''
select @sql = @sql + '','''''' + @TableName + ''''''''
select @sql = @sql + '','' + @PKFieldSelect
select @sql = @sql + '','' + @PKValueSelect
select @sql = @sql + '','''''' + @fieldname + ''''''''
select @sql = @sql + '',convert(varchar(1000),d.'' + @fieldname + '')''
select @sql = @sql + '',convert(varchar(1000),i.'' + @fieldname + '')''
select @sql = @sql + '','''''' + @UpdateDate + ''''''''
select @sql = @sql + '','''''' + @UserName + ''''''''
select @sql = @sql + '' from #ins i full outer join #del d''
select @sql = @sql + @PKCols
select @sql = @sql + '' where i.'' + @fieldname + '' <> d.'' + @fieldname
select @sql = @sql + '' or (i.'' + @fieldname + '' is null and  d.'' + @fieldname + '' is not null)''
select @sql = @sql + '' or (i.'' + @fieldname + '' is not null and  d.'' + @fieldname + '' is null)''
exec (@sql)
end
end
'
SELECT @sql
EXEC(@sql)
SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables
WHERE TABLE_NAME> @TABLE_NAME
AND TABLE_TYPE= 'BASE TABLE'
AND Table_Name = @TABLE
END

Of course you need someway of turning off the triggers, so here you are:

 

DECLARE @sql varchar(8000), @TABLE_NAME sysname

SET NOCOUNT ON



SELECT @TABLE_NAME= MIN(TABLE_NAME)

FROM INFORMATION_SCHEMA.Tables

WHERE

TABLE_TYPE= 'BASE TABLE'

AND TABLE_NAME!= 'sysdiagrams'

AND TABLE_NAME!= 'Audit'



WHILE @TABLE_NAME IS NOT NULL

 BEGIN

EXEC('IF OBJECT_ID (''' + @TABLE_NAME+ '_ChangeTracking'', ''TR'') IS NOT NULL DROP TRIGGER ' + @TABLE_NAME+ '_ChangeTracking')

SELECT @sql =

'

DROP trigger ' + @TABLE_NAME+ '_ChangeTracking'



SELECT @sql

EXEC(@sql)

SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables

WHERE TABLE_NAME> @TABLE_NAME

AND TABLE_TYPE= 'BASE TABLE'

AND TABLE_NAME!= 'sysdiagrams'

AND TABLE_NAME!= 'Audit'

END

That should do it, I got the majority of SQL from Jon Galloway post Adding simple trigger-based auditing to your SQL Server database, so I can;t take all the credit for these scripts, just the changes and modifications.

If performance is an issue they you could check out Centralized Asynchronous Auditing with Service Broker or Centralized Asynchronous Auditing across Instances and Servers with Service Broker

Dilbet

Spinning image for loading a page

Found a nice simple page to generate a Loading Images, simple to use and so easy to generate the image that you need

http://www.ajaxload.info/

Are you ready for HTML5

With HTML5 becoming more of the norm, well if it's not it is about to be, and IE6 not supporting it (hang on IE9 does not currently support it either!), so it will just have to be dumped, at last  :-)  As us developers will need to get up to speed on HTML5 there is a new resource available from Google, HTML5Rocks, lots of great resource and information available on this site

LINQ to SQL using SoC

I have had a few people saying that LINQ to SQL is hard to use for Separation of Concerns, so I've created a small project that show how to do it.  I have used the MEF as my SoC framework.

Project is using .Net 3.5

SoCLINQ2SQL.zip (905.61 kb)

To enhance this it is worth look at Mike Hadlow post of Repository.GetById using LINQ Expression Syntax

MEF not supporting open generic types

If you are trying to use MEF with open generic types like:

[Export(typeof(IRepository<>))]
public class itial; padding: 0px; margin: 0px; border: 0px initial initial;">Repository<T> : IRepository<al; background-color: transparent; color: black; background-position: initial initial; background-repeat: initial initial; padding: 0px; margin: 0px; border: 0px initial initial;">T>
   
where T : class
{

With an import of

[Import(typeof(IRepository<>))]
private IRepository<Contact> repository;

You'll come stuck, as the current implementation MEF does not support Open Generic Types.

For more information take a look at Glenn Block article Why doesn’t MEF support open-generics for exports? Because MEF is not type based

Also there is Open-generic support in the MEF Contrib, which can be found on codeplex.

The reasoning is this, MEF parts relate on contracts which are strings, not types. To illustrate, see the code below.

namespace Orders {

  public interface IOrderRepository {}

  [Export(typeof(IOrderRespository))]

  public class OrderRepository : IOrderRepository {

  }

}

Although I have passed typeof(IOrderRepsitory) to the export, that is just a convenience that gets converted to a contract name of "Orders.IOrderRepository". The same applies to the importer...

[Export]

public class OrderService(

  [Import]

  public IOrderRepository Repository {gets;set;} 

)

The import on IOrderRepository also gets converted to a contract name of "Orders.IOrderRepository". This way the container is able to satisfy the import as the 2 contracts match. In the same way we support closed generics, so....

public interface IRepository<T> {}

namespace Orders {

  [Export(typeof(IRepository<Order>))]

  public class OrderRepository : IRepository<Order> {

  }

}

[Export]

public class OrderService(

  [Import]

  public IRepository<Order> Repository {gets;set;} 

)

Will work because the OrderRepository is exporting the contractname "Orders.IRepository<Order>" and the OrderService is importing the same contract.

However, this is what it looks like if we try the same with open generics.

public interface IRepository<T> {}

namespace Utils {

  [Export(typeof(IRepository<>))]

  public class Repository : IRepository<T> {

  }

}

[Export]

public class OrderService(

  [Import]

  public IRepository<Order> Repository {gets;set;} 

Now the contract names will be different. The exporter will have a contract of  "Utils.IRepository<>" and the importer will have a contract of "Utils.IRepository<Order>".

It is a simple match up, that breaks down in the open-generics case. This is because fundamentally, MEF is not matching on type.

Original article form codeplex

How to generate LINQ to SQL Mapping

You may or may not have come across the ability within LINQ to SQL to have mapping to and from your own objects.

Microsoft has a mapping builder called sqlmetal, which is a code generation tool to generate the mapping automatically.

Here is a sample command line to generate the mapping file.

[code language=c#]

sqlmetal /server:. /database:mydb /map:mydbMappings.xml /code:code.dbml

[/code]

Running Gallio tests within Visual Studio

While using Gallio for performing Unit Tests within Visual Studio, I have noticed that some times you can't run Unit Tests, or it says that no tests available, with thanks to Lee, he pointed me to a simple fix

Visual Studio can be used to run your tests in process, with the debugger. In order for a project to be “testable”, you'll need to make sure it has the following in the csproj:

[code language=xml]

<ProjectTypeGuids>{3AC096D0-A1C2-E12C-1390-A8335801FDAB};{FAE04EC0-301F-11D3-BF4B-00C04F79EFBC}</ProjectTypeGuids>

[/code]

 

PRETEC i-DISK - how to rescue

I recently purchased a Pertec i-Disk Bullet 32Gb, and within a hour I had broken it, it had become write protected, not sure why.  After an e-mail to Pertec support they sent me a solution to solve the problem, here is what they said and how to get your i-Disk working again

Please use USB flash drive recovery tool as attach file. And repair unworkable i-Disk Bulletproof as following procedures;

If this utility still can’t help you to repair your failure Bulletproof USB, we suggest you can send back the USB to original purchased store/vendor,

They will provide you RMA service for further check and repair process.

URescue utility repairing procedures:

1.           Backup your personal data stored in i-Disk Bulletproof if available.

2.           Plug unworkable i-Disk Bulletproof into a safety virus-free desktop or notebook computer.

3.           Execute “URescue.exe”, click “Update” button to repair i-Disk Bulletproof.

4.           Please be remind, all data stored in i-Disk Bulletproof will be deleted. Click “OK” to continue.

 

5.           Repair successfully, click “OK” to exit URescue utility.

URescue.zip (5.31 mb)

AutoMapper

I have just given an hour presentation on AutoMapper, and great Object to Object mapper.

Here is the source file with the samples

AutoMapper.zip (855.99 kb)

About the author

You have probably figured out by now that my name is Bryan Avery (if not, please refer to your browser's address field).  Technology is more than a career to me - it is both a hobby and a passion.  I'm an ASP.NET/C# Developer at heart...

Month List