Triggers/Rowcount And NHibernate

 

The Short Story

If you have seen this error, whist using NHibernate with Triggers, this may be the right post for you

“Batch update returned unexpected row count from update; actual row count: 2; expected: 1″.

 

Long Story

So today I had to create some audit tables, ok some of you will surely go hey why didn’t you just use CQRS for that? Yes yes I know that by using CQRS and event sourcing I would indeed get full audit by way of the stored events, in fact I will be writing about that soon, but for now lets forget about that and just stick to the current situation which is :

 

  1. I have a table that I want to provide auditing for
  2. I am using SQL Server
  3. I am using Fluent NHibernate

 

Now there are numerous ways you may perform auditing in SQL server, in fact later versions of SQL Server come with inbuilt Audit functionality, or you could use a general all purpose audit table which records very generic information such as

 

  • The table name
  • The action performed (insert, update, delete)
  • The old value
  • The new value
  • Some narrative
  • Some date information

 

This is fine, but for my requirements, what I wanted was a full row copy from the original table, plus some extra columns such as

 

  • AuditId (Primary key auto generated by DB (Identity))
  • OperationType : I for Insert, D for delete, U for update

 

So lets have a look at some of this using some code example

 

SQL Server Table(s)

Let say I have the following table called “Deposit” in SQL Server

 

Deposit Table (source for Audit)

 

