How to convert petapocohelper executereader to List [C#]

Question

I'm developing .cs code to get data from a Stored Procedure and store it to a List but i'm having trouble getting all datasets to the List.

The SP:

ALTER PROCEDURE [_testSchm].[_test_sp]

@paramINint int,   

@paramINnvarchar nvarchar(max),   

@paramOUTint int OUTPUT,   

@paramOUTnvarchar nvarchar(max) OUTPUT

AS   

set @paramOUTint = @paramINint * 33

set @paramOUTnvarchar = REVERSE(@paramINnvarchar)

select 666 as ResultSet1

select 999 as ResultSet1

return 401

The class i defined to fill the list in the .cs code:

public class readerSQL

{

    public int paramOUTint { get; set; } //esto es de prueba y si, se devuelve a cliente con el valor null porque el SP no lo rellena a su llamada

    public string paramOUTnvarchar { get; set; }

    public int ResultSet1 { get; set; }

    public int ReturnValue { get; set; }

}

The .cs code:

            var paramINint = 3;

            var paramINnvarchar = "carlos";

            var paramOUTint = 0;

            var paramOUTnvarchar = "";

            object[] testJSONParameters = new object[] { paramINint, paramINnvarchar, paramOUTint, paramOUTnvarchar }; 

            IDataReader petaReaderTest = PetaPocoHelper.ExecuteReader( 

                DataProvider.Instance().ConnectionString,

                CommandType.StoredProcedure,

                "_testSchm._test_sp",

                testJSONParameters

            );

            var ReturnJSON = CBO.FillCollection<readerSQL>(petaReaderTest);

ReturnJSON returns only this:

<ArrayOfreaderSQL>
    <readerSQL>
        <ResultSet1>666</ResultSet1>
        <ReturnValue>0</ReturnValue>
        <paramOUTint>0</paramOUTint>
        <paramOUTnvarchar i:nil="true"/>
    </readerSQL>
</ArrayOfreaderSQL>

What SQL management studio returns:

ColName : ResultSet1

Value :      666

 ---------------------

ColName : ResultSet1

Value :       999

 ----------------

ColName : @paramOUTint   |||    @paramOUTnvarchar

Value :       99         |||          solrac

 -----------------

ColName : Return Value

Value :       401

What I want to be returned (or similar) as json :

<ArrayOfreaderSQL>
    <readerSQL>
        <ResultSet1>666</ResultSet1>
        <ReturnValue>0</ReturnValue>
        <paramOUTint i:nil="true"/>
        <paramOUTnvarchar i:nil="true"/>
    </readerSQL>
    <readerSQL>
        <ResultSet1>999</ResultSet1>
        <ReturnValue>0</ReturnValue>
        <paramOUTint i:nil="true"/>
        <paramOUTnvarchar i:nil="true"/>
    </readerSQL>
    <readerSQL>
        <ResultSet1>0</ResultSet1>
        <ReturnValue>0</ReturnValue>
        <paramOUTint>99</paramOUTint>
        <paramOUTnvarchar>solrac</paramOUTnvarchar>
    </readerSQL>
    <readerSQL>
        <ResultSet1>0</ResultSet1>
        <ReturnValue>401</ReturnValue>
        <paramOUTint i:nil="true"/>
        <paramOUTnvarchar i:nil="true"/>
    </readerSQL>
</ArrayOfreaderSQL>

score 1 · Answer 1 · answered Mar 14 '18 at 18:48

The way you are using petapoco is more like basic .net sqlClient object then petapoco... You need to learn how to work with petapoco...

The steps you'll need are:

First : instantiate a new instance of petapoco database with your connection string and supported provider. var database = new PetaPoco.Database(cnx,provider);

Then: you can use a fetch<T>() to get data from database in a list. Maybe use SQL helper to build your sql since you have in and out parameters. (fetch(string sql) can handle a valid sql statement in a string and will return the result of a query. (not the out params in your case).

for parameters with sql store procs, see http://www.enkelmedia.se/blogg/2014/5/3/stored-procedures-with-output-parameters-using-petapoco.aspx

var sql = PetaPoco.Sql.Builder()
    .Select("*")
    .From("")
    .Where("field = @0", fieldvalue);
var resultFromDb = database.Fetch<ObjToMatchSQLQuery>(sql); // ObjToMatchSQLQuery can be dynamic

After that, you have a list of object as you would have done by hand. Finaly push your list onto a JSON convertor, and you'll have what you need.

aka JSONCONVERTOR.serialize<ObjToSerialiseTo>(resultFromDb)

That is because it is not pure petapoco. It is petapocohelper, which is a datalayer introduced by DotNetNuke to execute any sql - basic SqlHelper like functionality (as they say at their documentation) The code I wrote above, actually works, the problem is that I only get the first dataset when calling that SP. — Carlos Miranda, Mar 16 '18 at 14:09
I need to get all the datasets, and it seems that the only way to approach it is to do a while(reader.Read()){doStuff...} as it reads every dataset However my code is returning an error similar to: "the reader is closed" when i do stuff inside the while instrucction When does petapocohelper close that connection? I don't know how to go on... — Carlos Miranda, Mar 16 '18 at 14:10
You can see an example where DNN uses petapocohelper.executereader() and a while bucle inside GetRecordById function definition here: http://sourcebrowser.io/Browse/dnnsoftware/Dnn.Platform/DNN%20Platform/Tests/DotNetNuke.Tests.Integration/Framework/Helpers/DatabaseHelper.cs#203 — Carlos Miranda, Mar 16 '18 at 14:10

How to convert petapocohelper executereader to List [C#]

1 Answers1