Background

Ever since I started out working with stuff like Data Access, SQL server, stored procedures and all that sort of gizmo's, I felt a need in certain situations for a stored procedure that is able to take in some kind of array, list, table of items and insert them in a table. This pops out the most in many-to-many relationships between tables. You'd take a .NET object, fill it up with several List<T> properties to hold that sort of info, and then what? Loop over them and open up a set of DB connections for each item? Parse the list into a comma separated string? Or parse it into an xml to process on the server? Sure, they all work, but it's a lot of work every time you need to do it. And between you and me, I'm not too fond of xml processing in SQL Server myself?

Eureka!

So after all those years (well I'm still young, so maybe not all THOSE years) I finally found ONE answer to my problem. Let me repeat the ONE keyword here, because in all honesty, I'm not even sure if this is actually a decent way to get things done. The only thing I guarantee is that it works for me, and kind of makes sense in how it works too (talk about luck!)

How it's done

Part 1: SQL Server

Starting out with the SQL Server part. Keep in mind a tiny scenario where we have a List<Person> object, that we want to insert into a table.

The first thing to do is create a User-Defined Table Type in SQL Server.

So first thing to notice is this is actually the same syntax as to create a regular table, except for the keyword TYPE ? AS TABLE. The rest of it is basic column definition. For simplicity reasons, we only keep track of an Id, a surname and a first name.

Next thing to do is to create the table itself. I'll create them with the same column names for the ease of it, but this of course, is not a restriction.

And to top it off, I'll add a stored procedure to the party that takes care of the insert logic.

So the parameter for this stored procedure is of the User-Defined Table Type Person. This needs to be marked as READONLY, otherwise the procedure won't get created.

Part 2: .NET

Now that the SQL part is out of the way, it's time to start the work in .NET. Let's take a step back and go over what needs to be there. We will definately need:

• A Person object to hold the properties
• A List<Person> to hold the people that need to be added to the table
• Some kind of hocus-pocus to transform a List<T> to a DataTable(System.Data)

Going in a chronological order, we start out with the object itself, which as suspected, is nothing magic.

Next on the list is adding some folks to a List<Person>:

Now, the biggest piece of work in all this would be to parse the List into a DataTable object. Considering the fact that the amount of possible objects is, well, infinite, you shouldn't be translating every single object to a DataTable right? Right. So my solution is to have a DataTable<T> object that holds a List<T>, and translates every property of T into a DataTable object. Nice and easy!

And if you remember the List<Person> object that was created a minute ago, called persons, all we need to do now is call the stored procedure to insert those persons in the table:

Part 3: the outcome

So when this small project started out, there was this situation at hand:

Now when I run this app, we get this:

Conclusion

There is a little bit of prep work to do with this solution, in configuring the SQL objects etc. But then again, all database designs require prep work, so that's something I'm willing to let slide. But to this day, it's the first easy to implement solution I have to a problem I've been dealing with for over four years now.

During my (short) times as a developer, I've come across a few situations where recursive method writing was in order. However, when I look back on a recent conversation I had about recursion, I started wondering why I would ever want to write something recursive. Because it's "clean" code' Because it's cool to say: "hayooo! I wrote something recursive!"? Who knows? One constant factor that is always certain, is that you want to write methods that execute in the most performant way possible. And that is where recursion just won't cut it'

Keeping the old Fibonacci post of a few weeks back in mind, I started investigating versions. The result is a small app that has two methods, one that calulates the x'th number using loops, and one that does the same using recursion.

The result either way:

Already from the beginning, the time difference in calculation between the two methods is noticable. Quite obvious, actually.

So now it's time to do some deeper investigation. For this case, I used a trial version of dotTrace to view the application. When the monitoring of the non-recursive method was done, the big part of the trace was as follows:

10ms to run the actual calculation of the "Looped" method. Nice! No biggie, all is great. Looks performant. Happy feelings. So what about the recursive method? Two simple lines of code vs a little more work in code? Let's see:

Are you kidding me? No of course not. This is kind of expected too, since you always keep on calling methods? And every method call consumes memory, resources... So yeah, big shocker that recursion is not as performant as iteration.

Is that a bad thing? No! Recursion is a great methodology to see things a little different than we normally do. You'll think of the algorithm more than the implementation itself, which to me is a good thing from time to time.

However, if one intends to use recursion in an intensive, deep-leveled method, I'd say think about writing "uglier" code with iterators. It might not save a lot of dev time, but it will probably save you heaps in processing and execution time.

And at the end of the day, execution time is what it's all about, right?

Last Monday I was starting with a personal little challenge, recommended by Bart De Smet (http://community.bartdesmet.net/blogs/bart/Default.aspx). It was about Haskell, a compact functional programming language Definition from Haskell itself: "Haskell is an advanced purely functional programming language. An open source product of more than twenty years of cutting edge research, it allows rapid development of robust, concise, correct software. With strong support for integration with other languages, built-in concurrency and parallelism, debuggers, profilers, rich libraries and an active community, Haskell makes it easier to produce flexible, maintainable high-quality software."

So thinking about the fact that a "Hello world" thing wouldn't cut it, I thought I might as well make a little Fibonacci calculator toy that takes in the number of the element in the row (eg the 5th element) and return me that value. That part got me to a recursive function like this:

fib :: Integer -> Integer
fib n
    | n == 0  = 0
    | n == 1  = 1
    | n  > 1  = fib (n-1) + fib (n-2)

I then started testing it out. fib 1 = 1; fib 5 = 5; fib 6 = 8; fib 7 = 13 and so on. However, all of a sudden I tried to do a fib 356. BIG MISTAKE!!!! The recursive part of the function didn't really like the whole 356 loops thing :) but this is actually where the fun part started.

I put a playful post up on my facebook saying that counting the 356th element of Fibonacci is hard even for a computer. Nothing more nothing less. Only a few minutes after that, I get the answer from Bart saying that it's: 69362907070206748494476200566565775354902428015845969798000696945226974645

Now THAT is a big number! Now being the alltime challenger that Bart is, he asks me how he managed to get that kind of precise number. Clearly int32 won't cut it in here, and double, long and all that is not sufficient either.

So I open up my Visual Studio environment and start puzzling but don't find the answer right away. At that point Bart was offline again so that bought me some time :) My "quick" solution was to split up the value of the number into smaller integer parts, and then pasting them back together in a string when it needed to be shown. I gave that answer on my facebook and he just simply says: System.Numerics.BigInteger in .NET 4.0

"DAMN YOU!" was my shocked reaction. All that time he was living it up with .NET 4.0 that has a BigInteger class while I was mindcrushing myself to find a solution.

So there are some conclusions to keep in mind from this adventure and post:

1. .NET 4.0 might have some fun new toys to make life easier :)
2. It's mighty fun to be mentally challenged!
3. I hope I impressed the showoff :p (Bart, if you?re reading this? :p)