Procedura dettagliata: accesso a un database SQL tramite provider di tipi (F#)

Articolo
06/14/2015

In questa procedura dettagliata viene illustrato come utilizzare il provider di tipi SqlDataConnection (LINQ to SQL) disponibile in F# 3.0 per generare i tipi per i dati in un database SQL quando si dispone di una connessione attiva a un database. Se non si dispone di una connessione attiva a un database, ma si ha a disposizione un file di schema LINQ to SQL (file DBML), vedere Procedura dettagliata: generazione di tipi F# da un file DBML (F#).

In questa procedura dettagliata vengono illustrate le attività seguenti. Queste attività devono essere eseguite in questo ordine affinché la procedura abbia successo:

Preparing a test database
Creating the project
Setting up a type provider
Querying the data
Working with nullable fields
Calling a stored procedure
Updating the database
Executing Transact-SQL code
Using the full data context
Deleting data
Create a test database (as needed)

Preparare un Database di Test

In un server sul quale è in esecuzione SQL Server, creare un database a scopo di test. È possibile utilizzare lo script di creazione di un database alla fine della pagina nella sezione MyDatabase Create Script per questo scopo.

Per preparare un database di test

Per eseguire MyDatabase Create Script, aprire il menu Visualizza quindi scegliere Esplora oggetti di SQL Server o premere Ctrl+\, Ctrl+S. Nella finestra Esplora oggetti di SQL Server, aprire il menu di scelta rapida per l'istanza appropriata, scegliere Nuova query, copiare lo script nella parte inferiore della pagina e quindi incollare lo script nell'editor. Per eseguire lo script SQL, selezionare l'icona della barra degli strumenti con il simbolo triangolare, o premere Ctrl+Q. Per ulteriori informazioni su Esplora oggetti di SQL Server, vedere Sviluppo del database connesso.

Creazione del progetto

A questo punto, creare un progetto di applicazione F#.

Per creare e configurare il progetto

Creare un nuovo progetto per una applicazione F#.
Aggiungere riferimenti a .FSharp.Data.TypeProviders nonché System.Data e System.Data.Linq.

Aprire il namespace appropriato aggiungendo le seguenti righe di codice all'inizio del codice F# del file Program.fs.

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

Come nella maggior parte dei programmi F#, è possibile eseguire il codice in questa procedura come un programma compilato, oppure eseguirlo in modo interattivo come uno script. Se si preferisce utilizzare gli script, aprire il menu di scelta rapida per il nodo del progetto, selezionare Aggiungi nuovo elemento, aggiungere un file di script F# e aggiungere il codice ad ogni passaggio allo script. Sarà necessario aggiungere le seguenti righe all'inizio del file per caricare i riferimenti di assembly.
```
#r "System.Data.dll"
#r "FSharp.Data.TypeProviders.dll"
#r "System.Data.Linq.dll"
```
È quindi possibile selezionare ciascun blocco di codice per aggiungerlo e premere Alt+Enter per eseguirlo in F# Interactive.

Installazione del provider di tipi

In questo passaggio, occorre creare un provider di tipi per il proprio schema di database.

Per installare il provider di tipi da una connessione diretta con il database

Ci sono due righe di codice critiche che sono necessarie per creare i tipi che possono essere utilizzati per eseguire una query su un database SQL utilizzando il provider di tipi. Innanzitutto, creare un'istanza del provider di tipi. Per fare questo, creare qualcosa di simile a un'abbreviazione di tipo per SqlDataConnection con un parametro generico statico. SqlDataConnection è un provider di tipi SQL e non deve essere confuso con il tipo SqlConnection utilizzato nella programmazione ADO.NET. Se si dispone di un database a cui si desidera connettersi e di una stringa di connessione, utilizzare il codice seguente per richiamare il provider di tipi. Sostituire la propria stringa di connessione con la stringa fornita nell'esempio. Ad esempio, se il server è MYSERVER e l'istanza del database è INSTANCE, il nome del database è MyDatabase e se si desidera utilizzare l'autenticazione di Windows per accedere al database, la stringa di connessione potrebbe essere come quella riportata nel seguente codice di esempio.
```
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
```
Ora si ha a disposizione il tipo, dbSchema, che è un tipo padre contenente tutti i tipi generati che rappresentano le tabelle del database. Inoltre si ha a disposizione un oggetto, db, che ha come suoi membri tutte le tabelle del database. I nomi delle tabelle sono proprietà e il tipo di queste proprietà viene generato dal compilatore F#. Gli stessi tipi appaiono come tipi annidati sotto dbSchema.ServiceTypes. Pertanto, tutti i dati recuperati per le righe di queste tabelle sono un'istanza del tipo appropriato generato per quella tabella. Questo tipo è denominato ServiceTypes.Table1.

Per acquisire familiarità su come il linguaggio F# interpreta le query nelle query SQL, rivedere la riga che imposta la proprietà Log sul contesto dei dati.

Per esplorare ulteriormente i tipi creati dal provider di tipi, aggiungere il codice seguente.
```
let table1 = db.Table1
```
Passare il mouse su table1 per vedere il tipo. Il suo tipo è System.Data.Linq.Table<dbSchema.ServiceTypes.Table1> e l'argomento generico implica che il tipo di ogni riga sia il tipo generato, dbSchema.ServiceTypes.Table1. Il compilatore crea un tipo simile per ogni tabella del database.

Eseguire una query sui dati

In questo passaggio, si scrive una query utilizzando le espressioni query di F#.

Per eseguire una query sui dati

Ora creare una query per tale tabella nel database. Aggiungere il codice riportato di seguito.
```
let query1 =
        query {
            for row in db.Table1 do
            select row
        }
query1 |> Seq.iter (fun row -> printfn "%s %d" row.Name row.TestData1)
```
L'aspetto della parola query indica che questa è un'espressione query, un tipo di espressione di calcolo che genera una raccolta di risultati simili a una tipica database query. Se si passa il mouse sulla query, si potrà notare che è un'istanza di Classe Linq.QueryBuilder (F#), un tipo che definisce l'espressione di calcolo della query. Se si passa il mouse su query1, si noterà che è un'istanza di IQueryable. Come suggerisce il nome, IQueryable rappresenta i dati sui quali è possibile fare una query, non il risultato di una query. Una query è soggetta alla lazy evaluation, ovvero viene eseguita una query sul database solo quando la query viene valutata. La riga finale passa la query attraverso Seq.iter. Le query sono enumerabili e possono essere iterate come le sequenze. Per ulteriori informazioni, vedere Espressioni di query (F#).
Aggiungere ora un operatore di query in una query. Esistono numerosi operatori di query disponibili da utilizzare per costruire query più complesse. In questo esempio viene inoltre mostrato come è possibile eliminare la variabile di query e utilizzare un operatore della pipeline al suo posto.
```
query {
        for row in db.Table1 do
        where (row.TestData1 > 2)
        select row
      }
|> Seq.iter (fun row -> printfn "%d %s" row.TestData1 row.Name)
```

Aggiungere una query più complessa con un join di due tabelle.

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)

