Stored Procedures to Bind a DataGrid

I feel like I'm missing something fundamental when it comes to using stored procedures for a DataGrid. Looking at the instructions here: https://www.radzen.com/documentation/blazor/ms-sql/#a-idstored-proceduresusing-stored-proceduresa it seems straightforward to me. I have a fairly simple stored proc that returns a number of columns (see below for the stored procedure). However Radzen doesn't seem to see the columns when I try to auto-generate. Is there something fundamental I'm not seeing with regards to handling stored procedures in Radzen?

ALTER PROCEDURE [dbo].[FullBordereau]
-- Add the parameters for the stored procedure here
@ReportDate as Datetime
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
SELECT        dbo.Claims.ClaimID, dbo.ClaimClaimants.ClaimantID, dbo.ClaimClaimants.Name AS Claimant, ISNULL(SUM(CASE WHEN Parameters.ParamDesc = 'Indemnity' AND 
                     Parameters_1.ParamDesc = 'Reserve' THEN Amount ELSE 0 END), 0) AS IndemnityReserve, ISNULL(SUM(CASE WHEN Parameters.ParamDesc = 'Indemnity' AND 
                     Parameters_1.ParamDesc = 'Payment' THEN Amount ELSE 0 END), 0) AS IndemnityPayment, ISNULL(SUM(CASE WHEN Parameters.ParamDesc = 'Adjusting' AND 
                     Parameters_1.ParamDesc = 'Reserve' THEN Amount ELSE 0 END), 0) AS AdjustingReserve, ISNULL(SUM(CASE WHEN Parameters.ParamDesc = 'Adjusting' AND 
                     Parameters_1.ParamDesc = 'Payment' THEN Amount ELSE 0 END), 0) AS AdjustingPayment, ISNULL(SUM(CASE WHEN Parameters.ParamDesc = 'Legal' AND Parameters_1.ParamDesc = 'Reserve' THEN Amount ELSE 0 END), 
                     0) AS LegalReserve, ISNULL(SUM(CASE WHEN Parameters.ParamDesc = 'Legal' AND Parameters_1.ParamDesc = 'Payment' THEN Amount ELSE 0 END), 0) AS LegalPayment, 
                     ISNULL(SUM(CASE WHEN Parameters.ParamDesc = 'Deductible' AND Parameters_1.ParamDesc = 'Recovery' THEN Amount ELSE 0 END), 0) AS Deductible, ISNULL(SUM(CASE WHEN Parameters.ParamDesc = 'Subrogation' AND
                      Parameters_1.ParamDesc = 'Recovery' THEN Amount ELSE 0 END), 0) AS Subrogation, ISNULL(SUM(CASE WHEN Parameters_1.ParamDesc = 'Reserve' THEN Amount ELSE 0 END), 0) AS TotalReserve, 
                     ISNULL(SUM(CASE WHEN Parameters_1.ParamDesc = 'Payment' THEN Amount ELSE 0 END), 0) AS TotalPayment, ISNULL(SUM(CASE WHEN Parameters_1.ParamDesc = 'Recovery' THEN Amount ELSE 0 END), 0) 
                     AS TotalRecovery, ISNULL(SUM(CASE WHEN Parameters_1.ParamDesc = 'Recovery' THEN (- 1 * Amount) ELSE AMOUNT END), 0) AS TotalIncurred
			INTO #Transactions
			FROM            dbo.Transactions LEFT OUTER JOIN
										dbo.ClaimClaimants ON dbo.Transactions.ClaimantID = dbo.ClaimClaimants.ClaimantID AND dbo.Transactions.ClaimID = dbo.ClaimClaimants.ClaimID LEFT OUTER JOIN
										dbo.Parameters ON dbo.Transactions.IncurredTypeID = dbo.Parameters.ParameterID LEFT OUTER JOIN
										dbo.Parameters AS Parameters_1 ON dbo.Transactions.IncurredCategoryID = Parameters_1.ParameterID RIGHT OUTER JOIN
										dbo.Claims ON dbo.Transactions.ClaimID = dbo.Claims.ClaimID
			WHERE TransactionDate <= @ReportDate
			GROUP BY dbo.Claims.ClaimID, dbo.ClaimClaimants.Name, dbo.ClaimClaimants.ClaimantID


