How To: Getting events from SQL Server


Sometimes we need to execute some long-running SQL Server processes (Stored procedures or functions) and it’d be nice to get progress messages, and why not, a percent value in order to show a progress bar in our application.

In the past I’ve used different techniques to archive this, from SQL Assemblies for calling a SignalR server, to execute xp_cmdshell, and so many other esoteric ways.

None of that solved the problem in an elegant way, but well, it worked… more or less…

Changing the point of view

Today I noticed the SqlConnection class has an InfoMessage event, and it can be used for “Clients that want to process warnings or informational messages sent by the server” which is EXACTLY what I was looking for.

It looks promising but, how can I trigger an error from my SQL?

The answer is really E-A-S-Y :)

If you are a TSQL programmer you surely know RAISERROR, which generates an error message in a similar way the throw C# keyword does.

The trick here is its severity argument: It indicates the type of problem encountered by SQL Server, and values under 10 indicate that these errors are not severe. Just warnings or information that SQL server sends to anyone is listening.

And who’s listening? You can imagine…

Putting it all together (show me the code)

Let’s assume we’ve this long-running stored procedure in our database:

CREATE PROCEDURE [dbo].[TestLongRunningSP]
RAISERROR('1) Starting', 0, 0) WITH NOWAIT;
WAITFOR DELAY '00:00:03'; --Wait for 3 seconds

RAISERROR('2) Fetching data', 0, 0) WITH NOWAIT;
WAITFOR DELAY '00:00:05'; --Wait for 5 seconds

RAISERROR('3) Some other actions', 0, 0) WITH NOWAIT;
WAITFOR DELAY '00:00:03'; --Wait for 3 seconds

RAISERROR('4) Calculating', 0, 0) WITH NOWAIT;
WAITFOR DELAY '00:00:02'; --Wait for 2 seconds

RAISERROR('5) Finishing', 0, 0) WITH NOWAIT;

Now let’s create some C# code:

First: A class to send the received messages from our repository to its consumer, which in this case the UI will be just a Windows Form (good old boys never die).

public class SqlMessageEventArgs {
public string Message { get; set; }
public int Progress { get; set; }

Notice the Progress property. We will use it in the last part, be patient ;)

Second: A repository class that open a connection, execute the stored procedure and send messages using a ProcessMessage event. We must explicitly enable the connection to receive these errors and raise handling the InfoMessage event:

public class SqlServerRepo
public event EventHandler ProcessMessage;
public string ConnectionString { get; internal set; }
public SynchronizationContext SyncContext { get; internal set; }
public async Task GetDataAsync() {
using (var con = new SqlConnection(ConnectionString))
con.FireInfoMessageEventOnUserErrors = true;
con.InfoMessage += (s, e) =>
SyncContext.Post(_ => ProcessMessage?.Invoke(
s, new SqlMessageEventArgs
Message = e.Message,
}), null);
var sql = "EXEC dbo.TestLongRunningSP";
var commandDefinition = new CommandDefinition(sql);
await con.ExecuteAsync(commandDefinition);

Note: When using async pattern the received messages come from a different thread and cannot be used to update the UI directly. For this reason I always recommend send to the Repo the current synchronization context and use this Context to post to the main thread.


BOOM: Typical cross thread exception when updating UI from a different thread.

Third: Finally let’s create a simple Windows Form with just a button, a progress bar and a list box).

