Fun with the SQL Output clause

The other day I was brushing up on my SQL skills by watching some training videos on Pluralsight. As I was watching, the presenter showed how the Output clause works. After playing around with it some more I decide to do this blog post.

So lets get started.  First lets create a temp table to work with.

create table #temptable (
    ID int not null identity primary key,
    FirstName nvarchar(100) null,
    LastName nvarchar(100) null,
    Cartoon nvarchar(100) null
)

Now lets insert some data using the output clause as part of the insert.  Note the script below is using the Table Row Constructor syntax.  This becomes extremely powerful when used with the output clause.

insert into #temptable
output inserted.ID, inserted.FirstName, inserted.LastName, inserted.Cartoon
values
('Fred','Flintstone', 'The Flinsones'),
('Wilma','Flintsone', 'The Flinsones'),
('Pebbles','Flintstone', 'The Flinsones'),
('Dino','', 'The Flinsones'),
('Barney','Rubble', 'The Flinsones'),
('Betty','Rubble', 'The Flinsones'),
('Bamm-Bamm','Rubble', 'The Flinsones'),
('George','Jetson', 'The Jetsons'),
('Jane','Jetson', 'The Jetsons'),
('Judy','Jetson', 'The Jetsons'),
('Elroy','Jetson', 'The Jetsons')

When this script is ran in SQL it will insert the rows into the temp table and return a result set that looks like this:

ID	FirstName	LastName	Cartoon
1	Fred	Flintstone	The Flinsones
2	Wilma	Flintsone	The Flinsones
3	Pebbles	Flintstone	The Flinsones
4	Dino		The Flinsones
5	Barney	Rubble	The Flinsones
6	Betty	Rubble	The Flinsones
7	Bamm-Bamm	Rubble	The Flinsones
8	George	Jetson	The Jetsons
9	Jane	Jetson	The Jetsons
10	Judy	Jetson	The Jetsons
11	Elroy	Jetson	The Jetsons

The inserting of data and the returned result set is done as a single transaction or round trip to the server.  The other benefit to this is the result set included the ID (identity) of each row.  Normally I would have re-queried the data to get this information.

Now let me show you an update using the output clause.  In the script below I am fixing the misspelling of Flintstone.

update #temptable
set Cartoon = 'The Flintstones'
output inserted.ID, deleted.Cartoon OldCartoonValue, inserted.Cartoon NewCartoonValue
where Cartoon = 'The Flinsones'

When the script is ran in SQL it will update the rows and return a result set that looks like this:

ID	OldCartoonValue	NewCartoonValue
1	The Flinsones	The Flintstones
2	The Flinsones	The Flintstones
3	The Flinsones	The Flintstones
4	The Flinsones	The Flintstones
5	The Flinsones	The Flintstones
6	The Flinsones	The Flintstones
7	The Flinsones	The Flintstones

Note that once again a single transaction to the server and a result set that references the ID’s that where updated along with the old cartoon value and the new cartoon value.  Keep reading below to learn more about the inserted and deleted column prefixes.

Now let me show you a delete with the output clause.  In this script below I am deleting any rows that have the last name of “Rubble”.

delete #temptable
output deleted.ID, deleted.FirstName, deleted.LastName, deleted.Cartoon
where LastName = 'Rubble'

When the script is ran in SQL it will delete the rows and return a result set that looks like this:

ID	FirstName	LastName	Cartoon
5	Barney	Rubble	The Flintstones
6	Betty	Rubble	The Flintstones
7	Bamm-Bamm	Rubble	The Flintstones

Not that once again a single transaction to the server and a result set that references the rows that where deleted.  Imagine if you used this in some sort of logging situation.

When using the output clause there are two special column prefixes to use:

• Deleted: Is a column prefix that specifies the value added by the insert or update operation. Columns prefixed with INSERTED reflect the value after the UPDATE, INSERT, or MERGE statement is completed but before triggers are executed. (DELETED cannot be used with the OUTPUT clause in the INSERT statement.)
• Inserted: Is a column prefix that specifies the value added by the insert or update operation. Columns prefixed with INSERTED reflect the value after the UPDATE, INSERT, or MERGE statement is completed but before triggers are executed.(INSERTED cannot be used with the OUTPUT clause in the DELETE statement.)

Getting custom extension methods to work in ASP.NET MVC 3 & Razor View Engine

I am going through the process of converting my site to use ASP.NET MVC 3 with the new razor view engine.  I use many extension methods that I have added to the HtmlHelper.  I store all these helpers in the following namespace “DevDave.Infrastructure.Helpers”.

In MVC 2 I added the namespace “DevDave.Infrastructure.Helpers” to the web.config file in the root of my MVC project like so:

<pages>
  <namespaces>
    <add namespace="System.Web.Mvc" />
    <add namespace="System.Web.Mvc.Ajax" />
    <add namespace="System.Web.Mvc.Html" />
    <add namespace="System.Web.Routing" />
    <add namespace="DevDave.Infrastructure.Helpers"/>
  </namespaces>
</pages>

This allowed me to use my custom helpers in all of my pages. So naturally I figured that the same thing would work for the MVC 3 application.  As it turns out it doesn’t, I get this stupid error instead:

As you can see it can’t seem to find my extension method “MainMenu”.  Now I did figure out that you could just add a using statement to the top of each page like this:

@using DevDave.Infrastructure.Helpers

Well I don’t want to have to put this in every view so then I tried to add the using statement into the _ViewStart file in the root of the views folder.  Like so:

@{
    using DevDave.Infrastructure.Helpers;
    
    Layout = "~/Views/Shared/_Layout.cshtml";
}

This did not work either something about having a using statement inside a code nugget.  So I moved it to its own line like so:

@using DevDave.Infrastructure.Helpers

@{
    Layout = "~/Views/Shared/_Layout.cshtml";
}

This still did not work.

To fix this I had to add the name space to the web.config inside of the views folder instead.  Like so:

<system.web.webPages.razor>
  <host factoryType="System.Web.Mvc.MvcWebRazorHostFactory, System.Web.Mvc, Version=3.0.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" />
  <pages pageBaseType="System.Web.Mvc.WebViewPage">
    <namespaces>
      <add namespace="System.Web.Mvc" />
      <add namespace="System.Web.Mvc.Ajax" />
      <add namespace="System.Web.Mvc.Html" />
      <add namespace="System.Web.Routing" />
      <add namespace="DevDave.Infrastructure.Helpers"/>
    </namespaces>
  </pages>
</system.web.webPages.razor>

This fixed the problem.

Getting Ninject to work with MVC 3

Before getting started lets make sure everyone is on the same page.  The screenshots and descriptions below assume the use of the following products and tools.

• Visual Studio 2010
• ASP.NET MVC 3
• .NET 4.0 framework

Lets start by creating a new ASP.NET MVC 3 Web Application

Lets go ahead and choose the Empty template.  We can bypass using a Test Project at this time.

Once the solution is created lets add the following folder structure: