C#从SQL查询公司数据仅返回一条的问题排查求助

你的代码仅返回一条数据的核心原因是只读取了SqlDataReader的第一行数据，未循环遍历所有返回行，具体问题点如下：

• 仅调用了一次reader.Read()方法：该方法每次调用会将指针移动到下一行并返回是否存在有效数据，单次调用只能获取第一行。
• reader.HasRows仅用于判断是否存在数据，但不会自动遍历所有行，必须配合循环实现逐行读取。

修正后的代码 #

namespace SECDataPurge
{
    internal class Program
    {
        static void Main(string[] args)
        {
            List<Dictionary<Guid, string>> companies = GetCompanies();

            foreach(var company in companies)
            {
                Console.WriteLine(company.Keys.FirstOrDefault() + " " + company.Values.FirstOrDefault());                
            }
            Console.ReadKey();
        }

        private static List<Dictionary<Guid, string>> GetCompanies()
        {
            List<Dictionary<Guid, string>> companies = new List<Dictionary<Guid, string>>();

            using (SqlConnection conn = new SqlConnection(ConfigurationManager.AppSettings["AppDbConnectionString"]))
            {
                string query = "SELECT id, [name] FROM [dbo].[company];";
                using (SqlCommand cmd = new SqlCommand(query, conn))
                {
                    conn.Open();
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        // 循环读取每一行，直到没有更多数据
                        while (reader.Read())
                        {
                            Dictionary<Guid, string> company = new Dictionary<Guid, string>();
                            Guid companyGuid = Guid.Empty;
                            string companyName = string.Empty;

                            if (reader["id"] != DBNull.Value)
                            {
                                companyGuid = (Guid)reader["id"];
                            }

                            if (reader["name"] != DBNull.Value)
                            {
                                companyName = (string)reader["name"];
                            }

                            company.Add(companyGuid, companyName);
                            companies.Add(company);
                        }
                    }
                }
            }

            return companies;
        }
    }
}

额外优化建议 #

当前用List<Dictionary<Guid, string>>存储数据，每个字典仅包含一个键值对，结构不够直观。更推荐定义自定义类封装数据，提升代码可读性与维护性：

// 定义Company类
public class Company
{
    public Guid Id { get; set; }
    public string Name { get; set; }
}

修改GetCompanies方法返回List<Company>：

private static List<Company> GetCompanies()
{
    List<Company> companies = new List<Company>();

    using (SqlConnection conn = new SqlConnection(ConfigurationManager.AppSettings["AppDbConnectionString"]))
    {
        string query = "SELECT id, [name] FROM [dbo].[company];";
        using (SqlCommand cmd = new SqlCommand(query, conn))
        {
            conn.Open();
            using (SqlDataReader reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    Company company = new Company();
                    
                    if (reader["id"] != DBNull.Value)
                    {
                        company.Id = (Guid)reader["id"];
                    }

                    if (reader["name"] != DBNull.Value)
                    {
                        company.Name = (string)reader["name"];
                    }

                    companies.Add(company);
                }
            }
        }
    }

    return companies;
}

对应Main方法简化为：

static void Main(string[] args)
{
    List<Company> companies = GetCompanies();

    foreach(var company in companies)
    {
        Console.WriteLine($"{company.Id} {company.Name}");                
    }
    Console.ReadKey();
}

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