如何从SQL数据表获取数据并赋值给C#中的List<...>

首先定义与SQL员工表对应的实体类，假设你的员工表包含Id、Name、Department、Salary字段：

public class Employee
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Department { get; set; }
    public decimal Salary { get; set; }
}

方法1：原生ADO.NET实现 #

适合需要精细控制数据库操作的场景：

using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;

public List<Employee> GetEmployeesWithAdoNet(string connectionString)
{
    var employees = new List<Employee>();
    string sql = "SELECT Id, Name, Department, Salary FROM Employees";

    // using语句确保数据库资源自动释放
    using (var connection = new SqlConnection(connectionString))
    {
        connection.Open();
        using (var command = new SqlCommand(sql, connection))
        {
            using (var reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    var employee = new Employee
                    {
                        Id = reader.GetInt32(reader.GetOrdinal("Id")),
                        Name = reader.GetString(reader.GetOrdinal("Name")),
                        Department = reader.GetString(reader.GetOrdinal("Department")),
                        Salary = reader.GetDecimal(reader.GetOrdinal("Salary"))
                    };
                    employees.Add(employee);
                }
            }
        }
    }
    return employees;
}

方法2：Entity Framework Core（EF Core）实现 #

ORM框架能大幅简化代码，减少重复操作：

第一步：配置DbContext #

using Microsoft.EntityFrameworkCore;
using System.Collections.Generic;

public class AppDbContext : DbContext
{
    public DbSet<Employee> Employees { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        // 替换为你的数据库连接字符串
        optionsBuilder.UseSqlServer("YourConnectionStringHere");
    }
}

第二步：查询数据并转为List #

public List<Employee> GetEmployeesWithEfCore()
{
    using (var context = new AppDbContext())
    {
        return context.Employees.ToList();
    }
}

注意事项 #

• 连接字符串建议从配置文件（如appsettings.json）读取，避免硬编码
• 实体类属性与表字段名称不一致时，可通过[Column("ColumnName")]特性或Fluent API做映射
• 需添加异常处理（如try-catch），处理数据库连接失败、查询错误等情况
• 大数据量查询建议分页，比如EF Core用Skip()+Take()，ADO.NET写分页SQL，避免内存溢出

内容的提问来源于stack exchange，提问作者Việt Hoàng Trương