How do I get an F# fsx script to re-execute and re-pull SQL data each time it's called from C#?

Question

I have written a simple C# web app that allows the user to input some data and then upon button click:

1. the data is saved to a local SQL db
2. an F# script is called to retrieve that data using SqlCommandProvider
3. that data is passed back to C#
4. the passed back data is used in some calculations
5. the calc results are displayed onto the screen of the web page

Everything works fine except that when the button is clicked more than once, the same data is sent back from F# from the very first execution.

It appears that the script is not re-executing as would be expected, but if it is, it seems that the SqlCommandProvider might be locked to the first set of results that it initially returned.

It is obviously unnecessary to send the data to the db and back in order to perform these calcs. This app is being built for demonstration purposes of F#/C# usage together in a solution, not actual efficient production usage of the app.

#I "../packages/FSharp.Data.3.0.0/lib/net45"
#r "FSharp.Data.dll"
open FSharp.Data

#I "../packages/FSharp.Data.SqlClient.2.0.1/lib/net40"
#r "FSharp.Data.SqlClient.dll"
open FSharp.Data.SqlClient

[<Literal>]
let ConnectionString = 
    @"server=(local); database=CostPriceCalc; user id=MyId; password=MyPassword;"

[<Literal>]
let SqlQuery = "SELECT SharesSold, PricePerShare, SellDate, CostMethod FROM [CalcInputs] WHERE Id = (SELECT MAX(Id) FROM [CalcInputs])"
let cmd = new SqlCommandProvider<SqlQuery, ConnectionString>(ConnectionString)
let result = cmd.Execute() |> Seq.toArray

Additionally, I am not yet handling the data properly coming back from F#, instead I'm very inelegantly just converting to string and parsing through it to get what I need. That however, seems irrelevant (other than it's massively incorrect) because the C# variables are clear each time before pulling from F# and the same data just keeps coming back each time from F#.

   public static List<NewData.Values> ParseFsharpData()
    {
        var parsedList = new List<NewData.Values>();
        foreach (var item in CalculateCostPrice.result)
        {
            var parsedData = item.ToString().Replace(";", ",").
                Replace("{ ", "").Replace(" }", "").Replace("Some ", "").
                Replace("M,", ",").Replace("\"", "").
                Split(',').ToList();
            parsedList = (from value in parsedData
                select new NewData.Values
                {
                    Value1 = value.Split('=')[0].Trim(),
                    Value2 = value.Split('=')[1].Trim()
                }).ToList();
        }
        return parsedList;
    }

Lastly, I have confirmed that the new data IS being written correctly to the db. The issue seems confined to either the F# fsx script itself (named CalculateCostPrice) not re-executing as expected, OR the SqlCommandProvider caching the data.

Answer 1

I haven't tried to compile .fsx scripts yet, but my experience with using modules in F# projects makes me think that:

let result = cmd.Execute() |> Seq.toArray

compiles to a static variable on the CalculateCostPrice class. This would mean it'll only get executed once (when it's first used, if not earlier), and the result would be stored in the "result" variable.

Adding a parameter of type "unit" should change it to a method (again, not tested yet):

let result() = cmd.Execute() |> Seq.toArray

And you would call it from C# as:

foreach (var item in CalculateCostPrice.result())

In this case, the execution will happen when you call the method, not when the class gets initialized. I might rename it from "result" to "executeQuery" or something along those lines.