npgsql: NpgsqlDataAdapter.Update on DataTable throws InvalidCastException if table contains a row of extended type

Hi Guys - I’ve setup a simple test case to load a postgres Table via NpgsqlDataAdapter into a DataSet -> DataTable - edit it in a .Net DataGridView and write it back via Update.

It’s quite straight forward - and works well for the most primitive data types. But - when I have a row with some higher type, for e.g. “time with time zone” or xml either a MissingOperator- (xml = text) or an InvalidCast-Exception occurs.

So obviously I’m missing something important here. How do I have to implement the type conversions?

Btw. Loading seems not to be the issue - the conversion from postgres to .net works fine - but on Update - the exceptions are thrown.

I’m using Npgsql (2.2.0.0) via NuGet with Target Framework 4.5 But I also tried the Npgsql download version for and with .net 2.0 - same issue.

Here the example code, To reproduce the issue - just setup a database with a table - first add only primitive data-type rows (int , uid, string, text ect.) test it - and you’ll see - update just works fine. Then add any extended data-type, like a time value, xml etc - and the update throws the exceptions

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Npgsql;
using NpgsqlTypes;

namespace PostgresCsharpTests
{
    public partial class PostgresCsharpTestsMainForm : Form
    {

        #region Connection

        NpgsqlConnectionStringBuilder   m_ConnectionString  = new NpgsqlConnectionStringBuilder();
        private const string            DBHost              = "localhost";
        private const string            DBName              = "testdb";
        private const string            DBUserName          = "testuser";
        private const string            DBPassword          = "pw";
        private const string            DBTable             = "testtable";
        private NpgsqlDataAdapter       m_DataAdapter       = new NpgsqlDataAdapter();
        private NpgsqlConnection        m_Connection        = null;

        #endregion

        public DataSet m_DataSet = new DataSet();
        public PostgresCsharpTestsMainForm()
        {
            InitializeComponent();
            InitializeDataConnection();
        }

        private void InitializeDataConnection()
        {
            m_ConnectionString.Clear();
            m_ConnectionString.Host = DBHost;
            m_ConnectionString.Database = DBName;
            m_ConnectionString.UserName = DBUserName;
            m_ConnectionString.Password = DBPassword;
            m_ConnectionString.PreloadReader = true;
            m_ConnectionString.UseExtendedTypes = false;

            m_Connection = new NpgsqlConnection(m_ConnectionString);

            SetupDataConnection();
        }

        private void SetupDataConnection()
        {
            m_Connection.Open();
            m_DataSet.Reset();
            string sQuery = "SELECT * FROM " + DBTable;
            m_DataAdapter = new NpgsqlDataAdapter(sQuery, m_Connection);
            m_DataAdapter.Fill(m_DataSet, DBTable);
            this.MainFormDataGridView.DataSource = m_DataSet;
            this.MainFormDataGridView.DataMember = DBTable;
            m_Connection.Close();
        }

        private int SubmitDataSetChanges()
        {
            m_Connection.Open();
            NpgsqlCommandBuilder cb = new NpgsqlCommandBuilder(m_DataAdapter as NpgsqlDataAdapter);
            m_DataAdapter.InsertCommand = cb.GetInsertCommand();
            m_DataAdapter.DeleteCommand = cb.GetDeleteCommand();
            m_DataAdapter.UpdateCommand = cb.GetUpdateCommand();
            DataTable table = m_DataSet.Tables[DBTable];
            int response = m_DataAdapter.Update(table);
            m_Connection.Close();
            return response;
        }

        private void MainFrom_Button_SubmitChanges_Click(object sender, EventArgs e)
        {
            SubmitDataSetChanges();
        }

    }
}

This is blocking my work for days now - I appreciate any Help, Hints or soothing words