求绑定SQL Server数据的ASP.NET Core MVC开源网格示例（支持排序、列筛选、分页，具备类Kendo UI功能）

Got it, let's walk through building exactly the ASP.NET Core MVC grid you need—bound to SQL Server, with sorting, column-based filtering, pagination, and that Kendo UI-style experience. I'll share a full open-source example using widely adopted tools that check all your boxes.

DataTables is a lightweight, open-source front-end grid library that mimics many of Kendo UI's core features (sorting, filtering, pagination) and plays seamlessly with ASP.NET Core MVC. We'll pair it with Entity Framework Core for SQL Server data access.

1. Project Setup & Dependencies #

First, add these packages to your ASP.NET Core MVC project via NuGet:

• Microsoft.EntityFrameworkCore.SqlServer (for SQL Server integration)
• Microsoft.EntityFrameworkCore.Tools (for database migrations)

For the front-end, we'll use DataTables' CDN (no external account required, fully open-source).

2. Data Model & EF Core Context #

Let's create a sample Product model and database context to represent your SQL Server data:

// Models/Product.cs
public class Product
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
    public decimal Price { get; set; }
    public DateTime CreatedDate { get; set; }
}

// Data/AppDbContext.cs
public class AppDbContext : DbContext
{
    public AppDbContext(DbContextOptions<AppDbContext> options) : base(options) { }
    
    public DbSet<Product> Products { get; set; }
}

Configure the context in Program.cs and add your SQL Server connection string to appsettings.json:

// Program.cs
builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection")));

// appsettings.json
"ConnectionStrings": {
    "DefaultConnection": "Server=(localdb)\\mssqllocaldb;Database=GridDemoDB;Trusted_Connection=True;MultipleActiveResultSets=true"
}

Run a migration to create the database:

Add-Migration InitialCreate
Update-Database

3. Backend Action (Supports Sorting, Filtering, Pagination) #

Create a controller action that handles server-side processing for the grid—this is where we'll implement the core logic to interact with SQL Server efficiently:

// Controllers/HomeController.cs
public class HomeController : Controller
{
    private readonly AppDbContext _context;

    public HomeController(AppDbContext context)
    {
        _context = context;
    }

    public IActionResult Index()
    {
        return View();
    }

    [HttpPost]
    public IActionResult LoadProducts(DataTableRequest request)
    {
        var productQuery = _context.Products.AsQueryable();

        // Global search filter
        if (!string.IsNullOrEmpty(request.Search.Value))
        {
            var searchTerm = request.Search.Value.ToLower();
            productQuery = productQuery.Where(p =>
                p.Name.ToLower().Contains(searchTerm) ||
                p.Price.ToString().Contains(searchTerm) ||
                p.CreatedDate.ToString("yyyy-MM-dd").Contains(searchTerm));
        }

        // Column sorting
        foreach (var order in request.Order)
        {
            var columnName = request.Columns[order.Column].Data;
            productQuery = order.Dir == "asc"
                ? productQuery.OrderBy(columnName)
                : productQuery.OrderByDescending(columnName);
        }

        // Pagination
        var totalRecords = productQuery.Count();
        var paginatedData = productQuery.Skip(request.Start).Take(request.Length).ToList();

        return Json(new
        {
            draw = request.Draw,
            recordsTotal = totalRecords,
            recordsFiltered = totalRecords,
            data = paginatedData
        });
    }

    // Helper classes to map DataTables' request format
    public class DataTableRequest
    {
        public int Draw { get; set; }
        public int Start { get; set; }
        public int Length { get; set; }
        public DataTableSearch Search { get; set; } = new();
        public List<DataTableOrder> Order { get; set; } = new();
        public List<DataTableColumn> Columns { get; set; } = new();
    }

    public class DataTableSearch { public string Value { get; set; } = string.Empty; public bool Regex { get; set; } }
    public class DataTableOrder { public int Column { get; set; } public string Dir { get; set; } = "asc"; }
    public class DataTableColumn { public string Data { get; set; } = string.Empty; public bool Searchable { get; set; } = true; public bool Orderable { get; set; } = true; }
}

4. Frontend View (Kendo-Style UI) #

Create the Index.cshtml view to render the grid with sorting, filtering, and pagination enabled:

@{
    ViewData["Title"] = "Product Management Grid";
}

<h1 class="mb-4">Product Grid</h1>

<table id="productGrid" class="table table-striped table-bordered" style="width:100%">
    <thead>
        <tr>
            <th>ID</th>
            <th>Product Name</th>
            <th>Price</th>
            <th>Created Date</th>
        </tr>
        <!-- Column-specific filters (Kendo-like touch) -->
        <tr>
            <th><input type="text" placeholder="Filter ID" class="column-filter form-control form-control-sm" data-column="0"></th>
            <th><input type="text" placeholder="Filter Name" class="column-filter form-control form-control-sm" data-column="1"></th>
            <th><input type="text" placeholder="Filter Price" class="column-filter form-control form-control-sm" data-column="2"></th>
            <th><input type="text" placeholder="Filter Date" class="column-filter form-control form-control-sm" data-column="3"></th>
        </tr>
    </thead>
    <tbody></tbody>
</table>

@section Scripts {
    <!-- DataTables CSS & JS -->
    <link rel="stylesheet" href="https://cdn.datatables.net/1.13.7/css/jquery.dataTables.min.css" />
    <script src="https://code.jquery.com/jquery-3.7.1.min.js"></script>
    <script src="https://cdn.datatables.net/1.13.7/js/jquery.dataTables.min.js"></script>

    <script>
        $(document).ready(function() {
            const productGrid = $('#productGrid').DataTable({
                serverSide: true,
                ajax: {
                    url: '@Url.Action("LoadProducts", "Home")',
                    type: 'POST',
                    contentType: 'application/json',
                    data: d => JSON.stringify(d)
                },
                columns: [
                    { data: 'Id' },
                    { data: 'Name' },
                    { data: 'Price', render: price => `$${price.toFixed(2)}` },
                    { data: 'CreatedDate', render: date => new Date(date).toLocaleDateString() }
                ],
                ordering: true,
                searching: true,
                paging: true,
                pageLength: 10,
                language: {
                    search: "Global Search:",
                    paginate: {
                        first: "First",
                        last: "Last",
                        next: "Next",
                        previous: "Previous"
                    }
                }
            });

            // Column-specific filter logic
            $('.column-filter').on('keyup', function() {
                const columnIndex = $(this).data('column');
                productGrid.column(columnIndex).search($(this).val()).draw();
            });
        });
    </script>
}

5. Key Features Explained #

• Sorting: Click any column header to sort ascending/descending—backend handles efficient SQL ORDER BY queries.
• Filtering: Use the global search bar or column-specific input boxes to filter data—backend uses WHERE clauses to query only matching records.
• Pagination: The grid loads only the current page of data (via Skip()/Take() in EF Core), avoiding performance hits from loading all records at once.

This implementation is fully open-source (DataTables uses MIT license, EF Core is Apache 2.0 licensed) and mirrors Kendo UI's core grid functionality while keeping things lightweight.

内容的提问来源于stack exchange，提问作者AuserP