Les bonnes techniques de programmation pour l'ERP Dynamics NAV

J'ai remarqué plusieurs fois que la plupart des gens utilisent les instructions d'extraction des données à partir de la base d'une façon peu performante. Ou alors saisissent des instructions non nécessaires qui rendent le code confus.

Ceci m'a poussé à écrire cet article afin d'expliquer à l'aide de quelques exemples comment utiliser (ou pas) ces différentes instructions et à quel moment le faire (ou pas).

La plupart des éléments traités ici supposent que le serveur de base de données utilisé est SQL Server. Mais certains sont aussi valables pour le serveur de base de données natif C/SIDE.

Les instructions CLEAR, INIT et RESET.

Commençons par présenter les différences entre ces trois instructions.

I. TheTable.RESET : utilisée pour supprimer tous les filtres (inclus les filtres indiqués dans les instructions FILTERGROUP) d'une variable de type record. Cette instruction réinitialise aussi la commande SETCURRENTKEY afin qu'elle utilise la clé primaire comme clé active pour les recherches.

Ne jamais utiliser cette instruction pour initialiser les champs d'une variable de type record. Ceci n'est pas son rôle.

a) TRÈS MAUVAIS : si une variable de type record a des valeurs dans ses champs, l'instruction RESET ne les initialisera pas.

TheTable.RESET;
TheTable."Primary Key" := Some Value;
TheTable."Field N" := Some Value;
TheTable.INSERT(FALSE);

b) CORRECT :

CLEAR(TheTable); // initialise aussi les valeurs des champs constituant la clé primaire
TheTable."Primary Key" := Some Value;
TheTable."Field N" := Some Value;
TheTable.INSERT(FALSE);

II. TheTable.INIT : cette instruction initialise (avec la valeur par défaut) tous les champs d'une variable de type record sauf les champs constituant la clé primaire. Elle n'affecte pas les filtres (FILTERS) et ne modifie pas la clé active (CURRENTKEY). Donc si vous souhaitez ajouter un nouvel enregistrement sans modifier les valeurs des champs constituant la clé primaire, cette instruction convient parfaitement.

III. CLEAR(TheTable) : cette instruction combine l'action d'un RESET et d'un INIT et initialise les valeurs des champs constituant la clé primaire. J'utilise toujours cette instruction pour créer un nouvel enregistrement. Il s'agit de l'unique commande qui réinitialise la commande CHANGECOMPANY.

IV. GET : cette instruction est la meilleure si vous voulez extraire un enregistrement à l'aide de sa clé primaire. Vous pouvez aussi filtrer sur la clé primaire et utiliser l'instruction FINDFIRST. La commande SELECT envoyée au serveur SQL est la même mais l'instruction GET nécessite moins de code et est plus facile à lire. Aucune commande RESET, SETCURRENTKEY, SETRANGE n'est nécessaire, ces dernières instructions n'influencent pas le fonctionnement de cette commande.

a) MAUVAIS (à cause de la confusion créée) :

TheTable.RESET;
TheTable.SETCURRENTKEY(...)
TheTable.SETRANGE/SETFILTER
TheTable.GET(...);

b) CORRECT :

TheTable.GET(...);

Si vous souhaitez effectuer une commande GET sans générer une erreur même si l'enregistrement recherché n'existe pas, vous pouvez procéder de cette manière :

Version 1

CLEAR(TheTable);
IF TheTable.GET(....) THEN ;

Version 2

IF NOT TheTable.GET(....) THEN
CLEAR(TheTable);

