My Adventures in Coding

February 6, 2015

IIS – PowerShell script to add IIS URL Rewrite Rule

Filed under: IIS — Brian @ 11:50 am
Tags: , , , , , , , ,

We have a Java REST API that runs on the same server as a .NET application. We only allow port 80 to be open on our web server. For this reason we needed to create a URL Rewrite Rule in IIS to redirect external traffic to the REST API on port 80 to port 81. It is fairly easy to do this setup in IIS, however we always want to have all of our server setup scripted. So here is a simple PowerShell script to create a URL Rewrite rule:

In this example the application is called “fddapi” and the root of the application path is “/fddapi” which is the pattern we are looking for in the URL Rewrite rule.

$site = "iis:\sites\Default Web Site"
$filterRoot = "system.webServer/rewrite/rules/rule[@name='fddapi$_']"
Clear-WebConfiguration -pspath $site -filter $filterRoot
Add-WebConfigurationProperty -pspath $site  -filter "system.webServer/rewrite/rules" -name "." -value @{name='fddapi' + $_ ;patternSyntax='Regular Expressions';stopProcessing='False'}
Set-WebConfigurationProperty -pspath $site  -filter "$filterRoot/match" -name "url" -value "(fddapi)/.*"
Set-WebConfigurationProperty -pspath $site  -filter "$filterRoot/conditions" -name "logicalGrouping" -value "MatchAny"
Set-WebConfigurationProperty -pspath $site  -filter "$filterRoot/action" -name "type" -value "Rewrite"
Set-WebConfigurationProperty -pspath $site  -filter "$filterRoot/action" -name "url" -value "http://localhost:8081/{R:0}"

After running the script your new URL Rewrite rule should now be created with the following settings:


 

January 11, 2015

Java to Sql Server – Cannot create PoolableConnectionFactory

Filed under: Uncategorized — Brian @ 11:14 pm

If you are connecting a Java application to a SQL Server database, which is up and running, but your connection fails with the following error:

Could not acquire a connection from DataSource – Cannot create PoolableConnectionFactory

The error is most likely that the windows service “SQL Server Browser” is disabled.

To fix the problem:

  • Start -> Control Panel -> Systems & Security -> Administrative tools -> Services
  • SQL Server Browser -> right click -> properties

image2014-10-30 19-17-34

  • Set Startup type to “Automatic”
  • Apply -> Start

image2014-10-30 19-19-16

Now you should be able to connect to SQL Server from your Java application!

IIS – Setup IIS as a proxy to Jetty

Filed under: .NET,IIS — Brian @ 12:35 am
Tags: , ,

On my current project we are working on two applications for a customer, one is in .NET and the other is in Java. The client only wants to have port 80 and 443 open on the server, however, we will need to have two web servers running, IIS for the .NET application and Jetty for the Java application. Our solution was to run the .NET application on IIS on port 80 and the Java application on Jetty on port 81, then have IIS route traffic for the Java application coming in on port 80 to port 81 using IIS as a proxy to Jetty. The following is a simple tutorial on how to setup IIS as a proxy to Jetty.

Install Application Request Routing (ARR)

Setup IIS to Jetty Redirect

For this example the route for the Jetty application will be “fddapi”.

Open IIS and on the Server¬†select “Application Request Routing” icon.

1-Open_AAR_from_Default_Website

Select “Server Proxy Settings”.

2-Server_Proxy_Settings

Set “Enable Proxy” setting and “Apply” the change.

3-Enable_Proxy_Setting

Go to “Default Web Site”, select “URL Rewrite”.

4-Select Url ReWrite

Under the Actions menu select “Add Rules”.

5-Add_Rule

Under “Inbound Rules” select “Blank Rule” and click “Ok”.

6-Blank_Rule

Edit the Inbound Rule with a pattern to match on and how to rewrite the url.

7-Edit_Inbound_Rule

Test the Setup

Now open a browser and go to the url: http://localhost/fddapi/someroute which should work now without needing to specify port 8081.

July 30, 2014

Java – Creating a simple retry command with function passing in Java 8

Filed under: Java — Brian @ 8:45 am
Tags: , , ,

Recently we have been working on an application that imports data from a number of different sources, where the network connection between us and each of these sources is not very reliable. So in our Gateway that makes these REST calls I wanted to be able to write a reusable piece of code that we could use in different calls, that in the event of a failure, would retry the command a few more times before finally giving up.

Java 7
I wanted to be able to write this retry logic and error handling code in one place and use it for a number of different method calls. There are several ways to do this, but previously in Java 7 I would have just written an abstract class with a single abstract method such as:

public abstract class RetryCommand<T> {
    private int maxRetries;

    public RetryCommand(int maxRetries) {
        this.maxRetries = maxRetries;
    }

    // This abstract command is the method that will be implemented 
    public abstract T command();

    public final T run() throws RuntimeException {
        try {
            return command();
        } catch (Exception e) {
            return retry();
        }
    }

