如何在C#中从MSSQL读取含关联餐桌的所有餐厅数据

一、一次性查询所有数据（推荐方案） #

不要循环调用单个餐厅查询接口，避免N+1查询的性能问题。直接用LEFT JOIN或CROSS APPLY一次性拉取所有餐厅和对应餐桌数据，再在代码里分组关联。

示例代码：

public List<Restaurant> GeefAlleRestaurants()
{
    var restaurants = new Dictionary<int, Restaurant>();

    using (var conn = new SqlConnection(JeConnectionString))
    {
        conn.Open();
        var query = @"
            SELECT r.Id AS RestaurantId, r.Naam, r.Adres,
                   t.Id AS TafelId, t.Nummer, t.AantalPlaatsen
            FROM Restaurants r
            LEFT JOIN Tafels t ON r.Id = t.RestaurantId";

        using (var cmd = new SqlCommand(query, conn))
        using (var reader = cmd.ExecuteReader())
        {
            while (reader.Read())
            {
                var restaurantId = reader.GetInt32(reader.GetOrdinal("RestaurantId"));
                if (!restaurants.ContainsKey(restaurantId))
                {
                    restaurants.Add(restaurantId, new Restaurant
                    {
                        Id = restaurantId,
                        Naam = reader.GetString(reader.GetOrdinal("Naam")),
                        Adres = reader.GetString(reader.GetOrdinal("Adres")),
                        Tafels = new List<Tafel>()
                    });
                }

                // 若存在餐桌数据，添加到对应餐厅的列表中
                if (!reader.IsDBNull(reader.GetOrdinal("TafelId")))
                {
                    restaurants[restaurantId].Tafels.Add(new Tafel
                    {
                        Id = reader.GetInt32(reader.GetOrdinal("TafelId")),
                        Nummer = reader.GetString(reader.GetOrdinal("Nummer")),
                        AantalPlaatsen = reader.GetInt32(reader.GetOrdinal("AantalPlaatsen"))
                    });
                }
            }
        }
    }

    return restaurants.Values.ToList();
}

二、解决“连接已打开”的错误 #

这个错误大多因为重复使用同一个SqlConnection实例且未正确关闭。最佳实践是每个数据库操作都用using语句包裹连接——ADO.NET自带连接池，会自动复用连接，无需担心频繁创建连接的性能问题。

错误示例（禁止这么写）：

// 错误：同一个连接被重复打开，且未正确释放
var conn = new SqlConnection(JeConnectionString);
conn.Open();
var resto1 = GeefRestaurant(conn, 1);
var resto2 = GeefRestaurant(conn, 2); // 此处大概率报错连接已打开
conn.Close();

正确写法：每个方法独立管理连接，或者在调用方明确连接状态，推荐前者：

public Restaurant GeefRestaurant(int restaurantId)
{
    using (var conn = new SqlConnection(JeConnectionString))
    {
        conn.Open();
        // 原有读取单个餐厅及餐桌的逻辑
    }
}

三、拆分方法的最佳实践 #

如果一定要拆分获取餐桌的方法，要么让每个方法独立管理连接，要么明确传递连接的状态责任。推荐前者，避免连接状态管理混乱：

// 拆分的获取餐桌方法
public List<Tafel> GetTafelsVoorRestaurant(int restaurantId)
{
    var tafels = new List<Tafel>();
    using (var conn = new SqlConnection(JeConnectionString))
    {
        conn.Open();
        var query = "SELECT Id, Nummer, AantalPlaatsen FROM Tafels WHERE RestaurantId = @RestaurantId";
        using (var cmd = new SqlCommand(query, conn))
        {
            cmd.Parameters.AddWithValue("@RestaurantId", restaurantId);
            using (var reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    tafels.Add(new Tafel
                    {
                        Id = reader.GetInt32(0),
                        Nummer = reader.GetString(1),
                        AantalPlaatsen = reader.GetInt32(2)
                    });
                }
            }
        }
    }
    return tafels;
}

// 在GeefAlleRestaurants中调用拆分方法
public List<Restaurant> GeefAlleRestaurants()
{
    var restaurants = new List<Restaurant>();
    using (var conn = new SqlConnection(JeConnectionString))
    {
        conn.Open();
        var query = "SELECT Id, Naam, Adres FROM Restaurants";
        using (var cmd = new SqlCommand(query, conn))
        using (var reader = cmd.ExecuteReader())
        {
            while (reader.Read())
            {
                var resto = new Restaurant
                {
                    Id = reader.GetInt32(0),
                    Naam = reader.GetString(1),
                    Adres = reader.GetString(2),
                    Tafels = GetTafelsVoorRestaurant(reader.GetInt32(0))
                };
                restaurants.Add(resto);
            }
        }
    }
    return restaurants;
}

注意：这种拆分方式会产生N+1查询（1次查所有餐厅，N次查每个餐厅的餐桌），数据量大时性能极差，因此优先选择第一种一次性查询的方案。

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