演练：使用类型提供程序访问 SQL 数据库 (F#)

  • 项目

此演练解释如何使用可用的 F# 3.0 生成数据的类型在 SQL 数据库中的 SqlDataConnection (LINQ to SQL) 类型提供程序，当您具有数据库中的活动连接。 如果没有与数据库的实时连接，但是具有与 LINQ to SQL 架构文件（DBML 文件），请参见 演练：根据 DBML 文件生成 F# 类型 (F#)。

本演练阐释了以下任务。 这些任务必须执行演示中的顺序至成功：

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

准备测试数据库

在运行 SQL Server 的服务器上，出于测试目的创建一个数据库。 您可以使用 MyDatabase Create Script 部分中的此页底部的创建脚本来执行此操作。

准备测试数据库

  • 若要运行 MyDatabase Create Script，请打开**“视图”菜单，然后选择SQL Server Object Explorer或选择 Ctrl+\, Ctrl+S 键。 在“SQL Server 对象资源管理器”窗口中，打开相应实例的快捷菜单，选择“新建查询”**，复制此页底部的脚本，然后将该脚本粘贴到编辑器。 若要运行 SQL 脚本，请通过用三角形符号来选择任务栏按钮，或选择 Ctrl+Q 键。 有关 SQL Server Object Explorer 的更多信息，请参见 Connected Database Development（已连接的数据库开发）。

创建项目

下一步，创建新 F# 应用程序项目。

创建和设置项目

  1. 创建新 F# 应用程序项目。

  2. 添加对 .FSharp.Data.TypeProviders，以及 System.Data 和 System.Data.Linq 的引用。

  3. 通过将如下代码行添加到您 F# 代码文件 Program.fs 的顶部来打开响应的命名空间。

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

  4. 与大部分 F# 程序一样，可在此演练中以已编译的程序的形式执行代码，或者可将其作为脚本交互执行。 如果希望使用脚本，请打开项目节点的快捷菜单，选择**“添加新项”**，添加 F# 脚本文件并将步骤中的代码添加到该脚本。 您需要在文件的顶部添加以下各行以加载程序集引用。

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

    您可以在添加各代码块时候对其进行选择，并按 Alt+Enter 在 F# Interactive 中运行它。

设置类型提供程序

在此步骤中，为您的数据库架构创建一个类型提供程序。

从一个直接数据库连接中设置类型提供程序

  • 具有使用类型提供程序，需要以创建类型可以使用查询 SQL 数据库代码的两个关键的行。 首先，您实例化类型提供程序。 为完成此操作，请使用静态泛型参数创建 SqlDataConnection 的类型缩写。 SqlDataConnection 为 SQL 类型提供程序，不应与用于 ADO.NET 编程中的 SqlConnection 类型相混淆。 如果具有要连接到的数据库并且具有连接字符串，请使用以下代码调用类型提供程序。 用您自己的连接字符串替换给定的示例字符串。 例如，如果您的服务器是 MYSERVER 和数据库实例是 INSTANCE，并且您想使用 Windows Authentication 访问 MyDatabase，则要使用连接字符串将如以下示例代码而提供。

    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

    现在您具有类型 dbSchema，其是包含表示数据库表的所有已生成类型的父级类型。 还有一个对象（db），其中作为其成员的所有表均在数据库中。 表名是属性，并且，这些属性的类型是由 F# 编译器生成。 类型自身显示作为嵌套类型 dbSchema.ServiceTypes 下。 因此，这些表行的任何数据检索是针对生成表相应类型的实例。 类型的名称是 ServiceTypes.Table1。

    若要熟悉 F# 语言如何将查询解释为 SQL 查询，请检查在数据上下文上设置 Log 属性的行。

    若要进一步浏览类型提供程序创建的类型，请添加以下代码。

    let table1 = db.Table1

    将鼠标悬停在 table1 上可查看其类型。 其类型为 System.Data.Linq.Table<dbSchema.ServiceTypes.Table1>，并且泛型参数意味着每行的类型为生成的类型，dbSchema.ServiceTypes.Table1。 编译器在数据库中为每个表创建一个类似的类型。

查询数据

在此步骤中，使用 F# 查询表达式编写查询。

查询数据。

  1. 此时，在数据库中为该表创建查询。 添加下列代码。

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

    query 这个词的外观指示这是一个查询表达式，生成类似于典型数据库查询的结果集合的计算表达式类型。 如果将鼠标悬停在查询中，您将看到它是 Linq.QueryBuilder 类 (F#) 实例，定义查询计算表达式的类型。 如果将鼠标悬停在 query1 上，您将看到它就是 IQueryable<T> 实例。 顾名思义，IQueryable<T> 表示可查询的数据，而不是查询结果。 查询受到延迟计算的限制，这意味着只有在评估查询时，才需要数据库。 最后一行由 Seq.iter 通过查询。 查询是可枚举的，并且可以像顺序一样迭代。 有关更多信息，请参见查询表达式 (F#)。

  2. 现在，向查询添加某个查询运算符。 可以使用可用的查询运算符的数量来构造更多复杂的查询。 此示例还演示，您可以消除语句中的查询变量和使用管道运算符。此示例还显示了移除查询变量和改用管线运算符

    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. 通过连接两个表添加更复杂的查询。

    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. 在具体代码中，队列中的参数通常是值或变量，而不是编译时常数。 添加在采用参数的函数中包装查询的以下代码，然后调用值为 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)

使用可为 null 的字段

在数据库中，字段通常允许存在空值。 在 .NET 类型系统中，不能为允许 null 的数据使用普通数值数据类型，因为这些类型未将 null 作为可能值。 因此，这些值由 Nullable<T> 类型的实例表示。 请不要直接使用字段的名称访问此类字段的值，而是需要添加一些附加步骤。 可以使用 Value 属性来访问可为 null 类型的基础值。 如果对象为 null 而不是具有值，该 Value 属性会引发异常。 您可以使用 HasValue 布尔值方法来确定值是否存在，或者使用 GetValueOrDefault 来确保在所有情况下是否都有实际值。 如果使用 GetValueOrDefault 并且数据库中存在 null，则其将为值所替换，例如空字符串将替换为字符串类型、0 替换为整型或 0.0 替换为浮点类型。

当您需要在可为 null 值（查询中的 where 子句）上执行相等测试或比较时，可在 Linq.NullableOperators 模块 (F#) 中找到可为 null 的运算符。 这些是类似于常规比较运算符 =，>，<=等，除此之外，显示问号可以为 null 值的运算符的左侧或右侧。 例如，运算符 >? 是大于具有可以为 null 值的运算符右侧。 这些运算符工作的方式是如果表达式的任一边为 null，则该表达式的计算结果为 false。 在 where 子句中，这通一般表示在查询结果中未选择和返回包含空字段的行。

使用可为 null 的字段

  • 下面的代码显示使用可以为 null 的值；假定是 TestData1 是允许 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)

调用存储过程

数据库上的所有存储过程均可以从 F# 调用。 必须在类型提供程序实例化中将静态参数 StoredProcedures 设置为 true。 该类型提供程序 SqlDataConnection 包含可用于配置生成的类型的多个静态方法。 有关这些的完整说明，请参见SqlDataConnection 类型提供程序 (F#)。 该数据上下文类型的方法为每个存储过程生成。

调用存储过程

  • 如果存储过程采用可为空的参数，则您需传递 Nullable<T> 值。 返回标量或表存储过程方法的值为 ISingleResult<T>，包含可以访问返回数据的属性。 ISingleResult<T> 取决于指定过程和类型提供程序生成类型之一的类型参数。 对于名为 Procedure1 的存储过程，该类型为 Procedure1Result。 类型 Procedure1Result 表示返回值，包含在返回表中的列的名称，或返回标量值的存储过程

    下面的代码假定为在 Procedure1 采用两个可以为 null 的整数作为参数的数据库的过程，运行返回名为 TestData1 的列的查询，并返回整数。

    

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)

更新数据库

LINQ DataContext 类型包含在完全类型化方式可以更轻松地执行处理数据库的更新与生成的类型的方法。

更新数据库

  1. 在下面的代码中，若干行将添加到数据库。 如果只添加行，则可以使用 InsertOnSubmit 来指定要添加的新行。 如果要插入多个行，则应将它们放在集合中并调用 InsertAllOnSubmit<TSubEntity>。 当您调用其中任一方法时，立即更改数据库。 必须调用 SubmitChanges 以便实际提交这些更改。 默认情况下，在调用 SubmitChanges 之前执行的所有操作都是同一事务的隐式部分。

    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. 现在，通过调用删除操作对行进行清理。

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

执行 Transact-SQL 代码

还可以直接通过使用 DataContext 类上的 ExecuteCommand 方法来指定 Transact-SQL。

执行自定义 SQL 命令

  • 下面的代码演示如何将 SQL 发送命令插入记录到表中并从表中删除记录。

    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

使用完整数据上下文

在前面的示例中，GetDataContext 方法用于获取“简化数据上下文” 为数据库架构调用的内容。 简化的数据上下文更易于使用当您在构造查询时，因为没有许多可用的成员。 因此，当您浏览 IntelliSense 中的属性时，您可以关注数据库结构，例如表和存储过程。 但是，对简化的数据上下文的用途有限制。 提供执行其他操作的能力的完整数据上下文。 还可用。如果提供了它，则其位于 ServiceTypes 且具有 DataContext 静态参数的名称。 如果未提供它，那么将根据其他输入通过 SqlMetal.exe 为你生成数据上下文类型的名称。 完整的数据上下文继承自 DataContext 并显示其基类的成员，包括对 ADO.NET 数据类型例如 Connection 对象的引用，可以使用来编写查询 SQL 中的 ExecuteCommand 和 ExecuteQuery 方法，并且也是一种使用显式事务的方法。

