Returning multiple datasets from a stored proc in VB

I have an encrypted SQL Server stored proc that I run with (or the vb .net equivalent code of):

declare @p4 nvarchar(100)
set @p4=NULL
declare @p5 bigint
set @p5=NULL
exec AA_PAY_BACS_EXPORT_RETRIEVE_S
@PS_UserId=N'ADMN', 
@PS_Department=N'',
@PS_PayFrequency=2,
@PS_ErrorDescription=@p4 output
select @p4, @p5

this returns 2 datasets and the output parameters for the results, the datasets are made up of various table joins etc, one holds the header record and one the detail records.

I need to get the 2 datasets into a structure in VB .net (e.g. linq, sqldatareader, typed datasets) so that I can code with them, I don't know what tables any of this comes from and there are alot of them Whooopeee!!!

I came close using Linq to SQL and IMultipleResults bu开发者_运维技巧t got frustrated when I had to recode it every time I made a change to the designer file.

My feelings are that there must be a simple way to do this, any ideas?

I'd put the results into an untyped dataset. This is the most flexable. You can loop though the columns collection of each table in the dataset to the get the field names. Each result set will be a table in the tables collection. (the tables still will be named Table1, Table2, etc unfortunately).

However, I'm unclear as to the exact nature of your problem.

I came close using Linq to SQL and IMultipleResults but got frustrated when I had to recode it every time I made a change to the designer file.

And why was that?

As far as I remember you can code whatever you want based on the class created by Linq-to-SQL and no need to recode if you make changes to the designer file... Unless you code it inside the auto-generated *.Designer.vb file.