Only creating a schema, when it does not exist in a Microsoft SQL Server database.

If you want to create a schema in a Microsoft SQL Server database only when it does not exist, use:

declare @SchemaName nvarchar(128)
set @SchemaName = 'Staging'
if not exists(select top 1 1 from information_schema.schemata where schema_name = @SchemaName)
begin
exec('create schema ' + @SchemaName)
end
go

Fix / restore user and login mappings in SQL Server

If you restore a SQL Server (< 2012) database on an other database server make sure you fix the user and login mappings by following the steps on http://atakala.com/browser/Item.aspx?user_id=amos&dict_id=2036

Short fix:

use MyDatabase

go

sp_change_users_login ‘update_one’, ‘myusername’, ‘myloginname’

 

Note

On SQL Server 2012 you can use Contained Databases, see http://blog.master-it.nl/blog00224/SQL_Server_2012_Contained_Databases_geen_Orphaned_Users_meer.htm

How to fix: Can’t drag and drop custom Team Foundation Build Activity on XAML build process template in Microsoft Visual Studio 2010.

A .NET 4.0 assembly containing a custom Team Foundation Build Activity can only be dragged and dropped from the toolbox on your build process template XAML file, when it is first registered in the GAC. For registering a .NET 4.0 assembly in the GAC you will have to use the gacutil.exe for .NET 4.0 found at [C:\Program Files (x86)\Microsoft SDKs\Windows\v7.0A\Bin\NETFX 4.0 Tools\gacutil.exe].

If you don’t register the assemlby in the GAC you can’t drag and drop the Team Foundation Build Activity on the build process template XAML file. If it was added by an other developer, you get the error: "Activity could not be loaded because of errors in the XAML":

 

image

 

 

After registering the .NET 4. 0 assembly with the gacutil.exe, the assembly can be found at [C:\Windows\Microsoft.NET\assembly\GAC_MSIL].

After registering, make sure you restart Microsoft Visual Studio 2010 before you drag and drop the custom Team Foundation Build Activity on your build process template XAML file.

How to correctly use select top 1 inside a inner join of an update statement in T-SQL.

And the answer is: don’t use select top 1, instead use the row_number() function and the OVER clause.

Lets assume we have a table structure like:

 

 

Table [Observation]

Multiple observations during a TimeSpan on 1 barcode are collected into 1 [Observation] record.

 

if object_id('[dbo].[Observation]') is null
begin
create table [dbo].[Observation]
(
[Id]                int not null identity(1,1)  constraint PK_Observation_Id primary key,
[Barcode]            varchar(max) not null,
[Postcode]        varchar(max) not null,
[Date]            date not null,
[Count]            int not null,
[TimeSpanId]        int not null constraint FK_Observation_TimeSpanId foreign key references TimeSpan(Id)
)
end
go
if not exists(select top 1 1 from [Observation])
begin
insert into [dbo].Observation([Barcode],[Postcode],[Date],[Count],[TimeSpanId]) values ('NN10000','4000AA','2012-06-18',1,1)
insert into [dbo].Observation([Barcode],[Postcode],[Date],[Count],[TimeSpanId]) values ('NN10001','4000AA','2012-07-02',1,1)
insert into [dbo].Observation([Barcode],[Postcode],[Date],[Count],[TimeSpanId]) values ('NN20000','4000AA','2012-05-29',2,2)
insert into [dbo].Observation([Barcode],[Postcode],[Date],[Count],[TimeSpanId]) values ('NN30000','4000AA','2012-06-18',2,3)
end

Table [TimeSpan]

The table TimeSpan contains timespans of 2 hours.

if object_id('[dbo].[TimeSpan]') is null
begin
create table [dbo].[TimeSpan]
(
[Id]                int not null identity(1,1)  constraint PK_TimeSpan_Id primary key,
[Name]            varchar(max),
[Start]            time(7),
[End]                time(7),
[IsMostImportant] int not null default(0)
)
end
go
if not exists(select top 1 1 from [TimeSpan])
begin
insert into [dbo].[TimeSpan]([Name],[Start],[End],[IsMostImportant]) values ('07:00 - 09:00','07:00:00','09:00:00',0)
insert into [dbo].[TimeSpan]([Name],[Start],[End],[IsMostImportant]) values ('10:00 - 12:00','10:00:00','12:00:00',0)           
insert into [dbo].[TimeSpan]([Name],[Start],[End],[IsMostImportant]) values ('13:00 - 15:00','13:00:00','15:00:00',0)
end

The most important TimeSpan

The first TimeSpan with the most- and youngest observations.

 

Update Query

The column [IsMostImportant] can be updated by using the following query:

