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:<\/p>\n
SqlConnection<\/code> uses SQL Server local transaction, you must manually transfer the transaction to the SqlCommand<\/code>s you use. If you don’t, you get InvalidOperationException<\/code>.\nExecuteNonQuery 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<\/font>.<\/p>\nYou are on your own in determining what transaction that is: SqlConnection<\/code> has this information, but it won’t tell you. More details here<\/a>.\n<\/li>\n- If you have a typed
DataSet<\/code> 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.\nSo, 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.<\/li>\n
SqlDataAdapter<\/code> won’t automatically initialize insert, update, and delete commands when given the SELECT command. You must use SqlCommandBuilder<\/code> object, which implicitly modifies the underlying adapter. Looks very weird.\nIf 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<\/code>. 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.<\/li>\n- If you use
SqlDataAdapter<\/code> 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.<\/li>\nBottom line: ouch. So much time spent on problems that simply should not be there in a decently designed system.<\/p>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"Layout":"","footnotes":""},"categories":[3,14,4],"tags":[],"class_list":["entry","author-ikriv","post-526","post","type-post","status-publish","format-standard","category-dotnet","category-db","category-hack"],"_links":{"self":[{"href":"https:\/\/ikriv.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/526","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ikriv.com\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ikriv.com\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ikriv.com\/blog\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/ikriv.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=526"}],"version-history":[{"count":0,"href":"https:\/\/ikriv.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/526\/revisions"}],"wp:attachment":[{"href":"https:\/\/ikriv.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=526"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ikriv.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=526"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ikriv.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=526"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}