如何利用EmploymentDate实现ADO.NET核心SqlClient对员工的过滤

Question

在我的ASP.Net Core-6 Web中，我正在ADO.NET核心中实现SqlClient。我要按雇用日期选择员工。

我有一个实体(表)：

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; }
}

然后，我在Server中创建了这个存储过程：

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

    RETURN 1

我希望使用ADO.NET Core SqlClient在选定雇用日期的范围内对员工进行假脱机。我写了这段代码：

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;
}

如何修改上面的代码以将StartDate和EndDate的EmploymentDate包含在存储过程中？

Answer 1

你可以用：

con.Open();
command = new SqlCommand("sp_employees", con);
command.CommandType = CommandType.StoredProcedure;
    
command.Parameters.AddWithValue("@pdStartDate", startDate);
command.Parameters.AddWithValue("@pdEndDate", endDate);
    
dataReader = command.ExecuteReader();