Ever since first reading about Table Valued Parameters introduced in SQL 2008, I have been longing to use them in my web development. A feeling not too unlike elation filled me at the thought of dustbin'ing the workarounds I had used in the past to store multiple records at once to the database (mainly SQL string building in a loop).
That feeling was short lived, however, when I realised that to use this new piece of kit, I would need to pump my VB.Net domain objects into DataTables before sending them on their way as TVPs. This definitely took the shine of the new kid on the block in my eyes - I connect with the ethos of domain driven design, even if I don't practice it perfectly, and having to mutate my creations into DataTables is the complete antithesis to this. I know DataTables have their place, but I decided that Business/Domain Objects were the best choice for the ever evolving enterprise application that I work on.
I came across one glimmer of hope here. More of a teaser I should say, in that Leonard specifically mentions that this is not possible in VB.Net. I toyed about with some of his suggested possible workarounds to no avail and left it back on the shelf.
It popped back into my head last week while working on my SQL stored procedure for generating domain object/repository/test VB.Net code. Looked through it again and done some fresh googling on the subject which lead me to the Asynch CTP extension for Visual Studio. Haven't even looked at any of the asynch stuff in it yet - what caught my eye was the inclusion of iterators for VB.Net.
Dusted off my test code and implemented the new VB.Net iterators. Some initial trial and error coding to get it just right, but I soon had multiple records storing over one SQL connection.
Start off with your table definition, create a type that mirrors it and a stored procedure that does the storing/updating. The SQL code I provide here is basic and not best practice, but it should be enough for you to get my drift. For better sample SQL code, look at Leonard's.
The VB.Net iterator goodness (note, it's worth a few minutes of your time acquainting yourself with the SqlDataRecord type and its members , which are used below in the conversion/mapping code below):
Obviously, this is a very basic example. Again, follow the link to Leonards post where he uses an order header with multiple order detail lines example.
No comments:
Post a Comment