fsprojects / fsprojects/FSharp.Data.SqlClient

Add streaming support

Open
#382 2 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

discussion improvement
Dominant language
F#
Stars
206
Forks
66
PR merge metrics
No merged PRs in 30d

Description

System.Data.SqlClient (and Microsoft.Data.SqlClient) supports streaming binary data.

For example, here is how data can be streamed to the DB (untested):

let insertFile (connStr: string) (filename: string) (stream: Stream) : Async<unit> =
  async {
    let! ct = Async.CancellationToken
    use conn = new SqlConnection(connStr)
    do! conn.OpenAsync (ct) |> Async.AwaitTask
    use cmd =
      new SqlCommand(
        "INSERT INTO [File]
           ([Filename], [CreatedAt], [Data])
         VALUES
           (@filename, @createdAt, @data)",
        conn)
    cmd.Parameters.AddWithValue("@filename", filename) |> ignore
    cmd.Parameters.AddWithValue("@createdAt", DateTimeOffset.Now) |> ignore
    cmd.Parameters.Add("@data", SqlDbType.Binary, -1).Value <- stream
    do! cmd.ExecuteNonQueryAsync(ct) |> Async.AwaitTask |> Async.Ignore<int>
  }

Also, here's how to stream data from the DB (untested):

let getData (connStr: string) (fileId: int) : Async<Stream option> =
  async {
    let! ct = Async.CancellationToken
    use conn = new SqlConnection(connStr)
    do! conn.OpenAsync (ct) |> Async.AwaitTask
    use cmd = new SqlCommand("SELECT [Data] FROM [File] WHERE FileId = @fileId", conn)
    cmd.Parameters.AddWithValue("@fileId", fileId) |> ignore
    use! reader = 
      cmd.ExecuteReaderAsync(CommandBehavior.SequentialAccess, ct)
      |> Async.AwaitTask
    match! reader.ReadAsync(ct) |> Async.AwaitTask with
    | false -> return None
    | true ->
        match! reader.IsDBNullAsync(0, ct) |> Async.AwaitTask with
        | true -> return None
        | false -> return reader.GetStream(0) |> Some
  }

Would it be possible to add support for this to SqlClient? (Note, IMHO this is a "nice to have" feature that should be prioritized below e.g. #348).

I realize that reading is partly possible if using the DataReader option, though one must still check for nulls and access stuff by column index (like I have done above) or string name, so it would be nice to have that strongly typed, too.

Contributor guide

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Start with SqlClient's existing DataReader option and the System.Data.SqlClient/Microsoft.Data.SqlClient streaming documentation linked in the issue. Define the typed API needed for streaming binary data into and out of the database, including null handling and named or strongly typed columns; done means both directions are supported and covered by tests.

Written by the indexing model from the issue text.

Assessment

Tech stack
fsharp, sql
Domain
database
Issue type
Feature
Difficulty
5/5
Estimated time
Over a week
Activity status
Stale
Clarity
Needs clarification
Newbie friendliness
25/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.