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.

Unit Testing C# Async Methods

This time I have a C# async method that should be tested. As you remember, async method must return Task or Task<>.  You can declare an async void method, but this should be used only for event handlers because you have no control on method execution and, the most important, its failure. Beside that, async void method is hard to test.

I have a simple async method that returns Task<int> instead of int in synchronous method (look at my previous post Unit Testing C# Synchronous Methods).

We must be happy that modern unit test frameworks allow to write async unit test where an async method is called asynchronously.

1. Check successful result

The async method being tested is called via await operator. This makes the code being executed in true asynchronous mode.

2.1. Check failure with ThrowsAsync<>

Unit test is awaiting for result from Assert.ThrowsAsync, that is awaiting for result from the method being tested. If you delete the inner async/await, the method would be executed in synchronous mode. If you omit the first outer await, the unit test method might finish before the code in NumberAsync would fail. So you will get wrong results!

2.2. Check failure with Record.ExceptionAsync

Unit Testing C# Synchronous Methods

When you need to unit test a method, you should check happy path (for example, the method returns a resulting value) and sad path (the method throws exception). Here I describe a basic usage of unit testing with Visual Studio 2017 and xUnit version 2.2.

The code being tested:

1. Check successful result (happy path) – it’s very straightforward.

2. Check fail when the method throws an exception (sad path)

If you have faced with MSTest, you might remember [ExpectedException] attribute. In this case MSTest waits for a particular exception would be thrown in a whole unit test method, but not in a specific line of code. Modern unit test frameworks have more graceful capabilities to catch the exception.

2.1. Using Throws<>

But this approach combines Act and Assert phases of unit test in one line of code. Richard Banks suggested a better way in his article Stop Using Assert.Throws in Your BDD Unit Tests.

2.2. Using Record.Exception

At first, I check that the exception was really caught, then check the type of that exception.

Next time I’ll tell about unit testing the asynchronous methods.

ASP.NET Identity. Part 2 – Customizing Entities

ASP.NET Identity gives a class ApplicationUser for storing data about users. It has a lot of properties defined in base class IdentityUser, such as Id, UserName, Email, PhoneNumber, etc. But when you need to add some extra properties, it becomes more complicated.

Entity Framework creates a database only once at first use. It does not recreate, nor modify it. We should use EF migrations to add new fields. Also it gives the possibility to change table names.

1. Add new properties in file Models\IdentityModels.cs

2. Open Package Manager Console and run command

EF will create folder Migrations and a couple of files: Configuration.cs and <timestamp>_InitialCreate.cs with code to create a default database structure.
3. Create new migration and give it a name

Visual Studio will open a file with two method to add and drop columns defined in step 1.
4. Apply EF migration to database.

Now I have the following database structure:

5. I want to enter first and last names during the registration process. So I’ll add two properties to class RegisterViewModel in file Models\AccountViewModels.cs

6. Add code to Views\Account\Register.cshtml file to display these them.

7. Change Register method in Account controller (file Controllers\AccountController.cs) to store them.

8. Also I want to display user’s first name on the top bar of the web application instead of his/her email. To do this we need to modify file Views\Shared\_LoginPartial.cshtml to get ApplicationUser via current user’s Id with the help of Owin context, then to show property FirstName. The final code looks like this:

ASP.NET Identity. Part 1 – Changing Default Database

When you create MVC project in Visual Studio 2017, you can use individual user accounts authentication that is shipped with MVC template. This implementation uses Entity Framework. By default, EF creates a database in SQL Server LocalDB with a name like aspnet-WebApp-<timestamp>.

To change the connection to database:
1. Open Web.config file
2. Find section <connectionStrings>
3. Modify the attribute connectionString in element with name DefaultConnection. Element will look like the following:

<add name="DefaultConnection" connectionString="Data Source=(local);Integrated Security=SSPI;Initial Catalog=AspNetIdentityDB;" providerName="System.Data.SqlClient"/>

If you dislike the name DefaultConnection, you should change it in Web.config file and in the constructor of ApplicationDbContext class (file Models\IdentityModels.cs).