Galin Iliev's blog

Software Architecture & Development

The case of SqlCommand concurrency issues

Past several days I was investigating some very interesting multi-threading issue. Luckily I was able to reproduce it in about 80% of cases so I could start debugging right away. The problem is the code base is big and I didn’t have an idea why this happens. But first a little background:


The hosting service is WCF application hosted in IIS. The underlying data is fetched from different MS SQL databases depending on the input. Basically depending on what data is needed one or more databases are queried using stored procedures.  To optimize performance since those calls are heavy are parallelized.

The issue

Randomly were logged exceptions of the type “System.InvalidOperationException: ExecuteReader requires an open and available Connection. The connection's current state is connecting/closed/reading.” (state is modified by me since all kind of states are passed there). When calls are made sequential the issue disappears but the overall time for operation increases a lot and thus such fix is not acceptable.

The story

Since the code contained some generics, lambda, loops to parallelize calls (it is .NET 3.5 application which doesn’t use Parallel Extensions). Thus where to start from?!

Of course – #1 try – repro in simple application. Unfortunately didn’t work(keep reading to see whySmile )

Next attempt is to start changing code so if there are some evil caveats (like this) to be avoided in time. So I killed generics.. Result is same Sad smile

Next is removing lambda and replacing with methods and delegates… Result is still same. Sad smile

Replace threading from BeginInvoke() and EndInvoke() to ThreadPool.QueueWorkItem()… Result is still same. Sad smile

Change threading to custom Thread.Start() and thread synchronization… Result is still same. Sad smile

Twisted threading here and there… still same Sad smile

Well… it was time to start cutting code: Cut DAO layer… no change. Cut infrastructure code for accepting calls.. still same. This greatly helped me to narrow the code too look at and I put working simple application inside the service. Strange but this worked with no issues. This could mean only one thing – I am getting very close. Then I started building up this simple method to look like the problem one. And then I spotted the problem – small, tiny problem that cause a lot of headache.

The problem

Examine the code below. All you need is access to a SQL database. Connection string is put in code for convenience.

   1:  class Program {
   3:          class CommandData {
   4:              public Action Command { get; set; }
   5:              public IAsyncResult AsyncResult { get; set; }
   6:          }
   8:          static string connectionString = @"data source=.;initial catalog=AdventureWorks;Integrated security=true;";
   9:          static int commandNumber = 20;
  11:          static void Main(string[] args) {
  13:              var commands = new List<CommandData>(commandNumber);
  15:              SqlCommand cmd = new SqlCommand("SELECT DB_NAME()");
  17:              for (int i = 0; i < commandNumber; i++) {
  18:                  Action act = () => {
  19:                      ExecuteCommand(cmd);
  20:                  };
  22:                  commands.Add(new CommandData { Command=act });
  23:              }
  25:              for (int i = 0; i < commands.Count; i++) {
  26:                  var item = commands[i];
  27:                  item.AsyncResult = item.Command.BeginInvoke(null, null);
  28:              }
  30:              for (int i = 0; i < commands.Count; i++) {
  31:                  var item = commands[i];
  32:                  item.Command.EndInvoke(item.AsyncResult);
  33:              }
  34:          }
  36:          public static DataSet ExecuteCommand(SqlCommand cmd) {
  37:              var result = new DataSet();
  38:              var conn = new SqlConnection(connectionString);
  40:              try {
  41:                  cmd.Connection = conn;
  42:                  var da = new SqlDataAdapter(cmd);
  43:                  da.Fill(result);
  44:                  Console.WriteLine("\t{0}", result.Tables.Count);
  45:                  Console.WriteLine();
  46:              } catch (Exception ex) {
  47:                  Console.WriteLine(ex.ToString());
  48:                  //throw;
  49:              } finally {
  50:                  conn.Close();
  51:              }
  53:              return result;
  54:          }
  55:      }

Run this code and you’ll see the error.


The resolution is simple – just replace for loop body on lines 18 to 20 with following snipped:

   1:  SqlCommand cmd2 = cmd.Clone();
   2:  Action act = () => {
   3:      ExecuteCommand(cmd2);
   4:  };

Run it now and should work fine.

The problem is that same instance of SqlCommand is used in all threads and different connections are assinged to it and not all of them are open as the exception suggests. But the true problem is concurrency on SqlCommand.


  1. Inside the loop try not to use variables declared outside the loop.
  2. Lambda is safe – using delegates has same issues – Mind this problem
  3. Watch your repro application to be as much possible close to original and yet simple. If needed ask a teammate to have a look.

I hope you enjoyed this long read Smile

Download the code.

Windows Phone 7: Panoramic navigation with ZERO code

In my free time (which is not that much as seen by “frequent” blog posts) I started exploring Silverlight 4 and most recently Windows Phone 7 development. WP7 applications are not much different from SL4 apps so kudos for the team for the good architecture.

Here is nice video from MIX 2010 for WP7 platform architecture:

Get Microsoft Silverlight

And here is why I decided to make this post: Here is step by step article how to create panoramic navigation on the phone without writing code at all – just using Expression Blend which is part of free tools:

What's New in ASP.NET 4

Since VS 2010 is out with many new features and there aren’t many books out yet to cover them I am trying to collect resources where those goodies can be learned. Here is my compilation for ASP.NET 4 new features:

Core Services
Web.config File Refactoring
Extensible Output Caching
Auto-Start Web Applications
Permanently Redirecting a Page
Shrinking Session State
Expanding the Range of Allowable URLs
Extensible Request Validation
Object Caching and Object Caching Extensibility
Extensible HTML, URL, and HTTP Header Encoding
Performance Monitoring for Individual Applications in a Single Worker Process

