Návod: Přístup k databázi SQL s použitím zprostředkovatelů typů (F#)

Článek
06/14/2015

Tento návod popisuje použití poskytovatele typů SqlDataConnection (LINQ to SQL) jazyka F# 3.0 generujícího typy dat v databázi SQL, máte-li aktivní připojení k databázi.Nemáte-li aktivní připojení k databázi, ale máte soubor schématu LINQ to SQL (soubor DBML), pak další informace naleznete v tématu Návod: Generování typů F# ze souboru DBML (F#).

Tento návod ukazuje následující úkoly.Aby tento návod fungoval, musí úkoly být prováděny v tomto pořadí:

Příprava testovací databáze
Vytvoření projektu
Nastavení poskytovatele typů
Dotazování na data
Práce s poli, které povolují hodnotu Null
Volání uložené procedury
Aktualizace databáze
Spuštění kódu jazyka Transact-SQL
Použití úplného kontextu dat
Odstranění dat
Vytvoření testovací databáze

Příprava testovací databáze

Na běžícím systému SQL Server vytvořte databázi pro účely testování.K tomu lze využít skript pro vytvoření databáze v dolní části této stránky v oddíle Vytvoření testovací databáze.

Chcete-li připravit testovací databázi

Chcete-li spustit skript Vytvoření testovací databáze, otevřete nabídku Zobrazit a poté vyberte položku Průzkumník objektů systému SQL Server nebo stiskněte klávesovou zkratku Ctrl+\, Ctrl+S.V okně Průzkumník objektů systému SQL Server otevřete místní nabídku příslušné instance, vyberte Nový dotaz, zkopírujte skript z dolní části této stránky a vložte jej do editoru.Chcete-li spustit skript jazyka SQL, zvolte ikonu panelu nástrojů s trojúhelníkovým symbolem nebo stiskněte klávesovou zkratku Ctrl+Q.Další informace o průzkumníku Průzkumník objektů systému SQL Server naleznete v tématu Connected Database Development.

Vytvoření projektu

Dále vytvořte projekt aplikace F#.

Chcete-li vytvořit a nastavit projekt

Vytvořte nový projekt aplikace F#.
Přidejte odkazy na sestavení .FSharp.Data.TypeProviders, System.Data a System.Data.Linq.
Otevřete příslušné obory názvů přidáním následujících řádků kódu na začátek souboru kódu F# Program.fs.
```
open System
open System.Data
open System.Data.Linq
open Microsoft.FSharp.Data.TypeProviders
open Microsoft.FSharp.Linq
```
Stejně jako většina programů jazyka F# lze kód v tomto návodu spustit jako zkompilovaný program nebo jej lze spustit interaktivně jako skript.Pokud dáváte přednost použití skriptů, otevřete místní nabídku uzlu projektu, vyberte položku Add New Item, přidejte soubor skriptu jazyka F# a do něj přidejte kód každého kroku návodu.Abyste načetli odkazy na sestavení je nutné přidat následující řádky do horní části souboru.
```
#r "System.Data.dll"
#r "FSharp.Data.TypeProviders.dll"
#r "System.Data.Linq.dll"
```
Chcete-li přidaný blok kódu spustit pomocí komponenty F# Interactive, vyberte jej a stiskněte klávesovou zkratku Alt+Enter.

Nastavení poskytovatele typů

V tomto kroku vytvoříte poskytovatele typů pro schéma databáze.

Chcete-li nastavit poskytovatele typů pomocí přímého připojení k databázi

Existují dva důležité řádky kódu, které jsou nutné pro vytvoření typů, které lze použít k dotazování databáze SQL pomocí poskytovatele typů.Nejprve vytvořte instanci poskytovatele typů.To provedete vytvořením řetězce, který vypadá jako zkratka typu SqlDataConnection se statickým generickým parametrem.Typ SqlDataConnection je poskytovatel typů SQL a neměl by být zaměňován s typem SqlConnection, který se používá v rozhraní ADO.NET.Pokud máte databázi, ke které se chcete připojit a máte připojovací řetězec, použijte následující kód k vyvolání poskytovatele typů.Nahraďte vlastní připojovací řetězec uvedeným příkladem řetězce.Například, pokud má váš server název MYSERVER, instance databáze má název INSTANCE, databáze má název MyDatabase a chcete použít ověřování systému Windows pro přístup k databázi, bude připojovací řetězec podobný tomu v následujícím příkladu kódu.
```
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
```
Nyní máte typ dbSchema, což je nadřazený typ obsahující všechny generované typy, které představují databázové tabulky.Máte také přístup k objektu db, který obsahuje všechny tabulky v databázi jako své členy.Názvy tabulek jsou vlastnosti a typ těchto vlastností je vygenerován pomocí kompilátoru jazyka F#.Typy samotné se zobrazí jako vnořené typy typu dbSchema.ServiceTypes.Data získaná z řádků těchto tabulek jsou tedy instancí odpovídajícího typu, který byl vygenerován pro tuto tabulku.Název typu je ServiceTypes.Table1.

Chcete-li se blíže seznámit s tím jak jazyk F# interpretuje dotazy na dotazy SQL, prohlédněte si řádek, který nastaví vlastnost Log kontextu dat.

Přidejte následující kód, chcete-li dále prozkoumat typy vytvořené pomocí poskytovatele typů.
```
let table1 = db.Table1
```
Jeho typ zobrazíte umístěním ukazatele myši nad table1.Jeho typ je System.Data.Linq.Table<dbSchema.ServiceTypes.Table1> a generický argument znamená, že typ každého řádku je vygenerovaný typ dbSchema.ServiceTypes.Table1.Kompilátor vytvoří podobný typ pro každou tabulku v databázi.

Dotazování na data

V tomto kroku vytvoříte dotaz pomocí výrazů dotazu jazyka F#.

Dotazování na data

Nyní můžete vytvořte dotaz pro tuto tabulku v databázi.Přidejte následující kód.
```
let query1 =
        query {
            for row in db.Table1 do
            select row
        }
query1 |> Seq.iter (fun row -> printfn "%s %d" row.Name row.TestData1)
```
Vzhled slova query označuje, že tento výraz je výrazem dotazu, což je typ výpočetního výrazu, který generuje kolekce výsledků podobné typickému databázovému dotazu.Pokud myší přejedete nad dotazem, uvidíte, že je instancí typu Linq.QueryBuilder – třída (F#), který definuje výpočetní výraz dotazu.Pokud přejedete myší nad query1, uvidíte, že se jedná o instanci typu IQueryable.Jak naznačuje název, objekt typu IQueryable představuje data, která lze dotazovat a ne výsledek dotazu.Dotazy jsou vyhodnocovány opožděně, což znamená, že databáze je dotazována pouze při vyhodnocení dotazů.Poslední řádek předá dotaz prostřednictvím Seq.iter.Dotazy mohou být procházeny jako sekvence.Další informace naleznete v tématu Výrazy dotazu (F#).
Nyní dotazu přidejte operátor dotazu.K dispozici je několik operátorů dotazu, které lze použít ke konstrukci složitějších dotazů.Tento příklad také ukazuje, že lze vynechat proměnnou dotazu a místo toho použít operátor přesměrování.
```
query {
        for row in db.Table1 do
        where (row.TestData1 > 2)
        select row
      }
|> Seq.iter (fun row -> printfn "%d %s" row.TestData1 row.Name)
```

Přidejte složitější dotaz pomocí spojení dvou tabulek.

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)

Ve skutečném kódu jsou parametry dotazu obvykle hodnoty nebo proměnné a ne konstanty definované při kompilaci.Přidejte následující kód, který zabalí dotaz do funkce, která přebírá parametr a pak tuto funkci zavolá s hodnotou 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)