    private final T retry() throws RuntimeException {
        System.out.println("FAILED - Command failed, will be retried " + maxRetries + " times.");
        int retryCounter = 0;
        while (retryCounter < maxRetries) {
            try {
                return command();
            } catch (Exception e) {
                retryCounter++;
                System.out.println("FAILED - Command failed on retry " + retryCounter + " of " + maxRetries + " error: " + ex );
                if (retryCounter >= maxRetries) {
                    System.out.println("Max retries exceeded.");
                    break;
                }
            }
        }
        throw new RuntimeException("Command failed on all of " + maxRetries + " retries");
    }
}

Then in my Gateway code, for each method that I want to wrap with my retry logic I would just do the following:

public class MyGateway {
    private RetryCommand<String> retryCommand;
    public MyGateway(int maxRetries) {
        retryCommand = new RetryCommand<>(maxRetries);
    }

    // Inline create an instance of  the abstract class RetryCommand
    // Define the body of the "command" method
    // Execute the "run" method and return the result
    public String getThing(final String id) {
        return new RetryCommand<String>() {
            public String command() {
                return client.getThatThing(id);
            }
        }.run();
    }
}

The reason for this layout was I could not pass a function as a parameter to a method, like I have done in Scala, Python, and C#. However, now that we have Java 8, we can finally pass functions as parameters using the handy features in java.util.function package!

Java 8
Java 8 uses Functional Interfaces, which are interfaces with a single abstract method. The package java.util.function defines a number of standard functional interfaces, so most of the time you will be able to use one of these. Some example functional interfaces are Function (function with return value and input param), Supplier (function with return value but no input param), and Consumer (function with input param but no return value). However, if one of these standard functional interfaces does not meet your needs you can always define your own. In the following example I used Supplier.

So now in Java 8 I would create a new RetryCommand class that has a “run” method which takes in a function:

import java.util.function.Supplier;

public class RetryCommandJava8<T> {
    private int retryCounter;
    private int maxRetries;

    public RetryCommandJava8(int maxRetries)
    {
        this.maxRetries = maxRetries;
    }

    // Takes a function and executes it, if fails, passes the function to the retry command
    public T run(Supplier<T> function) {
        try {
            return function.get();
        } catch (Exception e) {
            return retry(function);
        }
    }

    public int getRetryCounter() { return retryCounter; }

    private T retry(Supplier<T> function) throws RuntimeException {
        System.out.println("FAILED - Command failed, will be retried " + maxRetries + " times.");
        retryCounter = 0;
        while (retryCounter < maxRetries) {
            try {
                return function.get();
            } catch (Exception ex) {
                retryCounter++;
                System.out.println("FAILED - Command failed on retry " + retryCounter + " of " + maxRetries + " error: " + ex );
                if (retryCounter >= maxRetries) {
                    System.out.println("Max retries exceeded.");
                    break;
                }
            }
        }
        throw new RuntimeException("Command failed on all of " + maxRetries + " retries");
    }
}

So now in my gateway code, I would create my fancy new retry command executer:

public class MyGateway {
    private RetryCommandJava8<String> retryCommandJava8;
    public MyGateway(int maxRetries) {
        retryCommandJava8 = new RetryCommandJava8<>(maxRetries);
    }

    // Passing function using a Lamba expression 
    public String getThing(final String id) {
        return retryCommandJava8.run(() -> client.getThatThing(id));
    }
}

Also, this setup is fairly easy to unit test, here are some example tests:

import junit.framework.TestCase;

public class RetryCommandJava8Test extends TestCase {

    public String SUCCESS = "success";
    public int MAXRETRIES = 3;
    public int SECONDSTOWAIT = 0;
    RetryCommandJava8<String> retryCommandJava8;

    public void testRetryCommandShouldNotRetryCommandWhenSuccessful() {
        retryCommandJava8 = new RetryCommandJava8<>(MAXRETRIES, SECONDSTOWAIT);

        String result = retryCommandJava8.run(() -> SUCCESS);

        assertEquals(SUCCESS, result);
        assertEquals(0, retryCommand.getRetryCounter());
    }

    public void testRetryCommandShouldRetryOnceThenSucceedWhenFailsOnFirstCallButSucceedsOnFirstRetry() {
        retryCommand = new RetryCommandJava8<>(MAXRETRIES, SECONDSTOWAIT);

        String result = retryCommandJava8.run(() -> {
            if (retryCommand.getRetryCounter() == 0) throw new RuntimeException("Command Failed");
            else return SUCCESS;
        });

        assertEquals(SUCCESS, result);
        assertEquals(1, retryCommand.getRetryCounter());
    }

    public void testRetryCommandShouldThrowExceptionWhenMaxRetriesIsReached() {
        retryCommandJava8 = new RetryCommandJava8<>(MAXRETRIES, SECONDSTOWAIT);

        try {
            retryCommand.run(() -> {throw new RuntimeException("Failed");});
            fail("Should throw exception when max retries is reached");
        } catch (Exception e) { }
    }
}

