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％或分佈式交易，則可能需要檢查一些架構模式，例如傳奇模式。

如何貢獻

非常歡迎錯誤報告，功能請求和拉動請求！

請閱讀貢獻指南以開始。

許可

該項目已在麻省理工學院獲得許可。

有關許可證的更多信息，請參閱許可證文件。