How do I get an DataTable from IDataReader?

Question

I'm trying to get a DataTable or DataSet from an IDataReader, but I'm failing. Here's the code:

string sql = @"SELECT ID, DOCNUMBER FROM TBDOCUMENT";

using (IDbConnection conn = CreateConnection(provider, connectionString))
                {
                    conn.Open();
                    using (IDbCommand command = conn.CreateCommand())
                    {
                        command.CommandText = sql;
                        IDataReader reader = command.ExecuteReader();

                        using (reader)
                        {
                            while (reader.Read())
                            {
                                long key = reader.GetInt64(0);
                                decimal value = reader.GetDecimal(1);
                            }
                        }
                    }
                }

I'm using IDbConnection and IDbCommand because it will works with three different databases (the method CreateConnection(provider, connectionString) gets the specific type of connection according to the database). My query gets an ID (as Int64) and a DocNumber (as Decimal), but every time I try to get the decimal value, it throws an OverflowException with a message: "Conversion overflows." Both of values are important to me, but I don't know how do I get these values.

Actually, the code I'm not trying to convert to a DataTable, I have to get the value of the two without exception.

Some help?

what is the column type of DOCNUMBER in the TBDOCUMENT table? if it's an integer you should have int value = reader.GetInt... — Davide Piras, Sep 13 '11 at 12:24
Hi Davide, the type of DOCNUMBER in TBDOCUMENT is Decimal(30,0). — Guilherme Oliveira, Sep 13 '11 at 12:27

score 6 · Accepted Answer · answered Sep 13 '11 at 12:30

6

Though I haven't check by executing but it should work...

    // Load the data into the existing DataSet. 
    DataTableReader reader = GetReader();
    dataSet.Load(reader, LoadOption.OverwriteChanges,
    customerTable, productTable); 

    // Load the data into the DataTable.
    SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
    DataTable dt = new DataTable();
    dt.Load(dr);

answered Sep 13 '11 at 12:30

alloc_iNit

5,141
2
22
53

I believe that it should work. I tryed to execute here and throws an exception (OverflowException). Maybe the decimal values can't convert and then throws the exception. – Guilherme Oliveira Sep 13 '11 at 12:39

How do I get an DataTable from IDataReader?

1 Answers1