CREATE TABLE [dbo].[Deposit](
      [Id] [int] IDENTITY(1,1) NOT NULL,
      ….
      ….
      ….
      [Version] [timestamp] NOT NULL
CONSTRAINT [PK_Deposit] PRIMARY KEY CLUSTERED
(
      [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

 

And I also have the following Audit table

Deposit_Audit table

 

CREATE TABLE [dbo].[Deposit_Audit](
      [AuditId] [int] IDENTITY(1,1) NOT NULL,
      [OperationType] [nvarchar](1) NOT NULL,
      [Id] [int] NOT NULL,
      ….
      ….
      ….
      ….
CONSTRAINT [PK_Deposit_Audit] PRIMARY KEY CLUSTERED
(
      [AuditId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

 

The Fluent NHibernate Entities

So lets say I have a simple class called “Deposit” which looks like this. Note that for Fluent NHibernate/NHiberate to do their magic properties MUST be virtual

 

using System;
namespace Entities
{
    public class Deposit : Entity<int>
    {
        //NOTE : Do not remove this, as NHibernate needs default ctor to allow proxying
        public Deposit() { }
        public virtual int Id { get; set; }
        ….
        ….
        ….
        ….
        public virtual byte[] Version { get; set; }
    }
}

 

The Fluent NHibernate Mapping Files

Where I then have a Fluent NHibernate mapping file that looks like this

 

namespace Entities
{
    public class DepositMap : ClassMap<Deposit>
    {
        public DepositMap()
        {
            this.Schema("[dbo]");
            OptimisticLock.Version();
            this.Table("[Deposit]");
            this.Id(x => x.Id).Column("Id");
            ….
            ….
            ….
            Version(x => x.Version).Generated.Always();
        }
    }
}

 

 

The Trigger To Do The Audit

Then I have this trigger in place in SQL server to actually do the inserting of the data into the audit table

 

CREATE TRIGGER [dbo].[triDeposit_Audit]
   ON [dbo].[Deposit]
   AFTER INSERT, DELETE, UPDATE
AS

BEGIN

      DECLARE @rc AS INT;
      DECLARE @Id INT
      DECLARE @OperationType NVARCHAR(1)
      DECLARE @rcDel AS INT;
      DECLARE @rcInserted AS INT;

      SET @rc = (SELECT COUNT(*) FROM inserted);
      IF @rc = 0 RETURN;

      DECLARE @keycol AS INT;

      SELECT

            @keycol = ISNULL(inserted.Id, deleted.Id),
            @OperationType  =
            CASE

                              WHEN inserted.Id IS NULL THEN 'D' - we don't use this for now
                              WHEN deleted.Id IS NULL THEN 'I'
                              ELSE 'U'
            END
            FROM inserted
            FULL OUTER JOIN deleted
            ON inserted.Id = deleted.Id

     

      - single row
      IF @rc = 1

            BEGIN

                  INSERT INTO [Options].[dbo].[Deposit_Audit]

                  (    [OperationType]
                        ,[Id]
            		…..
            		…..
            		…..
            		…..
                  )             

                  SELECT     
                        @OperationType         
                	….
                	….
                	….
                	….
                  FROM [Options].[dbo].[Deposit] WHERE Id = @keycol
            END 
     

      - multiple rows
      ELSE
            BEGIN
                  SELECT DISTINCT * INTO #I FROM inserted;

                  BEGIN

                        INSERT INTO [Options].[dbo].[Deposit_Audit]
                        (
                           [OperationType]
                          ,[Id]
                            ……
                            ……
                            ……
                        )

                        SELECT     

                           'U'
                           ,[Id]
                            ……
                            ……
                            ……
                        FROM #I
                  END
            END  
END
GO

 

 

All good so far, so I then used my handy NHibernate ISession / Repository to do an INSERT, and then I got something that I was not expecting, I got this:

 

In NHibernate I got this error: “Batch update returned unexpected row count from update; actual row count: 2; expected: 1″. 

 

Yikes.

 

Turns out there is a simple fix for this, which to my mind was not obvious (even though I seem to recall seeing this before, and mindfully forgetting about it), you just need to include the following line at the start of your SQL Server trigger

 

SET NOCOUNT ON

 

By doing that you are saying that the code that runs inside the trigger will not effect the overall row count of the transaction. After adding this one line to my trigger everything worked as expected.

VS2013 Setup Project

I am fortunate enough to have been working on a big app for a while now, which means we have our deployment pretty sorted out, using a manner of different tools/scripts.

Then the other day I was asked to help out another team with a small bit of help, where they needed a small app written to allow them to analyse some data. The other team in question are not DEVS, so they need an installer really.

As I say I have not had to create an installer for quite a while now, I was obviously aware of the lack of “Setup Project” in VS2012/VS2013, but since I have not had a need for it lately I was like “meph”.

Until today.

Luckily help is at hand, there is a Visual Studio 2013 extension to get the ability to create Installer Projects in VS2013 again. God knows why Microsoft took that out in the first place, all that would have done is moved people to use other things such as

Anyway for those that want to use the old (probably familiar) VS Installer style project you can grab it from here:

https://visualstudiogallery.msdn.microsoft.com/9abe329c-9bba-44a1-be59-0fbf6151054d

Hopefully some of you will find that useful

 

NetMQ : Documentation is there….Phew

I have been beavering away over the past 2 weeks, creating the NetMQ documentation. I am pleased to announce that the docs have come along quite nicely and you can read them all here :

http://netmq.readthedocs.org/en/latest/

 

You can also read them directly at GitHub too :

https://github.com/zeromq/netmq/tree/master/docs

 

I actually prefer the formatting on GitHub, but “ReadTheDocs” is what we have. But at least you have choices.

I hope that helps you in some way in your journeys with NetMQ. Oh it is worth mentioning that there are a couple of pages still to do, which Doron himself will be doing. These pages are

  • Stream
  • Devices
  • Beacon
  • Scheduler

I think the docs I wrote should cover the most common cases though.

There was also one user who has offered to translate the docs into French, so you can expect some French version some time in the future too

Enjoy

 

 

 

NetMQ : Documentation II

I don’t know how many of you read my last post, but I have somehow managed to talk myself into becoming the official documentation writer for NetMQ. If you have not used NetMQ, it is a C# native port of ZeroMQ the socket library from outer space. I have really enjoyed learning about ZeroMQ (which I did by reading “The Guide”,  several times over) and playing around with NetMQ, where I even did a joint article about with NetMQs author Doron (who is a very smart guy indeed). Basically its a great library, and very interesting and lots of fun, and very light weight, with no dependencies at all.

Anway enough of the advertising, how is the documentation coming along? Well, over the past week I have made some good progress on the NetMQ documentation.

The following pages are now semi-ready (pretty much there truth be told)

Over the next week I hope to finish some more. I will notify you all when I have done some more, and again when it all gets released to become the final “official” docs.

If you have not used NetMQ I urge you to have a look, experiment, that is how I started, and now I love it, and can honestly see many places where it is a excellent fit

Anyway enjoy, over and out for now

Exporting A Html Canvas Data To Image

As Part of a bigger project I am doing right now, I wanted to be able to store data from a HTML Canvas as a byte[] and save it in Azure blog storage. I thought that this by iteself may make an interesting post. I have not posted up any code this time, but all you should need will be available as snippets here

So the first thing I did was start a new ASP MVC project, which gave me all that I needed to start

Next I added a changed Index.Html page to look like the following

@{
    ViewBag.Title = "Home Page";
}


@section Scripts {
    <script src="~/Scripts/Index.js"></script>
}

<br/>
<p>This small example saves the canvas to blob storage</p>
<div style="border:  1px solid black; margin: 10px;">
    <canvas id="canv" width="600" height="600"></canvas>
</div>
<br/>

<div id="savedImageDiv" style="display:none">
    <img id="savedImage" width="100" height="100"/>
</div>
<button type="button" id="saveCanvas" >Save Canvas TO Blob</button>

See how there is a Canvas element, and a initially hidden Div, where the Div contains an image. This Div will get shown and the image will get filled in with a valid Azure blog storage Url when the Canvas is saved to blob storage.

So how does this happen. There are not that many moving parts there is basically the following 4 things needed

  • An Azure storage account (you set this in the Web.Config), though if you want to try this out, you can just use the Azure storage emulator, and a reference to the Azure Storage dlls, which you can grab via Nuget
  • A page with a Canvas on it (shown above)
  • Some Javascript to post the canvas data over to a MVC controller
  • A controller
  • Lets have a look at each of these in turn

    The Web.Config

    You will need something like this in your app

    <?xml version="1.0" encoding="utf-8"?>
    <configuration>
    
      <appSettings>
    
        .....
        .....
        .....
    
        <!-- TODO : This would need to change to live azure value when deployed -->
        <add key="azureStorageConnectionString" value="UseDevelopmentStorage=true;" />
    
    
    
      </appSettings>
    
    
    .....
    .....
    .....
    </configuration>
    

    And you would need to install the following Nuget package “WindowsAzure Storage”

    Javascript

    With the pages html in place, we need some JavaScript to post the Canvas data to the server side controller action. This client side javascript is shown below

    $(document).ready(function () {
        
        fillCanvas();
    
        $("#saveCanvas").on("click", function () {
    
            saveCanvas();
    
        });
    });
    
    
    
    function saveCanvas() {
    
        var canvas = document.getElementById('canv');
        var imageData = canvas.toDataURL('image/jpeg', 1);
    
        $.ajax({
            type: "POST",
            url: "/Home/StoreImage",
            dataType: "json",
            data: { ImageData: imageData, ImageType: "jpeg" },
            success: function (data) {
    
                if (data.StoredOk) {
    
                    $('#savedImageDiv').show();
                    $('#savedImage').attr("src", data.Uri);
                    alert("success :-)");
                } else {
                    alert("fail :-(");
                }
    
                
            },
            error: function () {
                alert("fail :-(");
            }
        });
    }
    
    
    //Draws random stuff on the canvas
    function fillCanvas() {
        var canvas = document.getElementById('canv');
        var context = canvas.getContext('2d');
    
    
        context.fillStyle = "rgb(255,255,255)";
        context.fillRect(0, 0, 600, 600);
    
    
        context.fillStyle = "rgb(150,29,28)";
        context.fillRect(10, 10, 80, 80);
    
        context.fillStyle = "rgb(100,55,28)";
        context.fillRect(200, 200, 20, 80);
    }
    

    All that is then left to do is the server side (MVC Controller Action), which is shown below

    Server side code

    p>The server side controller action to save the canvas byte[] into blob storage is shown below. It will also return a JSON object which includes the Blob Uri is stored successfully. Then the javascript will examine this JSOn response, and decide whether to unhide the Div containing the image, and set the image Uri to the stored canvas data. Which is now stored in a Azure blob as an image.

    using System;
    using System.Collections.Generic;
    using System.Configuration;
    using System.IO;
    using System.Linq;
    using System.Web;
    using System.Web.Helpers;
    using System.Web.Mvc;
    
    using CanvasToBlob.Models;
    
    using Microsoft.WindowsAzure.Storage;
    using Microsoft.WindowsAzure.Storage.Blob;
    
    namespace CanvasToBlob.Controllers
    {
        public class HomeController : Controller
        {
    
            private readonly string azureStorageConnectionString;
            private readonly CloudStorageAccount storageAccount;
    
            public HomeController()
            {
                azureStorageConnectionString = 
    		ConfigurationManager.AppSettings["azureStorageConnectionString"];
    
                storageAccount = 
    		CloudStorageAccount.Parse(azureStorageConnectionString);
            }
    
    
            public ActionResult Index()
            {
                return View();
            }
    
    
            [HttpPost]
            public JsonResult StoreImage(ImageToStore imageToStore)
            {
                try
                {
    
                     CloudBlobClient blobClient = storageAccount.CreateCloudBlobClient();
     
                    // Retrieve a reference to a container. 
                    CloudBlobContainer container = blobClient.GetContainerReference("images");
                    container.CreateIfNotExists();
                    container.SetPermissions(
                       new BlobContainerPermissions
                       {
                           PublicAccess = BlobContainerPublicAccessType.Blob
                       });
    
                    CloudBlockBlob blockBlob = container.GetBlockBlobReference(
    			string.Format("{0}.{1}", Guid.NewGuid().ToString(), imageToStore.ImageType));
                    string marker = string.Format("data:image/{0};base64,", imageToStore.ImageType);
                    string dataWithoutJpegMarker = imageToStore.ImageData.Replace(marker, String.Empty);
                    byte[] filebytes = Convert.FromBase64String(dataWithoutJpegMarker);
    
                    blockBlob.UploadFromByteArray(filebytes, 0, filebytes.Length);
    
    
                    JsonImageResult result = new JsonImageResult(true, blockBlob.Uri.ToString());
                    return this.Json(result);
                }
                catch (Exception e)
                {
                    JsonImageResult result = new JsonImageResult(false, "");
                    return this.Json("");
                }
            }
        }
    }
    

    Anyway that is all there is too it, I hope you can use this somehow. Enjoy