Of course this example is a stripped down version of what we use, which does waits between retries, back off retries, and proper logging of errors, etc. I just wanted to use a retry command as my example code for trying out function passing in Java 8. However, I hope maybe you will find this useful if you are trying to get a working example going for your first use of function passing in Java 8.

If you are new to Java 8 (just like I am) I recommend reading Everything About Java 8.

May 2, 2014

SQL Server – Simple Recursive Query Example

Filed under: SQL,SQL Server — Brian @ 2:50 pm
Tags: , , , ,

Every once in a while I need to write a recursive query in SQL Server and I always forget the syntax so I have to track down a simple example to help me remember. However, if you are trying to write a recursive query for the first time, I find some of the examples online to be a little bit too complicated. So I wanted to post an example, but also give you the script to create the table and populate it with data so you can see how it works and try it yourself. In this post I use the common example of a table with countries, states, and cities and where we want to get a list of all cities in a single country. Enjoy!

Create a table called “Area”:

CREATE TABLE dbo.Area(
   AreaID int NOT NULL,
   AreaName varchar(100) NOT NULL,
   ParentAreaID int NULL,
   AreaType varchar(20) NOT NULL
CONSTRAINT PK_Area PRIMARY KEY CLUSTERED 
( AreaID ASC
) ON [PRIMARY])
GO

Add some “Area” data:

INSERT INTO dbo.Area(AreaID,AreaName,ParentAreaID,AreaType)
VALUES(1, 'Canada', null, 'Country')

INSERT INTO dbo.Area(AreaID,AreaName,ParentAreaID,AreaType)
VALUES(2, 'United States', null, 'Country')

INSERT INTO dbo.Area(AreaID,AreaName,ParentAreaID,AreaType)
VALUES(3, 'Saskatchewan', 1, 'State')

INSERT INTO dbo.Area(AreaID,AreaName,ParentAreaID,AreaType)
VALUES(4, 'Saskatoon', 3, 'City')

INSERT INTO dbo.Area(AreaID,AreaName,ParentAreaID,AreaType)
VALUES(5, 'Florida', 2, 'State')

INSERT INTO dbo.Area(AreaID,AreaName,ParentAreaID,AreaType)
VALUES(6, 'Miami', 5, 'City')

If I do a select by AreaType “City”:

select * from dbo.Area
where AreaType = 'City'

I get both Saskatoon and Miami:

AreaID	AreaName	ParentAreaID	AreaType
4       Saskatoon       3            City
6       Miami           5            City

However, what if I wanted to return all cities in Canada?

You can accomplish this by doing a recursive select which uses a common table expression (CTE).

WITH AreasCTE AS 
( 
--anchor select, start with the country of Canada, which will be the root element for our search
SELECT AreaID, AreaName, ParentAreaID, AreaType
FROM dbo.Area 
WHERE AreaName = 'Canada'
UNION ALL 
--recursive select, recursive until you reach a leaf (an Area which is not a parent of any other area)
SELECT a.AreaID, a.AreaName, a.ParentAreaID, a.AreaType 
FROM dbo.Area a 
INNER JOIN AreasCTE s ON a.ParentAreaID = s.AreaID 
) 
--Now, you will have all Areas in Canada, so now let's filter by the AreaType "City"
SELECT * FROM AreasCTE  
where AreaType = 'City' 

Now we get back the following results for cities in Canada:

AreaID	AreaName	ParentAreaID	AreaType
4       Saskatoon       3               City

That’s it! Now we have written a simple recursive query!

March 6, 2014

SQL Server – Alter database in Single User mode to Multi User mode

Filed under: SQL,SQL Server 2005 — Brian @ 8:43 am
Tags: , , , ,

We have some test environment databases that get rebuilt nightly. The job that does the rebuild always switches the database into single user mode when it is being rebuilt. When the rebuild is finished it will switch it back to multi user mode. However, if there was a problem with a script and the job failed, it will leave the database in single user mode.

First, open a SQL Server Management Studio query window connected to database “master”.

The command to change the database back to multi user mode is:

ALTER DATABASE {InsertDatabaseNameHere} SET MULTI_USER WITH ROLLBACK IMMEDIATE
GO

However, if there is an existing process blocking this alter, you may get the error message:

“Transaction (Process ID 864) was deadlocked on lock resources with another process and has been chosen as the deadlock victim”

Since both the existing running process and the current request have the same deadlock priority, preference is given to the longer running process which is why your alter database command is chosen as the deadlock victim. So to deal with this when you run the command again, set the deadlock priority to HIGH. However, if that does not work, you will need to kill the existing connection.

To find the “spid” of the existing connection, you can use the following query:

SELECT sd.[name], sp.spid, sp.login_time, sp.loginame 
FROM sysprocesses sp 
INNER JOIN sysdatabases sd on sp.dbid = sd.dbid  
WHERE sd.[name] = 'DatabaseNameGoesHere'

As an alternative, you can also use the command “sp_who” to get the “spid” of the open connection:

exec sp_who

So, here is our revised command:

KILL SpidToKillGoesHere
GO
SET DEADLOCK_PRIORITY HIGH
GO
ALTER DATABASE DatabaseNameGoesHere SET MULTI_USER WITH ROLLBACK IMMEDIATE
GO

That should do it!

February 24, 2014

C# – Mocking a method to return a different value when called a second time using Moq

Filed under: .NET,c#,Mocking,Testing — Brian @ 2:36 pm
Tags: , , , ,

I have been using Moq as my primary mocking framework in C# for several years now. Overall I really like it, the syntax is easy to read, the mock setup is straight forward, etc. However, there are a few cases I need to mock infrequently and I always forget how to do the setup. Often because I am cycling between Scala, Python, and C#. So in this post I am just listing those three cases in hopes they may help others, yet at the same time as a reference to myself!

So for this example, first here is my example service that we will be mocking in our tests:

namespace MoqExample
{
    public interface ISomeService
    {
        SomeStuff GetNextStuff();
        void DoStuff();
    }

    public class SomeService : ISomeService
    {
        public SomeStuff GetNextStuff()
        {
            return new SomeStuff();
        }

        public void DoStuff()
        {
        }
    }

    public class SomeStuff
    {
        public int Id { get; set; }
        public string Name { get; set; }
    }
}

Return a different value the second time a mocked method is called

Let’s say I have a call in a service that returns the next item to process, such as pulling in customer requests from a queue that need to be processed in order, one at a time. In this case I might want to have a test that demonstrates the application can handle two different requests back to back, or even handle the case where there is an item to process, but the next item returned is empty, meaning there is nothing left to process. If I need to test this case there are several simple ways to do this with Moq.

The more well known approach is to just use a Queue and have the mock call Dequeue and return the result each time the mocked method is called.

[Test]
public void MogMethodThatReturnsADifferentValueWhenCalledASecondTimeUsingAQueue()
{
    Mock<ISomeService> _mockSomeService = new Mock<ISomeService>();
    var queueStuff = new Queue<SomeStuff>();
    queueStuff.Enqueue(new SomeStuff { Id = 1, Name = "Real" });
    queueStuff.Enqueue(null);
    _mockSomeService.Setup(x => x.GetNextStuff()).Returns(queueStuff.Dequeue);

    Assert.IsNotNull(_mockSomeService.Object.GetNextStuff());
    Assert.IsNull(_mockSomeService.Object.GetNextStuff());
}

The alternative is to use a feature in Moq called Sequences which allows you to set multiple return values, that will be returned one at a time in order, each time the mocked method is called.

[Test]
public void MogMethodThatReturnsADifferentValueWhenCalledASecondTimeUsingSequences()
{
    Mock<ISomeService> _mockSomeService = new Mock<ISomeService>();
    _mockSomeService.SetupSequence(x => x.GetNextStuff())
		    .Returns(new SomeStuff {Id = 1, Name = "Real"})
		    .Returns(null);

    Assert.IsNotNull(_mockSomeService.Object.GetNextStuff());
    Assert.IsNull(_mockSomeService.Object.GetNextStuff());
}

Throw an exception the first time a mocked method is called, return a value the second time

In this case I want to test that my application will handle the case that when a call to a service throws an exception, it will retry and if it receives a valid response on the second try, process the request successfully and continue. You can accomplish this by using a feature in Moq called “Callback”.

[Test]
public void MogMethodThatThrowsAnExceptionFirstTimeCalledAndAnObjectWithSecondTime()
{
    Mock<ISomeService> _mockSomeService = new Mock<ISomeService>();
    var calls = 0;
    _mockSomeService.Setup(x => x.GetNextStuff())
	.Returns(() => new SomeStuff {Id = 1, Name = "Real"})
	.Callback(() =>
	{
	    calls++;
	    if (calls == 1)
		throw new Exception("Failure");
	});

    Assert.Throws<Exception>(() => _mockSomeService.Object.GetNextStuff());
    Assert.IsNotNull(_mockSomeService.Object.GetNextStuff());
}

Now, it is true that you can use a Moq Sequence to return a different value each time a mocked method is called, but as far as I can tell you can only use this where the valid value is first and throwing an exception is the last item in the sequence. In my case above I explicitly wanted to test that an exception was thrown on the first call and a valid value was returned on the second call. However, if all you need to test in your code is how it handles a valid value on the first call and an exception being thrown on the second call, you can use a Sequence for your mock setup.