const string cs = "Data Source=YOUR_SERVER;Initial Catalog=YOUR_DATABASE;Integrated Security=True;"
private async void button1_Click(object sender, EventArgs e) {
var repo = new SqlServerRepo() {
ConnectionString = cs,
SyncContext = SynchronizationContext.Current
repo.ProcessMessage += (ms, me) => {
listBoxLog.Items.Add($"{DateTime.Now} - {me.Message}");
await repo.GetDataAsync();
listBoxLog.Items.Add($"Finished Process!");

This code creates the repo, pass the connection string and the current synchronization context, handles the ProcessMessage event and invoke the GetDataAsync method.

Simply and elegant. No dependent assemblies or external resources.

One more thing

What about the progress bar? Also easy. Just use the state argument of the RAISERROR method on the SP like this:

RAISERROR('1) Starting', 0, 10) WITH NOWAIT;
WAITFOR DELAY '00:00:03'; --Wait for 3 seconds

RAISERROR('2) Fetching data', 0, 30) WITH NOWAIT;
WAITFOR DELAY '00:00:05'; --Wait for 5 seconds

RAISERROR('3) Some other actions', 0, 60) WITH NOWAIT;
WAITFOR DELAY '00:00:03'; --Wait for 3 seconds

RAISERROR('4) Calculating', 0, 70) WITH NOWAIT;
WAITFOR DELAY '00:00:02'; --Wait for 2 seconds

RAISERROR('5) Finishing', 0, 100) WITH NOWAIT;

Then update the GetDataAsync method:

public async Task GetDataAsync() {
using (var con = new SqlConnection(ConnectionString))
con.FireInfoMessageEventOnUserErrors = true;
con.InfoMessage += (s, e) =>
SyncContext.Post(_ => ProcessMessage?.Invoke(
s, new SqlMessageEventArgs
Message = e.Message,
Progress = e.Errors[0]?.State ?? 0
}), null);
var sql = "EXEC dbo.TestLongRunningSP";
var commandDefinition = new CommandDefinition(sql);
await con.QueryAsync(commandDefinition);

And finally the code in the form:

repo.ProcessMessage += (ms, me) => {
listBoxLog.Items.Add($"{DateTime.Now} - {me.Message}");
progressBar.Value = me.Progress;

And that’s all!

Hope you enjoy it, and please, send me your thoughts about this solution ;)

How To: Get data from OLAP cubes

cuberubikToday I had to read some data from different OLAP cubes and show this information in our application. This could be accomplished using ADOMD.NET client library, which is the multidimensional equivalent to the ADO.NET libraries.

This library provides a set of classes (Connections, Commands and Readers) that allows developers to connect, query and get data from these multidimensional sources in the same way the classical ADO.NET library does.

Maybe the easiest way to read data is using the class AdomdDataReader. This object retrieves a read-only, forward-only, flattened stream of data from an analytical data source, and also implements the IDataReader interface (like the good-old DataReader).

In non dynamic scenarios, we developers, tend to use ORMs like NHibertate, EF or Dapper (my favorite), to translate data from our models to classes. But these ORMs doesn’t support multidimensional sources, because after all, they are only supersets or extensions of the ADO.NET model and are based on this existing object model.

So, our goal here will be replicate in a multidimensional environment the way Dapper allows you to connect to a server, read some data and (the most important part) return these data as a typed List<T> of objects.

Let’s see how it works before dig into the details:

using (var conn = new AdomdConnection(connectionString))
var commandText = @"SELECT
[Measures].[Gains Period],
[Measures].[Gains YTD],
[Valuation Dates Accumulated].[Hierarchy].[Year].Members ON ROWS
FROM Accumulated
Where ({ @PortfolioId })";
var data = conn.Query<MyResultDTO>(commandText,
new AdomdParameterMultipleValues<int>()
ParameterName = "@PortfolioId",
MemberName = "[Portfolios].[Port Code]",
Values = new List<int>() { 282, 185 }

First of all we have a SQL script that returns some sample data of 3 different measures year by year (gains in period, gains year-to-date and the amount of money).


And we’d like to translate these data into a generic List of my own DTO:

internal class MyResultDTO
[OLAPMemberNameAttribute("[Valuation Dates Accumulated].[Hierarchy].[Year].[MEMBER_CAPTION]")]
public string Year { get; set; }
[OLAPMemberNameAttribute("[Measures].[Gains Period]")]
public decimal? GainsPeriod { get; set; }
[OLAPMemberNameAttribute("[Measures].[Gains YTD]")]
public decimal? GainsYTD { get; set; }
public decimal? Amount { get; set; }

view raw
hosted with ❤ by GitHub

Notice that in C# properties can not contain spaces so we previously have to ‘map’ the DataReader column names into valid property names. The trick here is create an attribute for decorating each property with the equivalent column name in the reader.

The magic here is inside the generic Query<T> Method. This method is just an extension method of the AdomdConnection object (Dapper style) and it’s mission will be:

  1. Replace the parameters in the command string “…Where ({ @PortfolioId })”
  2. Execute the command against a valid connection
  3. Transform the returned IDataReader into a List<MyResultDTO>
public static List<T> Query<T>(this AdomdConnection conn, string commandText, params object[] parameters)
var commandTextParameters = ReplaceCommandTextParameters(commandText, parameters);
var cmd = new AdomdCommand(commandTextParameters, conn);
var dr = cmd.ExecuteReader();
return dr.ToList<T>();

view raw
hosted with ❤ by GitHub

We’ll focus on the third one. Let’s create another extension method of the IDataReader interface. OMG! Code on interfaces? Yes… I’ll burn in hell, I know it… :D

This is the extension method with some auxiliary functions:

public static List<T> ToList<T>(this IDataReader dr)
var list = new List<T>();
var obj = default(T);
while (dr.Read())
obj = Activator.CreateInstance<T>();
foreach (var prop in obj.GetType().GetProperties())
var columnDescription = getPropertyDescription(obj, prop.Name);
if (existsDataReaderColumn(dr, columnDescription)) copyColumnValueToProperty(dr, obj, prop);
return list;
static void copyColumnValueToProperty<T>(IDataReader dr, T obj, PropertyInfo prop)
var columnDescription = getPropertyDescription(obj, prop.Name);
var columnOrdinal = dr.GetOrdinal(columnDescription);
var value = dr[columnOrdinal];
var canBeNull = !prop.PropertyType.IsValueType || (Nullable.GetUnderlyingType(prop.PropertyType) != null);
var castToType = Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType;
if (canBeNull && value == null)
prop.SetValue(obj, null, null);
prop.SetValue(obj, Convert.ChangeType(value, castToType, CultureInfo.InvariantCulture), null);
catch { }
static bool existsDataReaderColumn(IDataReader dr, string propertyName)
var obj = dr[propertyName];
return true;
catch { return false; }
private static string getPropertyDescription(object value, string propname)
var propinfo = value.GetType().GetProperty(propname);
var attributes =
typeof(OLAPMemberNameAttribute), false);
if (attributes != null && attributes.Length > 0)
return attributes[0].Description;
return value.ToString();

EDIT (2018-12-11) : Added custom attribute to the post, used to decorate our DTO.

public class OLAPMemberNameAttribute : System.Attribute
string _Description = string.Empty;
public OLAPMemberNameAttribute(string description)
_Description = description;
public string Description { get { return _Description; } }

As you can see the method iterates over the reader’s data and uses reflection to create an object per each row, map each column’s value to the correct property and then add the new object to the returned collection (*). Easy!


(*) image courtesy of oz-code, the best debugger addin for visual studio #imho ;)

Hope this helps! ;)

How To: Connect via SFTP using SSH.NET


In these times of APIs-everywhere, it may sound like an anachronism the use SFTP to connect to a remote server and get a list of files for exchanging information, but in the financial world (sadly) it’s more common than you think.

In my current project I’ve to connect to a remote server via Secure File Transfer Protocol (aka SFTP) using a user name, a RSA private key and a phassphrase. Once connected, the goal is to read some files from a remote folder and download them to a local folder.

To accomplish this I recommend you to use SSH.NET, one of the most popular SSH libraries for .NET, available on nuget.


More info about SSH.NET here:

Using SSH.NET to connect to the server

The most difficult part here is configure the connection, as usual. We need to provide the server url and port, in combination with the username and a file that contains the private key of the RSA certificate, and -of course- the passphrase.

Here’s the final code, and it works :)

private static void SFTP_Connect_And_Download_Sample()
var host = "";
var port = 999;
var username = "your_username";
var passphrase = "your_passphrase";
var privateKeyLocalFilePath = @"your_localpath\…\PrivateKeyOpenSSH.ppk";
var remoteFolderPath = "/";
var localFolferPath = @"D:\locafiles\";
var key = File.ReadAllText(privateKeyLocalFilePath);
var buf = new MemoryStream(Encoding.UTF8.GetBytes(key));
var privateKeyFile = new PrivateKeyFile(buf, passphrase);
var connectionInfo = new ConnectionInfo(host, port, username,
new PrivateKeyAuthenticationMethod(username, privateKeyFile));
using (var client = new SftpClient(connectionInfo))
var files = client.ListDirectory(remoteFolderPath);
foreach (var file in files)
using (var targetFile = new FileStream(Path.Combine(localFolferPath, file.Name), FileMode.Create))
client.DownloadFile(file.FullName, targetFile);

Bonus * the trick *

I’ve spent a lot of time before it works, receiving a SSH exception “Invalid private key file”, but the same key file works fine when using the app Filezilla to connect, so… #WhatTheHell :S

PuTTY-User-Key-File-2: ssh-rsa
Encryption: aes256-cbc
Comment: rsa-key-KBL-20171006
Public-Lines: 6
Private-Lines: 14
Private-MAC: d288fffe72914eb62ed60a7e50fd8ce775

The key file (*.ppk) looks something similar to this

This is because the private key must be compatible with SshNet, so we have to convert the private key using PuTTY key Generator (the same app we used to create the certificate). Once opened in PuTTY, just export your file key to OpenSSH and use this new ppk file instead of the previous one.


If you open the new file you will see that the key file now begins with


And ends with


Finally, this is a valid SSH private key and it works like a charm ;)

