MVC中基于关联查询创建嵌套WebGrid的实现疑问

Hey there! Let's walk through how to get your nested WebGrid working smoothly, and also refine your data access approach to be more robust.

First, Let's Assess Your Current Data Access Code #

Your existing ADO.NET approach using DataSet and SqlDataAdapter works in theory, but it has a few key issues that could lead to problems down the line:

• Resource leaks: You're not wrapping disposable objects like SqlCommand, SqlDataAdapter, or your database connection in using statements. This can leave connections open unnecessarily, hurting performance over time.
• Unstructured data: DataSet is untyped, which means you’ll have to do manual casting when binding to your nested grid—this is error-prone and messy to maintain.
• Scalability: Hardcoded SQL strings work here, but as your query grows or needs parameters, you’ll want a more maintainable pattern.

Here’s an optimized ADO.NET version that fixes these issues, plus it maps results directly to strong types (which we’ll use for the nested grid):

// Define strong-type models to hold your hierarchical data
public class OperModel
{
    public int OperSN { get; set; }
    public string OsnName { get; set; }
    public List<DocModel> Documents { get; set; } = new List<DocModel>();
}

public class DocModel
{
    public int DocSN { get; set; }
    public DateTime DateD { get; set; }
    public string DocNum { get; set; }
}

// Optimized data access method with proper resource management
public List<OperModel> GetOperWithDocuments()
{
    var operLookup = new Dictionary<int, OperModel>();
    var connectionString = "Your_Connection_String_Here";

    // Use using statements to auto-dispose resources when done
    using var con = new SqlConnection(connectionString);
    con.Open();

    var query = @"
        SELECT d.SN AS DocSN, d.date_d, d.DOC_NUM, o.SN AS OperSN, o.NAME AS OsnName
        FROM docs d
        LEFT JOIN opers o ON d.osn = o.SN
        WHERE d.TIP = 2 AND d.ISCHANGED = 0 AND d.BASAOUT = 2
        ORDER BY o.NAME, d.date_d DESC";

    using var cmd = new SqlCommand(query, con);
    using var reader = cmd.ExecuteReader();

    while (reader.Read())
    {
        int operSN = reader.GetInt32(reader.GetOrdinal("OperSN"));
        
        // Create or retrieve the OperModel from our lookup dictionary
        if (!operLookup.TryGetValue(operSN, out var oper))
        {
            oper = new OperModel
            {
                OperSN = operSN,
                OsnName = reader.GetString(reader.GetOrdinal("OsnName"))
            };
            operLookup.Add(operSN, oper);
        }

        // Add the document to the Oper's nested document list
        var doc = new DocModel
        {
            DocSN = reader.GetInt32(reader.GetOrdinal("DocSN")),
            DateD = reader.GetDateTime(reader.GetOrdinal("date_d")),
            DocNum = reader.GetString(reader.GetOrdinal("DOC_NUM"))
        };
        oper.Documents.Add(doc);
    }

    return operLookup.Values.ToList();
}

Alternatively, Use EF Core for Simpler Data Access #

If you’re open to using Entity Framework Core (the modern ORM for .NET), this becomes even cleaner—no manual data mapping needed, and it handles relationships out of the box:

1. First, define your entities and DbContext:

public class Doc
{
    public int SN { get; set; }
    public DateTime date_d { get; set; }
    public string DOC_NUM { get; set; }
    public int TIP { get; set; }
    public bool ISCHANGED { get; set; }
    public int BASAOUT { get; set; }
    public int osn { get; set; }

    // Navigation property to the related Oper
    public Oper Oper { get; set; }
}

public class Oper
{
    public int SN { get; set; }
    public string NAME { get; set; }

    // Navigation property for related Docs
    public List<Doc> Docs { get; set; } = new List<Doc>();
}

public class AppDbContext : DbContext
{
    public DbSet<Doc> Docs { get; set; }
    public DbSet<Oper> Opers { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer("Your_Connection_String_Here");
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // Configure the one-to-many relationship between Doc and Oper
        modelBuilder.Entity<Doc>()
            .HasOne(d => d.Oper)
            .WithMany(o => o.Docs)
            .HasForeignKey(d => d.osn);
    }
}

2. Then query with eager loading to fetch related data:

public List<OperModel> GetOperWithDocuments()
{
    using var context = new AppDbContext();
    return context.Opers
        .Include(o => o.Docs) // Load related docs for each oper
        .Where(o => o.Docs.Any(d => d.TIP == 2 && !d.ISCHANGED && d.BASAOUT == 2))
        .Select(o => new OperModel
        {
            OperSN = o.SN,
            OsnName = o.NAME,
            Documents = o.Docs
                .Where(d => d.TIP == 2 && !d.ISCHANGED && d.BASAOUT == 2)
                .OrderByDescending(d => d.date_d)
                .Select(d => new DocModel
                {
                    DocSN = d.SN,
                    DateD = d.date_d,
                    DocNum = d.DOC_NUM
                }).ToList()
        }).ToList();
}

Build the Nested WebGrid in Your View #

Now that you have a list of OperModel objects (each with a nested list of DocModel), you can build the nested WebGrid in your ASP.NET MVC view:

@{
    var mainGrid = new WebGrid(Model, canPage: true, rowsPerPage: 8);
}

<div class="table-responsive">
    @mainGrid.GetHtml(
        tableStyle: "table table-striped table-bordered",
        headerStyle: "thead-dark",
        columns: mainGrid.Columns(
            mainGrid.Column("OsnName", "Operator Name"),
            mainGrid.Column("Documents", format: @<text>
                @{
                    var subGrid = new WebGrid(item.Documents, canPage: false);
                    @subGrid.GetHtml(
                        tableStyle: "table table-sm mt-2",
                        columns: subGrid.Columns(
                            subGrid.Column("DocNum", "Document Number"),
                            subGrid.Column("DateD", "Date", format: @<text>@item.DateD.ToString("MM/dd/yyyy")</text>),
                            subGrid.Column("DocSN", "Doc ID")
                        )
                    )
                }
            </text>)
        )
    )
</div>

Key Takeaways #

• Your initial data access approach works, but switching to strong-type models and wrapping disposable objects in using statements will make your code safer and easier to maintain.
• EF Core is a great choice if you want to reduce boilerplate and leverage built-in relationship management.
• Nested WebGrids work best with hierarchical data structures (like OperModel containing a list of DocModel), so mapping your query results to these structures upfront is critical.

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