[Test]
public void MockMethodThatReturnsAnObjectFirstTimeCalledAndThrowsAnExceptionSecondTimeUsingSequences()
{
    Mock<ISomeService> _mockSomeService = new Mock<ISomeService>();
    _mockSomeService.SetupSequence(x => x.GetNextStuff())
	.Returns(new SomeStuff())
	.Throws<Exception>();

    Assert.IsNotNull(_mockSomeService.Object.GetNextStuff());
    Assert.Throws<Exception>(() => _mockSomeService.Object.GetNextStuff());  
}

Mock a void method to throw an exception

Lets say I have some void method that normally just silently does some task for me and has no need to have a return type, such as a call to write a stat or a log entry. However, if I want to test how my application handles the case when this call throws an exception, I can use the following setup to mock this method.

[Test]
public void MogMethodThatThrowsAnExceptionIsVoidAssertExceptionIsThrown()
{
    Mock<ISomeService> _mockSomeService = new Mock<ISomeService>();
    _mockSomeService.Setup(x => x.DoStuff()).Throws(new Exception("Failure"));

    Assert.Throws<Exception>(() => _mockSomeService.Object.DoStuff());
}

May 27, 2013

SQL Server – Check Index Fragmentation on ALL Indexes in a Database

Filed under: SQL,SQL Server 2005 — Brian @ 11:46 am
Tags: , , , ,

Often, when I am using a development or test environment and I run into a situation where a query is behaving slower than normal, first thing I want to rule out is “Do I have any fragmented indexes?”. Here is a simple query that will list every index on every table in your database, ordered by percentage of index fragmentation. This query will work on SQL2K5 or newer.

SELECT dbschemas.[name] as 'Schema', 
dbtables.[name] as 'Table', 
dbindexes.[name] as 'Index',
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
ORDER BY indexstats.avg_fragmentation_in_percent desc

That should do the trick!

March 27, 2013

C# – Using Unity for Dependency Injection in an ASP.NET MVC 4 Web API

Filed under: .NET,c#,MVC,REST — Brian @ 11:27 pm
Tags: , , ,

When I created my first REST API since I started working with MVC 4 Web API, immediately I wanted to figure out how to handle dependency injection. When I had previously worked in Java I used Spring, in Scala I used Google Guice, and so now I was on the hunt for another dependency injection tool. There are several options out there, such as Spring .NET and also Ninject, however for now we decided to go with Unity because of it’s simple setup and maturity.

With MVC 4 Web API the application creates an instance of the controller class whenever a REST call is made. So for example if you have the REST endpoint /cars it will create an instance of the controller CarsController to service the request. Now, this all works fine until you change the constructor on CarsController to take a parameter. Once you do this, you will need a dependency injection framework to handle knowing how to resolve the dependencies for CarsController when one is being created. So let’s use this as our example.

Note: For this example I am using:
Visual Studio 2012 Express Web and version 2.1.505.2 of the Unity NuGet package.

Now, in Visual Studio 2012 NuGet is built right in, so no need to install the NuGet Plugin anymore (Woohoo!)

Step 1 – Create a new Web API Project

Create a new Web API project in Visual Studio:

  • File -> New Project
  • Installed -> Templates -> Visual C# -> Web
  • Give the project the name: UnityExample
  • Click “OK”
  • Select Project Template -> Web API
  • Click “OK”

That’s all! Now you have a new Web API project called UnityExample.

Step 2 – Install Unity NuGet Package

For installing NuGet packages you can use either the GUI tool or the console.

To use the Package Manager Console tool:

  • To open the console: Tools -> Library Package Manager -> Package Manager Console
  • Now at the console prompt enter the command: Install-Package Unity

Now, if you prefer to use the GUI tool:

  • In Solution Explorer, right click on the solution and select Manage NuGet Packages for Solution
  • Ensure Online is selected, then in the search box type Unity
  • The Unity package will be displayed at the top of the list then click Install

Step 3 – Create an IOC (Inversion of Control) Container

You will need to create an IOC container that impelements IDependencyResolver. I did not write my own, I got this code from here.

using System;
using System.Collections.Generic;
using System.Web.Http.Dependencies;
using Microsoft.Practices.Unity;

namespace UnityExample
{
    // This code is from:
    // http://www.asp.net/web-api/overview/extensibility/using-the-web-api-dependency-resolver
    // The website also has a detailed explanation of how to setup dependency injection

    public class ScopeContainer : IDependencyScope
    {
        protected IUnityContainer container;

        public ScopeContainer(IUnityContainer container)
        {
            if (container == null)
            {
                throw new ArgumentNullException("container");
            }
            this.container = container;
        }

        public object GetService(Type serviceType)
        {
            if (container.IsRegistered(serviceType))
            {
                return container.Resolve(serviceType);
            }
            else
            {
                return null;
            }
        }

        public IEnumerable<object> GetServices(Type serviceType)
        {
            if (container.IsRegistered(serviceType))
            {
                return container.ResolveAll(serviceType);
            }
            else
            {
                return new List<object>();
            }
        }

