Vp.FSharp.Sql

Vp.FSharp.Sql

使您能够与F＃和任何ADO提供商一起工作的核心库。

在大多数情况下，该库仅用于创建其他F＃库利用相关的ADO提供商。

如果您只想执行SQL命令A-LA-F＃，则可能需要查看此部分。

倾斜的炒作

我们遵循“高度争议的实践”，尽我们所能！

地位	包裹
好的
好（orda）
TBD
TBD

Nuget软件包

姓名	版本	命令
Vp.FSharp.Sql		Install-Package Vp.FSharp.Sql

如何使用此库？

该图书馆主要旨在成为与相关的Ado.net提供商建立其他图书馆的基础，以提供强烈的体验。

您可以查看下面的库，每个利用Vp.FSharp.Sql和相关的ado.net提供商：

姓名	ADO.NET提供商	版本	命令
Vp.FSharp.Sql.Sqlite	System.Data.SQLite.Core		Install-Package Vp.FSharp.Sql.Sqlite
Vp.FSharp.Sql.SqlServer	Microsoft.Data.SqlClient		Install-Package Vp.FSharp.Sql.SqlServer
Vp.FSharp.Sql.PostgreSql	Npgsql		Install-Package Vp.FSharp.Sql.PostgreSql

简而言之，您可以创建自己的完整提供商，但是您只需使用所需的东西即可。

让我们演练Vp.FSharp.Sql.Sqlite提供商实现。

数据库值类型

首先，您需要最重要的类型，即数据库值类型。

就SQLite而言， SqliteDbValue可以建模为简单的歧视联盟（DU）：

 /// Native SQLite DB types.
/// See https://www.sqlite.org/datatype3.html
type SqliteDbValue =
    | Null
    | Integer of int64
    | Real of double
    | Text of string
    | Blob of byte array

^{摘要源|锚}

这些案例是在官方SQLite文档后创建的。

DB值转换为DB参数

在这里，我们将公共API中暴露的DU转换为可以从核心库函数中消费的实际DbParameter类别。

在大多数情况下，实现包括在不同的数据库值类型案例上编写模式匹配，并创建ADO.NET提供商中可用的相关DbParameter特定类型，如果有的话：

 let dbValueToParameter name value =
    let parameter = SQLiteParameter ()
    parameter.ParameterName <- name
    match value with
    | Null ->
        parameter.TypeName <- ( nameof Null ) .ToUpperInvariant ()
    | Integer value ->
        parameter.TypeName <- ( nameof Integer ) .ToUpperInvariant ()
        parameter.Value <- value
    | Real value ->
        parameter.TypeName <- ( nameof Real ) .ToUpperInvariant ()
        parameter.Value <- value
    | Text value ->
        parameter.TypeName <- ( nameof Text ) .ToUpperInvariant ()
        parameter.Value <- value
    | Blob value ->
        parameter.TypeName <- ( nameof Blob ) .ToUpperInvariant ()
        parameter.Value <- value
    parameter

注意：此功能不必公开，只有DU必须公开。

绑定依赖性：类型和功能

SqlDependencies作用就像粘附在所有最重要的基础特定特定操作的胶水中：

 /// SQLite Dependencies
type SqliteDependencies =
    SqlDependencies <
        SQLiteConnection ,
        SQLiteCommand ,
        SQLiteParameter ,
        SQLiteDataReader ,
        SQLiteTransaction ,
        SqliteDbValue >

这种类型的实例可以以：

 let beginTransactionAsync ( connection : SQLiteConnection ) ( isolationLevel : IsolationLevel ) _ =
    ValueTask.FromResult ( connection.BeginTransaction ( isolationLevel ))

let executeReaderAsync ( command : SQLiteCommand ) _ =
    Task.FromResult ( command.ExecuteReader ())

{ CreateCommand = fun connection -> connection.CreateCommand ()
  SetCommandTransaction = fun command transaction -> command.Transaction <- transaction
  BeginTransaction = fun connection -> connection.BeginTransaction
  BeginTransactionAsync = beginTransactionAsync
  ExecuteReader = fun command -> command.ExecuteReader ()
  ExecuteReaderAsync = executeReaderAsync
  DbValueToParameter = Constants.DbValueToParameter }

