Code - Financial Independence

Linq To SQL

Probablement ce qui m’a permis de ne pas me fatiguer de créer des SQL statements ou des stored procedures. Linq propose une technologie d’interfaçage avec les bases de données Microsoft SQL Server permettant de rester en C# tout en contrôlant les interactions avec ces dernières. Oui, il est quand même possible d’utiliser des procédures stockées avec Linq To SQL.

Le but est d’être indépendant du moteur de base de données et de limiter son utilisation de SQL pour les interactions avec celle-ci. De plus, on souhaite rendre plus rapide et plus flexible la programmation d’applications. Voici comment créer une base de données et y lier une application C# en utilisant Linq To SQL.

1. Mise en place de l’environnement

La première étape pour utiliser Linq To SQL est la création d’une vraie base de données. Assumant que vous avez déjà une installation de Microsoft SQL Server (vidéo tutoriel pour l’installation de MSSQL et Management Studio), nous commencerons par créer une base de données de type employés-appareils (très commun je l’admets) pour notre exemple.

  • Employees – contient les employés.
  • Devices – contient les appareils appartenus par les employés, par exemple un BlackBerry, un moniteur, etc.

Voici le schéma de base de données final. Vous pouvez copier le code suivant et l’exécuter sur votre serveur de base de données pour partir sur la même que moi.