        public void Dispose()
        {
            container.Dispose();
        }
    }

    class IoCContainer : ScopeContainer, IDependencyResolver
    {
        public IoCContainer(IUnityContainer container)
            : base(container)
        {
        }

        public IDependencyScope BeginScope()
        {
            var child = container.CreateChildContainer();
            return new ScopeContainer(child);
        }
    }
}

Step 4 – Create a new class called NameService.cs

The following class with be the dependency that we will inject into our new controller.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace UnityExample
{
    public class NameService
    {
        private string _myName;

        public NameService(string myName)
        {
            _myName = myName;
        }

        public string GetMyName()
        {
            return string.Format("My name is: {0}", _myName);
        }
    }
}

Step 5 – Create a new Controller called NameController which requires NameService as a constructor parameter

To create the new controller NameController:

  • Right click on the Controllers folder and select Add -> Controller
  • Enter Controller name: NameController
  • From the Template drop-down select Empty API controller

Now enter the following code in your new NameController class:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Web.Http;

namespace UnityExample.Controllers
{
    public class NameController : ApiController
    {
        private NameService _nameService;

        public NameController(NameService nameService)
        {
            _nameService = nameService;
        }

        public string Get()
        {
            return _nameService.GetMyName();
        }
    }
}

Step 6 – Setup Dependency Injection with Unity in Global.asax.cs

Now the final step is to create an instance of NameService and tell Unity to use that instance anytime an instance of NameController is created.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Http;
using System.Web.Mvc;
using System.Web.Optimization;
using System.Web.Routing;

// Add usings for Unity and Controllers
using Microsoft.Practices.Unity;
using UnityExample.Controllers;

namespace UnityExample
{
    public class WebApiApplication : System.Web.HttpApplication
    {
        protected void Application_Start()
        {
            AreaRegistration.RegisterAllAreas();
            WebApiConfig.Register(GlobalConfiguration.Configuration);
            FilterConfig.RegisterGlobalFilters(GlobalFilters.Filters);
            RouteConfig.RegisterRoutes(RouteTable.Routes);
            BundleConfig.RegisterBundles(BundleTable.Bundles);

            // Create a new Unity dependency injection container
            var unity = new UnityContainer();

            // Register the Controllers that should be injectable
            unity.RegisterType<HomeController>();
            unity.RegisterType<NameController>();

            // Register instances to be used when resolving constructor parameter dependencies
            unity.RegisterInstance(new NameService("It Worked!!!"));

            // Finally, override the default dependency resolver with Unity
            GlobalConfiguration.Configuration.DependencyResolver = new IoCContainer(unity);
        }
    }
}

Step 7 – Now let’s run the app and see that it worked!

Now run the app: Debug -> Start Without Debugging or (Ctrl F5).

When a browser opens, you will see the usual default home page for a .Net MVC 4 Web API application.

Now add the following to your url: /api/name. For example: http://localhost:62283/api/name.

Note: Remember the default path for all ApiControllers in Web API always starts with /api.

You should see the following output:

<string>My name is: It Worked!!!</string>

That is all!

Now that you have a working example, I recommend you read more about how it works in the following article:

Using the Web API Dependency Resolver

January 21, 2013

LINQ to SQL – Unit Testing a Repository

Filed under: .NET,c#,LINQ,Testing — Brian @ 10:47 pm
Tags: , ,

Over the last few months I have been using LINQ to SQL for accessing a SQL Server database from an MVC 4 WEB API application. Overall it works very well, and when looking at the SQL it generates, it does generate very efficient SQL. Also, I really like the LINQ to SQL syntax as well, very simple and clean, and it is easy to translate a SQL query into the LINQ to SQL syntax.

So normally in the past when I was testing a repository in Java/Scala projects that used Hibernate, we just used SQLite for our unit tests. But since the syntax used in LINQ to SQL is just LINQ expressions, I was wondering if it would be possible to unit test the LINQ expressions in my repository by just passing in a list of objects.

I looked at several existing solutions for unit testing repositories using LINQ to SQL, which looked good, but they each had the same problem. They were creating a repository with a DataContext being provided in the constructor, which allowed it to be mocked in a test. But, the problem with this approach is that the DataContext is designed to be used for a single database transaction, so you should always create and dispose of a new DataContext for reach request to the database. For Example I would expect each method in the repository to look like:

using (var db = new EmployeeDataContext(new DataContext(_connectionString)))
{
    return (from employee in db.Employees() where employee.Id == id select employee).SingleOrDefault();
} 

Instead of:

return (from employee in existingContext.Employees() where employee.Id == id select employee).SingleOrDefault();

The only way this other approach would work reliably would be if the application were creating a new instance of the repository class on every request, but I did not want to do that. So the solution I came up with to get the tests working the way I wanted AND while still creating a new DataContext for each transaction, was to create a wrapper class for the DataContext. This allowed me create a stub for the DataContextWrapper that could be used to replace the real one in a test. This may not be the most eloquent solution, but it worked for what I needed. If anyone has a cleaner solution for accomplishing this, please let me know!

