ADO.NET pass a single record from dbReader

[kleinma]

using the dbReader I loop through a table and need to pass each ROW to a function. Anyone know what property, method of the SqlDataReader I can use to get the full row and pass it

[SeanGrebey]

Why not just pass a reference to the DataReader itself?

[LeeSalter]

If you use the OleDBDataReader you can use it like this:-

 Dim cmd1 As New OleDbCommand
 
 cmd1.Connection = cnn1 'your database connection object
 cmd1.CommandText = _
            "SELECT * FROM TABLENAME"

 Dim drd1 As OleDbDataReader
 
 drd1 = cmd1.ExecuteReader
 do while drd1.read
      'each row is returned here...
      messagebox.show(drd1.GetString(ColumnNumber))
 loop

HTH

[nemaroller]

Second SeanGray's answer...

[techgnome]

Originally posted by SeanGrebey
Why not just pass a reference to the DataReader itself?

Because that's inefficient. That's why.

Originally posted by LeeSalter
If you use the OleDBDataReader you can use it like this:-

 Dim cmd1 As New OleDbCommand
 
 cmd1.Connection = cnn1 'your database connection object
 cmd1.CommandText = _
            "SELECT * FROM TABLENAME"

 Dim drd1 As OleDbDataReader
 
 drd1 = cmd1.ExecuteReader
 do while drd1.read
      'each row is returned here...
      messagebox.show(drd1.GetString(ColumnNumber))
 loop

HTH

I'm fairly new to .NET, but know enough that that isn't much help. Doesn't pass the entire row, just the specified column's value.

Ok, on to the issue. Kleinma, see what you can find out about DataRow... I use it when dealing with DataSets/DataTables in an app I'm working on. I don't know if it works the same for a Reader as it does for the DT (which is what I'm currently using).

TG

[nemaroller]

I don't see how passing a datareader by reference is inefficient.

Also, DataRow does not exist for a datareader, you would have to populate a table first, then pass the appropriate row. That may work for his solution, it may be unncessary.

If Kleinma still have issues with passing a datareader by reference, he can simply declare an empty object array and use it as the target of the DataReader.GetValues() method.

While dr.Read()
 Dim o() As Object
 dr.GetValues(o)
 CallAFunction(o)
End While

If all he wants is a common area to inspect column values, passing the datareader by reference to a common function is fine. You only have to worry about closing the connection properly.

[hellswraith]

Originally posted by techgnome
Because that's inefficient. That's why.

That isn't true at all, you are passing a reference to an object, which would be WAY more efficient than passing a row of values.

[techgnome]

Originally posted by hellswraith
That isn't true at all, you are passing a reference to an object, which would be WAY more efficient than passing a row of values.

Uncle, uncle, uncle..... OK, I'll conceed that, BUT, we don't know what the requirements are either. Maybe he's calling a func he doesn't have control over and can't use the object ref.... who knows...

TG

[SeanGrebey]

If he has to have a DataRow, he might as well fill a datatable instead of a datareader and step through that instead. Otherwise I stand by my pass a ref to the DataReader statement.

[nemaroller]

Ok... here's the final say( to have a datarow passed, in case you dont want to pass a ref variable to the datareader, or its a
function you can't control and it NEEDS a datarow):

Dim dr As System.Data.SqlClient.SqlDataReader
        dr = sqlcommand1.ExecuteReader(CommandBehavior.CloseConnection)
        Dim dbe As New System.Data.Common.DbEnumerator(dr)
        Dim dbr As System.Data.Common.DbDataRecord
        dbe.MoveNext()
        While dr.Read
            dbr = dbe.Current
            callme(dbr)
            dbe.MoveNext()
        End While
        dr.Close()
 
    End Sub
 
    Private Function callme(ByVal f As System.Data.Common.DbDataRecord)
        MessageBox.Show(f(0))
         'or 
         'Messagebox.show(f("COLUMNNAME"))
    End Function

[kleinma]

sorry guys was at lunch, but i am thankful for the posts.

The reason I didnt want to pass the entire reader was because im loading a collection object called PRODCUTS, but the load method itself is a friend function in the PRODUCT class, not the products class.. because all the variables in the PRODUCT class are private, and the public properties are read only. So I can't just create a new PRODUCT object in the PRODUCTS class, and load each PRODUCT and add it to the collection

I also didnt want to call a new DB query for EACH product to load it as that would be stupid

so what I wanted it to do was call the select statement to get the records in the PRODUCTS class, and then loop and call the friend function in the PRODUCT class in the loop just passing that row, and in turn, fill the private vars of the PRODUCT class..

sorry i know that probably sounds confusing...

I guess I could either try nemaroller's suggestion using that System.Data.Common.DbDataRecord object, or I guess I could pass ByRef the reader each time and just load the record from the current position of the reader..

which of those 2 would you guys suggest?

[nemaroller]

I would go with the pass byRef on the dreader first....

Of course, it kinda does not make sense to pass a datareader to a 'Products' class... seems more you would have a SetValues method that took an object array...

While dr.Read()
 Dim o() As Object
 dr.GetValues(o)
 CallAFunction(o)
End While

[techgnome]

All things considered, I'd pass the object byRef..... no compelling reason to do otherwise....

TG