Adding Web API Controller To Existing MVC Project

If you have MVC project and you need to add Web API controller to this project, it can be done very easy.

1. Add Nuget package Microsoft.AspNet.WebApi.Core. This will add references to System.Web.Http.dll.

2. Add registration file for Web API. It would better to do it like Visual Studio does – add file App_Start\WebApiConfig.cs

3. Add using System.Web.Http; to Global.asax.cs

4. Add GlobalConfiguration.Configure(WebApiConfig.Register); in Application_Start() method before registering MVC routes.

Application_Start() would be like this.

5. Create a controller and derive it from System.Web.Http.ApiController

Renaming Database

Sometimes you need to rename a SQL Server database. Beside the database name itself, this name is usually a part of logical database file names, physical file names, maybe file groups.
To rename the database name you need:

  1. Bring database offline;
  2. Change paths to database files in system catalog;
  3. Rename physical file names using Windows Explorer;
  4. Bring database online;
  5. Change database name;
  6. Change logical file names (optionally).

Here is the script to do this. Be careful to put the correct logical database file names.

Drop Filestream In SQL Server

I have a table in SQL Server where one column is placed in Filestream filegroup. Now I need to delete this column and all files on disk.
Just recall the process to implement Filestream in SQL Server database:

  1. Add Filestream filegroup;
  2. Add file to a newly created Filestream filegroup;
  3. Create table where column is marked as FILESTREAM and you pointed FILESTREAM_ON to a Filestream group.

To drop Filestream you need to implement these steps in reverse order, but check Filestream columns, files and filegroups before.

Then do the following:

Installing DLL Into GAC Using Powershell

To install DLL into Global Assembly Cache (GAC) you need Visual Studio or SDK to run gacutil.exe. It’s not convenient when you need to deploy ASP.NET application in IIS. This time you can do this with Powershell as described in TechNet article How to Install a DLL to the GAC on Windows Server 2012 Using Only PowerShell by Muhammad Khalid Latif.

Line Number In C# Program

I have a method in ASP.NET application that works good but sometimes it fails. I need some kind of error logging or tracing. If you don’t want to write stupid messages everywhere in your code, you have some possibilities.

In debug mode:
1. Embrace your code in try-catch block. Compile the application in debug mode and place .pdb file along with .exe file. When the error is occurred, exception will show the line number.
2. You can write trace info manually as described by Scott Hanselman in Getting the Line Number and File Name from C#.

.pdb file is necessary here, or you’ll see 0 instead of a line number.

In release mode:
Thanks to Marc Gravell, but you need .NET Framework 4.5 or higher here.

SQL Scalar Functions – MIN of Multiple Values

SQL Server built-in function MIN() is an aggregate function. It can find minimal value on a single column from many rows.
To find minimum of multiple columns you can write UDF or use a subquery.

Important! My examples works fine even with nullable values, i.e. they ignore null values and return a minimum only from concrete values. If all values are null, they return null.

1. Scalar function for two values

2. Scalar function for three values

It can be quite hard to write a function for more input values. You can use a subquery here.

Shrinking VHD

To shrink VHD file occupied by virtual Windows OS you need to shrink the volume (i.e. disk C:) inside virtual machine and physical VHD file on host machine. It works fine when you don’t use differential VHDs.

Inside virtual Windows OS run command prompt:

Then shut down virtual machine.

On host Windows OS run command prompt:

Truncating DateTime In SQL Server

Sometimes we need to get only date from a datetime value, or truncate time to an hour or minute in SQL Server. There are some ways to do this:

  • casting;
  • manipulating with string conversion;
  • working on total number of years/months/days/hours in DateTime value.

Casting works fine for truncating to date.

You can convert datetime value to string (cast to nvarchar type), get a substring, and convert back to datetime. But it’s not efficient and can depend on string format (lond or short date format) or locale.

Another way is to calculate the number of years/months, etc. from a special date (DATEDIFF), then add this number of years/months to that special date (DATEADD).

Zero means the January 1, 1900.

Dynamic SQL Queries In SQL Server

SQL Server allows to execute SQL code from a variable. The simple way is to use EXEC command.

The second way is to use sp_executesql stored procedure. It even allows to run code with parameters.
1. Simple query

Don’t forget to change single quote (‘) with two single quotes inside a string.

2. Query with a parameter
You need to pass a list of parameters to be used inside a dynamic SQL query and actual values for those parameters.

Note that name of the parameter inside the query and the name of variable with actual value could be different.

3. Query with output parameter

4. Query with table-valued parameter
The rules of the SQL Server are the same: we need to create table-valued type and use a readonly parameter.

5. Temporary tables with dynamic SQL
SQL Server allows only to use temp tables that were created before running dynamic SQL code. If you create a temp table during dynamic query, it would be inaccessible after that query.

Important! You can pass more parameters to sp_executesql procedure than is really used in dynamic query. This can be very helpful when you need to dynamically add joins or where clauses to a query. So you pass all the parameters, but consume just a part of them.

Also you can download full sample code to play with it.

Querying Entity Framework. Part 24 – Aggregating Nullable Fields

It’s a very simple case for Entity Framework model. I have a nullable column in Clients table.

I want to count values and to sum up them. The LINQ query to EF model can be very simple.

But these queries reveals some magic. Count() returns int type, but Sum() return int?. So you may fall into one of the following cases.

Method Return type Result on all rows Result on not-nullable rows Result on empty dataset
Count() int Count all rows regardless null or not null Count only not-nullable rows Zero
Sum() int? Sum only not-nullable values Sum only not-nullable values Null

The most dangerous is the last case when Sum() returns null, so  you should add a check for null value. If you want to get zero, you need to change a request and add DefaultIfEmpty() method.