jQuery Included with Web Forms and MVC
Content Delivery Network Support
ScriptManager Explicit Scripts

Web Forms
Setting Meta Tags with the Page.MetaKeywords and Page.MetaDescription Properties
Enabling View State for Individual Controls
Changes to Browser Capabilities
Routing in ASP.NET 4
Setting Client IDs
Persisting Row Selection in Data Controls
ASP.NET Chart Control
Filtering Data with the QueryExtender Control
Html Encoded Code Expressions
Project Template Changes
CSS Improvements
Hiding div Elements Around Hidden Fields
Rendering an Outer Table for Templated Controls
ListView Control Enhancements
CheckBoxList and RadioButtonList Control Enhancements
Menu Control Improvements
Wizard and CreateUserWizard Controls 56

Areas Support
Data-Annotation Attribute Validation Support
Templated Helpers

Dynamic Data
Enabling Dynamic Data for Existing Projects
Declarative DynamicDataManager Control Syntax
Entity Templates
New Field Templates for URLs and E-mail Addresses
Creating Links with the DynamicHyperLink Control
Support for Inheritance in the Data Model
Support for Many-to-Many Relationships (Entity Framework Only)
New Attributes to Control Display and Support Enumerations
Enhanced Support for Filters

Visual Studio 2010 Web Development Improvements
Improved CSS Compatibility
HTML and JavaScript Snippets
JavaScript IntelliSense Enhancements

Web Application Deployment with Visual Studio 2010
Web Packaging
Web.config Transformation
Database Deployment
One-Click Publish for Web Applications

Download PDF version of this whitepaper [1.13 MB]
What's New in ASP.NET 4 and Visual Web Developer on MSDN

Learn Visual Studio 2010 ALM Features

Charles Sterling posted a nice compilation of resources for VS2010. I am reposting here:


My best advice for running these labs is to check out Brian’s blog at:

These labs can be found in the three VS2010 Virtual machines:

  1. Visual Studio 2010 RC (Hyper-V)
  2. Visual Studio 2010 RC (Windows [7] Virtual PC)
  3. Visual Studio 2010 RC (Virtual PC 2007 SP1)

Also coming soon there will be an ALM course on Channel9


Windows Phone 7 Series Developer Training Kit

And here is how apps can be written for Windows Phone 7:

Windows Phone 7 Series promises to be an amazing mobile phone operating system given its innovative user interface and functionality, as well as its great development platform upon which you can quickly and easily build games and applications. With a myriad of new devices, a powerful and immersive software platform, and a new marketplace to attract developers and provide easy access to applications, consumer demand for Windows Phones will be high, and developers will quickly adopt the Windows Phone platform to capitalize on this growing mobile marketplace. This Training Kit will give you a jumpstart into the new Windows Phone world by providing you with a step-by-step explanation of the tools to use and some key concepts for programming Windows Phones.

Check this training kit on Channel9.

Ctrl+Alt+Del in Remote Desktop

Sometimes you need to press Ctrl+Alt+Del for different reasons:

  • Start Task manager (Ctrl+Shift+ESC)
  • Lock Computer (Win+L)
  • Change password (no shortcut)

But how this works in RDC? Very simple but not very obvious:

Instead pressing Ctrl+Alt+Del press Ctrl+Alt+End

Free .NET 4 Certification Exams

Are you reading a lot about .NET 4? Are you keeping your certification record up-to-date? There there is a good news for you: Microsoft learning is making free beta exams on .NET 4 certification track for following exams in this period March 31, 2010 to April 20, 2010:

More info and FAQs on Microsoft Certification Blog.

Hands-on Lab: Silverlight 4 Line of Business Application using WCF RIA Services


screenshot1_2 If you want to dive in Silverlight 4 RIA services for Line of Business apps – there is your hands-on-lab (~31 MB). There is no more struggle, watching videos to see how to trigger some things. Thanks to Swiss MSDN team who developed 108 pages :) hands on lab manual – both in PDF and DOCX.

These features are implemented in the lab:

  • True multi-tier architecture.
  • Entity framework and service layer definition.
  • Data filtering, paging, sorting and grouping.
  • Data modification and validation.
  • Foreign key management.
  • Projections.
  • Programmatic printing from a Silverlight application.
  • COM interop with Microsoft Excel and running full-trust out-of-browser.

Prerequisites and used components:

Optional Components

The hands-on lab manual has detailed instructions on installing all the prerequisites.

Great job Swiss MSDN team


Silverlight 4 Podcast Pack with Tim Heuer

I’ve just found a great set of podcasts with Tim Heuer about Silverlight 4. It is not new now but for sure it is very interesiting. Here is the ToC

Overview: What are the core things to know about Silverlight 4?  How does it compare to Silverlight 3?
Controls: What are the new controls in Silverlight 4?  What new support is there for controls?
Out Of Browser: How do elevated privileges work?  What are toast notifications?  How can Html be hosted in an Out Of Browser application?
Printing and Webcam: How do the new printing/webcam APIs work? 
WPF vs Silverlight: With Silverlight 4, there are fewer differences between Silverlight and WPF.  How do we decide which technology to use?
Text Improvements: What are the big text and international improvements in Silverlight 4?
Networking: How do the networking changes in 4 impact Silverlight developers?  What new things can they do?
Databinding: How does the new Dependency Object support help designers?

Download podcast pack