C#从SQL数据库读取数据存入List失败问题求助

许久未用C#开发，当前编写程序从SQL三张表读取数据并存入List<Product>集合。测试时直接打印DataReader的数据可正常输出，但调用GetList方法后List无数据，求排查问题。

原始代码 #

主逻辑代码 #

List<Product> products = null;
string connetionString;
SqlConnection cnn;
connetionString = @"";

cnn = new SqlConnection(connetionString);
cnn.Open();
if (cnn != null)
{
    Console.WriteLine("Connection Open!");
}

SqlCommand command = new SqlCommand("SELECT data1.zip_code ZipCode, data1.order_date as OrderDate, data1.order_number as OrderNumber, data1.first_name as firstName, data1.last_name as lastName, data2.order_number as OrderNumb2Two, data2.sku_id as SkuID, data2.quantity as Quantity, data2.price as Price, data3.article_name as articleName, data3.[weight] as Weight FROM data1,data2,data3", cnn);

var dataReader = command.ExecuteReader();

/*
while (dataReader.Read())
{
    Console.WriteLine(dataReader["ZipCode"].ToString());
    Console.WriteLine(dataReader["OrderDate"].ToString());
    Console.WriteLine(dataReader["OrderNumber"].ToString());
    Console.WriteLine(dataReader["firstName"].ToString());
    Console.WriteLine(dataReader["lastName"].ToString());
    Console.WriteLine(dataReader["SkuID"].ToString());
    Console.WriteLine(dataReader["Quantity"].ToString());
    Console.WriteLine(dataReader["Price"].ToString());
    Console.WriteLine(dataReader["articleName"].ToString());
    Console.WriteLine(dataReader["Weight"].ToString());
}*/

products = GetList<Product>(dataReader);

if(products != null)
{
    Console.WriteLine(products);
}
else
{
    Console.WriteLine(products);
}
foreach(object o in products)
{
    Console.WriteLine(o);
}

cnn.Close();

Product实体类 #

public class Product
{
    public int SkuID { get; set; }
    public Int32 Quantity { get; set; }
    public Int32 Weight { get; set; }
    public Int32 Price { get; set; }
    public Int64 OrderNumber { get; set; }
    public Int64 OrderNumb2Two { get; set; }
    public string articleName { get; set; }
    public DateTime OrderDate { get; set; }
    public int ZipCode { get; set; }
    public string firstName { get; set; }
    public string lastName { get; set; }
}

GetList泛型方法 #

public static List<T> GetList<T>(IDataReader reader)
{
    List<T> list = new List<T>();
    while (reader.Read())
    {
        var type = typeof(T);
        T obj = (T)Activator.CreateInstance(type);

        foreach (var prop in type.GetProperties())
        {
            var propType = prop.PropertyType;
            prop.SetValue(obj, Convert.ChangeType(reader[prop.Name].ToString(), propType));
            //Console.WriteLine(Convert.ChangeType(reader[prop.Name].ToString(), propType));
        }
        list.Add(obj);
    }
    return list;
}

核心问题与修复方案 #

1. 最直接原因：DataReader指针已耗尽 #

你注释掉的while(dataReader.Read())循环已经将DataReader的读取指针移到了数据末尾，后续调用GetList时，reader.Read()会直接返回false，不会执行任何循环添加数据的逻辑。

修复：删除或注释掉测试用的DataReader循环，保证调用GetList时指针处于初始位置。

2. SQL语句存在笛卡尔积风险 #

FROM data1,data2,data3是无关联条件的交叉连接，会生成三张表的笛卡尔积（数据量为三张表行数的乘积），数据逻辑完全错误，即使存入List也都是无效重复数据。

修复：添加表连接条件（根据实际业务关系调整），示例：

SELECT 
    data1.zip_code ZipCode, 
    data1.order_date as OrderDate, 
    data1.order_number as OrderNumber, 
    data1.first_name as firstName, 
    data1.last_name as lastName, 
    data2.order_number as OrderNumb2Two, 
    data2.sku_id as SkuID, 
    data2.quantity as Quantity, 
    data2.price as Price, 
    data3.article_name as articleName, 
    data3.[weight] as Weight 
FROM data1
INNER JOIN data2 ON data1.order_number = data2.order_number
INNER JOIN data3 ON data2.sku_id = data3.sku_id

3. 类型转换存在隐患 #

Convert.ChangeType(reader[prop.Name].ToString(), propType)会导致：

• 数据库字段为NULL时，ToString()直接报错
• DateTime类型转字符串再转回，可能出现格式不兼容问题
• 数值类型转字符串再转回，存在精度丢失风险

修复后的GetList方法：

public static List<T> GetList<T>(IDataReader reader) where T : new()
{
    List<T> list = new List<T>();
    var type = typeof(T);
    var properties = type.GetProperties();

    while (reader.Read())
    {
        T obj = new T(); // 用new()约束替代Activator，更简洁安全

        foreach (var prop in properties)
        {
            object value = reader[prop.Name];
            // 处理数据库NULL值
            if (value == DBNull.Value)
            {
                if (prop.PropertyType.IsValueType)
                {
                    prop.SetValue(obj, Activator.CreateInstance(prop.PropertyType));
                }
                else
                {
                    prop.SetValue(obj, null);
                }
                continue;
            }

            // 安全转换类型
            try
            {
                prop.SetValue(obj, Convert.ChangeType(value, prop.PropertyType));
            }
            catch (Exception ex)
            {
                Console.WriteLine($"转换属性{prop.Name}出错：{ex.Message}");
            }
        }
        list.Add(obj);
    }
    return list;
}

4. 资源未正确释放 #

SqlConnection、SqlCommand、IDataReader未用using语句包裹，容易导致资源泄漏。

修复后的主逻辑：

List<Product> products = null;
string connetionString = @"你的数据库连接字符串";

// 使用using自动释放资源
using (SqlConnection cnn = new SqlConnection(connetionString))
{
    cnn.Open();
    Console.WriteLine("Connection Open!");

    string sql = @"SELECT 
                    data1.zip_code ZipCode, 
                    data1.order_date as OrderDate, 
                    data1.order_number as OrderNumber, 
                    data1.first_name as firstName, 
                    data1.last_name as lastName, 
                    data2.order_number as OrderNumb2Two, 
                    data2.sku_id as SkuID, 
                    data2.quantity as Quantity, 
                    data2.price as Price, 
                    data3.article_name as articleName, 
                    data3.[weight] as Weight 
                FROM data1
                INNER JOIN data2 ON data1.order_number = data2.order_number
                INNER JOIN data3 ON data2.sku_id = data3.sku_id";

    using (SqlCommand command = new SqlCommand(sql, cnn))
    {
        using (var dataReader = command.ExecuteReader())
        {
            products = GetList<Product>(dataReader);
        }
    }
}

// 优化输出逻辑
if (products != null && products.Count > 0)
{
    Console.WriteLine($"List中共存入{products.Count}条数据");
    foreach (var product in products)
    {
        Console.WriteLine($"订单号：{product.OrderNumber} | 商品名：{product.articleName}");
    }
}
else
{
    Console.WriteLine("List为空");
}

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