Tutorial: Obtener acceso a una base de datos SQL mediante proveedores de tipo (F#)

  • Artículo

Este tutorial explica cómo utilizar el proveedor de tipos de SqlDataConnection (LINQ a SQL) que está disponible en F# 3.0 para generar tipos para los datos de una base de datos SQL, cuando se tiene una conexión activa a una base de datos.Si no se tiene una conexión activa a una base de datos, pero tiene un archivo de esquema de LINQ a SQL (archivo DBML), vea Tutorial: Generar tipos en F# a partir de un archivo DBML (F#).

En este tutorial se muestran las siguientes tareas:Estas tareas se deben realizar en este orden para que el tutorial se realice correctamente:

  • Preparar una base de datos de prueba

  • Crear el proyecto

  • Configurar un proveedor de tipos

  • Consultar los datos

  • Trabajar con campos que aceptan valores nulos

  • Llamar a un procedimiento almacenado

  • Actualizar la base de datos

  • Ejecutar el código de Transact-SQL

  • Utilizar el contexto de datos completo

  • Eliminar datos

  • Crear una base de datos de prueba

Preparar una base de datos de prueba

En un servidor que ejecuta SQL Server, cree una base de datos para hacer comprobaciones.Puede utilizar el script para crear una base de datos, en la parte inferior de esta página, en la sección Crear una base de datos de prueba para hacerlo.

Para preparar una base de datos de prueba

  • Para ejecutar Crear una base de datos de prueba, abra el menú Ver y, a continuación, elija Explorador de objetos de SQL Server o elija las claves Ctrl+\, Ctrl+S.En la ventana Explorador de objetos de SQL Server, abra el acceso directo para la instancia correcta, elija Nueva consulta, copie el script en la parte inferior de esta página y, a continuación, pegue el script en el editor.Para ejecutar el script SQL, elija el icono de la barra de herramientas con el símbolo triangular o pulse las teclas Ctrl+Q.Para obtener más información sobre Explorador de objetos de SQL Server, vea Desarrollo de bases de datos en línea.

Crear el proyecto

A continuación, se creará un proyecto de aplicación F#.

Para crear y configurar el proyecto

  1. Cree un nuevo proyecto de aplicación F#.

  2. Agregue referencias a .FSharp.Data.TypeProviders, así como a System.Data y System.Data.Linq.

  3. Abra los espacios de nombres adecuados agregando las siguientes líneas de código a la parte superior del archivo de código F# Program.fs.

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

  4. Como ocurre con la mayoría de los programas de F#, se puede ejecutar el código de este tutorial como un programa compilado o puede ejecutarlo interactivamente como un script.Si prefiere utilizar scripts, abra el acceso directo para el nodo del proyecto, seleccione Agregar nuevo elemento, agregue un archivo de script de F# y vaya agregando al script el código en cada paso.Necesitará agregar las líneas siguientes en la parte superior del archivo para cargar las referencias de ensamblado.

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

    En ese momento, puede seleccionar cada bloque de código a medida que lo agrega y presionar Alt+Enter para ejecutarlo en modo F# interactivo.

Configurar un proveedor de tipos

En este paso, se creará un proveedor de tipos para el esquema de la base de datos.

Para configurar el proveedor de tipos desde una conexión de base de datos directa

  • Hay dos líneas de código críticas que se necesitan para crear los tipos que puede utilizar para consultar una base de datos SQL mediante el proveedor de tipos.Primero, se crea una instancia del proveedor de tipos.Para ello, cree lo que parece ser una abreviatura de tipo para SqlDataConnection con un parámetro genérico estático.SqlDataConnection es un proveedor de tipos SQL y no debe confundirse con el tipo SqlConnection que se utiliza en programación en ADO.NET.Si tiene una base de datos a la que desea conectarse y tiene una cadena de conexión, utilice el siguiente código para invocar al proveedor de tipos.Sustituya su propia cadena de conexión para la cadena de ejemplo indicada.Por ejemplo, si el servidor es MYSERVER y la instancia de base de datos es INSTANCE, el nombre de la base de datos es MyDatabase y si desea utilizar la autenticación de Windows para tener acceso a la base de datos, entonces la cadena de conexión sería como se muestra en el código de ejemplo siguiente.

    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

    Ahora tiene un tipo, dbSchema, que es un tipo primario que contiene todos los tipos generados que representan tablas de base de datos.También tiene un objeto, db, que tiene como miembros todas las tablas de la base de datos.Los nombres de tabla son propiedades y el compilador de F# genera el tipo de estas propiedades.Los tipos aparecen como tipos anidados bajo dbSchema.ServiceTypes.Por consiguiente, cualquier dato recuperado para las filas de estas tablas son instancias del tipo adecuado generado para la tabla.El nombre de este tipo es ServiceTypes.Table1.

    Para familiarizarse con la interpretación de consultas SQL que hace el lenguaje F#, revise la línea que establece la propiedad Log en el contexto de datos.

    Para explorar más los tipos creados por el proveedor de tipos, agregue el código siguiente.

    let table1 = db.Table1

    Mantenga el cursor sobre table1 para ver su tipo.Su tipo es System.Data.Linq.Table<dbSchema.ServiceTypes.Table1> y el argumento genérico implica que el tipo de cada fila es el tipo generado, dbSchema.ServiceTypes.Table1.El compilador crea un tipo similar para cada tabla de la base de datos.

Consultar los datos

En este paso, se escribe una consulta mediante expresiones de consulta de F#.

Para consultar los datos

  1. Ahora cree una consulta para esa tabla en la base de datos.Agregue el código siguiente:

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

    El aspecto de la palabra query indica que es una expresión de consulta, un tipo de expresión de cálculo que genera una colección de resultados similares a los de una consulta de base de datos convencional.Si mantiene el cursor sobre la consulta, verá que es una instancia de Linq.QueryBuilder (Clase de F#), un tipo que define la expresión de cálculo de la consulta.Si mantiene el cursor sobre query1, verá que es una instancia de IQueryable<T>.Como su propio nombre indica, IQueryable<T> representa los datos que pueden ser consultados, no el resultado de una consulta.Una consulta está sujeta a la evaluación perezosa, lo que significa que se consulta a la base de datos sólo cuando se evalúa la consulta.La línea final pasa la consulta a través de Seq.iter.Las consultas son enumerables y se pueden recorrer como secuencias.Para obtener más información, vea Expresiones de consulta (F#).

  2. Ahora agregue un operador de consulta a la consulta.Hay varios operadores de consulta disponibles que se pueden utilizar para construir consultas más complejas.Este ejemplo también muestra que se puede eliminar la variable de consulta y utilizar un operador de canalización en su lugar.

    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. Agregue una consulta más compleja con una combinación de dos tablas.

    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. En código del mundo real, los parámetros de la consulta son normalmente valores o variables, no constantes en tiempo de compilación.Agregue el código siguiente que encapsula una consulta en una función que toma un parámetro y a continuación llama a esa función con el 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)

Trabajar con campos que aceptan valores nulos

En bases de datos, los campos permiten a menudo valores nulos.En el sistema de tipos de .NET, no se pueden usar los tipos de datos numéricos normales para los datos que permiten valores nulos, ya que esos tipos no aceptan valores nulos.Por consiguiente, estos valores se representan por instancias de tipo de Nullable<T>.En lugar de tener acceso al valor de dichos campos directamente con el nombre del campo, se necesita agregar algunos pasos adicionales.Se puede utilizar la propiedad Value para tener acceso al valor subyacente de un tipo que acepta valores nulos.La propiedad Value lanza una excepción si el objeto es null en lugar de tener un valor.Se puede utilizar el método booleano HasValue para determinar si existe un valor, o usar GetValueOrDefault para asegurarse de que tiene un valor real en todos los casos.Si se utiliza GetValueOrDefault y hay un valor nulo en la base de datos, éste se reemplaza con un valor como una cadena vacía para los tipos string, 0 para tipos enteros o 0,0 para los tipos de coma flotante.

Cuando se necesita realizar pruebas o comparaciones de igualdad de valores que pueden ser nulos en una cláusula where en una consulta, se pueden utilizar los operadores que aceptan valores nulos que se encuentran en Linq.NullableOperators (Módulo de F#).Estos son como los operadores de comparación regulares =, >, <=, etc., sólo que un signo de interrogación aparece a la izquierda o la derecha del operador donde se encuentran los valores que pueden ser nulos.Por ejemplo, el operador >? es un operador mayor-que que acepta valores NULL a la derecha.La forma en que trabajan estos operadores es tal que si cualquier lado de la expresión es nula, la expresión se evalúa como false.En una cláusula where, suele suceder que las filas que contienen campos nulos no se seleccionan y por tanto no son devueltas en los resultados de la consulta.

Para trabajar con campos que aceptan valores nulos

  • El código siguiente muestra cómo trabajar con valores que pueden ser nulos; suponga que TestData1 es un campo entero que permite valores nulos.

    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)

Llamar a un procedimiento almacenado

Cualquier procedimiento almacenado en la base de datos se puede llamar desde F#.Se debe establecer el parámetro estático StoredProcedures a true en la instancia del proveedor de tipos.El proveedor de tipos SqlDataConnection contiene varios métodos estáticos que puede utilizar para configurar los tipos que se generan.Para obtener una descripción completa de estas características, vea SqlDataConnection (Proveedor de tipo de F#).Para cada procedimiento almacenado se genera un método en el tipo del contexto de datos.

Para llamar a un procedimiento almacenado.

  • Si los procedimientos almacenados toman parámetros que admiten valores nulos, se le debe pasar un valor Nullable<T> apropiado.El valor devuelto por un método de procedimiento almacenado que devuelve un valor escalar o una tabla es ISingleResult<T>, que contiene propiedades que permiten tener acceso a los datos devueltos.El argumento tipo para ISingleResult<T> depende del procedimiento concreto aunque también es uno de los tipos que el proveedor de tipos genera.Para un procedimiento almacenado denominado Procedure1, el tipo es Procedure1Result.El tipo Procedure1Result contiene los nombres de las columnas en una tabla devuelta o representa el valor devuelto para un procedimiento almacenado que devuelve un valor escalar.

    El código siguiente supone que hay un procedimiento Procedure1 en la base de datos que toma dos enteros que aceptan como parámetros valores nulos, ejecuta una consulta que devuelve una columna denominada TestData1 y devuelve un entero.

    

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)

Actualizar la base de datos

El tipo DataContext de LINQ contiene métodos que facilitan las actualizaciones de transacciones en la base de datos, siguiendo un modo de tipos completos con los tipos generados.

Para actualizar la base de datos

  1. En el código siguiente, se agregan varias filas a la base de datos.Si está agregando sólo una fila, se puede utilizar InsertOnSubmit para especificar la nueva fila que se va a agregar.Si está insertando varias filas, debe agruparlas y llamar a InsertAllOnSubmit<TSubEntity>.Cuando se llama a cualquiera de estos métodos, la base de datos no cambia inmediatamente.Se debe llamar a SubmitChanges para confirmar los cambios.Por defecto, todo lo que se hace antes de llamar a SubmitChanges forma parte implícitamente de la misma transacción.

    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. Ahora borre las filas llamando a una operación de eliminació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

Ejecutar el código de Transact-SQL

También se puede especificar Transact-SQL directamente utilizando el método ExecuteCommand en la clase DataContext.

Para ejecutar comandos SQL personalizados

  • El código siguiente muestra cómo enviar comandos SQL para insertar un registro en una tabla así como para eliminar un registro de una tabla.

    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

Utilizar el contexto de datos completo

En los ejemplos anteriores, el método GetDataContext se ha utilizado para obtener lo que se denomina contexto de datos simplificado para el esquema de la base de datos.El contexto de datos simplificado es más fácil de utilizar cuando se construyen consultas porque no hay tantos miembros disponibles.Por consiguiente, cuando se examinan las propiedades en IntelliSense, se puede centrar en la estructura de base de datos, como son las tablas y los procedimientos almacenados.Sin embargo, existen ciertas limitaciones sobre lo que se puede hacer con el contexto de datos simplificado.Un contexto de datos completo que proporciona la capacidad de realizar otras accionestambién está disponible. Éste se encuentra en ServiceTypes y tiene el nombre de parámetro estático DataContext si se ha proporcionado.Si no se ha proporcionado, se genera automáticamente el nombre del tipo de contexto de datos para SqlMetal.exe basado en la otra entrada.El contexto de datos completo hereda de DataContext y expone los miembros de su clase base, incluyendo referencias a los tipos de datos de ADO.NET como el objeto Connection, los métodos como ExecuteCommand y ExecuteQuery que se pueden utilizar para escribir consultas en SQL y también permiten ejecutar transacciones explícitamente.

