Problems reading data from an SQL Server stored procedure

[Brian Collings]

I have an asp.net core 6 application that uses Syncfusion controls, SQL Server and Entity Framework,

I can read data from SQL Server tables and views to populate a Syncfusion datagrid but I am trying to read data from an SQL Server Stored Procedure for the first time and it isn't working for me.

The code below is an example of reading from a table that works perfectly. It's a portion of code from Index.cshtml.cs:

public async System.Threading.Tasks.Task OnGetAsync()

        {

            if (_context.DataType != null)

            {

                DataType = await _context.DataType.ToListAsync();

            }

        }

        public JsonResult OnPostDataSource([FromBody] DataManagerRequest dm)

        {

            IEnumerable<DataType> DataSource = _context.DataType.ToList();

            DataOperations operation = new DataOperations();

The following code is my current attempt to read from a stored procedure and it does not work:

        public async System.Threading.Tasks.Task OnGetAsync()

        {

            // Read the 'AssetCode' cookie and assign it to a string variable

            string currentAssetCode = HttpContext.Request.Cookies["AssetCode"];

            if (_context.AssetDetail != null)

            {

                AssetDetails = (IList<AssetDetail>)_context.AssetDetail

                .FromSqlRaw("EXEC dbo.GetAssetDetails '00017'")

                .ToListAsync();

            }

        }

I have hard coded the asset code so I can focus on getting the code right.

I have tried a number of alternatives for reading the stored procedure but none of them work, including using FromSqlInterpolated instead of FromSqlRaw.

Can somebody please help me to get this code working.

Let me know if you need more information.

[Vasanthakumar K]

Hi Brian Collings,

Greetings from Syncfusion support.

Query: I can read data from SQL Server tables and views to populate a Syncfusion DataGrid but I am trying to read data from an SQL Server Stored Procedure for the first time, and it isn't working for me.

To load data into a Syncfusion Grid using stored procedures, follow these steps:

Key Steps:

1. Stored Procedure: Create a stored procedure in SQL Server that retrieves the required data in the desired format.
2. Server-Side Binding: Use the SqlClient in your ASP.NET Core backend to call the stored procedure and fetch data.
3. Grid Binding: Use the UrlAdaptor in the Syncfusion Grid to bind the fetched data, enabling server-side actions like pagination, sorting, and filtering.

Explanation:

• DataManagerRequest: Syncfusion Grid sends this object containing action data (e.g., filters, sorting, pagination) to the backend.
• Response Format: The backend should return data in { result: <data[]>, count: <totalCount> } format for proper rendering.
• UrlAdaptor: This adaptor ensures server-side processing and binds the stored procedure's data efficiently.

Resources:

• Razor Grid URL Adaptor Data Binding Detailed Guide with Sample: How to Render Grid in ASP.NET Core Razor Page
• Stored Procedures Knowledge Base with sample: Guidance on SQL Server Stored Procedure Data Binding with Syncfusion DataGrid.
• URL Adaptor Documentation: https://ej2.syncfusion.com/aspnetcore/documentation/grid/data-binding/remote-data#handling-on-demand-grid-actions
• Sample Code: A complete working example is attached (including Stored Procedures SQL Queries file) for your reference.

This approach leverages server-side data handling to ensure efficient data rendering and interaction within the Syncfusion Grid.

Regards,

Vasanthakumar K

Attachment: 655989,195395coreSQLurlcruddbStoredprocedures_209313b5.zip