Skip to content

DataReaders

Jump to bottom
Jordan Marr edited this page Feb 6, 2023 · 7 revisions

Using the "generate data readers" option will generate a special HydraReader class that will provide strongly typed readers for each table in a given database schema.

Generated data readers work in tandem with SqlHydra.Query, but they can also be used on their own with any query provider that returns an IDataReader. This section will cover using the generated readers on their own.

  • The HydraReader will contain a property for each table in the schema.
  • The generated record for a given table can be loaded in its entirety via the Read method.
  • Each table property in the HydraReader will contain a property for each column in the table to allow reading individual columns.

HydraReader2

Reading Generated Table Records

The following example loads the generated AdventureWorks Customer and Address records using the Read and ReadIfNotNull methods. The getCustomersLeftJoinAddresses function returns a Task<(SalesLT.Customer * SalesLT.Address option) list>.

let getCustomersLeftJoinAddresses(conn: SqlConnection) = task {
    let sql = 
        """
        SELECT TOP 20 * FROM SalesLT.Customer c
        LEFT JOIN SalesLT.CustomerAddress ca ON c.CustomerID = ca.CustomerID
        LEFT JOIN SalesLT.Address a on ca.AddressID = a.AddressID
        ORDER BY c.CustomerID
        """
    use cmd = new SqlCommand(sql, conn)
    use! reader = cmd.ExecuteReaderAsync()
    
    let hydra = SalesLT.HydraReader(reader)

    return [
        while reader.Read() do
            hydra.Customer.Read(), 
            hydra.Address.ReadIfNotNull()
    ]
}

Reading Individual Columns

The next example loads individual columns using the property readers. This is useful for loading your own custom domain entities or for loading a subset of fields. The getProductImages function returns a Task<(string * string * byte[] option) list>.

/// A custom domain entity
type ProductInfo = 
    {
        Product: string
        ProductNumber: string
        ThumbnailFileName: string option
        Thumbnail: byte[] option
    }

let getProductImages(conn: SqlConnection) = task {
    let sql = "SELECT TOP 10 [Name], [ProductNumber] FROM SalesLT.Product p WHERE ThumbNailPhoto IS NOT NULL"
    use cmd = new SqlCommand(sql, conn)
    use! reader = cmd.ExecuteReaderAsync()
    
    let hydra = SalesLT.HydraReader(reader)

    return [ 
        while reader.Read() do
            { 
                ProductInfo.Product = hydra.Product.Name.Read()
                ProductInfo.ProductNumber = hydra.Product.ProductNumber.Read()
                ProductInfo.ThumbnailFileName = hydra.Product.ThumbnailPhotoFileName.Read()
                ProductInfo.Thumbnail = hydra.Product.ThumbNailPhoto.Read()
            }
    ]
}

Automatic Resolution of Column Naming Conflicts

When joining tables that have the same column name, the generated HydraReader will automatically resolve the conflicts with the assumption that you read tables in the same order that they are joined.

let getProductsAndCategories(conn: SqlConnection) = task {
    let sql = 
        """
        SELECT p.Name, c.Name
        FROM SalesLT.Product p
        LEFT JOIN SalesLT.ProductCategory c ON p.ProductCategoryID = c.ProductCategoryID
        """
    use cmd = new SqlCommand(sql, conn)
    use! reader = cmd.ExecuteReaderAsync()
    
    let hydra = SalesLT.HydraReader(reader)

    return [
        while reader.Read() do
            hydra.Product.Name.Read(), 
            hydra.ProductCategory.Name.Read()
    ]
}

Overriding the Data Reader Type

If you want to use a different ADO.NET provider, you can override the generated IDataReader by specifying an optional fully qualified IDataReader type. (The wizard will prompt you for this if you choose to not accept the default.)

Recommended Data Library?

  • SqlHydra.Query is made to complement SqlHydra.* generated types and data readers.
  • Or you can use any ADO.NET library that returns an IDataReader with the SqlHydra generated readers.*
  • If you like to meticulously craft your SQL by hand, then Donald with the SqlHydra generated HydraReader pairs very well together.
  • If you want to use only the generated types, then Dapper.FSharp is a great fit since Dapper uses reflection out of the box to transform IDataReader query results into your generated entity records.

SqlHydra.Query

Examples of using SqlHydra generated records and data readers with SqlHydra.Query.

ADO.NET

Examples of using SqlHydra generated types with ADO.NET.

Donald

Examples of using SqlHydra generated types with Donald.

Dapper.FSharp

Examples of using SqlHydra generated types with Dapper.FSharp.

Dapper

Examples of using SqlHydra generated types with Dapper.

After creating SqlHydra, I was trying to find the perfect ORM to complement SqlHyda's generated records. Ideally, I wanted to find a library with

  • First-class support for F# records, option types, etc.
  • LINQ queries (to take advantage of strongly typed SqlHydra generated records)

FSharp.Dapper met the first critera with flying colors. As the name suggests, Dapper.FSharp was written specifically for F# with simplicity and ease-of-use as the driving design priorities. FSharp.Dapper features custom F# Computation Expressions for selecting, inserting, updating and deleting, and support for F# Option types and records.

If only it had Linq queries, it would be the perfect complement to SqlHydra...

So I submitted a PR to Dapper.FSharp that adds Linq query expressions (now in v2.0+)!

Between the two, you can have strongly typed access to your database:

module SampleApp.DapperFSharpExample
open System.Data
open Microsoft.Data.SqlClient
open Dapper.FSharp.MSSQL
open SampleApp.AdventureWorks // Generated Types

Dapper.FSharp.OptionTypes.register()
    
// Tables
let customerTable =         table<Customer>         |> inSchema (nameof SalesLT)
let customerAddressTable =  table<CustomerAddress>  |> inSchema (nameof SalesLT)
let addressTable =          table<SalesLT.Address>  |> inSchema (nameof SalesLT)

let getAddressesForCity(conn: IDbConnection) (city: string) = 
    select {
        for a in addressTable do
        where (a.City = city)
    } 
    |> conn.SelectAsync<SalesLT.Address>
    
let getCustomersWithAddresses(conn: IDbConnection) =
    select {
        for c in customerTable do
        leftJoin ca in customerAddressTable on (c.CustomerID = ca.CustomerID)
        leftJoin a  in addressTable on (ca.AddressID = a.AddressID)
        where (isIn c.CustomerID [30018;29545;29954;29897;29503;29559])
        orderBy c.CustomerID
    } 
    |> conn.SelectAsyncOption<Customer, CustomerAddress, Address>
Clone this wiki locally