I'm newbie in ORM stuffs and after review several products, it seems that LLBLGen is quite promising.

In my developer team, we used to be using stored procedure (sql server) a lot. For example, sp_create_txn will do several things: 1. call authentication sp (member or not, already login or not, etc) 2. call balance checking sp (enough balance or not) 3. call SP to retrieve supporting data (currency conversion, etc) 4. then insert data into transaction table. From the documentation I understand (partially of course ) that using LLBLGen (adapter approach) then I have to shift all above process into Business Logic tier. In such case it means that there will be 3 round-trips (roughly) between Business Logic tier and DB.

The question is, using LLBLGen is it possible to wrap step no.1-3 into one round trip only? I need this approach because sp_create_txn will be called approximately 10.000+ hits concurrently.

Many thanks in advance. Johan

You can keep the stored procedure if you want, you can add the procedure to your project as a call, you can then call it with one line of code. The results of the procedure you can then use to fetch an entity or not. You can call procedures in the same transaction as saving an entity for example. You can for example add them to a unitofwork and schedule the call to take place in one of the defined slots, see the unitofwork documentation for details on that.

The core element of O/R mapping is that you work with your data using objects. This means that your entities aren't accessed/manipulated through procedures but by loading the entities into memory (into objects) and manipulating them there, and after the process is done, saving them again.

If a long-running process is to be taken place, you could optimize it by cutting out the roundtrips, that is, if these roundtrips are significant to the process time. If the process takes 1 second and the roundtrips 20ms, it's of no use to optimize that out, you won't notice it.

LLBLGen Pro doesn't support loading entities using procedures. This is done to avoid crashes inside the runtime library due to changing resultset definitions of hte procedures, which are hard to track down.

I'll take a look about unitofwork later.

Meanwhile, the reason I'm looking for ORM framework is to eliminate or at least minimize the usage of Stored Procedure, because it's getting out of hand maintaining so many SP.

With prefetch path, I can retrive related entities within one roundtrip. Based on this may be it is also possible that I can also fetch unrelated entities within one roundtrip also.

For example:

IBatchFetch batch = new IBatchFetch();

AuthEntity auth = new AuthEntity("admin"); BalanceEntity ball = new BalanceEntity("user1"); CurrencyEntity curr = new CurrencyEntity("USD");

batch.Add(auth); batch.Add(ball); batch.Add(curr);

batch.Fetch(); //batch loading.....

What do you think of this?

Again, thanks in advance. Johan

cjexxi wrote:

I'll take a look about unitofwork later.

Meanwhile, the reason I'm looking for ORM framework is to eliminate or at least minimize the usage of Stored Procedure, because it's getting out of hand maintaining so many SP.

With prefetch path, I can retrive related entities within one roundtrip. Based on this may be it is also possible that I can also fetch unrelated entities within one roundtrip also.

Prefetch paths make 1 roundtrip per node. So if you fetch customers - orders - order details - products, it needs 4 roundtrips. This is inevidable, as it has to handle all graph types. It uses the same connection so roundtrip times are limited to the bare minimum.

For example:

IBatchFetch batch = new IBatchFetch();

AuthEntity auth = new AuthEntity("admin"); BalanceEntity ball = new BalanceEntity("user1"); CurrencyEntity curr = new CurrencyEntity("USD");

batch.Add(auth); batch.Add(ball); batch.Add(curr);

batch.Fetch(); //batch loading.....

What do you think of this?

This still requires 3 queries being executed as 3 different resultsets. To limit roundtrip time, you could do: (only adapter)

// instantiate a DataAccessAdapter instance and signal it to keep open the connection
using(DataAccessAdapter adapter = new DataAccessAdapter(true))
{
adapter.OpenConnection();
// do the fetches here, all 3
adapter.FetchEntity(auth);
adapter.FetchEntity(ball);
adapter.FetchEntity(curr);
adapter.CloseConnection();
}

This will make 3 roundtrips, but over the same connection, which should limit the time spend on the routine.