Rants Tagged with “ADO.NET”
Glenn Johnson has a very good book here on ADO.NET 2.0. Unfortunately, it just good not great. Here are my pros and cons:
Pros:
- Well written and thought out.
- Excellent coverage of ADO.NET Trace Logging.
- Coverage of LOBs/BLOBs/CLOBs is very well thought out.
- Discussion of Connection Pooling is very good.
- Coverage of writting your own classes that work with System.Transactions is invalulable.
Cons:
- Too many basic topics covered for an "Advanced Topics" book.
- ASP.NET GridView/WinForms GridView chapters are unnecessary and incomplete.
- Code examples are terse and somewhat unreadable (no blank lines).
- Some information inaccurate (e.g. Suggestion of using Database Mirroring in SQL Server 2005 which was dropped as a supported feature.)
- SQL Server Specific...lackluster Oracle, ODBC, OleDb coverage.
- Data Caching only discusses caching with SqlDependencyCache. There are a myriad of caching options, and this is only one of them.
While not really a problem with the book, I disagree with the author in a number of assertions:
- He pushes the idea of GUIDs as keys, but never discusses the index fragmentation issue with GUIDs as keys.
- His discussion of SQLCLR doesn't warn the users enough (I know "enough" is a subjective phrase) that they shouldn't write all their code in SQLCLR.
- Mentions that "The 8,000-byte limit is much higher than you should ever need." when discussing SQLCLR User Defined Types. I disagree since a single object might not reach that, but a shallow object graph will reach 8K very easily.
- No comparison between SQLCLR UDT's and XML Typed XML.
- Using XML in SQL Server is touted instead of disuaded. More often than not, storing your XML in SQL Server just to have it there (or without dissecting it into relational data) will just hurt performance and raise the complexity of a system.
- He should have had coverage of ADO.NET and Custom Data Objects as well.
I gave the book a four out of five starts on Amazon.com because I think it will be a valuable resource for most developers. But it is not a perfect book.
I have been a fan of Typed DataSets since the PDC Beta of .NET. I’ve been asked to detail my recent criticism of Table Adapters in the .NET 2.0 Typed DataSets. Here are the points that I am most concerned with:
- TableAdapter designers are fragile. Editing the first query in a TableAdapter attempts to make modifications to other queries to keep them in sync. This works the majority of the time with all text queries, but will lose changes occasionally.
- Choosing to “Refresh the Data Table” in the DataAdapter Wizard causes a SELECT statement to be put after the INSERT (and I think the UPDATE) statement(s). If you change the SelectCommand, this statement isn’t changed. Also, there is no way except for the wizard or manually editing the .xsd file to force this change to happen.
- While I understand that the purpose of the way that Typed DataSets are to be used are more for a RAD type of environment, I think that the TableAdapters do not work well with stored procedures. Changing the resultsets inside a stored procedure seems to confuse the Table Adapter.
- One of the most common (and hopefully performant) suggestions I gave to users in 1.x was to use a DataAdapter to fill their DataSet by returning multiple resultsets. As Table Adapters work today, each table adapter opens and closes a connection (though this is mitigated by pooling) and returns a single resultset. I would like to see a better story to the batch return of data.
- The way that TableAdapters put together their connection strings by stringing together parts of the namespace in the web.config (or similar file) is frustrating in that either code that needs connection strings, must either share the connection strings that the Typed DataSet generate or must keep two copies of the connection string. I would like to see it where you can specify the connection string for a particular TableAdapter or DataSet to use a specific name in a .config setting.
- I should be able to create a DataAdapter as part of the Typed DataSet designer (though not specifically a TableAdapter issue). If it’s a design surface, it should be able to handle DataAdapters for more complex scenarios.
- The ClearBeforeFill option is intriguing but making the default be clear the Data Table before filling further clouds one of the most intriguing features of DataSets: the ability to be a growing cache of data. I make a case for this in my book. I think this is a technological fix to a training problem.
Anything I missed?
I was having a chat with David Sceppa about TableAdapters recently when he mentioned that in the final VS 2005 bits, the TableAdapters will use timestamp fields for concurrency. I told him flatly I didn't think it worked, but I was wrong. If you create a Table in a Typed DataSet in VS 2005 and include the timestamp field in the select statement, it will use the timestamp field for concurrency. Awesome!
(Note: ASP.NET 2.0 DataSources use their own source code for concurrency so that doesn't work at all in DataSources AFAIK.)
Recently I posted about Timestamps and CommandBuilders and I got a well informed reply by Luciano Evaristo Guerche concerning a related approach of using BINARY_CHECKSUM in SQL Server to do the same thing. I think Luciano's response means to say that if you can't use Timestamp's in the database (like you don't have control over the schema) then BINARY_CHECKSUM is an improvement over the brute force concurrency that CommandBuilders do by default.
I thought Luciano was right, but I wanted to prove it out. I ran some tests using a Typed DataSet and the Northwind Customer table:
- I changed all 91 records of the customer table by changing a single field (Address).
- I updated the table with the brute force method, the BINARY_CHECKSUM method and the timestamp method.
- I ran it in batches of iterations to get some scale. I changed the entire table in each batch. I ran it in batches of 5, 25, 100, 250, 500 and 1,000 iterations.
In general the results were what I thought. The brute force performed worst, the BINARY_CHECKSUM was a performance boost, but not to a great extent; and the timestamp showed considerable improvement. Here is a chart of my results. The number of rows updated corresponds ot the batches (e.g. 5 iterations x 91 rows = 455, etc.) and I have calculated the row/second response time from each result:
Let me know if you have any questions...
Since my announcement last year that there would not be an update to my book ("Pragmatic ADO.NET") to show the changes in ADO.NET 2.0, I have been asked what book I would recommend. I have scoured the ADO.NET 2.0 books and finally have a firm recommendation:
Pro ADO.NET 2.0, by Sahil Malik
While it is not the book I would have written, it is a very good book. It covers ADO.NET 2.0 very thoroughly and in a very readable manner. If you need an ADO.NET 2.0 book, go get this book today.
My new article on how to get started with
NHibernate is up on DevSource. Check it out.
Thanks to Bill Booth via the Windows Off Topic Mailing List, I was concerned that the DTC was being used for intra-database transactions with SQL Server 2000. I was looking at timing and using System.Transactions with SQL 2000 is through the roof slower than using traditional client-side transactions. Interestingly SQL 2005 doesn't have this limitation. Lazar Florin has a great blog post that explains what is happening here (found here).
Short story is that SQL 2000 can't automatically use the Lightweight Transaction Coordinator inside of System.Transactions (SQL 2005 has promotable transactions and can use it fine). Luckily it is not too hard to make SQL 2000 behave (as seen in Lazar's blog entry). Great find Bill!
I am currently doing a bunch of evaluations on OR Mappers, Code Gen and Business Object Frameworks for my new book. If anyone knows about anything great that might go unnoticed, please drop a comment or send me an email!
Thanks!
I was surprised to learn that someone just finished a review of my ADO.NET 1.x book. Wow. If you interested, follow the link!
My article on upgrading tips for your Typed DataSets is up on DevSource. Take a look!
