Microsoft SQL Server database / Visual Studio C# : date handling

I’m trying to figure out what would be the quickest and most convenient way to grab dates from the database and parse/cast them correctly into my DAL (DataAccessLayer) methods.

The columns inside my database are of datatype date.

Specifically I have this Order class:

public int Id { get; set; }
public DateTime OrderDate { get; set; }  
public DateTime ConfirmedDeliveryDate { get; set; }  
public DateTime ShippingDate { get; set; }
public Supplier Supplier { get; set; }
public List<Product> ListOfProducts { get; set; } = new List<Product>();

And then of course I have a DAL method for, let’s say, getting a list of orders from the database.

List<Order> GetAll()
{
  ... ... ...
  ... ... ...
  SqlDataReader rdr = cmd.ExecuteReader()

  while(rdr.Read())
  {
    int orderId = (int)rdr["id"];
    DateTime orderDate = ???                 // HOW TO IMPLEMENT?
    DateTime? confirmedDeliveryDate = ???    // HOW TO IMPLEMENT?
    DateTime? shippingDate = ???             // HOW TO IMPLEMENT?
    ... ...
    ... ...

  }
  ... ... ...
}

NOTE: On my web I will only need YEAR, MONTH and DAY from the above mentioned dates. Not sure if that helps you, probably not but just wanted to say.

Answer

A date column in sql maps to a DateTime in c#. There is no need to do any special parsing. Assuming you’re using a SqlDataReader, just use SqlDataReader.GetDateTime, or if you don’t know the column ordinal use the indexer and pass it the column name then cast the result to a DateTime just like you’re doing with the "id" column.

DateTime orderedDate = (DateTime)rdr["orderedDate"];
// or
DateTime orderdDate = rdr.GetDateTime(/*column ordinal*/)

If the date columns in the database are nullable, then you need to account for that in your code by making the DateTime objects nullable (DateTime?) and checking for null when reading the results from the SqlDataReader.

List<Order> GetAll()
{
    SqlDataReader rdr = cmd.ExecuteReader()
    while(rdr.Read())
    {
        int orderId = (int)rdr["id"];
        DateTime? orderedDate = null;
        if (rdr["orderedDate"] != DBNull.Value)
        {
            orderedDate = (DateTime)rdr["orderedDate"];
        }
    }
}

You could put this into an extension method on SqlDataReader if you wanted…

public static class SqlDataReaderExtensions
{
    public static DateTime? GetNullableDateTime(this SqlDataReader rdr, string columnName)
    {
        if (rdr[columnName] != DBNull.Value)
        {
            return (DateTime)rdr[columnName];
        }
        return null;
    }
}

List<Order> GetAll()
{
    SqlDataReader rdr = cmd.ExecuteReader()
    while(rdr.Read())
    {
        int orderId = (int)rdr["id"];
        DateTime? orderedDate = rdr.GetNullableDateTime("ordredDate");
    }
}

If your view only needs to display the year, month, and day then you can do that in several ways. For example, DateTime.ToShortDateString or DateTime.ToString(string).