Stored Procedure with nullable params

Radzen Studio Blazor Server
1

Hi Guys,

I'm trying to make my SQL Server Stored Procedure work with the datagrid. It has many nullable parameters ( = @param or @param is null), and looking at the post here https://forum.radzen.com/t/storeprocedure-nullable-is-false/1261/7 it should be okay. But I get the error on run (error CS7036: There is no argument given that corresponds to the required formal parameter 'param' of 'ItInventoryDataService.GetSpEquipments(string, bool?, int?, int?, int?, int?, int?, string, string, string, string, string, string, string, string, string)' )

I've tried to add null to each parameter on the Invoke data source method using null, ${null}, etc. and with this it does run, but the grid is empty.

Any thoughts?
Slosuenos

2

Maybe your stored procedure does not return any items. Did you check that? Also did you check if there are any exception in Radzen's output window?

3

Yes, the stored procedure runs successfully from within SSMS. The only exception was the error CS7036 that I wrote about in the original post.

4

You said it runs without error but the grid is empty. You can use the debugger to inspect what the stored procedure returned.

5

@korchev hello,

This should be a pretty easy one for you. In the forum post I linked to when opening this case, I was under the impression that Radzen recognized nullable parameters in stored procedures - obviously I must be confused. I've tried debugging, but the process seems to halt before returning anything of value - probably not doing that correctly either.

So, instead I'm sending the stored procedure the parameters as null to keep Radzen happy. Looks like this:

image
image1859×1061 54 KB

This is what Radzen puts in the designer.cs for the SP:

    protected async System.Threading.Tasks.Task Load()
    {
        var itInventoryDataGetSpEquipmentsResult = await ItInventoryData.GetSpEquipments($"null", null, null, null, etc.........);
        getSpEquipmentsResult = itInventoryDataGetSpEquipmentsResult;
    }

The problem seems to be that for strings and dates Radzen inserts $"null" instead of null. Bools and integers are fine and expressed correctly.

If I manually change the $"null" params to null in the designer.cs with Visual Studio and run it, then the app runs correctly.

Some silly little thing I'm doing incorrectly, I'm sure.

Thanks,
Stephen

6

Indeed Radzen treats string and date parameters in a special manner and add quotes (to spare the user the need to do that). We should handle null as a special value and ignore it.

As a workaround I can suggest creating a page property for those parameters and setting it to null. Then use the page property as the stored procedure parameter value e.g. ${endUserName}. Another thing you can try is ${null} - this could fool the current implementation and prevent it from adding quotes.

1 Like
7

Thanks Dude, I'll try it...

8

@korchev, well none of those solutions worked, they all produce empty strings, not nulls.

Parameter as ${null} produced $"{null}" in the designer.cs, which results in an empty string being sent to the SP.

Setting a page property to null produced a $"{propertyname}" in the designer.cs and results in an empty string being sent to the SP.

Event setting a page property to ${null} and then using the property as the SP parameter results in an empty string.

Any thoughts? Thanks,
Stephen

9

That is correct. The only solution until we fix that issue is to use the code generation ignore list or create a custom method which invokes the stored procedure with the right parameters.

    public partial class HomeComponent
    {
      public async Task<IQueryable<Models.Northwind.SalesByCategory>> GetSalesByCategory(string categoryName)
      {
        return await NorthwindService.GetSalesByCategory(categoryName);
      }
    }

image
image2514×730 53.9 KB

10

Okay, thank you. I hope this fix makes it into a build soon. I rely heavily on this stuff to help compensate for my poor Entity Framework skills.