1

I'm running into some trouble trying to load data into a DataTable using an IDataReader. To keep it really simple, I just call ExecuteReader() on the command, create a DataTable, call Load() on it and feed it the object implementing IDataReader:

...
if(dataReader.HasRows)
{
    DataTable tempDT = new DataTable();                             
    tempDT.Load(dataReader);
    ....
}
...

This works in the vast majority of cases. However, in (rare) circumstances, I get the following exception (column name is obviously variable - in this case, it's ID): 

Error - MaxLength applies to string data type only. You cannot set Column `ID` property MaxLength to be a non-negative number

I investigated the source table I was trying to load, and I suspect that the problem stems from it having a VARCHAR(256) ID column, that is a Required, Unique, Key (the issue doesn't seem to occur when the PK is a regular old int). This type of situation is really uncommon in the source data, and while it definitely isn't ideal, I can't modify the schema of the source data.

I took a look at the SchemaTable in more detail, and I am at a loss:

  • ColumName - ID
  • ColumnSize - 256
  • ProviderType - NVarChar
  • DataType - {Name = "String" FullName = "System.String"}
  • IsIdentity - True
  • IsKey - True
  • IsAutoIncrement - True
  • IsUnique - True

It just doesn't make sense to me. The source table uses unique codes as the ID, and while it isn't the way I would've designed it, it's.. fine. But I don't understand how a String/Varchar can ever be an identity, auto-increment, etc.

Unfortunately, I'm at the mercy of this source data and can't mess with it, so I'm hoping someone here might have more insight into what exactly is going on. Can anyone conceive of a way for me to Load() my DataTable without applying all the constraints from the IDataReader source data? Is there an entirely alternative approach that would avoid this problem?

Thanks for reading, thanks in advance for any assistance. It's my first question so be gentle. If there's any more information that would help, please let me know!

EDIT: Some people asked for the full code for loading the DataTable. Appended here. Should add that the CacheCommand/etc. comes in from this 'InterSystems.Data.CacheClient' assm. Kinda hoping the problem can be approached more generically. In this case, the Query string is just a 'SELECT TOP 10 *' test.

using (CacheConnection cacheConnection = new CacheConnection())
{
    cacheConnection.ConnectionString = connectionString;

    cacheConnection.Open();

    using (CacheCommand cacheCommand = new CacheCommand(Query, cacheConnection))
    {                       
        using (CacheDataReader cacheDataReader = cacheCommand.ExecuteReader())
        {
            if (cacheDataReader.HasRows)
            {
                DataTable tempDT = new DataTable();
                tempDT.Load(cacheDataReader); // Exception thrown here.
                cacheConnection.Close();
                return tempDT;
            }
            else
            {
                cacheConnection.Close();
                return null;
            }
        }
    }
}

EDIT 2: In case it's not clear, I'm trying to extract the entirety of a (small) table from the Cache DB into a DataTable. I normally do this by calling dataTable.Load(cacheDataReader), which works fine 99% of the time, but breaks when the source table in the Cache DB has an identity column of type VARCHAR.

Calling Load() on my DataTable object (which is empty) causes it to infer the schema based on the result set from the imported IDataReader (in this case, CacheDataReader). The problem is that the schema in the CacheDataReader specifies the data in the list above^, and DataTable doesn't seem to allow the MaxLength property, even though the type is VARCHAR/String.

Jack K
  • 71
  • 3
  • 1
    What database is this? – Steve Jan 12 '16 at 20:03
  • 2
    Share the SQL code to create table to reproduce the problem. – Reza Aghaei Jan 12 '16 at 20:14
  • @JackK can you show how you are declaring and or filling the DataReader..? show us your Sql statement please.. – MethodMan Jan 12 '16 at 20:17
  • using the `.Load` method I can load perfectly fine using datatable and datareader on my end.. `Check the actual database` and make sure that you are actually returning non negative or null values for the ID column can you manually run a query to check – MethodMan Jan 12 '16 at 20:19
  • @Steve Databases is an Intersystems Cache db (I don't have much experience with this DB - it doesn't appear many people do). – Jack K Jan 12 '16 at 20:21
  • @MethodMan Sure I'll edit it into the post. – Jack K Jan 12 '16 at 20:22
  • here is a reference link that you can revert to as well http://docs.intersystems.com/ens20091/csp/docbook/DocBook.UI.Page.cls?KEY=RSQL the error kind of gives you a hint on what the issue(s) is / are.. – MethodMan Jan 12 '16 at 20:23
  • how many rows do you normally return what does the SQL query look like..? are you returning `1` datatable or many datatables also when using the `.HasRows` method you need to check this within a `While Loop` if you are returning on a single datatable the use the `.Fill` method to populate the data.. are you allowed to edit the code if not then I would do this in a while loop you do not need to call the `.Close()` since you are inside of a `using(){}` – MethodMan Jan 12 '16 at 20:41
  • @MethodMan thanks for your reply. I'm only returning a single datatable, so .Fill() is definitely an option. While I didn't write the actual ExecuteQuery() method (the one I edited in at the end that returns a DataTable), I _am_ able to edit that as much as I need to. The query is just 'SELECT TOP 10 * FROM table.', but in use it will be the entire table. I don't think I need a while() - the code works totally fine for tables with normal, integer ID PKs. I only run into trouble when the primary key is a non int - can return rows just fine, just can't Load() the DataTable. I'll try Fill() tho. – Jack K Jan 12 '16 at 21:01
  • @MethodMad I added some more description to the end of the post. Should make things a little clearer. – Jack K Jan 12 '16 at 21:10
  • if your primary keys are defined as `Int` then you have some data issues the bad data needs to be cleaned up and or deleted or you need to alter your Select statement I am going to test some SQL that you can use to determine if values in a column are numeric.. I will be right back – MethodMan Jan 12 '16 at 21:10

1 Answers1

0
SELECT TOP 10 * FROM table
WHERE IsNumeric(ColumName) = 0

This will return only data where the Primary Key is of type Int

MethodMan
  • 18,625
  • 6
  • 34
  • 52
  • unfortunately, I need to extract a table where the Primary Key _isn't_ of type int. It's a varchar/string in the DataTable, but the DataTable seems to think it's like, a mixture of both an int and a varchar - It disallows a MaxLenght like an int, but it says the type is varchar. – Jack K Jan 12 '16 at 21:24
  • then change it to = 0 it will pull back all varchar values -check the edit – MethodMan Jan 12 '16 at 21:26