FSharp.Data.SqlClient


Unit-testing

Often there is a need to test business or presentation logic independently of database. This can be archived via Repository pattern.

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
11: 
12: 
13: 
14: 
15: 
16: 
17: 
18: 
19: 
20: 
21: 
22: 
23: 
24: 
25: 
26: 
27: 
28: 
29: 
30: 
31: 
32: 
33: 
34: 
35: 
36: 
37: 
38: 
39: 
40: 
41: 
42: 
43: 
44: 
45: 
46: 
47: 
48: 
49: 
50: 
51: 
52: 
53: 
54: 
55: 
56: 
57: 
58: 
59: 
60: 
61: 
62: 
63: 
64: 
65: 
66: 
67: 
68: 
69: 
70: 
71: 
//Command types definitions
type GetEmployeesByLevel = 
    SqlCommandProvider<"
        SELECT P.FirstName, P.LastName, E.JobTitle
        FROM HumanResources.Employee AS E
	        JOIN Person.Person AS P ON E.BusinessEntityID = P.BusinessEntityID
        WHERE OrganizationLevel = @orgLevel
    ", connectionString>

type GetSalesChampion = SqlCommandProvider<"
    SELECT TOP 1 FirstName, LastName
    FROM Sales.vSalesPerson
    WHERE CountryRegionName = @countryRegionName
    ORDER BY SalesYTD DESC
    " , connectionString, SingleRow = true>

//Repository inteface
type IRepository = 
    abstract GetEmployeesByLevel: int16 -> list<GetEmployeesByLevel.Record>
    abstract GetSalesChampion: country: string -> option<GetSalesChampion.Record>

//Production implementation
type Repository(?connectionString: string) = 
    interface IRepository with 
        member __.GetEmployeesByLevel(orgLevel) = 
            use cmd = new GetEmployeesByLevel()
            cmd.Execute(orgLevel) |> Seq.toList

        member __.GetSalesChampion( region) = 
            use cmd = new GetSalesChampion()
            cmd.Execute(region) 
        
//logic to test
let whoReportsToCEO(db: IRepository) = 
    [ for x in db.GetEmployeesByLevel(1s) -> sprintf "%s %s" x.FirstName x.LastName, x.JobTitle ]

let bestSalesRepInCanada(db: IRepository) = 
    db.GetSalesChampion("Canada")

//unit tests suite
module MyTests = 
    
    //mock the real repo
    let mockRepository = {
        new IRepository with 
            member __.GetEmployeesByLevel(orgLevel) = 
                if orgLevel = 1s 
                then 
                    [ 
                        //Generated record types have single constructor that include all properties
                        //It exists to support unit-testing.
                        GetEmployeesByLevel.Record("David", "Bradley", JobTitle = "Marketing Manager") 
                    ]
                else []

            member __.GetSalesChampion( region) = 
                use cmd = new GetSalesChampion()
                cmd.Execute(region) 
    }

    //unit test
    let ``who reports to CEO``() = 
        let expected = [ "David Bradley", "Marketing Manager" ]
        assert (whoReportsToCEO mockRepository = expected)        
        //replace assert invocation above with invocation to your favorite unit testing framework 
        //fro example Xunit.NET: Assert.Equal<_ list>(expected, actual)

    //unit test
    let ``best sales rep in Canada``() = 
        let expected = Some( GetSalesChampion.Record("Jos", "Saraiva"))
        assert (bestSalesRepInCanada mockRepository = expected)        
type GetEmployeesByLevel = obj

Full name: Unit-testing.GetEmployeesByLevel
type SqlCommandProvider

Full name: FSharp.Data.SqlCommandProvider



<summary>Typed representation of a T-SQL statement to execute against a SQL Server database.</summary>
<param name='CommandText'>Transact-SQL statement to execute at the data source.</param>
<param name='ConnectionStringOrName'>String used to open a SQL Server database or the name of the connection string in the configuration file in the form of “name=&lt;connection string name&gt;”.</param>
<param name='ResultType'>A value that defines structure of result: Records, Tuples, DataTable, or SqlDataReader.</param>
<param name='SingleRow'>If set the query is expected to return a single row of the result set. See MSDN documentation for details on CommandBehavior.SingleRow.</param>
<param name='ConfigFile'>The name of the configuration file that’s used for connection strings at DESIGN-TIME. The default value is app.config or web.config.</param>
<param name='AllParametersOptional'>If set all parameters become optional. NULL input values must be handled inside T-SQL.</param>
<param name='ResolutionFolder'>A folder to be used to resolve relative file paths to *.sql script files at compile time. The default value is the folder that contains the project or script.</param>
<param name='DataDirectory'>The name of the data directory that replaces |DataDirectory| in connection strings. The default value is the project or script directory.</param>
val connectionString : string

