Partager via

Procédure pas à pas : accès à une base de données SQL à l'aide des fournisseurs de type (F#)

  • Article

Cette procédure pas - à - pas montre comment utiliser le fournisseur de type de SqlDataConnection (LINQ to SQL) qui est disponible dans F# 3,0 pour générer des types pour des données dans une base de données SQL lorsque vous avez une connexion active à une base de données. Si vous n'avez pas une connexion active à une base de données, mais vous avez un fichier de schema LINQ to SQL (fichier DBML), consultez Procédure pas à pas : génération de types F# à partir d'un fichier DBML (F#).

Cette procédure pas à pas décrit les tâches suivantes : Ces tâches doivent être exécutées dans cette ordre pour que la procédure pas - à - pas réussisse :

  • Préparation d'une base de données de test

  • Création du projet

  • Installation d'un fournisseur de type

  • Interrogation des données

  • Utilisation des champs nullable

  • Appeler une procédure stockée

  • Mise à jour de la base de données

  • Exécuter le code Transact-SQL

  • En utilisant le contexte complet de données

  • Supprimer des données

  • Création d'une base de données de test

Préparation d'une base de données de test

Sur un serveur qui exécute SQL Server, créez une base de données à des fins de test. Vous pouvez utiliser le script de création d´une base de données en bas de cette page dans la section Création d'une base de données de test.

Pour préparer une base de données de test

  • Pour exécuter Création d'une base de données de test, ouvrir le menu Afficher , puis choisir Explorateur d'objets SQL Server ou choisir le raccourci Ctrl+\, Ctrl+S . Dans la fenêtre Explorateur d'objets SQL Server , ouvrez le menu contextuel pour l'instance appropriée, choisissez Nouvelle requête, copiez le script en bas de cette page, puis collez le script dans l'éditeur. Pour exécuter le script SQL, choisissez l'icône de barre d'outils avec le symbole triangulaire, ou choisir Ctrl+Q. Pour plus d'informations sur Explorateur d'objets SQL Server, consultez Développement de base de données connectés.

Création du projet

Ensuite, vous créez un projet d'application F#.

Pour créer et paramétrer le projet

  1. Créez un nouveau projet d'application F#.

  2. Ajoutez des références à .FSharp.Data.TypeProviders, ainsi qu´à System.Data, et System.Data.Linq.

  3. Ouvrez les espaces de noms appropriés en ajoutant les lignes de code suivantes au début de votre fichier Program.fs de code F#.

    open System
open System.Data
open System.Data.Linq
open Microsoft.FSharp.Data.TypeProviders
open Microsoft.FSharp.Linq

  4. Comme avec la plupart des programmes F#, vous pouvez exécuter le code dans cette procédure pas - à - pas comme programme compilé, ou vous pouvez l'exécuter en mode interactif comme script. Si vous préférez utiliser des scripts, ouvrez le menu contextuel du nœud de projet, sélectionnez Ajouter un nouvel élément, ajoutez un fichier de script F#, puis ajoutez le code dans chaque étape du script. Vous devez ajouter les lignes suivantes au début du fichier pour charger les références d'assembly.

    #r "System.Data.dll"
#r "FSharp.Data.TypeProviders.dll"
#r "System.Data.Linq.dll"

    Vous pouvez ensuite sélectionner chaque bloc de code au fur et à mesure que vous l'ajoutez et appuyez sur Alt+Enter pour l'exécuter dans F# interactive.

Installation d'un fournisseur de type

Dans cette étape, vous créez un fournisseur de type pour votre schéma de base de données.