Ces deux versions renverront un enregistrement avec des valeurs par défaut si elles ne trouvent pas l'enregistrement recherché. Aucune valeur de champ non enregistrée ne sera conservée sur la variable après l'exécution du code. Personnellement, je préfère la première instruction : j'initialise d'abord la variable, ensuite j'extrais l'enregistrement (s'il existe).

Les prochaines commandes nécessitent d'appliquer des filtres sur la variable de type record. Avant de les détailler, je voudrais commencer par vous donner une bonne technique de programmation qui s'applique à celles-ci : pour réaliser un code facilement maintenable, il est important de placer les instructions appliquant des filtres ensemble et après la commande RESET. Ceci vous permet de ne pas vous soucier si certains filtres ont été appliqués avant et s'ils doivent être supprimés ou pas. J'utilise toujours ces instructions ensemble, toute personne lisant mon code saura ainsi que je n'ai pas oublié d'appeler la commande SETCURRENTKEY.

TheTable.RESET;
TheTable.SETCURRENTKEY(...); //Même si vous allez utiliser la clé primaire comme clé active
TheTable.SETRANGE(...)ou TheTable.SETFILTER(....)

Quelques remarques :

(a) FIND,FINDSET: ces commandes ouvrent un curseur SQL Server. Utiliser un curseur est très coûteux en termes de performances, il vaut mieux toujours les éviter si cela est possible.

(b) Les exemples suivants présentent les commandes à utiliser si vous souhaitez extraire les données sans verrouiller les enregistrements. Ensuite je présenterai des exemples avec utilisation des verrous.

(c) FINDSET extrait les N premiers enregistrements d'une table en une passe. N est défini dans le champ « Jeu d'enregistrements » des propriétés de la base de données en suivant le menu : Fichier -> Base de données -> Modifier et en cliquant sur l'onglet « Avancé ».

V. FIND(‘-‘) : cette instruction ne doit plus être utilisée (sauf dans certaines situations que j'expliquerai plus tard), car il existe d'autres instructions plus optimisées comme FINDFIRST et FINDSET.

VI. FIND(‘+') : cette instruction ne doit plus être utilisée (sauf dans certaines situations que j'expliquerai plus tard), car il existe d'autres instructions plus optimisées comme FINDLAST.

VII. FIND ('=') : cette instruction me semble un peu bizarre. Elle agit la plupart du temps comme la commande GET afin d'extraire un enregistrement à l'aide de sa clé primaire. On l'appelle après le remplissage des valeurs des champs constituant la clé primaire. Tous les filtres appliqués sur la variable de type record sont pris en compte. Cette commande est utile si la variable Record contient déjà l'enregistrement que l'on recherche et on voudrait juste mettre à jour ses différents champs.

VIII. FIND ('>') / FIND('<') : cette commande s'appuie sur les valeurs des champs constituant la clé primaire pour extraire l'enregistrement suivant ou précédent. Tous les filtres appliqués sur la variable de type record sont pris en compte. Il vaut mieux utiliser l'instruction NEXT ou NEXT(-1).

IX. FIND ('=<>') : cette commande est la moins performante de toutes. Elle commence par exécuter l'instruction FIND(‘=') pour extraire l'enregistrement recherché, si ce dernier n'est pas trouvé, la commande FIND(‘<') est appelée, si rien n'est toujours trouvé, la commande FIND(‘>') est enfin appelée. À éviter absolument.

X. FINDFIRST : ceci est la commande à utiliser si on ne souhaite extraire que le premier enregistrement d'un jeu de données. Par contre, ne l'utilisez jamais si vous souhaitez parcourir les lignes du jeu de résultats obtenu.

XI. FINDLAST : ceci est la commande à utiliser si on ne souhaite extraire que le dernier enregistrement d'un jeu de données. Par contre, ne l'utilisez jamais si vous souhaitez parcourir les lignes du jeu de résultats obtenu.

XII. FINDSET : ceci est la commande à utiliser si vous souhaitez extraire un ensemble de lignes d'une table donnée. Elle récupère les N premières lignes en une seule passe. Si le nombre de lignes dans la table est plus petit que N, la commande va extraire toutes les lignes de la table. N est défini dans le champ « Jeu d'enregistrements » des propriétés de la base de données en suivant le menu : Fichier -> Base de données -> Modifier et en cliquant sur l'onglet « Avancé ».

XIII. ISEMPTY : ceci est la commande à utiliser si vous souhaitez vérifier si votre jeu de données comporte au moins une ligne ou pas.

XIV. COUNT : cette commande est utilisée si vous souhaitez avoir le nombre précis de lignes de votre jeu de données. Elle est plus lourde en termes de performances que la commande COUNTAPPROX.

XV. COUNTAPPROX : cette commande est à utiliser si vous souhaitez avoir un nombre approximatif de lignes de votre jeu de données. Elle peut servir pour mettre à jour le contrôle ProgressBar. Elle est moins gourmande en ressources que la commande COUNT.

3-A. Vous devez vérifier l'existence d'au moins un enregistrement dans votre jeu de données, mais vous ne souhaitez pas utiliser les données de ce dernier s'il existe▲

IF TheTable.COUNT > 0 THEN ... //ou COUNTAPPROX

IF TheTable.FINDSET THEN ...

IF TheTable.FIND('-') THEN ... // or FIND('+')

IF TheTable.FINDFIRST THEN ... // or FINDLAST

IF NOT TheTable.ISEMPTY THEN ...

3-B. Vous voulez extraire le premier/dernier enregistrement (s'il existe), mais pas plus▲

If TheTable.FINDSET THEN ... 
// Pour avoir le dernier enregistrement, vous auriez besoin d'appeler 
//l'instruction ASCENDING(FALSE) avant d'exécuter la commande FINDSET 
//(À voir plus bas dans ce document car c'est encore plus mauvais que « très mauvais »)

If TheTable.FIND('-') THEN ... // ou FIND('+')

If TheTable.FINDFIRST THEN ... // ou FINDLAST

3-C. Vous souhaitez extraire toutes les lignes d'un jeu de données dans l'ordre ascendant▲

IF TheTable.FINDFIRST THEN
 REPEAT
...
 UNTIL TheTable.NEXT = 0;

IF TheTable.FIND('-') THEN
 REPEAT
 ...
 UNTIL TheTable.NEXT = 0;

IF TheTable.FINDSET THEN
 REPEAT
 ...
 UNTIL TheTable.NEXT = 0;

3-D. Vous souhaitez extraire toutes les lignes d'un jeu de données dans l'ordre descendant▲

TheTable.ASCENDING(FALSE);
IF TheTable.FINDSET THEN
 REPEAT
 ...
 UNTIL TheTable.NEXT = 0;

IF TheTable.FINDSET THEN
 REPEAT
   //Ajout de lignes dans la table temporaire
   tmpTheTable := TheTable;
   tmpTheTable.INSERT(FALSE);
 UNTIL TheTable.NEXT = 0;

//Parcours de la table temporaire
tmpTheTable.RESET;
tmpTheTable.ASCENDING(FALSE);
IF tmpTheTable.FIND('-') THEN
 REPEAT
  ...
 UNTIL tmpTheTable.NEXT = 0;

TheTable.ASCENDING(FALSE);
IF TheTable.FIND('-') THEN
 REPEAT
 ...
 UNTIL TheTable.NEXT= 0;

Pour commencer, quelques conseils sur la façon de mettre à jour les données dans la base.

1) Créez des transactions le plus tard possible dans le code (commencez par lire toutes les données nécessaires sans appliquer de verrou et ensuite effectuez les opérations de « mise à jour »).

2) Appliquez des verrous sur le moins d'enregistrements possible (si vous voulez changer une ligne dans une table qui en contient 10 millions, pourquoi verrouiller toute la table alors que vous pouvez verrouiller seulement un seul enregistrement ?).

3) Le code contenu dans vos transactions doit être le plus rapide possible (il est par exemple très mauvais de mettre une instruction SLEEP dans une transaction).

4) Le code contenu dans vos transactions doit être le plus court possible (OK, mais pas trop petit : si vous souhaitez mettre à jour 1000 lignes dans une table, n'appelez pas la commande COMMIT à chaque mise à jour).

5) Effectuez vos transactions de sorte que chacune d'elles contienne, le cas échéant et en un seul bloc, toutes les instructions de mise à jour prévues, sinon aucune.

LOCKTABLE(ou FINDSET(TRUE...)) : cette commande fonctionne différemment avec le serveur de base de données natif C/SIDE. Dans ce cas, toute la table est verrouillée. Avec le serveur SQL, à partir de ce point du code, tous les enregistrements extraits de la table seront verrouillés. Mais les enregistrements des autres tables ne le seront pas. Il est cependant possible que certains autres enregistrements soient verrouillés à cause du mécanisme de verrouillage de SQL Server sur lequel nous n'avons pas vraiment de contrôle (au moins à partir du langage C/AL).

4-A. Vous ne devez modifier qu'un seul enregistrement▲

//Vous n'avez pas de LOCKTABLE ici (un LOCKTABLE sur une autre table ne 
//verrouillera pas la table courante)
TheTable.GET(...); // ou FINDFIRST,FINDLAST
TheTable."Some Field" := 'Some Value';
TheTable.MODIFY(FALSE);

TheTable.LOCKTABLE;
TheTable.GET(...); // ou FINDFIRST,FINDLAST
TheTable."Some Field" := 'Some Value';
TheTable.MODIFY(FALSE);

4-B. Vous devez récupérer un enregistrement et éventuellement le mettre à jour▲

TheTable.GET(...); // ou FINDFIRST,FINDLAST
IF (to be changed) THEN BEGIN
 TheTable."Some Field" := 'Some Value';
 TheTable.MODIFY(FALSE);
END;

TheTable.LOCKTABLE;
TheTable.GET(...); // ou also FINDFIRST,FINDLAST
IF (to be changed) THEN BEGIN
 TheTable."Some Field" := 'Some Value';
 TheTable.MODIFY(FALSE);
END;

4-C. Vous devez extraire un ensemble d'enregistrements et tous les modifier▲

IF TheTable.FINDFIRST THEN // ou aussi FIND('-')
 REPEAT
   TheTable."Some Field" := 'Some Value';
   TheTable.MODIFY(FALSE);
 UNTIL TheTable.NEXT= 0;

IF TheTable.FINDSET THEN
 REPEAT
   TheTable2 := TheTable;
   TheTable2."Some Field" := 'Some Value';
   TheTable2.MODIFY(FALSE);
 UNTIL TheTable.NEXT = 0;

IF TheTable.FINDSET(TRUE,TRUE) THEN
 REPEAT
   TheTable2 := TheTable;
   TheTable2."Some Field" := 'Some Value';
   TheTable2.MODIFY(FALSE);
 UNTIL TheTable.NEXT= 0;

4-D. Vous devez extraire un ensemble d'enregistrements et en modifier certains▲

IF TheTable.FINDSET(TRUE,TRUE) THEN
 REPEAT
   TheTable2 := TheTable;
   TheTable2."Some Field" := 'Some Value';
   TheTable2.MODIFY(FALSE);
 UNTIL TheTable.NEXT= 0;

// Il n'y a pas de verrou sur la table "TheTable" !
IF TheTable.FINDSET THEN
 REPEAT
  // copie des données dans la table temporaire
  IF (to be changed) THEN BEGIN
    tmpTheTable := TheTable;
    tmpTheTable.INSERT(FALSE);
  END;
 UNTIL TheTable.NEXT= 0;

//Appel de l'instruction LOCKTABLE. Ceci garantit que tous les enregistrements lus seront verrouillés 
CLEAR(TheTable);
TheTable.LOCKTABLE;

//Parcours de la table temporaire, le code utilise la version des enregistrements 
//de la table temporaire pour mettre à jour les enregistrements correspondants 
//de la table. Si un enregistrement est modifié entre-temps, une erreur est générée. 
//Remarque : l'instruction MODIFY génère une commande SELECT avec un verrou exclusif 
//avant l'instruction UPDATE. Ceci est nécessaire car Navision doit vérifier si la 
//ligne n'a pas changé de version (voir le champ RowVersion)

tmpTheTable.RESET;
IF tmpTheTable.FINDSET THEN
 REPEAT
   TheTable := tmpTheTable;
   TheTable."Some Field" := 'Some Value';
   TheTable.MODIFY(FALSE);
 UNTIL tmpTheTable.NEXT = 0;

//Parcours de la table temporaire. Ce code extrait toujours des enregistrements en appliquant 
//un verrou exclusif et ensuite effectue des mises à jour. Si l'enregistrement est modifié 
//entre sa lecture et notre mise à jour, une erreur ne sera pas générée dans le MODIFY parce 
//que la version de l'enregistrement extrait était la plus récente.
tmpTheTable.RESET;
IF tmpTheTable.FINDSET THEN
 REPEAT
   TheTable := tmpTheTable;
   TheTable.FIND('='); 

//Ou TheTable.GET(primary key). Les deux commandes sont possibles mais vous devez 
//vérifier les filtres présents dans la table TheTable. La commande CLEAR(TheTable) 
//appelée avant supprime tous ces filtres éventuels 

   TheTable."Some Field" := 'Some Value';
   TheTable.MODIFY(FALSE);
 UNTIL tmpTheTable.NEXT = 0;

5-A. Utilisation de l'instruction FILTERGROUPS▲

TheTable.RESET;
TheTable.SETCURRENTKEY(....);
TheTable.FILTERGROUP(10); // application d'un filtergroup inaccessible pour l'utilisateur
TheTable.SETRANGE(....);
TheTable.FILTERGROUP(0); //application du filtergroup par défaut. L'utilisateur peut 
//modifier ou supprimer ce filtre
FORM.RUNMODAL(0,TheTable); // L'utilisateur ne pourra pas mettre à jour les filtres

TheTable.RESET;
TheTable.SETCURRENTKEY(...);
TheTable.SETFILTER("The Field",'A*C*B');
IF TheTable.FINDSET THEN

TheTable.RESET;
TheTable.SETCURRENTKEY(...);
TheTable.FILTERGROUP(10);
TheTable.SETFILTER("The Field",'A*');
TheTable.FILTERGROUP(11);
TheTable.SETFILTER("The Field",'*C*');
TheTable.FILTERGROUP(12);
TheTable.SETFILTER("The Field",'*B');
TheTable.FILTERGROUP(0);
IF TheTable.FINDSET THEN

5-B. Lecture/Insertion/Mise à jour/Suppression d'enregistrements dans une autre société▲

CLEAR(TheTable);
TheTable.CHANGECOMPANY('Some Other Company');
TheTable.VALIDATE("Field 1",'Some Value');
TheTable.VALIDATE("Field 2",'Some Value');
TheTable.VALIDATE("Field 3",'Some Value');
TheTable.INSERT(TRUE);

CLEAR(TheTable);
TheTable.CHANGECOMPANY('Some Other Company');
TheTable."Field 1" := 'Some Value';
TheTable."Field 2" := 'Some Value';
TheTable."Field 3" := 'Some Value';
TheTable.INSERT(FALSE);

CLEAR(TheTable);
TheTable.CHANGECOMPANY('Some Other Company');
TheTable.GET(....);

CLEAR(TheTable);
TheTable.CHANGECOMPANY('Some Other Company');
TheTable.SETRANGE("Field 1",'Some Value');
TheTable.FINDSET;
REPEAT
...
UNTIL TheTable.NEXT= 0;

//Ou

TheTable.DELETEALL(FALSE); // Sans triggers!!!!!

//Ou

TheTable.MODIFYALL(FALSE); // Sans triggers!!!!!

5-C. Vous souhaitez savoir la quantité totale en stock d'un article donné dans un magasin donné▲

CLEAR(recItem);
recItem."No." := '1000'; // ou recItem.GET('1000');
recItem.SETRANGE("Location Filter",'BLU');
recItem.CALCFIELDS(Inventory);
MESSAGE('%1',recItem.Inventory);

