Removing a DataTable from a DataSet causes an Invalid Cast Exception

Question

I have some code that I'm using to attempt to force a sort on a pair of DataTables within a DataSet. The DataSet is used by some generic code to populate DataGridViews and is called by multiple points within the solution. Note that I cannot change any of the code that takes the DataSet and shows the DataTables in the DataGridViews. All I can do is setup the DataSet first and then pass that.

I have one DataSet with two tables, named "OBSSettlementPrice" and "BrokerSettlementPrice". The DataSet is called ResultsDs. My code is:

DataTable dtOBS;
DataTable dtBroker;

ResultsDs.Tables["OBSSettlementPrice"].DefaultView.Sort = "SettlementDate, Product, DeliveryMonth";
ResultsDs.Tables["BrokerSettlementPrice"].DefaultView.Sort = "BrokerName, SettlementDate, Product, DeliveryMonth";
dtO = ResultsDs.Tables["OBSSettlementPrice"].DefaultView.ToTable();
dtB = ResultsDs.Tables["BrokerSettlementPrice"].DefaultView.ToTable();
ResultsDs.Tables.Remove(ResultsDs.Tables["OBSSettlementPrice"]);
ResultsDs.Tables.Add(dtOBS);
ResultsDs.Tables.Remove(ResultsDs.Tables["BrokerSettlementPrice"]); // error occurs here
ResultsDs.Tables.Add(dtBroker);

The aim is to set the sort on the default view for each data table, create a view from that and turn it into an independent table. Then to replace each of the original tables with the new ones.

I get an error on the last but one line, where I'm removing the table BrokerSettlementPrice. The error is:

  HResult=0x80004002
  Message=System.InvalidCastException: Unable to cast object of type 'System.Data.DataTable' to type 'OBSSettlementPriceDataTable'.

Before the execution of that line there are two tables in ResultsDs.Tables. Afterwards, only OBSSettlementPrice is there.

Note that the solution uses a DataSet definition files (.xsd files), where the tables are called BrokerSettlementPrice and OBSSettlementPrice, so these are strongly-typed. How do I remove the old versions of the tables and replace them with the new ones?

Answer 1

Just remove the table with ResultsDs.Tables.Remove("OBSSettlementPrice"); (or with "BrokerSettlementPrice").

The DataTableCollection.Remove Method has an overload accepting a string.