Full name: Unit-testing.connectionString
type GetSalesChampion = obj

Full name: Unit-testing.GetSalesChampion
type IRepository =
  interface
    abstract member GetEmployeesByLevel : int16 -> 'a0
    abstract member GetSalesChampion : country:string -> 'a0
  end

Full name: Unit-testing.IRepository
Multiple items
abstract member IRepository.GetEmployeesByLevel : int16 -> 'a0

Full name: Unit-testing.IRepository.GetEmployeesByLevel

--------------------
type GetEmployeesByLevel = obj

Full name: Unit-testing.GetEmployeesByLevel
Multiple items
val int16 : value:'T -> int16 (requires member op_Explicit)

Full name: Microsoft.FSharp.Core.Operators.int16

--------------------
type int16 = Int16

Full name: Microsoft.FSharp.Core.int16

--------------------
type int16<'Measure> = int16

Full name: Microsoft.FSharp.Core.int16<_>
type 'T list = List<'T>

Full name: Microsoft.FSharp.Collections.list<_>
Multiple items
abstract member IRepository.GetSalesChampion : country:string -> 'a0

Full name: Unit-testing.IRepository.GetSalesChampion

--------------------
type GetSalesChampion = obj

Full name: Unit-testing.GetSalesChampion
Multiple items
val string : value:'T -> string

Full name: Microsoft.FSharp.Core.Operators.string

--------------------
type string = String

Full name: Microsoft.FSharp.Core.string
type 'T option = Option<'T>

Full name: Microsoft.FSharp.Core.option<_>
Multiple items
type Repository =
  interface IRepository
  new : ?connectionString:string -> Repository

Full name: Unit-testing.Repository

--------------------
new : ?connectionString:string -> Repository
val connectionString : string option
Multiple items
override Repository.GetEmployeesByLevel : orgLevel:int16 -> 'b

Full name: Unit-testing.Repository.GetEmployeesByLevel

--------------------
type GetEmployeesByLevel = obj

Full name: Unit-testing.GetEmployeesByLevel
val orgLevel : int16
val cmd : GetEmployeesByLevel
module Seq

from Microsoft.FSharp.Collections
val toList : source:seq<'T> -> 'T list

Full name: Microsoft.FSharp.Collections.Seq.toList
val __ : Repository
Multiple items
override Repository.GetSalesChampion : region:string -> 'a

Full name: Unit-testing.Repository.GetSalesChampion

--------------------
type GetSalesChampion = obj

Full name: Unit-testing.GetSalesChampion
val region : string
val cmd : GetSalesChampion
val whoReportsToCEO : db:IRepository -> (string * 'a) list

Full name: Unit-testing.whoReportsToCEO
val db : IRepository
val x : obj
abstract member IRepository.GetEmployeesByLevel : int16 -> 'a0
val sprintf : format:Printf.StringFormat<'T> -> 'T

Full name: Microsoft.FSharp.Core.ExtraTopLevelOperators.sprintf
val bestSalesRepInCanada : db:IRepository -> 'a

Full name: Unit-testing.bestSalesRepInCanada
abstract member IRepository.GetSalesChampion : country:string -> 'a0
module MyTests

from Unit-testing
val mockRepository : IRepository

Full name: Unit-testing.MyTests.mockRepository
val __ : IRepository
val ( who reports to CEO ) : unit -> unit

Full name: Unit-testing.MyTests.( who reports to CEO )
val expected : (string * string) list
val ( best sales rep in Canada ) : unit -> unit

Full name: Unit-testing.MyTests.( best sales rep in Canada )
val expected : obj option
union case Option.Some: Value: 'T -> Option<'T>
Fork me on GitHub