Hope you enjoy it!

HowTo: Redeploy CLR assemblies to SQL Server (Newtonsoft.Json version issue)



The Story

Hi again,

Some time ago I published a post about how to send notifications from SQL Server using SignalR. We are using SignalR self-hosted in a Windows Service in a separated server, and it had been running fine until now.

This week I had to set up a new SQL server which will replace the old one, which will replace the current ‘preprod’ one, and (entering recursive mode…).

After configuring the new SQL Server settings, I tried to update the previously imported assemblies to the database to the latest versions I’m currently using in the .NET project (Newtonsoft.Json & Microsoft.AspNet.SignalR.Client).

References map:

  • MyAssembly
    • Microsoft.AspNet.SignalR.Client
      • Newtonsoft.Json

As you can see, the app references the SignalR client, which references Newtonsoft dll.

So, in order to redeploy the assemblies it’s necessary to:

  • Remove all the CLR Stored Procedures that are using these assemblies, otherwise you cannot delete the assemblies.
  • Remove the main assembly and then all its dependencies in the correct order (Note: It’s not necessary to remove Framework assemblies if it’s not changed).
  • At this point you’ll be able to create the assembly again via CREATE ASSEMBLY

All dependent assemblies will be created again in the database (in this case Newtonsoft.Json & Microsoft.AspNet.SignalR.Client). Easy…