declare    @DateNow date
set @DateNow = '2012-07-12'
set nocount on                                -- SET NOCOUNT ON, added to prevent extra result sets from interfering with SELECT statements.
set datefirst 1                               -- Monday is first day of the week.
update [TimeSpan]
set [IsMostImportant] = 1
from [TimeSpan] ts
inner join
(
select
ob3.[RowNumber],
ob3.[TimeSpanId]
from
(
select
row_number() over(partition by ob2.[Postcode] order by ob2.[Count] desc, ob2.[DateDiffInDays] asc) as [RowNumber],
[Count],
[DateDiffInDays],
[Postcode],
[TimeSpanId]
from 
(
select
sum(ob.[Count]) as [Count],
sum(datediff(day, ob.[Date], @DateNow) * ob.[Count]) as [DateDiffInDays],
ob.[Postcode],
ob.[TimeSpanId]
from Observation ob
inner join TimeSpan ts on ob.TimeSpanId = ts.Id
group by ob.[Postcode], ob.[TimeSpanId]
) ob2
) ob3
where ob3.[RowNumber] = 1
) ob4
on ts.[Id] = ob4.[TimeSpanId]

 

Result

image

Convert string to bytes and bytes to string without encoding

I wanted to save information found in a file to a database without having to deal with encodings, well I found my solution at

http://stackoverflow.com/questions/472906/net-string-to-byte-array-c-sharp

 

    [TestClass]
public class RliResearchTester
{
[TestMethod]
public void Test()
{
string input = "Hèllo world";
byte[] inputBytes = GetBytes(input);
string output = GetString(inputBytes);
Assert.AreEqual(input, output);
}
public byte[] GetBytes(string str) 
{ 
byte[] bytes = new byte[str.Length * sizeof(char)];
System.Buffer.BlockCopy(str.ToCharArray(), 0, bytes, 0, bytes.Length);
return bytes;
}
public string GetString(byte[] bytes) 
{ 
char[] chars = new char[bytes.Length / sizeof(char)];
System.Buffer.BlockCopy(bytes, 0, chars, 0, bytes.Length);
return new string(chars);
}
}

Microsoft TechEd Europe 2012

After every Microsoft TechEd, I am overwhelmed by the new possibilities. Microsoft TechEd Europe 2012 in Amsterdam was no exception. You always learn new stuff, things you didn’t know they exists or hints to which direction Microsoft is taking.

 

Disclaimer

Because of the brain overload after the TechEd, some notes might not be entirely correct, so please lookup the terms if you are interested in some described technique.

 

DevOps

In many companies the development role and operations are strictly separated, but now there is a trend these roles blur or in many cases a team consist of developers and IT professionals. I think this is a good thing, in doing so, the team is made responsible for the complete application lifecycle management.

BYOD

Bring your own device was a big thing on the TechEd. They showed a lot of improvements they have done to allow people to bring there own devices in to the corporate network and allowing administrators to have control over these device. Or the other way around with Windows as you go, just install Windows on a USB stick and plug this stick in to your computer at home, now the OS on the stick can use the CPU, Memory etc from the home computer, but it cannot access the hard drives from that computer, this ensures the OS on the USB stick is isolated from “evil” things sitting on the home computer. An other improvement is the fact, that apps on android or iOS are now able to authenticate against active directory without these devices joining a domain.

 

Out of band deployment with NuGet

As we move in to the future, more and more parts of the .NET framework will ship as NuGet packages. This allows for a faster development cycle. New version of ASP .NET don’t have to wait on the next .NET version. This does not only apply to ASP .NET but also other parts like Entity Framework and maybe even WinForms. Because NuGet allows you to target previous versions, new versions can introduce breaking changes, without impacting the previous releases.

 

One ASP .NET

Just start a new project with an empty template and add features by using NuGet. Mix and match web forms with entity framework or even use MVC en web forms in one project.

 

Async and Await

At first I thought what is all that fuss on async and await, I can do asynchronous programming since .NET1.1, but after seeing it in use this is really, really nice. It will make code easier to understand, because you can write code in the way you think (synchronously). An other nice feature: Await will return on the UI thread. As a developer you don’t have to worry about marshaling back to the UI thread.

Even on the server it can be used to free the threads that handle the incoming request. Overall it’s a technique to be used, when you expect an operation to take some time or requires a lot of resources.

Async and Await can now also be used in unit tests, just decorate your test method with the "async" keyword and you can use await in your test.

 

WCF vs ASP.NET web api vs SignalR

To communicate from a client app to a server, there are many techniques you can use, 3 of them are WCF, ASP.NET web api and SignalR.

