What would be great if I could name my stored procedure something like:

MyEntity_GetPrice(@myentityid int, @someotherproperty int, @currentdate datetime).

Then the code generation would see a proc matching [entityname]_procname and add that procedure as a METHOD of the entity. It would evaluate the names of the parameters and if the names of the parameters matched fieldnames of the entity, would NOT pass them as parameters, but assign them from the current object.

So the above would produce a method on the MyEntity object named GetPrice something like this: (assuming, myentityid and someotherproperty were field names in MyEntity)

DataTable GetPrice(currentDate DateTime){ assign param @myentityid=this.myentityid; assign param @someotherproperty=this.someotherproperty; assign param @currentdate=currentDate; call stored proc }

perhaps some hooks in there were derrived entities can get involved in the proc call after the assignments but before the call to the proc and after the call to the proc?

This would be great.

Nothing stops you from writing an .lpt include template to generate these calls into an entity

While that is true, what is stopping me is I have only been using your product for a few days (but a lot of time in those days). I am not extremely familar with it and have just now downloaded all the template stuff, much less started to read about it.

In what little looking around I have done, it does not seem there are great 'hooks' into the LLBLGen designer, other than running an add in. Some of the stuff I am suggesting would need to store user settings, compare against changes, etc - all the stuff you already have done.

I would think it would be a fantastic feature and something you would like to be in the product. I would guess you have no plans for enhanced support for these things?

To be honest: we had that plan, but we dropped it for v2, because the reliance on a resultset with respect to the format of the entity is simply too fragile: it's uncertain what a proc can and will return.

The thing is: if you have to obey 10 or more rules for each proc to make it work, it will likely be used by a very small group of people, as most people who have to work with procs for persistence use procs written by a DBA, who writes the procs as he/she pleases, not as some external tool likes them to be.

So we decided to invest our time in other features than a fragile support for procedures other than the support we have now.

I of course completely disagree. It seems you have taken an extreme view of this situation. Instead consider that there are a great MANY procs that we create/control and not supporting procs with the same level of features as the rest of your product really hurts us (and the product - which I love and am only trying to improve it to my situations!). Especially if you look into the future where .net code is in the proc!

I am not sure what you mean by 'reliance on a resultset with respect to the format of the entity is too fragile'. Please clarify.

Please, please, please - give me some light at the end of the tunnel concerning procs!

(edit: ) For v2 we consider low-level methods to fill entities with a call to a proc. How this is done and what thick rulebook the procedure has to obey to isn't set in stone yet (other features have higher priority at the moment). If support for procs was easy and reliable, it would have been implemented a long time ago. I hope you understand that. Also you've to understand that initially llblgen pro was meant to be solely using procedures for persistence logic however this turned out to be not workable at all and we had to drop 3+ months of work and start over. These things happen, but I'd like you to know that we're not refusing proc support just because it might look like we don't like procs, but because it will very likely result in problems for our users and we don't like that to happen.

WayneBrantley wrote:

I of course completely disagree. It seems you have taken an extreme view of this situation. Instead consider that there are a great MANY procs that we create/control and not supporting procs with the same level of features as the rest of your product really hurts us (and the product - which I love and am only trying to improve it to my situations!). Especially if you look into the future where .net code is in the proc!

You can trust me: .NET code won't be in the procedures. Oracle developers (that is: developers of Oracle db engine) told me that not a lot of people use Java in stored procs on Oracle, despite the effort they've put into it. For some 'fire and forget' procs, it might be helpful, but in most cases, it's not useful: SQL is much more powerful. I would be very surprised if this would be different on Sqlserver. Of course, a function here and there might be CLR code, but compared to the SQL part, that's pretty minor. Add to that the cumbersome way how .NET code has to be maintained in sqlserver and I can assure you most DBA's will definitely move away from .NET code in sqlserver.

