locked
DataTable.Load(IDataReader) fails on geography column RRS feed

  • 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?

     

    Monday, September 21, 2009 6:46 PM

Answers

  • 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);
            }
        }
    }
    
    Friday, October 30, 2009 8:17 PM

All replies

  • 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

    Tuesday, September 22, 2009 5:25 AM
  • 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.
    Tuesday, September 29, 2009 2:40 AM
  • 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);
            }
        }
    }
    
    Friday, October 30, 2009 8:17 PM
  • 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
    Friday, October 30, 2009 9:24 PM
  • 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.
    Monday, November 2, 2009 3:08 AM
  • Apparently still an issue. See "SqlException: Assembly was not found in current database or version did not match."
    Wednesday, August 3, 2011 9:15 PM