WCF: Best used, when specific bindings are needed, like Microsoft Message Queue, TCP binary transport etc. In the past this was the technique I would use, but moving in to the future I will be using Microsoft web api more and more. 

ASP .NET Web Api: best used when creating clients apps that will communicate to REST services by using JSON or XML

SignalR: best used when real time communication / push notification is required. I think it will play create with cloud based solutions and I think it has the potential to be the communication technique of the future. It will even play nice with Note.js, iOS, Android, Windows 8, Windows Phone 8 etc. SignalR can use web sockets which as we know can be used through firewalls and can be used with load balancers to supply a persistent full duplex connection. This allows for real time communication. It is now possible to serve 40.000 concurrent persistent connections on one server and the goal of the developers is 100.000 concurrent persistent connections. This is really mind blowing, I have once got a server on his knees with 30 concurrent connections (well the software had some bugs that caused this situation, but then again…. 40.000 connections is a lot.) And above all, it scales like “piep”.

Let say, you want to update all apps, when a user closes it’s browser, this is possible with SignalR, showing all users in real time, user x has left the building.

 

XML vs JSON

In the past XML was the way to communicate with web services, moving to the future JSON will be technique I use to communicate to web services (where possible). It’s even possible to communicate to an ODATA service by using JSON. JSON is less bulky and serialization / deserializations tends to be easier and faster. Even some config files start to use JSON instead of XML.

General .NET 4.5 improvements

  • Reduced footprint, the overall .NET footprint is 40% smaller then the 4.0 footprint.
  • Automatic and smart ngen, generates native images for frequently used assemblies in the background.
  • For more information, see http://blogs.microsoft.co.il/blogs/sasha/archive/2011/09/17/improvements-in-the-clr-core-in-net-framework-4-5.aspx
  • WeakReference<T>, a weakreference is a way to tell the CLR: don’t keep this object around on my account,
    but for as long as something else needs it, I’d like to be able to get access to.
  • Reduced attributes in MEF, I think attributes have there place, but I don’t like attributes to be scattered around every where, so I think the reduction of attributes in MEF is a good thing.
  • SecureString, using encrypted strings in memory.
  • Never used it, but if you are developing for ARM make sure you use the volatile keyword, this ensure your variables are set before you use them, this has something to do with the difference in memory allocation between x86 and ARM.
  • OOB a lot faster then 4.0, so you van improve the speed of your apps just by compiling against .net 4.5.

WCF 4.5 improvements

  • Add reference will only generate the necessary config files.
  • svcutil /sc will generate a proxy class, like the old WSDL SOAP tool.
  • NetHttpBinding (MSDN: NetHttpBinding is a binding designed for consuming HTTP or WebSocket services and uses binary encoding by default. Note: WebSockets are only supported on Windows 8.)
  • UDP support. This enables broadcasting.

Microsoft Visual Studio 2012 improvements

  • Round-trip development with Microsoft Visual Studio 2010. This will be the first time the solution and project files of a new Microsoft Visual Studio version are compatible with a previous version. This allows members of a team to have different versions of Microsoft Visual Studio and work on the same project.
  • Remote desktop from visual studio, so you don’t have to leave Microsoft Visual Studio and you can store a list of different remote desktop connection.
  • CSS, smart color editor.
  • No more support for msi packages. Microsoft wants you to create Windows 8 Metro style apps and they have there own deployment mechanism and this will be the future of deployment, good by setup projects and *.msi packages.

Project Roslyn

Compiler as a service, well I think it was after the TechEd in Barcelona in 2007 that I heard of project roslyn and know it will ship with Microsoft Visual Studio 2012 and in the next version (Microsoft Visual Studio 2014) it will be the default compiler. It allows you to “new up” an instance of the compiler class and then you will be able to generate and use this generated code from within a .NET application. Think of the possibilities, they are endless!

MVC4

  • Because it maps directly to the HTTP verbs it seems a better fit to HTTP then for example web forms:
    • GET       (select / query)
    • POST     (insert)
    • PUT       (update)
    • DELETE  (delete)
    • HEAD     (header info)
  • It used JSON.NET for serialization and deserialization to and from JSON.
  • Can use content negotiation, this allows one services to service the same content as XML or JSON based on the client request.
  • Single page web api: http://www.asp.net/single-page-application. Create a single page web app.
  • Apply AOP with MVC4 action- and authentication filters.
  • MVC can ben deployed as a combined UI / Service layer or only the UI or only the Service layer.
  • Validation on controls instead of whole page, so a html editor on a website can sent "unsafe" code to the server, while all other controls are validated.
  • Easy support for OpenID and OAuth. (authentication for Google, Facebook, twitter etc.)

