none
How can I get the entire row from the SqlDataReader RRS feed

  • Question

  • Hi,

     

    How can I get the entire row from the SqlDataReader. How can i do this.

    I want to do this because while going through the Reader, i need to call a function that accepts datarow as input parameter.

     

    Is GetSqlValues is a good method to achieve this. if yes that what is the best method to convert object array to datarow.

     

    ....I don't want to use datset for this...

     

     

    Thanks

    SP

    Monday, July 16, 2007 4:21 PM

All replies

  • Use this code and your row is populated

     

    Code Snippet

    DataRow row;

     

    while (reader.Read())

    {

        row = new DataRow();

        row.ItemArray = new object[reader.FieldCount];

     

        reader.GetValues(row.ItemArray);

     

        // pass DataRow to function to parse it

        ParserFunction(row);

    }

     

    Monday, July 16, 2007 4:36 PM
  • Thanks for the code snippet --

    However i am getting compilation error (VB.NET) when i tried to write this code

     

    Dim dr As DataRow

    While (dReader.Read())

    dr = New DataRow 'This row is giving compilation error.

    dr.ItemArray = New Object((dReader.FieldCount) - 1) {}

    dReader.GetSqlValues(dr.ItemArray)

    ParserFunction(dr)

    End While

     

    Monday, July 16, 2007 5:50 PM
  • Sorry about that, I forgot you aren't really supposed to create a DataRow like that. Try this.

     

    Code Snippet

    Dim dr As DataRow

    Dim table As New DataTable()

     

    While (dReader.Read())

        dr = table.NewRow()

        dr.ItemArray = New Object((dReader.FieldCount) - 1) {}

        dReader.GetSqlValues(dr.ItemArray)

        ParserFunction(dr)

    End While

     

    Monday, July 16, 2007 5:57 PM
  • Thanks once again...However when i tried to run the appication i am getting error

    Input array is longer than the number of columns in this table. at line dr.ItemArray = New Object((dReader.FieldCount) - 1) {}

     

    I checked dReader.FieldCount is giving 20 field count which is correct.

     

    Any pointers?

     

    Thanks

    Monday, July 16, 2007 6:33 PM
  • Sorry, I haven't been doing this in the IDE so some of there errors maybe my fault. The problem is that the table has no columns in it.Try this:

     

    Code Snippet

    Dim dr As DataRow

    Dim table As New DataTable()

     

    table.Columns.AddRange(New DataColumn(dReader.FieldCount) {})

     

    While (dReader.Read())

        dr = table.NewRow()

        dr.ItemArray = New Object((dReader.FieldCount) - 1) {}

        dReader.GetSqlValues(dr.ItemArray)

        ParserFunction(dr)

    End While

     

    I am not sure if you should use dReader.FieldCount - 1 or  just dReader.FieldCount since this is VB and I use C# now. If you have another index problem try changing that first.

    Monday, July 16, 2007 6:49 PM
  • Hi beshaghy,

     

    No still it is giving the same error. I think the problem is

    table.Columns.AddRange(New DataColumn(dReader.FieldCount) {}) the new DataColumn is expecting a string (Column Name) and we are passing an integer in this.

     

    IS there any other way we can acheive this?

     

    Appreciate for all your help.

     

    Thanks

    Monday, July 16, 2007 7:26 PM
  • That line of code should just be declaring a new Array of DataColumns to the size of dReader.FieldCount. All of the DataColumns should default. Sorry this is hard without being able to test it. Let's try this:

    Code Snippet

     

    Dim dr As DataRow

    Dim table As New DataTable

     

    For i As Integer = 0 To i < dReader.FieldCount

        table.Columns.Add(New DataColumn(i.ToString()))

    Next

     

    While (dReader.Read())

        dr = table.NewRow()

       

        dr.ItemArray = New Object((dReader.FieldCount) - 1) {}

     

        dReader.GetSqlValues(dr.ItemArray)

        Me.ParserFunction(dr)

    End While

     

    Fingers crossed.

    Monday, July 16, 2007 7:38 PM