Creating EntityCollection from DataTable (from Stored Procedure)

Posts   
 
    
DrM
User
Posts: 49
Joined: 09-Feb-2004
# Posted on: 12-Feb-2004 16:02:57   

What is the most efficient way to create an entity collection from a stored procedure?

The stored proc is recognized correctly as a retrieval stored procedure and returns a DataTable (I'm presuming this is the original .NET class).

Is there a quick method of creating an EntityCollection from this DataTable?

Regards Derek

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 12-Feb-2004 19:04:29   

DrM wrote:

What is the most efficient way to create an entity collection from a stored procedure?

The stored proc is recognized correctly as a retrieval stored procedure and returns a DataTable (I'm presuming this is the original .NET class).

Is there a quick method of creating an EntityCollection from this DataTable?

Not at the moment no. You have to loop through the rows in the datatable, and for each row add a new entity and for each field in the row, set the corresponding field's value to the value of that table field. Something like this:


EntityCollection customers = new EntityCollection(new CustomerEntityFactory());
DataTable myDataTable = new DataTable();
for(int i=0;i<myDataTable.Rows.Count;i++)
{
    CustomerEntity newCustomer = customers.EntityFactoryToUse.Create();
    for (int j = 0; j < newCustomer.Fields.Count; j++)
    {
        newCustomer.Fields[j].ForcedCurrentValueWrite(myDataTable.Rows[i][j]);
    }
}

This of course doesn't handle NULL values. It also requires that the columns in the resultset are in the same order as the fields in the entity.

It's a little inefficient, because the DataAccessAdapter's internal entity fetcher uses a datareader as its datasource. It would be best if you could feed this routine with a datareader which holds a resultset from a stored proc.

Interesting idea, I'll think about adding a little routine making this possible, with all the responsibility at the developer of course: the proc has to return the right resultset and the right data.

Frans Bouma | Lead developer LLBLGen Pro
billb
User
Posts: 50
Joined: 09-Jul-2004
# Posted on: 21-Jul-2004 21:26:09   

Is this still the case? I'm subcontracting for a project and the contractor is REQUIRING the use of stored procedures (already tried to argue it). We use LLBLGen Pro for other products quite happily (w/o stored procs) and would like to extend it to this new project as well. I'm in need of a clean solution or else I'll be creating object by hand. rage

Thanks.

Otis wrote:

DrM wrote:

What is the most efficient way to create an entity collection from a stored procedure?

The stored proc is recognized correctly as a retrieval stored procedure and returns a DataTable (I'm presuming this is the original .NET class).

Is there a quick method of creating an EntityCollection from this DataTable?

Not at the moment no. You have to loop through the rows in the datatable, and for each row add a new entity and for each field in the row, set the corresponding field's value to the value of that table field. Something like this:


EntityCollection customers = new EntityCollection(new CustomerEntityFactory());
DataTable myDataTable = new DataTable();
for(int i=0;i<myDataTable.Rows.Count;i++)
{
    CustomerEntity newCustomer = customers.EntityFactoryToUse.Create();
    for (int j = 0; j < newCustomer.Fields.Count; j++)
    {
        newCustomer.Fields[j].ForcedCurrentValueWrite(myDataTable.Rows[i][j]);
    }
}

This of course doesn't handle NULL values. It also requires that the columns in the resultset are in the same order as the fields in the entity.

It's a little inefficient, because the DataAccessAdapter's internal entity fetcher uses a datareader as its datasource. It would be best if you could feed this routine with a datareader which holds a resultset from a stored proc.

Interesting idea, I'll think about adding a little routine making this possible, with all the responsibility at the developer of course: the proc has to return the right resultset and the right data.

Fishy avatar
Fishy
User
Posts: 392
Joined: 15-Apr-2004
# Posted on: 21-Jul-2004 22:00:56   

I use DataAdapter and I use the Entities within my PL. I bind the EntityCollection to grids. If the user changes information in the grid I simply call the dataadapter Save method against the EntityCollection (in the BL, of course) and my data base is updated.

The works very well.

I had once thought, hey it would be great to call a stored proc, create the EntityCollection and perform binding and updates. Of course the problem is I could not dynamically update with the dataadapter. I would have to go thru the EntityCollection, see what was updated, and then call a stored proc to perform the update.

So, what would the point be of creating the EntityCollection as opposed to using a DataTable?

Now, if Frans could make a change to map a stored proc, associate additional stored procs for doing updates and deletes and have a way to check for schema consistancy (optional, could trust the programmer/dba); that would be fantastic. stuck_out_tongue_winking_eye

billb
User
Posts: 50
Joined: 09-Jul-2004
# Posted on: 21-Jul-2004 22:09:05   

For starters, having the code generated is a big plus. The thought of having to hand code collections is not appealing. I do enough monkey work it seems.

You definitely make a good point about the updating, however, I'm going to have to do the same thing with my own collections anyhow. This is a case where I'm really not using the tool as it was designed to be used, and I understand that. I'm just seeing if there's a way I can use it to save time and effort.

Somehow I don't think we're going to get Frans to add that level of support for stored procs. Although, for me, it would be nice in this particular situation.

I'm still sitting here trying to figure out if I want to use LLBLGen for this product or not. confused

Thanks for the response.

Fishy wrote:

I use DataAdapter and I use the Entities within my PL. I bind the EntityCollection to grids. If the user changes information in the grid I simply call the dataadapter Save method against the EntityCollection (in the BL, of course) and my data base is updated.

The works very well.

I had once thought, hey it would be great to call a stored proc, create the EntityCollection and perform binding and updates. Of course the problem is I could not dynamically update with the dataadapter. I would have to go thru the EntityCollection, see what was updated, and then call a stored proc to perform the update.

So, what would the point be of creating the EntityCollection as opposed to using a DataTable?

Now, if Frans could make a change to map a stored proc, associate additional stored procs for doing updates and deletes and have a way to check for schema consistancy (optional, could trust the programmer/dba); that would be fantastic. stuck_out_tongue_winking_eye

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 21-Jul-2004 22:26:48   

It's too much of a failure point to add stored proc support for entity persistence logic: everything has to match, which can go wrong in a lot of places.

Adapter will get a fetch entity collection method which accepts a datareader (open). So you can somehow work around it, but not in full...

Frans Bouma | Lead developer LLBLGen Pro
billb
User
Posts: 50
Joined: 09-Jul-2004
# Posted on: 21-Jul-2004 22:33:32   

That's really the answer I was expecting, but was hoping for something magical up your sleeve that I maybe hadn't thought about or didn't know existed.

Thanks for the response.

Otis wrote:

It's too much of a failure point to add stored proc support for entity persistence logic: everything has to match, which can go wrong in a lot of places.

Adapter will get a fetch entity collection method which accepts a datareader (open). So you can somehow work around it, but not in full...

Fishy avatar
Fishy
User
Posts: 392
Joined: 15-Apr-2004
# Posted on: 22-Jul-2004 03:20:15   

billb wrote:

I'm still sitting here trying to figure out if I want to use LLBLGen for this product or not. confused

If you can't directly access the tables then the only benifit I see is for creating strongly typed datatables out of Views. disappointed That alone can save you alot of time. simple_smile

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 22-Jul-2004 09:34:26   

billb wrote:

That's really the answer I was expecting, but was hoping for something magical up your sleeve that I maybe hadn't thought about or didn't know existed.

Sorry, I can't help you further. disappointed

Frans Bouma | Lead developer LLBLGen Pro