Hi, How do i load an Entity or EntityCollection with Data if I have a stored procedure which will return the required values.

Say the stored Procedure takes one argument CityName and returns back all restaurants in the City.

string cityname="Austin";

RestaurantEntityCollection reataurants=new RestaurantEntityCollection();

// here how do I load this collection using the stored procedure ??

Look in the help for "Retrieval Stored Procedure". This returns a DataTable. Loop through that and for each row create an entity and add it to your collection.

Better yet, skip the stored procedure. What's the point? Just fill your collection directly.

We have some things for which we need to use stored procedures only ( both fetching and updating ), so want to see how I can use a stored procedure to fill my entities.

jakkamma wrote:

We have some things for which we need to use stored procedures only ( both fetching and updating ), so want to see how I can use a stored procedure to fill my entities.

That's not supported in the code, due to the high maintenance issue: if an entity changes, you also have to make sure the procedures change (which often means 3 or 4 procs per entity).

As Jim says, you have to do this manually.

But the way ADO.net DataAdapter does, cant we get similar functionality

We can tell the Adapter either the sql statement or SP name for filling even for typed datasets.

Cant we acheive similar functionality using your Adapter based classes ??

jakkamma wrote:

But the way ADO.net DataAdapter does, cant we get similar functionality

We can tell the Adapter either the sql statement or SP name for filling even for typed datasets.

Cant we acheive similar functionality using your Adapter based classes ??

