In ASP.NET Core, I have a table called Products.

It has this fields: ProductName, Unit, CostPrice, SellingPrice, CreatedDate

CreatedDate is datetime. I want to filter products where CreatedDate between fromDate and toDate. I have this code:

public async Task<List<Product>> GetByDateBetweenAsync(string fromDate, string toDate)
{
    var sql = "SELECT * FROM Products WHERE CreatedDate ";
    using (var connection = new SqlConnection(configuration.GetConnectionString("DefaultConnection")))
    {
        connection.Open();
        var result = await connection.QuerySingleOrDefaultAsync<Product>(sql, new { CreatedDate = fromDate });
        return result;
    }
}

fromDate and toDate should be in this fromat: yyyymmdd.

How do I modify my code above to achieve this using Dapper?

Thanks

CodePudding user response：

Change to this:

public async Task<List<Product>> GetByDateBetweenAsync(string fromDate, string toDate)
{
    var startAt=DateTime.Parse(fromDate);
    var endAt=DateTime.Parse(toDate);

    var sql = $"SELECT * FROM Products WHERE CreatedDate BETWEEN {startAt} AND {endAt}";

    using (var connection = new SqlConnection(configuration.GetConnectionString("DefaultConnection")))
    {
        connection.Open();
        var result = await connection.QuerySingleOrDefaultAsync<Product>(sql);
        return result;
    }
}

CodePudding user response：

1. The SQL query should be a parameterized query. Dapper does support the Parameterized Query.

2. To query with the result of multiple rows, you need either of these methods: .Query(), .Query<T>(), .QueryAsync() or QueryAsync<T>(). Reference: Selecting Multiple Rows Of Data With Dapper

3. Work with DateTime.TryParseExact() to safely convert a date string with a pattern to DateTime.

public async Task<List<Product>> GetByDateBetweenAsync(string fromDate, string toDate)
{
    bool isValidDateFrom = DateTime.TryParseExact(fromDate, "yyyyMMdd", CultureInfo.InvariantCulture,
                              DateTimeStyles.None, out dateFrom);

   bool isValidDateTo = DateTime.TryParseExact(toDate, "yyyyMMdd", CultureInfo.InvariantCulture,
                              DateTimeStyles.None, out dateTo);

   if (!(isValidDateFrom && isValidDateTo))
   {
       // Handle for parsed fromDate & toDate is not a valid date
       return new List<Product>();
   }

    var parameters = new { DateFrom = dateFrom, DateTo = dateTo };

    var sql = "SELECT * FROM Products WHERE CreatedDate BETWEEN @DateFrom AND @DateTo";
    using (var connection = new SqlConnection(configuration.GetConnectionString("DefaultConnection")))
    {
        connection.Open();
        var result = await connection.QueryAsync<Product>(sql, parameters);
        return result;
    }
}