Producer-Consumer scenario in .NET using BlockingCollection

The BCL contains helpful classes to build your producer-consumer scenarios. We want to have a thread or several that produces data and one or several threads that consume that data. In this article I will show a simple single producer, single consumer scenario. BlockingCollection contains the necessary logic to support this. Consider the following code:

using System;
using System.Collections.Concurrent;
using System.Threading;
using System.Threading.Tasks;

namespace BlockingCollection
{
    class Program
    {

        private static readonly BlockingCollection<int> _blockingQueue = new BlockingCollection<int>(boundedCapacity: 1); 


        static void Main(string[] args)
        {
            Task.Run(() =>
            {
                foreach (var item in _blockingQueue.GetConsumingEnumerable())
                {
                    Console.WriteLine("Hey I got this item: " + item + "!");
                    Thread.Sleep(1000);
                }

            });

            int[] nums = { 1, 3, 8, 11, 94, 28 };
            foreach (var n in nums)
                _blockingQueue.Add(n);
            _blockingQueue.CompleteAdding();

            Console.WriteLine("Hit the any key!");
            Console.ReadKey(); 
        }

    }
}

We create a blocking collection with a bound capacity of one item. This will only accept a single item at a time. This means that our second call to the .Add() method will actually block. We also use the .CompleteAdding() method to signal that we are finished filling up values. It is also important to start up our consumer before the blocking call. I use Task.Run()here to start up a new thread. We will inside that thread Call the .GetConsumingEnumerable() to set up our consumer. Note that I also use Thread.Sleep to make the consumer wait a bit, this is only for testing and normally you would of course not wait here. Note also that you could spawn multiple consumers with Task.Run or similar to support 1 producer, multiple consumers scenarios. For multiple producers you could use a single shared blocking collection or possibly multiple blocking collections. BlockingCollection is very flexible. It is important that you call .CompleteAdding() on your producer side to allow the consumers to finish also their execution.
What if you want to not have a FIFO ordering in your Producer-Consumer scenario, but say a LIFO scenario? As you probably can see, if you instantiate the BlockingCollection, the constructor takes overloads to somethhing called an IProducerConsumerCollection Collection. So we just adjust our instantiation like the following (note that I had to remove the boundedCapacity here set to 1 to make it work!):

   private static readonly BlockingCollection<int> _blockingQueue = new BlockingCollection<int>(
            new ConcurrentStack<int>()); 

So now we get our new output:

Hit the any key!
Hey I got this item: 28!
Hey I got this item: 94!
Hey I got this item: 11!
Hey I got this item: 8!
Hey I got this item: 3!
Hey I got this item: 1!

The BCL contains several interesting classes in the System.Collections.Concurrent namespace. Happy .NET coding!

How to open up a SQL connection with SQL Management studio 2012 through Powershell

Sometimes it is nice to just log into a SQL server database with just running a command. To maintain security, we let our user input the password manually and use Powershell to start up SQL Server Management Studio 2012. If you got another version of SQL Server Management Server (SSMS), just adjust the path to ManagementStudio.

$domain="SOMEDOMAIN"
$user="SomeUser"
$workingdir="c:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio"
$databaseserver="somedbserver.somedomain.net"
$cmd="ssms.exe"
$arguments=" -S $databaseserver" 

$domainuser = $domain + "\" + $user

$response = Read-host "Enter password" -AsSecureString 
#$secpasswd = [Runtime.InteropServices.Marshal]::PtrToStringAuto([Runtime.InteropServices.Marshal]::SecureStringToBSTR($response))

$credential = New-Object System.Management.Automation.PSCredential ($domainuser, $response)

Start-Process -WorkingDirectory $workingdir -FilePath $cmd -Argument $arguments -Credential $credential 

You can add an icon on your desktop and assign it a shortcut to powershell and then paste the script above to a .ps1 file and then as the argument of the shortcut point to a .ps1 with the script above.

How to display the SQL involved for in Entity Framework programatically with DbContext and ObjectContext

As a developer, we often use an Object-Relational Mapper (ORM) to abstract from the way we work with a database. Gone are the days of building a SqLCommand object, setting the CommandText and executing row by row the result set, as used with ADO.NET. Today, most .NET developers use EntityFramework to work with the database. But this abstraction is all and well, and makes us work more efficient. Sadly, many developers are agnostic to the fact that despite they get the results out from the database, they do often do so in a slow manner. The reason of this is often not that there is a lot of data in the database, but we use Entity Framework queries that generate the wrong sql, i.e we get the results, but the SQL involved got a poor performance. You can use LinqPad for example to display the SQL involved in Entity Framework queries. But we can also achieve this using programatically methods, with C#. Here is an extension class I wrote to achieve this. The extension methods works with both data contexts that inherit from DbContext and data contexts that inherit from ObjectContext.

using System;
using System.Data.Entity.Core.Objects;
using System.Linq;
using System.Reflection;

namespace Hemit.OpPlan.Data.EntityFramework
{
    
    public static class IQueryableExtensions
    {

        /// <summary>
        /// Shows the sql the IQueryable query will be generated into and executed on the database
        /// </summary>
        /// <param name="query">The IQueryable to analyze</param>
        /// <param name="decodeParameters">Set to true if this method should try decoding the parameters</param>
        /// <remarks>This is the generated SQL query in use for Entity Framework. This works using ObjectContext</remarks>
        public static string ShowSqlUsingObjectContext(this IQueryable query, bool decodeParameters = false)
        {
            var objectQuery = (ObjectQuery)query; 
           
            string result = ((ObjectQuery)query).ToTraceString();

            if (!decodeParameters)
                return result; 

            foreach (var p in objectQuery.Parameters)
            {
                string valueString = p.Value != null ? p.Value.ToString() : string.Empty;
                if (p.ParameterType == typeof(string) || p.ParameterType == typeof(DateTime))
                    valueString = "'" + valueString + "'";
                result = result.Replace("@" +p.Name, p.Value != null ? valueString : string.Empty); 
            }
            return result; 
        }

        public static string ShowSqlUsingDbContext(this IQueryable query, bool decodeParameters = false)
        {
            var memberInfo = query.GetType().BaseType;
            if (memberInfo != null)
            {
                var internalQueryField = 
                memberInfo.GetFields(BindingFlags.NonPublic
              | BindingFlags.Instance).FirstOrDefault(f => f.Name.Equals("_internalQuery"));
                if (internalQueryField != null)
                {
                    var internalQuery = internalQueryField.GetValue(query);
                    var objectQueryField =
                        internalQuery.GetType().GetProperty("ObjectQuery"); 

                    // Here's your ObjectQuery!
                    if (objectQueryField != null)
                    {
                        var objectQuery = objectQueryField.GetValue(internalQuery) as ObjectQuery;
                        string sql = ShowSqlUsingObjectContext(objectQuery, decodeParameters);
                        return sql;
                    }
                }
            }

            return null;
        }

    }
}

Note that when we use an IQueryable inside a DbContext, the BaseType is actually a DbQuery, and this wraps the ObjectQuery inside a field called "_internalQuery". In addition, we get a property inside this field that is called "ObjectQuery". So we can get hold of the ObjectQuery inside a DbQuery. When we got hold of the ObjectQuery, it is easy to decode the contents using ToTraceString() method and if we want to further decode the parameters EntityFramework generates, we can do so using the Parameters property of ObjectQuery. We can then interpolate the SQL parametrization and get the SQL string most readable, if we like this form. Of course, some developers rather like the parametrized version. There may be some queries that don't have any parameters at all, but this is fine. Of course, all this parametrization business is to hinder SQL injection. Please do not resort to creating methods that accepts such "clean sql", you may easily generate an attack vector into your system if you try do adjusts queries so and not being careful. With this extension method we can easily test it out:

using SomeAcme.EntityFramework;
using Nunit.Framework; 

[Test]
public void TestGettingSomeSql(){

 using (var context = new SomeAcmeContext()){
  IQueryable query = context.SomeDataEntity.Where(x => x.SomeProperty == 123).AsQueryable();
  
  string sql = string.Empty; 

  //If this is a dbContext: 

   sql = query.ShowSqlUsingDbContext(decodeParameters: true);

   //Or if this is an ObjectContext: 

   sql = query.ShowSqlUsingObjectContext(decodeParameters: true); 

   Console.WriteLine(sql);

 }
}

We create a query, using the .AsQueryable() extension method in Linq and we then pass the IQueryable object to the extension methods of the class shown earlier. Of course, the extension method to use depends on the type of data context you work with. Either a DbContext or an ObjectContext.