Ever used Google Mail and the search when trying to find something and it can’t find it. This is most probably because it is somewhere where the default search does not look in, like the BIN
To search everywhere you use in:anywhere
To see all the the possibilities of search in Google Mail check out:
http://support.google.com/mail/bin/answer.py?hl=en&answer=7190
Found this quick reference for Rhino Mocks (3.3), very useful
So I need to trap and look at the traffic going over HTTP and knowing that Firefox is the browser to use with all it different plugins it makes sense to download the latest version of Firefox and see what I can plug in to it.
What I like about Firefox is it is so easy to install plugins, just click restart Firefox and you’re done.
So what I need to do is trap, monitor and post HTTP requests using XML, so what plug in am I going to use?
First one is FireBug every good developer and bad developer, even hackers will be using FireBug.
Next I need to monitor what is happening and I found Tamper Data which monitors ongoing requests, and more importantly monitors and posts and gets in the back ground.
Okay now the more tricky issue of being about to POST or GET using HTTP, I settled for Poster a great plug in for all the REST requests and perfect for what I am looking for.
So to summerise here is a list of what I have downloaded:
So I’m off, if I get any problems with them I’mm report them directly on this blog with comments so if you see no comments all is good.
So I’m back and ready to go, with just getting back from my travelling I’m back at work with only two days of my own time….! Oh well I was looking at taking December off and start fully working in the new year. Not that I took the first job that came along, far from it. While on the beaches of Goa I made up a list of what work I will only accept it included working near where I live, work I have done before so as to make it easier to get back in to the working mode, something to do with the Olympics, and a small team on a new project. And you guessed it this role fits all the criteria so a result
So time to keep my blog up to date and running again……!
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
Found a nice simple page to generate a Loading Images, simple to use and so easy to generate the image that you need
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
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
To enhance this it is worth look at Mike Hadlow post of Repository.GetById using LINQ Expression Syntax
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