First, we will try to call a simple stored procedure. In the sample database, there is a stored procedure called "Ten Most Expensive Products". We will call this stored procedure to get the top ten most expensive products.

Before we can call this stored procedure, we need to model it.

This will add the method Ten_Most_Expensive_Products to the NorthwindDataContext class, and add a new class, Ten_Most_Expensive_ProductsResult, as the result data type of the stored procedure.

Now, from the Program.cs, we can call this stored procedure as follows:

var tenProducts = from p in db.Ten_Most_Expensive_Products() select p;
foreach (var p in tenProducts)
{
Console.WriteLine("Product Name: {0}, Price; {1}", p.TenMostExpensiveProducts, p.UnitPrice);
}

Because we know exactly the return result of the stored procedure, we can also replace the var data type with the specific return type, as in the following code:

IEnumerable<Ten_Most_Expensive_ProductsResult> tenProducts = from p in db.Ten_Most_Expensive_Products() select p;
foreach (Ten_Most_Expensive_ProductsResult p in tenProducts)
{
Console.WriteLine("Product Name: {0}, Price; {1}", p.TenMostExpensiveProducts, p.UnitPrice);
}

The output will look like the following image:

In the above example, LINQ to SQL creates a new type for the return result of the stored procedure. It actually just added the word "Result" after the stored procedure name, to create the name of the return data type. If we know that the return result is a kind of entity, we can tell LINQ to SQL to use that specific entity as the return type instead of creating a new type.

For example, let us create a stored procedure like this:

Create PROCEDURE [dbo].[GetProduct]
(
@ProductID int
)
AS
SET NOCOUNT ON
Select * from Products where ProductID = @ProductID

You can create this stored procedure in Microsoft SQL Server Management Studio, or by right-clicking on the Stored Procedures node in the Server Explorer of Visual Studio 2008, and selecting Add New Stored Procedure from the context menu.

After the stored procedure has been created, drag and drop it into the Product class on the Northwind.dbml design pane. Now, LINQ to SQL will use the Product class as the return type of this stored procedure. The method for this stored procedure will be as follows:

[Function(Name="dbo.GetProduct")]
public ISingleResult<Product> GetProduct([Parameter(Name="ProductID", DbType="Int")] System.Nullable<int> productID)
{
IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), productID);
return ((ISingleResult<Product>)(result.ReturnValue));
}

From the signature of the method, we know that the return type is of the Product class.

Interestingly, if you drag and drop the same stored procedure to the right-hand panel of the Northwind.dbml design window, instead of the Product class, LINQ to SQL will automatically create a new class for the return type. The new method might be as follows:

[Function(Name="dbo.GetProduct")]
public ISingleResult<GetProductResult> GetProduct1([Parameter(Name="ProductID", DbType="Int")] System.Nullable<int> productID)
{
IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), productID);
return ((ISingleResult<GetProductResult>)(result.ReturnValue));
}

The generated return type class GetProductResult is almost identical to the Product class, except that there are no event handling methods.

Another difference between the GetProduct and GetProduct1 methods is that the product you retrieved using GetProduct is within the DataContext. So, any changes you made to it will be committed back to the database if you call db.SubmitChanges() later. However, the product you retrieved using GetProduct1 is not within the DataContext, and thus won't be committed back to the database if you call db.SubmitChanges() later.

Also, when a stored procedure is dropped to an entity class, LINQ to SQL will first check the return result of the stored procedure to make sure it is compatible with the target class. If not, you will get a warning message, and the stored procedure won't be mapped on the model. For example, if you drag and drop the stored procedure Ten Most Expensive Products to the Product class, you will see a dialog box like this:

Now that we have a basic understanding of LINQ to SQL stored procedure processing, we will create a fairly complex stored procedure with an input parameter, an output parameter, a return code, multiple shapes of a single result-set, and multiple result-sets.

CREATE PROCEDURE [dbo].[GetCategoryDetails]
@CategoryID int,
@FullOrPartial bit,
@AveProductPrice money OUTPUT
AS
SET NOCOUNT ON
if not exists (select 1
from categories
where categoryID = @categoryID)
return 10001
if @FullOrPartial = 1
select * from Categories
where categoryID = @categoryID
else
select categoryID, categoryName from Categories
where categoryID = @categoryID
select * from products
where categoryID = @categoryID
select @AveProductPrice = avg(UnitPrice)
from products
where categoryID = @CategoryID
return 0

[Function(Name="dbo.GetCategoryDetails")]
public ISingleResult<GetCategoryDetailsResult> GetCategoryDetails([Parameter(Name="CategoryID", DbType="Int")] System.Nullable<int> categoryID, [Parameter(Name="FullOrPartial", DbType="Bit")] System.Nullable<bool> fullOrPartial, [Parameter(Name="AveProductPrice", DbType="Money")] ref System.Nullable<decimal> aveProductPrice)
{
IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), categoryID, fullOrPartial, aveProductPrice);
aveProductPrice = ((System.Nullable<decimal>)(result.GetParameterValue(2)));
return ((ISingleResult<GetCategoryDetailsResult>)(result.ReturnValue));
}

public partial class GetCategoryDetailsResult

public partial class NorthwindDataContext
{
// modified GetCategoryDetails, to overwrite the generated one
[Function(Name = "dbo.GetCategoryDetails")]
[ResultType(typeof(PartialCategory))]
[ResultType(typeof(Category))]
[ResultType(typeof(Product))]
public IMultipleResults GetWholeOrPartialCategoryDetails(
[Parameter(Name="CategoryID", DbType="Int")]
System.Nullable<int> categoryID,
[Parameter(Name="FullOrPartial", DbType="Bit")]
System.Nullable<bool> fullOrPartial,
[Parameter(Name="AveProductPrice", DbType="Money")]
ref System.Nullable<decimal> aveProductPrice)
{
IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), categoryID, fullOrPartial, aveProductPrice);
aveProductPrice = ((System.Nullable<decimal>)(result. GetParameterValue(2)));
return ((IMultipleResults)(result.ReturnValue));
}
}

Testing the stored procedure

Now, inside the file program.cs, we can add this test method:

static void TestComplexStoredProcedure(int categoryID, bool wholeOrPartial)
{
decimal? avePrice = 0;
IMultipleResults result = db.GetWholeOrPartialCategoryDetails(cate goryID, wholeOrPartial, ref avePrice);
int returnCode = (int)result.ReturnValue;
if (returnCode == 0)
{
if (wholeOrPartial == true)
{
Category wholeCategory = result.GetResult<Category>(). FirstOrDefault();
Console.WriteLine("Category name: {0}", wholeCategory. CategoryName);
Console.WriteLine("Category description: {0}", wholeCategory.Description);
}
else
{
PartialCategory partialCategory = result.GetResult<PartialCategory>().FirstOrDefault();
Console.WriteLine("Category name: {0}", partialCategory.CategoryName);
}
Console.WriteLine("Average product price: {0}", avePrice);
IEnumerable<Product> products = result.GetResult<Product>();
Console.WriteLine("Total products in category: {0}", products.Count());
}
else
{
Console.WriteLine("No category is retrieved, return code : {0}", returnCode);
}
}

Inside the Main method, we call the above method three times as follows:

// get full category details
TestComplexStoredProcedure (2, true);
// get partail category details
TestComplexStoredProcedure (6, false);
// invalid category ID
TestComplexStoredProcedure (999, true);

The first call will return the full category information for category two, including category ID, name, description, and picture. The second call will return only partial information for category six, including category ID, and name. In both of the cases, it will return the products in the category, and the average product price in that category. The third call will print an error message because there is no category with ID 999.

The output is as shown in the following image: