Manuel de configuration DATA Concept 2016 Version française 2.0 Présentation Cette application permet d’interroger très facilement les bases de données de l’entreprise afin d’obtenir des résultats qui ne sont pas facilement accessibles à partir des logiciels institutionnels. Par exemple il est possible d’extraire des données issues d’un ERP en quelques clics de souris. Les résultats sont affichés sous forme d’une grille. Ces données peuvent être exportées dans un fichier au format Excel ou copiées dans le presse papier. Bien entendu cette simplicité d’utilisation est obtenue grâce à la configuration des requêtes que vous allez construire. Outil de configuration des requêtes Pour accéder à la configuration La configuration des requêtes consiste 1. A créer une chaîne de connexion associée à un fournisseur de données (Provider) qui correspond à la base de données à interroger. 2. A créer une requête SQL avec les paramètres d’interrogation de la clause WHERE qui seront matérialisés pour l’utilisateur par des contrôles Windows. (Boîte texte, liste déroulante ou calendrier) : Toutes les fenêtres peuvent être fermées avec la touche d’ Échappement. La fenêtre est divisée horizontalement en deux parties. Elle est redimensionnable et la hauteur des grilles peut être ajustée en fonction de vos besoins. La grille du haut représente la liste des requêtes définies. La grille du bas représente la liste des chaînes de connexion associées à leur fournisseur de données. A droite de chaque grille 3 boutons Création d’un nouvel élément. Modification de l’élément sélectionné. Un double clic sur la ligne a le même effet. Suppression de l’élément sélectionné. Création de la chaîne de connexion Avant de pouvoir créer une requête il est nécessaire de lui affecter un fournisseur de données et une chaîne de connexion afin qu’elle puisse se connecter à la base de données. Cliquer sur dans la zone Fournisseurs de données Nom (Obligatoire) Fournisseur de données Chaîne de connexion Nom du fournisseur de données que sera choisi pour la requête associée. Nom du fournisseur de données (voir plus bas) Chaîne de connexion pour accéder à la base de données. Ce bouton permet de tester la connexion à la base de données en fonction des paramètres que vous avez saisis. Un message vous indique si la connexion a réussie ou échouée. A propos du fournisseur de données Cette application est développée à l’aide du Framework .Net de Microsoft. Nous avons utilisé un accès aux données neutre (system.data.common) qui permet de se connecter à n’importe quelle base de données dont un fournisseur de données existe pour le Framework de Microsoft. Les fournisseurs suivants peuvent être utilisés. Fournisseur Syntaxe ODBC System.Data.Odbc OLE DB System.Data.OleDb SQL SERVER System.Data.SqlClient SQL SERVER CE System.Data.SqlServerCe.4.0 ORACLE System.Data.OracleClient SQLITE System.Data.SqLite Base de données compatibles Toute base de données accessible par un fournisseur ODBC Base de données exposant un fournisseur OleBD (Access) Base de données SQL Serveur Base de données SQL Serveur compact Edition Base de données Oracle (Non testé) Base de données SQL Lite (Non testé) Ces fournisseurs sont installés par défaut sur les systèmes Windows version 7 et supérieur. Le logiciel est fourni avec des exemples de fournisseurs de données et de chaînes de connexion. Reportez-vous à votre responsable de base de données pour connaître le format de la chaîne de connexion. Création de la requête Cliquer sur dans la zone requêtes Titre SQL Le nom de la requête que verra l’utilisateur dans la liste des requêtes Requête SQL Donner un titre à la requête (zone obligatoire) Choisir la connexion dans la liste déroulante La liste correspond au nom que vous avez donné dans la fenêtre des fournisseurs de données. Nous vous conseillons de construire la requête SQL dans votre outil d’administration de base de données et de la copier ensuite dans la zone prévue. Mais vous pouvez également la construire dans cette fenêtre si vous le souhaitez et la vérifier en tant qu’utilisateur. Pour vous aider à trouver les tables et les champs associés à la chaine de connexion, vous pouvez cliquer sur le bouton qui affiche une fenêtre listant les tables et les champs de votre base de données. . Vous pouvez par glisser-déposer, faire glisser une table ou un champ dans la zone de la requête. Le texte déplacé s’insérera où vous avez positionné votre curseur avant de quitter la zone Les champs obtenus depuis la fenêtre Structure contenant des espaces sont entourés par des crochets. (Vérifiez que votre base de données supporte cette syntaxe). Raccourcis claviers La zone de requête dispose de quelques raccourcis claviers. Ctrl + C Copier Ctrl + V Coller Ctrl + A Sélectionner tout Ctrl + U Mise en majuscule de tout le texte Relations entre configuration et interface utilisateur Relations entre configuration et requête exécutée Exemple de construction d’une requête Pour créer cet exemple nous avons utilisé la base de données Access Northwind que vous pouvez télécharger facilement sur Internet. Fournisseur de données System.Data.OleDb Chaîne de connexion Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Bases\Nwind.mdb Requête Nous allons effectuer une requête simple sur les produits afin de connaître le stock. Titre de la requête : Stock produits La requête va se présenter comme ci-dessous SELECT ProductName , UnitsInStock FROM Products Vous pouvez vous aider de la fenêtre Structure pour faire glisser le nom de la table et des champs. Cliquez sur et testez la requête en mode utilisateur (Menu Affichage / requêtes) (Reportez-vous au manuel utilisateur pour le fonctionnement de l’interface) Bien, la liste des produits s’affiche avec le stock. Maintenant nous souhaitons que la requête soit paramétrée pour pouvoir lister les produits d’une seule catégorie Pour cela nous allons utiliser les variables. Les variables correspondent aux paramètres de la requête. Revenez sur la fenêtre de création de la requête. Nous allons ajouter une clause WHERE à notre Requête SELECT ProductName , UnitsInStock FROM Products WHERE CategoryID = Nous allons créer une variable qui permettra à l’utilisateur de saisir le numéro de la catégorie qu’il souhaite visualiser. Boîte texte Remplissez les zones comme ci-dessous : Nom Etiquette Type de contrôle Type de contenu VAR1 N° de la catégorie Boite texte TexteLibre Cliquez ensuite sur La variable s’est ajoutée à la liste des variables. Cliquez sur Allez dans la zone de la requête, placez votre curseur à la fin de la requête et faites un clic droit Un menu contextuel apparait, Chaque élément du menu correspond à une variable. Cliquez sur le nom de la variable que vous venez de créer Le variable s’est écrite dans la requête. Les variables sont entourées des signes «et » Les signes «et » font partie intégrante de la variable, ils ne font pas partie de la requête SQL. Si la variable doit être remplacée par une valeur alphanumérique n’oubliez pas de l’entourer de cottes. Exemple ‘«VAR3»’ et non pas Testez la requête Vous observez qu’une boîte texte s’affiche dans laquelle l’utilisateur peut saisir le numéro de la catégorie. Si vous ouvrez l’onglet message de la requête, vous voyez que le nom de la variable a été remplacé par la valeur saisie. SELECT ProductName, UnitsInStock FROM Products WHERE CategoryID = 2 Liste déroulante (Valeur) Ce serait plus sympathique si l’utilisateur pouvait choisir le numéro dans une liste. Comme il n’y a que 8 catégories, nous allons les taper pour créer la liste. Nous allons modifier la variable préalablement saisie. Pour modifier une variable existante, double cliquez sur la ligne de la variable afin qu’elle se retrouve dans la zone d’édition. C’est également de cette façon que l’on supprime une variable. La suppression d’une variable de la liste ne supprime pas la variable dans la requête, celle-ci doit être supprimée manuellement. N’oubliez pas de cliquer sur Modifiez la variable comme ci-dessous. Cliquez sur , puis sur Testez la requête Vous observez que la boîte texte a été remplacée par une liste déroulante contenant les numéros de catégories. Voilà, l’interface utilisateur est plus conviviale, l’utilisateur ne risque plus de saisir un numéro de catégorie qui n’existe pas ou saisir autre chose qu’un chiffre. Dans le cas où la requête renvoie une erreur (L’utilisateur a tapé ‘A’ à la place d’un numéro de catégorie.) La fenêtre de résultat s’affiche sur l’onglet message en présentant la requête et le message d’erreur. Lorsqu’une erreur se produit le texte est écrit en rouge SELECT ProductName , UnitsInStock FROM Products WHERE CategoryID = A Aucune valeur donnée pour un ou plusieurs des paramètres requis Liste déroulante (Listebase) Nous avons amélioré l’interface, mais cela oblige l’utilisateur à connaître la correspondance entre le numéro de la catégorie et son nom. Essayons d’améliorer. Ouvrez à nouveaux la fenêtre de création de la requête. Nous allons présenter à l’utilisateur la liste des noms de catégories et remplacer la variable par le numéro de la catégorie. Pour cela, il va falloir faire une requête sur la table catégories . select CategoryName, CategoryID from Categories Justement la liste déroulante du type ListeBase est conçue pour cela. La requête exécutée est de la forme suivante SELECT [Champ affiché], [Champ valeur] FROM [TABLE] <ORDER BY [CHAMP TRI]> [Champ affiché], [Champ valeur] [TABLE] [CHAMP TRI] Les valeurs de ce champ sont affichées à l’utilisateur dans la liste déroulante. C’est une de ces valeurs qu’il doit choisir. Les valeurs de ce champ ne sont pas visibles à l’utilisateur. Elles sont utilisées pour donner une valeur à la variable Nom de la table sur laquelle la requête doit être appliquée. Optionnel, Champ permettant de trier la liste. Ces informations sont saisies dans la zone Liste sous la forme suivante : [TABLE],[CHAMP affiché],[Champ valeur], [Champ tri] Exemple : Categories, CategoryName, CategoryID, CategoryName Les éléments doivent être séparés par des virgules La requête sous-jacente sera select CategoryName, CategoryID from Categories order by CategoryName Si vous devez présenter une liste contenant des doublons, exemple liste des clients à partir des factures, vous pouvez faire précéder le champ affiché du mot clé DISTINCT. Exemple : FACTURES,DISTINCT CLI_NOM,CLI_NUM Si vous souhaitez trier les éléments en sens inverse ajoutez DESC au champ tri Exemple : CategoryName DESC La variable doit être construite comme ci-dessous : Cliquez sur , puis sur Testez la requête La liste contient bien les noms des catégories. Dans la liste chaque ligne Nom de la catégorie est reliée au numéro de la catégorie correspondant. Si vous regardez l’onglet message, vous voyez que c’est bien le numéro de la catégorie qui est affiché SELECT ProductName , UnitsInStock FROM Products WHERE CategoryID = 2 Liste déroulante (ListeMembre) Cette liste est un mélange des deux listes précédentes. Les éléments affichés de la liste correspondent à des valeurs qui seront appliquées à la requête. Ces éléments ne proviennent pas de la base de données, mais ils sont saisis manuellement dans la zone liste. Les éléments affichés sont séparés des valeurs par le caractère | (Alt Gr + 6) Exemple : BAIGNOIRE, LAVABO, VASQUE |45,23,12 Calendrier La variable de type calendrier (TexteDate) permet à l’utilisateur de sélectionner une date dans un contrôle calendrier. L’aspect de ce calendrier peut différer suivant la version de Windows installée sur votre machine. Si vous souhaitez faire une recherche sur une plage de date, il suffit d’avoir deux variables de type TexteDate Variables Descriptif des zones Nom Etiquette Type de contrôle Type de contenu Format Liste Nom de la variable Texte qui sera au-dessus du contrôle (Sa largeur influe sur la largeur du contrôle) Boite texte, Liste déroulante, Calendrier En fonction du type de contrôle Format à appliquer au texte saisi par l’utilisateur Eléments de la liste dans le cas d’un contrôle de type liste déroulante. Types de contenus Boîte texte Liste déroulante Saisie date Texte libre Liste membre Liste base Liste valeur Textedate Zone Format Cette zone permet d’appliquer un format à la valeur qui remplace la variable. Le format correspond au format défini par Microsoft. https://msdn.microsoft.com/fr-fr/library/8kb3ddd4(v=vs.110).aspx Prenons l’exemple suivant : La gestion commerciale Sage ligne 30 ou ligne 100 base propriétaire peut être interrogée avec un pilote ODBC fourni par Sage. Les dates doivent avoir le format suivant : pour 25/04/2016 -> {d 2016-04-25} soit {d yyyy-MM-dd}. La zone format permet de résoudre cette contrainte. Le format suivant sera employé \'\{\d yyyy-MM-dd\}\' \ est un caractère d’échappement, il signifie que le caractère suivant sera pris comme un caractère littéral. Dans cet exemple les cottes (‘) ont été intégré au format, elles auraient pues être appliquées à la variable. Dans ce cas le format serait : \{\d yyyy-MM-dd\} et la variable ‘«VAR3»’. Commentaires Nous espérons que ce logiciel vous apportera toute satisfaction. Néanmoins nous sommes conscients que tout produit peut être amélioré. C’est avec plaisir que nous attendons vos suggestions et vos remarques. Vous pouvez pour cela nous écrire à [email protected] Objet : Logiciel requêtes. Nous vous invitons également à visiter notre site Internet pour voir nos autres produits. www.data-concept.fr L’équipe de développement DATA Concept