Demystifying DLINQ: Part 4.1 - Stored Procedures that return scalar results

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

Stored Procedures that return scalar results

 

Okay so, let us start with our favorite northwind database. Assume that I wish to write a stored procedure that returns me the number of customers. I could write this procedure as follows –

Create Procedure GetCustomerCount

AS

Select Count(*) from Customers

 

If you were to run SqlMetal on the above, the extracted stored procedure definition in the strongly typed DataContext would look like as below –

[StoredProcedure(Name="GetCustomerCount")]

public StoredProcedureResult<GetCustomerCountResult> GetCustomerCount()

{

  return this.ExecuteStoredProcedure<GetCustomerCountResult>(

    ((MethodInfo)(MethodInfo.GetCurrentMethod())));

}

 

Indeed, the above, even though it is a scalar value, the runtime interprets it as a “resultset”. To truly return a scalar, your stored procedure should be written as –

Create Procedure GetCustomerCount

AS

Declare @Count INT

Select @Count = Count(*) from Customers

Return @Count

 

When you do so, the extracted stored procedure definition in the strongly typed DataContext now looks like this –

[StoredProcedure(Name="GetCustomerCount")]

public int GetCustomerCount()

{

   StoredProcedureResult result =

     this.ExecuteStoredProcedure(

      ((MethodInfo)(MethodInfo.GetCurrentMethod())));

   return result.ReturnValue.Value;

}

 

Okay, that seems to make a bit more sense J. Truly, the second implementation is really what can be construed as a stored procedure that returns a scalar value. The first implementation will be discussed along with simple resultsets when I get around to blogging about that.

Now once the definition of your stored procedure is set up, using it is brain dead.

Northwind db = new Northwind() ;

Console.WriteLine("Number of Customers is:" + db.GetCustomerCount()) ;

Now that was easy J.

Sound off but keep it civil:

Older comments..


On 8/12/2006 12:02:40 PM Caesar said ..
Easy does it! But, but, ... what if wanted to return a date?