Archive

Archive for May, 2011

The delights of SqlMetal: Multiple ResultSets from a stored procedure

May 17, 2011 5 comments

Ever wanted to return multiple result-sets from a stored procedure using Linq to SQL? Well I have and, like you if you’re reading this, I found that the default settings in Visual Studio generated code did not allow it. The problem was that the code generated only returned the first result set from the stored procedure. I.e:

SELECT * FROM USERS
SELECT * FROM ROLES

Gave me an IEnumerable return value. Now this just wasn’t good enough; so without further ado here’s the simple code to create a database entities ‘later’ using SqlMetal (a Microsoft authored tool supplied with both Visual Studio 2008 and 2010). First of all, the easiest way to start this tool (as with all Visual Studio tools) is to open the command window within the Tools sub-menu in the Visual Studio section of the Start menu.

Once in try this:

SqlMetal /server:. /database:MyDb /code:”DalEntities.cs” /sprocs /namespace:DalEntities /pluralize /serialization:Unidirectional

…and you’ll find that magically the usual dbml style generated code is produced and placed in DalEntities.cs – in this particular case with all the Stored Procedures included also.

What’s more, as previously said, the tool supports multiple result-sets to be returned from stored procedures. As shown below:

[global::System.Data.Linq.Mapping.FunctionAttribute(Name="dbo.TestStoredProcedure1")]
[global::System.Data.Linq.Mapping.ResultTypeAttribute(typeof(TestStoredProcedure1Result1))]
[global::System.Data.Linq.Mapping.ResultTypeAttribute(typeof(TestStoredProcedure1Result2))]
public IMultipleResults TestStoredProcedure1()
{
	IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())));
	return ((IMultipleResults)(result.ReturnValue));
}

It’s like magic, I tell ye!  Have fun!

Advertisements

Oompa Loompas, Royal Weddings and Coca-Cola

It seems to have been an age since I last updated this blog; mainly because there wasn’t that much to say (not unusual, life sometimes throws these long repetitive streaks at you, during which the most interesting thing is that the spider that is oft seen crawling eastwards across the floor decides to crawl in another direction; incidentally: if you are really stuck for something to do you can catch that spider and make it crawl Westwards using a thin piece of thread – if you feel so inclined – but you would, indeed, have to be extra-ordinarily sadistic and bored to the point of brain death). Photo 0091

So: many months through the work, home, beer, TV cigarettes and peanuts routine, I ventured out onto the London Underground (that’s a capital ‘U’ there as it seems, now, to be a ‘brand’ – with T-shirts, mugs and maps – lots of maps) and I ran into some Oompa Loompas, as one would prior to coming home to write about it. Picture embedded.

I also notice that there’s been a Royal Wedding (I presume with a capital R & W, although not because of it’s capitalist tendencies… although???). Yes, sitting peacefully on my own throne reading the paper it appears I missed ‘the moment of the century’ – something I was quite shocked about. When did it happen???  Aaaah – I slept through it, I hope it held as much promise and joi-de-vivre as the last one – if not more…

Anyway – this brought me on to the next weird episode, something I had completely forgotten about; something to do with some terrorist – anyway, apparently he’s been caught or shot; not sure which; but it turned out that the president of America is very happy; no, relieved (I think is the official line).Photo 0104

Lastly, I’ve found a new place to live – a little sad as the old place (this place) is still great – but the new place is nearer to the things I love the most. Food and beer, mainly. Which can make an awfully big impact on one’s belly. SO I had better be careful and get fit – it had to happen sooner or later; I would have preferred later.

So, all in all, a good week – except for those Oompa Loompas, where were they going? Why was there only one female and, more to the point, since when has there been female Oompa Loompas anyway, at the last incarnation of them they were all clones of the one Oompa (and had a very unstable relationship with the world of pop music) – which all seemed rather surreal; I presume the underlying message behind the latest ‘Charlie and the Chocolate Factory’ ‘Drugs – just say no!’, which I sort of remember from my days playing arcade machines in the late 1980’s. Astonishingly, though, Coca-cola (despite the rumours one way and the other) does still contain ingredients from the Coca plant (albeit, I stress, a cocaine-free ingredient). Don’t believe me? Well here’s the extract from the Wikipedia entry:Photo 0097

In the United States, Stepan Company is the only manufacturing plant authorized by the Federal Government to import and process the coca plant,[34] which it obtains mainly from Peru and, to a lesser extent, Bolivia. Besides producing the coca flavoring agent for Coca-Cola, Stepan Company extracts cocaine from the coca leaves, which it sells to Mallinckrodt, a St. Louis, Missouri pharmaceutical manufacturer that is the only company in the United States licensed to purify cocaine for medicinal use.

Cocaine itself was dropped from the secret formulae back in 1900 according to this article: ‘Coca Kick in drinks spurs export fears‘. Amazing! Well – on a different topic, my photograph of the Oompas leaves a lot to be desired, I’m obviously not quite at the paparazzi level; but that’s a different story. Instead to finish with here’s some recently married Esso people – they’re living very happily together in a shop window not far from here. I missed their wedding too.

Oh – incidentally, the piano graveyard picture really has nothing to do with this post – but as I didn’t get off my arse to take the picture of the spider running across the carpet recently I thought I’d add my favourite picture of the moment. Who knows, this may become a running feature. But probably not.

%d bloggers like this: