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?
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.

What?
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”)) {
conn.Open();
// 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.

When?
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

Comments

• Anonymous
  March 22, 2005
  I think System.Data.dll should expose only System.* namespaces. I think you have to use System.Data.SqlServer namespace for SqlPipe, SqlContext etc. classes.
  
  In general I think it is great to have unified programming model for bot in-proc/out-of-proc scenarios.
  
  BTW, Am I right that in Whidbey SqlClient provider is rewritten and now is fully managed, without need for dbnetlib.dll or any other unmanaged lib?

• Anonymous
  March 22, 2005
  Lex, you are right regarding the fact that Whidbey SqlClient Provider doesnt not have any dependencies on DBNetLib.dll. The substitured new code base (SNI) is now integrated in System.Data.dll.

• Anonymous
  March 22, 2005
  The comment has been removed

• Anonymous
  March 22, 2005
  Lexp, you're right in that SqlClient no longer uses dbnetlib.dll. However, we still use native code for the transport layers, we didn't rewrite the whole provider in managed code. The differences being that 1)it's not dbnetlib code and 2)native code is linked into system.data.dll (mixed mode assembly) so we don't need a separate dll.
  
  -pc
  

• Anonymous
  March 22, 2005
  Hi Pablo,
  
  Thanks for sharing this update. It sounds like you're implementing it in a fairly painless way for those of us using it.
  
  And thanks for joining the blogging world :-)
  
  Regards,
  
  Greg

• Anonymous
  March 22, 2005
  http://objectsharp.com/Blogs/barry/archive/2005/03/22/1558.aspx
  
  Pablo, my comments above. I have mixed emotions about this change. I think I have to come out against this because it hides a developers intentions. Usually connection strings are configured strings (external file) but there are huge transactional semantic differences between SqlClient and SqlServer and I liked the fact that my intention was "compiled in" vs. now a configurable thing. This scares me.

• Anonymous
  March 26, 2005
  Thanks for joining the world of blog...

• Anonymous
  March 28, 2005
  The comment has been removed

• Anonymous
  March 28, 2005
  Hi Cos,
  
  There is a short sample in this post that uses the context connection (the only piece of code in the post).
  
  Before, you'd write:
  SqlConnection conn=SqlContext.GetConnection();
  
  now you'd write:
  SqlConnection conn=new SqlConnection("context connection=true")
  
  Also, you need to call Open() on the connection object.
  
  Let me know if you need more info. I wrote an article on the topic, it's in techreview right now so it will be online soon.
  
  -pablo
  

• Anonymous
  March 29, 2005
  Pablo,
  Thanks. What I am most interest in (I think) is the sqlpipe object.
  It occurs to me that the fundemental order of operations is :
  A. stored procedure calls managed code
  B. managed code executes
  C. managed code sends a response back to the SP
  D. SP sends data back to the original caller
  
  In my case I need to build a data set inside the managed code and (again, based on the samples I saw from pre-beta 2) put the DS in the sqlpipe to get it back to the SP and then send that back to the caller. If I'm missing something here please let me know.
  
  I look forward to your forthcoming article, please let me know when/where it is published.
  
  Thanks again,
  
  Cos

• Anonymous
  March 29, 2005
  Cos, The SqlPipe object will also be available in System.Data.dll assembly.
  In the sequence that you mention, you should also be able to get the results of your Managed Stored Procedure via parameters. It should be no different from a normal TSQL procedure.

• Anonymous
  March 29, 2005
  hmmm. I must be on the wrong build then I find only a sqlpipebase and not the sqlpipe or sqlcontext objects in system.data.

• Anonymous
  March 29, 2005
  Cos, this change is not available to public yet. These would be available in the .Net Framework v2.0(aka "Whidbey") Beta2 bits or SQL Server 2005 Beta3, which will be coming soon.

• Anonymous
  March 29, 2005
  Well, that explains A LOT :)

• Anonymous
  April 05, 2005
  Pablo,
  
  This is awesome! I know its just merging 2 namespaces, but I think its a great idea!
  
  Being the ADO.NET and SQL Server geek that I am, I think this blog is awesome!
  
  Thanks guys/gals!
  
  -- Papa

• Anonymous
  April 09, 2005
  Unification of System.Data.SqlClient and System.Data.SqlServer is a great news.
  
  but..
  I think System.Data.dll should contain only namespaces which are under System namespace.
  
  I propose below list for namespace name for classes that expose the SqlContext object, SqlPipe, etc.
  
  System.Data.SqlClient.SqlServer
  System.Data.SqlClient.InProcess
  System.Data.SqlClient.InProc

