System.Data.SqlClient Conversion overflows errors within reports

[Calee]

We have many Telerik reports that are fed from SQL Server stored procedures, some of which pull from columns with a type of decimal(38,18) (SQL's max). The system that interacts with these reports uses C# and we limit the values for those columns to 28 significant figures (C#'s max).

 

For example, these values would not be supported by our system:

• 12,345,678,901.123456789012345678 (11 LHS digits + 18 decimals = 29 total digits)
• 12,345,678,901,234,567,890.123456789 (20 LHS digits + 9 decimals = 29 total digits)

 

While these would be:

• 12,345,678,901,234,567,890.12345678 (20 LHS digits + 8 decimals = 28 total digits)
• 1,234,567,890.123456789012345678 (10 LHS digits + 18 decimals = 28 total digits)

 

We're running into some "Conversion overflows" errors with numbers that have <= 28 significant digits, which after troubleshooting we found to be occurring in Telerik's internal SQL processing, specifically in the System.Data.SqlClient library. Which has this ticket logged: https://github.com/dotnet/SqlClient/issues/95

 

Our system uses Microsoft.Data.SqlClient, which correctly handles truncating trailing zeros.

 

Is there any option for Telerik using the Microsoft.Data.SqlClient library rather than the System.Data.SqlClient library or any other workaround available for this issue? 

 

Here is the full stack trace:

System.OverflowException: Conversion overflows.

   at System.Data.SqlClient.SqlBuffer.get_Decimal()

   at System.Data.SqlClient.SqlBuffer.get_Value()

   at System.Data.SqlClient.SqlDataReader.GetValues(Object[] values)

   at Telerik.Reporting.Processing.Data.SqlDataEnumerable.ReadValues(IDataReader reader)

   at Telerik.Reporting.Processing.Data.SqlDataEnumerable.GetEnumerator()+MoveNext()

   at Telerik.Reporting.Processing.Data.LazyList`1.LazyListEnumerator.MoveNext()

   at System.Collections.Generic.LargeArrayBuilder`1.AddRange(IEnumerable`1 items)

   at System.Collections.Generic.EnumerableHelpers.ToArray[T](IEnumerable`1 source)

   at Telerik.Reporting.Processing.Data.SeedDataAdapter.Execute(IEnumerable`1 data)

   at Telerik.Reporting.Processing.Data.ResultSetAdapter.Execute(IEnumerable`1 data)

   at Telerik.Reporting.Processing.Data.MultidimentionalDataProvider.Execute(MultidimensionalQuery query)

   at Telerik.Reporting.Processing.DataItemResolveDataAlgorithm.GetDataCore(IDataSource dataSource, MultidimensionalQuery query, IServiceProvider serviceProvider, EvalObject expressionContext, IProcessingContext processingContext)

   at Telerik.Reporting.Processing.Report.GetDataCore(IDataSource dataSource, MultidimensionalQuery query)

   at Telerik.Reporting.Processing.DataItemResolveDataAlgorithm.ResolveData(String processingId, InMemoryState inMemoryState, MultidimensionalQuery query, Func`1 getDataCore, EvalObject expressionContext)

   at Telerik.Reporting.Processing.Report.ResolveData()

   at Telerik.Reporting.Processing.Report.ProcessItemCore()

   at Telerik.Reporting.Processing.Report.ProcessItem()

   at Telerik.Reporting.Processing.ReportItemBase.ProcessElement()

   at Telerik.Reporting.Processing.ProcessingElement.Process(IDataMember dataContext)

[Dimitar]

Hello Calee,

Thank you for the provided information about the problem!

If I understood correctly, switching to the Microsoft.Data.SqlClient data provider will resolve the issue, is that correct?

I confirm that it is indeed possible to use this data provider in Telerik Reporting. In a project that uses Telerik Reporting, you only need to install the Microsoft.Data.SqlClient package to it and then follow the How to register a new DbProviderFactory instance in a .NET Core project - Telerik Reporting KB article to register the provider.

However, the process is more complicated for the Standalone Report Designer. As far as I am aware, there is no installer for this data provider so there is no easy way to register it in machine.config. Thus, to use the provider in the report designer application, you have to do the following:

1. Create a .NET Core Console application and add the Microsoft.Data.SqlClient NuGet package
2. Build the project and copy the assemblies from the {ProjectFolder}/bin/Debug/netx.0 folder to the folder where the .NET Designer is located which is the path C:\Program Files (x86)\Progress\Telerik Reporting R2 2023\Report Designer\.NET
3. Now, copy the Microsoft.Data.SqlClient.dll assembly from the {ProjectFolder}/bin\Debug\net6.0\runtimes\win\lib\netcoreapp3.1 folder again to the folder where the .NET Designer executable is located.
4. Lastly, you will need to copy an assembly named Microsoft.Data.SqlClient.SNI.dll again to the folder with the .NET Designer executable. This assembly can be found at the following path:  C:\Users\{user}\.nuget\packages\microsoft.data.sqlclient.sni.runtime\{package.version}\runtimes\win-x64\native

Where the package version is the version of the Microsoft.Data.SqlClient NuGet package that you have installed, for example, 4.0.0, 5.0.0, 5.1.0, etc.

There are a lot of assemblies that need to be copied so in the end, the folder with the .NET Designer will look like this:

Once the provider is configured correctly, you should be able to see it in the SqlDataSouce Wizard as an option for the dropdown with providers.

I hope that the provided information will help, please let me know if you have any additional questions.

Regards,
Dimitar
Progress Telerik

[Michael]

Hi Dimitar,

Thank you for your response, we were able to get the report designer working. On the project side, I followed the steps listed in the How to register a new DbProviderFactory instance in a .NET Core project - Telerik Reporting article, and I made the connection string update listed in Connecting to Microsoft.Data.SqlClient in Web Report Designer - Telerik Reporting. However, when I run the report in the report viewer I get an error, as it looks like it's still trying to use System.Data.SqlClient. This occurs even though I've added the providerName value to the appsettings and I've verified that it's being picked up by the reporting engine configuration. I've attached some screenshots of the configuration and setup (as well as the error) for reference, is there anything you would recommend we try next?

Regards,

Michael

[Dimitar]

Hello Michael,

Thank you for the provided information!

Considering that you have made all the necessary setups, I suggest making sure that the alias for your connection used by the SqlDataSource in the report is indeed named "DefaultConnection". In the report designer, the alias would be the ConnectionString property on your SqlDataSource.

Apart from that, I assume you do not but still have to ask, do you have any bindings on the report that edit the connection dynamically or any code in your project that does so? If you do, changing the connection would mean that it would be using the default data provider and that would explain the error. If you do such a thing, you need to also update the ProviderName.

If you need further assistance, could you please send me your report for inspection? Also, it wouldn't hurt to generate a trace log if possible - How to troubleshoot errors in ASP.NET Core projects - Telerik Reporting, to be sure that there are no other issues causing this.

 

 

[Calee]

Hi Dimitar,

Thank you for your response to our follow-up question. Your suggestion about the connection alias in the report needing to match the appsettings is exactly what we were missing. Once we adjusted that were able to get it working there, and we also were able to get it working in the .Net Report Designer.

Thanks again for your thorough assistance!

Calee