Para utilizar el contexto de datos completo

  • El código siguiente muestra cómo obtener un objeto completo de contexto de datos y utilizarlo para ejecutar comandos directamente en la base de datos.En este caso, se ejecutan dos comandos como parte de la misma transacción.

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

Eliminar datos

Este paso muestra cómo eliminar filas de una tabla de datos.

Para eliminar filas de la base de datos

  • Ahora, borre cualquier fila agregada escribiendo una función que elimina filas de una tabla especificada, una instancia de la clase Table<TEntity> .Luego, escriba una consulta para buscar todas las filas que desea eliminar y canalize los resultados de la consulta a la función deleteRows.Este código aprovecha la capacidad de proporcionar la aplicación parcial de argumentos de función.

    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."

Crear una base de datos de prueba

En esta sección se muestra cómo configurar la base de datos de prueba que se desea usar en este tutorial.

Observe que si se modifica la base de datos de alguna manera, se tendrá que restaurar el proveedor de tipos.Para restaurar el proveedor de tipos, recompile o limpie el proyecto que contiene el proveedor de tipos.

Para crear la base de datos de prueba

  1. En el Explorador de servidores, abra el acceso directo para el nodo Conexiones de datos y elija Agregar conexión.Aparecerá el cuadro de diálogo Agregar conexión.

  2. En el cuadro Nombre del servidor, especifique el nombre de una instancia de SQL Server a la que tenga acceso administrativo o si no tiene acceso a un servidor, especifique (localdb\v11.0).SQL Express LocalDB proporciona un servidor de bases de datos ligero para el desarrollo y las pruebas en el equipo.Un nuevo nodo se crea en el Explorador de servidores bajo Conexiones de datos.Para obtener más información acerca de LocalDB, consulte Tutorial: Crear una base de datos LocalDB.

  3. Abra el acceso directo para el nuevo nodo de conexión y seleccione Nueva consulta.

  5. Copie el script SQL siguiente, péguelo en el editor de consultas y, a continuación, haga clic en el botón Ejecutar en la barra de herramientas o presione las teclas Ctrl+Mayús+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);

Vea también

Tareas

Tutorial: Generar tipos en F# a partir de un archivo DBML (F#)

Referencia

SqlDataConnection (Proveedor de tipo de F#)

Expresiones de consulta (F#)

SqlMetal.exe (Herramienta de generación de código)

Otros recursos

Proveedores de tipo

LINQ to SQL