FSharp.Data.SqlClient/tests/SqlClient.Tests/TempTableTests.fs at 4dba2a891f83b087b8a17ba4d9c910442ba98dd2 · fsprojects/FSharp.Data.SqlClient · GitHub

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
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
module FSharp.Data.SqlClient.Tests.TempTableTests
open FSharp.Data.SqlClient
open FSharp.Data.SqlClient.Tests

open FSharp.Data
open Xunit
open System.Data.SqlClient

type TempTable =
    SqlCommandProvider<
        TempTableDefinitions = "
            CREATE TABLE #Temp (
                Id INT NOT NULL,
                Name NVARCHAR(100) NULL)",
        CommandText = "
            SELECT Id, Name FROM #Temp",
        ConnectionStringOrName =
            ConnectionStrings.AdventureWorksLiteral>

[<Fact>]
let usingTempTableWithConnectionString() =
    // Regression test for issue #306: LoadTempTables should auto-open the connection
    use cmd = new TempTable(ConnectionStrings.AdventureWorksLiteral)

    cmd.LoadTempTables(
        Temp =
            [ TempTable.Temp(Id = 1, Name = Some "cat")
              TempTable.Temp(Id = 2, Name = Some "dog") ])

    let actual =
        cmd.Execute()
        |> Seq.map(fun x -> x.Id, x.Name)
        |> Seq.toList

    let expected = [
        1, Some "cat"
        2, Some "dog"
    ]

    Assert.Equal<_ list>(expected, actual)

[<Fact>]
let usingTempTable() =
    use conn = new SqlConnection(ConnectionStrings.AdventureWorksLiteral)
    conn.Open()

    use cmd = new TempTable(conn)

    cmd.LoadTempTables(
        Temp =
            [ TempTable.Temp(Id = 1, Name = Some "monkey")
              TempTable.Temp(Id = 2, Name = Some "donkey") ])

    let actual =
        cmd.Execute()
        |> Seq.map(fun x -> x.Id, x.Name)
        |> Seq.toList

    let expected = [
        1, Some "monkey"
        2, Some "donkey"
    ]

    Assert.Equal<_ list>(expected, actual)

[<Fact>]
let queryWithHash() =
    // We shouldn't mangle the statement when it's run
    use cmd =
        new SqlCommandProvider<
            CommandText = "
                SELECT Id, Name
                FROM
                (
                    SELECT 1 AS Id, '#name' AS Name UNION
                    SELECT 2, 'some other value'
                ) AS a
                WHERE Name = '#name'",
            ConnectionStringOrName =
                ConnectionStrings.AdventureWorksLiteral>(ConnectionStrings.AdventureWorksLiteral)

    let actual =
        cmd.Execute()
        |> Seq.map(fun x -> x.Id, x.Name)
        |> Seq.toList

    let expected = [
        1, "#name"
    ]

    Assert.Equal<_ list>(expected, actual)

type TempTableHash =
    SqlCommandProvider<
        TempTableDefinitions = "
            CREATE TABLE #Temp (
                Id INT NOT NULL)",
        CommandText = "
            SELECT a.Id, a.Name
            FROM
            (
                SELECT 1 AS Id, '#Temp' AS Name UNION
                SELECT 2, 'some other value'
            ) AS a
            INNER JOIN #Temp t ON t.Id = a.Id",
        ConnectionStringOrName =
            ConnectionStrings.AdventureWorksLiteral>

[<Fact>]
let queryWithHashAndTempTable() =
    // We shouldn't mangle the statement when it's run
    use conn = new SqlConnection(ConnectionStrings.AdventureWorksLiteral)
    conn.Open()

    use cmd = new TempTableHash(conn)

    cmd.LoadTempTables(
        Temp =
            [ TempTableHash.Temp(Id = 1) ])

    let actual =
        cmd.Execute()
        |> Seq.map(fun x -> x.Id, x.Name)
        |> Seq.toList

    let expected = [
        1, "#Temp"
    ]

    Assert.Equal<_ list>(expected, actual)