Add streaming support

[cmeeren]

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.

[smoothdeveloper]

@cmeeren thanks for the suggestion.

binary type is currently mapped as byte array:

FSharp.Data.SqlClient/src/SqlClient.DesignTime/SqlClientExtensions.fs

Line 120 in 2ef6c06

"binary", (SqlDbType.Binary, "System.Byte[]", true)

How would you like to distinguish when user wants to use byte array or stream or mixture of both depending the column / parameter?

Also, in your reading sample, I assume if you start reading the stream after the reader is disposed, you'll get a runtime error.

[cmeeren]

Also, in your reading sample, I assume if you start reading the stream after the reader is disposed, you'll get a runtime error.

Yep, I discovered as much.

How would you like to distinguish when user wants to use byte array or stream or mixture of both depending the column / parameter?

Yes, that's a challenge. I don't really have a good answer to this. Particularly since I moved from DB streaming to Azure Blob Storage a while ago (after posting this issue), so I no longer have an actual use-case for this.