Stored Procedures that return a definite shape

Next let us consider the scenario where you have a stored procedure that returns you definite shape, i.e. rows and columns, whose structure can be determined at design time.

Here is an example of such a stored proc –

CREATE PROCEDURE GetCustomersInCity
 @City NVARCHAR(30)
AS
SELECT 
      CustomerID, ContactName, Address
FROM 
      Customers
WHERE City = @City

GO

Such a stored procedure is easily represented in a data context as follows –

[StoredProcedure(Name="GetCustomersInCity")]
public StoredProcedureResult<GetCustomersInCityResult> 
    GetCustomersInCity(
        [Parameter(Name="City", DBType="NVarChar(30)")] string city)
{
    return this.ExecuteStoredProcedure<GetCustomersInCityResult>
        (((MethodInfo)(MethodInfo.GetCurrentMethod())), city);
}

Once such a stored procedure has been represented, you can then easily use it in your C# code as follows –

Northwind db = new Northwind("") ; 

StoredProcedureResult<GetCustomersInCityResult> results = 
    db.GetCustomersInCity("London") ;
foreach (GetCustomersInCityResult result in results)
{
    Console.WriteLine(result.ContactName) ;
}

Oh but wait a minute, the above looks very “procedural”, what about that whole new cool LINQ idea that we’ve been talking about? J

Indeed the above could also be written as follows –

var contactNames = 
    from result in db.GetCustomersInCity("London")
    select result.ContactName;

foreach (string contactName in contactNames)
{
    Console.WriteLine(contactName) ;
}

The big/huge/insanely cool advantage of the above is that, the above being a LINQ query, you get “deferred execution”. Remember – DLINQ queries aren’t TSQL queries, but merely a potential to execute a TSQL query. So in the first scenario, the query is executed soon as you call db.GetCustomersInCity, thus if your stored procedure returned 1000 results, you just overloaded your memory by all that data. Whereas, in the second case, the query isn’t actually executed until you hit the “foreach”. This is incredibly helpful, especially in scenarios such as Sorting and Paging, where the underlying DB is probably way more effective in performing such tasks, than your C# code.

A minor, but rather practical difference ;-).