Práce s poli, které povolují hodnotu Null

V databázích pole často povolují hodnoty Null.V typovém systému rozhraní .NET nelze použít běžné číselné datové typy pro data, která povolují hodnotu Null, protože to tyto typy neumožňují.Proto jsou tyto hodnoty reprezentované pomocí instancí typu Nullable.Namísto přímého přístupu k hodnotám takových polí pomocí názvu pole, je nutné učinit některé další kroky.Pro přístup k hodnotě typu s možnou hodnotou Null lze použít vlastnost Value.Vlastnost Value vyvolá výjimku, pokud má objekt hodnotu Null.Chcete-li určit zda hodnota existuje, lze použít metodu HasValue vracející typ Boolean nebo metodu GetValueOrDefault chcete-li zajistit, že máte skutečnou hodnotu ve všech případech.Pokud použijete typ GetValueOrDefault a v databázi je uložená hodnota Null, je tato hodnota nahrazena hodnotou jako je prázdný řetězec pro typy String, 0 pro celočíselné typy nebo 0.0 pro typy s plovoucí desetinou čárkou.

Potřebujete-li provést testy rovnosti nebo porovnání hodnot s možnou hodnotou Null v klauzuli where dotazu, lze použít operátory, které naleznete v tématu Linq.NullableOperators – modul (F#).Ty jsou podobné běžným operátorům porovnání =, >, <= atd., s tím rozdílem, že vlevo nebo vpravo je zobrazen otazník podle toho, kde jsou hodnoty s možnou hodnotou Null.Například operátor >? je operátor větší něž s možnou hodnotou Null na pravé straně.Způsob práce těchto operátorů je takový, že pokud má některá strana výrazu hodnotu Null, výraz se vyhodnotí jako false.V klauzuli where to obvykle znamená, že řádky obsahující pole s hodnotou Null nejsou vybrány a vráceny ve výsledcích dotazu.

Chcete-li pracovat s poli povolujícími hodnotu Null

Následující kód ukazuje práci s hodnotami s možnou hodnotou Null. Předpokládejme, že proměnná TestData1 je celočíselné pole umožňující použití hodnot 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)