Let’s use the standard database “Employee” example, just to be consistent.

Note: I used Moqand NUnit in this example.

Employee Entity
Just a standard LINQ to SQL entity object

[Table(Name = "dbo.Employee")]
public class EmployeeEntity
{
    [Column(IsPrimaryKey = true, IsDbGenerated = true)] public int Id { get; set; }
    [Column] public string FirstName { get; set; }
    [Column] public string LastName { get; set; }
    [Column] public DateTime StartDate { get; set; }
    public override string ToString()
    {
        return string.Format("Id={0}, FirstName={1}, LastName={2}, StartDate={3}", Id, FirstName, LastName, StartDate);
    }
}

Employee Data Context
DataContext for accessing the “Employees” table in our database.

public interface IEmployeeDataContext : IDisposable
{
    ITable<EmployeeEntity> Employees();
    void ExecuteCommand(string command, params object[] parameters);
    void SubmitChanges();
}

public class EmployeeDataContext : IEmployeeDataContext
{
    private readonly DataContext _dataContext;

    public EmployeeDataContext(DataContext dataContext)
    {
        _dataContext = dataContext;
    }

    public ITable<EmployeeEntity> Employees()
    {
        return _dataContext.GetTable<EmployeeEntity>();
    }

    public void ExecuteCommand(string command, params object[] parameters)
    {
        _dataContext.ExecuteCommand(command, parameters);
    }

    public void SubmitChanges()
    {
        _dataContext.SubmitChanges();
    }

    public void Dispose()
    {
        _dataContext.Dispose();
    }
}

Data Context Wrapper
Now, let’s wrap the EmployeeDataContext in another class that has a method called CreateDataContext() which allows us to create a new EmployeeDataContext.

public interface IDataContextWrapper
{
    IEmployeeDataContext CreateDataContext();
}

public class DataContextWrapper: IDataContextWrapper
{
    private readonly string _connectionString;
    public DataContextWrapper(string connectionString)
    {
        _connectionString = connectionString;
    }

    public IEmployeeDataContext CreateDataContext()
    {
        return new EmployeeDataContext(new DataContext(_connectionString));
    }
}

Employee Repository
The constructor of our EmployeeRepository now takes a parameter of type IDataContextWrapper. So in each method that calls the database, it can now call _dataContextWrapper.CreateDataContext() in a using block to create a new DataContext for the request and also ensure the DataContext will be disposed.

public interface IEmployeeRepository
{
    EmployeeEntity AddEmployee(EmployeeEntity employee);
    EmployeeEntity GetEmployee(int id);
    List<EmployeeEntity> GetAllEmployees();
    List<EmployeeEntity> GetEmployeesByLastName(string lastName);
    List<EmployeeEntity> GetEmployeesByStartDate(DateTime minDate, DateTime maxDate);
}

public class EmployeeRepository : IEmployeeRepository
{
    private readonly IDataContextWrapper _dataContextWrapper;

    public EmployeeRepository(IDataContextWrapper dataContextWrapper)
    {
        _dataContextWrapper = dataContextWrapper;
    }

    public EmployeeEntity AddEmployee(EmployeeEntity employee)
    {       
        using (var db = _dataContextWrapper.CreateDataContext())
        {
            db.Employees().InsertOnSubmit(employee);
            db.SubmitChanges();
            return employee;
        }    
    }

    public EmployeeEntity GetEmployee(int id)
    {
        using (var db = _dataContextWrapper.CreateDataContext())
        {
            return (from employee in db.Employees() where employee.Id == id select employee).SingleOrDefault();
        }        
    }

    public List<EmployeeEntity> GetAllEmployees()
    {
        using (var db = _dataContextWrapper.CreateDataContext())
        {
            return (from employee in db.Employees() select employee).ToList();
        }  
    }

    public List<EmployeeEntity> GetEmployeesByLastName(string lastName)
    { 
        using (var db = _dataContextWrapper.CreateDataContext())
        {
            return (from employee in db.Employees() where employee.LastName == lastName select employee).ToList();
        }    
    }

    public List<EmployeeEntity> GetEmployeesByStartDate(DateTime minDate, DateTime maxDate)
    {
        using (var db = _dataContextWrapper.CreateDataContext())
        {
            return (from employee in db.Employees() where employee.StartDate >= minDate && employee.StartDate <= maxDate select employee).ToList();
        }
    }
}

Stub Data Context Wrapper
Now let’s create a stub class for our DataContextWrapper which will be used to replace the real one in our unit tests.

public class StubDataContextWrapper : IDataContextWrapper
{
    private readonly IEmployeeDataContext _dataContext;
    public StubDataContextWrapper(IEmployeeDataContext dataContext)
    {
        _dataContext = dataContext;
    }

