How to add all ref cursors into dataset C# PostgreSQL

67 Views Asked by At

I wrote following code to add all ref cursors values into dataset.

public async Task<DataSet> GetReportData(string PeriodFrom, string PeriodTo)
{
    try
    {
        Utils oUtils = new Utils();

        DataSet dsReturnData = new DataSet("dsQLT_REV_DASHBOARD");
        //; fetch all in rccmd;fetch all in rccpd;
        _dataService.InitializeCommand("CALL public.review_dashboard(@pd_fromdate ,@pd_todate,@rccmd,@rccpd,@rcfmd,@rccts,@rcoperation,@rcntw);fetch all in rccmd;fetch all in rccpd;", CommandType.Text);
        _dataService.AttachParameter("@pd_fromdate", NpgsqlTypes.NpgsqlDbType.Timestamp, oUtils.GetStringToDate(PeriodFrom));
        _dataService.AttachParameter("@pd_todate", NpgsqlTypes.NpgsqlDbType.Timestamp, oUtils.GetStringToDate(PeriodTo));

        _dataService.AttachParameter("@rccmd", NpgsqlTypes.NpgsqlDbType.Refcursor, "rccmd");
        _dataService.AttachParameter("@rccpd", NpgsqlTypes.NpgsqlDbType.Refcursor, "rccpd");
        _dataService.AttachParameter("@rcfmd", NpgsqlTypes.NpgsqlDbType.Refcursor, "rcfmd");
        _dataService.AttachParameter("@rccts", NpgsqlTypes.NpgsqlDbType.Refcursor, "rccts");
        _dataService.AttachParameter("@rcoperation", NpgsqlTypes.NpgsqlDbType.Refcursor, "rcoperation");
        _dataService.AttachParameter("@rcntw", NpgsqlTypes.NpgsqlDbType.Refcursor, "rcntw");

        using (NpgsqlDataReader dataReader = await _dataService.ExecuteDataReaderAsync().ConfigureAwait(false))
        {

            dataReader.NextResult();
            DataTable schemaTable = new DataTable("QIS_GT_DASHBOARD");
            schemaTable.Load(dataReader);
            dsReturnData.Tables.Add(schemaTable);

            dataReader.NextResult();
            DataTable schemaTable2 = new DataTable("QIS_GT_DASHBOARD1");
            schemaTable2.Load(dataReader);
            dsReturnData.Tables.Add(schemaTable2);

        }

        return dsReturnData;
    }
    catch (Exception ex)
    {

        throw;
    }
}

above code only adding 1st cursor (rccmd) data only. how can I add other ref cursors data into dataset?

0

There are 0 best solutions below