Customizing Operations by Using Stored Procedures Exclusively (LINQ to SQL)

Access to data by using only stored procedures is a common scenario.

Example

Description

You can modify the example provided in Customizing Operations By Using Stored Procedures (LINQ to SQL) by replacing even the first query (which causes dynamic SQL execution) with a method call that wraps a stored procedure.

Assume CustomersByCity is the method, as in the following example.

Code

<[Function]()> _
Public Function CustomersByCity( _
    <Parameter(Name:="City", DbType:="NVarChar(15)")> ByVal _
        city As String) As IEnumerable(Of Customer)

    Dim result = Me.ExecuteMethodCall(Me, _
        (CType(MethodInfo.GetCurrentMethod(), IEnumerable(Of  _
        Customer))), city)
    Return CType(result.ReturnValue, IEnumerable(Of Customer))
End Function

[Function()]
public IEnumerable<Customer> CustomersByCity(
    [Parameter(Name = "City", DbType = "NVarChar(15)")] 
    string city)
{
    IExecuteResult result = this.ExecuteMethodCall(this,
        ((MethodInfo)(MethodInfo.GetCurrentMethod())),
        city);
    return ((IEnumerable<Customer>)(result.ReturnValue));
}

The following code executes without any dynamic SQL.

Dim db As New Northwnd("...")
' Use a method call (stored procedure wrapper) instead of
' a LINQ query against the database.
Dim custQuery = db.CustomersByCity("London")

For Each custObj In custQuery
    ' Deferred loading of custObj.Orders uses the override
    ' LoadOrders. There is no dynamic SQL.

    For Each ord In custObj.Orders
        ' Make some changes to customers/orders.
        ' Overrides for Customer are called during the execution
        ' of the following:
        db.SubmitChanges()
    Next
Next

NorthwindThroughSprocs db = new NorthwindThroughSprocs("...");
// Use a method call (stored procedure wrapper) instead of
// a LINQ query against the database.
var custQuery =
    db.CustomersByCity("London");

foreach (Customer custObj in custQuery)
{
    // Deferred loading of custObj.Orders uses the override
    // LoadOrders. There is no dynamic SQL.
    foreach (Order ord in custObj.Orders)
    {
        // Make some changes to customers/orders.
        // Overrides for Customer are called during the execution
        // of the following.
    }
}
db.SubmitChanges();

See Also

Concepts

Responsibilities of the Developer In Overriding Default Behavior (LINQ to SQL)