• Call the procedure, this will give you a datatable. then do: (say you use adapter, and you're fetching Customers)

EntityCollection customers = new EntityCollection(new CustomerEntityFactory());
foreach(DataRow row in theDataTable.Rows)
{
    CustomerEntity newCustomer = (CustomerEntity)customers.AddNew();
    foreach(DataColumn column in theDataTable.Columns)
    {
        newCustomer.SetNewFieldValue(column.ColumnName, row[column]);
    }
}

jakkamma wrote:

We have some things for which we need to use stored procedures only ( both fetching and updating ), so want to see how I can use a stored procedure to fill my entities.

But your stored procedure appears to do nothing more than wrap a SELECT statement.

Thanks for that bit of code, it does the trick. However, I've changed the inner foreach statement to a for loop so only the fields in the entity are checked:

for (int i = 0; i < newCustomer.Fields.Count; i++)
{
    newCustomer.SetNewFieldValue(i, row[newCustomer.Fields[i].Name]);
}

This has a small advantage so that the stored procedure can contain additional columns that are not required by the entity, in case the stored procedure is used elsewhere.

I'd also like to request built in support for filling entity collections from stored procedures. I've already found a couple places in my current project where SP's would be useful, especially to take advantage of some of the new features in SQL Server 2005. Things like Recursive Common Table Expressions, and the ROW_NUMBER() and PARTITION BY functions. Plus stored procedures already support UDF's and UNION's.

Also, it would help where performance is critical. Fewer round trips to the database is a good thing.

I'm aware of the fact that a procedure is sometimes an alternative and it's not that hard to push in a datareader which fills an entity collection. The main issue though is that if a procedure is changed, the whole thing can fall apart, though it will be seen as an error in llblgen pro first.

I know this topic is a bit old, but I've got some questions that are related.

Suppose I am using the Adapter model and I want to be able to create entities from the results of stored procedures. From what I have seen, it looks like I would want to change the behavior of the DataAccessAdapterBase class so that a "fetch" call would run a stored procedure and populate the entity with data from the resulting DataTable. I only have the demo, and this class is hidden from me, so I don't know how feasible this is. I guess I would end up inheriting off of the DataAccessAdapterBase class and overriding the fetch routines...Does anyone have any comments on this?

The fetch routine uses a datareader internally, and it's not recommended to bypass that routine.

The thing with stored procs fetching data for entities is that it gives more maintenance issues: if the selectlist of the procedure changes, it will break the fetch code, though it's hard to track down why.

If you really want to, you can first fetch the proc's data into a datatable, then use this: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=453

Otis wrote:

The fetch routine uses a datareader internally, and it's not recommended to bypass that routine.

The thing with stored procs fetching data for entities is that it gives more maintenance issues: if the selectlist of the procedure changes, it will break the fetch code, though it's hard to track down why.

If you really want to, you can first fetch the proc's data into a datatable, then use this: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=453

Unfortunately it looks like this is a necessity in the project I am working on. Fortunately, the select list that is created in the stored procedures is constructed based on data from another table (which has a list of fields for any given business object), so I am guessing we would be able to load this table into some kind of typed view and use its contents to spin through the datatable created from the stored procedure call (using your retrieval stored procedure method) and fill out the properties of the entity classes. I do believe we can get this to work by, like I said before, overriding (or just changing the code directly) of the DataAccessAdapterBase class. It appears that the fetch routines of this class are not overridable, however, so we would need to change this code and recompile...this is possible if we buy LLBLGEN Pro, no?

I don't recommend people altering the fetch routines, as that will become a serious maintenance burden. The routines which accept a datareader are currently not accessable though when this is done, you could call these routines with a datareader created from a proc call.. The real issue is when you save a graph recursively, like customer - order- order detail. You then have to do this manually, and also sync fk/pk keys, as you have to call the right procs each time, effectively removing the benefit of using such a framework.

Otis wrote:

I don't recommend people altering the fetch routines, as that will become a serious maintenance burden. The routines which accept a datareader are currently not accessable though when this is done, you could call these routines with a datareader created from a proc call.. The real issue is when you save a graph recursively, like customer - order- order detail. You then have to do this manually, and also sync fk/pk keys, as you have to call the right procs each time, effectively removing the benefit of using such a framework.

I hate to keep beating this horse, but how about overriding ExecuteSingleRowRetrievalQuery and ExecuteMultiRowRetrievalQuery to do this?

If you formulate the IDbCommand object in the IActionQuery and IRetrievalQuery object passed in these routines in such a way they call your proc, you can do it of course. Though it will be some work which will likely be void when 1.0.2005.1 comes out, which supports inheritance (entity mapped on multiple targets)

What is also a problem is that with recursive saves, and unit of work commits, you can't simply call a method and be done with it, you have to setup the proc calls yourself for each entity.

Otis wrote:

If you formulate the IDbCommand object in the IActionQuery and IRetrievalQuery object passed in these routines in such a way they call your proc, you can do it of course. Though it will be some work which will likely be void when 1.0.2005.1 comes out, which supports inheritance (entity mapped on multiple targets)

I'm not sure I understand what you mean by "entity mapped on multiple targets." Can you explain this or point me to some information elsewhere?

What is also a problem is that with recursive saves, and unit of work commits, you can't simply call a method and be done with it, you have to setup the proc calls yourself for each entity.

We wouldn't need proc calls for saves, I think LLBLGEN can do that stuff fine, supposing we can figure out a way to limit the saving to specific fields (some fields in the entity should not be updateable by some users, which can be determined by security objects that will be in memory). It is only the initial loading of the entities that doesn't fit with the model we are making. My goal here is just to control what data gets loaded via the stored procedures which contain our retrieval security code, and hopefully LLBLGEN can do the rest.

mikeg22 wrote:

Otis wrote:

If you formulate the IDbCommand object in the IActionQuery and IRetrievalQuery object passed in these routines in such a way they call your proc, you can do it of course. Though it will be some work which will likely be void when 1.0.2005.1 comes out, which supports inheritance (entity mapped on multiple targets)

I'm not sure I understand what you mean by "entity mapped on multiple targets." Can you explain this or point me to some information elsewhere?

In the next upgrade, you'll be able to map entities in a hierarchy, with supertypes/subtypes. So you have for example Employee with subtype Manager which has a subtype Boardmember. All these entities are mapped on different tables. This means that Boardmember has fields from 3 different entities and when saved it has to save (or update or delete) in 3 different tables.

What is also a problem is that with recursive saves, and unit of work commits, you can't simply call a method and be done with it, you have to setup the proc calls yourself for each entity.

We wouldn't need proc calls for saves, I think LLBLGEN can do that stuff fine, supposing we can figure out a way to limit the saving to specific fields (some fields in the entity should not be updateable by some users, which can be determined by security objects that will be in memory). It is only the initial loading of the entities that doesn't fit with the model we are making. My goal here is just to control what data gets loaded via the stored procedures which contain our retrieval security code, and hopefully LLBLGEN can do the rest.

Well, if you derive a class from DataAccessAdapter, and in there override ExecuteMultiRowRetrievalQuery(), and you can intercept the fetch calls. ExecuteSingleRowRetrievalQuery().

The problem for you then is to figure out which proc to call, so you might as well override FetchEntityCollection and FetchEntity and friends. Which can signal (by setting a flag for example) which proc to call based on the entity specifications they receive.

Execute*RowRetrievalQuery() receive an IRetrievalQuery object. That object has a Command property (cast to RetrievalQuery first). Which is an IDbCommand object. You then have to modify that to call the proper proc, set the CommandType to procedure and eventually set parameters again.

Be aware that if the call specifies any filters, you've to call a different proc for example.

I am retrieving a datatable via a retrieval stored procedure (multiple joins involved). Is my dataset readonly or am I overlooking a grid issue? thanks

lwatson wrote:

I am retrieving a datatable via a retrieval stored procedure (multiple joins involved). Is my dataset readonly or am I overlooking a grid issue? thanks

It's not readonly. So please check the grid settings and also the datatable column properties.

But, if I may ask, what does that have to do with this thread you're posting in? Please don't hijack other threads, as it's confusing. Please next time start your own new thread