Hi All,

Currently I'm sekecting DAL tool for the next project. So I'm evaluating LLBL Gen Pro. Please sorry fo stupid questions.

1. We need generate typed resultset for the stored procedure calls. All stored procedures always returns same resultset. We dont need and dont like DataTable. So, is it possible to generate typed resultsets for the procedure calls?

2. We need use stored procedures to fetch/update/delete some entity types. Is it possible?

3. I've modified the task queue, disabled CommonBaseClassGenerator. So, the CommonBaseClass is not generated but my test entity class still inherits it. What I did wrong?

4. Where is templates language description. I've not found it in the CHM.

Thanks.

Tom3m wrote:

Hi All,

Currently I'm sekecting DAL tool for the next project. So I'm evaluating LLBL Gen Pro. Please sorry fo stupid questions.

1. We need generate typed resultset for the stored procedure calls. All stored procedures always returns same resultset. We dont need and dont like DataTable. So, is it possible to generate typed resultsets for the procedure calls?

No, as the determination of the resultset fields (and types of the columns) is not reliable on any database other than firebird, so we don't support that. Some tools from microsoft like the linq to sql designer and the entity framework designer show some resultsets of procs, but this is misleading: there's no other way to determine the resultset of a proc than the SET FMTONLY OFF/ON route, which isn't reliable.

1. We need use stored procedures to fetch/update/delete some entity types. Is it possible?

No, you have to use projections in code to do that.

1. I've modified the task queue, disabled CommonBaseClassGenerator. So, the CommonBaseClass is not generated but my test entity class still inherits it. What I did wrong?

The templates aren't that clever the inheritance is hardcoded in the template.

1. Where is templates language description. I've not found it in the CHM. Thanks.

It's in the SDK which is available to customers.

I see you're strongly focussed on stored procedures, we don't support stored procedures for persistance of entity instances directly. I'm not sure if this automatically rules us out as a tool for you to consider, however, you could also look at ways to use our dynamic sql, which is always parameterized, and tailored towards the task at hand, so often more optimal than a proc can be.

No, as the determination of the resultset fields (and types of the columns) is not reliable on any database other than firebird, so we don't support that. Some tools from microsoft like the linq to sql designer and the entity framework designer show some resultsets of procs, but this is misleading: there's no other way to determine the resultset of a proc than the SET FMTONLY OFF/ON route, which isn't reliable.

Actually you should take in to account thati n all serious projects there are a lot of legacy code, imagine if we've e.g. 1000 stored procedures. We cannot rewrite it in one day. We need some time for it. And we need a roadmap. We need evolutionary approach. Unfortunately you don't support strongly typed SP and entity updates using SP ( So, we will not be able use LLBL Gen pro, UNFORTUNATELY, because in general the product is excelent). IMHO If Microsoft has such a feature (generating strongly typed resultset) then you should also have it.

Thanks for your answer

Tom3m wrote:

No, as the determination of the resultset fields (and types of the columns) is not reliable on any database other than firebird, so we don't support that. Some tools from microsoft like the linq to sql designer and the entity framework designer show some resultsets of procs, but this is misleading: there's no other way to determine the resultset of a proc than the SET FMTONLY OFF/ON route, which isn't reliable.

Actually you should take in to account thati n all serious projects there are a lot of legacy code, imagine if we've e.g. 1000 stored procedures. We cannot rewrite it in one day. We need some time for it. And we need a roadmap. We need evolutionary approach. Unfortunately you don't support strongly typed SP and entity updates using SP ( So, we will not be able use LLBL Gen pro, UNFORTUNATELY, because in general the product is excelent). IMHO If Microsoft has such a feature (generating strongly typed resultset) then you should also have it.

Actually, we did

We removed it because the results were unreliable: to determine the resultset of a proc, the proc has to be 'executed' using SET FMTONLY ON. This doesn't really 'execute' the proc, however it still executes external stored procs. This could harm databases. Furthermore, SET FMTONLY ON;exec proc;SET FMTONLY OFF; also crashes often, for example when a temptable is used, or doesn't give proper results if multiple selects are inside the proc .