Nel codice reale, i parametri della query sono in genere valori o variabili, non costanti in fase di compilazione. Si aggiunga il seguente codice che esegue il wrapping di una query in una funzione che accetta un parametro e poi si chiami quella funzione con il valore 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)

Utilizzare i campi nullable

Nei database, i campi consentono spesso di avere valori null. Nel sistema di tipi di .NET, non è possibile utilizzare i tipi di dati numerici comuni per i dati che consentono valori null perché questi tipi non hanno null come possibile valore. Di conseguenza, questi valori sono rappresentati da istanze del tipo Nullable. Anziché accedere al valore di tali campi direttamente tramite il nome del campo, è necessario aggiungere alcuni passaggi aggiuntivi. È possibile utilizzare la proprietà Value per accedere al valore sottostante di un tipo nullable. La proprietà Value genera un'eccezione se l'oggetto è null anziché avere un valore. È possibile utilizzare il metodo booleano HasValue per determinare se un valore è presente, oppure è possibile utilizzare GetValueOrDefault per assicurarsi di avere un valore effettivo in tutti i casi. Se si utilizza GetValueOrDefault e vi è un null nel database, questo viene sostituito con un valore come una stringa vuota per i tipi di stringa, 0 per i tipi interi o 0,0 per i tipi a virgola mobile.

Quando è necessario eseguire i test di uguaglianza oppure i confronti con valori null in una clausola where in una query, è possibile utilizzare gli operatori nullable presenti in Modulo Linq.NullableOperators (F#). Questi sono simili ai normali operatori di confronto =, >, <=, e così via, ma un punto interrogativo viene visualizzato a sinistra o a destra dell'operatore dove ci sono valori nullable. Ad esempio, l'operatore >? è un operatore maggiore di con un valore nullable a destra. Il modo di funzionamento di questi operatori è che se entrambi i lati dell'espressione sono null, l'espressione vale false. In una clausola where, questo indica in genere che le righe che contengono campi null non sono selezionate e non vengono restituite nei risultati della query.

Per utilizzare i campi nullable

Il codice seguente mostra l'utilizzo dei valori nullable; si supponga che TestData1 sia un campo intero che ammetta valori null.

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)

Chiamare una stored procedure

Le stored procedure nel database possono essere chiamate da F#. Si deve impostare il parametro statico StoredProcedures a true nell'istanza del provider di tipi. Il provider di tipi SqlDataConnection contiene diversi metodi statici che possono essere utilizzati per configurare i tipi generati. Per una descrizione completa di questi, vedere Provider di tipo SqlDataConnection (F#). Un metodo sul tipo di contesto dei dati viene generato per ogni stored procedure.

Per chiamare una stored procedure

