Pass DataReader Current Row to Method

[Bernie]

Does anyone know how to pass the current row of a DataReader as a paramater in the method call?

I have a datareader that is running through all the rows retrieved from the database. For each row, I perform some tasks. I'd like to move some of that code off into a method to make it cleaner. So it would look something like this;

while (myReader.read)
myMethod(the current row in the data reader)
end while

If I read all the rows into a datatable, I can do a for each row and pass the row to a method. I'm just curious if there is any way to do this with a datareader.

I'd rather not read all the rows into a table first, due to speed issues.

Bernie

 

[jmcilhinney]

If you've used the data reader at all then you know that it has no property for the current row. All fields are accessed via the reader itself. As such, the only thing you could do would be to pass the data reader itself as an argument. In that case, it would be pointless having the loop and the processing separated.

You should probably define a type that represents a single record. In your loop you create an instance of that type and popuate its fields from the reader. You then pass that object as an argument to the processing method. Alternatively you could just pass each field value as a separate argument.

 

[Bernie]

Thanks for the quick replay. I didn't think there was but I figured I'd throw it out and make sure I didn't miss anything.

Bernie

 

[cjard]

Take a read of the DW2 link in my signature, section Creating a Simple Data App
At the end of that tutorial you'll be aware of the uses od DataSet, DataTable and TableAdapter

Youll get your data like:
Dim d as MyDataTable = myTableAdapter.GetSomeTable(someParametersToYourQuery)

You can loop it like:
For Each dr as MyDataRow in dt
*methodCall(dr)
Next


DataReader is typically used when youre dumping many rows to disk or network or the flow of data is one way only.. The above menthod will download all rows from the query into memory, so don't use it for millions opf rows, but do use it where the flow of data is 2 way (back to db) or there's a lot of client side processing of the data

 

[Bernie]

That's what I'm doing now and it's a large table with very slow response, so there is plenty of spare time. I was hoping to find an easy way to avoid the delay of waiting on all the rows to load to a table. This application is one way only, no updating the data.

 

[cjard]

Ah.. ok.. Then as jmc advised, and similar to Microsoft's example here:
Retrieving Data Using a DataReader

You basically start a loop while reader.Read() //it returns false when there is nothing more to read
and use the GetXXX methods of the reader to retrieve whatever kind of data you require

Handing it off to a separate method to do this probable wont clean up your code. Instead do:

Using connection
        Dim command As SqlCommand = New SqlCommand( _
          "SELECT CategoryID, CategoryName FROM Categories;", _
          connection)
        connection.Open()

        Dim reader As SqlDataReader = command.ExecuteReader()

        If reader.HasRows Then
            Do While reader.Read()
                Dim age as Integer = reader.GetInt32(0) 
                Dim name as String = reader.GetString(1)

                ProcessCategory(name, age)[B] 'this is what you should write, not a method that takes a datareader[/B]
            Loop
        Else
            Console.WriteLine("No rows found.")
        End If

        reader.Close()
    End Using

 

[Bernie]

The record has 63 fields. Your example is where I started and it looked like a mess, hahaha. That's why I was looking to hand off a row as a whole rather than storing each one as a value and passing as parameters. I think the current method of creating a datatable and then walking through the table is going to be the cleanest code. Its an overnight processing task moving data out of a legacy system into a newer system, so the work load will not be expanding. So I know my time hit for making the table is finite at it's current acceptable level.

I think this one is as good as its going to get.

Thanks,

Bernie

 

[cjard]

Not much has been said about how the transfer of data is being done between systems, but you could probably just use a loop rather than pulling everything out individually

Suppose your new system has a stored procedure/insert query with 63 parameters, just make a relevant command and parameterize it:

INSERT INTO newTable VALUES(@param0, @param1, @param2..)

Then in your 63 long messy loop instead do:

While reader.Read()
  For i = 0 to 62
    myCOmmand.Parameters("@param" & i).Value = reader.GetString(i)
  Next i
  myCOmmand.Execute()
Wend

If you want to get more fancy by use of an IF (datatype is date) Then (read from the reader as a date instead of string), inside your for loop then cool.. dont need to know the names.. just make sure your query returns data in the same order as the insert/proc is expecting it.. making everything a string (and convert in the proc/insert) might make your life easier in vb.. but it;s quite an easy thing to put an if inside the forloop