DateTime Format after Serialization with BulkCopy

Question

I have a problem after deserializing an XML response. The Date is always set to {1/1/0001 12:00:00 AM}.

Here is my DTO class definition:

    [XmlElement]
    public System.DateTime Date_of_Birth { get; set; }

The problem is the formatting of the received XML from the server. I used a DB first approach and populated my objects from the existing DB so the class was automatically constructed with DateTime as the property type. The SQL data type is "Date". I don't have a problem when manually importing records in an excel spreadsheet, but the purpose of this program is to automate the procedure using the XML response.

An example response looks like this:

<root><i Other_Attributes="" Date_of_Birth="10/21/1999" More_Attributes="" /></root>

I've seen the suggestions like the one below:

public class SomeClass
{
    [XmlIgnore]
    public DateTime SomeDate { get; set; }

    [XmlElement("SomeDate")]
    public string SomeDateString
    {
        get { return this.SomeDate.ToString("yyyy-MM-dd HH:mm:ss"); }
        set { this.SomeDate = DateTime.Parse(value); }
    }
}

But this results in my datatable having too many columns (the extra string value) and the BulkCopy fails.

EDIT: The reason my conversion to a BulkCopy as DataTable was failing was because I was either mapping too many columns, or using the wrong accessor method. I was able to solve by changing my DTO to the following:

    [XmlIgnore]
    public DateTime Date_of_Birth { get; set; }
    [XmlAttribute(AttributeName = "Date_of_Birth")]
    public string Date_of_Birth_String
    {
        get { return this.Date_of_Birth.ToString("yyyy-MM-dd HH:mm:ss"); }
        set { this.Date_of_Birth = DateTime.Parse(value); }
    }

Then I had to change my method that converts my deserialized object to a DataTable adding the string value to the DateTime row like so:

    public static DataTable AsDataTable<T>(this IEnumerable<T> data)
    {
        PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(typeof(T));
        var table = new DataTable();
        foreach (PropertyDescriptor prop in properties)
        {
            if( prop.Name == "Date_of_Birth_String")
            {
                continue;
            }
            table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);
        }
        foreach (T item in data)
        {
            DataRow row = table.NewRow();
            foreach (PropertyDescriptor prop in properties)
            {
                if (prop.Name == "Date_of_Birth_String")
                {
                    row["Date_of_Birth"] = prop.GetValue(item) ?? DBNull.Value;
                    continue;
                }
                row[prop.Name] = prop.GetValue(item) ?? DBNull.Value;
            }
            table.Rows.Add(row);
        }
        return table;

Answer 1

You are correct that XmlSerializer does not support customized formats for DateTime properties, so you will need a surrogate string property to parse and format your DateTime in the required format.

However, in your XML the Date_Of_Birth is an attribute rather than a child element so you will need to mark the surrogate property with [XmlAttribute(AttributeName="Date_of_Birth")]:

[XmlRoot(ElementName="i")]
public class I 
{
    [XmlIgnore]
    public DateTime SomeDate { get; set; }      

    [XmlAttribute(AttributeName="Other_Attributes")]
    public string Other_Attributes { get; set; }

    [XmlAttribute(AttributeName="Date_of_Birth")]
    public string Date_of_Birth 
    { 
        get { return this.SomeDate.ToString("yyyy-MM-dd HH:mm:ss"); }
        set { this.SomeDate = DateTime.Parse(value); }          
    }

    [XmlAttribute(AttributeName="More_Attributes")]
    public string More_Attributes { get; set; }
}

[XmlRoot(ElementName="root")]
public class Root {
    [XmlElement(ElementName="i")]
    public I I { get; set; }
}

Here I used https://xmltocsharp.azurewebsites.net/ to automatically generate classes for your XML then modified the Date_of_Birth property to make use of the underlying DateTime SomeDate property.

Sample fiddle showing that your XML string can be deserialized and re-serialized to:

<root>
  <i Other_Attributes="" Date_of_Birth="1999-10-21 00:00:00" More_Attributes="" />
</root>