使用完整数据上下文

  • 下面的代码演示了获取完整的数据上下文对象和使用它直接执行数据库命令。 在这种情况下，两个命令将作为同一事务的一部分而执行。

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

删除数据

这一步演示如何从数据表中删除行。

从数据库中删除行

  • 现在，请通过编写从指定表 和 Table<TEntity> 类的实例删除行的函数来清理所有已添加的行。 然后写入查找要删除的所有行的查询，并用管道查询的结果到 deleteRows 函数中。 此代码采用能够提供函数参数的部分应用程序的优点。

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

创建测试数据库

本节演示如何设置要在本演练中使用的测试数据库。

请注意，如果以某种方式修改数据库，则必须重新设置类型提供程序。 若要重置类型提供程序，请重新生成或清理包含类型提供程序的项目。

创建测试数据库

  1. 在**“服务器资源管理器”中，打开快捷菜单“数据连接”节点，然后选择“添加链接”。 即会出现“添加连接”**对话框。

  2. 在**“服务器名称”框中，指定您具有管理权限的 SQL Server 的示例名称，或者，如果您无法访问服务器，请指定 (localdb \ v11.0)。 SQL express LocalDB 用于开发提供一个轻量数据库服务器和测试的计算机。 新节点在“数据连接”下的“服务器资源管理器”**中创建。 有关 LocalDB 的更多信息，请参见 演练：创建 LocalDB 数据库。

  3. 打开新的连接节点的快捷菜单，然后选择**“新建查询”**。

  5. 复制下面的 SQL 脚本，请将它粘贴到查询编辑器，然后在工具栏上选择**“执行”**按钮或选择 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);

请参见

任务

演练：根据 DBML 文件生成 F# 类型 (F#)

参考

SqlDataConnection 类型提供程序 (F#)

查询表达式 (F#)

SqlMetal.exe（代码生成工具）

其他资源

类型提供程序

LINQ to SQL