Customizing Insert, Update, and Delete Operations

In This Topic

By default, the SubmitChanges method persists modified data via the standard insert, update and delete SQL commands (adapted to the current SQL dialect). These commands include the 'where' clauses that are built based on the columns mapped to your entity classes and Update Checks set for these columns. The update commands include setting of all changed fields, and the insert commands set all mapped columns not being auto-generated.

In some cases, this may be not the behaviour you want. For example, you may want to perform some validation, calculate some of the columns or even change data in other tables when saving changes to an entity object. To configure such custom logic in your DataContext type, you can implement the corresponding partial methods. This can be done either manually in the code or by mapping these methods to stored procedures in Entity Developer. Below we describe each option in details.

Implementing Custom Insert, Update, or Delete Methods Manually

When saving an entity, the LinqConnect engine checks whether your DataContext type has custom methods for inserting, updating or deleting the entities of this type. These methods should be defined as:

C#csharp	Copy Code
partial void Insert[EntityType]([EntityType] instance); partial void Update[EntityType]([EntityType] instance); partial void Delete[EntityType]([EntityType] instance);

Visual Basic	Copy Code
Partial Private Sub Insert[EntityType](instance As [EntityType]) Partial Private Sub Update[EntityType](instance As [EntityType]) Partial Private Sub Delete[EntityType](instance As [EntityType])

For example, when saving entities of the Company type the runtime will search for the following methods:

C#csharp	Copy Code
void InsertCompany(Company instance); void UpdateCompany(Company instance); void DeleteCompany(Company instance);

Visual Basic	Copy Code
Private Sub InsertCompany(instance As Company) Private Sub UpdateCompany(instance As Company) Private Sub DeleteCompany(instance As Company)

Note:

To make implementation of these methods simplier, the code generated by Entity Developer for LinqConnect models includes their definitions. To avoid the necessity of changing the generated code, these methods are declared as partial:

C#csharp	Copy Code
partial void InsertCompany(Company instance); partial void UpdateCompany(Company instance); partial void DeleteCompany(Company instance);

Visual Basic	Copy Code
Partial Private Sub InsertCompany(instance As Company) Partial Private Sub UpdateCompany(instance As Company) Partial Private Sub DeleteCompany(instance As Company)

To customize performing the insert, update or delete operation, we can implement the corresponding method somewhere in our code. In this case, this method will be executed instead of the default one.

For example, suppose that we set Update Checks for all Company fields to 'Always' to handle all possible concurrency violations, but don't want to check if the cached data are up-to-date when deleting unnecessary companies. Then, the only condition we need in the delete command is that CompanyId is equal to a certain value. For this case, we can implement the delete method as follows:

C#csharp	Copy Code
// The namespace you've set your DataContext class to be generated in. namespace CrmDemoContext { // Your custom DataContext class. partial class CrmDemoDataContext { // The implementation of the custom delete method. void DeleteCompany(Company instance) { // The sample is written for Oracle and may need to be modified for other DBMSs. this.ExecuteCommand( "delete from \"Company\" where \"CompanyID\" = {0}", instance.CompanyID ); } } }

C#csharp

Copy Code

// The namespace you've set your DataContext class to be generated in.
namespace CrmDemoContext {
 
    // Your custom DataContext class.
    partial class CrmDemoDataContext
    {
 
        // The implementation of the custom delete method.
        void DeleteCompany(Company instance)
        {
 
            // The sample is written for Oracle and may need to be modified for other DBMSs.
            this.ExecuteCommand(
                "delete from \"Company\" where \"CompanyID\" = {0}",
                instance.CompanyID
            );
        }
    }
}

Visual Basic	Copy Code
Namespace CrmDemoContext ' Your custom DataContext class. Partial Class CrmDemoDataContext ' The implementation of the custom delete method. Private Sub DeleteCompany(instance As Company) ' The sample is written for Oracle and may need to be modified for other DBMSs. Me.ExecuteCommand( _ "delete from ""Company"" where ""CompanyID"" = {0}", _ instance.CompanyID _ ) End Sub End Class End Namespace

Visual Basic

Copy Code

Namespace CrmDemoContext
 
    ' Your custom DataContext class.
    Partial Class CrmDemoDataContext
 
        ' The implementation of the custom delete method.
        Private Sub DeleteCompany(instance As Company)
 
            ' The sample is written for Oracle and may need to be modified for other DBMSs.
            Me.ExecuteCommand( _
                "delete from ""Company"" where ""CompanyID"" = {0}", _
                instance.CompanyID _
            )
        End Sub
    End Class
End Namespace

This is great if you want to change the standard behaviour completely. But if you only want to perform some checks at the client side before, e.g., updating a company, don't you need to rewrite the whole update statement, with all its checks and a number of 'if's for setting modified columns only? Fortunately, you don't, as DataContext provides protected internal (Protected Friend in VB) methods ExecuteDynamicInsert, ExecuteDynamicUpdate, and ExecuteDynamicDelete. These methods get an object instance and perform the default insert, update or delete operation for it.

For example, let there be a demand that the email address is specified for the company being updated. (And we don't want to enforce a server constraint for whatever reason - e.g., there is already a lot of companies without emails currently in the database). Then we can implement the UpdateCompany method as follows:

C#csharp	Copy Code
void UpdateCompany(Company instance) { // Perform necessary validations. if (instance.Email == null \|\| instance.Email == "") throw new InvalidOperationException( "The Company object being updated should have the email address specified."); // Execute the standard update operation. this.ExecuteDynamicUpdate(instance); }

C#csharp

Copy Code

void UpdateCompany(Company instance)
{
 
    // Perform necessary validations.
    if (instance.Email == null || instance.Email == "")
        throw new InvalidOperationException(
          "The Company object being updated should have the email address specified.");
    // Execute the standard update operation.
    this.ExecuteDynamicUpdate(instance);
}

Visual Basic	Copy Code
Private Sub UpdateCompany(instance As Company) ' Perform necessary validations. If instance.Email Is Nothing OrElse instance.Email = "" Then Throw New InvalidOperationException("The Company object being updated should have the email address specified.") End If ' Execute the standard update operation. Me.ExecuteDynamicUpdate(instance) End Sub

Visual Basic

Copy Code

Private Sub UpdateCompany(instance As Company)
 
    ' Perform necessary validations.
    If instance.Email Is Nothing OrElse instance.Email = "" Then
        Throw New InvalidOperationException("The Company object being updated should have the email address specified.")
    End If
    ' Execute the standard update operation.
    Me.ExecuteDynamicUpdate(instance)
End Sub

Because of the way LinqConnect persists changes, there are some limitations (or rather recommendations) on the CUD methods custom implementations:

A custom method should either perform the corresponding CUD operation or throw an exception. The reason is that the DataContext object performs certain operations related to object tracking when objects are inserted, updated or deleted. If the changes are not saved at the server side, these operations would make cached entity objects inconsistent with the database data.
For the same reason, it should not call SubmitChanges or Attach methods.
It should not start, commit or roll back a transaction. The point is that the SubmitChanges method starts (and commits, if there were no errors) its own transaction, and working with the transaction explicitly in the override methods may cause unexpected results.
It should throw a ChangeConflictException when an optimistic concurrency conflict occurs, so that such a conflict can be resolved within the LinqConnect error processing mechanism.
Whenever data should be synchronized with the server, it is supposed that the override methods return the server-generated values to their input entity. The samples of such situations are when inserting an entity with auto-generated primary keys, or updates of entities having a version field.

The entry point for these queries is the Devart.Data.Linq.Table class described in a previous topic.

Mapping Insert, Update, or Delete Methods to Stored Procedures

Instead of implementing custom CUD methods manually, you can map them to existing stored procedures.

Consider you have a stored procedure that updates a company in the database.

Oracle

CREATE OR REPLACE PROCEDURE CRM_DEMO.Company_Update(
  pOriginal_CompanyID IN NUMBER,
  pCompanyName IN  VARCHAR,
  pEmail IN  VARCHAR,
  pAddress IN  VARCHAR,
  pCity IN  VARCHAR,
  pCountry IN  VARCHAR,
  pPhone IN  VARCHAR
)
AS
BEGIN
  UPDATE CRM_DEMO."Company" SET
  "CompanyName" = pCompanyName,
  "Email" = pEmail,
  "Address" = pAddress,
  "City" = pCity,
  "Country" = pCountry,
  "Phone" = pPhone
  WHERE "CompanyID" = pOriginal_CompanyID;
END;

MySQL

CREATE PROCEDURE CRM_DEMO.Company_Update(
  pOriginal_CompanyID INT (11),
  pCompanyName VARCHAR (40),
  pEmail VARCHAR (50),
  pAddress VARCHAR (60),
  pCity VARCHAR (30),
  pCountry VARCHAR (20),
  pPhone VARCHAR (25)
)
BEGIN
  UPDATE CRM_DEMO.Company SET
  CompanyName = pCompanyName,
  Email = pEmail,
  Address = pAddress,
  City = pCity,
  Country = pCountry,
  Phone = pPhone
  WHERE CompanyID = pOriginal_CompanyID;
END;

PostgreSQL

CREATE OR REPLACE FUNCTION Company_Update(
  IN pOriginal_CompanyID numeric,
  IN pCompanyName character varying,
  IN pEmail  character varying,
  IN pAddress character varying,
  IN pCity character varying,
  IN pCountry character varying,
  IN pPhone character varying
) RETURNS void
AS
$BODY$
  UPDATE "Company" SET
  "CompanyName" =  $2,
  "Email" =  $3,
  "Address" =  $4,
  "City" =  $5,
  "Country" =  $6,
  "Phone" =  $7
  WHERE "CompanyID" =  $1;
 $BODY$
LANGUAGE 'sql' VOLATILE

SQL Server

CREATE PROCEDURE dbo.CompanyUpdate
  @pOriginal_CompanyID int,
  @pCompanyName varchar,
  @pEmail  varchar,
  @pAddress varchar,
  @pCity varchar,
  @pCountry varchar,
  @pPhone varchar
AS
BEGIN
  UPDATE dbo.Company SET
  CompanyName = @pCompanyName,
  Email = @pEmail,
  Address = @pAddress,
  City = @pCity,
  Country = @pCountry,
  Phone = @pPhone
  WHERE CompanyID = @pOriginal_CompanyID;
END
GO

Let's map it to a method.

C#csharp	Copy Code
[Function(Name=@"CRM_DEMO.COMPANY_UPDATE")] public System.Int32 CompanyUpdate( [Parameter(Name = "PORIGINAL_COMPANYID", DbType = "NUMBER")] System.Nullable<double> PoriginalCompanyid, [Parameter(Name = "PCOMPANYNAME", DbType = "VARCHAR2")] string Pcompanyname, [Parameter(Name = "PEMAIL", DbType = "VARCHAR2")] string Pemail, [Parameter(Name = "PADDRESS", DbType = "VARCHAR2")] string Paddress, [Parameter(Name = "PCITY", DbType = "VARCHAR2")] string Pcity, [Parameter(Name = "PCOUNTRY", DbType = "VARCHAR2")] string Pcountry, [Parameter(Name = "PPHONE", DbType = "VARCHAR2")] string Pphone ) { IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), PoriginalCompanyid, Pcompanyname, Pemail, Paddress, Pcity, Pcountry, Pphone); return ((System.Int32)(result.ReturnValue)); }

