0

I am learning about the POCOs and while I like a lot of the concepts, I think I am not quite getting it.

I have a problem like the following:

I have one sproc which returns multiple columns and values against these columns which dynamically build inside the sproc based on certain conditions.

e.g based on the input, one of the below result should return,

1) 

  Id -- Name -- Age  
  1     Peter   25   
  2     Janit   53

2) 

Id -- Provider Name -- Provider Type
5     C. A              hospital

I cant create class for these dynamic columns, therefore I fetch records using dynamic object and POCO DB. 

List<dynamic> list = db.fetch<dynamic>(sql);

Problem occurs when somebody else call the function with different parameter then result keeps the column information for first call of POCO and result for desire one. 

  Id -- Name --    Age  
  5     C. A       hospital

this discrepancy causing runtime error.

Can you please help me to resolve this issue? or how can I define class for this kind of scenario?

Hope I explained my problem in detail manner.

user1182370
  • 19
  • 5

3 Answers3

0

You can define a POCO class just to grab the results. I use plenty of them. PetaPoco will fill only the fields that the SP returns.

Eduardo Molteni
  • 38,786
  • 23
  • 141
  • 206
  • thanks for the information. can you please provide me one example for the same. Actually when I create POCO class for my object it doesn't support to other result set as SPROC returns dynamic columns. sometimes with different names and different counts. that's why I use List object to fetch data. but problem here is that first fetch holds columns information in memory. – user1182370 Sep 19 '14 at 05:28
  • 1
    I did try to have a single POCO and Petapoco keeps filling only the first fields. I had to create a type for each different kind of output to make it work. I take the opportunity to mention that it is clearly not a good idea to have such stored procedure with dynamic column definition depending on inputs... – asidis Mar 20 '23 at 13:54
0

Create a POCO with all columns you expect to be returned from the dynamic SP in a manner like the following: 

public class PocoName    
{
    public int Id {get; set;}
    public string Name {get; set;}
    public int Age  {get; set;}
    public string ProviderName  {get; set;}
    public string ProviderType  {get; set;}
    ...
}

Then call the function as follows: 

List<PocoName> list = db.fetch<PocoName>(sql);

Every time you run the sproc with different input parameters, only the columns returned by the sproc will be populated within your POCO.

kagundajm
  • 1,152
  • 1
  • 15
  • 26
  • There are some more challenges in this structure. 1) I have 7 different SPROCs around 60+ columns. 2) Few column names like "Provider
    Type", it is existing configuration to display columns into the a grid with this manner only. how will I handle this then? is there any other dynamic approach?     – user1182370 Sep 21 '14 at 08:02
0

Though it's a 3 years old post, I came across a similar issue recently. Hopefully the workaround will help someone who encounters this in future.

  • PetaPoco keeps object definitions cached. So any db.fetch/db.query with diff parameters to the same SP will return the first call's object definition when we are expecting to have diff dynamic object list as result. I tried to cache burst, but couldn't find a way. Maybe there's option/config, but I haven't tried that far. If anyone knows the way to cache burst, please do share.
  • Since it is not practical to have Defined POCO classes to fit the dynamic nature of this case (as mentioned earlier by author), I tried returning DataTable and then converted it to dynamic object list which is working as expected.

Thanks

reezvi
  • 46
  • 4