Demystifying DLINQ: Part 4.3 - Stored Procedures that return variable shapes

Posted on 6/30/2006 @ 9:02 PM in #Vanilla .NET by | Feedback | 4225 views

I hope you are enjoying my DLINQ Series detour where I am covering stored procedure in DLINQ in depth.

After having covered stored procedures that return scalars, and stored procedures that return definite shapes, the next obvious question is – what if there is a stored procedure that depending upon inputs returns variable shapes? Well, DLINQ says, “I WILL SURVIVE” (NSFW).

Do note however, this only applies to stored procedures that return a definite set of variable shapes that can be accurately determined at runtime. Thus if you are doing some weird string concatenation and executing it using sp_executeSql, something DLINQ can’t look “inside of”, you won’t get a strongly typed representation back, and thus won’t be supported in DLINQ. But stored procedures returning variable shapes based on inputs, that can be determined at design time, will be supported.

So, say for instance you had a stored procedure like as below –

CREATE PROCEDURE GetCustomerCities

  @IncludePostalCode BIT

AS

IF (@IncludePostalCode = 1)

      SELECT DISTINCT City, PostalCode from Customers

ELSE

    SELECT DISTINCT City from Customers

GO

 

This stored procedure can be represented inside C# as follows –

/// <summary>

/// Can return the following result types:

/// GetCustomerCitiesResult1

/// GetCustomerCitiesResult2

/// </summary>

[StoredProcedure(Name="GetCustomerCities")]

public StoredProcedureMultipleResult GetCustomerCities(

  [Parameter(Name="IncludePostalCode", DBType="Bit")] System.Nullable<bool> includePostalCode)

{

return

    (

    (StoredProcedureMultipleResult)

        (this.ExecuteStoredProcedure(

            ((MethodInfo)(MethodInfo.GetCurrentMethod())), includePostalCode)));

}

Note the elegant use of System.Nullable<bool> as the input parameter. The obvious question here is, “how will we use it in our code”. That’s pretty darned easy actually.

StoredProcedureMultipleResult results =

    from result in db.GetCustomerCities(false)

    select result ;

 

foreach (GetCustomerCitiesResult1 cityResult in results.GetResults<GetCustomerCitiesResult1>())

{

    Console.WriteLine(cityResult.City + " " + cityResult.PostalCode) ;

}

 

Now you will note that I “should have” passed in GetCustomerCitiesResult2 (because it doesn’t contain definition for “PostalCode”, and that is what would be returned for “false”). Well, as it turns out, our GetCustomerCitiesResult1 is a superset of GetCustomerCitiesResult2, and thus the superset can be used interchangeably. I choose to use that instead of GetCustomerCitiesResult2, because it makes my code simpler J.

Also note that the query isn’t actually executed UNTIL you execute the foreach and results.GetResults<GetCustomerCitiesResult1>().

 

Sound off but keep it civil:

Older comments..


On 7/3/2006 5:04:47 AM JasonBSteele said ..
That's pretty clever. I currently have a couple of sprocs that have a flag bit to indicate whether the Text columns should be returned. The result is used to populate a DataSet which has the Text columns in it but I have made them nullable.

Obviously the solution you have presented above would work for me, but is there a way of achieving it without using sprocs at all? And if so, how would I go about it?

A suggestion is that I take a copy of the SQLMetalled class and make another version of it without the Text columns. I could even get the one with text columns to inherit from it and then extend it to also retrieve the text columns.

...butr then I'd be messing with generated code - but I suppose I could put it in a Partial class.

I'm hoping that the LINQ for Entities stuff may also hope in this area.


On 9/3/2006 10:53:44 PM vikram said ..
Great post.


On 9/4/2006 3:24:35 PM Sahil Malik said ..
Thanks Vikram.


On 11/29/2011 7:11:32 AM puspraj said ..
very good but some importance are miss