Demystifying DLINQ: Part 4.2 - Stored Procedures that return a definite shape

Posted on 6/30/2006 @ 8:59 PM in #Vanilla .NET by | Feedback | 4911 views

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

 

Sound off but keep it civil:

Older comments..


On 7/18/2007 2:30:01 PM C said ..
Please note: The code in above examples is no longer the way to declare LINQ stored procedures. Please see http://msdn2.microsoft.com/en-us/library/bb425822.aspx#linqtosql_topic31 for updated examples.


On 7/23/2008 9:21:03 AM san said ..
Can you suggest a way for adding and deleting a stored procedure through C# code.


This is required since I have to use dynamic tables whose columns are dynamically changing and i use stored procedures to access data from these tables. Any solutions?