recItemLedgerEntry.RESET;
recItemLedgerEntry.SETCURRENTKEY("Item no.","Location Code");
recItemLedgerEntry.SETRANGE("Item No."','1000');
recItemLedgerEntry.SETRANGE("Location Code",'BLU');
recItemLedgerEntry.CALCSUMS(Inventory);
MESSAGE('%1',recItemLedgerEntry.Quantity);

recRecordVar1.RESET; => recRecordVar[1].RESET;
CLEAR(recRecordVar1); => CLEAR(recRecordVar[1]);
recRecordVar1.SETRANGE(...) => recRecordVar[1].SETRANGE(...)

intSomeInteger := 1;
recRecordVar1.RESET; => recRecordVar[ intSomeInteger ].RESET;

CLEAR(recRecord[1]);
recRecord[1]."No." := '1');
recRecord[1].INSERT(FALSE);
recRecord[2].RESET;
recRecord[2].FINDFIRST;
MESSAGE('%1',recRecord[2]."No."); // la valeur de l'élément  1 sera affichée

5-D. Vous souhaitez calculer la somme du champ « montant » en groupant par « Gen. Bus. Posting Group » (groupe de comptabilisation marché) et « Gen. Prod. Posting Group » (groupe de comptabilisation produit) de la table 15 : « G/L Entry » (écriture comptable).▲

SELECT  "Gen_ Bus_ Posting Group","Gen_ Prod_ Posting Group",SUM(Amount) 
FROM dbo."CRONUS International Ltd_$G_L Entry"
GROUP  BY"Gen_ Bus_ Posting Group","Gen_ Prod_ Posting Group"
ORDER  BY"Gen_ Bus_ Posting Group","Gen_ Prod_ Posting Group"

recGLEntry.SETCURRENKEY(...); // Utiliser la clé appropriée pour l'exécution de la requête
recGLEntry.SETRANGE(.....); // appliquer les filtres
IF recGLEntry.FINDSET THEN
 REPEAT
 
   //"tmpGLEntry" table temporaire utilisée pour enregistrer les totaux
   tmpGLEntry.RESET;


   // Utiliser la clé appropriée pour les filtres
   IF NOT tmpGLEntry.SETCURRENTKEY("Gen. Bus. Posting Group") THEN;
   IF NOT tmpGLEntry.SETCURRENTKEY("Gen. Prod. Posting Group") THEN;
 
   //filtre sur les lignes utilisées pour le regroupement
   tmpGLEntry.SETRANGE("Gen. Bus. Posting Group",recGLEntry."Gen. Bus.Posting Group");
   tmpGLEntry.SETRANGE("Gen. Prod. Posting Group",recGLEntry."Gen. Prod.Posting Group");


   IF NOT tmpGLEntry.FINDFIRST THEN BEGIN
      // L'enregistrement n'existe pas il faut le créer
      // N'oubliez pas que vous devez utiliser une clé unique pour ajouter un enregistrement.
      // Chaque "recGLEntry" est unique donc on peut insérer
      // "recGLEntry" dans la table temporaire.
      tmpGLEntry := recGLEntry;
      tmpGLEntry.insert(FALSE);
   END ELSE BEGIN
      //l'enregistrement trouvé correspond à la combinaison voulue donc 
      //on ajoute son montant dans le champ de totalisation
      tmpGLEntry.Amount+= recGLEntry.Amount;
      tmpGLEntry.MODIFY(FALSE);
   END;
   
 UNTIL recGLEntry.NEXT= 0;



 //Dans la table temporaire, j'ai les lignes contenant les totaux suivant les combinaisons 
 //que je voulais. Bien sûr si on veut ordonner la table, il faut disposer de la clé 
 //appropriée et avec Sql Server on peut mettre la propriété MaintainSQLIndex de cette clé à FALSE. 
 //Une autre façon de faire est de créer une nouvelle table avec tous les champs 
 //et clés nécessaires et l'utiliser comme une table temporaire. 
 //Vous n'avez pas besoin de licence pour une nouvelle table si vous ne 
 //l'utilisez que comme table temporaire 



 //Maintenant vous pouvez utiliser vos totaux dans la table temporaire
tmpGLEntry.RESET;
FORM.RUNMODAL(0,tmpGLEntry);

Je tiens ici à remercier Alain Krikilion pour son aimable autorisation de traduire cet article.
Merci à djug et gorgonite pour leurs conseils.
Merci à ClaudeLELOUP et jacques_jean pour leur relecture attentive.