如何用System.Data.SqlClient获取含关联用户的Company对象？

Great question! Let's break down the two main approaches here using only System.Data.SqlClient, and walk through their pros and cons to help you pick the best fit for your scenario.

方案1：单联表查询一次性获取所有数据 #

This approach hits the database once with a JOIN query to pull both company and user data in one go, then maps the results to your Company object with its CompanyUsers list. It cuts down on round-trips to the database, which is great for performance when you know you always need the user data alongside the company.

Here's a code example of how you could implement this:

public Company GetCompanyWithUsers(int companyId)
{
    Company company = null;
    string query = @"SELECT c.CompanyID, c.Company_Name, c.softID, 
                            u.UserID, u.User_Name
                     FROM Company c
                     LEFT JOIN [User] u ON c.CompanyID = u.CompanyID
                     WHERE c.CompanyID = @CompanyID";

    using (SqlConnection conn = new SqlConnection(yourConnectionString))
    {
        conn.Open();
        using (SqlCommand cmd = new SqlCommand(query, conn))
        {
            cmd.Parameters.AddWithValue("@CompanyID", companyId);
            using (SqlDataReader reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    // Initialize the company object only once (since JOIN will return multiple rows per company)
                    if (company == null)
                    {
                        company = new Company
                        {
                            CompanyID = (int)reader["CompanyID"],
                            CompanyName = reader["Company_Name"].ToString(),
                            softID = reader["softID"].ToString(), // Adjust type based on your actual schema
                            CompanyUsers = new List<User>()
                        };
                    }

                    // Add user only if the user row isn't null (handles companies with no users)
                    if (!reader.IsDBNull(reader.GetOrdinal("UserID")))
                    {
                        company.CompanyUsers.Add(new User
                        {
                            UserID = (int)reader["UserID"],
                            CompanyID = (int)reader["CompanyID"],
                            UserName = reader["User_Name"].ToString()
                        });
                    }
                }
            }
        }
    }
    return company;
}

Note: Use LEFT JOIN instead of INNER JOIN if you want to include companies that have no users (they'll just have an empty CompanyUsers list).

方案2：分两次查询（现有方法+新增用户查询） #

If you already have a getCompany method that fetches basic company info, you can add a new getUsersByCompanyID method to pull the users separately, then combine them. This is more flexible—you can choose to load users only when you need them, which saves bandwidth and memory if you often work with just company data without users.

First, the new getUsersByCompanyID method:

public List<User> GetUsersByCompanyID(int companyId)
{
    List<User> users = new List<User>();
    string query = @"SELECT UserID, CompanyID, User_Name
                     FROM [User]
                     WHERE CompanyID = @CompanyID";

    using (SqlConnection conn = new SqlConnection(yourConnectionString))
    {
        conn.Open();
        using (SqlCommand cmd = new SqlCommand(query, conn))
        {
            cmd.Parameters.AddWithValue("@CompanyID", companyId);
            using (SqlDataReader reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    users.Add(new User
                    {
                        UserID = (int)reader["UserID"],
                        CompanyID = (int)reader["CompanyID"],
                        UserName = reader["User_Name"].ToString()
                    });
                }
            }
        }
    }
    return users;
}

Then, to get the full Company object:

public Company GetCompanyWithUsers(int companyId)
{
    Company company = getCompany(companyId); // Your existing method
    if (company != null)
    {
        company.CompanyUsers = GetUsersByCompanyID(companyId);
    }
    return company;
}

哪种方案更优？ #

• 选单JOIN查询的场景：

  • 你几乎每次获取公司信息时都需要关联的用户数据
  • 公司的用户数量不多，结果集大小可控
  • 你希望尽量减少数据库往返次数（对高并发应用很关键）

• 选两次查询的场景：

  • 你经常只需要公司基础信息，不需要用户数据（避免加载不必要的用户数据）
  • 部分公司有大量用户（拆分查询能让每个结果集更小，更易处理）
  • 你不想修改已有的getCompany方法，希望复用现有代码

两种方案的重要提醒 #

一定要使用参数化查询（比如示例中的@CompanyID参数）来防止SQL注入攻击——这是使用System.Data.SqlClient时的必备规范。

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