Se le stored procedure accettano parametri che sono nullable, è necessario passare un valore appropriato Nullable. Il valore restituito da un metodo di stored procedure che restituisce uno scalare o una tabella è ISingleResult, che contiene le proprietà che consentono di accedere ai dati restituiti. Il tipo dell'argomento per ISingleResult dipende dalla routine specifica ed è anche uno dei tipi generati dal provider dei tipi. Per una stored procedure denominata Procedure1, il tipo è Procedure1Result. Il tipo Procedure1Result contiene i nomi delle colonne della tabella restituita, o, per una stored procedure che restituisce un valore scalare, rappresenta il valore restituito.

Il codice seguente presuppone l'esistenza di una routine Procedure1 sul database che accetta due interi nullable come parametri, esegue una query che ritorna una colonna chiamata TestData1 e restituisce un intero.
```
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)
```

Aggiornamento del database

Il tipo LINQ DataContext contiene dei metodi che semplificano la gestione degli aggiornamenti del database in modo completamente tipizzato con i tipi generati.

Per aggiornare il database

Nel codice seguente, diverse righe sono state aggiunte al database. Se si aggiunge una sola riga, è possibile utilizzare InsertOnSubmit per specificare la nuova riga da aggiungere. Se si inseriscono più righe, è necessario inserirle in una raccolta e chiamare InsertAllOnSubmit``1. Quando si chiama uno di questi metodi, il database non viene immediatamente modificato. È necessario chiamare SubmitChanges per applicare effettivamente i cambiamenti. Di default, tutte le operazioni eseguite prima di chiamare SubmitChanges fanno parte in modo implicito della stessa transazione.

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

Ora occorre ripulire le righe chiamando un'operazione di eliminazione.

// 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

Eseguire del codice Transact-SQL

È inoltre possibile specificare Transact-SQL direttamente tramite il metodo ExecuteCommand nella classe DataContext.

Per eseguire i comandi SQL personalizzati

Nel codice seguente viene illustrato come inviare comandi SQL per inserire un record in una tabella e anche per eliminare un record da una tabella.

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

Utilizzare il contesto dei dati completo

Negli esempi precedenti, il metodo GetDataContext è stato utilizzato per ottenere ciò che viene chiamato contesto dei dati semplificato per lo schema del database. Il contesto dei dati semplificato è più facile da utilizzare quando si stanno costruendo delle query perché non ci sono molti membri disponibili. Pertanto, quando si sfogliano le proprietà in IntelliSense, è possibile concentrarsi sulla struttura del database, come per esempio le tabelle e le stored procedure. Tuttavia, esiste un limite su ciò che è possibile fare con il contesto dei dati semplificato. Un contesto dei dati completo consente di eseguire altre azioni. Questo è presente nei ServiceTypes e ha il nome del parametro statico DataContext se è stato fornito. Se non è stato fornito, il nome del tipo del contesto dei dati viene generato automaticamente da SqlMetal.exe in base all'altro input. Il contesto dei dati completo eredita da DataContext ed espone i membri della classe base, inclusi i riferimenti ai tipi di dati ADO.NET come Connection, i metodi come ExecuteCommand e ExecuteQuery``1 che è possibile utilizzare per scrivere le query in SQL ed è anche un mezzo per utilizzare le transazioni in modo esplicito.

Per utilizzare il contesto dei dati completo

Il codice seguente dimostra come ottenere un contesto dei dati completo utilizzando i comandi di esecuzione direttamente nel database. In questo caso, due comandi vengono eseguiti come parte della stessa transazione.

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()

Eliminazione di dati

Questo passaggio mostra come eliminare righe da una tabella dati.

Per eliminare righe dal database

Ora, si eliminano tutte le righe aggiunte scrivendo una funzione che consente di eliminare righe da una tabella specificata, un'istanza della classe Table. Quindi occorre scrivere una query per trovare tutte le righe che si desidera eliminare e convogliare i risultati della query nella funzione deleteRows. Questo codice sfrutta la possibilità di fornire un applicazione parziale agli argomenti della funzione.
```
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."
```

Creare un database di test

In questa sezione viene illustrato come installare il database di test da utilizzare in questa procedura.

Si noti che se si modifica il database in qualche modo, sarà necessario reimpostare il provider di tipi. Per reimpostare il provider, ricompilare oppure cancellare il progetto che contiene il provider di tipi.

Creare il database di test

In Esplora server, aprire il menu di scelta rapida del nodo Connessioni dati e scegliere Aggiungi connessione. Verrà visualizzata la finestra di dialogo Aggiungi connessione.
Nella casella Nome server, specificare il nome di un'istanza di SQL Server a cui è possibile accedere come amministratore, o se non si ha accesso a un server, specificare (localdb\v11.0). SQL Express LocalDB fornisce un semplice database server per lo sviluppo e il testing sulla vostra macchina. Un nuovo nodo viene creato in Esplora server in Connessioni dati. Per ulteriori informazioni su LocalDB, vedere Procedura dettagliata: creazione di un file di database locale in Visual Studio.
Aprire il menu di scelta rapida per il nuovo nodo della connessione e selezionare Nuova query.

Copiare il seguente script SQL, incollarlo nell'editor di query e quindi premere il pulsante Esegui sulla barra degli strumenti o premere CTRL+MAIUSC+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);

Vedere anche

Condividi tramite