4.0.13.523 SD.LLBLGen.Pro.DQE.SqlServer.dll 4.0.13.0725 SD.LLBLGen.Pro.ORMSupportClasses.dll 4.0.13.406 SD.LLBLGen.Pro.ORMSupportClasses.Web.dll DotNet 4.0 vs2010 project Adapter template SQL Server 2008 R2

I read this thread (https://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=21055) but still don't understand this.

I have a legacy sproc that I will rewrite asap that has 50+ optional parameters. I want to use it in LLB until I have rewritten it (which will take weeks, once I can schedule it).

The Optional checkbox in the designer simply says the parameter can be null. I still need to supply 50 parameters, most of which will be null at any one time. Using C# 4 I can easily use option arguments, but the generated code doesn't do this. Is there a workaround?

Do you mean you want to submit default values for the optional parameters rather than nulls?

I don't want to submit any values if I don't have them.

ActionQuery.MySproc(searchText:"Find this")

instead of this

ActionQuery.MySproc(null,null,null,null,...x30,searchText:"Find this",null,null,x20)

Each call to this sproc could have anywhere between 1 and say 20 arguments depending on what the user needs to filter.

Each parameter is defaulted to NULL in the sproc and the body of the sproc generates dynamic sql based on what parameters have non null values. Yes it's horrible, but it works and has grown over the years to be a fundamental element of the database search.

I am looking forward to spending the time to write it using LLB. If you want nightmares, I have attached it.

There is no support for that. However it's easy to write a wrapper for the SPs that uses optional parameters and named arguments. You can convert those wrappers into a new template.

... it's easy to write a wrapper for the SPs that uses optional parameters and named arguments. You can convert those wrappers into a new template.

Anybody can provide some guidance? Where is the wrapper? What does it look like? How is it maintained when the sproc's signature changes? Do you know of an example somewhere in the documentation or example code?

Thanks

A wrapper as in: a second method which has named parameters, namely the ones which don't have a default value in the meta data.

So you generate (the wrapper): ActionQuery.MySproc(string searchText)

and that method calls:

ActionQuery.MySproc(null,null,null,null,...x30,searchText,null,null,x20)

which is the one already generated. The wrapper is generated with an additional template. We currently don't support this so you have to write the template yourself. You can do this with an include template bound to the templateid 'Custom_ActionProceduresTemplate', so it's included when the action stored procedures are generated.

It's been a few years and I don't know exactly what David was referring at but I think it was this.

Now the problem: there's no meta-data available which says 'this parameter has a default vaule of 'NULL''. We use SELECT * FROM INFORMATION_SCHEMA.PARAMETERS on sqlserver to obtain the meta-data of parameters of a proc, but that doesn't report whether a parameter has a default value or is optional. SSMS uses SMO which parses the header of a proc, and with that can determine whether it has a default value, but we don't use that and there's no other way through the database.

So the only way is that you know the procs and parameters which are optional and mark them as such in the designer with the SP call. (check the 'Is Optional' checkbox). You can then use this info for the template. But as they're not DB sourced, if the signature changes, it won't update the sp call in the project.

Thanks for the reply. I'm trying to follow instructions (as a new user I will postpone writing templates for now - small steps...):

So you generate (the wrapper): ActionQuery.MySproc(string searchText) and that method calls: ActionQuery.MySproc(null,null,null,null,...x30,searchText,null,null,x20)

1) Do you mean ActionProcedures ? I read the doc and the threads, it says a SPROC that does not return a resultset will be generated as part of class ActionProcedures ?!? 2) My SPROC does get generated as part of class ActionProcedures BUT, contrary to the examples I see in the doc and the threads it generated MySprocCallBack(...). Why does it generate the sproc code with the callback suffix? I saw no example in threads and no documentation about this (hence I can't use this).

Thanks

You should have both generated. The callbacks are delegates used in UnitOfWork

You are right - they are generated. Apologies. In summary, to keep things simple for now this is what I did: In the

#region Included Code

... of the generated

public static partial class ActionProcedures

... I wrote a new

public static int MySproc( ... 

... with a different signature, named parameters and default values. In my code I use this method. It seems to work fine. I guess I won't loose this code (on re-generation) since it's in the #region.

I'm sure this is not the best practise but it's quick and easy for a new user of LLBLGen (any additional advice to improve on this solution?).

Q: when I call it in my code, the method always returns -1. The SPROC has SET NOCOUNT ON and simply does RETURN (or throws an error). In this case is the returned -1 normal ? I tried RETURN 0 but it still does return -1. Is this normal ?

Thanks

FrankMonroe wrote:

You are right - they are generated. Apologies. In summary, to keep things simple for now this is what I did: In the

#region Included Code

... of the generated

public static partial class ActionProcedures

... I wrote a new

public static int MySproc( ... 

... with a different signature, named parameters and default values. In my code I use this method. It seems to work fine. I guess I won't loose this code (on re-generation) since it's in the #region.

I'm sure this is not the best practise but it's quick and easy for a new user of LLBLGen (any additional advice to improve on this solution?).

I'd go with a partial class instead. The region is supported and not overwritten, but it's easier if you e.g. create a folder in the project e.g. 'Extensions' and add all partial classes there so you know what code you added yourself: it's not hidden away in some region in a generated file.

Q: when I call it in my code, the method always returns -1. The SPROC has SET NOCOUNT ON and simply does RETURN (or throws an error). In this case is the returned -1 normal ? I tried RETURN 0 but it still does return -1. Is this normal ? Thanks

The value returned from the method is the # of rows affected. As you suppress rowcounting it will return -1. RETURN isn't supported on SQL Server: we aren't returning the value. We had it till v3.0 where we removed it: we generated 2 versions for every proc, one with and one without RETURN, but with adapter it became really problematic, as the # of proc methods generated became very large, so we remove it as it wasn't a feature used much. To return a value to calling code, please use an output parameter.