Tuesday, December 13, 2011 #

EFProviders require MultipleActiveResultSets=True for System.Data.SqlClient connection strings

I was playing with the new Membership API (System.Web.Providers) for the upcoming Virtual TechDays  

While I was trying out a lot of options for using as DB store, one of the obvious choices was SQL Azure.  With SQL Azure, I could offload the Database hosting capabilities to Azure and just focus on my application code.  Of course, it comes at a cost and SQL Azure is a subscription based database available in different sizes and rates there of.

One of the challenges I faced was, working with the Membership API’s connectionstring called as “DefaultConnection”.  The Default Connection is something you would use simply for all connection strings once you upgrade the application to use the New Membership API.  While configuring the connection string, I copied an existing connection string of SQL Azure and changed just the database name.

The new Membership API is supposed to create the database if it doesn’t exist and then use it for creating tables for storing users, roles, etc.,  It failed!!

The error precisely I got was the title i.e. “EFProviders require MultipleActiveResultSets=True for System.Data.SqlClient connection strings.”

I was doubly sure that the attribute existed in connection string and moved it around, as much closer to the other attributes i.e. User Id, Database name etc.,  Still no luck.

This connection string had earlier worked with SQL Azure in another application earlier and I was also damn sure about it.  The only difference was that application wasn’t using the new Membership API.

Then, I changed the case of the attribute (lowercase) to camel case to make it “MultipleActiveResultSets” and voila, it worked!!

I couldn’t believe that this was the problem but eventually figured that, indeed it was Smile

So, if you hit the above error and are sure that the attribute exists, make sure, it is of pascal case.  The Membership API doesn’t like it in other formats Smile

Cheers!!!

Posted On Tuesday, December 13, 2011 3:25 PM | Comments (5)