Export (0) Print
Expand All

Customizing Operations By Using Stored Procedures

Stored procedures represent a common approach to overriding default behavior. The examples in this topic show how you can use generated method wrappers for stored procedures, and how you can call stored procedures directly.

If you are using Visual Studio, you can use the Object Relational Designer to assign stored procedures to perform inserts, updates, and deletes. How to: Assign Stored Procedures to Perform Updates, Inserts, and Deletes (O/R Designer)
How to: Assign Stored Procedures to Perform Updates, Inserts, and Deletes (O/R Designer)

Note Note

To read back database-generated values, use output parameters in your stored procedures. If you cannot use output parameters, write a partial method implementation instead of relying on overrides generated by the Object Relational Designer. Members mapped to database-generated values must be set to appropriate values after INSERT or UPDATE operations have successfully completed. For more information, see Responsibilities of the Developer In Overriding Default Behavior.

In the following example, assume that the Northwind class contains two methods to call stored procedures that are being used for overrides in a derived class.

[Function()]
public IEnumerable<Order> CustomerOrders(
    [Parameter(Name = "CustomerID", DbType = "NChar(5)")]
    string customerID)
{
    IExecuteResult result = this.ExecuteMethodCall(this,
        ((MethodInfo)(MethodInfo.GetCurrentMethod())),
        customerID);
    return ((IEnumerable<Order>)(result.ReturnValue));
}

[Function()]
public IEnumerable<Customer> CustomerById(
    [Parameter(Name = "CustomerID", DbType = "NChar(5)")]
    string customerID)
{
    IExecuteResult result = this.ExecuteMethodCall(this,
        ((MethodInfo)(MethodInfo.GetCurrentMethod())),
        customerID);
    return (IEnumerable<Customer>)(result.ReturnValue);
}

The following class uses these methods for the override.

public class NorthwindThroughSprocs : Northwnd
{

    public NorthwindThroughSprocs(string connection) :
        base(connection)
    {
    }

    // Override loading of Customer.Orders by using method wrapper. 
    private IEnumerable<Order> LoadOrders(Customer customer)
    {
        return this.CustomerOrders(customer.CustomerID);
    }
    // Override loading of Order.Customer by using method wrapper. 
    private Customer LoadCustomer(Order order)
    {
        return this.CustomerById(order.CustomerID).Single();
    }
    // Override INSERT operation on Customer by calling the 
    // stored procedure directly. 
    private void InsertCustomer(Customer customer)
    {
        // Call the INSERT stored procedure directly. 
        this.ExecuteCommand("exec sp_insert_customer …");
    }
    // The UPDATE override works similarly, that is, by 
    // calling the stored procedure directly. 
    private void UpdateCustomer(Customer original, Customer current)
    {
        // Call the UPDATE stored procedure by using current 
        // and original values. 
        this.ExecuteCommand("exec sp_update_customer …");
    }
    // The DELETE override works similarly. 
    private void DeleteCustomer(Customer customer)
    {
        // Call the DELETE stored procedure directly. 
        this.ExecuteCommand("exec sp_delete_customer …");
    }
}

You can use NorthwindThroughSprocs exactly as you would use Northwnd.

NorthwindThroughSprocs db = new NorthwindThroughSprocs("");
var custQuery =
    from cust in db.Customers
    where cust.City == "London" 
    select cust;

foreach (Customer custObj in custQuery)
    // deferred loading of cust.Orders uses the override LoadOrders. 
    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();
Show:
© 2014 Microsoft