Implicitly convert Oracle Date type to String using dataAdapter.Fill()

Question

I am currently using ODP.net to execute a large amount of SQL, and I am retrieving the data in a uniform way using an OracleDataAdapter, and the .Fill() method, as shown:

using (var oracleCommand = new OracleCommand(query, Connection))
{
    DataSet dataSet = new DataSet();
    OracleDataAdapter dataAdapter = new OracleDataAdapter(oracleCommand);

    dataAdapter.Fill(dataSet);

    var result = new List<T>();

    foreach (DataTable table in dataSet.Tables)
    {
        foreach (DataRow row in rows)
        {
            var data = new T();
            foreach (DataColumn column in columns)
            {
                data[column.Ordinal] = row[column].ToString();
            }
            result.Add(data);
        }
    }
}

The issue I am having is that, as outlined here SQL Server Data Type Mappings, the OracleDataAdapter is implicitly converting my Date objects to .net DateTime which consequently puts the date in a different format.

The result is being put into a report thus it is important that it is represented as a String in the format that is output from the SQL server. Up until now I have simply been casting all results using the ToString() method as shown, but these dates are converted before this point.

The current solution is to use TO_CHAR on the script level, however this is not ideal, I would prefer not to have to change the SQL.

It is also not acceptable to write a method to try and parse any dates in C# as the SQL statements vary and each Date object could be under any column ordinal, so I would have to try and parse every single cell which I want to avoid entirely.

Cheers

Answer 1

As mentioned in the comments a Date doesn't have a particular format. My issue was that I wanted the results as string literals, regardless of their type. Primarily because dataAdapter.Fill would remove any TimeZone related data by converting all TimeStamps (even TimeStampTZ) to DateTime objects. SafeMapping was a potential solution to get around this:

adapter.SafeMapping.Add("*", typeof(string));

however this was seemingly deprecated at some point and now throws an exception when used. My final solution was to use

dataAdapter.ReturnProviderSpecificTypes = true;

This allowed me to handle the Date types as their respective provider specific types thus I could intercept any OracleTimeStampTZ objects before they were implicitly converted to DateTime objects, as shown:

public static string ConvertFormat(OracleTimeStampTZ ts) { var TIME_ZONE = ((ts.GetTimeZoneOffset() < TimeSpan.Zero) ? "-" : "") + ts.GetTimeZoneOffset().ToString(@"hh\:mm"); return ts.Value.ToString(NLS_TIMESTAMP_TZ_FORMAT) + TIME_ZONE; }