Volání uložené procedury

Jakékoliv uložené procedury v databázi lze volat z jazyka F#.Při vytváření instance poskytovatele typů je nutné nastavit statický parametr StoredProcedures na hodnotu true.Poskytovatel typů SqlDataConnection obsahuje několik statických metod, které lze použít ke konfiguraci generovaných typů.Úplný popis těchto metod naleznete v tématu SqlDataConnection – poskytovatel typů (F#).Pro každou uloženou proceduru je vygenerována metoda typu datového kontextu.

Volání uložené procedůry

Pokud uložené procedury přebírají parametry s možnou hodnotou Null, je nutné předat vhodnou hodnotu typu Nullable.Vrácená hodnota metody uložené procedury, která vrací skalární hodnotu nebo tabulku je typu rozhraní ISingleResult, které obsahuje vlastnosti umožňující přístup k vráceným datům.Argument typu pro rozhraní ISingleResult závisí na konkrétní uložené proceduře a je také jedním z typů, které poskytovatel typů generuje.Uložená procedura s názvem Procedure1 je typu Procedure1Result.Typ Procedure1Result obsahuje názvy sloupců ve vrácené tabulce nebo pro uloženou proceduru vracející skalární hodnotu představuje vrácenou hodnotu.

Následující kód předpokládá, že v databázi existuje procedura Procedure1, která přebírá dvě celá čísla povolující hodnotu Null jako parametry, spustí dotaz vracející sloupec s názvem TestData1 a vrátí celé číslo.
```
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)
```

Aktualizace databáze

Typ DataContext jazyka LINQ obsahuje metody, které usnadňují provádění transakčních aktualizací databáze plně typovým způsobem pomocí vygenerovaných typů.

Chcete-li aktualizovat databázi

V následujícím kódu je do databáze přidáno několik řádků.Pokud přidáváte pouze jeden řádek, lze pro přidání nového řádku použít metodu InsertOnSubmit.Pokud přidáváte více řádků, měli byste je vložit do kolekce a zavolat metodu InsertAllOnSubmit``1.Při volání kterékoliv z těchto metod se databáze nezmění okamžitě.Chcete-li skutečně potvrdit změny, musíte zavolat metodu SubmitChanges.Standardně je všechno co provedete před voláním metody SubmitChanges implicitně součástí jedné transakce.

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

Nyní odstraňte řádky pomocí volání operace odstranění.

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

Spuštění kódu jazyka Transact-SQL

Kód jazyka Transact-SQL lze také určit přímo pomocí metody ExecuteCommand třídy DataContext.

Chcete-li spustit vlastní příkazy jazyka SQL

Následující kód ukazuje, jak odeslat příkazy jazyka SQL pro vložení záznamu do tabulky a také pro odstranění záznamu z tabulky.

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

Použití úplného kontextu dat