SELECT        ClaimDetailsBordereau.ClaimID, ClaimDetailsBordereau.ClaimNo, ClaimDetailsBordereau.AdjusterClaimNo, ClaimDetailsBordereau.ProgramID, 
                     ClaimDetailsBordereau.[Program Name], ClaimDetailsBordereau.ContractYearID, ClaimDetailsBordereau.[Contract Year], ClaimDetailsBordereau.ClaimStatusID, ClaimDetailsBordereau.[Claim Status], 
                     ClaimDetailsBordereau.Adjuster, ClaimDetailsBordereau.[Defense Counsel], ClaimDetailsBordereau.[Coverage Counsel], ClaimDetailsBordereau.[File Handler], ClaimDetailsBordereau.[Original File Handler], 
                     ClaimDetailsBordereau.Brokerage, ClaimDetailsBordereau.[Brokerage Transaction Role], ClaimDetailsBordereau.CloseDate, ClaimDetailsBordereau.EntryDate, 
                     ClaimDetailsBordereau.ClaimDate, ClaimDetailsBordereau.AgreementDate, ClaimDetailsBordereau.ReportDate, ClaimDetailsBordereau.ClaimPaidDate, 
                     ClaimDetailsBordereau.LapseDate, ClaimDetailsBordereau.ServedDate, ClaimDetailsBordereau.[Brokerage Only ?], ClaimDetailsBordereau.[Loss Cause], ClaimDetailsBordereau.OccurrenceNo, 
                     ClaimDetailsBordereau.ValueOfTransaction, ClaimDetailsBordereau.ExpectedPayout, ClaimDetailsBordereau.AdjustedGrossClaim, ClaimDetailsBordereau.[Coverage Issue], ClaimDetailsBordereau.[Not Yet Reported], 
                     ClaimDetailsBordereau.[Class Action], #Transactions.Claimant, #Transactions.IndemnityReserve, #Transactions.IndemnityPayment, #Transactions.LegalReserve, #Transactions.LegalPayment, #Transactions.AdjustingReserve,
					 #Transactions.AdjustingPayment, #Transactions.Deductible, #Transactions.Subrogation, #Transactions.TotalReserve, #Transactions.TotalPayment, #Transactions.TotalRecovery, #Transactions.TotalIncurred
FROM            ClaimDetailsBordereau LEFT OUTER JOIN
						 #Transactions ON ClaimDetailsBordereau.ClaimID = #Transactions.ClaimID
WHERE ClaimDetailsBordereau.ReportDate <= @ReportDate

END

Hi @kgordon,

With complex procedures sometimes Radzen cannot retrieve the schema and in this case you can add columns manually.

Still running into roadblocks with this.
Do you have a tutorial or example on using Stored Procedures to fill a datagrid when Radzen doesn't doesn't recognize the schema? This is a pretty fundamental issue as all my reporting will require some parameters similar to what I've described above. The stored proc isn't that complex, but it does use a temporary table, so I suspect that is why Radzen can't handle it.

Another option, Is it possible to do this instead:

  • Create a simple stored procedure and bind to the grid
  • Expand the stored procedure to include the values from a view. (Both have ClaimID as the key column, I just want to be able to link both result sets and display in my grid).

If this is possible, what do I need to do to put that in place? Is there something more than setting the view in the Expand option that I would need to do?

Here is how to add columns from unknown schema:

Not sure I understand the second option.

I tried that, it complained that the storedprocedure results were an int that couldn't be equated to the FullBordereau resultset. Can I send you my project? Even if you just setup one column to show me how to get it working, I'll be able to take it from there.

Yes, please send the entire project along with your database schema as SQL script to info@radzen.com

1 Like