Using recordset values in SQL Server 2008 R2

Question

I use Dynamics AX 2012 with business connector in C# for retrieving data by odbc methods.

I'm using SQL Server 2008 R2 (version 10.50.2500 - not AX database).

The code looks like this:

using MIL = Miceoaodr.Dynamics.AX.ManagedInterop;
...
namespae mynamespace
{
   public class myclass
   {
     public static MIL.Session axSession = null;
     ...
     public void test()
     {
        MIL.Container c;
        OdbcDataReader r;
        OdbcConnection conn;
        OdbcCommand cmd;
        object o;
        conn = new OdbcConnection("my connection string");
        conn.open();
        cmd = new OdbcCommand("select * from mytable", conn);

        r = cmd.ExecuteReader();
        while (r.Read())
        {
           c.clear();
           for (int i = 0; i < reader.FieldCount; i++)
           {
              o = reader.getValue(i);
              c.Add(o); // **** fails sometimes
           }

        }
        c = new MIL.Container();
        c.add(0); // **** here is the problem. program halts without any **** warning!
     }
   }
}

The line with asterisks fails sometimes (c.add(0)... ).

It seems that it fails only of table-colums that their type in db that are: int, or for type that are bigint with the value = 0.

What shall I do in order code will not fails like described?

Thanks :)

Answer 1

Polupate data from odbc can be done in several ways, and not just odbcDataReader (which is inconveniant way, because need to get record by record).

When using OdbcDataAdapter class, like the link https://msdn.microsoft.com/en-us/library/system.data.odbc.odbcdataadapter%28v=vs.110%29.aspx

Also the following code may help:

...
OdbcDataAdapter d;
DataTable dt;
d = new OdbcDataAdapter(cmd);
d.Fill(dt);
... 
foreach (DataRow row in dt.Rows)
{
   foreach (DataColumn column in dt.Columns)
   { // fill the column header for the first time by column.ColumnName.
   }
   foreach (DataColumn column in dt.Columns)
   { // fill the colum values using row[column] combination.
   }
}

That's no need converting the values from database type to .net types.