V předchozích příkladech byla metoda GetDataContext použita k získání tzv. zjednodušeného kontextu dat schématu databáze.Zjednodušený kontext dat je jednodušší použít při vytváření dotazů, protože zde není k dispozici tolik členů.Proto se při procházení vlastností pomocí technologie IntelliSense můžete zaměřit na strukturu databáze, jako jsou tabulky a uložené procedury.Avšak existuje omezení co můžete se zjednodušeným kontextem dat provést.Úplný kontext dat umožňuje provádět další akce.Je dostupný prostřednictvím typu ServiceTypes a pokud je zadán, má název statického parametru typu DataContext.Pokud není zadán, název typu kontextu dat je vygenerován pomocí programu SqlMetal.exe v závislosti na dalším vstupu.Úplný kontext dat dědí z třídy DataContext a zpřístupňuje členy své základní třídy včetně odkazů na datové typy rozhraní ADO.NET, jako je objekt Connection, metody ExecuteCommand a ExecuteQuery``1, které lze použít pro psaní dotazů jazyka SQL a také pro explicitní práci s transakcemi.

Chcete-li použít úplný kontext dat

Následující kód ukazuje získání úplného objektu kontextu dat a jeho použití k provedení příkazů přímo proti databázi.V tomto případě jsou dva příkazy provedeny jako součást stejné transakce.

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

Odstranění dat

Tento krok ukazuje, jak odstranit řádky z tabulky dat.

Chcete-li odstranit řádky z databáze

Nyní odstraňte všechny přidané řádky pomocí funkce, která odstraní řádky z dané tabulky instance třídy Table.Poté vytvořte dotaz, který najde všechny řádky k odstranění a výsledky dotazu předá funkci deleteRows.Tento kód využívá schopnost poskytovat částečnou aplikaci argumentů funkce.
```
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."
```

Vytvoření testovací databáze

Tato část ukazuje jak nastavit testovací databázi použitou v tomto návodu.

Poznámka: Pokud databázi nějakým způsobem změníte, bude nutné obnovit poskytovatele typů.Chcete-li poskytovatele typů obnovit, znovu sestavte nebo vyčistěte projekt obsahující poskytovatele typů.

Chcete-li vytvořit testovací databázi

V podokně Průzkumníku serveru otevřete místní nabídku uzlu Datová připojení a zvolte Přidat připojení.Zobrazí se dialogové okno Přidat připojení.
Do pole Název serveru zadejte název instance systému SQL Server, ke kterému máte přístup pro správu nebo pokud nemáte k serveru přístup, zadejte (localdb\v11.0).Server SQL Express LocalDB poskytuje lehký databázový server pro vývoj a testování ve vašem počítači.V podokně Průzkumník serveru je pod uzlem Datová připojení vytvořen nový uzel.Další informace o databázi LocalDB naleznete v tématu Návod: Vytvoření lokálního databázového souboru v sadě Visual Studio.
Otevřete místní nabídku nového uzlu připojení a vyberte Nový dotaz.

Zkopírujte následující skript jazyka SQL, vložte jej do editoru dotazu a poté na panelu nástrojů stiskněte tlačítko Spustit nebo stiskněte klávesovou zkratku Ctrl + Shift + 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);

Viz také

Sdílet prostřednictvím

Návod: Přístup k databázi SQL s použitím zprostředkovatelů typů (F#)

Příprava testovací databáze

Chcete-li připravit testovací databázi

Vytvoření projektu

Chcete-li vytvořit a nastavit projekt

Nastavení poskytovatele typů

Chcete-li nastavit poskytovatele typů pomocí přímého připojení k databázi

Dotazování na data

Dotazování na data

Práce s poli, které povolují hodnotu Null

Chcete-li pracovat s poli povolujícími hodnotu Null

Volání uložené procedury

Volání uložené procedůry

Aktualizace databáze

Chcete-li aktualizovat databázi

Spuštění kódu jazyka Transact-SQL

Chcete-li spustit vlastní příkazy jazyka SQL

Použití úplného kontextu dat

Chcete-li použít úplný kontext dat

Odstranění dat

Chcete-li odstranit řádky z databáze

Vytvoření testovací databáze

Chcete-li vytvořit testovací databázi

Viz také

Úkoly

Referenční dokumentace

Další zdroje

Další materiály