Pour installer le fournisseur de type d'une connexion de base de données directe

  • Il existe deux lignes de code critiques dont vous avez besoin afin de créer des types que vous pouvez utiliser pour interroger une base de données SQL à l'aide du fournisseur de type. D'abord, vous instanciez le fournisseur de type. Pour ce faire, créez ce qui ressemble à une abréviation de type pour SqlDataConnection avec un paramètre générique statique. SqlDataConnection est un fournisseur de type SQL, et ne doit pas être confondu avec le type SqlConnection utilisé dans la programmation ADO.NET. Si vous avez une base de données à laquelle vous souhaitez vous connecter, et avez une chaîne de connexion, utilisez le code suivant pour appeler le fournisseur de type. Substituez votre propre chaîne de connexion pour la chaîne d'exemple donnée. Par exemple, si votre serveur est MYSERVER et l'instance de base de données est INSTANCE, le nom de la base de données est MyDatabase, et vous souhaitez utiliser l'authentification Windows pour accéder à la base de données, la chaîne de connexion est spécifiée comme dans l'exemple de code suivant.

    type dbSchema = SqlDataConnection<"Data Source=MYSERVER\INSTANCE;Initial Catalog=MyDatabase;Integrated Security=SSPI;">
let db = dbSchema.GetDataContext()

// Enable the logging of database activity to the console.
db.DataContext.Log <- System.Console.Out

    Vous avez maintenant un type, dbSchema, qui est un type parent qui contient tous les types générés qui représentent des tables de base de données. Vous avez également un objet, db, qui a comme membres toutes les tables dans la base de données. Les noms de tables sont des propriétés, et le type de ces propriétés est généré par le compilateur F#. Les types eux-mêmes s'affichent en tant que types imbriqués sous dbSchema.ServiceTypes. Par conséquent, toutes les données récupérées des lignes de ces tables sont une instance du type approprié qui a été généré pour cette table. Ce type s'appelle ServiceTypes.Table1.

    Pour vous familiariser avec la manière dont le langage F# interprète les requêtes dans des requêtes SQL, examinez la ligne qui définit la propriété Log dans le contexte de données.

    Pour explorer davantage les types créés par le fournisseur de type, ajoutez le code suivant.

    let table1 = db.Table1

    Pointez sur table1 pour afficher son type. Son type est System.Data.Linq.Table<dbSchema.ServiceTypes.Table1>et l'argument générique implique que le type de chaque ligne est le type généré, dbSchema.ServiceTypes.Table1. Le compilateur crée un type semblable pour chaque table dans la base de données.

Interrogation des données

Dans cette étape, vous écrivez une requête à l'aide des expressions de requête F#.

Pour interroger les données

  1. Créez maintenant une requête pour la table dans la base de données. Ajoutez le code ci-dessous.

    let query1 =
        query {
            for row in db.Table1 do
            select row
        }