However, when tried to import the assembly to SQL Server I’ve got this error:

“Assembly ‘Microsoft.AspNet.SignalR.Client’ references assembly ‘newtonsoft.json, version=, culture=neutral, publickeytoken=XXXXXXXXX.’, which is not present in the current database. SQL Server attempted to locate and automatically load the referenced assembly from the same location where referring assembly came from, but that operation has failed (reason: version, culture or public key mismatch). Please load the referenced assembly into the current database and retry your request.”

I decided to import the assemblies one by one, from down to top.

Firstly I imported Newtonsoft.Json with no problems, but when I tried to import the SignalR client I received this error:


It seems a versions issue, because I’ve imported Newtonsoft.Json version 9, not 6! Let’s see what’s in the assemblies view:

select * from sys.assemblies


It’s correct. I’ve imported version 9 of the library… so why the hell SQL is looking for version 6? Well, if you inspect Microsoft.AspNet.SignalR.Client you’ll see that this library has a reference to Newtonsoft.Json version :S


This version number is used to determine the minimum version number (the library won’t work with previous versions), but it seems SQL Server is trying to match this version number with the assembly version number in its catalog, and if the number is different is unable to import the assembly.

If I’m wrong, please correct me! Thx :)


After spending some hours trying different solutions , the only way to solve that -at this time- is register a previous version of Newtonsoft.Json instead of the latest one.

