A first demo of the combined data provider

When I did my first demonstration with the combined SqlClient and SqlServer provider in the April CTP version of SQL Server, I was a bit surprised. I wrote a simple stored procedure to run in the server, exactly the way I've always written it to run on the client (modulo sending results back to the client):

// error handling elided
SqlConnection conn = new SqlConnection("context connection=true");
SqlCommand cmd = new SqlCommand("select * from authors", conn);

I was surprised because this produced the error:

System.Security.HostProtectionException: Attempted to perform an operation that was forbidden by the CLR host

The protected resources (only available with full trust) were: All
The demanded resources were:SharedState

After a little experimentation, I discovered they what was causing my problems was using Dispose(). Interestingly, I didn't technically need to use Dispose() (all .NET instances are available for garbage collection when the procedure invocation ends) and, in addition, using the C#/VB.NET "Using" contruct worked fine.

using (SqlConnection conn = new SqlConnection("context connection=true"))
using (SqlCommand cmd = new SqlCommand("select * from authors", conn))

After consulting the Reflector, the two methods are different because the "using" feature calls IDisposable::Dispose on the SqlCommand/SqlConnection itself (after casting). The direct Dispose() call generates a call to ComponentModel.Dispose. Both SqlConnection and SqlCommand inherit (eventually) from System.ComponentModel.Component. That's where the shared state (and the exception) comes in.

Watch out for this. Using "using" (that's Using-End Using in VB.NET) is your best bet.

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.