如何修改ADO.NET Core SqlClient代码，按入职日期范围筛选员工

我在ASP.NET Core 6 Web API里使用ADO.NET Core的SqlClient实现功能，需要按入职日期范围筛选员工。已定义Employee实体、SQL Server存储过程sp_employees，现需修改现有GetEmployees方法，传入起止日期参数到存储过程，获取指定入职日期区间的员工列表。

现有相关代码 #

Employee实体

public class Employee
{
    public int EmployeeId { get; set; }
    public string Firstname { get; set; }
    public string Lastname { get; set; }
    public string Email { get; set; }
    public string EmploymentDate { get; set; }
}

存储过程sp_employees

CREATE PROCEDURE [dbo].[sp_employees]
    @pdStartDate datetime,
    @pdEndDate datetime
AS
    SELECT
         *
    FROM
        [Employees].[dbo].[employees] 
    WHERE
       EmployementDate BETWEEN @pdStartDate AND @pdEndDate

    RETURN 1

现有GetEmployees方法

public IEnumerable<Employee> GetEmployees()
{
    List<Employee> employeelist = new List<Employee>();
    using (con = new SqlConnection(connection))
    {
        con.Open();
        command = new SqlCommand("sp_employees", con);
        command.CommandType = CommandType.StoredProcedure;
        dataReader = command.ExecuteReader();
        while (dataReader.Read())
        {
            Employee employee = new Employee();
            employee.EmployeeId = Convert.ToInt32(dataReader["EmployeeId"]);
            employee.Firstname = dataReader["Firstname"].ToString();
            employee.Lastname = dataReader["Lastname"].ToString();
            employee.Email = dataReader["Email"].ToString();
            employee.EmploymentDate = Convert.ToDateTime(dataReader["EmploymentDate"].ToString());

            employeelist.Add(employee);
        }
        con.Close();
    }
    return employeelist;
}

修改步骤及最终代码 #

1. 修正实体与存储过程的细节问题

• 把Employee实体的EmploymentDate类型从string改为DateTime，避免不必要的类型转换
• 修正存储过程中EmployementDate的拼写错误（应为EmploymentDate），否则筛选逻辑不生效

修正后的Employee实体：

public class Employee
{
    public int EmployeeId { get; set; }
    public string Firstname { get; set; }
    public string Lastname { get; set; }
    public string Email { get; set; }
    public DateTime EmploymentDate { get; set; } // 改为DateTime类型
}

修正后的存储过程：

CREATE PROCEDURE [dbo].[sp_employees]
    @pdStartDate datetime,
    @pdEndDate datetime
AS
    SELECT
         *
    FROM
        [Employees].[dbo].[employees] 
    WHERE
       EmploymentDate BETWEEN @pdStartDate AND @pdEndDate -- 修正拼写错误

    RETURN 1

2. 修改GetEmployees方法传入参数

调整方法签名添加起止日期参数，通过SqlParameter将参数传入存储过程，同时优化资源释放逻辑：

public IEnumerable<Employee> GetEmployees(DateTime startDate, DateTime endDate)
{
    var employeelist = new List<Employee>();
    // 使用局部变量+using语句自动释放资源，避免类字段导致的连接泄漏
    using (var con = new SqlConnection(connection))
    {
        con.Open();
        using (var command = new SqlCommand("sp_employees", con))
        {
            command.CommandType = CommandType.StoredProcedure;
            
            // 向存储过程添加参数，指定参数类型避免隐式转换问题
            command.Parameters.Add("@pdStartDate", SqlDbType.DateTime).Value = startDate;
            command.Parameters.Add("@pdEndDate", SqlDbType.DateTime).Value = endDate;
            
            using (var dataReader = command.ExecuteReader())
            {
                while (dataReader.Read())
                {
                    var employee = new Employee
                    {
                        EmployeeId = Convert.ToInt32(dataReader["EmployeeId"]),
                        Firstname = dataReader["Firstname"].ToString(),
                        Lastname = dataReader["Lastname"].ToString(),
                        Email = dataReader["Email"].ToString(),
                        EmploymentDate = Convert.ToDateTime(dataReader["EmploymentDate"])
                    };

                    employeelist.Add(employee);
                }
            }
            // 无需手动关闭连接，using语句会自动处理
        }
    }
    return employeelist;
}

额外注意事项 #

• 确保connection是从配置文件（如appsettings.json）读取的合法连接字符串，不要硬编码
• 如果需要支持日期参数为空的场景（如仅传开始/结束日期），可修改存储过程参数为可空（@pdStartDate datetime = null），并调整WHERE条件，同时方法参数改为DateTime?类型处理null值
• 所有实现IDisposable的对象（SqlConnection、SqlCommand、SqlDataReader）都用using包裹，确保资源自动释放

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