Any version? Nope.

The only version it worked for me is 6.0.8, previous ‘6’ versions didn’t work because of this error:

“‘Method not found: ‘Newtonsoft.Json.Linq.JValue Newtonsoft.Json.Linq.JValue.CreateNull()’.'”

Finally, once I’ve copied the 6.0.8 version of the Newtonsoft.Json to the server path the import process has worked like a charm.


Now I’m looking for a better solution, because I don’t like using old versions of assemblies. I will update this post with a better solution as soon as possible :D

In the meanwhile, hope this helps.

HowTo: Send Notifications from SQL Server using SignalR



In our current project we’ve to push some notifications directly from the database server (SQL Server 2014) to different apps. For example, we’ve scheduled some SSIS (SQL Server Integration Services) projects that pre-calculates large amounts of data. And well, should be nice notify all the apps when new data is available without unnecessary round trips to database. And you know? we’re already using SignalR in our apps… so, why not try to build a lightweight client for sending SignalR notifications?

Dear architects, please, don’t judge me :P

The idea

  1. I finally decided to create a small project in C# that connects to our SignalR (which is self-hosted in a Windows Service). That project contains a single method for sending notifications with a message (just a string for simplify).
  2. Once the project is ready and tested, the next step is create an ASSEMBLY, that is, a module that contains class metadata and managed code as an object in an instance of SQL Server. By referencing this module, common language runtime (CLR) functions, stored procedures, triggers, user-defined aggregates, and user-defined types can be created in the database.
  3. And finally, create a Stored Procedure that use our previous assembly CLR function (for isolation purposes and decoupling), and then use it in our SSIS projects with a simply ‘Execute SQL Task‘ when the process finalizes.

Creating the C# project

Create a blank class Library project and add the SignalR client (Nuget will add depending assemblies -like Newtonsoft.Json- automatically)


Once we added SignalR Client references to the project we need to create the C# method, so let’s add a class item to the project with this code:

public class SqlServerNotificationsClientService
    internal static HubConnection hubConnection = null;
    internal static IHubProxy hubProxy = null;
    public static void SendMessageToHub(
        string severURL, string hubName, string hubMethod, params object[] args)
            if (hubConnection == null || isConnectionClosed())
                hubConnection = new HubConnection(severURL.Trim()) 
                { Credentials = CredentialCache.DefaultNetworkCredentials };
            if (hubProxy == null || isConnectionClosed())
                hubProxy = hubConnection.CreateHubProxy(hubName.Trim());
            if (isConnectionClosed())
            if(args.Length == 0)
                hubProxy.Invoke(hubMethod.Trim(), args).Wait();
        catch (Exception exc)
            SqlContext.Pipe.Send(string.Format("{0}.{1} error: '{2}'{3}", 
                hubName.Trim(), hubMethod.Trim(), exc.Message, Environment.NewLine));

    private static bool isConnectionClosed()
        return !(hubConnection.State == ConnectionState.Connected
            || hubConnection.State == ConnectionState.Reconnecting
            || hubConnection.State == ConnectionState.Connecting);