C#csharp

Copy Code

[Function(Name=@"CRM_DEMO.COMPANY_UPDATE")]
public System.Int32 CompanyUpdate(
    [Parameter(Name = "PORIGINAL_COMPANYID", DbType = "NUMBER")]  System.Nullable<double> PoriginalCompanyid,
    [Parameter(Name = "PCOMPANYNAME", DbType = "VARCHAR2")] string Pcompanyname,
    [Parameter(Name = "PEMAIL", DbType = "VARCHAR2")] string Pemail,
    [Parameter(Name = "PADDRESS", DbType = "VARCHAR2")] string Paddress,
    [Parameter(Name = "PCITY", DbType = "VARCHAR2")] string Pcity,
    [Parameter(Name = "PCOUNTRY", DbType = "VARCHAR2")] string Pcountry,
    [Parameter(Name = "PPHONE", DbType = "VARCHAR2")] string Pphone
)
{   
    IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())),
        PoriginalCompanyid, Pcompanyname, Pemail, Paddress, Pcity, Pcountry, Pphone);
    return ((System.Int32)(result.ReturnValue));
}

Visual Basic	Copy Code
<[Function](Name:="CRM_DEMO.COMPANY_UPDATE")> _ Public Function CompanyUpdate( _ <Parameter(Name:="PORIGINAL_COMPANYID", DbType:="NUMBER")> PoriginalCompanyid _ As System.Nullable(Of Double), _ <Parameter(Name:="PCOMPANYNAME", DbType:="VARCHAR2")> Pcompanyname As String, _ <Parameter(Name:="PEMAIL", DbType:="VARCHAR2")> Pemail As String, _ <Parameter(Name:="PADDRESS", DbType:="VARCHAR2")> Paddress As String, _ <Parameter(Name:="PCITY", DbType:="VARCHAR2")> Pcity As String, _ <Parameter(Name:="PCOUNTRY", DbType:="VARCHAR2")> Pcountry As String, _ <Parameter(Name:="PPHONE", DbType:="VARCHAR2")> Pphone As String _ ) As System.Int32 Dim result As IExecuteResult = Me.ExecuteMethodCall( _ Me, _ DirectCast(MethodInfo.GetCurrentMethod(), MethodInfo), _ PoriginalCompanyid, Pcompanyname, Pemail, Paddress, _ Pcity, Pcountry, Pphone _ ) Return CInt(result.ReturnValue) End Function