All in all, we found the results too unreliable so the user couldn't build on the feature and we couldn't do anything about it. As MS uses the same path, they too have these problems. On other databases it's even worse, as no resultset info is possible.

I understand your point about a migration path. We do support entity fetching by procs through projections, though the projection has to be written manually. Persisting isn't supported, there, you too have to map field values to parameters manually, though the call is 1 line of code.

Actually, we did

We removed it because the results were unreliable: to determine the resultset of a proc, the proc has to be 'executed' using SET FMTONLY ON. This doesn't really 'execute' the proc, however it still executes external stored procs. This could harm databases. Furthermore, SET FMTONLY ON;exec proc;SET FMTONLY OFF; also crashes often, for example when a temptable is used, or doesn't give proper results if multiple selects are inside the proc .

What have I did right now. I've start sql2005 profiler and add all my stored procedures in the EF designer. There is no FMTONLY in the trace.

**So, my general question, why Microsoft could generate strongly typed resultsets but you're not? **

All in all, we found the results too unreliable so the user couldn't build on the feature and we couldn't do anything about it. As MS uses the same path, they too have these problems.

Ok, I'm agreed that there could be complex cases. So, you should give ability to manually create/specify resultset class. I'm sure that it will be possible to generate typed resultset in 95% cases but in rest 5% cases users will be able write typed class themself.

In other databases it's even worse, as no resultset info is possible.

Please lets talk now about MS SQL. I know you support different databases but currently I'm not ready speak about other databases (dont have sufficient knowledge)

Tom3m wrote:

Actually, we did

We removed it because the results were unreliable: to determine the resultset of a proc, the proc has to be 'executed' using SET FMTONLY ON. This doesn't really 'execute' the proc, however it still executes external stored procs. This could harm databases. Furthermore, SET FMTONLY ON;exec proc;SET FMTONLY OFF; also crashes often, for example when a temptable is used, or doesn't give proper results if multiple selects are inside the proc .

What have I did right now. I've start sql2005 profiler and add all my stored procedures in the EF designer. There is no FMTONLY in the trace.

**So, my general question, why Microsoft could generate strongly typed resultsets but you're not? **

I do recall that I've seen a trace here on sql2000 with FMTONLY on, but it might have been linq to sql, not sure. When I try it on sqlserver 2005 adventureworks, I see no fmtonly on, but I also don't see any resultset definition of any proc. I can map an entity onto a proc, but there's no way I can specify that field X is to be mapped onto column 4 of the resultset. For example I can define a function import for proc P and map two totally random entities onto P so there's no checking whatsoever. Which is logical, as it's not possible. Linq to Sql does have the resultsets, but as said it's not reliable.

All in all, we found the results too unreliable so the user couldn't build on the feature and we couldn't do anything about it. As MS uses the same path, they too have these problems.

Ok, I'm agreed that there could be complex cases. So, you should give ability to manually create/specify resultset class. I'm sure that it will be possible to generate typed resultset in 95% cases but in rest 5% cases users will be able write typed class themself.

For v3 which is currently in development we're toying with that idea, though it doesn't have a high priority. The main problems are: multi-resultset procs and the high failure rate at runtime for this: the person who defines the mappings has to know the resultset of the proc. This is tedious, as it might very well be that the person isn't aware of that resultset, and also might not be allowed to execute the proc.

We could opt for the proc map route, however it gives a lot of problems for entity fetches/persistence. For example fetching entities in a hierarchy, using prefetch paths isn't possible. Fetching entities using a filter, it's not possible. So it's severily limited, to the procs. On the other hand, it indeed could help some people who still have to work with procs.

In other databases it's even worse, as no resultset info is possible.

Please lets talk now about MS SQL. I know you support different databases but currently I'm not ready speak about other databases (dont have sufficient knowledge)

we have to look at other databases as well . It's a bit of a shame that so many databases have so little meta-data information available and even keep it up to date (MS SQL for example doesn't keep view meta-data up to date when tables change. )... I'm sorry that I don't have a more positive story for you, perhaps later this year, but that's not a solution today of course.