LLBLGen Pro 3.5 Final SD.LLBLGen.Pro.ORMSupportClasses.NET20.dll: 3.5.12.0417 SD.LLBLGen.Pro.DQE.SqlServer.NET20.dll: 3.5.12.0317 .NET Framework 3.5 SQL Server 2005

We are upgrading from LLBLGen Pro 2.6 (Build: 2.6.0.0.11052011) to 3.5 and experience problems with some stored procedures. After some investigation, we found out that the treatment of optional stored procedure parameters between V2.6 and V3.5 differs.

In both cases the generated RetrievalProcedures are called with value null in optional parameter(s). For example parameter ‘dagenVervallenVan’ in the following code .

///////////////////////////////////////////////////////////////
// This is generated code. 
//////////////////////////////////////////////////////////////
// Code is generated using LLBLGen Pro version: 3.5
// Code is generated on: maandag 4 juni 2012 16:22:18
// Code is generated using templates: SD.TemplateBindings.SharedTemplates.NET20
// Templates vendor: Solutions Design.
// Templates version: 
//////////////////////////////////////////////////////////////
using System;
using System.Data;
using SD.LLBLGen.Pro.ORMSupportClasses;

namespace Piramide.Bis.LLBL.SqlServer
{
    /// <summary>Class which contains the static logic to execute retrieval stored procedures in the database.</summary>
    public static partial class RetrievalProcedures
    {
        …
        …
        public static DataTable PrcCrediteurPost_selall(Nullable<System.Int32> bedrijfNummer, Nullable<System.Int32> crediteurNummerVA, Nullable<System.Int32> crediteurNummerTM, Nullable<System.Int32> werkNummer, Nullable<System.Int32> werkOrderNummer, Nullable<System.Int32> administrateur, Nullable<System.Int32> gelieerdCode, Nullable<System.DateTime> peilDatum, Nullable<System.Int32> peilPeriode, Nullable<System.Int32> dagenVervallenVan, Nullable<System.Int32> dagenVervallenTm 
, IDataAccessCore dataAccessProvider)
        {
            using(StoredProcedureCall call = CreatePrcCrediteurPost_selallCall(dataAccessProvider, bedrijfNummer, crediteurNummerVA, crediteurNummerTM, werkNummer, werkOrderNummer, administrateur, gelieerdCode, peilDatum, peilPeriode, dagenVervallenVan, dagenVervallenTm 
))
            {
                DataTable toReturn = call.FillDataTable();
                return toReturn;
            }
        }
        …
        …
    }
}

The executed SQL statements are (catched with SQL Server Profiler):

V2.6:
exec [BISNETF4_MAINDATA].[dbo].[PrcCrediteurPost_selall] @BedrijfNummer=33,@CrediteurNummerVA=1042500,@CrediteurNummerTM=1042500,@WerkNummer=default,@WerkOrderNummer=default,
@Administrateur=default,@GelieerdCode=default,@PeilDatum=default,@PeilPeriode=201205,@DagenVervallenVan=default,@DagenVervallenTm=default

V3.5:
exec [BISNETF4_MAINDATA].[dbo].[PrcCrediteurPost_selall] @BedrijfNummer=33,@CrediteurNummerVA=1042500,@CrediteurNummerTM=1042500,@WerkNummer=NULL,@WerkOrderNummer=NULL,
@Administrateur=NULL,@GelieerdCode=NULL,@PeilDatum=NULL,@PeilPeriode=201205,@DagenVervallenVan=NULL,@DagenVervallenTm=NULL

Is it an intentional change to use ‘NULL’ instead of ‘default’? If so, why is it not documented (for us it is a breaking change)?

What does the SP look like? Please post SP code.

It is rather large. Here is the parameter declaration part. The whole SP is attached.

ALTER procedure [dbo].[PrcCrediteurPost_selall]
(
   @BedrijfNummer int = null    -- Selectie op bedrijfsnummer (verplicht)
 , @CrediteurNummerVA int = null    -- Selectie op Crediteurnummer vanaf (optioneel)
 , @CrediteurNummerTM int = null    -- Selectie op Crediteurnummer t/m (optioneel)
 , @WerkNummer int = null       -- Selectie op Werknummer (optioneel)
 , @WerkOrderNummer int = null  -- Selectie op Werkordernummer (optioneel)
 , @Administrateur int = null   -- Selectie op Administrateur (optioneel)
 , @GelieerdCode int = null -- Selectie op Gelieerd Crediteur (optioneel)
 , @PeilDatum datetime = null       -- Selectie op Datum (optioneel)
 , @PeilPeriode int = 0         -- Selectie op Periode (optioneel)
 , @DagenVervallenVan int = 0       -- Selectie op Aantal vervaldagen van
 , @DagenVervallenTm int = 0        -- Selectie op Aantal vervaldagen t/m
)
as

[see attached file]

The problem occurs (for instance) in the part:

...
    where (@DagenVervallenVan = 0 or AantalDagenVervallen >= @DagenVervallenVan)
...

In V3.5 the parameter value null is used., in V2.6 default. V3.5: Comparing null with an (not null) int value results in no records selected. V2.6: The default value 0 is compared with 0 and results in true. The same code to call the generated RetrievalProcedure is used in both cases.

I can solve the problem (using null as default), but I am curious if this is an intentional change and about the documentation.

In both cases, the value of the method parameter is passed as the parameter value. In v2.6's case we use the SqlParameter ctor, in v3.x we use the 'Value' property. I have no idea what 'default' means in this case, it's definitely not something we emit / cause / intend, as we simply set the value of the parameter in the ctor (as you can see in the generated code in v2.6) with the method parameter passed in, and we do the same thing in v3, only differently, as in v3 we use the DbProviderFactory system, so we set the parameter's value with the .Value property.

My guess is that SqlParameter's ctor ignores a null value when passed into the ctor, but doesn't ignore it when .Value is set to null. Again, I don't know what 'default' means in the context of a SqlParameter, as it's not a 'default' value we set the parameter to.

So i.o.w.: an unintended change, however one we can't avoid.

In http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=6959 you wrote:

IF you define a parameter as NULL, the SqlClient of .NET thinks it's an optional parameter. Which is IMHO a choice they shouldn't make, but they have, so you've to live with it. In a way I can understand it, why specify NULL for a parameter to a proc? and also, nullable parameters are used in procs only for OPTIONAL parameters (most cases anyway).

So, if you want to have a nullable parameter, you've to specify a DEFAULT for the parameter in the proc definition.

So I thought you knew: IF you assing a parameter the value NULL (in the generated code), actually the value default is supplied as parameter value to SQL Server (see my posted executed SQL statement for V2.6)! That is the reason why I works the way you explained it. B.T.W.: By default, parameters are nullable in SQL Server. The ‘value’ default is a valid (T-SQL) value for a proc parameter value. See http://msdn.microsoft.com/en-us/library/ms188332(v=sql.90).aspx.

After a test with the (generated) V2.6 code, I found out that there is a difference in behavior between using NULL and DBNull.Value used as SqlParameter.Value:

• NULL: **default **is used as parameter value

• DBNull.Value: NULLis used as parameter valueThat may be the difference!

In the debugger I changed the parameter value of DagenVervallenVan from null to DBNull.Value before CallRetrievalStoredProcedure(), resulting in this catch (SQL Profiler):

exec BISNETF4_ERADATA.[dbo].[PrcCrediteurPost_selall] @BedrijfNummer=33,@CrediteurNummerVA=1042500,@CrediteurNummerTM=1042500,@WerkNummer=default,@WerkOrderNummer=default,
@Administrateur=default,@GelieerdCode=default,@PeilDatum=default,@PeilPeriode=201205,@DagenVervallenVan=NULL,@DagenVervallenTm=default

I did the same (but inversed: DBNull.Value to null) for V3.5 and the result was the same: * NULL: **default **is used as parameter value * DBNull.Value: NULLis used as parameter value Here the catched SQL (Profiler):

exec [BISNETF4_ERADATA].[dbo].[PrcCrediteurPost_selall] @BedrijfNummer=33,@CrediteurNummerVA=1042500,@CrediteurNummerTM=1042500,@WerkNummer=NULL,@WerkOrderNummer=NULL,
@Administrateur=NULL,@GelieerdCode=NULL,@PeilDatum=NULL,@PeilPeriode=201205,@DagenVervallenVan=default,@DagenVervallenTm=NULL

The change of behavior is between the V2.6 generated code and de V3.x ORMSupportClasses. In StoredProcedureCall.AddParameter() the parameter value is translated from null to DBNull.Value, resulting in calling the proc with null (V3.x) instead of default (V2.6) in SQL Server.

Well, in the past 6 years that little detail has slipped my mind apparently, but good you brought it up! That might indeed be the problem.

We'll look into this if we can fix this.

It's a problem which isn't easily fixed as the code to add the parameter is used by all databases, as well as the code to convert null -> DBNull.Value (which is actually needed for some db's).

I.o.w.: for the code it's unknown whether the parameter to create is for a proc call and whether it's for sqlserver (inside the StoredProcedureCall class, where the call to the parameter create method is made).

So we can't simply 'reset' the value in the proc parameter to 'null' if it's DBNull.Value, as for some ADO.NET provider's for some databases that's not going to work (hello ODP.NET). It might have been fixed in today's versions, but not in older ones. It's also documented in MSDN: DbParameter.Value:

When you send a null parameter value to the server, you must specify DBNull, not null. The null value in the system is an empty object that has no value. DBNull is used to represent null values.

As all ADO.NET parameter objects inherit from this, they can't change .Value to have different meaning, DBNull.Value is the null value, which is passed in as value in the method, not some magic value. What's stupid is that for SQL Server, DBNull.Value is apparently not seen as 'undefined' when calling a proc, so the default is used when an undocumented (!) feature is used. Especially since SqlParameter.Value specifies the exact same quote!

For input parameters, the value is bound to the SqlCommand that is sent to the server. For output and return value parameters, the value is set on completion of the SqlCommand and after the SqlDataReader is closed.

When you send a null parameter value to the server, the user must specify DBNull, not null. The null value in the system is an empty object that has no value. DBNull is used to represent null values.

An exception is thrown if non-Unicode XML data is passed as a string.

So we have to make an adjustment for SQL Server specific and also for procs specific, which isn't doable without changing method signatures and introducing virtual methods (as all logic used for creating the actual parameter object is shared among all db's). This means it's change across assemblies. So a breaking change and we won't go that far during a release cycle.

It's a problem though, however not one we can solve at this point. (it's actually a dirty hack in MS' code IMHO: specify 'null' and you get 'default', specify DBNull.Value (their default for 'null' EVERYWHERE in ado.net!) and you get 'null'. However inserting a row into a table and specify DBNull.Value, you will get the default constraint value defined on the column...

So in your proc do: where (COALESCE(@DagenVervallenVan, 0) = 0 or AantalDagenVervallen >= COALESCE(@DagenVervallenVan, 0))

I don't know whether this is doable for you with respect to the amount of time it will take to adjust the procs. (it simply comes down to moving the default from the proc header to the code itself).

So it's a breaking change for v3.x in the code, so we'll update the docs. It's also a breaking change to 'fix' it, so we can't do that today, mid-release.

If you absolutely need a fix for this, because your project otherwise fails (e.g. you have to adjust 10K procs which will take weeks), please let us know and we'll see what we can do.

You wrote:

What's stupid is that for SQL Server, DBNull.Value is apparently not seen as 'undefined' when calling a proc, so the default is used when an undocumented (!) feature is used.

No, it is seen as NULL, which is a valid parameter value. Slightly different with default, meaning "is missing". The value resulting in default is indeed a undocumented. So, V2.6 uses an undocumented feature to make it possible to use (SQL Server) proc defaults!? I now (even more) understand why it is not wise to use undocumented features.

Anyway, I do not ‘absolutely need a fix’ right now. The number of procs are limited (about 60) and only those with a default other than NULL must be changed (the proc defaults where use as ‘no filter’ indicator).

You wrote:

It's also a breaking change to 'fix' it, so we can't do that today, mid-release.

Will it (ever) be ‘fixed’? Because then, I don’t have to make the effort to change the procs.

Peter wrote:

You wrote:

What's stupid is that for SQL Server, DBNull.Value is apparently not seen as 'undefined' when calling a proc, so the default is used when an undocumented (!) feature is used.

No, it is seen as NULL, which is a valid parameter value. Slightly different with default, meaning "is missing".

But in both cases a value is specified, namely null vs. DBNull.Value. I'd agree with you if the .Value property wasn't set at all in the case of 'null'. But it is set to a value, namely the value it's supposed to be set

I do see why this is needed in some cases btw, namely in the situation where you have a default defined but you want to override it and pass in null. In the situation where both null and DBNull.Value lead to 'default' this is never possible. That's the only situation I can think of in which this might be necessary. But then one wonders: why define the default in the first place if it's not mandatory .

The value resulting in default is indeed a undocumented. So, V2.6 uses an undocumented feature to make it possible to use (SQL Server) proc defaults!? I now (even more) understand why it is not wise to use undocumented features.

Anyway, I do not ‘absolutely need a fix’ right now. The number of procs are limited (about 60) and only those with a default other than NULL must be changed (the proc defaults where use as ‘no filter’ indicator).

Ok, so it's relatively minor. That's 'een geluk bij een ongeluk '

You wrote:

It's also a breaking change to 'fix' it, so we can't do that today, mid-release.

Will it (ever) be ‘fixed’? Because then, I don’t have to make the effort to change the procs.

It will be fixed, in v4, where we have the freedom to change method signatures in multiple places. However v4 isn't scheduled to arrive this year, so I wouldn't wait for it if you need it this year.

OK, I will change the procs.

You wrote:

But then one wonders: why define the default in the first place if it's not mandatory .

It is not mandatory, but it is not possible to not fill in this parameter in the generated code, because only one method with all parameters is generated to access the proc. I don’t know if it is possible to have more than one version of a proc generated in LLBLGen: one with all parameters and one (or more) with several parameters. I thought maybe the column “Optional” in the designer has something to do with this. But now I am wondering: what IS the functionthat column? I can’t find much documentation about it. Is it only needed to make possible to have nullable parameter in the generated code? Then, I don’t understand the name. I woud expect 'Nullable'.

Peter wrote:

OK, I will change the procs.

You wrote:

But then one wonders: why define the default in the first place if it's not mandatory .

It is not mandatory, but it is not possible to not fill in this parameter in the generated code, because only one method with all parameters is generated to access the proc. I don’t know if it is possible to have more than one version of a proc generated in LLBLGen: one with all parameters and one (or more) with several parameters. I thought maybe the column “Optional” in the designer has something to do with this. But now I am wondering: what IS the functionthat column? I can’t find much documentation about it. Is it only needed to make possible to have nullable parameter in the generated code? Then, I don’t understand the name. I woud expect 'Nullable'.

Optional is used throughout the designer for 'nullable' at the model level, so we kept it consistent with the proc call as well. It's a little ambiguous perhaps in that context, however the parameter being 'null' by default is (to my knowledge) called an 'optional parameter' in the proc.

The problem is that C# 2.0 doesn't have optional parameters, so we can't define a method signature with the parameters as 'optional', only make them nullable. So in the ultimate case, it would be nicer to have all 'optional' parameters of the proc call to be generated as 'optional' parameters in the method but as not all supported languages support that, we couldn't do that. Instead we made them nullable, as in most cases the 'optional' parameter in a proc is used in the context of a nullable parameter.

I think I understand it now. Optional proc parameters are implemented as nullable method parameters, because of limitations.

You wrote:

… however the parameter being 'null' by default is (to my knowledge) called an 'optional parameter' in the proc.

I don’t agree. If you define a stored procedure parameter you have to supply a value to execute the proc, except if a default is defined. So the parameter is optional because of having a default. All parameters are nullable. If a default is used, that can be anything (including null).

aha, there is the root of the confusion on my part. Thanks for clearing that up, makes sense