DataTable.Load(IDataReader) fails on geography column

Question

I have some code like the following that fills a DataTable using an arbitrary select command.  But when the reader contains a column with geography datatype I get the exception seen below:

 

SqlCommand cmd = new SqlCommand(command, conn);
SqlDataReader reader = cmd.ExecuteReader();
DataTable table = new DataTable();
table.Load(reader);

Unhandled Exception: System.Data.DataException: Type 'Microsoft.SqlServer.Types.SqlGeometry, Microsoft.SqlServer.Types, Version=10.0.0.0, Cu
lture=neutral, PublicKeyToken=89845dcd8080cc91' does not implement IComparable interface. Comparison cannot be done.
   at System.Data.Common.SqlUdtStorage.CompareValueTo(Int32 recordNo1, Object value)
   at System.Data.Index.CompareRecords(Int32 record1, Int32 record2)
   at System.Data.RBTree`1.RBInsert(Int32 root_id, Int32 x_id, Int32 mainTreeNodeID, Int32 position, Boolean append)
   at System.Data.Index.InitRecords(IFilter filter)
   at System.Data.Index..ctor(DataTable table, IndexField[] indexFields, DataViewRowState recordStates, IFilter rowFilter)
   at System.Data.DataTable.GetIndex(IndexField[] indexDesc, DataViewRowState recordStates, IFilter rowFilter)
   at System.Data.DataColumn.get_SortIndex()
   at System.Data.DataColumn.IsNotAllowDBNullViolated()
   at System.Data.DataTable.EnableConstraints()
   at System.Data.DataTable.set_EnforceConstraints(Boolean value)
   at System.Data.DataTable.EndLoadData()
   at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, I
nt32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue)
   at System.Data.Common.DataAdapter.Fill(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
   at System.Data.Common.LoadAdapter.FillFromReader(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
   at System.Data.DataTable.Load(IDataReader reader, LoadOption loadOption, FillErrorEventHandler errorHandler)

However, if I use an SqlDataAdapter instead of an SqlDataReader then I am able to successfully fill a DataTable with my results when they contain a geography column:

 

SqlCommand cmd = new SqlCommand(command, conn);
DataTable table = new DataTable();
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
adapter.Fill(table);

What's the difference here?  The problelm is that I already have a lot of code that passes around SqlDataReader objects, and it would be a lot of work (and testing) to replace it with SqlDataAdapter.  Can I somehow fill a DataTable using a SqlDataReader when the result set contains UDT's?

 

Answer 1

I can't reproduce your error with the four lines of code you have here.

SqlCommand cmd = new SqlCommand(command, conn);
SqlDataReader reader = cmd.ExecuteReader();
DataTable table = new DataTable();
table.Load(reader);

It works fine for me. Is that you're only code? If you try it with the AdventureWorks2008 database and "select SpatialLocation from Person.Address", do those exact four lines of code produce the exception you're seeing? It doesn't produce an exception for me, and I also tried a geometry column separately.

If so, what's the version of System.Data.dll you have installed on the client (mine is 2.0.50727.3053)?

Cheers,
Bob Beauchemin
SQLskills

Answer 2

Hi,

Have the problem solved? If so please share the solution(s) with us, it could help others who have similar problems.

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Answer 3

Sorry about the very long hiatus.  I had to move on to other things, and I was completely stuck on this problem.

I discovered a simple workaround.  The problem only occurs when the spatial column in the table I'm querying is set to "not null".  If the column allows nulls then the problem doesn't occur.

Bob -- I tried the query you suggested on the AdventureWorks2008 database.  Below is my program in its entirety and it worked fine.  The SpatialLocation column in the Person.Address table does allow nulls, so I temporarily modified it to not allow nulls and reran the program.  It failed with the same exception and stack trace as I described above.  I changed the column back to allow nulls and the program worked again.

Perhaps this is a documented feature and I just don't know about it.  For now I'm just going to set my spatial columns to allow nulls and leave it at that.

using System;
using System.Data;
using System.Data.SqlClient;

namespace TestDataTable
{
    class Program
    {
        static void Main(string[] args)
        {
            SqlConnection conn = new SqlConnection("Server=.;Database=AdventureWorks2008;Integrated Security=true;");
            conn.Open();
            
            SqlCommand cmd = new SqlCommand("select SpatialLocation from Person.Address", conn);
            SqlDataReader reader = cmd.ExecuteReader();
            DataTable table = new DataTable();
            table.Load(reader);
            Console.WriteLine("Number of rows read:  " + table.Rows.Count);
        }
    }
}

Answer 4

Thanks for feeding that back, Chris. Sounds like a bug with DataTable.Load to me, you should report this on Connect.

Cheers,
Bob Beauchemin
SQLskills

Answer 5

Hi Chris,

If you have submitted a feedback on Microsoft Connect, please share us the link that can help others who have the same problem.

Thanks

---

ChunSong Feng
Microsoft Online Community Support

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.