However, what I really wanted was a way to remote data, and not worry about the more OO-centric techniques at this point. As a result, I wrote a framework, DrTier, to do just that. I now have it working the way I want in .NET. DataSets are streamed between client and server, user code is minimal, the app servers are extensible, and I'm able to take advantage of the best things that .NET has to offer. However, ADO.NET is not among them.
For example, if you have a stateless server, you cannot guarantee the SQL statement that was used to Fill the DataSet will be the same when you get back to the app server to update the data. I ended up using the DataSet.ExtendedProperties property to cache the SQL select statement and pass it around between client and server. By doing this, I can guarantee that I'm building the appropriate INSERT/UPDATE/DELETE SQL statements (DML) when I need to update the DataSet.
Speaking of which, ADO.NET wants you to create DML statements for every table. There are countless posts and articles chastising the use of CommandBuilder (poor performance, unoptimized for MSSQL, etc.). Creating your own DML statements at run-time is no picnic, even after we've solved the above problem. If you get schema information based on your SELECT statement, you will see that the types for each field are provider-specific. That means that you would need to have some kind of mapping between provider-specific types and DbType, or find another solution to parameterize your queries (dynamic type instantiation based on the string types returned in GetSchemaTable comes to mind as one possibility).
Another good lesson learned is that when using the Data Access Block, I have found that I can't take advantage of most of the methods in that block because they aren't customizable at all. You want a DataSet loaded with schema information? Good luck. Now you're using Database.DbProviderFactory to create concrete classes like you do in straight ADO.NET. The helper methods lack extensibility, so you're forced into this pattern. Returning fully formed DbCommands that point to a shared DbConnection isn't really even supported. You need to do that manually, too.
I won't go in-depth on the other features I found lacking (unlike Delphi), like ProviderFlags, UpdateMode, TFields, extensive events during reconciliation, robust error resolving support, etc., etc., etc. It seems that ADO.NET forces you into a pattern, and if you want to deviate from that pattern, you better be prepared to work.
Yes, I have an ulterior motive. I want my app servers written in .NET, and I want them to behave like MIDAS app servers so that I can call them from existing Delphi Win32 clients. Next up, I will need to write the interop code to get things working between MIDAS and DrTier. Once all of that is done, we can get our feet wet in .NET without resorting to a complete and total rewrite on both the client and server. So far, so good, but the finish line is a long way off, and I'm afraid ADO.NET will fight me every step of the way.
Disclaimer The opinions expressed herein are my own personal opinions and do not represent my employer's view in any way.