Npgsql DataReader uses DateTime type for time without time zone rather than TimeSpan

Question

I'm selecting out of a table in Postgres using Npgsql. This table has a column auction_time that is type time without time zone. The data table that the reader returns casts this column auction_time to a DateTime rather than a TimeSpan as I would expect. This causes an issue when bulk copying the data to another table in MS SQLServer of type time(7).

Here is where I select out:

using (NpgsqlConnection connection = new NpgsqlConnection(String.Format(PropertyDataDB.ConnectionStringWithSearchPath, schemaName)))
{
    connection.Open();
    NpgsqlCommand command = new NpgsqlCommand
    {
        CommandText = commandText,
        Connection = connection
    };

    using (NpgsqlDataReader dataReader = command.ExecuteReader())
    {
        DataTable dt = new DataTable();
        //dt = dataReader.GetSchemaTable();
        dt.Load(dataReader);

        BulkCopy(destinationTable, dt);
    }
}

So the auction_type in dt will be of type DateTime and all the time's will have the the date 1/1/0001 appended to the front of it. How can I prevent this?

Thanks.

Answer 1

My solution:

class Model
{
    public readonly static string tableName = "tb_name";
    public Int32 id {get; private set;}
    public string auth_token {get; private set;}
    public TimeSpan column_time {get; set;} // this is the target !!

    /* more columns for model object   here.. */

    public static NpgsqlCommand Query(string sql = null )
    {
        // treturn new NpgsqlCommand from my global NpgsqlConnection  definition
        return Connection.CreateObject().Query(sql); 
    }

    public static Model FindByPk(int id)
    {
        NpgsqlCommand query = Query(string.Format("select  * from {0} where id = @id order by id asc limit 1" , tableName ));
        query.Parameters.AddWithValue("@id", id);

        NpgsqlDataReader reader = query.ExecuteReader();
        if(reader.HasRows == false)
            return null;

        Model obj = new Model();
        return (obj.SetDbProperties(reader)) ? obj : null;
    }

    protected bool SetDbProperties(NpgsqlDataReader reader)
    {
        if(reader.IsClosed)
            return false;
        try{
            reader.Read();

            for (int i = 0; i < reader.FieldCount; i++)
            {
                string key = reader.GetName(i);

                if(reader.GetValue(i) == DBNull.Value )
                    continue;

                //@todo add dinamic setter by key value
                // https://titiandragomir.wordpress.com/2009/12/22/getting-and-setting-property-values-dynamically/
                switch(key)
                {
                    case "id":
                        this.id =  reader.GetInt32(i);
                        break;
                    case "auth_token":
                        this.auth_token = reader.GetString(i);
                        break;

                    case "column_time":
                            // solution!!!!
                            var colValue = reader.GetTime(i);
                            this.horario_dia1_init = new TimeSpan(0, colValue.Hours , colValue.Minutes, colValue.Seconds, colValue.Microseconds ); 
                        break;
                }
            }
            reader.Close();
            return true;
        }
        catch(Exception e){
            Console.WriteLine(e.Message);
            return false;
        }
    }
}

Example use:

    public static void Main(string[] args)
    {

        Model model = Model.FindByPk(1);
        if(model == null)
            Console.WriteLine("record not found");

        else {
            Console.WriteLine("model.id               {0}", model.id);
            Console.WriteLine("model.auth_token       {0}", model.auth_token);
            Console.WriteLine("model.column_time      {0}", model.column_time); 
        }

        Console.Read();
    }

Answer 2

Another fix is to upgrade Npgsql to version 3.x, this issue was fixed. http://www.npgsql.org/doc/release-notes/3.0.html

Answer 3

This is what I ended up doing, only do something like this if you need to stay on verison 2.x. It works, but I don't like it at all. I just clone the data table, change the data type, and perform the "cast" when needed.

using (NpgsqlConnection connection = new NpgsqlConnection(String.Format(PropertyDataDB.ConnectionStringWithSearchPath, schemaName)))
{
    connection.Open();
    NpgsqlCommand command = new NpgsqlCommand
    {
        CommandText = commandText,
        Connection = connection
    };

    using (NpgsqlDataReader dataReader = command.ExecuteReader())
    {
        DataTable dt = new DataTable();
        dt.Load(dataReader);

        if (dt.Columns.Contains("auction_time"))
        {
            DataTable clone = dt.Clone();
            clone.Columns["auction_time"].DataType = typeof(TimeSpan);
            foreach (DataRow row in dt.Rows)
            {
                DataRow newRow = clone.NewRow();
                foreach (DataColumn column in dt.Columns)
                {
                    if (column.ColumnName == "auction_time" && !row.IsNull(column.Ordinal))
                    {
                        newRow[column.Ordinal] = ((DateTime)row[column.Ordinal]).TimeOfDay;
                    }
                    else
                    {
                        newRow[column.Ordinal] = row[column.Ordinal];
                    }
                }
            }
            dt = clone;
        }

        BulkCopy(destinationTable, dt);
    }
}