    public IEmployeeDataContext CreateDataContext()
    {
        return _dataContext;
    }
}

Stub Employee Table
Also, we will need to create a stub of the employee table, which will be used in our tests to wrap the list of EmployeeEntity objects we will return as the result from our EmployeeDataContext when it is mocked in our tests.

public class StubEmployeeTable : ITable<EmployeeEntity>
{
    protected List<EmployeeEntity> internalList;

    public StubEmployeeTable(List<EmployeeEntity> list)
    {
        internalList = list;
    }

    public void Attach(EmployeeEntity entity)
    {
    }

    public void DeleteOnSubmit(EmployeeEntity entity)
    {
        internalList.Remove(entity);
    }

    public void InsertOnSubmit(EmployeeEntity entity)
    {
        internalList.Add(entity);
    }

    public IEnumerator<EmployeeEntity> GetEnumerator()
    {
        return this.internalList.GetEnumerator();
    }

    System.Collections.IEnumerator System.Collections.IEnumerable.GetEnumerator()
    {
        return this.internalList.GetEnumerator();
    }

    public Type ElementType
    {
        get { return this.internalList.AsQueryable().ElementType; }
    }

    public System.Linq.Expressions.Expression Expression
    {
        get { return this.internalList.AsQueryable().Expression; }
    }

    public IQueryProvider Provider
    {
        get { return this.internalList.AsQueryable().Provider; }
    }
}

Employee Repository Unit Tests
Now we can write our unit tests around the EmployeeRepository. The first step is to mock the EmployeeDataContext. Next we create a StubDataContextWrapper passing in the mock object. Then to create the EmployeeRepository we pass in the StubDataContextWrapper. We setup some EmployeeEntity objects with data we will use for testing, then finally we set the test data as the return value for the Employees() method on the mock of our EmployeeDataContext, using the StubEmployeeTable to wrap the result, to make it look like a response from a LINQ to SQL query.

[TestFixture]
public class EmployeeRepositoryTest
{
    private Mock<IEmployeeDataContext> _mockEmployeeDataContext;
    private IDataContextWrapper _stubDataContextWrapper;
    private IEmployeeRepository _employeeRepository;

    [SetUp]
    public void Setup()
    {
        _mockEmployeeDataContext = new Mock<IEmployeeDataContext>();
        _stubDataContextWrapper = new StubDataContextWrapper(_mockEmployeeDataContext.Object);
        _employeeRepository = new EmployeeRepository(_stubDataContextWrapper);

        var employee1 = new EmployeeEntity { Id  = 1, FirstName = "John", LastName = "Smith", StartDate = new DateTime(2013,01,10)};
        var employee2 = new EmployeeEntity { Id  = 2, FirstName = "Frank", LastName = "Smith", StartDate = new DateTime(2013,01,15)};
        var employee3 = new EmployeeEntity { Id  = 3, FirstName = "Stan", LastName = "Johnson", StartDate = new DateTime(2013,01,20)};

        var employees = new List<EmployeeEntity> {employee1, employee2, employee3};
        _mockEmployeeDataContext.Setup(x => x.Employees()).Returns(new StubEmployeeTable(employees));
    }

    [Test]
    public void AddEmployeeShouldAddNewEmployeeWhenDoesNotExist()
    {
        _employeeRepository.AddEmployee(new EmployeeEntity { FirstName = "New", LastName = "Employee", StartDate = new DateTime(2013, 01, 25) });
        Assert.AreEqual(4, _employeeRepository.GetAllEmployees().Count);
    }

    [Test]
    public void GetAllEmployeesByIdShouldReturnEmployeeWhenIdExists()
    {
        var results = _employeeRepository.GetAllEmployees();
        Assert.AreEqual(3, results.Count);
    }

    [Test]
    public void GetEmployeeByIdShouldReturnEmployeeWhenIdExists()
    {
        var result = _employeeRepository.GetEmployee(1);
        Assert.AreEqual(1, result.Id);
    }

    [Test]
    public void GetEmployeesByLastNameShouldReturnEmployeeWhenLastNameMatches()
    {
        var results = _employeeRepository.GetEmployeesByLastName("Smith");
        Assert.AreEqual(2, results.Count);
    }

    [Test]
    public void GetEmployeesByStartDateShouldReturnEmployeeWhenStartDateIsWithinRange()
    {
        var results = _employeeRepository.GetEmployeesByStartDate(new DateTime(2013, 01, 14), new DateTime(2013, 01, 16));
        Assert.AreEqual(1, results.Count);
        Assert.AreEqual(new DateTime(2013, 01, 15), results[0].StartDate);
    }
}

That is all. Of course this example can be made more generic, but it is just a simple example. If you have an example that works better for LINQ to SQL, please let me know, I would be happy to hear about it!

Next Page »

The Rubric Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 33 other followers