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 (33)

  1. lexp says:

    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?

  2. Sushil Chordia - DataWorks Team says:

    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.

  3. Sahil Malik says:

    This blog is great, I love it.

    Almost as much as I love the fact that we don’t have System.Data.SqlServer now.

    Another advantage I’d like to bring up is, in intellisense I don’t have to do

    System.Data.Sq (DownArrow->Tab).

    Now it’s System.Data.S (Tab).

    🙂

    Okay .. so here’s another one. When I create an application in VB.NET, why does that not add a reference to System.Data, whereas C# does?

    And you already know about this but .. hey I’ll mention it again. Please have the TableAdapters understand CommandBuilder.ConflictResolution .. I mean that’s such a low hanging fruit to miss.

    Yet another question I have is around SqlResultset, I understand why it could be misused and it was removed – but it does serve a good purpose of wrapping a server side cursor. Can we ever in the future expect some server side cursor (other than SqlCommand) functionality in ADO.NET?

    .. more comin’ !! 🙂

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

  5. Greg Low says:

    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

  6. Barry Gervin says:

    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.

  7. Bill Vaughn says:

    Thanks for joining the world of blog…

  8. Cos Callis says:

    Ok, now that we have removed the sqlserver space and unified it with sqlclient, by all accounts a good thing…, where do I get my sqlcontext and pipe objects??? the documentation in the beta 2 suggests that it might be in either system.data or microsoft.data.sqlclient, but I can’t find further reference to either of these…

    My problem is this, I MUST go, via an ODBC, connection to a legacy system to get vast chunks of my data, that are returned to me in very one-dimensional datarecord. I need to reshape this data into several 2-D schema’s (tables) and merge some of the data with local sql data to transform several return ‘codes’ with plain text values. Then I want to serve that to various consumers via (especially SRS). Thus an stored proc, with a CLR element behind it is IDEAL for me. But ALL of the samples that I can find are based off of the now defunct system.data.sqlserver namespace… Every body says how great the merge is, and nowbody’s talking about HOW TO USE IT NOW???? (ok perhaps is just that all the articles I can find via google JUST show the "OLD" way… still, if you tell me where some good samples are, It would be appreciated., Thanks)

  9. Pablo Castro [MS] says:

    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

  10. Cos Callis says:

    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

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

  12. Cos Callis says:

    hmmm. I must be on the wrong build then I find only a sqlpipebase and not the sqlpipe or sqlcontext objects in system.data.

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

  14. Cos Callis says:

    Well, that explains A LOT 🙂

  15. John Papa says:

    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

  16. Cengiz HAN says:

    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

  17. Pablo Castro [MS] says:

    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

  18. Andrey Skvortsov says:

    On little note-plase use "unified model" all across API!Why SqlPipe works only with SqlDataReader but not with DbDataReader?

  19. Pablo Castro [MS] says:

    Andrey,

    SqlPipe only takes "sql" result-sets, it doesn’t take result-sets that originate on other datasources such as DbDataReader or datareaders from other providers.

    This greatly simplifies interaction between the reader and the pipe, because there are no type-system differences. Doing this across type-systems is tricky because you have to define some arbitrary mapping, so we decided not to do it for this release.

    -pablo

  20. Andrey Skvortsov says:

    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.

  21. Pablo Castro [MS] says:

    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

  22. Andrey Skvortsov says:

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

  23. Andrey Skvortsov says:

    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:-)

  24. Andrey Skvortsov says:

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

  25. Pablo Castro [MS] says:

    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

  26. Pablo Castro writes in the new Data Access Team Blog that they are unifying System.Data.SqlServer classes