Stored Procedure/UDF Support in DLINQ.
Sorry for the long gap in blogposts, I was at TechED 2006 @ Boston, MA. As Kimberly Tripp mentioned, TechED was a monster conference. I had the pleasure of shaking hands with her and introducing myself – she is really nice. I also had the pleasure of chit chatting at length with Kent Tegels, and Bob Beauchemin – both awesome folks. I also had the good luck chance of meeting up with Scott Guthrie, he showed BLINQ on one of his 3 laptops , what an incredible guy frankly. I met an incredibly long list of people, everyone better than the other.
Perhaps the most memorable moment was on Thursday Morning, 3 AM, when the fire alarm went off at the speaker hotel. I was in deep sleep, and
My first impression was “Goddamit this better be real”.
My second thought was – “I wish they hadn’t served alcohol at the party tonight”.
My third thought was – “Where is my laptop”.
So we ran out, and the building was lined with ambulances, fire brigades, lots of flashing lights. I immediately noticed two things –
a) All the ladies without their makeup on. UHHHH what a shock!! Please no more fires!!
b) All the nerds with their man-purses and their laptops. We left clean underwear up in the hotel room, but by god we carried our laptops outside. J
My two talks went fairly well, also the TechED Birds of Feather was a lot of fun. We had awesome participation, and fantastic representation from the Microsoft folks. Both Bob Beauchemin, and myself were fighting to prevent the crappy microphones from creating annoying feedback, but it was all worth it. The only regret I have is that someone ate my chicken while I was moderating with the microphone. You see, I do like chicken wings!! J
A major part of the discussion was focused on Stored Procedures/UDFs. So I figured, a blogpost elucidating stored proc/UDF support in DLINQ was in order (applause).
So here is something to consider – there are two seemingly contradictory goals.
a) Anything new that comes out must support a huge current install base, and the upgrade path should be least painful. It is a fact that a majority of applications use stored procedures/UDFs as of today, it is also a fact that a number of developers and DBAs prefer to use stored procedures UDFs for various reasons.
b) Stored procedures/UDFs are the biggest pain in the rear for anything that resembles an ORM, or anything that alleviates the need for an end developer, or even DBA to write SQL Queries by hand, embedded as strings.
So it is not a shock that stored procedures/UDFs are supported in DLINQ. But then it is also true that a simple DLINQ query, out of the box, will prefer to use dynamic SQL over stored procedures/UDFs. But you do have enough extensibility points within the framework, allowing you to use your existing or future stored procedure base.
And a word of practical advice, and purely personal point of view: Wean your development and DBAs off the stored procedure crack. You are severely restricting your productivity by being stuck in a stored procedure world.
Okay good, so coming to stored procedures/UDFs, here is how they are supported in DLINQ:
In very short, any stored procedure whose meta data can be accurately determined by the runtime at design time, will be supported. This means, anything that uses temp-tables directly, or does something funky like string concatenation and then execute sp_executesql inside – anything that prevents the DLINQ runtime from accurately extracting the metadata, thus preventing strongly typed support, will not be supported (and I think that is a very reasonable limitation).
As usual, I am going to work with the ever-favorite Northwind database to demonstrate stored procedure support. The good news is, you can easily extract stored procedures in your strongly typed DataContext by passing in the /sprocs command line switch to SqlMetal. This can be done easily by executing the following at commandline –
"c:\Program Files\LINQ Preview\Bin\SqlMetal.exe" /server:. /database:Northwind /pluralize /sprocs /code:Northwind.cs
But before you execute the above, let us enumerate the various funky cases you may be faced with when working with stored procedures, and setup the requisite stored procedures up first.
a) You may have a stored procedure that returns a scalar result.
b) You may have a stored procedure that returns a definite shape, i.e. rows and columns, or sets or rows and columns that do not change as the input parameters change.
c) You may have a stored procedure that returns variable shapes, i.e. depending upon the inputs, the output shape, rows and columns, can change. Note that DLINQ still needs to figure out, at design time, the exact definite set of all possible return shapes. sp_executesql and concatenated strings producing dynamic SQL won’t be supported by this scenario, because a definite set of all possible return shapes is not determinable at design time.
d) You may have a stored procedure that returns various shapes – all in the same resultset, but in a definite order, sequentially.
e) A stored procedure with output scalar parameters.
f) A stored procedure that returns a resultset, and has output scalar parameters.
In subsequent blogposts, I will be covering each one of these cases, before discussing UDF support in DLINQ. So tighten your seatbelts, and enjoy the ride J.