I sort of found what I was looking for with this post http://llblgen.com/TinyForum/Messages.aspx?ThreadID=2929 but then realized that it was for version 1.x. Is there a similar fix that is posted for v2 that will let me pass null values to my stored procedures?

Using the LLBLGen Pro Designer: 1- Open the stored proc. "Edit/Properties" 2- Select the parameters tab 3- Select the parameter you wnat and check the IsNullable field.

A nullable type will be generated instead.

(edit)

The following was copied from the docs:

To mark a parameter as a nullable parameter, you can check its Is nullable checkbox. A stored procedure's parameters are generated as method parameters for the generated method representing the stored procedure call in code. When the .NET type of the parameter is a ValueType, it will be generated as a method parameter of type Nullable<T> / Nullable(Of T) when .NET 2.0 is the target platform. When the target platform is .NET 1.x, the .NET type of the parameter in the generated stored procedure call method's signature will be object.

I initially tried setting all the parameters to IsNullable in the designer, but when I call my stored procedure and pass it null values, I get a Sql exception stating that the stored procedure expects a parameter, and it was not passed to it. Is llbl passing null to the stored prodecure, or not even setting the value to null and not passing anything to the stored procedure?

Turn diagnostics on, then when you debug you can see the queries it generates in the Output window.

    <system.diagnostics>
        <switches>
            <add name="SqlServerDQE" value="4"/>
            <add name="ORMGeneral" value="0"/>
            <add name="ORMStateManagement" value="0"/>
            <add name="ORMPersistenceExecution" value="3"/>
        </switches>
    </system.diagnostics>

See docs for details - I leave this on when debugging.

I turned debugging on and tested with code that worked, and it output the queries just fine. When I tried the call to RetrievalProcedures.x that was throwing an exception it did not output anything about the stored procedure call, and after looking into the documentation I don't believe there is support for that. I'll probably have to contact support about this, but has anyone else had success getting null passed to a retrieval stored prodecure? My call is acting like nothing is even sent to the sql server for parameters. Here's the error:

Procedure or Function 'fx_AssociateSearch' expects parameter '@DistrictMode', which was not supplied.

Are you using .Net 1.1 or .Net 2 code?

Walaa wrote:

Are you using .Net 1.1 or .Net 2 code?

.Net 2

When you specify a parameter as Nullable, the generated code generates Nullable<type> parameters for the method to call the proc (see the generated code).

You then can pass a normal value OR null.

So I did a small test with nullable datetime parameters. I indeed got the same error: Unhandled Exception: System.Data.SqlClient.SqlException: Procedure 'Employee Sales by Country' expects parameter '@Beginning_Date', which was not supplied.

Silly, as the code does supply this parameter. I pass 'null' as the value for the parameter, not some nullable structure, as Nullable<DateTime> which you set to null, is null.

(edit). OK!

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.

example: Northwind proc, first the initial version, which will crash with a nullable parameter IN CODE:

ALTER  procedure "Employee Sales by Country" 
@Beginning_Date DateTime, @Ending_Date DateTime AS
SELECT Employees.Country, Employees.LastName, Employees.FirstName, Orders.ShippedDate, Orders.OrderID, "Order Subtotals".Subtotal AS SaleAmount
FROM Employees INNER JOIN 
    (Orders INNER JOIN "Order Subtotals" ON Orders.OrderID = "Order Subtotals".OrderID) 
    ON Employees.EmployeeID = Orders.EmployeeID
WHERE Orders.ShippedDate Between @Beginning_Date And @Ending_Date

now the altered one which has optional parameters which does work with nullable parameters defined in llblgen pro:

ALTER  procedure "Employee Sales by Country" 
@Beginning_Date DateTime = NULL, @Ending_Date DateTime = NULL AS
SELECT Employees.Country, Employees.LastName, Employees.FirstName, Orders.ShippedDate, Orders.OrderID, "Order Subtotals".Subtotal AS SaleAmount
FROM Employees INNER JOIN 
    (Orders INNER JOIN "Order Subtotals" ON Orders.OrderID = "Order Subtotals".OrderID) 
    ON Employees.EmployeeID = Orders.EmployeeID
WHERE Orders.ShippedDate Between @Beginning_Date And @Ending_Date

Wow...for the first time, I actually read about an error I was about to get while implementing the code that was about to make the error

What I would like to inquire about is Diagnostic support for StoredProcedure calls. As you are aware, nothing is output in Diagnostics for these calls or for code like this:

ResultsetFields fields = new ResultsetFields(1);
fields.DefineField(new EntityField("AFld", expressionToApply), 0);
TypedListDAO dao = new TypedListDAO();
dao.GetScalar(fields, null, null, null, null);

Can we get this added?

Enable tracing for ORMPersistenceExecution to level 4. It should show the proc name executed.

However I'm not really sure what you mean by diagnostics support for scalar queries, as these are shown in tracing output as well. The only SQL not shown are queries which are executed by a IDbDataAdapter to fill a datatable for example.

Putting ORMPersistenceExecution to level 4 took care of what I was missing.

Would be nice if there was a level that was just all the SQL and parameters, none of the "Method Enter", "Method Exit" kind of thing....

Thanks!

I was mistaken, still missing some of the calls....I have a call like:

RetrievalProcedures.SomeStoredProc

This does not generate any diag information. However, if I get the diag info if I do this:

TypedListDAO dao = new TypedListDAO();
dr = dao.GetAsDataReader(null, RetrievalProcedures.SomeStoredProcForUserCallAsQuery(), CommandBehavior.CloseConnection);

I am using v2 and .net 2. My switches are:

<system.diagnostics>
        <switches>
            <add name="SqlServerDQE" value="4"/>
            <add name="ORMGeneral" value="0"/>
            <add name="ORMStateManagement" value="0"/>
            <add name="ORMPersistenceExecution" value="4"/>
        </switches>
    </system.diagnostics>

There's no diag info as the call is done through a DataAdapter, i.e. a Fill() on the SqlDataAdapter is performed.

Would it be possible to get those thrown into the code that is generated? I want every thing that is done to be shown.

Obviously I could write my own template set for procs, or you could add a simple TraceHelper.Write to those calls.

Would it be possbile to get this done?

WayneBrantley wrote:

Would it be possible to get those thrown into the code that is generated? I want every thing that is done to be shown.

Obviously I could write my own template set for procs, or you could add a simple TraceHelper.Write to those calls.

Would it be possbile to get this done?

In DbUtils.cs, in the proc call routines, you could add a few trace calls to TraceHelper.WriteLineIf() statements, where you pass for the flag TraceHelper.PersistenceExecutionSwitch.TraceInfo or TraceVerbose and the message you want to display. I didn't add tracing to the proc calls as these often don't make any sense, simply because all you can trace is which proc is called and which parameters are passed which is right there in your code: all other logic inside the proc isn't traced of course, you have to use the tools provided by the rdbms for that.

Yeah, I know I can add them, but they would get overwritten each build or I could copy and paste the template to generate my own.

I was hoping I could convice you to add them there for 'completeness' - all database calls coming from LLBLGen will be written to the trace output...

Are you convinced - or should I roll my own?

WayneBrantley wrote:

Yeah, I know I can add them, but they would get overwritten each build or I could copy and paste the template to generate my own.

I was hoping I could convice you to add them there for 'completeness' - all database calls coming from LLBLGen will be written to the trace output...

Are you convinced - or should I roll my own?

Even if I'm convinced, it will end up on the todo list. Trace output is often slowing things down tremendously and it can be a lot of info. Having trivial info in the logs, which you can determine without tracing as well, is IMHO not a big help.

So if you want to use it now, you should make these few changes to the dbutils template and add a small templatebindings file with a new binding for the dbutils templateid to your own version and place it above the sharedtemplates templatebindings so it overrules the standard one

Will put my own in for now - thanks!

Todo list would be great

For what it is worth....

Obviously, I have to build the SQL that is generated, the parameter collection and values to output to the trace. Of course you have already done that. I could easily reuse that with the following changes.

You have a ToString() override in your Query class. That functionality can be refactored into a method like:

static string GetQueryFromCommand(IDbCommand theCommand)
{
     //exact same code, but use theCommand instead of _command and
     //theCommand.Parameters instead of this.Parameters.
}

//then of course 
public override string ToString()
{
    return GetQueryFromCommand(_command);
}

Then, I could use this static function from my procedure calls - (or better yet, you could place the following code right before/after the Fill in the procedure calls in the shipping templates )

                if (DynamicQueryEngineBase.Switch.TraceVerbose)
                    TraceHelper.WriteLineIf(DynamicQueryEngineBase.Switch.TraceVerbose, GetProcedureCallDefinition(command), "Executed Stored Procedure");

Side note - the new template/bindings system is MUCH better and cleaner than before - the override of existing templates is a great feature - enabling me to do the above quite easily....