Entity Framework improvements

  • Not new, but for me the first time I saw Entity Framework migrations at work and I was impressed. Updating the database schema based on changes on the POCO classes even letting you to create a T-SQL script of these changes. Rolling back en forward between different versions of the database, nice.
  • A Seed method to enter automatically enter test data or lookup data into your database.
  • Support for spatial data
  • Support for table valued functions
  • Support for enums
  • Coming to Windows Phone

Microsoft TFS improvements

  • Time tracking on TFS work items is not supplied OOB but it can be accomplished by subscribing to the TFS event model.
  • Within Microsoft every night, 17TB of code is compiled and tested, that’s a lot Winking smile
  • Microsoft now offers TFS service in the cloud for every body, sign up for free. What intrigues me, is the pace on which they can update this service, as I understand the service is updated every week with code delivered for RTM, 3 week earlier.
  • Finally a nice customizable digital Kanban board.

Microsoft IIS 8 improvements

  • CPU throttling bases on load. So a process can take 100% of the CPU, when there are no other user requests, but when new user request come in, this processes can be throttled to 30% CPU usage.
  • Dynamic IP restrictions. Block or log ip addresses based on the number of request over time. Even if these IP addresses are behind proxies. The stack in the HTTP headers will be walked until the root client is found. The dynamic ip restrictions can reduce the impact of DDOS attacks.
  • Automatic website warm-up: a static HTML page can be shown as long as the web site is in it’s startup face or when you are using a load balancer an other server can serve request as long as the web site is in it’s startup face, then you will be able to recycle application pools without impacting availability.

WinRT (Windows Runtime)

Think of WinRT as the managed counter part of the Win32 api. So you can access system resources right from the metal by using managed code without the dreadful p-invoke. Interact with Windows on a managed way. Native HTML5 apps will come to Windows Phone, because Windows Phone will be using WinRT and IE10.

Hyper-V

  • 1.000.000 ops on one server is impressive.
  • It seems to me that it is becoming a real option for the VMWare products.

Small developer tips

  • Lambdas, just learn to use and love them, if you aren’t familiar with them.
  • Scaffolding, is a technique used to generate database access code, that can be used to apply curd operations on the generated database entities.
  • Use Task.Delay.Sleep (non blocking operation, but with some overhead) instead of Thread.Sleep (blocking operation).
  • Automatic documentation generation of web services api with ASP .NET IApiExplorer.
  • Less, extends CSS with dynamic behavior such as variables, mixins, operations and functions http://lesscss.org/
  • Some interesting stuff I have to look at:  gherkin, specflow, speclog, Microsoft StreamInsight, DataSift and rx.net

 

And the list goes on and on….. Winking smile

 

I didn’t mention Microsoft Visual Studio LightSwitch with the HTML client and improvements on Microsoft Azure, because that will be separate blog posts.

Continuous testing with Mighty Moose and Microsoft Visual Studio

A colleague pointed me to Mighty Moose. It’s a FREE plugin for Microsoft Visual Studio and it can be used for continuous testing. CTRL+S, not only saves your code, but also builds and runs al unit tests that were impacted by the code change.

http://codebetter.com/gregyoung/2012/06/23/mighty-moose-free/

This is really very nice!

TIP: Reassign keyboard shortcuts, when using TDD with Microsoft Visual Studio and TestDriven.Net

If you are using TDD in Microsoft Visual Studio 2010 and apply that red – green – refactor thingie, you want your tests to execute as fast as possible. Well MSTEST in Microsoft Visual Studio 2010 is known to be a snail, so for TDD in Microsoft Visual Studio 2010 you probably want to use external tools like ReSharper, TestDriven.Net  etc.

 

To further improve the speed I reassign the keyboard shortcuts:

  • CTRL + Q to run the current test(s) in context without debugger.
  • CTRL + W to run the current test(s) in context with debugger.

image

 

Notes

  • By the way, in Microsoft Visual Studio 2012 there is a tremendous performance improvement in running unit test, so you might want to look at that.
  • An other tip: don’t use CTRL + S (to save code), instead use CTRL + Q to save, compile and run tests on your code at once.

How to set DropDownList.SelectedValue for unit testing in ASP .NET web forms.

If you want to set the [SelectedValue] property of a DropDownList in a unit test, you must first add items to the [Items] property of the DropDownList. If you don’t add items, setting the SelectedValue to a value will not persist.

[TestMethod]
public void TestMethod1()
{
var dropDownList = new DropDownList();
dropDownList.SelectedValue = "First item";
Assert.AreEqual(string.Empty, dropDownList.SelectedValue);
dropDownList.Items.Add("First item");
Assert.AreEqual("First item", dropDownList.SelectedValue);
}