How to get multiple RecordSets from StoredProcedure with NReco.Data in Asp.NetCore

Question

I use NReco.Data in my Asp.NetCore Application to make db-calls, because I don't want to use EF and DataTable isn't supported yet.

Now I need to call a StoredProcedure and get Multiple RecordSets (or Dictionarylists).

At the moment I called this: dbAdapter.Select($"STOREDNAME @{nameof(SQLPARAMETER)}", SQLPARAMETER).ToRecordSet()

But the stored gives me more than 1 recordset, can anyone help me to get the others?

Answer 1

Currently NReco.Data.DbDataAdapter has no API for processing multiple result sets returned by single IDbCommand.

You can compose IDbCommand by yourself, execute data reader and read multiple result sets in the following way:

IDbCommand spCmd;  // lets assume that this is DB command for 'STOREDNAME'
RecordSet rs1 = null;
RecordSet rs2 = null;
spCmd.Connection.Open();
try {
    using (var rdr = spCmd.ExecuteReader()) {
        rs1 = RecordSet.FromReader(rdr);
        if (rdr.NextResult())
            rs2 = RecordSet.FromReader(rdr);
    }
} finally {
    spCmd.Connection.Close();
}

As NReco.Data author I think that support for multiple result sets may be easily added to DbDataAdapter API (I've just created an issue for that on github).

-- UPDATE --

Starting from NReco.Data v.1.0.2 it is possible to handle multiple result sets in the following way:

(var companies, var contacts) = DbAdapter.Select("exec STOREDNAME").ExecuteReader( 
  (rdr) => {
    var companiesRes = new DataReaderResult(rdr).ToList<CompanyModel>();
    rdr.NextResult();
    var contactsRes = new DataReaderResult(rdr).ToList<ContactModel>();
    return (companiesRes, contactsRes);
  });

In the same manner DataReaderResult can map results to dictionaries or RecordSet if needed.