Personalizar operaciones utilizando procedimientos almacenados
Los procedimientos almacenados representan un enfoque común para invalidar el comportamiento predeterminado. Los ejemplos de este tema muestran cómo utilizar los contenedores de método generados para los procedimientos almacenados, y cómo se puede llamar directamente a los procedimientos almacenados.
Si utiliza Visual Studio, puede usar el Object Relational Designer para asignar los procedimientos almacenados para realizar operaciones de inserción, actualización y eliminación.
Nota
Para volver a leer los valores generados por la base de datos, utilice parámetros de salida en los procedimientos almacenados. Si no puede usar parámetros de salida, escriba una implementación de método parcial en lugar de confiar en las invalidaciones generadas por Object Relational Designer. Los miembros asignados a los valores generados por la base de datos deben establecerse en los valores adecuados una vez que se ha completado correctamente una operación INSERT o UPDATE. Para obtener más información, consulte Responsabilidades del desarrollador al invalidar un comportamiento predeterminado.
Ejemplo 1
En el ejemplo siguiente se da por hecho que la clase Northwind contiene dos métodos para llamar a los procedimientos almacenados que se utilizan para las invalidaciones en una clase derivada.
[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
Ejemplo 2
La clase siguiente utiliza estos métodos para la invalidación.
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
Ejemplo 3
Puede utilizar NorthwindThroughSprocs de la misma forma que utilizaría 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