Hi,

I am trying to extend the TDL to have a token called TypeOfParameterName, which I want to use in ActionProcedures and RetrievalProcedures templates to check the type of parameter and generate different code for these.

So what I have done is ( I have added some already existing code in this post, so that it's possible for you to see where I have typed the new code):

In EnumConstants: in Token:

UserCodeRegionStart,
WhiteSpace,
TypeOfParameterName

in NonTerminalType:

UserCodeRegionName,
UserCodeRegionStart,
TypeOfParameterName

In the Parser: In ParseStatement():

case Token.UniqueConstraintIndex:
case Token.UserCodeRegionName:
case Token.TypeOfParameterName:
   // single token statement
   toReturn = HandleSingleTokenStatement(ref tokensInStatement);
   break;

In HandleUserCodeRegionStart():

case Token.TypedViewFieldName:
case Token.TypeOfParameterName:
   // valid argument. Pop it.
   tokensInStatement.Add(_tokenStream.Dequeue());

In HandleIfStart():

case Token.TypedViewFieldName:
case Token.TypeOfParameterName:
   // object type specified. Pop it.
   tokensInStatement.Add(_tokenStream.Dequeue());
   StripWhiteSpace();
   break;

In HandleCasingStatement():

case Token.TypedViewFieldName:
case Token.TypeOfParameterName:
   // pop it
   tokensInStatement.Add(_tokenStream.Dequeue());
   break;

In HandleSingleTokenStatement():

case Token.TypeOfParameterName:
   toReturn = new NonTerminal(NonTerminalType.TypeOfParameterName);
   break;

In CreateTokenDefinitions():

// ' ' (space) or TAB
_tokenDefinitions.Add(new TDLTokenDefinition((int)Token.WhiteSpace, @"[ \t]+", RegexOptions.Compiled | RegexOptions.IgnoreCase));
// 'TypeOfParameterName'
_tokenDefinitions.Add(new TDLTokenDefinition((int)Token.TypeOfParameterName, @"TypeOfParameterName", RegexOptions.Compiled | RegexOptions.IgnoreCase));

In the Interpreter:

In HandleNonTerminal:

case NonTerminalType.TypedViewFieldName:
case NonTerminalType.TypeOfParameterName:
   HandleNameStatement();
   break;

In HandleNameStatementToken():

case Token.TypeOfParameterName:
   if(_currentSPParameter!=null)
   {
      toReturn = _currentSPParameter.DotNetType.ToString();
   }
   break;

In HandleIfStringValueEqualsStart():

case Token.TypeOfParameterName:
   if(_currentSPParameter!=null)
   {
      nameOfObject = _currentSPParameter.DotNetType.ToString();
   }
   break;

Then I have compiled the code, everything compiled correct.

Then I copied the four DLL's AND the PDB's to the TaskPerformers directory of LLBLGen

The I have changed the procedure templates to contain this:

<[Foreach InputParameter CrLf]> 
    <[If StringValueEquals TypeOfParameterName "System.Guid"]>
        if(<[CaseCamel CurrentParameterName]> == Guid.Empty)
            parameters[<[ParameterIndex]>] = new SqlParameter("<[ActualParameterName]>", SqlDbType.<[TypeOfActualParameter]>, <[ParameterSize]>, ParameterDirection.Input, true, <[ParameterPrecision]>, <[ParameterScale]>, "",  DataRowVersion.Current, DBNull.Value);                            
        else
    <[EndIf]>
    <[If StringValueEquals TypeOfParameterName "System.DateTime"]>
        if(<[CaseCamel CurrentParameterName]> == new DateTime() )
            parameters[<[ParameterIndex]>] = new SqlParameter("<[ActualParameterName]>", SqlDbType.<[TypeOfActualParameter]>, <[ParameterSize]>, ParameterDirection.Input, true, <[ParameterPrecision]>, <[ParameterScale]>, "",  DataRowVersion.Current, DBNull.Value);                            
        else
    <[EndIf]>       
    parameters[<[ParameterIndex]>] = new SqlParameter("<[ActualParameterName]>", SqlDbType.<[TypeOfActualParameter]>, <[ParameterSize]>, ParameterDirection.Input, true, <[ParameterPrecision]>, <[ParameterScale]>, "",  DataRowVersion.Current, <[CaseCamel CurrentParameterName]>);
<[NextForeach]>

This creates code if the parameter is of type System.Guid or System.DateTime and the code generated should check if the parameter is Guid.Empty in case of this type and new DateTime() in case of the other type and enter DBNull.Value as the value into the SP.

This way I would get REAL DBNull Values into the database where I have a date or a Guid on my entities (since I give for example Project.CustomerObjNo as parameter which is a Guid

The code it generates:

parameters[6] = new SqlParameter("@OWNER", SqlDbType.VarChar, 20, ParameterDirection.Input, true, 0, 0, "",  DataRowVersion.Current, owner);

    
    
        if(customerObjNo == Guid.Empty)
            parameters[7] = new SqlParameter("@CUSTOMER_OBJ_NO", SqlDbType.UniqueIdentifier, 0, ParameterDirection.Input, true, 0, 0, "",  DataRowVersion.Current, DBNull.Value);                           
        else
    
            
    parameters[7] = new SqlParameter("@CUSTOMER_OBJ_NO", SqlDbType.UniqueIdentifier, 0, ParameterDirection.Input, true, 0, 0, "",  DataRowVersion.Current, customerObjNo);

And this is working great!!! Now I don't have to check the values of my Guids and Dates and I can use a normal strong typed template instead of changing all the parameter types to type 'object'. Which really sucks as a workaround.

Now after I have done this, I just have a simple question actually:

Which types should I check with which value to make sure that a correct DBNull value is inserted.

I mean: string.empty, will this enter NULL or an empty string in the database? And what is the best way to handle decimals, since the value 0 must be possible. How about other types?

My other question is: Will this be solved in the new 2005 version with Nullable Types?

Gr.,

G.I.

Hmm...after some thinking, I might have been a little enthousiastic about extending the TDL, though it was fun.

But it might be better just to create a function in the template which does stuff like this:

private Object SetDbObjectValueFromValue(string sourceValue)
{
   if(sourceValue == null)
     return (Object)DBNull.Value;
   else
      return (Object)sourceValue;
} 

change the line in the template to this:

parameters[<[ParameterIndex]>] = new SqlParameter("<[ActualParameterName]>", SqlDbType.<[TypeOfActualParameter]>, <[ParameterSize]>, ParameterDirection.Input, true, <[ParameterPrecision]>, <[ParameterScale]>, "",  DataRowVersion.Current, SetDbObjectValueFromValue(<[CaseCamel CurrentParameterName]>));

And create overloaded functions for int, decimal, guid, datetime, etc.

These I could check for example:

int.MaxValue, decimal.MaxValue, guid.empty, datetime = new DateTime() or datetime.MaxValue.

What seems to be the better idea? And which comparisons should I run?

And is there a simple way to set object values to these comparison values if they are null? I mean, if you have a decimal, it is default 0 on the object. Is there a way to make this default a decimal.MaxValue? Or should I still set this value also before I call the SP?

Gr.,

G.I.

You should indeed go for the function, which IMHO is less work and more flexible.

You then should define a set of 'magic' values which represent NULL for the various value types and in your function overloads (one per type) you should test on these magic values, like -1 for int, DateTime.MinValue for datetime etc.

In that case, is there someway to change the default NULL values that are used in LLBLGen when creating an object. I mean, when I create a project, this project will have a startdate and en end date. The start date for me always contains a value, but the end date not neccessarily have one.

When I use LLBLGen and get the data into an project entity, this entity will have 01/01/0001 as a default value for this. In this case that probably is the DateTime.MinValue. But for decimals the default is 0, and not decimal.MinValue.

Gr.,

G.I.

G.I. wrote:

In that case, is there someway to change the default NULL values that are used in LLBLGen when creating an object. I mean, when I create a project, this project will have a startdate and en end date. The start date for me always contains a value, but the end date not neccessarily have one.

When I use LLBLGen and get the data into an project entity, this entity will have 01/01/0001 as a default value for this. In this case that probably is the DateTime.MinValue. But for decimals the default is 0, and not decimal.MinValue.

Yes, these are changeable with the template SharedTemplates\C# (or VB.NET)\typeDefaultValueInclude.template

which is bound to the templateid: SD_TypeDefaultValueIncludeTemplate

So if you bind another file to that templateid with other defaults, you get different default values .