Hi,

Is it possible to get more than one resultset, with one roundtrip?

I have two typed views, that I need to pupulate two grid controls on the same form.

I'm using adapter scenario.

These typed views are not related, so is it possible to get them alltogether like stored procedures can retrieve more than one resultset?

Thanks in advance

Gabor

Yes you can, adapter lets you cheat on this

As you have to pass in the fields for the typed view and the datatable based typed view class, you can pass in the same typed view class twice but the second time, you pass in the fields of the second view, so effectively fetching the second's typed view's data into the first typed view's object

Frans,

Does it mean, that the two typed view have to have same fileds, but different value?

Gabor

Gabor wrote:

Frans,

Does it mean, that the two typed view have to have same fileds, but different value?

Gabor

Well, to merge 2 resultsets, the types of the fields have to match. So if you have at position 3 an Int32 field in view A, you also have to have an Int32 field at position 3 in view B. This is also true for UNION queries.

If you just want 2 resultsets, separately, you have to call FetchTypedView() two times.

Frans, Just wondering if it Is something which would be possible to implement in Self Servicing? Actually it will be great if we can have something which would get multiple resultsets in 1 trip and save to multiple tables in 1 trip. I don't know how big this is but thought i would just ask. Thanks.

netLearner wrote:

Frans, Just wondering if it Is something which would be possible to implement in Self Servicing? Actually it will be great if we can have something which would get multiple resultsets in 1 trip and save to multiple tables in 1 trip. I don't know how big this is but thought i would just ask. Thanks.

It can't be done in .NET 1.x, as I use datareaders to fetch the data. Datareaders in 1.x can only have 1 resultset. In .NET 2.0, for Sqlserver, MS implemented MARS, multiple active result sets . Though I'm not sure if that's for Yukon or for everyone. Still, it will be hard to implement it into the core of the object fetcher, as it works with a factory and a datareader now, it then has to work with multiple factories and multiple resultsets and which set belongs to which factory...

Frans, Inorder to save 2 or more different/unrelated entities at 1 time i am thinking i should use UnitOfwork in self servicing. Just wondering if you agree? Thanks.

Correct. You can also create a transaction, add them to the transaction and save them separately. Either way, per entity a query is produced.

Thanks Frans. For creating a transaction i am thinking it may not work for my scenario as it is like this: I have 2 sets of tables like:

Parent1 | ---------Child1 | ----------Child2

Parent2 | ---------Child1 | ----------Child2

From the above i have 6 brand new entities and inorder to save all 6 in 1 trans i can follow these approaches while saving:

Approach 1: Parent1.Save(true); Parent2.Save(true);

The problem with this approach is they are 2 transactions and i need it to be 1.

Approach 2:

UOW.AddForSave(Parent1,true); UOW.AddForSave(Parent2,true); UOW.Commit();

This will create 1 trans, so it is good.

Approach 3:

trans = new Transaction(); trans.Add(Parent1); Parent1.Save(true);

trans.Add(Parent2); Parent2.Save(true); trans.commit();

This is only 1 trans right. Thanks.

Correct

Otis wrote:

Yes you can, adapter lets you cheat on this

As you have to pass in the fields for the typed view and the datatable based typed view class, you can pass in the same typed view class twice but the second time, you pass in the fields of the second view, so effectively fetching the second's typed view's data into the first typed view's object

Frans,

I'm a little confused about the above... In my case I'm not using a typed view. Is it possible to get multiple resultsets from a Stored Procedure? I don't think it is... Is this something you have thought about for future versions?

This is another example of where I would love to get access to the underlying datareader....

Marcus

Marcus wrote:

Otis wrote:

Yes you can, adapter lets you cheat on this

As you have to pass in the fields for the typed view and the datatable based typed view class, you can pass in the same typed view class twice but the second time, you pass in the fields of the second view, so effectively fetching the second's typed view's data into the first typed view's object

Frans,

I'm a little confused about the above... In my case I'm not using a typed view. Is it possible to get multiple resultsets from a Stored Procedure? I don't think it is... Is this something you have thought about for future versions?

In .NET 1.x, it's not possible to have multiple resultsets over a single datareader. That is, if you're not using Oracle. With Oracle, you can have multiple Ref cursor parameters, which you can bind to datatables, and which are then filled by the cursors, effectively the same as MARS. MARS is available on sqlserver in .NET 2.0 and sqlserver 2005.

This is another example of where I would love to get access to the underlying datareader....

It wouldn't help you in .NET 1.x.

Otis wrote:

In .NET 1.x, it's not possible to have multiple resultsets over a single datareader. That is, if you're not using Oracle. With Oracle, you can have multiple Ref cursor parameters, which you can bind to datatables, and which are then filled by the cursors, effectively the same as MARS. MARS is available on sqlserver in .NET 2.0 and sqlserver 2005.

This is news to me...

I'm trying to remember if I have ever implemented multiple resultsets in .Net 1.1... and I'm sure I have...

What's the IDataReader.NextResult() method for then?

UPDATE: see Multiple ResultSets in ADO.NET... am I missing something?

Marcus

Hmm, I'm sure I read it 2 days ago... can't find it back, so I think I'm wrong.

What I think am confusing is that you can't have multiple active resultsets, at least not with the IDataReader interface, only after eachother.

Still, this can lead to some optimizations, if the provider used supports batching (or ref cursors). Batching isn't supported on firebird/access/mysql and db2. (it's supported by the db engine sometimes, but not in the provider).

Though I have to add: don't think you can save seconds with this. Profiling the system showed that the vast majority of the time spend fetching the data is in either transporting the data from db to client and/or moving it into objects, not in executing a new reader, as the connection is already open.

Otis wrote:

Though I have to add: don't think you can save seconds with this. Profiling the system showed that the vast majority of the time spend fetching the data is in either transporting the data from db to client and/or moving it into objects, not in executing a new reader, as the connection is already open.

No doubt... but I guess this is app specific I have a requirement to fetch 10-12 different readonly resultsets for presentation on a web page, each of which only contains a single row.

One day you'll expose the IDataReader

It's still on the todo