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 :)

I see no error. I see blog on http://stackoverflow.com/questions/1058322/anybody-got-a-c-sharp-function-that-maps-the-sql-datatype-of-a-column-to-its-clr I think that I shall convert between sqltype to .net type, but I didn't find any class that do that conversion. Thanks :) — Eitan, Mar 02 '15 at 13:14
If c.add(o) fails some times, it should be simple to put a try catch around it and figure out why it fails? — Allan S. Hansen, Mar 02 '15 at 14:03
No I know why is this fails. The type in sql (sqldbtype) is not the same as .net type, so I need doing conversion to that. Thanks :) — Eitan, Mar 02 '15 at 14:10

score 0 · Accepted Answer · answered Mar 02 '15 at 18:41

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.

Using recordset values in SQL Server 2008 R2

1 Answers1