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 https://stackoverflow.com/a/14122771, 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 Dynamic-SQL-queries.zip 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.

Generating Date Sequence With SQL Query

The task is to create SQL query that will produce a date sequence starting from @startDate till @endDate including, i.e. 2017-01-01, 2017-01-02, 2017-01-03 and so on.

There is a couple of obvious solutions.

1. DATEADD() with integer sequence where the integer sequence is an increment

Please take care how many records are in sys.all_objects, it won’t generate a long sequence. If you need more, use a self cross join as shown in Generating Integer Sequence With SQL Query.

2. Recursive query

The last option is very important because SQL Server limits the recursion level to 100 by default.

Casting C# Enums

It’s a short memo how to cast enum to int or string and vice versa.

1. Enum -> int
int value = (int)myEnum;

2. Enum -> string
string name = myEnum.ToString();

3. Int -> Enum
myEnum = (MyEnum)2;

4. String -> Enum
myEnum = (MyEnum) Enum.Parse(typeof(MyEnum), stateName);
bool parseSuccess = Enum.TryParse(stateName, out myEnum);

Querying Entity Framework. Part 23 – Filtering Nullable Field

When you run queries against non-nullable columns (NOT NULL in SQL), everything is going smoothly. You should pay some attention when you deal with nullable fields (one of the cases will be covered at the end of this post).

Now let’s look closer to Entity Framework queries against nullable field. I’ve added a new column LastUpdate with the type DateTime? to my Client entity.

So I have some records with concrete dates, and some with NULL values.

1. Filtering “greater or equal than”

C# understands it perfectly. We have a simple and correct SQL query.

2. Filtering all concrete dates aka WHERE … IS NOT NULL
You can use one of the choices.

Both generate the same correct SQL query.

3. Filtering multiple dates aka WHERE … IN

This case is a bit harder because C# could not compare DateTime[] array and DateTime? field. You need to get a not-nullable value from column.

Looks good, but I’m not satisfied with conversion of C# DateTime array values to SQL Server DateTime2 type. Especially when I set a strong annotation to use Date type in database.

If you don’t want to build a query with every parameter despite whether it is null or not, you might build a dynamic filter like in the following sample.

What will be if both parameters are null? No filters will be added, and you’ll get a query returning all(!) rows regardless of having a concrete value or not. In this case add another check when both parameters are null. One of the simplest implementation is to add third if statement.