在这种特殊情况下， System.Data.SQLite ，最特定的类型只能通过非同步API获得。

例如，我们使用command.ExecuteReader而不是command.ExecuteDbDataReader ，因为返回类型是最特定的类型：

• SQLiteCommand.ExecuteDbDataReader()
• SQLiteCommand.ExecuteReader()

同样，您可能已经注意到，异步API没有出现，这意味着异步“实现”（或缺乏其）依赖于基类实施：

• DbCommand.ExecuteReaderAsync()
• DbCommand.ExecuteDbDataReader(CommandBehavior behavior)

这只是同步版本周围的异步包装器。

同样，当涉及connection.BeginTransaction时。BeginTransaction而不是command.BeginTransactionAsync ：

• SQLiteConnection.BeginTransaction()
• DbConnection.BeginTransactionAsync()

仅此示例就显示了您可以期望在最可用的ADO.NET提供商实现中找到的差异。

⌨命令定义

为了简单起见，您可以用相关的ADO提供商类型来约束CommandDefinition类型，作为某种类型的粘合剂：

 /// SQLite Command Definition
type SqliteCommandDefinition =
    CommandDefinition <
        SQLiteConnection ,
        SQLiteCommand ,
        SQLiteParameter ,
        SQLiteDataReader ,
        SQLiteTransaction ,
        SqliteDbValue >

以后可以与SqlCommand函数一起使用，该功能接受CommandDefinition作为其参数之一。

配置

在通用约束方面还有另一个专业化：

 /// SQLite Configuration
type SqliteConfiguration =
    SqlConfigurationCache <
        SQLiteConnection ,
        SQLiteCommand >

这种类型是类型的另一种粘合剂，并充当缓存。执行命令时，它将与命令定义一起传递。

指挥构建

这很简单，您需要做的就是：

• 创建一个新的模块（如果需要）。
• 定义与您的库相关的构造功能，并将命令定义传递给SqlCommand Core函数。

 [<RequireQualifiedAccess>]
module Vp.FSharp.Sql.Sqlite.SqliteCommand

open Vp. FSharp . Sql


/// Initialize a new command definition with the given text contained in the given string.
let text value : SqliteCommandDefinition =
    SqlCommand.text value

/// Initialize a new command definition with the given text spanning over several strings (ie. list).
let textFromList value : SqliteCommandDefinition =
    SqlCommand.textFromList value

/// Update the command definition so that when executing the command, it doesn't use any logger.
/// Be it the default one (Global, if any.) or a previously overriden one.
let noLogger commandDefinition = { commandDefinition with Logger = LoggerKind.Nothing }

/// Update the command definition so that when executing the command, it use the given overriding logger.
/// instead of the default one, aka the Global logger, if any.
let overrideLogger value commandDefinition = { commandDefinition with Logger = LoggerKind.Override value }

/// Update the command definition with the given parameters.
let parameters value ( commandDefinition : SqliteCommandDefinition ) : SqliteCommandDefinition =
    SqlCommand.parameters value commandDefinition

/// Update the command definition with the given cancellation token.
let cancellationToken value ( commandDefinition : SqliteCommandDefinition ) : SqliteCommandDefinition =
    SqlCommand.cancellationToken value commandDefinition

/// Update the command definition with the given timeout.
/// Note: kludged because SQLite doesn't support per-command timeout values.
let timeout value ( commandDefinition : SqliteCommandDefinition ) : SqliteCommandDefinition =
    SqlCommand.timeout value commandDefinition

/// Update the command definition and sets whether the command should be prepared or not.
let prepare value ( commandDefinition : SqliteCommandDefinition ) : SqliteCommandDefinition =
    SqlCommand.prepare value commandDefinition

/// Update the command definition and sets whether the command should be wrapped in the given transaction.
let transaction value ( commandDefinition : SqliteCommandDefinition ) : SqliteCommandDefinition =
    SqlCommand.transaction value commandDefinition

命令执行

同样，命令执行遵循相同的原则，也就是将相关强大的参数（对应于您的当前和特定ADO.NET提供程序）传递给SQLCOMMAND CORE函数。

 /// Execute the command and return the sets of rows as an AsyncSeq accordingly to the command definition.
/// This function runs asynchronously.
let queryAsyncSeq connection read ( commandDefinition : SqliteCommandDefinition ) =
    SqlCommand.queryAsyncSeq
        connection ( Constants.Deps ) ( SqliteConfiguration.Snapshot ) read commandDefinition

/// Execute the command and return the sets of rows as an AsyncSeq accordingly to the command definition.
/// This function runs synchronously.
let querySeqSync connection read ( commandDefinition : SqliteCommandDefinition ) =
    SqlCommand.querySeqSync
        connection ( Constants.Deps ) ( SqliteConfiguration.Snapshot ) read commandDefinition

/// Execute the command and return the sets of rows as a list accordingly to the command definition.
/// This function runs asynchronously.
let queryList connection read ( commandDefinition : SqliteCommandDefinition ) =
    SqlCommand.queryList
        connection ( Constants.Deps ) ( SqliteConfiguration.Snapshot ) read commandDefinition

/// Execute the command and return the sets of rows as a list accordingly to the command definition.
/// This function runs synchronously.
let queryListSync connection read ( commandDefinition : SqliteCommandDefinition ) =
    SqlCommand.queryListSync
        connection ( Constants.Deps ) ( SqliteConfiguration.Snapshot ) read commandDefinition

/// Execute the command and return the first set of rows as a list accordingly to the command definition.
/// This function runs asynchronously.
let querySetList connection read ( commandDefinition : SqliteCommandDefinition ) =
    SqlCommand.querySetList
        connection ( Constants.Deps ) ( SqliteConfiguration.Snapshot ) read commandDefinition

/// Execute the command and return the first set of rows as a list accordingly to the command definition.
/// This function runs synchronously.
let querySetListSync connection read ( commandDefinition : SqliteCommandDefinition ) =
    SqlCommand.querySetListSync
        connection ( Constants.Deps ) ( SqliteConfiguration.Snapshot ) read commandDefinition

/// Execute the command and return the 2 first sets of rows as a tuple of 2 lists accordingly to the command definition.
/// This function runs asynchronously.
let querySetList2 connection read1 read2 ( commandDefinition : SqliteCommandDefinition ) =
    SqlCommand.querySetList2
        connection ( Constants.Deps ) ( SqliteConfiguration.Snapshot ) read1 read2 commandDefinition

/// Execute the command and return the 2 first sets of rows as a tuple of 2 lists accordingly to the command definition.
/// This function runs synchronously.
let querySetList2Sync connection read1 read2 ( commandDefinition : SqliteCommandDefinition ) =
    SqlCommand.querySetList2Sync
        connection ( Constants.Deps ) ( SqliteConfiguration.Snapshot ) read1 read2 commandDefinition

/// Execute the command and return the 3 first sets of rows as a tuple of 3 lists accordingly to the command definition.
/// This function runs asynchronously.
let querySetList3 connection read1 read2 read3 ( commandDefinition : SqliteCommandDefinition ) =
    SqlCommand.querySetList3
        connection ( Constants.Deps ) ( SqliteConfiguration.Snapshot ) read1 read2 read3 commandDefinition

/// Execute the command and return the 3 first sets of rows as a tuple of 3 lists accordingly to the command definition.
/// This function runs synchronously.
let querySetList3Sync  connection read1 read2 read3 ( commandDefinition : SqliteCommandDefinition ) =
    SqlCommand.querySetList3Sync
        connection ( Constants.Deps ) ( SqliteConfiguration.Snapshot ) read1 read2 read3 commandDefinition

/// Execute the command accordingly to its definition and,
/// - return the first cell value, if it is available and of the given type.
/// - throw an exception, otherwise.
/// This function runs asynchronously.
let executeScalar < 'Scalar > connection ( commandDefinition : SqliteCommandDefinition ) =
    SqlCommand.executeScalar < 'Scalar , _, _, _, _, _, _, _, _>
        connection ( Constants.Deps ) ( SqliteConfiguration.Snapshot ) commandDefinition