Visual Basic

Copy Code

        <[Function](Name:="CRM_DEMO.COMPANY_UPDATE")> _
        Public Function CompanyUpdate( _
            <Parameter(Name:="PORIGINAL_COMPANYID", DbType:="NUMBER")> PoriginalCompanyid _
                As System.Nullable(Of Double), _
            <Parameter(Name:="PCOMPANYNAME", DbType:="VARCHAR2")> Pcompanyname As String, _
            <Parameter(Name:="PEMAIL", DbType:="VARCHAR2")> Pemail As String, _
            <Parameter(Name:="PADDRESS", DbType:="VARCHAR2")> Paddress As String, _
            <Parameter(Name:="PCITY", DbType:="VARCHAR2")> Pcity As String, _
            <Parameter(Name:="PCOUNTRY", DbType:="VARCHAR2")> Pcountry As String, _
            <Parameter(Name:="PPHONE", DbType:="VARCHAR2")> Pphone As String _
        ) As System.Int32
            Dim result As IExecuteResult = Me.ExecuteMethodCall( _
                Me, _
                DirectCast(MethodInfo.GetCurrentMethod(), MethodInfo), _
                PoriginalCompanyid, Pcompanyname, Pemail, Paddress, _
                Pcity, Pcountry, Pphone _
            )
            Return CInt(result.ReturnValue)
        End Function

And here is how you invoke this method:

