Removing enters from excel

Just to remind myself when someone delivers crappy import xls files:

1) Press CTRL+H (i.e. shortcut for Find and Replace)
2) Click in the ‘Find what‘ field, hold down the ALT key and type 0010;. It may not seem like anything happened but you actually entered an invisible line break character
3) At this point you can choose to Replace All.

SQL Audit Trail with pass through user credentials

For a customer we needed to add an audit trail to the database.

When you first start looking on the internet, you will probably find this link: http://www.nigelrivett.net/AuditTrailTrigger.html. The mayor problem with this audit trail is that the table will be huge because it stores the change of every field in a table in a seperate row. For our audit trail we needed a more simple approach: Just a shadow copy of the record which was edited. After some investigation I've found the build in SQL feature called 'Change Data Capture', which is only available in the enterprise edition. This is a little above our budget of 0 euro (as usual in IT it may cost nothing) ;). The other problem I found with Change Data Capture is that it isn't transparent enough. It does it's job, but nobody knows exactly how it works, I find this a bit tricky for our production environment.

So after just some more (2 hours!) searching I've come to the magnificent piece of SQL you'll find below from http://www.codeproject.com/Articles/21068/Audit-Trail-Generator-for-Microsoft-SQL.

After some minor adjustments it was ready to go!

But wait...

Our website users don't connect directly to the database, they use LINQ to SQL which has the user credentials of our Application Pool, so we still can't see who did the change :( StackOverflow has the answer: http://stackoverflow.com/questions/3197607/how-to-pass-out-of-band-current-user-id-data-to-sql-server-2008. Apparently there is an option to set CONTEXT_INFO on a connection so we can pass some extra data from the website user.

Time to hack LINQ! We want to execute the following statement everytime a new connection is used by the user:

SET CONTEXT_INFO 0x<binaryusername>

First we create a base class for our DataContext on which we fetch all Connection StateChange events. When the connection is opened send the 'SET CONTEXT_INFO' statement.

public class DataContextAuditBase
{
    public DataContextAuditBase(string connection) : base(connection)
    {
        Connection.StateChange += ConnectionStateChange;
    }

    public DataContextAuditBase(IDbConnection connection) : base(connection)
    {
        Connection.StateChange += ConnectionStateChange;
    }

    public DataContextAuditBase(string connection,
             MappingSource mappingSource) : base(connection, mappingSource)
    {
        Connection.StateChange += ConnectionStateChange;
    }

    public DataContextAuditBase(IDbConnection connection,
             MappingSource mappingSource) : base(connection, mappingSource)
    {
        Connection.StateChange += ConnectionStateChange;
    }

    private string UserName
    {
        get { return (HttpContext.Current != null) ? HttpContext.Current.User.Identity.Name : Environment.UserName; }
    }

    private string StringToHex(string convert)
    {
        System.Text.ASCIIEncoding  encoding = new System.Text.ASCIIEncoding();
        return BitConverter.ToString(encoding.GetBytes(convert)).Replace("-","").ToLower();
    }

    private void ConnectionStateChange(object sender, StateChangeEventArgs e)
    {
        if( e.CurrentState == ConnectionState.Open)
        {
            ExecuteCommand("SET CONTEXT_INFO 0x" + StringToHex(UserName));
        }
    }
}

And change the BaseClass of the DataContext:

Run the newly created website and see the magic happening, right before your eyes!

The used audit trail stored procedure (used to create tables on which you want to use the audit):

/******************************
** Name: sp_GenerateAuditTable
** Desc: creates an audit table in a database of  your choosing from a user 
**       table that you specify in whatever database you run the sproc against
** Original Author: Cedric Baelemans
** Modifying Author: Andrew Tappert (2011-3-17)
** Modifying Author: Luuk Sommers (2012-04-17)
** Date: 2012-04-17
** 
** Found on: http://sameproblemmorecode.blogspot.com/2012/04/sql-audit-trail-with-pass-through-user.html
** More info on original: http://www.codeproject.com/Articles/21068/Audit-Trail-Generator-for-Microsoft-SQL
**************************/
ALTER PROCEDURE [dbo].[sp_GenerateAuditTable]
 @TableName varchar(128),
 @Owner varchar(128) = 'dbo',
 @AuditNameExtension varchar(128) = '_shadow',
 @DropAuditTable bit = 0,
 @AuditDatabaseName varchar(128) = null
AS
BEGIN
 declare @sql nvarchar(4000)
 
 if not exists( SELECT schema_name FROM information_schema.schemata WHERE schema_name = @Owner )
 BEGIN
  PRINT 'Creating chema [' + @Owner + ']'
  set @sql = 'CREATE SCHEMA [' + @Owner + '] AUTHORIZATION [dbo]'
  print @sql
  EXEC (@sql)
 END

 -- Check if table exists
 IF not exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[' + @Owner + '].[' + @TableName + ']') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
 BEGIN
  PRINT 'ERROR: Table does not exist'
  RETURN
 END
 
 IF @AuditDatabaseName is null
 BEGIN
   set @AuditDatabaseName = (select DB_NAME())
 END
 
 -- Check @AuditNameExtension
 IF @AuditNameExtension is null
 BEGIN
  PRINT 'ERROR: @AuditNameExtension cannot be null'
  RETURN
 END
 
 -- Drop audit table if it exists and drop should be forced
 IF (exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[' + @AuditDatabaseName + '].[' + @Owner + '].[' + @TableName + @AuditNameExtension + ']') and OBJECTPROPERTY(id, N'IsUserTable') = 1) and @DropAuditTable = 1)
 BEGIN
  PRINT 'Dropping audit table [' + @AuditDatabaseName + '].[' + @Owner + '].[' + @TableName + @AuditNameExtension + ']'
  set @sql = 'drop table [' + @AuditDatabaseName + '].[' + @Owner + '].[' + @TableName + @AuditNameExtension + ']'
  print @sql
  EXEC (@sql)
 END
 
 -- Declare cursor to loop over columns
 DECLARE TableColumns CURSOR Read_Only
 FOR SELECT b.name, c.name as TypeName, b.length, b.isnullable, b.collation, b.xprec, b.xscale
  FROM sysobjects a 
  inner join syscolumns b on a.id = b.id 
  inner join systypes c on b.xtype = c.xtype and c.name <> 'sysname' 
  WHERE a.id = object_id(N'[' + @Owner + '].[' + @TableName + ']') 
  and OBJECTPROPERTY(a.id, N'IsUserTable') = 1 
  ORDER BY b.colId
 
 OPEN TableColumns
 
 -- Declare temp variable to fetch records into
 DECLARE @ColumnName varchar(128)
 DECLARE @ColumnType varchar(128)
 DECLARE @ColumnLength smallint
 DECLARE @ColumnNullable int
 DECLARE @ColumnCollation sysname
 DECLARE @ColumnPrecision tinyint
 DECLARE @ColumnScale tinyint
 
 -- Declare variable to build statements
 DECLARE @CreateStatement varchar(8000)
 DECLARE @ListOfFields varchar(2000)
 SET @ListOfFields = ''
 
 declare @AuditTableExists int
 declare @existsParams nvarchar(4000)
 set @existsParams='@Exists int output'
 set @sql = 'if exists (SELECT * FROM [' + @AuditDatabaseName + '].information_schema.tables  WHERE table_schema=N''' + @Owner + ''' and table_name=''' + @TableName + @AuditNameExtension + ''') set @Exists=1 else set @Exists=0'
 -- Check if audit table exists
 print @sql
 Exec sp_executesql  @sql, @existsParams, @Exists=@AuditTableExists output
 print @AuditTableExists
 IF @AuditTableExists = 1
 BEGIN
  -- AuditTable exists, update needed
  PRINT 'Table already exists. Only triggers will be updated.'
 
  FETCH Next FROM TableColumns
  INTO @ColumnName, @ColumnType, @ColumnLength, @ColumnNullable, @ColumnCollation, @ColumnPrecision, @ColumnScale
  
  WHILE @@FETCH_STATUS = 0
  BEGIN
   IF (@ColumnType <> 'text' and @ColumnType <> 'ntext' and @ColumnType <> 'image' and @ColumnType <> 'timestamp')
   BEGIN
    SET @ListOfFields = @ListOfFields + @ColumnName + ','
   END
 
   FETCH Next FROM TableColumns
   INTO @ColumnName, @ColumnType, @ColumnLength, @ColumnNullable, @ColumnCollation, @ColumnPrecision, @ColumnScale
 
  END
 END
 ELSE
 BEGIN
  -- AuditTable does not exist, create new

  -- Start of create table
  SET @CreateStatement = 'CREATE TABLE [' + @AuditDatabaseName + '].[' + @Owner + '].[' + @TableName + @AuditNameExtension + '] ('
  SET @CreateStatement = @CreateStatement + '[AuditId] [bigint] IDENTITY (1, 1) NOT NULL,'
 
  FETCH Next FROM TableColumns
  INTO @ColumnName, @ColumnType, @ColumnLength, @ColumnNullable, @ColumnCollation, @ColumnPrecision, @ColumnScale
  
  WHILE @@FETCH_STATUS = 0
  BEGIN
   IF (@ColumnType <> 'text' and @ColumnType <> 'ntext' and @ColumnType <> 'image' and @ColumnType <> 'timestamp')
   BEGIN
    SET @ListOfFields = @ListOfFields + @ColumnName + ','
  
    SET @CreateStatement = @CreateStatement + '[' + @ColumnName + '] [' + @ColumnType + '] '
    
    IF @ColumnType in ('binary', 'char', 'nchar', 'nvarchar', 'varbinary', 'varchar')
    BEGIN
     IF (@ColumnLength = -1)
      Set @CreateStatement = @CreateStatement + '(max) '   
     ELSE
      SET @CreateStatement = @CreateStatement + '(' + cast(@ColumnLength as varchar(10)) + ') '   
    END
  
    IF @ColumnType in ('decimal', 'numeric')
     SET @CreateStatement = @CreateStatement + '(' + cast(@ColumnPrecision as varchar(10)) + ',' + cast(@ColumnScale as varchar(10)) + ') '   
  
    IF @ColumnType in ('char', 'nchar', 'nvarchar', 'varchar', 'text', 'ntext')
     SET @CreateStatement = @CreateStatement + 'COLLATE ' + @ColumnCollation + ' '
  
    IF @ColumnNullable = 0
     SET @CreateStatement = @CreateStatement + 'NOT '   
  
    SET @CreateStatement = @CreateStatement + 'NULL, '   
   END
 
   FETCH Next FROM TableColumns
   INTO @ColumnName, @ColumnType, @ColumnLength, @ColumnNullable, @ColumnCollation, @ColumnPrecision, @ColumnScale
  END
  
  -- Add audit trail columns
  SET @CreateStatement = @CreateStatement + '[AuditAction] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,'
  SET @CreateStatement = @CreateStatement + '[AuditDate] [datetime] NOT NULL ,'
  SET @CreateStatement = @CreateStatement + '[AuditUser] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,'
  SET @CreateStatement = @CreateStatement + '[AuditApp] [varchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,' 
  SET @CreateStatement = @CreateStatement + '[AuditContext] [varchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL)' 
 
  -- Create audit table
  PRINT 'Creating audit table [' + @Owner + '].[' + @TableName + @AuditNameExtension + ']'
  print @CreateStatement
  EXEC (@CreateStatement)
 
  -- Set primary key and default values
  SET @CreateStatement = 'ALTER TABLE [' + @AuditDatabaseName + '].[' + @Owner + '].[' + @TableName + @AuditNameExtension + '] ADD '
  SET @CreateStatement = @CreateStatement + 'CONSTRAINT [DF_' + @TableName + @AuditNameExtension + '_AuditDate] DEFAULT (getdate()) FOR [AuditDate],'
  SET @CreateStatement = @CreateStatement + 'CONSTRAINT [DF_' + @TableName + @AuditNameExtension + '_AuditUser] DEFAULT (suser_sname()) FOR [AuditUser],CONSTRAINT [PK_' + @TableName + @AuditNameExtension + '] PRIMARY KEY  CLUSTERED '
  SET @CreateStatement = @CreateStatement + '([AuditId])  ON [PRIMARY], '
  SET @CreateStatement = @CreateStatement + 'CONSTRAINT [DF_' + @TableName + @AuditNameExtension + '_AuditApp]  DEFAULT (''App=('' + rtrim(isnull(app_name(),'''')) + '') '') for [AuditApp],'
  SET @CreateStatement = @CreateStatement + 'CONSTRAINT [DF_' + @TableName + @AuditNameExtension + '_AuditContext]  DEFAULT (CAST(CONTEXT_INFO() AS VARCHAR)) for [AuditContext]'
 
  EXEC (@CreateStatement)
 
 END
 
 CLOSE TableColumns
 DEALLOCATE TableColumns
 
 /* Drop Triggers, if they exist */
 PRINT 'Dropping triggers'
 
 
 set @sql = '
 IF exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(''[' + @Owner + '].[tr_' + @TableName + '_Insert]'') and OBJECTPROPERTY(id, ''IsTrigger'') = 1) 
  EXEC (''drop trigger [' + @Owner + '].[tr_' + @TableName + '_Insert]'')
 
 IF exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(''[' + @Owner + '].[tr_' + @TableName + '_Update]'') and OBJECTPROPERTY(id, ''IsTrigger'') = 1) 
  EXEC (''drop trigger [' + @Owner + '].[tr_' + @TableName + '_Update]'')
 
 IF exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(''[' + @Owner + '].[tr_' + @TableName + '_Delete]'') and OBJECTPROPERTY(id, ''IsTrigger'') = 1) 
  EXEC (''drop trigger [' + @Owner + '].[tr_' + @TableName + '_Delete]'')'
 
 
 exec (@sql)
 
 /* Create triggers */
 PRINT 'Creating triggers' 
 set @sql = 'CREATE TRIGGER tr_' + @TableName + '_Insert ON [' + @Owner + '].[' + @TableName + '] FOR INSERT AS INSERT INTO ['+ @AuditDatabaseName + '].[' + @Owner + '].[' + @TableName + @AuditNameExtension + '](' +  @ListOfFields + 'AuditAction) SELECT ' + @ListOfFields + '''I'' FROM Inserted'
 print @sql
 EXEC (@sql)
 
 set @sql = 'CREATE TRIGGER tr_' + @TableName + '_Update ON [' + @Owner + '].[' + @TableName + '] FOR UPDATE AS INSERT INTO [' + @AuditDatabaseName + '].[' + @Owner + '].[' + @TableName + @AuditNameExtension + '](' +  @ListOfFields + 'AuditAction) SELECT ' + @ListOfFields + '''U'' FROM Inserted'
 print @sql
 EXEC (@sql)
 
 set @sql = 'CREATE TRIGGER tr_' + @TableName + '_Delete ON [' + @Owner + '].[' + @TableName + '] FOR DELETE AS INSERT INTO [' + @AuditDatabaseName + '].[' + @Owner + '].[' + @TableName + @AuditNameExtension + '](' +  @ListOfFields + 'AuditAction) SELECT ' + @ListOfFields + '''D'' FROM Deleted'
 print @sql
 EXEC (@sql)
 
END

Use the comments for any issues, questions and of course comments :)

Happy auditing!

Luuk

Export and import a large set of resx resources

For a project at work we needed to export a lot of resource tables to excel and let an external party validate, correct and complement them. For this we used simple excel sheets. But then the application grows larger, so do the resource tables. With more than 13 languages this becomes a huge pain in the ***. To overcome this we've build a Resource Extractor which uses NPOI to export and import the data.

Currently only the Export functions are included. Once the import is finished I'll write another more complete post.

If you have even more translations to do you can also use dedicated websites so external parties can help you even better with translating. The one I've seen which looks very impressive is amanuens. If you have any experience with them or any other, please let us know in the comments.

The source is available at GitHub https://github.com/luuksommers/resourceextractor

How to add Beyond Compare to Visual Studio TFS

To use the mother of all compare tools "Beyond Compare" as your default code comparer in Visual Studio 2010, you need to follow the next steps:

1. Install BC3 (Download the trial and buy it if you like at http://www.scootersoftware.com)

2. Go to the options followed by Source Control, Visual Studio Team Foundation Server and click on Configure User Tools.

3. Click on Add and add a Compare command for extension .* using command C:\Program Files (x86)\Beyond Compare 3\BComp.exe and arguments %1 %2 /title1=%6 /title2=%7

4. Click on Add and add a Merge command for extension .* using command C:\Program Files (x86)\Beyond Compare 3\BComp.exe and arguments %1 %2 /savetarget=%4 /title1=%6 /title2=%7
- or -
if you want BC Pro's 3-way merge: %1 %2 %3 %4 /title1=%6 /title2=%7 /title3=%8 /title4=%9

You need to use the bcomp.exe, else it wound work see http://www.scootersoftware.com/vbulletin/showthread.php?t=5538

Cheers,
L

Creating a secure RESTfull wcf service and consume it cross domain with jquery using basic authentication

Last week I was very busy trying to create a secure wcf service which can be consumed using jquery jquery and wcf. There are a lot of resources (see References below) but none of them contain the full working package. Below I try to highlight the steps of creating the service and consumer. And because a sample says more than a thousand words, you can download it here.

Note:
The solution only works when the wcf service is hosted using iis. I have not tried to create the service using the self-hosting feature of wcf.

Note 2:
For basic authentication to be secure, you need to access it using SSL.

Step 1. Create your basic interface using the WebGet and WebInvoke

[OperationContract]
[WebGet(UriTemplate = "get/Do/{echo}", ResponseFormat = WebMessageFormat.Json)]
DoResponse Do(string echo);

[OperationContract]
[WebInvoke(Method = "POST", UriTemplate = "post/Do/{echo}", ResponseFormat = WebMessageFormat.Json)]
DoResponse DoPost(string echo);

Step 2. Enable cross domain calls using the global.asax

protected void Application_BeginRequest(object sender, EventArgs e)
{
    EnableCrossDomainAjaxCall();
}

private void EnableCrossDomainAjaxCall()
{
    HttpContext.Current.Response.AddHeader("Access-Control-Allow-Origin", "*");

    if (HttpContext.Current.Request.HttpMethod == "OPTIONS")
    {
        HttpContext.Current.Response.AddHeader("Access-Control-Allow-Methods", "GET, POST");
        HttpContext.Current.Response.AddHeader("Access-Control-Allow-Headers", "Content-Type, Authorization, Accept");
        HttpContext.Current.Response.End();
    }
}

Step 3. Configure the basic html module

<bindings>
    <webHttpBinding>
        <binding name="BasicAuthentication">
            <security mode="TransportCredentialOnly">
                <transport clientCredentialType="Basic" />
            </security>
        </binding>
    </webHttpBinding>
</bindings>

<serviceBehaviors>
<behavior>
    <!-- To avoid disclosing metadata information, set the value below to false and remove the metadata endpoint above before deployment -->
    <serviceMetadata httpGetEnabled="true"/>
    <!-- To receive exception details in faults for debugging purposes, set the value below to true.  Set to false before deployment to avoid disclosing exception information -->
    <serviceDebug includeExceptionDetailInFaults="false"/>
    <serviceAuthorization serviceAuthorizationManagerType="WcfSampleApp.Wcfservice.BasicAuthorization, WcfSampleApp.Wcfservice" />
</behavior>
</serviceBehaviors>

Step 4. Call the service using jquery ajax using basic authentication header.

$.ajax( {
 url : 'http://localhost:35461/SampleApp.svc/get/Do/same problem',
 dataType: 'json',
 type: "GET",
 beforeSend : function(xhr) {
     xhr.setRequestHeader("Authorization", "Basic " + encodeBase64("sameproblem:morecode"));
 },
 error : function(xhr, ajaxOptions, thrownError) {
     $('#GetDiv').html("error");
 },
 success : function(model) {
     $("#GetDiv").html(model.Echo);
 }
});

Download example solution

References:
http://msdn.microsoft.com/en-us/library/dd203052.aspx
http://stackoverflow.com/questions/1640391/how-do-i-make-a-jsonp-call-with-jquery-with-basic-authentication
http://blog.rassemblr.com/2011/05/jquery-ajax-and-rest-http-basic-authentication-done-deal/
http://www.codeproject.com/KB/ajax/jQueryWCFRest.aspx
http://msdn.microsoft.com/en-us/magazine/cc948343.aspx
http://weblogs.asp.net/cibrax/archive/2009/03/20/custom-basic-authentication-for-restful-services.aspx

ASP.NET AjaxControlToolkit Popup Extension not popping up

Today we had a problem with an popup extension. This was caused by storing the control in a session and use it for later. We have to make sure that Sessions are only used to store data, not full ASP.NET controls. So when you see something like the followin in your code:

Session["Popup"] = popupControl;

popupControl = Session["Popup"];
popupControl.Show()

There is a chance that after a postback from the client, the popup is unable to show. The solution was to recursively find the popup control by looking in all parent controls:

private T FindParentControl( T control )
    where T : class
{
    if( control.Parent is T )
    {
        return control.Parent as T;
    }
    return control.Parent != null ? FindParentControl( control.Parent ) : null;
}

This is also posted at stack overflow: http://stackoverflow.com/questions/4343144/ajaxcontroltoolkitmodalpopupextender-not-working-at-second-attempt/7658857#7658857

How to add a big fat TFS "Source Control" button in your toolbar

As developer we spend a lot of time in Source Control. In VS2010 there isn't a default big Source Control button in the toolbar, and because I always forget how to add it back after a reset settings, here's how I do it: First click on the little arrow-down next to the toolbar you want the Source Control button in, then Add or Remove buttons followed by Customize.

Click on Add command and look for View in the left column and TfsSourceContolExplorer in the right column (be patient, clicking on view takes a couple of seconds). And click OK.

Click on Modify selection to Rename the button, begin a new group and make sure the Image and Text is selected!

Bazinga! The 'never have to look for source control'-button is there to be clicked!