Monitoring SQL Server Backups With DMV

I have a rather long-running backup that was started via Maintenance plan. So I have no output about the progress of a particular backup. In this case you can query DMV to get the information needed.

The output would be like this.


As you see, one backup is running while the others are pending.

Code First. Executing Stored Procedures With Table-Valued Parameter

Today I need to retrieve data from Entity Framework using stored procedure, and passing input values from .NET collection through the table-valued parameter. As for technologies that will be used here, I wrote in these posts:

  1. Using Table-Valued Parameters in SQL Server Stored Procedures – how to create SQL Server type and stored procedure;
  2. Calling SQL Server Stored Procedure With Table-Valued Parameter in C# – calling stored procedure via ADO.NET;
  3. Code First. Executing Stored Procedures – calling stored procedures via DbContext.Database.SqlClient().

As stated in MSDN article Table-Valued Parameters, table-valued parameter can be populated from:

  • DataTable
  • DbDataReader
  • IEnumerable

If you have a .NET collection with parameters, you could transform it to IEnumerable. Here is a good article on C# Corner Passing Table Valued Parameter to Stored Procedure: Part 2 that shows how to make this conversion.

Let’s start to write the code.

1. Create SQL Server type UserType to store the row of a table-valued parameter

2. Create stored procedure with a readonly parameter of type UserType

3. Create .NET class UserEntry to store the row of a table-valued parameter

4. Create .NET class UserParamCollection that must be inherited from the preceding .NET class and also implements IEnumerable

5. Convert List<UserEnty> to a newly created class UserParamCollection

6. Create SqlParameter

Here you need to specify parameter type SqlDbType.Structured. TypeName must be the same as SQL Server type defined in step 1.

7. Call stored procedure and pass the preceding SqlParameter

You must place the parameter name of the SQL Server stored procedure right after the name of the stored procedure as following: “[dbo].[ListUsers] @UserList”. If you omit it, you’ll get an error:
Procedure or function 'ListUsers' expects parameter '@UserList', which was not supplied.

Because SqlQuery() returns a collection of type System.Data.Entity.Infrastructure.DbRawSqlQuery, you would better to cast it to IEnumerable to conform to application interfaces.

Querying Entity Framework. Part 22 – Multiple Fields In Join, GroupBy And OrderBy

Now I want to summarize the info about how to use multiple fields in LINQ queries. You know that LINQ to Entities allows to make joins, grouping and sorting based on single field or multiple fields.

Generally speaking, when the query operation is based on a single field, you use this single field alone. When the operation is based on multiple fields, you would use a construct as the following:
new { field1, field2... }

Here are examples of how to do it in C#.


There are identical key selectors in inner and outer parts of the join.



In this case EF will sort the rows by both fields in ascending order. The last row in T-SQL will look like this:
ORDER BY [Extent1].[CountryCode] ASC, [Extent1].[Name] ASC

If you use OrderByDescending() method, so the output will be sorted by both fields, but in descending order.
In a situation when you need different sorting, you should use ThenBy()/ThenByDescending() for the second and consequent fields.

Not All .NET Types Can Be Used In Entity Framework

Recently I had a demand to use an unsigned integer type in Entity Framework model. Everything was OK, but migration did not create a field for that type.

I knew that EF could not create a SQL type for char (System.Char), even if you place an annotation like this:

I wrote about it in my post Entity Framework. C# To SQL Type Translation.

It was surprise for me that EF could not create any type for unsigned types like ushort, uint or ulong. Column annotation to use signed type ain’t help too.

And the most “amazing” thing is that EF rejects sbyte type (as for the EF version 6.1.3). It generates an error:

There is no store type corresponding to the conceptual side type 'SByte' of primitive type 'SByte'.

The conclusion is that you can use any .NET type in Entity Framework model except char, ushort/uint/ulong and sbyte.

Querying Entity Model. Part 21 – Intersect And Except

SQL Server and LINQ both have some commands that operates on sets like Union, Intersect and Except. I’ve investigated the behavor of Union in Entity Framework earlier in my post Querying Entity Model. Part 15 – Union. Now let’s look at intersect and except.

I want to see the intersection between two sets of country codes taken from Clients and Phones tables (I use the same simple EF model through all the Querying Entity Model series).

Wow! Entity Framework uses exactly INTERSECT operator. Well done.

Except case is similar to intersect one.

I used a collection with one field, but EF is capable to operate on collections with many fields. The only thing you should care about is to intersect/except the collections of the same type.