C#csharp	Copy Code
CrmDemoDataContext db = new CrmDemoDataContext(); string companyName = "Borland UK 1 - CodeGear Division"; string eMail = "[email protected]"; string address = "8 Pavilions Ruscombe Business Park"; string city = "Twyford"; string country = "United Kingdom"; string phone = "44(0)1189241400"; int rowsAffected = db.CompanyUpdate(1, companyName, eMail, address, city, country, phone);

C#csharp

Copy Code

CrmDemoDataContext db = new CrmDemoDataContext();
string companyName = "Borland UK 1 - CodeGear Division";
string eMail = "[email protected]";
string address = "8 Pavilions Ruscombe Business Park";
string city = "Twyford";
string country = "United Kingdom";
string phone = "44(0)1189241400";
int rowsAffected = db.CompanyUpdate(1, companyName, eMail, address, city, country, phone);

Visual Basic	Copy Code
Dim db As CrmDemoDataContext = New CrmDemoDataContext() Dim companyName As String = "Borland UK 1 - CodeGear Division" Dim eMail As String = "[email protected]" Dim address As String = "8 Pavilions Ruscombe Business Park" Dim city As String = "Twyford" Dim country As String = "United Kingdom" Dim phone As String = "44(0)1189241400" Dim rowsAffected As Integer = db.CompanyUpdate(1, companyName, eMail, address, city, country, phone)

Visual Basic

Copy Code

Dim db As CrmDemoDataContext = New CrmDemoDataContext()
Dim companyName As String = "Borland UK 1 - CodeGear Division"
Dim eMail As String = "[email protected]"
Dim address As String = "8 Pavilions Ruscombe Business Park"
Dim city As String = "Twyford"
Dim country As String = "United Kingdom"
Dim phone As String = "44(0)1189241400"
Dim rowsAffected As Integer = db.CompanyUpdate(1, companyName, eMail, address, city, country, phone)

This function on its own is not much meaningful, but you can tell LinqConnect to use the stored procedure to update the Company entity. To do this create a new data context method in the following way:

C#csharp	Copy Code
private void UpdateCompany(Company instance) { Company original = (Company)(Companies.GetOriginalEntityState(instance)); this.CompanyUpdate( (System.Nullable<double>)original.CompanyID, instance.CompanyName, instance.Email, instance.Address, instance.City, instance.Country, instance.Phone); }

C#csharp

Copy Code

private void UpdateCompany(Company instance)
{
    Company original = (Company)(Companies.GetOriginalEntityState(instance));
    this.CompanyUpdate(
        (System.Nullable<double>)original.CompanyID, instance.CompanyName, 
        instance.Email, instance.Address, instance.City, instance.Country, instance.Phone);
}

Visual Basic	Copy Code
Private Sub UpdateCompany(ByVal instance As Company) Dim original As Company = Me.Companies.GetOriginalEntityState(instance) Me.CompanyUpdate(New Integer?(original.Companyid), instance.Companyname, instance.Email, _ instance.Address, instance.City, instance.Country, instance.Phone) End Sub

Visual Basic

Copy Code

Private Sub UpdateCompany(ByVal instance As Company)
    Dim original As Company = Me.Companies.GetOriginalEntityState(instance)
    Me.CompanyUpdate(New Integer?(original.Companyid), instance.Companyname, instance.Email, _
       instance.Address, instance.City, instance.Country, instance.Phone)
End Sub

The name of this method consists of the keyword Update and the name of the class. For example, for Person class the method name would be UpdatePerson.

In the same way you can define stored procedures for adding and removing entities. On of the big advantages of using a stored procedure is that you can define some additional checks and business logic on the server, which improves perfromance and reliability of entire application.

With our visual model designer - Entity Developer you can make all of these actions in GUI, and Entity Developer will generate the necessary code automatically. See Entity Developer documentation for more information.

Implementing Custom Insert, Update, or Delete Methods Manually

Mapping Insert, Update, or Delete Methods to Stored Procedures

See Also