DataAccess can be done in various ways. Using an O/R mapper which generates SQL is one, using a pre-defined DAL with procs is another. Mixing the two is a marriage doomed to fail, and if you don't believe me, that's fine, but I've spend months of work on proc support alone so I know a little about what kind of problems one might run into.

It's easy to say: "just support procedures", but it's in the details where this goes wrong. For example, the procedures have to obey a large set of strict rules: not obeying one detail of these rules and the system falls apart. For example, how would you write the procs for a prefetch path of 4 nodes deep? With filters per node, sort clauses per node etc. ?

Data processing procedures which return resultsets can still be used: call the proc via the retrieval proc call mechanism in llblgen pro. Ok, you can't fill an entityset with it, but who combines dataprocessing mechanisms with data retrieval mechanisms?

I am not sure what you mean by 'reliance on a resultset with respect to the format of the entity is too fragile'. Please clarify.

Stored procedure returns resultset with fields F1, F2, F3 and F4. You want to push that into a set of entities of type E which has fields F1, F2, F3. Be aware: these fields are based on the table/view the entity is mapped on. Now, the proc gets changed and it returns F1, F2, A3 and F4. Poof... a crash at runtime and it's not easy to tell where it broke. The runtime will likely hold up and fill F1 and F2 in the entity, but won't fill F3 as that's not in the resultset. So this leads to unexpected behavior in the application. The worst kind of errors you can run into.

This is even worse when you consider hierarchical entities, i.e. entities which are in an inheritance hierarchy. The fields are aligned in a special way, so the fields can be used to determine the type of the entity to instantiate. This makes writing a proc for that even more cumbersome. Not in the beginning of the project perhaps, but after a year or so in production when a feature has to be added, no-one knows these tiny little details and it's likely you run into these problems. There's no way to determine this earlier than at runtime because there's no way to pre-check the resultset with the mapping data. Ok, with running the proc, but that's not always possible if the proc alters data, you don't want to run procs which alter data to test resultset mappings.

Hi Franz,

Something that would be nice for me is the ability to group stored procs.

For example, I have a legacy application with about 300 stored procs. When I get the time, I'll be updating it to use entities etc. In the meantime however, it would be nice to be able to do something like the following:

DataTable resultSet = HotelProcedures.SelectHotelById(300);

or 

RateProcedures.UpdateRates(agent, ratesXml);

It would just make things a bit easier to access

Cheers, Pete

PS: Apologies for hijacking the thread. It didn't seem worth starting a new one

Thanks for your responses Otis. I know you have been down this road before, just keep it in mind as I know you do.

Data processing procedures which return resultsets can still be used: call the proc via the retrieval proc call mechanism in llblgen pro. Ok, you can't fill an entityset with it, but who combines dataprocessing mechanisms with data retrieval mechanisms?

I just wanted to throw a quick example by you. Off of the top of my head, I can think of 7 examples of something like this we currently do. Here is a simple one:

I need to determine the complete amoritization for a loan. So, I have a stored procedure that you pass the LoanID into and it returns a table that represents the amoritzation table. Obviously to achieve this you HAVE to combine dataprocessing with data retrieval - since I am generating the data we are retrieving!

There are other examples, where you combine data retrieval with data processing, because it is so much faster on the server side, when it is not as simple as just doing some where clause. As a workaround on this particular one, I could probably rework it on the server side to be a User Defined Function. I then could call that function from a View and in turn map that view in your product.

Just keep it in mind!

Wayne

To perform a call to a function in a where clause, you could think of implementing IPredicate or derive a class from Predicate and simply emit the call to the function in the ToQueryText override. This can then be used in normal code with a normal llblgen pro fetch. But I understand what you mean. It's on the table, a lot of discussion about this topic is currently going on for v2's featurelist, and I'm not decided yet what the end-result will be. Fact is that there will be some kind of low-level, expert focussed, way of fetching data using procs, but how flexible that will be is not determined yet.

Thanks for the example, it gives me a good user-case for extensions to the current framework.