Instruções passo a passo: acessando um banco de dados SQL por meio de provedores de tipos (F#)

Artigo
06/10/2015

Essa explicação passo a passo explica como usar o provedor de tipo SqlDataConnection (LINQ te o SQL) que está disponível no F# 3,0 para gerar tipos para dados em um banco de dados SQL quando você tiver uma conexão ativa a um banco de dados. Se você não tiver uma conexão ativa a um banco de dados, mas você tem um arquivo esquema LINQ to SQL (arquivo DBML), consulte Instruções passo a passo: gerando tipos F# com base em um arquivo DBML (F#).

Essa explicação passo a passo ilustra as seguintes tarefas: Essas tarefas devem ser executadas nesta ordem para que o passo a passo tenha sucesso:

Preparando um Banco de Dados Teste
Criando o projeto
Configurando um provedor de tipo
Consultando os dados
Trabalhando com campos anuláveis
Chamando um procedimento armazenado
Atualizando o banco de dados
Executando código Transact-SQL
Usando o contexto de dados completo
Excluindo dados
Criando um banco de dados teste

Preparando um Banco de Dados Teste

Em um servidor que esteja executando SQL Server, crie um banco de dados para fins de teste. Para fazer isso, você pode usar o script de criação de banco de dados na parte inferior desta página na seção Criando um banco de dados teste.

Para preparar um Banco de Dados de teste

Para executar o Criando um banco de dados teste, abra o menu Visualização, e então escolha Explorador de Objeto SQL Server ou escolha as chaves Ctrl+\, Ctrl+S. Na janela Explorador de Objeto SQL Server, abra o menu de atalho para a instância apropriada, escolha Nova Consulta, copie o script na parte inferior desta página, e então cole o script no editor. Para executar o script SQL, escolha o ícone da barra de ferramentas com o símbolo triangular, ou escolha as chaves Ctrl+Q. Para obter mais informações sobre Explorador de Objeto SQL Server, consulte Desenvolvimento de Banco de Dados Conectado

Criando o projeto

Em seguida, você cria um projeto de aplicativo F#.

Para criar e configurar o projeto

Crie um novo projeto de aplicativo F#.
Adicione referências a .FSharp.Data.TypeProviders, bem como System.Data, e System.Data.Linq.
Abra os namespaces apropriados adicionando as seguintes linhas de código à parte superior do seu arquivo Program.fs de código F#.
```
open System
open System.Data
open System.Data.Linq
open Microsoft.FSharp.Data.TypeProviders
open Microsoft.FSharp.Linq
```
Assim como a maioria dos programas de F#, você pode executar o código nessa explicação passo a passo como um programa compilado, ou você pode executá-lo interativamente como um script. Se você prefere usar scripts, abra o menu de atalho para o nó do projeto, selecione Adicionar novo item, adicione um arquivo de script F#, e adicione o código em cada etapa para o script. Você precisará adicionar as seguintes linhas na parte superior do arquivo para carregar as referências assembly.
```
#r "System.Data.dll"
#r "FSharp.Data.TypeProviders.dll"
#r "System.Data.Linq.dll"
```
Você pode então selecionar cada bloco de código enquanto você o adiciona e pressionar Alt+Enter para o executar em F# interativo.

Configurando um provedor de tipo

Nesta etapa, você cria um provedor de tipo para seu esquema de banco de dados.

Para configurar o provedor de tipo de uma conexão de banco de dados direta.

Há duas linhas de código importantes que você precisa para criar os tipos que você pode usar para consultar um banco de dados SQL usando o provedor de tipo. Primeiro, você cria uma instância do provedor de tipo. Para fazer isso, crie o que parece uma abreviação de tipo para uma SqlDataConnection com um parâmetro estático genérico. SqlDataConnection é um tipo de provedor SQL, e não deve ser confundido com o tipo SqlConnection que é usado na programação ADO.NET. Se você tiver um banco de dados ao qual você deseja se conectar, e tem uma cadeia de conexão, use o seguinte código para chamar o provedor de tipo. Substitua sua própria cadeia de conexão pelo cadeia de caracteres dada como exemplo. Por exemplo, se o seu servidor é MYSERVER e a instância do banco de dados é INSTANCE, o nome do banco de dados é MyDatabase, e você deseja usar a Autenticação do Windows para acessar o banco de dados, então a cadeia de conexão seria como a fornecida no seguinte exemplo de código.
```
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
```
Agora você tem um tipo, dbSchema, que é um tipo pai que contém todos os tipos gerados que representam tabelas de banco de dados. Você também tem um objeto, db, que tem como seus membros todas as tabelas no banco de dados. Os nomes de tabela são propriedades, e o tipo dessas propriedades é gerado pelo compilador de F#. Os próprios tipos aparecem como tipos aninhados em dbSchema.ServiceTypes. Portanto, quaisquer dados recuperados para linhas dessas tabelas são uma instância do tipo apropriado que foi gerado para esta tabela. O nome do tipo é ServiceTypes.Table1

Para se familiarizar com como a linguagem F# interpreta consultas em consultas SQL, revise a linha que define a propriedade Log no contexto de dados.

Para explorar mais os tipos criados pelo provedor de tipo, adicione o seguinte código.
```
let table1 = db.Table1
```
Passe sobre table1 para ver seu tipo. O tipo é System.Data.Linq.Table<dbSchema.ServiceTypes.Table1> e o argumento genérico implica que o tipo de cada linha é o tipo gerado dbSchema.ServiceTypes.Table1. O compilador cria um tipo semelhante para cada tabela no banco de dados.

Consultando os dados

Nesta etapa, você escreve uma consulta usando expressões F# de consulta.

Para consultar os dados

Agora crie uma consulta para esta tabela no banco de dados. Adicione o seguinte código.
```
let query1 =
        query {
            for row in db.Table1 do
            select row
        }
query1 |> Seq.iter (fun row -> printfn "%s %d" row.Name row.TestData1)
```
A aparência da palavra query indica que essa é uma expressão de consulta, um tipo da expressão de computação que gera uma coleção de resultados semelhantes a uma consulta típica de banco de dados. Se você pairar sobre uma consulta, você verá que é uma instância Classe Linq.QueryBuilder (F#), um tipo que define a expressão de computação de consulta. Se você pairar sobre query1, você verá que é uma instância IQueryable. Como o nome sugere, IQueryable representa dados que podem ser consultados, não o resultado de uma consulta. Uma consulta está sujeita a avaliação lenta, o que significa que o banco de dados somente é consultado quando a consulta é avaliada. A linha final passa a consulta pelo Seq.iter. Consultas são enumeráveis e podem ser iteradas como sequências. Para obter mais informações, consulte Expressões de consulta (F#).
Agora adicione um operador de consulta à consulta. Há muitos operadores de consulta disponíveis que você pode usar para criar consultas mais complexas. Este exemplo também mostra que você pode eliminar a variável de consulta e usar um operador pipeline em vez disso.
```
query {
        for row in db.Table1 do
        where (row.TestData1 > 2)
        select row
      }
|> Seq.iter (fun row -> printfn "%d %s" row.TestData1 row.Name)
```

Adicione uma consulta mais complexa com uma união de duas tabelas.

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)

No código do mundo real, os parâmetros em sua consulta são geralmente valores ou variáveis, não constantes de tempo de compilação. Adicione o seguinte código que envolve uma consulta em uma função que utiliza um parâmetro e, em seguida, chama esta função com o valor 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)
```

Trabalhando com campos anuláveis

Em bancos de dados, campos frequêntemente permitem valores nulos. No sistema tipo .NET, você não pode usar os tipos de dados numéricos comuns para dados que permitem nulo porque estes tipos não têm o zero como um valor possível. Portanto, esses valores são representados por instâncias do tipo Nullable. Em vez de acessar o valor desses campos diretamente com o nome do campo, você precisa adicionar algumas etapas extras. Você pode usar a propriedade Value para acessar o valor subjacente de um tipo anulável. A propriedade Value gera uma exceção se o objeto é nulo em vez de ter um valor. Você pode usar o método booleano HasValue para determinar se um valor existe, ou usar GetValueOrDefault para garantir que tem um valor real em todos os casos. Se você usar GetValueOrDefault e há um zero no banco de dados, então este é substituído por um valor como uma cadeia de caracteres vazia para tipos de cadeia de caracteres, 0 para tipos integral ou 0.0 para tipos de ponto flutuante.

Quando você precisa realizar testes de igualdade ou comparações em valores anuláveis em uma cláusula where de uma consulta, você pode usar os operadores anuláveis localizados no Módulo Linq.NullableOperators (F#). Esses são como os operadores de comparação comuns =, >, <=, e assim por diante, exceto que um ponto de interrogação aparece na esquerda ou direita do operador onde os valores anuláveis estão. Por exemplo, o operador >? é um operador maior-que com um valor anulável à direita. A maneira como esses operadores trabalham é que se um ou outro lado da expressão for zero, a expressão será avaliada como false. Em uma cláusula where, isso geralmente significa que as linhas que contêm valores nulos não são selecionadas nem retornadas nos resultados da consulta.

Para trabalhar com campos anuláveis

O código a seguir mostra como trabalhar com valores anuláveis; suponha que TestData1 é um campo inteiro que permita nulo.

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)

Chamando um procedimento armazenado

Quaisquer procedimentos armazenados no banco de dados podem ser chamados por F#. Você deve definir o parâmetro estático StoredProcedures como true na instanciação do provedor de tipo. O provedor de tipo SqlDataConnection contém vários métodos estáticos que você pode usar para configurar os tipos que são gerados. Para obter uma descrição completa desses, consulte Provedor de tipo SqlDataConnection (F#). Um método no tipo de contexto de dados é gerado para cada procedimento armazenado.

Para chamar um procedimento armazenado

Se os procedimentos armazenados usam parâmetros que são anuláveis, você precisa passar um valor apropriado Nullable. O valor de retorno de um método de procedimento armazenado que retorna um escalar ou uma tabela é ISingleResult, que contém propriedades que permitem que você acesse os dados retornados. O argumento de tipo para ISingleResult depende do procedimento específico e também é um dos tipos que o provedor de tipo produz. Para um procedimento armazenado chamado Procedure1, o tipo é Procedure1Result O tipo Procedure1Result contém os nomes das colunas em uma tabela retornada, ou, para um procedimento armazenado que retorna um valor escalar, ele representa o valor de retorno.

O código a seguir pressupõe que há um procedimento Procedure1 no banco de dados que recebe dois inteiros anuláveis como parâmetros, executa uma consulta que retorna uma coluna chamada TestData1, e retorna um número inteiro.
```
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)
```

Atualizando o banco de dados

O tipo LINQ DataContext contém métodos que facilitam a execução de atualizações transacionadas do banco de dados em uma forma totalmente tipada com os tipos gerados.

Para atualizar o banco de dados

No código a seguir, várias linhas são adicionadas ao banco de dados. Se você estiver apenas adicionando uma linha, você pode usar InsertOnSubmit para especificar a nova linha a adicionar. Se você estiver inserindo várias linhas, você deve coloca-las em uma coleção e chamar InsertAllOnSubmit``1. Quando você chama qualquer desses métodos, o banco de dados não é alterado imediatamente. Você deve chamar SubmitChanges para realmente confirmar as alterações. Por padrão, tudo que você faz antes de chamar SubmitChanges é implicitamente parte da mesma transação.

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

Agora limpe as linhas chamando uma operação de exclusão.

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

Executando código Transact-SQL

Você também pode especificar Transact-SQL diretamente usando o método ExecuteCommand na classe DataContext.

Para executar comandos SQL personalizados

O código a seguir mostra como enviar comandos SQL para inserir um registro em uma tabela e também como excluir um registro de uma tabela.

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

Usando o contexto de dados completo

Nos exemplos anteriores, o método GetDataContext foi usado para obter o que é chamado contexto de dados simplificado para o esquema do banco de dados. O contexto de dados simplificado é mais fácil de usar quando você está construindo consultas porque não há tantos membros disponíveis. Portanto, quando você pelas propriedades no IntelliSense, você pode focalizar na estrutura do banco de dados, como as tabelas e procedimentos armazenados. No entanto, há um limite para o que você pode fazer com o contexto de dados simplificado. Um contexto de dados completo que fornece a capacidade de executar outras ações. também está disponível. Está localizado no ServiceTypes e possui o nome do parâmetro estático DataContext se você o forneceu. Se você não tiver fornecido, o nome do tipo de contexto de dados é gerado para você pelo SqlMetal.exe baseado em outra entrada. O contexto de dados completa herda do DataContext e expõe os membros de sua classe base, incluindo referências a tipos de dados ADO.NET como o objeto Connection, métodos como ExecuteCommand e ExecuteQuery``1 que você pode usar para escrever consultas em SQL, e também um meio para trabalhar com transações explicitamente.

Para usar o contexto de dados completo

O código a seguir demonstra como obter um objeto de contexto de dados completo e usá-lo para executar comandos diretamente contra o banco de dados. Nesse caso, dois comandos são executados como parte da mesma transação.

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

Excluindo dados

Esse passo mostra como excluir linhas de uma tabela de dados.

Para excluir linhas do banco de dados

Agora, limpe quaisquer linhas adicionadas escrevendo uma função que excluir linhas de uma tabela especificada, uma instância da classe Table. Então escreva uma consulta para localizar todas as linhas que você deseja excluir, e leve os resultados da consulta na função deleteRows. Esse código aproveita a capacidade de fornecer aplicação parcial de argumentos de função.
```
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."
```

Criando um banco de dados teste

Esta seção mostra como configurar o banco de dados teste para usar na explicação passo a passo.

Observe que se você alterar o banco de dados de alguma maneira, você terá que reinicializar o provedor de tipo. Para redefinir o provedor de tipo, recontrua ou limpe o projeto que contém o provedor de tipo.

Para criar o banco de dados de teste

No Gerenciador de Servidores, abra o menu de atalho para o nó Conexões de Dados, e clique em Adicionar Conexão. Aparecerá a caixa de diálogo Add Connection.
Na caixa Nome do servidor, especifique o nome de uma instância do Servidor SQL que você tem acesso administrativo, ou se você não tiver acesso a um servidor, especifique (localdb\v11.0). O SQL Server Express LocalDB fornece um servidor de banco de dados leve para desenvolvimento e testes no seu computador. Um novo nó é criado no Gerenciador de Servidores em baixo de Conexões de Dados. Para obter mais informações sobre LocalDB, veja Instruções passo a passo: criando um arquivo de banco de dados local no Visual Studio.
Abra o menu de atalho para o novo nó de conexão, e selecione Nova Consulta.

Copie o seguinte script SQL, cole-o no editor de consulta, e em seguida, clique no botão Executar na barra de ferramentas ou escolha as chaves 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);

Consulte também

Compartilhar via