使用預存程式自定義作業

預存程式代表覆寫預設行為的常見方法。 本主題中的範例示範如何使用預存程式產生的方法包裝函式，以及如何直接呼叫預存程式。

如果您使用 Visual Studio，您可以使用物件關係型設計工具來指派預存程式來執行插入、更新和刪除。

備註

若要讀取資料庫產生的值，請在預存程式中使用輸出參數。 如果您無法使用輸出參數，請撰寫部分方法實作，而不是依賴物件關係型設計工具所產生的覆寫。 對應至資料庫產生值的成員，必須在INSERT或UPDATE作業成功完成後設定為相應的適當值。 如需詳細資訊，請參閱 開發人員在覆寫預設行為中的責任。

範例 1

在下列範例中，假設 類別 Northwind 包含兩個方法來呼叫衍生類別中用於覆寫的預存程式。

[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);
}

<[Function]()> _
Public Function CustomerOrders( _
    <Parameter(Name:="CustomerID", DbType:="NChar(5)")> ByVal _
    customerID As String) As IEnumerable(Of Order)

    Dim result As IExecuteResult = Me.ExecuteMethodCall(Me, _
        (CType(MethodInfo.GetCurrentMethod(), MethodInfo)), _
        customerID)
    Return CType(result.ReturnValue, IEnumerable(Of Order))
End Function

<[Function]()> _
Public Function CustomerById( _
    <Parameter(Name:="CustomerID", DbType:="NChar(5)")> ByVal _
        customerID As String) As IEnumerable(Of Customer)

    Dim result As IExecuteResult = Me.ExecuteMethodCall(Me, _
        CType(MethodInfo.GetCurrentMethod(), MethodInfo), _
        customerID)

    Return CType(result.ReturnValue, IEnumerable(Of Customer))
End Function

範例 2

以下類別會使用這些方法進行覆寫。

public class NorthwindThroughSprocs : Northwind
{

    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 …");
    }
}

Public Class NorthwindThroughSprocs : Inherits Northwind
    Sub New()
        MyBase.New("")
    End Sub
    ' Override loading of Customer.Orders by using method wrapper.
    Private Function LoadOrders(ByVal customer As Customer) As  _
        IEnumerable(Of Order)
        Return Me.CustomerOrders(customer.CustomerID)
    End Function

    ' Override loading of Order.Customer by using method wrapper.
    Private Function LoadCustomer(ByVal order As Order) As Customer
        Return Me.CustomerById(order.CustomerID).Single()
    End Function

    ' Override INSERT operation on Customer by calling the
    ' stored procedure directly.
    Private Sub InsertCustomer(ByVal customer As Customer)
        ' Call the INSERT stored procedure directly.
        Me.ExecuteCommand("exec sp_insert_customer …")
    End Sub

    ' The UPDATE override works similarly, that is, by
    ' calling the stored procedure directly.
    Private Sub UpdateCustomer(ByVal original As Customer, ByVal _
        current As Customer)
        ' Call the UPDATE stored procedure by using current
        ' and original values.
        Me.ExecuteCommand("exec sp_update_customer …")
    End Sub

    ' The DELETE override works similarly.
    Private Sub DeleteCustomer(ByVal customer As Customer)
        ' Call the DELETE stored procedure directly.
        Me.ExecuteCommand("exec sp_delete_customer …")
    End Sub
End Class

範例 3

您可以像使用NorthwindThroughSprocs一樣使用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();

Dim db As New NorthwindThroughSprocs()
Dim custQuery = From cust In db.Customers _
                Where cust.City = "London" _
                Select cust

For Each custObj In custQuery
    ' Deferred loading of cust.Orders uses the override LoadOrders.
    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

另請參閱

• 開發人員在覆寫預設行為中的責任