Personalizzazione di operazioni utilizzando stored procedureCustomizing Operations By Using Stored Procedures

Le stored procedure costituiscono un approccio comune per eseguire l'override del comportamento predefinito.Stored procedures represent a common approach to overriding default behavior. Negli esempi riportati in questo argomento viene illustrato come usare wrapper di metodi generati per le stored procedure e chiamare direttamente le stored procedure.The examples in this topic show how you can use generated method wrappers for stored procedures, and how you can call stored procedures directly.

Se si utilizza Visual Studio, è possibile utilizzare il Object Relational DesignerObject Relational Designer per assegnare stored procedure per eseguire inserimenti, aggiornamenti ed eliminazioni.If you are using Visual Studio, you can use the Object Relational DesignerObject Relational Designer to assign stored procedures to perform inserts, updates, and deletes.

Nota

Per rileggere i valori generati dal database, usare i parametri di output nelle stored procedure.To read back database-generated values, use output parameters in your stored procedures. Se non è possibile usare parametri di output, scrivere un'implementazione di metodo parziale anziché basarsi sugli override generati da Object Relational DesignerObject Relational Designer.If you cannot use output parameters, write a partial method implementation instead of relying on overrides generated by the Object Relational DesignerObject Relational Designer. I membri con mapping ai valori generati dal database devono essere impostati su valori appropriati dopo il completamento delle operazioni INSERT o UPDATE.Members mapped to database-generated values must be set to appropriate values after INSERT or UPDATE operations have successfully completed. Per ulteriori informazioni, vedere responsabilità dello sviluppatore nell'override del comportamento predefinito.For more information, see Responsibilities of the Developer In Overriding Default Behavior.

EsempioExample

DescrizioneDescription

Nell'esempio seguente si presupporre che la classe Northwind contenga due metodi per chiamare stored procedure usate per gli override in una classe derivata.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.

CodiceCode

[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

EsempioExample

DescrizioneDescription

Nella classe seguente vengono usati questi metodi per l'override.The following class uses these methods for the override.

CodiceCode

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 …");
    }
}
Public Class NorthwindThroughSprocs : Inherits Northwnd
    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

EsempioExample

DescrizioneDescription

È possibile usare NorthwindThroughSprocs esattamente come si utilizzerebbe Northwnd.You can use NorthwindThroughSprocs exactly as you would use Northwnd.

CodiceCode

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

Vedere ancheSee Also

Responsabilità dello sviluppatore nell'override del comportamento predefinitoResponsibilities of the Developer In Overriding Default Behavior