Note: Before continue, take a look to the last parameter ‘args’. This parameter allows you to pass a different number of arguments to this method, which is nice because when we invoke the hub’s method we can use the same argument type. Unfortunately this argument type is not compatible with SQL CRL and will crash when creating the Stored Procedure in the database :( So we need to change this argument to a simple string.

This code is just an example, maybe would be a better idea split it into different methods (remember the ‘S’ of SOLID?) :)

Let’s assume the code is ok and is able to connect and send the notification. Now…

Create the SQL Assembly

In order to deploy your assemblies to a SQL Server you need the same version of the .NET Framework installed on the server. Yep, it’s too obvious but…

Create a folder in the server called ‘C:\SQLServerAssemblies’ (or wherever you want) and copy all the assemblies generated by the previous project.


But we’ve not finished yet… Now we’ve to add some related assemblies (assemblies dependencies). Otherwise, assembly creation will fail.


You can find these assemblies in the same server, in the correct version of .NET Framework. In my case:

C:\Windows\Microsoft.NET\Framework64\v4.0.30319 -> Microsoft Framework 4.5

Once we’ve all the necessary assemblies copied, let’s create the assembly. First we need to activate some things in the server:

ALTER DATABASE Falcon SET trustworthy ON

exec sp_configure 'show advanced options', 1;

exec sp_configure 'clr enabled', 1;

exec sp_configure 'show advanced options', 0;

CREATE ASSEMBLY Falcon_SqlProxy from 


Yes! Assemblies created successfully in SQL Server ;)

Creating the SP

Finally we only need to create the Stored Procedure. I recommend you use a custom schema. I’ve created one called ‘clr’

@url nchar(125) ,
@hubName nchar(75),
@hubMethod NCHAR(75),
@message NVARCHAR(MAX)
EXTERNAL NAME [Falcon_SqlProxy].[Falcon.NotificationsClient.SqlProxy.SqlServerNotificationsClientService].SendMessageToHub

Remember the note? Have you changed the last argument of the method  SendMessageToHub to a string?

If you did it and copied all the assemblies to the folder it should have worked like a charm ;)

Now it’s time to test our SP… Success!



If you wanna use this SP from a SSIS project to send notifications to your apps, just add a Execute SQL Task to your project and configure the SQL statement:


Hope this helps! :)

Curiosity: What happens if you remove all the Trim() in the c# code? Why connection to SignalR fails? ;)

See you,

How to: Remove diacritics (accents) in C# strings


Quick note: How to remove diacritics (accents) from a C# string?
Use this extension method ;)

public static string RemoveDiacritics(this string input)
    var stFormD = input.Normalize(NormalizationForm.FormD);
    var len = stFormD.Length;
    var sb = new StringBuilder();
    for (int i = 0; i < len; i++)
        var uc = System.Globalization.CharUnicodeInfo.GetUnicodeCategory(stFormD[i]);
        if (uc != System.Globalization.UnicodeCategory.NonSpacingMark)
    return (sb.ToString().Normalize(NormalizationForm.FormC));

How to: Server Side Filter database (Entity Framework + DevExpress Filter control)

Imagine you have a table with millions of rows. It’s not a good idea showing all these data in a grid or similar, and it doesn’t matter you’re developing a web or desktop application… It’s not a good idea at all.

In these cases we should use conditional filters applied in the data source (usually a database) to get part of the information and return only the rows that match with the specified criteria. Using this technique the amount of network traffic could be reduced to a hundreds of rows or less, depending on the criteria.

There are different ways of accomplish that, but today I’d like to focus on apply this technique using a control from the DevExpress suite: The Filter Editor control.



As you can see the control looks nice, and it’s quite simple to use: You just need to link this control to another (usually a Grid or Tree), and automatically will retrieve the columns and values information from the source control. Then you’ll be able to filter the linked control and get a filter string which you can reuse later in other controls of the suite.

However the previous filter has the disadvantage that is a client filter, and is applied after we retrieve all the rows from the data source, not before. Our goal have to be apply the filter before getting the information.

Today I’ve been playing a little bit with this control, using a SQL Server database that contains a products table with 3.7 million rows and Entity Framework 6.0 as an ORM.

Note: For this test I’d like to use a SQL Tabled-Valued Function to access the data instead of using the default DbSet, because it’s supposed that I’ve to do some hard calculations inside the function. But you can also use the EF DbSet directly, as usual.


Now our goal here is:

  1. Create at runtime a filter over the Products table using the filter control.
  2. Parse that filter string and adapt it to the EF syntax.
  3. Append the filter to the mapped function (thx to IQueryable!) before execution.
  4. Finally, execute the filtered query against the data source

First we need to add an EF designer (we won’t use Code First because we’re going to map a Tabled valued function with is not supported). And add the table Products and the GetAllProducts function.

In our form, we just need to add a couple of Devexpress controls: A GridControl and a FilterControl, linked using the FilterControl SourceControl property (this allows the filter control to retrieve the names and data types of the grid columns).


Then we’ll add three buttons. The first one will retrieve the first 25.000 rows from the database (previously I’ve tried to get all rows from table, but the app crashed with an OutOfMemory exception ;))

The second button (this is the interesting one) will execute the current filter from the FilterControl.

Let’s code:

The first button is really simple. It’s just a query over the GetAllProducts function adding a where clause which will be resolved later (because EF is based on IQueryable<T>) when calling ToListAsync method.

private async void simpleButton1_Click(object sender, EventArgs e)
    using (var context = new TestBigDbEntities())
        var filteredproducts = context.GetAllProducts().Where(p => p.ProductId <= 25000);
        await ExecuteQueryAsync(filteredproducts);

private async Task ExecuteQueryAsync(IQueryable filteredProducts)
    productBindingSource.DataSource = await filteredProducts.ToListAsync();
    Text = clock.ElapsedMilliseconds.ToString("n2");

For the second button we need something more. Something that allows us to translate the filter into a valid where clause, and append it to the function.  The good news are that this functionality is provided natively by Devexpress:

private async void simpleButton2_Click(object sender, EventArgs e)
    var converter = new DevExpress.Data.Linq.CriteriaToEFExpressionConverter();
    using (var context = new TestBigDbEntities())
        var filteredproducts =
        await ExecuteQueryAsync(filteredproducts);

All we’ve to do is that:

Clean and simple. The first one creates a converter for translating the filter into EF syntax, while the second one appends the filter to the EF function using the previous converter.
And finally, here’s the SQL in the profiler. Exactly what I’ve expected.


So well… if you’re using Devexpress in your current project there’re no excuses for not including dynamic filters that user can save and reuse at runtime, uh? :P

Happy coding!


The third button code shows how to get the TSQL syntax from the Filter control, that you can use it to query the database directly ;)

private void simpleButton3_Click(object sender, EventArgs e)
    var tsql = DevExpress.Data.Filtering.CriteriaToWhereClauseHelper.


Extract extra large Icon from a file, including network paths!

Edit (04/16/2014): Added a new function called GetBitmapFromFolderPath, which returns the associated icon from the path to a Folder (or drive!).


Sometimes we are required to show a list of files with their associated icons. This task sounds quite easy, and in fact it is… Except if you have to deal with files in network paths or you want to get different icons sizes, apart the typical 32×32.

If you want to achieve this using managed code (the easy way), there is a static method called ExtractAssociatedIcon under the class Icon to achieve that, but sadly this method doesn’t work with UNC paths neither return other sizes that 32×32 pixels.