• Anonymous
  April 09, 2005
  John,
  
  Thanks for the feedback :)
  
  Believe me, it was quite a bit more that "just merging 2 namespaces". We didn't want to simply have two providers pushed together into a single API layer. We wanted consistent behavior as far as we could (there areas where there are still some differences).
  
  The integration of the two providers happened a little bit deeper than the API layer, so the top layer is actually common (e.g. parameter validation, most API-level restrictions, etc.)
  
  -pablo

• Anonymous
  April 16, 2005
  Microsoft.SqlServer.Server

• Anonymous
  May 29, 2005
  On little note-plase use "unified model" all across API!Why SqlPipe works only with SqlDataReader but not with DbDataReader?

• Anonymous
  May 31, 2005
  The comment has been removed

• Anonymous
  May 31, 2005
  OK,but why provider model at all?Most of times I need to "select..." from some other relational datasource and make that data accessible in SQL Server,now I must write TVF for every "select..."(with row provider function) I made,drawing "most used case" useless.Why TVF support IEnumerable=DbDataReader but need that strange method for row generation-all meta data can be taken from DbDataReacord,why separated method definition?
  
  Best wishes.

• Anonymous
  June 01, 2005
  Hi Andrey,
  
  There are several questions there. Let me tackle them separately:
  
  >>> OK,but why provider model at all?Most of times I need to "select..." from some other relational datasource and make that data accessible in SQL Server,now I must write TVF for every "select..."(with row provider function) I made,drawing "most used case" useless.
  
  If what you need is to query another server and then make the resultset available to the client directly, then you can either query directly from the client, or use the SQL Server distributed-query support to query the other server directly.
  
  If you absolutely need your own function, then you'll need to do SendResultsStart/SendResultsRow/SendResultsEnd manually. It still handles the scenario, it's just that it takes some more code.
  
  I understand your scenario and I think it's valid. We may look at doing something like this in a future release.
  
  >>> Why TVF support IEnumerable=DbDataReader but need that strange method for row generation-all meta data can be taken from DbDataReacord,why separated method definition?
  
  The pattern with IEnumerable + "row accessor method" is very simple to implement and extremely efficient. The model where you return a DbDataReader implies that for scenarios where you don't have a DbDataSource (e.g. you produce the data using some heavy computation algorithm) you'd have to implement yor own DbDataReader, which has a lot of methods to implement. So the new IEnumerable+accessor pattern is actually much easier to implement for most cases. The only exception is when the source was already a DbDataReader.
  
  -pablo

• Anonymous
  June 01, 2005
  <quote>
  So the new IEnumerable+accessor pattern is actually much easier to implement for most cases. The only exception is when the source was already a DbDataReader.
  </quote>
  But if we accept provider model as common basis,which means existence of many providers for different datasources then DbDataReader must be commonly used construction,don't you think?So custom support mechanism for such cases completly approve itself.It's very rarely need to really "compute" row,much more common to layer above it some kind of access abstraction and "DbDataReader/provider model" is such abstration(very useful=simple for streaming 2D access).And I believe that support for such "common abstractions" must be implemented to maximum degree.
  
  Thanks.

• Anonymous
  June 01, 2005
  Anyway,I think that making more datasources "accessible" by sql 2005 engine in SIMPLE ways must be priority no1 for easy adaption.Something like-let's make any data sql/xml aware!;-)Same as XmlNavigator abstraction for XML must exist SQL/XQuery abstraction-that's SQL Server:-)

• Anonymous
  June 01, 2005
  And please,PLEASE include "Full-text search" in express otherwise STS is simply bunch of docs,completly incaccessible.File system without search-nonsense!

• Anonymous
  June 02, 2005
  Hi Andrey,
  
  Thanks for your feedback. I understand your passion for unifying patterns, as it happens in this case with the common base classes for ADO.NET.
  
  However, we got very strong feedback from many customers that implementing a full reader for TVFs was really painful. It turns out that in pretty much all of these cases they computed rows, they didn't import them from external sources.
  
  We'd rather choose one pattern instead of two, because it's less surface area, less supportability, etc. Given that it's relatively straight-forward to use a reader in the IEnumerable+accessor pattern, and the other way around is very hard, we chose the first one.
  
  So, as you can see, it's hard to make every scenario trivial, and depending on what's your area of focus one or the other pattern can be more convenient. We had to settle on somethign that works in all scenarios, even if it's suboptimal (from the convenience perspective) in some of them.
  
  -pablo

• Anonymous
  March 20, 2007
  Pablo Castro writes in the new Data Access Team Blog that they are unifying System.Data.SqlServer classes

• Anonymous
  May 31, 2009
  PingBack from http://outdoorceilingfansite.info/story.php?id=5158