How to Dynamically Display Associated Data from Multiple Tables in JqGrid using ASP.NET MVC?

Dynamically Populating JqGrid with Related Data from Multiple ASP.NET MVC Tables

This guide demonstrates a method for displaying data from two related database tables within a JqGrid in an ASP.NET MVC application. The tables are linked, but only ID values are available for the relationship.

Solution Overview:

This approach involves modifying the controller to return the necessary data in a JSON format, creating a helper function to format location data for JqGrid, dynamically setting column options using JqGrid's beforeProcessing event, and enhancing the user experience with Bootstrap Select2 for search and filtering.

1. Controller Modification:

The controller action needs to retrieve data from both tables (e.g., Students and Locations) and include the location data within the JSON response.

2. Server-Side Data Formatting:

A helper function is crucial for transforming the location data into a format compatible with JqGrid's editoptions.value and searchoptions.value. This typically involves ordering the locations and concatenating them as "ID:Value;".

3. Dynamic JqGrid Column Configuration:

Use the beforeProcessing event in your client-side JavaScript code to dynamically set JqGrid column options based on the server's response. This allows for runtime adjustments of column formatters, edit types, and other settings.

4. Enhanced Search/Filtering with Bootstrap Select2:

Integrate Bootstrap Select2 to improve search and filter functionality in dropdowns, providing advanced search capabilities and a better user interface compared to standard select elements.

Example Controller Code:

<code class="language-csharp">public class HomeController : Controller
{
    // ... other controller actions ...

    public JsonResult Students()
    {
        var students = new List<Student> { /* ... your student data ... */ };
        var locations = new List<City> { /* ... your city data ... */ };

        var formattedLocations = locations.OrderBy(l => l.CNAME)
                                         .Aggregate("", (current, location) => current + $"{location.CID}:{location.CNAME};");

        // Remove trailing semicolon
        if (formattedLocations.EndsWith(";"))
        {
            formattedLocations = formattedLocations.Substring(0, formattedLocations.Length - 1);
        }

        return Json(new
        {
            colModelOptions = new
            {
                CITY = new
                {
                    formatter = "select",
                    edittype = "select",
                    editoptions = new { value = formattedLocations },
                    stype = "select",
                    searchoptions = new { sopt = new[] { "eq", "ne" }, value = $":Any;{formattedLocations}" }
                }
            },
            rows = students
        }, JsonRequestBehavior.AllowGet);
    }
}</code>

This example demonstrates a simplified structure. Adapt the Student and City classes and data retrieval to match your specific database schema and data access methods. Remember to adjust the client-side JqGrid configuration accordingly to handle the dynamically provided colModelOptions.

The above is the detailed content of How to Dynamically Display Associated Data from Multiple Tables in JqGrid using ASP.NET MVC?. For more information, please follow other related articles on the PHP Chinese website!