Unification of System.Data.SqlClient and System.Data.SqlServer [Pablo Castro]

Many of us would like to have a space to write about the stuff we do, publish open issues for debate, and in general have a handy, informal point of contact with our developer community. Most of us don’t have the time to actually sign up for a blog and actually keep it interesting (folks such as Angel and Sushil actually do it…don’t know how they find the time :). So we came up with this “Data Access Team Blog” which lets us have a blog without signing up any one of us to maintain it full time.

In opening this blog, I wanted to share with you a decision we made some time ago. I mentioned this decision in the last webcast, back in December, but we didn’t make much noise about this. So here is it. I’d love to hear your comments, like/hate feelings, etc.

Unification of System.Data.SqlClient and System.Data.SqlServer
After a long debate, we decided to combine the two SQL Server providers into one. Basically, we unified them into SqlClient, which can now talk to the server both outside of the server and inside SQL/CLR stored-procedures, functions, etc.
If you’re not familiar with the System.Data.SqlServer namespace, here is a 2-line summary: it’s the namespace that contains the inproc .NET data access provider for SQL Server. Using that provider you can connect to the server “directly”, that is, without using another session and without going through the protocols/networking layer like SqlClient would do (ok, I over-simplified the issue a little bit, but you get the idea).

Why did we do something like this? Well, there are a number of reasons, including:

Usability: There are a lot of users that are already familiar with SqlClient and use it in their day-to-day applications. In the end, at least from the surface, there is no need for another provider if you just want to talk to the same backend server.
Consistency: We want a single, consistent programming model that works inside and outside of the server. Of course, there are some differences because the execution environment is fundamentally different, and we also have some temporary limitations we’ll have to live with for this release.
Long-term story: This is also around consistency, but in the long term. We knew that we wanted a consistent, unified programming model. If we stayed in the path we were (with two providers), then we wouldn’t be able to merge then in the future without breaking existing applications and forcing customers to re-learn how to do in-proc and/or out-of-proc data-access. Nobody likes that :), so it was important to unify the providers in this release.

As for what actually changed, it’s actually relatively little when you look at it from the outside. The only big change in that you no longer need to use System.Data.SqlServer, nor need to reference sqlaccess.dll.
In order to connect to the calling SQL Server session directly, you simply use a new connection string attribute called “context connection”. For example:

using System.Data.SqlClient;
public class MyProcClass {
public static void MyProc() {
using(SqlConnection conn = new SqlConnection(“context connection=true”)) {
// create some command and do something interesting with the connection here

   As you can see, the only thing in the code above that would change to make it work from a client and inside the server is the connection string. So if you want to run inside SQL/CLR and have existing helper libraries for data access or want to re-use some code that uses SqlClient, you’re ready to go :)

   We did our best to keep the two “modes” compatible. There are some features that work only with “regular” connections and others that work only for inproc (or “context”) connections. For example, we’ll not support asynchronous execution, MARS or SqlBulkCopy in inproc-mode. Some of this restrictions are intentional and we plan to keep them, other are only temporary restrictions and we may decide to relax them in future releases (not signing up for it, just saying that it could happen :)

   Note that you can use SqlClient with a regular connection string from inside the server as well. In that case you would create another session, would have to provide credentials, and you would pay for the extra performance hit of connecting to the server over the network infrastructure. All that being said, there are still scenarios where it’s necessary to connect using a non-context connection, and that’s a fully supported scenario.

Any other implications?
We moved a few things around as part of this change. We still have all the classes that expose the SqlContext object, SqlPipe, etc. All those now live in the Microsoft.SqlServer.Server namespace, and they are stored in System.Data.dll instead of SqlAccess.dll.

We’re shooting for having the unified provider included in SQL Server Beta 3. We’re really eager to hear your feedback once you get a chance to play with it.

More information?
There is no much information on the topic right now. I’m putting an article together that discusses data-access from within SQL/CLR in general, so stay tuned.

One more thing I wanted to add: if you guys have any topics you’d like me or other folks in the Data Access Team (ADO.NET, OLEDB, ODBC, etc.) to talk about in this space, feel free to let us know, e.g. by sending feedback to this post or in the newsgroups.

Pablo Castro
Program Manager - ADO.NET Team

Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights