- Notifications
You must be signed in to change notification settings - Fork20
A lightweight, generic F# database abstraction.
License
pimbrouwers/Donald
Folders and files
| Name | Name | Last commit message | Last commit date | |
|---|---|---|---|---|
Repository files navigation
MeetDonald (Chamberlin).
If you're a programmer and have used a database, he's impacted your life in a big way.
This library is named after him.
Honorable mention goes to@dsyme another important Donald and F#'sBDFL.
Donald is a generic library that aims to make working withADO.NET safer and more succinct. It is an entirely generic abstraction, and will work with all ADO.NET implementations.
- Support all ADO implementations
- Provide a succinct, type-safe API for interacting with databases
- Enable asynchronuos workflows
- Make object mapping easier
- Improve data access performance
- Provide additional context during exceptions
Install theDonald NuGet package:
PM> Install-Package DonaldOr using the dotnet CLI
dotnet add package Donald
openDonaldtypeAuthor={ AuthorId:int FullName:string}letauthors(conn:IDbConnection):Author list= conn|> Db.newCommand" SELECT author_id , full_name FROM author WHERE book_count >= @book_count"|> Db.setParams["book_count", SqlType.Int323]|> Db.query(fun rd->{ AuthorId= rd.ReadInt"author_id" FullName= rd.ReadString"full_name"})
For this example, assume we have anIDbConnection namedconn:
Reminder: Donald will work withany ADO implementation (SQL Server, SQLite, MySQL, Postgresql etc.).
Consider the following model:
typeAuthor={ AuthorId:int FullName:string}moduleAuthor-letofDataReader(rd:IDataReader):Author={ AuthorId= rd.ReadInt32"author_id" FullName= rd.ReadString"full_name"}
Important: Donald is set to use
CommandBehavior.SequentialAccessby default. Seeperformance for more information.
conn|> Db.newCommand"SELECT author_id, full_name FROM author"|> Db.query Author.ofDataReader// Author list// Asyncconn|> Db.newCommand"SELECT author_id, full_name FROM author"|> Db.Async.query Author.ofDataReader// Task<Author list>
conn|> Db.newCommand"SELECT author_id, full_name FROM author"|> Db.setParams["author_id", SqlType.Int321]|> Db.querySingle Author.ofDataReader// Author option// Asyncconn|> Db.newCommand"SELECT author_id, full_name FROM author"|> Db.setParams["author_id", SqlType.Int321]|> Db.Async.querySingle Author.ofDataReader// Task<Author option>
conn|> Db.newCommand"INSERT INTO author (full_name) VALUES (@full_name)"|> Db.setParams["full_name", SqlType.String"John Doe"]|> Db.exec// unit// Asyncconn|> Db.newCommand"INSERT INTO author (full_name) VALUES (@full_name)"|> Db.setParams["full_name", SqlType.String"John Doe"]|> Db.Async.exec// Task<unit>
conn|> Db.newCommand"INSERT INTO author (full_name) VALUES (@full_name)"|> Db.execMany["full_name", SqlType.String"John Doe""full_name", SqlType.String"Jane Doe"]// unit// Asyncconn|> Db.newCommand"INSERT INTO author (full_name) VALUES (@full_name)"|> Db.Async.execMany["full_name", SqlType.String"John Doe""full_name", SqlType.String"Jane Doe"]//Task<unit>
This can be accomplished in two ways:
- Using
Db.batchorDb.Async.batchwhich processes the action in anall-or-none fashion.
conn|> Db.batch(fun tran->for fullNamein["John Doe";"Jane Doe"]do tran|> Db.newCommandForTransaction"INSERT INTO author (full_name) VALUES (@full_name)"|> Db.setParams["full_name", SqlType.String fullName]|> Db.exec)
- Using the extension methods:
TryBeginTransaction(),TryCommit()andTryRollback().
// Safely begin transaction or throw CouldNotBeginTransactionError on failureuse tran= conn.TryBeginTransaction()for fullNamein["John Doe";"Jane Doe"]do tran|> Db.newCommandForTransaction"INSERT INTO author (full_name) VALUES (@full_name)"|> Db.setParams["full_name", SqlType.String fullName]|> Db.exec// Attempt to commit, will rollback automatically on failure, or throw DbTransactionExceptiontran.TryCommit()// Will rollback or throw DbTransactionException// tran.TryRollback ()
Command parameters are represented bySqlType which contains a case for all relevant types.
typeSqlType=| Null| Stringofstring| AnsiStringofstring| Booleanofbool| Byteofbyte| Charofchar| AnsiCharofchar| Decimalofdecimal| Doubleofdouble| Floatoffloat| GuidofGuid| Int16ofint16| Int32ofint32| Intofint32| Int64ofint64| DateTimeofDateTime| Bytesofbyte[]letp1:SqlType= SqlType.Nullletp2:SqlType= SqlType.Int321
Helpers also exist which implicitly call the respective F# conversion function. Which can be especially useful when you are working with value types in your program.
letp1:SqlType= sqlInt32"1"// equivalent to SqlType.Int32 (int "1")
To make obtaining values from reader more straight-forward, 2 sets of extension methods are available for:
- Get value, automatically defaulted
- Get value as
option<'a>
Assuming we have an activeIDataReader calledrd and are currently reading a row, the following extension methods are available to simplify reading values:
rd.ReadString"some_field"// string -> stringrd.ReadBoolean"some_field"// string -> boolrd.ReadByte"some_field"// string -> byterd.ReadChar"some_field"// string -> charrd.ReadDateTime"some_field"// string -> DateTimerd.ReadDecimal"some_field"// string -> Decimalrd.ReadDouble"some_field"// string -> Doublerd.ReadFloat"some_field"// string -> float32rd.ReadGuid"some_field"// string -> Guidrd.ReadInt16"some_field"// string -> int16rd.ReadInt32"some_field"// string -> int32rd.ReadInt64"some_field"// string -> int64rd.ReadBytes"some_field"// string -> byte[]rd.ReadStringOption"some_field"// string -> string optionrd.ReadBooleanOption"some_field"// string -> bool optionrd.ReadByteOption"some_field"// string -> byte optionrd.ReadCharOption"some_field"// string -> char optionrd.ReadDateTimeOption"some_field"// string -> DateTime optionrd.ReadDecimalOption"some_field"// string -> Decimal optionrd.ReadDoubleOption"some_field"// string -> Double optionrd.ReadFloatOption"some_field"// string -> float32 optionrd.ReadGuidOption"some_field"// string -> Guid optionrd.ReadInt16Option"some_field"// string -> int16 optionrd.ReadInt32Option"some_field"// string -> int32 optionrd.ReadInt64Option"some_field"// string -> int64 optionrd.ReadBytesOption"some_field"// string -> byte[] option
If you need an explicit
Nullable<'a>you can useOption.asNullable.
Several custom exceptions exist which interleave the exceptions thrown by ADO.NET with contextually relevant metadata.
/// Details of failure to connection to a database/server.typeDbConnectionException=inherit ExceptionvalConnectionString:string option/// Details of failure to execute database command or transaction.typeDbExecutionException=inherit ExceptionvalStatement:string optionvalStep:DbTransactionStep option/// Details of failure to access and/or cast an IDataRecord field.typeDbReaderException=inherit ExceptionvalFieldName:string option/// Details of failure to commit or rollback an IDbTransactiontypeDbTransactionException=inherit ExceptionvalStep:DbTransactionStep
By default, theIDataReader is consumed usingCommandBehavior.SequentialAccess. This allows the rows and columns to be read in chunks (i.e., streamed), but forward-only. As opposed to being completely read into memory all at once, and readable in any direction. The benefits of this are particular felt when reading large CLOB (string) and BLOB (binary) data. But is also a measureable performance gain for standard query results as well.
The only nuance to sequential access is thatcolumns must be read in the same order found in theSELECT clause. Aside from that, there is no noticeable difference from the perspective of a library consumer.
ConfiguringCommandBehavior can be done two ways:
letsql="SELECT author_id, full_name FROM author"conn|> Db.newCommand sql|> Db.setCommandBehavior CommandBehavior.Default|> Db.query Author.ofDataReader
There's anissue for that.
Built with ♥ byPim Brouwers in Toronto, ON. Licensed underApache License 2.0.
About
A lightweight, generic F# database abstraction.