Stored procedure code generation options..
Joined: 10-Mar-2006
Otis, we have had several discussions around stored procedures in the last few days. I have a few more questions for you:
<Edit>
I apologize for the length/detail of this message 
</Edit>
Before LLBLGenPro, I rolled my own data access layer using a custom built template and CodeSmith. It was 100% stored procs. Now, in moving over to your product (which is great) I have a few needs.
I my templates, when I specified a stored proc to generate code for, I would specify what kind of method and return type I wanted for that proc. Here were the options I had:
1) Nothing. This was equivalent to your ActionStoredProcedures.
2) DataSet. This was basically equivalent to your RetrievalStoredProcedures.
3) DataReader. This was the same as #2, except would return a DataReader, which was necessary in certain circumstances. Caller would of course have to close the connection.
4) FirstColumnOfFirstRow. This caused the code to use the optimized ExecuteScalar when calling the proc. The procedure would then return this as the result.
5) FirstOutputParam. This is close to the same as what you do, except in this case, the code would return the value of the first output parameter in the call to the proc. (generally designed for use in procs with one output parameter) and that resulted in less code for a very common situation: int myvalue=0; callsomeproc_regularly(param1, param2, ref myvalue); something.visible=myvalue;
was turned into this:
something.visible = callsomeproc_regularly(param1, param2);
6) FalseOnException. this would cause the call to the proc to be wrapped up in a try/finally and if the call failed would return false, if succeeded, would return true. This was helpful for procs that regularly raised exceptions when something went wrong in the proc. The user did not have to do the full try/finally wrap of every call. This tied in with the next item.
7) This was not a return type, but instead on option on any particular stored procedure. This option cas called ExceptionInOutParameter. That would add a out string errorStr to the method that was generated. The code would wrap the call and put the result of the exception in the output parameter. This GREATLY reduced the burden on calling stored procs that would do a 'raiserror' with the reason it failed in the proc - thereby letting you return that error easily to the end user:
string myError="";
if (!callsome_storedproc(param1, param2, out myError))
{ SomeLabel.Visible=true; SomeLabel.Text=myError; }
Anyway, now that you have a glimpse into the code I was generating, I now 'miss' some of that functionality and wonder how I might add that into your product. Basically, I would need to specify options on each stored procedure then I could modify the template that generates the code to have this additional functionality.
Preferably, I would like to set this in the GUI on each proc. Can that even be done?
How would I specify which proc is to be called which way? Obviously not all options are applicable for every proc - like for 2,3&4 proc must return a result set. For #5 it must have at least one output param, etc.
Joined: 10-Mar-2006
Yes, this is very specific for the user, and other users would want different options, but the set of realistic useful options is pretty limited.
4) FirstColumnOfFirstRow. This caused the code to use the optimized ExecuteScalar when calling the proc. The procedure would then return this as the result.
Isn't that the same as an actionprocedure with a returnvalue? ExecuteScalar isn't more optimized than the others, it simply also executes ExecuteReader (as the others do) below the surface.
No, it is not. A stored procedure can only return an integer, and that is generally designed as a status value. The FirstColumnOfFirstRow could be any datatype.
Also, I have to disagree with you on ExecuteScalar. While they generate the same code to the database, they are much different after that. In execute scalar, you do not have to have an adapter - because it just grabs the first column and first row of a result - it does need to adapt the data to do a fill into a datatable or anything. So it has to be faster! 
The stored procedure templates are rather small and pretty straight forward. Though they're still TDL. If you need maximum power, you could consider .lpt templates, which are similar to codesmith templates. It would require a bit of porting from the code that's currently in the proc templates, but it's pretty minor, as the bulk code is stored in the dbutils class which contains the proc call routine.
Minor to you, major to someone with the SDK documentation reading it for the first time
You say the stored proc templates are TDL - is that why there is not a place for user code in what is generated for the procs now (I am guessing they did not have support for that)?
Is this not something you have converted or are converting for 2.0 that I could have as a starting place? 
I am looking into the SDK now - be prepared for more questions! 
Joined: 10-Mar-2006
WayneBrantley wrote:
4) FirstColumnOfFirstRow. This caused the code to use the optimized ExecuteScalar when calling the proc. The procedure would then return this as the result.
Isn't that the same as an actionprocedure with a returnvalue? ExecuteScalar isn't more optimized than the others, it simply also executes ExecuteReader (as the others do) below the surface.
No, it is not. A stored procedure can only return an integer, and that is generally designed as a status value. The FirstColumnOfFirstRow could be any datatype.
Also, I have to disagree with you on ExecuteScalar. While they generate the same code to the database, they are much different after that. In execute scalar, you do not have to have an adapter - because it just grabs the first column and first row of a result - it does need to adapt the data to do a fill into a datatable or anything. So it has to be faster!
I was talking about the performance underneath. All calls on a command in ado.net uses executereader below the surface.
Of course, using a dataadapter of some sort is slower, I wasn't talking about that.
The stored procedure templates are rather small and pretty straight forward. Though they're still TDL. If you need maximum power, you could consider .lpt templates, which are similar to codesmith templates. It would require a bit of porting from the code that's currently in the proc templates, but it's pretty minor, as the bulk code is stored in the dbutils class which contains the proc call routine.
Minor to you, major to someone with the SDK documentation reading it for the first time
No, user code regions are there for extensibility. As the proc calls are static methods, there's no logic in placing user code regions there, they're not helping in letting you extending the generated code. TDL is the template language used for the shipped templates. TDL is limited, but has the advantage that it's not an imperative language, so it doesn't let you make mistakes or bugs very easily, so debugging isn't that necessary
WayneBrantley wrote:
First problem. There appears to be no way to determine if a StoredProcedure has Output parameters?
There are: HasInputAndOutputParameters (which requires both) HasParameter (which means any parameter or just any input parameters)
As I said, it's pretty limited, as in: it does what it needs to do. To loop through output parameters use foreach output parameter...
Also, I have all the template sources and such from the download area. However, I cannot find any lpt templates. TemplateStudio is installed also. They all seem to be TDL. Perhaps I did not download something?
If you want lpt templates, you can for example look at our subversion repository:
svn://www.sd.nl/LLBLGenPro/Templates
.lpt templates work as Codesmith templates (with a little difference in how to declare assembly references etc. but the <% %> stuff is the same). You can access the complete object model available in a project, which is described in the SDK reference manual. In there you can for example see that to loop through the action procedure call objects, you simply do:
<%
Project currentProject = _executingGenerator.ProjectDefinition;
foreach(SPCallDefinition spCall in currentProject.ActionSPCalls)
{
%><%=spCall.ElementTargetName%><%
}
%>
Which will print all action procedure's real names (catalog.schema.procname).
If you want to start simple, I'd suggest to look at the current proc templates and see how you can alter these to generate the code you want, for example by copying one version and alter that one to meet your requirements