After a long while I am back to dealing with databases directly from ADO.NET. Guys, this is ridiculous. Any serious library designed like that would be heckled. This is what I found (or rediscovered) in the course of one day:

• if your SqlConnection uses SQL Server local transaction, you must manually transfer the transaction to the SqlCommands you use. If you don’t, you get InvalidOperationException.

  ExecuteNonQuery requires the command to have a transaction when the connection assigned to the command is in a pending local transaction. The Transaction property of the command has not been initialized.

  You are on your own in determining what transaction that is: SqlConnection has this information, but it won’t tell you. More details here.

• If you have a typed DataSet with a number of tables connected by relationship, there is no easy way to fill it from the database all at once. E.g. if I have customers and orders, there is no way to get “all orders for customers with State=’OK'” into the dataset. If you write a JOIN query, it will create one table, and the dataset won’t convert it into multiple tables. You can use OleDB proprietary SHAPE queries, but a) this is not standard SQL, and b) you get some weird table names, so I could not make it to work as well.

  So, you can build a relational structure in memory, but you cannot copy data from DB. The best you can do is filling the tables one by one. Ridiculous.

• SqlDataAdapter won’t automatically initialize insert, update, and delete commands when given the SELECT command. You must use SqlCommandBuilder object, which implicitly modifies the underlying adapter. Looks very weird.

  If their point was separation of concerns, then why the command builder cannot do it without the data adapter? Just take command text and return SqlCommand. More often than not, I do want the commands built if that’s possible. I would either have the adapter build the commands by default (which can be turned off if not desired), or at least have some adapter factory object.

• If you use SqlDataAdapter to add new rows with identity columns, there is no way to get resulting identity values back. The only thing I found short of writing SQL by hand is to build the insert command, steal its text, append something like “; SELECT SCOPE_IDENTITY();” to it, and then execute it by hand, bypassing the adapter.

Bottom line: ouch. So much time spent on problems that simply should not be there in a decently designed system.