F# FSharp.Data.SqlClient not recognizing multiple return tables from Stored Procedure

Question

I am not sure if this is possible but I have not been able to come across clear documentation for this use case. I am using F# 4 and the FSharp.Data.SqlClient library to connect to SQL Server 2016. I am wanting to call a stored procedure that returns multiple tables and turn those tables into the corresponding records. In this case the first table is made up of items and the second table is made up of customers.

My instinct is that it should look something like this:

let items, customers = cmd.Execute()

My gut is that items would be an IEnumerable<item> and customers would be an IEnumerable<customer> where item and customer are both Record types. What it appears is happening though is that FSharp.Data.SqlClient is only seeing the first returned table from the stored procedure. I am working on a SQL Server 2016 Developer instance. Here is the T-SQL to setup the example:

create table Item (
    ItemID int identity(1, 1) primary key,
    ItemName nvarchar(50)
)
go

create table Customer (
    CustomerID int identity(1, 1) primary key,
    CustomerName nvarchar(50)
)
go

insert into Item (ItemName) values ('A');
insert into Item (ItemName) values ('B');
insert into Item (ItemName) values ('C');

insert into Customer (CustomerName) values ('Gary');
insert into Customer (CustomerName) values ('Sergei');
insert into Customer (CustomerName) values ('Elise');
go

create procedure dbo.ExampleProcedure
as
begin
    set nocount on;

    select
        ItemID,
        ItemName
    from Item

    select
        CustomerID,
        CustomerName
    from Customer

end;

And here is the F# script that I am testing with. It shows what I would like to be able to do but I get a compile error on the last line:

#r "../packages/FSharp.Data.SqlClient.1.8.2/lib/net40/FSharp.Data.SqlClient.dll"
#r "../packages/FSharp.Data.2.3.2/lib/net40/FSharp.Data.dll"
#r "System.Xml.Linq.dll"
open FSharp.Data

[<Literal>]
let connStr = 
    "Data Source=**connection string**;"

type queryExample = SqlProgrammabilityProvider<connStr>
do
    use cmd = new queryExample.dbo.ExampleProcedure(connStr)
    let items, customers = cmd.Execute()

I am wanting items to correspond to the first returned table and customers to correspond to the second returned table. The intellisense suggests that FSharp.Data.SqlClient is only seeing the first table. When I hover over cmd.Execute() the popup says "This expression was expected to have type 'a*'b but here has type System.Collections.Generic.IEnumerable<SqlProgrammabilityProvider<...>.dbo.ExampleProcedure.Record>". If I do the following I get access to the Items query in the stored procedure:

// Learn more about F# at http://fsharp.org. See the 'F# Tutorial' project
// for more guidance on F# programming.
#r "../packages/FSharp.Data.SqlClient.1.8.2/lib/net40/FSharp.Data.SqlClient.dll"
#r "../packages/FSharp.Data.2.3.2/lib/net40/FSharp.Data.dll"
#r "System.Xml.Linq.dll"
open FSharp.Data

[<Literal>]
let connStr = 
    "Data Source=**connection string**;"

type queryExample = SqlProgrammabilityProvider<connStr>
do
    use cmd = new queryExample.dbo.ExampleProcedure(connStr)
    for item in cmd.Execute() do
        printfn "%A" item.ItemID

Is this even possible? Is my approach wrong? I could not find clear documentation on this use case but I thought it would be common enough it would be covered.

Update

Just to clarify what I am trying to achieve I am showing how I solve this in C#. In C# I create a DataSet object and populate it with the results of the Stored Procedure. From there I pick out the individual tables to work with. After extracting the tables I then use LINQ to transform the rows into the corresponding objects. It often looks something like the following:

using System.Data;
using System.Data.SqlClient;

var connStr = "**connection string**"
var sqlConnection = new SqlConnection(connStr );
var sqlCommand = new SqlCommand("ExampleProcedure", sqlConnection);
sqlCommand.CommandType = CommandType.StoredProcedure;
var dataSet = new DataSet();
var adapter = new SqlDataAdapter(sqlCommand);
adapter.Fill(dataSet);
var itemsTable = dataSet.Tables[0];
// Turn the itemsTable into a List<Item> using LINQ here
var customersTable = dataSet.Tables[1];
// Turn the customersTable into List<Customer> using LINQ here

I find this to be overly verbose for such a simple thing as extracting the individual tables but perhaps I am too sensitive to code clutter. I know that F# must have a more elegant and terse way to express this.

Answer 1

I don't know F#, however this is a data access problem.

When a stored procedure returns multiple resultsets, you need to access they in sequence, one by one.

cmd.ExecuteReader() returns an instance of a datareader pointing to the first resultset. You need to process this resultset, may be filling a list with instances of a custom class, than you call the method "NextResult" and you will have access to the next resultset and so on.

A reference for the method "NextResult": https://msdn.microsoft.com/pt-br/library/system.data.sqlclient.sqldatareader.nextresult(v=vs.110).aspx