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>().