Summary

This project was to develop a site to validate files. The user will drop a .csv file on the page (or click the Browse button to find a file). After the file is uploaded, it will be analyzed to determine if it matches the expected format.

This was an Azure project from the beginning, which made it fun. I was also able to use SignalR to provide immediate feedback to the user as the file was being analyzed. I use Redis on the server to store the results for the last 7 days. Purging old results is automatic, since Redis can expire a key after a set number of days.

The site uses FluentValidation for the many rules to check the file. This tool made some pretty complex validation much easier. It also helped eliminate what would have been a bunch of repetitious code.

Screens

Main Screen

When the user loads the site, this is the first screen they see. They can either drop a file on this page or click the browse button for a file picker dialog.
The User textbox and associated button was a temporary way of grouping SignalR messages. The name really should have been changed to Group instead of user. The idea is that everybody listening in Group “Fred” would receive the same messages. This is important because of the next screen.

Receiving File Screen

As the file is being analyzed, results are being sent back to the browser. The top 5 errors encountered are entered in the green list (not sure why I made it green). The gauge animates to show the number of lines processed.

Upload Complete Screen

When the analysis is complete, the user will either see a green check mark indicating the file is good, or they will see the green check mark with the red “not” circle indicating there were problems. A DataTables grid appears at the bottom of the page where they can view all the errors (using paging).

History List Screen

The user can view the history for the last 7 days. This screen had as much value to me as the developer as it did to the user. Before I added this screen, I would get an email saying some file didn’t validate. The email was not very descriptive as to why it didn’t validate and I was left searching log messages to figure out the problem. This screen allowed me to easily see why a file failed validation. I added a date picker to select the day and view the results.

History Detail Screen

Once a file is clicked from the History List Screen, this screen displays the detailed error messages for that file.

Code

Code Coverage

I know how rare it is to get 97% code coverage. I have been on projects where we were happy with 75-80% (and many where we had none). This was really helpful when we needed to do a version 1.1 of this project. I made the required changes, ran my tests. Some of them were broke, as they should have been, based on the new requirements. I fixed those tests and added a few more.
I highlighted the catch block above, since that is the code that is hardest for me to get code coverage on. I know many people would eliminate the try/catch and let it bubble up. That would “fix” the code coverage issue, but I am not really sure that is a fix. In my experience, I like to catch the error as close to where it happened as possible. Often, I can add parameters that were passed to the method in the error handler so I know what value it was working with when the error occurred.

Code Execution Time

We all agree that our unit tests should be fast so they can be run often. However, this was another one of those rare cases that I don’t see on most projects. It ran all 130 tests in under 2 seconds.

Technology / Tools

• C#
• SignalR
• Redis
• Azure
• FluentValidation
• AngularJS
• DataTables
• DropZone

UPDATE: I don’t usually claim something as an original idea. Anything formed in my brain is just an amalgamation of the videos I have watched and the stuff I have read. For this idea of just creating a JSON field in each table, I don’t remember seeing it anywhere else before. That is why I thought it was neat when I was perusing the Octopus tables today, and I noticed they also include a JSON field in each table. They even went as far as to make theirs Not Null, which I didn’t do.

Others can argue when to use Sql vs a NoSql solution. There is a time and place for each. I have went down both paths and there usually comes a time where the grass looks a little greener on the other side. Sql is great for creating related tables and helping to ensure referential integrity at the database level. However, it can be a little cumbersome to add one simple field to an existing table. Even if you are doing migrations from code, there is some work involved.

On the other hand, NoSql is great at handling this “jagged” data. You simple add new properties to your json object and store it. However, the problem I have had with NoSql in the past is that you have to put more thought into your data design up front to know how you will want to pull it out. Often, ad hoc queries require a full table scan. Some of these things may no longer be true, but who can keep up with how quickly things change around us?

Anyway, in some recent work, I stumbled across an approach that I think combines the benefits of both approaches. Others have probably been doing some version of this for years, but it just clicked for me the other day. The “trick” is to create a VARCHAR(max) field where we can store a json object. This gives me most of the relational goodness of Sql along with the ability to easily add “minor” fields, without touching the database. This will not work for “major” fields that are a key to another table or something that is going to be searched often. However, for something like adding a link to store the gravatar on a user record, this can be pretty sweet.

Finally, some code. Here is the script I use to create a table. The key is line 21, where I add my JsonData field. Any table that might need “extended” in the future gets this new field.

Here is the C# code for the Person object.

public class Person : EntityBase
{
    public long PersonId { get; set; }
    public long TenantId { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }

    public Person_NoSql NoSql { get; set; }

    public Person()
    {
        NoSql = new Person_NoSql();
    }