/// Execute the command accordingly to its definition and,
/// - return the first cell value, if it is available and of the given type.
/// - throw an exception, otherwise.
/// This function runs synchronously.
let executeScalarSync < 'Scalar > connection ( commandDefinition : SqliteCommandDefinition ) =
    SqlCommand.executeScalarSync < 'Scalar , _, _, _, _, _, _, _, _>
        connection ( Constants.Deps ) ( SqliteConfiguration.Snapshot ) commandDefinition

/// Execute the command accordingly to its definition and,
/// - return Some, if the first cell is available and of the given type.
/// - return None, if first cell is DBNull.
/// - throw an exception, otherwise.
/// This function runs asynchronously.
let executeScalarOrNone < 'Scalar > connection ( commandDefinition : SqliteCommandDefinition ) =
    SqlCommand.executeScalarOrNone < 'Scalar , _, _, _, _, _, _, _, _>
        connection ( Constants.Deps ) ( SqliteConfiguration.Snapshot ) commandDefinition

/// Execute the command accordingly to its definition and,
/// - return Some, if the first cell is available and of the given type.
/// - return None, if first cell is DBNull.
/// - throw an exception, otherwise.
/// This function runs synchronously.
let executeScalarOrNoneSync < 'Scalar > connection ( commandDefinition : SqliteCommandDefinition ) =
    SqlCommand.executeScalarOrNoneSync < 'Scalar , _, _, _, _, _, _, _, _>
        connection ( Constants.Deps ) ( SqliteConfiguration.Snapshot ) commandDefinition

/// Execute the command accordingly to its definition and, return the number of rows affected.
/// This function runs asynchronously.
let executeNonQuery connection ( commandDefinition : SqliteCommandDefinition ) =
    SqlCommand.executeNonQuery
        connection ( Constants.Deps ) ( SqliteConfiguration.Snapshot ) commandDefinition

/// Execute the command accordingly to its definition and, return the number of rows affected.
/// This function runs synchronously.
let executeNonQuerySync connection ( commandDefinition : SqliteCommandDefinition ) =
    SqlCommand.executeNonQuerySync
        connection ( Constants.Deps ) ( SqliteConfiguration.Snapshot ) commandDefinition

无效的助手

我们可以为无效的助手创建另一个模块，其余的就是将相关参数传递给基础核心函数。

 [<RequireQualifiedAccess>]
module Vp.FSharp.Sql.Sqlite.SqliteNullDbValue

open Vp. FSharp . Sql


/// Return SQLite DB Null value if the given option is None, otherwise the underlying wrapped in Some.
let ifNone toDbValue = NullDbValue.ifNone toDbValue SqliteDbValue.Null

/// Return SQLite DB Null value if the option is Error, otherwise the underlying wrapped in Ok.
let ifError toDbValue = NullDbValue.ifError toDbValue ( fun _ -> SqliteDbValue.Null ) 

交易帮助者

这里也有更多。

 [<RequireQualifiedAccess>]
module Vp.FSharp.Sql.Sqlite.SqliteTransaction

open Vp. FSharp . Sql
open Vp. FSharp . Sql . Sqlite


let private beginTransactionAsync = Constants.Deps.BeginTransactionAsync
let private beginTransaction = Constants.Deps.BeginTransaction

/// Create and commit an automatically generated transaction with the given connection, isolation,
/// cancellation token and transaction body.
/// This function runs asynchronously.
let commit cancellationToken isolationLevel connection body =
    Transaction.commit cancellationToken isolationLevel connection beginTransactionAsync body

/// Create and commit an automatically generated transaction with the given connection, isolation,
/// and transaction body.
/// This function runs synchronously.
let commitSync isolationLevel connection body =
    Transaction.commitSync isolationLevel connection beginTransaction body

/// Create and do not commit an automatically generated transaction with the given connection, isolation,
/// cancellation token and transaction body.
/// This function runs asynchronously.
let notCommit cancellationToken isolationLevel connection body =
    Transaction.notCommit cancellationToken isolationLevel connection beginTransactionAsync body

/// Create and do not commit an automatically generated transaction with the given connection, isolation,
/// and transaction body.
/// This function runs synchronously.
let notCommitSync isolationLevel connection body =
    Transaction.notCommitSync isolationLevel connection beginTransaction body

/// Create and commit an automatically generated transaction with the given connection, isolation,
/// cancellation token and transaction body.
/// The commit phase only occurs if the transaction body returns Some.
/// This function runs asynchronously.
let commitOnSome cancellationToken isolationLevel connection body =
    Transaction.commitOnSome cancellationToken isolationLevel connection beginTransactionAsync body

/// Create and commit an automatically generated transaction with the given connection, isolation,
/// and transaction body.
/// The commit phase only occurs if the transaction body returns Some.
/// This function runs synchronously.
let commitOnSomeSync isolationLevel connection body =
    Transaction.commitOnSomeSync isolationLevel connection beginTransaction body

/// Create and commit an automatically generated transaction with the given connection, isolation,
/// cancellation token and transaction body.
/// The commit phase only occurs if the transaction body returns Ok.
/// This function runs asynchronously.
let commitOnOk cancellationToken isolationLevel connection body =
    Transaction.commitOnOk cancellationToken isolationLevel connection beginTransactionAsync body

/// Create and commit an automatically generated transaction with the given connection, isolation,
/// and transaction body.
/// The commit phase only occurs if the transaction body returns Ok.
/// This function runs synchronously.
let commitOnOkSync isolationLevel connection body =
    Transaction.commitOnOkSync isolationLevel connection beginTransaction body

/// Create and commit an automatically generated transaction with the given connection and transaction body.
/// This function runs asynchronously.
let defaultCommit connection body = Transaction.defaultCommit connection beginTransactionAsync body

/// Create and commit an automatically generated transaction with the given connection and transaction body.
/// This function runs synchronously.
let defaultCommitSync connection body = Transaction.defaultCommitSync connection beginTransaction body

/// Create and do not commit an automatically generated transaction with the given connection and transaction body.
/// This function runs asynchronously.
let defaultNotCommit connection body = Transaction.defaultNotCommit connection beginTransactionAsync body

/// Create and do not commit an automatically generated transaction with the given connection and transaction body.
/// This function runs synchronously.
let defaultNotCommitSync connection body = Transaction.defaultNotCommitSync connection beginTransaction body

/// Create and commit an automatically generated transaction with the given connection and transaction body.
/// The commit phase only occurs if the transaction body returns Ok.
/// This function runs asynchronously.
let defaultCommitOnSome connection body = Transaction.defaultCommitOnSome connection beginTransactionAsync body

/// Create and commit an automatically generated transaction with the given connection and transaction body.
/// The commit phase only occurs if the transaction body returns Ok.
/// This function runs synchronously.
let defaultCommitOnSomeSync connection body = Transaction.defaultCommitOnSomeSync connection beginTransaction body

/// Create and commit an automatically generated transaction with the given connection and transaction body.
/// The commit phase only occurs if the transaction body returns Some.
/// This function runs asynchronously.
let defaultCommitOnOk connection body = Transaction.defaultCommitOnOk connection beginTransactionAsync body

/// Create and commit an automatically generated transaction with the given connection and transaction body.
/// The commit phase only occurs if the transaction body returns Some.
/// This function runs synchronously.
let defaultCommitOnOkSync connection body = Transaction.defaultCommitOnOkSync connection beginTransaction body

瞧！您现在都解决了，准备在您喜欢的数据库上执行最疯狂的命令！

TransactionScope助手

这些帮助者都可以使用，无论您使用的是ADO.NET提供商，只要它支持TransactionScope即可。

话虽如此，我们强烈阻止您使用这些帮助者：

• 请记住，自.NET核心时代以来，对分布式交易的支持尚不可用。
• 使用TransactionScope （有或没有这些帮助者）非常容易出错，您可能会遇到意外的行为而没有明显的错误消息。
• 考虑到此支持几乎没有发展，因此在不支持分布式交易的情况下使用TransactionScope应用程序有限，这些帮助者可能会移至单独的库（即库库 + Nuget软件包）。

如果您需要可行的可行解决方法到2％或分布式交易，则可能需要检查一些架构模式，例如传奇模式。

如何贡献

非常欢迎错误报告，功能请求和拉动请求！

请阅读贡献指南以开始。

许可

该项目已在麻省理工学院获得许可。

有关许可证的更多信息，请参阅许可证文件。