In my current project I needed to show four different icons sizes (including the extra-large icon, also called “jumbo”), so I’ve decided to use some functions and structures from the Win32 API. Of course, if anyone knows a better way to do it, please contact with me ASAP :)

First, let’s see the final code:

private static IntPtr GetIconHandleFromFilePath(
    string filepath, IconSizeEnum iconsize)
    var shinfo = new Shell.SHFILEINFO();
    const uint SHGFI_SYSICONINDEX = 0x4000;
    const int FILE_ATTRIBUTE_NORMAL = 0x80;
    const int ILD_TRANSPARENT = 1;
    uint flags = SHGFI_SYSICONINDEX;
    var retval = SHGetFileInfo(filepath, FILE_ATTRIBUTE_NORMAL,
        ref shinfo, Marshal.SizeOf(shinfo), flags);
    if (retval == 0) throw (new System.IO.FileNotFoundException());
    var iconIndex = shinfo.iIcon;
    var iImageListGuid = newGuid("46EB5926-582E-4017-9FDF-E8998DAA0950");
    Shell.IImageList iml;
    var hres = SHGetImageList((int)iconsize, ref iImageListGuid, out iml);
    var hIcon = IntPtr.Zero;
    hres = iml.GetIcon(iconIndex, ILD_TRANSPARENT, ref hIcon);
    return hIcon;

In this code we’re using several API calls. Here’s the tricky part:

First, we need to make call to the SHGetFileInfo function that receives a reference to a structure of type SHFILEINFO, which contains the index of the icon image within the system image list. We will use this index later.

Then we’ve to make is a second call to the SHGetImageList function that receives an output parameter with an IImageList structure, which is modified within the function.

This struct retrieve a COM interface and we need to keep in mind a couple of things:

a) We must use the GUID of this interface in the declaration:


b) And in the improbable case you are going to deploy your project over XP, remember that SHGetImageList is not exported correctly in XP. For this reason you must hardcode the function’s entry point. Apparently (and hopefully) ordinal 727 isn’t going to change…

Once we have that COM interface, we only need to call its GetIcon method, passing a parameter with the desired size, and obtaining a handle to the icon by reference.

After having the handle its really simple create an Icon from the handle, and then convert to a Bitmap, BitmapSource or other:

public static System.Drawing.Bitmap GetBitmapFromFilePath(
 string filepath, IconSizeEnum iconsize)
    IntPtr hIcon = GetIconHandleFromFilePath(filepath, iconsize);
    var myIcon = System.Drawing.Icon.FromHandle(hIcon);
    var bitmap = myIcon.ToBitmap();
    SendMessage(hIcon, WM_CLOSE, IntPtr.Zero, IntPtr.Zero);
    return bitmap;

Tip: It’s very important don’t forget to destroy the resources (Icon) when working with the Win32 API!

This method calls the previous one, obtains the icon’s handle and then creates the icon using the handle. Then creates a bitmap from the icon, destroys the icon and returns the bitmap.

I’ve also created an enumeration with the different flags values:

private const int SHGFI_SMALLICON = 0x1;
private const int SHGFI_LARGEICON = 0x0;
private const int SHIL_JUMBO = 0x4;
private const int SHIL_EXTRALARGE = 0x2;
private const int WM_CLOSE = 0x0010;

public enum IconSizeEnum
 MediumIcon32 = SHGFI_LARGEICON,
 ExtraLargeIcon = SHIL_JUMBO

Finally, retrieving the icon from a file (inclusive if it’s in a network location) and specify the desired size it’s as easy as:

var size = ShellEx.IconSizeEnum.ExtraLargeIcon;
var ofd = new OpenFileDialog();
if (ofd.ShowDialog() == System.Windows.Forms.DialogResult.OK)
    labelFilePath.Text = ofd.FileName;
    pictureBox1.Image = ShellEx.GetBitmapFromFilePath(ofd.FileName, size);



Hope this helps! ;)

Download the source code.