CREATE DATABASE [Inventory]
GO
USE [Inventory]
GO
CREATE TABLE [dbo].[Devices](
	[DeviceID] [int] IDENTITY(1,1) NOT NULL,
	[EmployeeID] [int] NOT NULL,
	[IdentificationNumber] [varchar](50) NOT NULL,
	[Brand] [varchar](50) NOT NULL,
	[Model] [varchar](50) NOT NULL,
 CONSTRAINT [PK_Devices] PRIMARY KEY CLUSTERED 
(
	[DeviceID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Employees](
	[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
	[EmployeeNumber] [varchar](10) NOT NULL,
	[FirstName] [varchar](50) NOT NULL,
	[LastName] [varchar](50) NOT NULL,
 CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED 
(
	[EmployeeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Devices]  WITH CHECK ADD  CONSTRAINT [FK_Devices_Employees] FOREIGN KEY([EmployeeID])
REFERENCES [dbo].[Employees] ([EmployeeID])
GO
ALTER TABLE [dbo].[Devices] CHECK CONSTRAINT [FK_Devices_Employees]
GO

Inventory Database Diagram

2. Création d’un nouveau projet dans Visual Studio

La prochaine étape est de créer un nouveau projet dans Visual Studio. Pour ce faire je vais simplement m’en tenir à un projet .Net Framework de type console. Mais vous pouvez modifier vous-même votre projet existant pour y inclure Linq To SQL aussi.

LinqToSQL new Project

3. Ajout du Linq To SQL Data Context

Cette étape est aussi facile que les autres, cliquez droit sur votre projet ou encore sur un fichier dans celui-ci et ajoutez un nouveau Data Context comme montré ci-dessous. Le Data Context servira à ajouter nos tables ou procédures stockées.

Add Data Context

Vous avez peut-être remarqué que j’ai mis mon Data Context dans une arborescence de deux répertoires. La raison est simplement pour conserver de l’ordre dans les classes. Le Data Context créera des objets du type de vos tables donc je le mets dans le répertoire DataObjects. Le dossier DataAccess quant à lui, contiendra les classes d’accès aux tables de la base de données. J’ajoute le préfixe DataLink à ces classes pour bien les identifier. En effet, vous n’êtes pas obligés de suivre mon standard, c’est à votre discrétion.

4. Liaison de la base de données au Data Context

Pour créer les objets correspondants à vos tables, vous devez tout d’abord créer la connexion à votre base de données à l’aide du Server Explorer. Puis, vous pourrez glisser vos tables directement dans le rectangle de gauche de votre Data Context. Le rectangle de droit quant à lui, sert putôt à ajouter les procédures stockées.

Cliquez sur l’onglet Server Explorer. S’il n’apparaît pas, vous pourrez le trouver en cliquant sur l’élément de menu View en haut de votre fenêtre. Cliquez ensuite droit sur Data Connections puis cliquez sur Add Connection…

Add Connection

Vous devriez voir l’écran suivant. Sélectionnez Microsoft SQL Server puis cliquez sur Continue.

Microsoft SQL Server

Entrez les informations de votre serveur de base de données puis testez la connexion.

SQL Server Connection test

Vous pouvez soit utiliser le compte sa ou encore l’authentification Windows si le serveur est sur votre propre ordinateur (ou si vous avez autorisé votre ordinateur à se connecter à votre serveur). Toutefois, l’authentification Windows est considérée comme la meilleure pratique étant donné qu’aucun mot de passe n’est contenu dans votre fichier de configuration.

Vous remarquerez que je me connecte à mon premier serveur appelé EIN, mais vous devrez très probablement entrer localhost si vous avez effectué l’installation sur l’ordinateur que vous utilisez. Il est d’ailleurs aussi possible d’entrer l’adresse IP directement.

Un fois que vous aurez choisi la base de données dans le menu déroulant, cliquez sur le bouton Test Connection pour s’assurer que tout fonctionne, puis sur Ok.

5. Création des objets Linq To SQL

Nous avons presque fini la configuration, courage! À cette étape, vous devez retourner dans l’onglet Server Explorer et sélectionner les tables que vous voulez ajouter. Puis, glissez les dans le rectangle de gauche et votre Data Context créera les objets pour vous.

Vous pourriez voir cette fenêtre apparaître si vous utilisez l’authentification SQL, cliquez simplement sur Yes pour continuer.

Clear Text Credentials

Si vous êtes curieux et voulez essayer d’utiliser un autre ordinateur/machine virtuelle comme Serveur SQL (sans domaine), vous pouvez facilement utiliser l’authentification Windows en ajoutant un nouveau compte à celui-ci ayant le même nom d’utilisateur et mot de passe que celui que vous utiliserez pour vous connecter. Ensuite, donnez l’autorisation à ce compte sur votre serveur SQL pour qu’il puisse se connecter à votre base de données avec les permissions data_reader et data_writer.

Glissez ensuite les tables du Server Explorer vers le rectangle de gauche et cela devrait vous donner quelque chose semblable à l’image ci-dessous.

DataContext Linq To SQL

Comment accéder aux données?

Même l’accès aux données est très facile, disons que vous n’avez qu’à suivre ces deux étapes :

  1. Création d’un objet Data Context dans votre code;
  2. Exécution d’une requête Linq sur le Data Context.

Je vous suggère de diviser les objets de type DataLink selon les tables ou encore ce qui est logique pour votre projet. Dans mon cas, ils contiennent tous un minimum de 5 méthodes soit : Retrieve, Add, Update, Delete et Exists. Avec ces méthodes, vous aurez l’accès de base à votre table. J’admets qu’il s’agît d’un genre de wrapper mais qui aide toutefois à organiser ses idées et à abstraire le niveau d’accès aux données.

Puis, comme mentionné plus haut, les Data Objects seront tous compris dans votre .dbml (DataContext).

Linq To SQL Folder Structure

Voici un exemple de code pour un objet DataLink. Vous pourrez ensuite créer une nouvelle instance de cet objet pour l’appeler directement dans votre code.

DevicesDataLink.cs

using System;
using System.Collections.Generic;
using System.Linq;

using LinqToSQL.DataAccess.DataObjects;

namespace LinqToSQL.DataAccess
{
    /// 
    /// Not thread safe DataLink
    /// 
    public class DevicesDataLink
    {
        InventoryDataContext _InventoryDataContext;

        public DevicesDataLink()
        {
            _InventoryDataContext = new InventoryDataContext();
        }

        /// 
        /// Récupère un appareil à l'aide de son IdentificationNumber.
        /// 
        /// 
        /// Le numéro de série.
        /// 
        /// Le device ayant l'IndentificationNumber fourni.
        public Device Retrieve(string IdentificationNumber)
        {
            if (!Exists(IdentificationNumber))
                throw new ArgumentException("Le device entré n'existe pas.", "DeviceToAdd");

            Device databaseObject = _InventoryDataContext.Devices.Single(device => device.IdentificationNumber == IdentificationNumber);
            return databaseObject;
        }

        /// 
        /// Récupère tous les appareils.
        /// 
        /// 
        /// Un IEnumerable contenant tout les appareils de la base de données.
        public IEnumerable RetrieveAll()
        {
            IEnumerable devices = _InventoryDataContext.Devices;

            return devices;
        }

        /// 
        /// Ajoute un nouveau device.
        /// 
        /// 
        /// Le device à ajouter.
        public void Add(Device DeviceToAdd)
        {
            if (Exists(DeviceToAdd.IdentificationNumber))
                throw new ArgumentException("L'appareil existe déjà dans la base de données.", "DeviceToAdd");

            _InventoryDataContext.Devices.InsertOnSubmit(DeviceToAdd);
            _InventoryDataContext.SubmitChanges();
        }

        /// 
        /// Met à jour un appareil dans la base de données.
        /// 
        /// 
        /// L'appareil à mettre à jour.
        public void Update(Device DeviceToUpdate)
        {
            if (!Exists(DeviceToUpdate.DeviceID))
                throw new ArgumentException("L'appareil fourni n'existe pas.", "DeviceToUpdate");

            Device databaseObject = _InventoryDataContext.Devices.Single(device => device.DeviceID == DeviceToUpdate.DeviceID);

            databaseObject.EmployeeID = DeviceToUpdate.EmployeeID;
            databaseObject.IdentificationNumber = DeviceToUpdate.IdentificationNumber;
            databaseObject.Brand = DeviceToUpdate.Brand;
            databaseObject.Model = DeviceToUpdate.Model;

            _InventoryDataContext.SubmitChanges();
        }

        /// 
        /// Vérifie si un appareil existe dans la base de données à l'aide de son identifiant.
        /// 
        /// 
        /// L'identifiant de l'appareil.
        /// 
        /// True si l'appareil existe, False en autres cas.
        public bool Exists(int DeviceID)
        {
            if (_InventoryDataContext.Devices.Any(device => device.DeviceID == DeviceID))
                return true;
            return false;
        }

        /// 
        /// Vérifie si un appareil existe dans la base de données à l'aide de son numéro d'identification (S/N).
        /// 
        /// 
        /// Le numéro d'identification.
        /// 
        /// True si l'appareil existe, False en autres cas.
        public bool Exists(string IdentificationNumber)
        {
            if (_InventoryDataContext.Devices.Any(device => device.IdentificationNumber == IdentificationNumber))
                return true;
            return false;
        }
    }
}

Ce code a pour but de vous donner une idée générale de comment il est facile d’utiliser Linq pour faire des requêtes CRUD (Create, Read, Update, Delete) sur une base de données à l’aide de Linq To SQL. Avec une quantité de code minimal, il est donc possible de facilement tester et accéder les données.

Vous remarquerez que la méthode Update est un peu plus complexe, car elle comprend un référence implicite à l’élément dans la base de données. C’est-à-dire que Single vous retourne un objet lié à la base de données. Quand vous modifiez ce dernier, les changements affecteront directement les données dès que vous aurez fait SubmitChanges().

La méthode Exists est aussi utile parce qu’il est facile de vérifier si un élément est compris déjà dans la base de données. Autre petite précision, j’ai deux méthodes avec le même nom ici, il s’agit d’overloading, vous pourrez trouver plus d’information dans ce post.

Programs.cs

using System.Linq;

using LinqToSQL.DataAccess;
using LinqToSQL.DataAccess.DataObjects;

namespace LinqToSQL
{
    class Program
    {
        static void Main(string[] args)
        {
            //Utilisation de Linq To SQL sans le DataLink
            Employee NewEmployee = new Employee();
            NewEmployee.EmployeeNumber = "9830324";
            NewEmployee.FirstName = "Alex";
            NewEmployee.LastName = "Test";

            //Création d'un nouveau DataContext
            InventoryDataContext DataContext = new InventoryDataContext();

            //Insertion d'un nouvel employé
            DataContext.Employees.InsertOnSubmit(NewEmployee);

            //Confirmation des modifications à effectuer à la base de données
            DataContext.SubmitChanges();

            //------------------------------------------------
            //Cécupère l'employé ajouté
            Employee InsertedEmployee = DataContext.Employees.Single(employee => employee.EmployeeNumber == "9830324");

            //Utilisation de Linq To SQL avec le DataLink            
            DevicesDataLink DevicesDataLink = new DevicesDataLink();

            //Création d'un nouvel appareil
            Device NewDevice = new Device();
            NewDevice.EmployeeID = InsertedEmployee.EmployeeID;
            NewDevice.IdentificationNumber = "FSDWE43ASDBBYT76";
            NewDevice.Brand = "Blackberry";
            NewDevice.Model = "Priv";

            //Ajout d'un nouvel appareil à la base de données
            DevicesDataLink.Add(NewDevice);

            Device AddedDevice = DevicesDataLink.Retrieve("FSDWE43ASDBBYT76");
        }
    }
}

Cet exemple vous montre la différence entre l’utilisation directe du DataContext et celle d’un wrapper DataLink. L’avantage du wrapper est qu’il donne la possibilité d’abstraire l’ajout à la base de données en créant un autre niveau. De plus, il assure qu’il n’y ait qu’un seul endroit capable de travailler avec les données soit le namespace DataAccess.

Les requêtes Linq!

Les requêtes Linq constituent un sujet plus avancé qui sera discuté dans un autre blog post. En attendant, si vous voulez plus d’information et commencer à coder dès maintenant, vous pouvez aller voir ce lien de Microsoft.

Linq To SQL peut vraiment vous aider à rapidement mettre en place une connexion et des requêtes sur une base de données. C’est un outil très puissant qui peut faire bien plus que ce qui est montré dans ce post. Néanmoins, vous devriez maintenant avoir une meilleure idée de son utilisation et de sa mise en place. Bref, j’espère bien que ce post vous aura donné un petit coup de main et que vous aurez autant de plaisir à utiliser cette technologie que moi!

Youtube

Next article Compost, recyclage, etc!
Previous article Spéculation et cryptomonnaies

Related posts

0 Comments

No Comments Yet!

You can be first to comment this post!

Leave a Comment

Your data will be safe! Your e-mail address will not be published. Also other data will not be shared with third person. Required fields marked as *