    public string JsonData
    {
        get { return JsonConvert.SerializeObject(NoSql); }
        set { NoSql = JsonConvert.DeserializeObject<Person_NoSql>(value); }
    }

    public class Person_NoSql
    {
        public string UrlAvatar { get; set; }
        public string AnyOtherField { get; set;}
    }
}

The code to use the new property is pretty simple.

Person person = mgr.GetPerson(personId);

if (!ReferenceEquals(null, person))
{                       
    FirstName = person.FirstName;
    LastName = person.LastName;

    UrlAvatar = person.NoSql.UrlAvatar;
    AnyOtherField = person.NoSql.AnyOtherField;
}

I am using Dapper for the data layer. When it sees a JsonData field returned from a query, it maps it to the JsonData string property on the object. As far as Dapper knows, this is just another string field.

That is pretty much it. I thought this would be a longer post, but in the words of the great “Forrest Gump”, That’s all I have to say about that

Summary

Have you ever written that “perfect” piece of code to handle some situation? You spend hours reading blogs and other code, and you finally pull it all together in a version that works in your project. In your mind, you know that since you just spent a few hours on this, there is no way you are going to forget it. In a future project when you will need this same code, you will just “remember” it was in xyz project. Well, for me, I start out that way, but I soon realize that I can’t remember where I did it. To my credit, I usually at least remember that I did it, I just don’t know where. While I love BareGrep as much as anybody, it is a bit like Google. If you don’t enter the right search terms, you still don’t find it.

So this post (and future posts like it) are an attempt to put that code in “one place” where I hope to be able to find it down the road. If the chunk of code is not worth a blog post, then it must not have been that impressive after all. 🙂

This morning, I was trying to to implement error handling in my catch block. I found a chunk of code I had used in the past, and it was rolling back transactions, which reminded me that I had forgot to put that in the current sproc I was working on. At that point, I started this post. Below are some notes that should be helpful to me in the future. I hope they will benefit you as well.

Header Comment

Include some kind of comment. I include a PRINT statement, which helps in the SqlCmd (future post) script that calls this. This also includes the DROP/CREATE approach which I like to do for sprocs.

/*
  Revision History
    4Jul15  ScottB  Initial Version
*/
PRINT 'ProceduresCall_spt.sql'

SET ANSI_NULLS ON
GO

SET ANSI_NULLS ON
GO

-- http://technet.microsoft.com/en-us/library/ms190324(v=sql.100).aspx
IF (OBJECT_ID(N'call.Call_spt', 'P') IS NOT NULL ) BEGIN
   DROP PROCEDURE call.Call_spt
   PRINT '   Dropped';
END
GO

SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE call.Call_spt
	@xml XML
AS
...

top

Initial Variables

Declare a couple variables at the very beginning that will be needed later. The @tranCount is needed to be able to commit and rollback transactions. The @tenantId is needed in the Error handler, so we set it outside the BEGIN TRY (not sure if this is necessary in TSQL, but it would be in C#).

BEGIN
	SET NOCOUNT ON;

	DECLARE @tranCount INT = @@TRANCOUNT;
	-- Default to System, but it should get reset below.
    DECLARE @tenantId INT = 1; 

top

BEGIN TRANSACTION ?? / COMMIT

As close as possible to your actual Insert or Update, you need a block like this. It handles the situation where we were already in a transaction (possibly from a calling sproc).

IF (@tranCount = 0) BEGIN
   BEGIN TRANSACTION
END ELSE BEGIN
   SAVE TRANSACTION Call_spt
END

-- Insert or Update ...

IF (@tranCount = 0) BEGIN
   COMMIT TRANSACTION 
END

top

Catch Block

This next block is assuming that the prior code was in a TRY CATCH block.

BEGIN CATCH

    DECLARE @xstate INT = XACT_STATE();
    IF @xstate = -1 BEGIN
       ROLLBACK TRANSACTION
    END
    IF @xstate = 1 AND @tranCount = 0 BEGIN
       ROLLBACK TRANSACTION
    END
    IF @xstate = 1 AND @tranCount > 0 BEGIN
       ROLLBACK TRANSACTION Call_spt
    END

    PRINT 'Error ' + CONVERT(varchar(50), ERROR_NUMBER()) +
          ', Severity ' + CONVERT(varchar(5), ERROR_SEVERITY()) +
          ', State ' + CONVERT(varchar(5), ERROR_STATE()) + 
          ', Procedure ' + ISNULL(ERROR_PROCEDURE(), '-') + 
          ', Line ' + CONVERT(varchar(5), ERROR_LINE());
    PRINT ERROR_MESSAGE();        

    EXEC tenant.ReThrowError_spt @tenantId

    RETURN -1;
END CATCH	

top