How to filter employee by EmploymentDate using ADO.NET Core SqlClient-CodePudding

In my ASP.Net Core-6 Web API, I am implementing SqlClient in ADO.NET Core. I want to select employees by employment date.

I have this entity (table):

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

Then I created this stored procedure in the SQL Server DB:

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

    RETURN 1

I want to spool employees between a range of selected employment date using ADO.NET Core SqlClient. I have written this code:

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

How do I modify the code above to include the StartDate and EndDate of the EmploymentDate in the stored procedure?

CodePudding user response：

You could use:

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();