query1 |> Seq.iter (fun row -> printfn "%s %d" row.Name row.TestData1)

    L'apparence du mot query indique qu'il s'agit d'une expression de requête, un type d'expression de calcul qui génère une collection de résultats semblables à une requête de base de données typique. Si vous pointez sur la requête, vous verrez que c'est une instance de Linq.QueryBuilder, classe (F#), un type qui définit l'expression de calcul de requête. Si vous pointez sur query1, vous verrez que c'est une instance de IQueryable. Comme son nom l'indique, IQueryable représente des données qui peuvent être interrogées, pas le résultat d'une requête. Une requête est sujette à l'évaluation tardive, ce qui signifie que la base de données est interrogée uniquement lorsque la requête est évaluée. La dernière ligne passe la requête dans Seq.iter. Les requêtes sont énumérables et peuvent être itérées comme des séquences. Pour plus d'informations, consultez Expressions de requête (F#).

  2. Ajoutez maintenant un opérateur de requête à la requête. Il existe un nombre d'opérateurs de requête disponibles que vous pouvez utiliser pour construire des requêtes plus complexes. Cet exemple montre également que vous pouvez éliminer la variable de requête et utiliser un opérateur de pipeline à la place.

    query {
        for row in db.Table1 do
        where (row.TestData1 > 2)
        select row
      }
|> Seq.iter (fun row -> printfn "%d %s" row.TestData1 row.Name)

  3. Ajoutez une requête plus complexe avec une jointure de deux tables.

    query {
        for row1 in db.Table1 do
        join row2 in db.Table2 on (row1.Id = row2.Id)
        select (row1, row2)
}
|> Seq.iteri (fun index (row1, row2) ->
     if (index = 0) then printfn "Table1.Id TestData1 TestData2 Name Table2.Id TestData1 TestData2 Name"
     printfn "%d %d %f %s %d %d %f %s" row1.Id row1.TestData1 row1.TestData2 row1.Name
         row2.Id (row2.TestData1.GetValueOrDefault()) (row2.TestData2.GetValueOrDefault()) row2.Name)

  4. Dans le code réel, les paramètres de votre requête sont généralement des valeurs ou des variables, constantes. Ajoutez le code suivant qui encapsule une requête dans une fonction qui prend un paramètre, puis appelle la fonction avec la valeur 10.

    let findData param =
    query {
        for row in db.Table1 do
        where (row.TestData1 = param)
        select row
        }
findData 10 |> Seq.iter (fun row -> printfn "Found row: %d %d %f %s" row.Id row.TestData1 row.TestData2 row.Name)

Utilisation des champs nullable

Dans les bases de données, les champs permettent souvent des valeurs NULL. Dans le système de type. NET, vous ne pouvez pas utiliser les types de données numériques ordinaires pour les données qui autorisent la valeur null car ces types n'ont pas null comme valeur possible. Par conséquent, ces valeurs sont représentées par les instances de type Nullable . Au lieu d'accéder à la valeur de ces champs directement avec le nom du champ, vous devez ajouter des étapes supplémentaires. Vous pouvez utiliser la propriété Value pour accéder à la valeur sous-jacente d'un type Nullable. La propriété Value lève une exception si l'objet est null au lieu d'avoir une valeur. Vous pouvez utiliser la méthode booléenne HasValue pour déterminer si une valeur existe, ou utiliser GetValueOrDefault afin de garantir que vous avez une valeur réelle dans tous les cas. Si vous utilisez GetValueOrDefault et qu'il y a la valeur null dans la base de données, elle est remplacée par une valeur telle qu'une chaîne vide pour les types de chaînes, 0 pour les types intégraux ou 0,0 pour les types à virgule flottante.

Lorsque vous devez exécuter des tests ou des comparaisons d'égalité des valeurs nullables dans une clause where dans une requête, vous pouvez utiliser les opérateurs nullables disponibles dans Linq.NullableOperators, module (F#). Ceux-ci sont comme les opérateurs de comparaison normaux =, >, <=, etc., mais un point d'interrogation apparaît sur la gauche ou à droite de l'opérateur où les valeurs nullables sont. Par exemple, l'opérateur >? est un opérateur supérieur à avec une valeur nullable à droite. Le fonctionnement de ces opérateurs est que si l'un ou l'autre côté de l'expression est null, l'expression correspond à false. Dans une clause where , cela signifie généralement que les lignes qui contiennent des champs Null ne sont pas activées et ne sont pas retournés dans les résultats de la requête.

Pour utiliser les champs nullable

  • Voici de code qui travaille avec les valeurs nullables ; supposons que TestData1 est une zone entière qui autorise les nulls.

    query {
        for row in db.Table2 do
        where (row.TestData1.HasValue && row.TestData1.Value > 2)
        select row
      }
|> Seq.iter (fun row -> printfn "%d %s" row.TestData1.Value row.Name)

query {
        for row in db.Table2 do
        // Use a nullable operator ?>
        where (row.TestData1 ?> 2)
        select row
      }
|> Seq.iter (fun row -> printfn "%d %s" (row.TestData1.GetValueOrDefault()) row.Name)

Appeler une procédure stockée

Les procédures stockées dans la base de données peuvent être appelées via le F#. Vous devez définir le paramètre statique StoredProcedures à true dans l'instanciation du fournisseur de type. Le fournisseur de type SqlDataConnection contient plusieurs méthodes statiques que vous pouvez utiliser pour configurer les types qui sont générés. Pour une description complète de ces éléments, consultez SqlDataConnection, fournisseur de type (F#). Une méthode sur le type de contexte de données est générée pour chaque procédure stockée.

Pour appeler une procédure stockée

  • Si les procédures stockées acceptent les paramètres qui sont nullables, vous devez passer la valeur appropriée Nullable . La valeur de retour d'une méthode de procédure stockée qui retourne une variable scalaire ou un tableau est ISingleResult, qui contient des propriétés qui vous permettent d'accéder aux données retournées. L'argument de type pour ISingleResult dépend de la procédure spécifique et est également l'un des types que le type fournisseur génère. Pour une procédure stockée nommée Procedure1, le type est Procedure1Result. Le type Procedure1Result contient les noms des colonnes dans une table retournée, ou, pour une procédure stockée qui retourne une valeur scalaire, il représente la valeur de retour.

    Le code suivant suppose qu'il existe une procédure Procedure1 sur la base de données qui prend deux entiers nullables comme paramètres, exécute une requête qui retourne une colonne nommée TestData1, et retourne un entier.

    
type schema = SqlDataConnection<"Data Source=MYSERVER\INSTANCE;Initial Catalog=MyDatabase;Integrated Security=SSPI;",
                                StoredProcedures = true>

let testdb = schema.GetDataContext()

let nullable value = new System.Nullable<_>(value)

let callProcedure1 a b =
    let results = testdb.Procedure1(nullable a, nullable b)
    for result in results do
        printfn "%d" (result.TestData1.GetValueOrDefault())
    results.ReturnValue :?> int

printfn "Return Value: %d" (callProcedure1 10 20)

Mise à jour de la base de données

Le type de LINQ DataContext contient des méthodes qui facilitent les mises à jour de base de données traitées de façon complètement typée avec les types générés.

Pour mettre à jour la base de données

  1. Dans le code suivant, plusieurs lignes sont ajoutées à la base de données. Si vous ajoutez une seule ligne, vous pouvez utiliser InsertOnSubmit pour spécifier la nouvelle ligne à ajouter. Si vous insérez plusieurs lignes, vous devez les placer dans une collection et appeler InsertAllOnSubmit``1. Lorsque vous appelez l'une de ces méthodes, la base de données n'est pas modifiée immédiatement. Vous devez appeler SubmitChanges pour valider réellement les modifications. Par défaut, tout ce que vous faites avant d'appeler SubmitChanges est implicitement une partie de la même transaction.

    let newRecord = new dbSchema.ServiceTypes.Table1(Id = 100,
                                                 TestData1 = 35, 
                                                 TestData2 = 2.0,
                                                 Name = "Testing123")
let newValues =
    [ for i in [1 .. 10] ->
          new dbSchema.ServiceTypes.Table3(Id = 700 + i,
                                           Name = "Testing" + i.ToString(),
                                           Data = i) ]
// Insert the new data into the database.
db.Table1.InsertOnSubmit(newRecord)
db.Table3.InsertAllOnSubmit(newValues)
try
    db.DataContext.SubmitChanges()
    printfn "Successfully inserted new rows."
with
   | exn -> printfn "Exception:\n%s" exn.Message

  2. Nettoyez maintenant les lignes en appelant une opération de suppression.

    // Now delete what was added.
db.Table1.DeleteOnSubmit(newRecord)
db.Table3.DeleteAllOnSubmit(newValues)
try
    db.DataContext.SubmitChanges()
    printfn "Successfully deleted all pending rows."
with
   | exn -> printfn "Exception:\n%s" exn.Message

Exécuter le code Transact-SQL

Vous pouvez également spécifier du Transact-SQL directement à l'aide de la méthode ExecuteCommand dans la classe DataContext .

Pour exécuter des commandes SQL Personnalisées

  • Le code suivant montre comment envoyer des commandes SQL Pour Insérer un enregistrement dans un tableau et également de supprimer un enregistrement d'une table.

    try
   db.DataContext.ExecuteCommand("INSERT INTO Table3 (Id, Name, Data) VALUES (102, 'Testing', 55)") |> ignore
with
   | exn -> printfn "Exception:\n%s" exn.Message
try //AND Name = 'Testing' AND Data = 55
   db.DataContext.ExecuteCommand("DELETE FROM Table3 WHERE Id = 102 ") |> ignore
with
   | exn -> printfn "Exception:\n%s" exn.Message

En utilisant le contexte complet de données

Dans les exemples précédents, la méthode GetDataContext a été utilisée pour obtenir ce qui est appelé le contexte simplifié de données du schéma de base de données. Le contexte simplifié de données est plus facile à utiliser lorsque vous construisez des requêtes car il n'existe pas autant de membres disponibles. Par conséquent, lorsque vous recherchez les propriétés dans Intellisense, vous pouvez vous concentrer sur la structure de la base de données, tels que des tables et des procédures stockées. Toutefois, il existe une limite à ce que vous pouvez faire avec le contexte simplifié de données. Un contexte complet de données qui offre la possibilité d'exécuter d'autres actions. est également disponible il se trouve dans ServiceTypes et porte le nom du paramètre statique DataContext si vous l´avez fourni. Si vous ne l'avez pas fourni, le nom du type de contexte de données est généré pour vous par SqlMetal.exe sur l'autre entrée. Le contexte complet de données hérite de DataContext et expose des membres de sa classe de base, y compris les références aux types de données ADO.NET tels que l'objet Connection , les méthodes telles que ExecuteCommand et ExecuteQuery``1 que vous pouvez utiliser pour écrire des requêtes SQL, et également un moyen d'utiliser des transactions explicitement.

Pour utiliser le contexte de données complet

  • Le code suivant montre comment obtenir un objet de contexte de données complet et l'utiliser pour exécuter des commandes directement sur la base de données. Dans ce cas, deux commandes sont exécutées dans le cadre de la même transaction.

    let dbConnection = testdb.Connection
let fullContext = new dbSchema.ServiceTypes.MyDatabase(dbConnection)
dbConnection.Open()
let transaction = dbConnection.BeginTransaction()
fullContext.Transaction <- transaction
try
    let result1 = fullContext.ExecuteCommand("INSERT INTO Table3 (Id, Name, Data) VALUES (102, 'A', 55)")
    printfn "ExecuteCommand Result: %d" result1
    let result2 = fullContext.ExecuteCommand("INSERT INTO Table3 (Id, Name, Data) VALUES (103, 'B', -2)")
    printfn "ExecuteCommand Result: %d" result2
    if (result1 <> 1 || result2 <> 1) then
        transaction.Rollback()
        printfn "Rolled back creation of two new rows."
    else
        transaction.Commit()
        printfn "Successfully committed two new rows."
with
    | exn -> transaction.Rollback()
             printfn "Rolled back creation of two new rows due to exception:\n%s" exn.Message

dbConnection.Close()

Supprimer des données

Cette étape indique comment supprimer des lignes d'une table de données.

Pour supprimer des lignes de la base de données

  • Maintenant, nettoyer toutes les lignes ajoutées en écrivant une fonction qui supprime les lignes d'une table spécifiée, une instance de la classe Table . Entrez une requête pour rechercher toutes les lignes que vous souhaitez supprimer, puis ajoutez les résultats de la requête dans la fonction deleteRows . Ce code tire parti de la capacité de fournir l'application partielle d'arguments de fonction.

    let deleteRowsFrom (table:Table<_>) rows =
    table.DeleteAllOnSubmit(rows)

query {
    for rows in db.Table3 do
    where (rows.Id > 10)
    select rows
    }
|> deleteRowsFrom db.Table3

db.DataContext.SubmitChanges()
printfn "Successfully deleted rows with Id greater than 10 in Table3."

Création d'une base de données de test

Cette section explique comment installer la base de données de test à utiliser dans cette procédure pas - à - pas.

Notez que si vous modifiez la base de données d'une certaine façon, vous devez réinitialiser le fournisseur de type. Pour réinitialiser le fournisseur de type, regénérer ou nettoyer le projet qui contient le fournisseur de type.

Pour créer la base de données de test

  1. Dans Explorateur de serveurs, ouvrez le menu contextuel du nœud Connexions de données , puis choisissez Ajouter une connexion. La boîte de dialogue Ajouter une connexion s'affiche.

  2. Dans la zone Nom du serveur , spécifiez le nom d'une instance de SQL Server à laquelle vous avez un accès administratif, ou si vous n'avez pas accès à un serveur, spécifiez (localdb \ v11.0). SQL Express LocalDB fournit un serveur de base de données léger pour le développement et sur votre ordinateur. Un nœud est créé dans Explorateur de serveurs sous Connexions de données. Pour plus d’informations sur les ressources, voir Procédure pas à pas : création d'un fichier de base de données local dans Visual Studio.

  3. Ouvrez le menu contextuel pour le nouveau nœud de connexion, puis sélectionnez Nouvelle requête.

  5. Copiez le script SQL suivant, collez-le dans l'éditeur de requête, puis choisissez le bouton Exécuter dans la barre d'outils ou cliquez sur les touches ctrl+maj+E.

    SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

USE [master];
GO

IF EXISTS (SELECT * FROM sys.databases WHERE name = 'MyDatabase')
                DROP DATABASE MyDatabase;
GO

-- Create the MyDatabase database.
CREATE DATABASE MyDatabase;
GO

-- Specify a simple recovery model 
-- to keep the log growth to a minimum.
ALTER DATABASE MyDatabase 
                SET RECOVERY SIMPLE;
GO

USE MyDatabase;
GO

-- Create the Table1 table.
CREATE TABLE [dbo].[Table1] (
    [Id]        INT        NOT NULL,
    [TestData1] INT        NOT NULL,
    [TestData2] FLOAT (53) NOT NULL,
    [Name]      NTEXT      NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);

--Create Table2.
CREATE TABLE [dbo].[Table2] (
    [Id]        INT        NOT NULL,
    [TestData1] INT        NULL,
    [TestData2] FLOAT (53) NULL,
    [Name]      NTEXT      NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);


--     Create Table3.
CREATE TABLE [dbo].[Table3] (
    [Id]   INT           NOT NULL,
    [Name] NVARCHAR (50) NOT NULL,
    [Data] INT           NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);
GO

CREATE PROCEDURE [dbo].[Procedure1]
       @param1 int = 0,
       @param2 int
AS
       SELECT TestData1 FROM Table1
RETURN 0
GO

-- Insert data into the Table1 table.
USE MyDatabase

INSERT INTO Table1 (Id, TestData1, TestData2, Name)
VALUES(1, 10, 5.5, 'Testing1');
INSERT INTO Table1 (Id, TestData1, TestData2, Name)
VALUES(2, 20, -1.2, 'Testing2');

--Insert data into the Table2 table.
INSERT INTO Table2 (Id, TestData1, TestData2, Name)
VALUES(1, 10, 5.5, 'Testing1');
INSERT INTO Table2 (Id, TestData1, TestData2, Name)
VALUES(2, 20, -1.2, 'Testing2');
INSERT INTO Table2 (Id, TestData1, TestData2, Name)
VALUES(3, NULL, NULL, 'Testing3');

INSERT INTO Table3 (Id, Name, Data)
VALUES (1, 'Testing1', 10);
INSERT INTO Table3 (Id, Name, Data)
VALUES (2, 'Testing2', 100);

Voir aussi

Tâches

Procédure pas à pas : génération de types F# à partir d'un fichier DBML (F#)

Référence

SqlDataConnection, fournisseur de type (F#)

Expressions de requête (F#)

SqlMetal.exe (outil de génération de code)

Autres ressources

Fournisseurs de type

LINQ à SQL [LINQ to SQL]