A stored procedure returns a DataTable. Would be nice if we could define what it returned and an Entity be constructed. Perhaps if we gave you 'dummy parameter values' you could get most of the basic entity properties from an examination of the DataTable that was returned from this call to the proc. We could define anything that could not be determined or any relationships we needed.

While, your product does an unbelievable job with Tables and relationships, support for Views and Stored procs needs some enhancements as they are both a necesssity in database development.

Thanks for consideration and your product is really fantastic!

This has been requested a lot, though it's not as simple as it might sound. The main problem is that the filter to specify which entity/entities to return isn't obvious and has to be present in the proc. Also, the proc's resultset has to obey strict rules.

This is very inflexible and can fail easily, and thus makes the system rather fragile, hence the absense for such a method. For v2 we're looking into adding some sort of support for this, but nothing has been decided to do this for sure.

It's not hard for us to pass an open datareader to the object fetch logic, it's hard to make it robust so it will work in all cases users will try this out, and that's not that simple.

Great. Would love to see support.

I could see where a proc returned different values based on parameters, etc and that would break. What I was saying is that for procs that return the same structure everytime, I would define a NEW entity that it would return. If I then wanted to get that into some other existing entity, I would have to manually write code to map it over.

Thanks for the response.

What I wonder is: why not move the select statement into a view and use that instead in the llblgen pro designer? You can map an entity onto a view without a problem. That would give you the same feature + you can fully use the entity with other entities, have relations with the view etc. etc.

The operation cannot be done in a view. Lets say your proc goes out and does some calculations, calls other stuff, but ultimately returns row(s) of data with one or more fields in it.

There are tons of examples of this situation and I have several - you just cannot get it done in a view.

Also, speaking of the view and defining relations and such. I have a view that pulls data from tables(s). The view then has primary keys. I know I can mark those as primary keys and then you will generate direct method calls to lookup by primary key. However, I have other unique items in the view than the primary key and would like to look up by them also. Of course, I can just write that code, but if it were a Table, they would be generated from me because of the unique constraint. Would be great if I could identify that somehow also.

Maybe I'm out of topic, sorry if it's the case (and forget this post if it's the case) But I think you can call sp from a view (in mssql) so you can make a view that only return the result of an sp. But of course it doesn't work if your sp need some parameters...

WayneBrantley wrote:

The operation cannot be done in a view. Lets say your proc goes out and does some calculations, calls other stuff, but ultimately returns row(s) of data with one or more fields in it.

There are tons of examples of this situation and I have several - you just cannot get it done in a view.

There are cases where a view can't do what a proc does, but the goal here is to return a resultset, not do number crunching, and ALSO that resultset represents per row an entity. I don't see the logic in that or that being a common scenario. Normally you have data retrieval routines and data manipulation routines, IF you use procs.

The semantic interpretation of what a row MEANS in such a resultset isn't an 'entity' as defined in an abstract model, but a row in a resultset.

I understand people want to use every proc they run into with the persistence core but that's simply not possible. A lot of work has been spend to make data-access be less fragile and introducing procedure persistence will make it more fragile. As I said above, it's not hard to pass a datareader to the object fetcher and force it to read entities from that datareader and hope for the best. However when it falls apart at runtime, you won't be happy and neither will we because users will be faced with a fragile system that falls apart as soon as something changes which then can't check or have control over.

That said, you still can call a procedure and have the resultset be used in your code, you just can't read the proc data into an entity collection. I don't see this as a downside. You still can read the data you want.

Also, speaking of the view and defining relations and such. I have a view that pulls data from tables(s). The view then has primary keys. I know I can mark those as primary keys and then you will generate direct method calls to lookup by primary key. However, I have other unique items in the view than the primary key and would like to look up by them also. Of course, I can just write that code, but if it were a Table, they would be generated from me because of the unique constraint. Would be great if I could identify that somehow also.

Unique constraints aren't settable for tables either, only if they're read from the table. This is also done for a reason. Setting the PK in the designer was a thing we had to do because the views were otherwise not usable as a true entity. Though it's also not a very solid thing, because it's uncertain if the view really returns 1 unique value per row for the PK set. With UC's this is true as well, if they're not read from the schema metadata.

First, let me thank you for your replies/comments. Now on to the good stuff

You allow me to set the PK for a view so I can use it as an Entity.

Now, that view also contains columns that could be used in a unique constraint (they came from a UC in a table).

I need to search the view based on that 'virtual' UC, but you provide no method to search using a view other than PK. I could use an EntityCollection, do a GetMulti which would return one result row, and then return that Entity.

However, I would rather put code into the Entity. I looked through all the code that is generated and created my own method to do the search. Does this look ok?

        public bool FetchUsingUCField1UCField2(System.String uCField1, System.Int32 uCField2, IPrefetchPath prefetchPathToUse, Context contextToUse)
        {
            IPredicateExpression selectFilter = new PredicateExpression();
            selectFilter.Add(MyEntityFields.UCField1 == uCField1);
            selectFilter.Add(MyEntityFields.UCField2 == uCField2);
            MyEntityDAO dao = (MyEntityDAO)CreateDAOInstance();
            dao.PerformFetchEntityAction(this, base.Transaction, selectFilter, prefetchPathToUse, contextToUse);
            bool fetchResult = false;
            if (base.Fields.State == EntityState.Fetched)
            {
                base.IsNew = false;
                fetchResult = true;
                if (contextToUse != null)
                {
                    base.ActiveContext = contextToUse;
                    IEntity dummy = contextToUse.Get(this);
                }
            }
            return fetchResult;
        }

If that is right, man this would be much easier if the generated base classes had a FetchAnyUC method (to avoid all this code repeat) that would simply look like:

        public bool FetchAnyUC(IPredicateExpression selectFilter, IPrefetchPath prefetchPathToUse, Context contextToUse)
        {
            MyEntityDAO dao = (MyEntityDAO)CreateDAOInstance();
            dao.PerformFetchEntityAction(this, base.Transaction, selectFilter, prefetchPathToUse, contextToUse);
            bool fetchResult = false;
            if (base.Fields.State == EntityState.Fetched)
            {
                base.IsNew = false;
                fetchResult = true;
                if (contextToUse != null)
                {
                    base.ActiveContext = contextToUse;
                    IEntity dummy = contextToUse.Get(this);
                }
            }
            return fetchResult;
        }

Not only would it help extensions like mine, you could call this method from the UC methods you already generate instead of duplicating the same code. (Again assuming they all generate code like this, which they appear to do, but I am no expert! )

Thoughts?

Also - there is always this 'option' to modify and generate my own code. So here is the question.

Can I put an extension into the GUI that let me specify field lists to be UniqueConstraints on views, so that the above could be generated?

The FetchUsingUC... methods are helper methods which accept field values, so you can directly pass a uc field value to the method. These methods are of course redundant. I mean, if I want to fetch a customer entity based on the companyname, I can also do:

CustomerCollection customers = new CustomerCollection();
customers.GetMulti(CustomerFields.CompanyName=="Solutions Design");
CustomerEntity c = customers[0];

(of course testing if there are results is probably a good idea )

You can add name-value pairs in the custom properties of entities, and you can consult these values at generation time in a template. So for example you can specify in the custom properties that field 'CompanyName' is a uc field, and in an include template, you can loop through these and generate code which works on these fields to fetch data.

You can automate this with a plugin. So you can write a simple plugin which scans your complete schema in the project (or entities or a file on disk, it's up to you) and which sets these custom properties if it needs to.