How to load from datareader into a grid

[bnathvbdotnet]

Dim helper As AdoHelper
Dim drsql As IDataReader
Dim sconn As String

helper = GetAdoHelper()
sconn = GetConnectionString()

drsql = helper.ExecuteReader(sconn, CommandType.Text, "Select * From Customers")

drsql is a datareader object and now I would like to load its content into a grid.

How can I do that?

[hellswraith]

grid.datasource = drsql

[CyberHawke]

sorry, HW, that won't work

A DataReader is like a Forward Only cursor. You can read it once and then it's done.

If you want to use your Reader to get data into your Grid, you will have to write code to manually translate the contents of your Reader into values you can insert into your Grid.

I suggest dropping the Reader and filling a datatable instead. You get all functionality of a table and you can hook it directly to your grid.

[bnathvbdotnet]

so how to read the content of datareader and populate into datagrid.

any suggestions are very helpful.
thanks

[nemaroller]

You can use the datareader to populate the datatable.

Dim dt As New DataTable
dt.Columns.Add(New DataColumn("pkSomething", Gettype(Integer)))
dt.Columns.Add(New DataColumn("SomeText", GetType(String)))
 
Dim datar As DataRow
While dr.read
  [b]datar = dt.NewRow()
  datar(0) = dr(0)
  datar(1) = dr(1)
  dt.Rows.Add(datar)[/b]
End While
dr.close
dt.acceptchanges
datagrid1.datasource = dt
 
 
'or
 
            [b]datar = dt.NewRow
                Dim o(1) As Object
                dr.GetValues(o)
                datar.ItemArray = o
                o = Nothing
                dt.Rows.Add(datar)[/b]

[CyberHawke]

I don't want to sound sarcastic, (ok maybe a little) but why would you open your data with a reader only to populate a table with it Seriously ---->

I realize that by using a reader you don't have to mess with DataAdapters, but it's just so much easier to get your data with an Adapter and a Table....

Please help me understand what you are trying to do here...

Signed

Seriously

[nemaroller]

In ASP.NET, any changes made to a DataGrid, DataList, or have you, have to be stored somewhere until update. However, between posts to the page, you lose all data, so you must either send the data along in viewstate, requery the database, or save it in session state. Unless you want to query the database each time, a datatable is the easiest format to persist that data between postbacks.

As a dataAdapter is just a set of commands, it doesn't help you persist the data anywhere. It really helps for batch updating though, where manually writing the code would be arrogant.

In Windows Forms applications, whether the form wizard makes it for you, or you write them by hand makes no difference, other than you cut the slough. If he only needs to throw the data in a table, I would still pass the dataadapter object, and use a datareader. It is still by far, the fastest way to get data.

[CyberHawke]

Originally posted by nemaroller
Unless you want to query the database each time, a datatable is the easiest format to persist that data between postbacks.

Anyone with more than two days of experience with ADO.NET should know this.

As a dataAdapter is just a set of commands, it doesn't help you persist the data anywhere. It really helps for batch updating though, where manually writing the code would be arrogant.

Again, anyone with more than two days experience should know this too.

In Windows Forms applications, whether the form wizard makes it for you, or you write them by hand makes no difference, other than you cut the slough. If he only needs to throw the data in a table, I would still pass the dataadapter object, and use a datareader. It is still by far, the fastest way to get data.

ok, I realize that using a datareader is akin to what we (back in the day) referred to as a firehose connection, where we would grab the data, read it out of the recordset and into the variable, object etc. in which we planned to use it if we were populating something like an array or listbox, combobox etc.. but the effort necessary to populate a table from data in a reader is just as heavy as using and adapter and table combination, so I still don't see what it accomplishes.

[nemaroller]

"Anyone with more than two days experience should know this"
Then why'd you ask me about datareaders?

"but the effort necessary to populate a table from data in a reader is just as heavy as using and adapter and table combination, so I still don't see what it accomplishes."

CyberHawk, you seem to be a hunting for an argument you cannot win. You just stated they are equal, i would disagree, giving favor to a datareader.It's a matter of preference, how do you win on preference?

I have only occasional need for the Adapter class. For web applications i build, I use readers, because 9 times out of 10, I am only retrieving data. There are only 4 or 5 places where I use the Adapter class. Why include all the slough that comes with an Adapter?

Now, those are my personal reasons for your question. As to this thread, the author asked:

"so how to read the content of datareader and populate into datagrid"

That was my answer. You can be sarcastic all you want.. ... my code runs cleaner.

[CyberHawke]

Then why'd you ask me about datareaders?
I didn't I only asked why you would code this way, I've been using .NET since 1.0 was a beta so I fully familiar with all the objects and libraries contained in System.Data

I don't use ASP, simply because none of my clients are looking for web solutions. As for preference, you are correct, both sides win when it comes to personal preference.

Additionally, we come down to my original question which is why would you use a reader to populate a table when it would be just as easy to use an adapter. Your answer was based on ASP, and no where in this thread to I see any indication that anyone is writing ASP code or plans to use the responses for a web solution.

So I guess it depends on which you prefer, and what your needs are. Most of my projects work with interactive datasets, but I usually only use adapters to fill my tables or sets, I typically use command objects for inserts, updates and deletes and execute them against the connection because for real-time transaction processing, the dataadapter method is too slow for automated processes.

Finally, you are absolutely correct, he did ask how you would use a datareader to populate a datagrid, I was simply offering an alternative solution to getting his data because you cannot directly use the reader as a data source for something like a grid. I am not actually looking for an argument and if I were, this is a no win situation because we are both right from our perspectives. It is refreshing to speak with someone who actually knows what they are doing

[nemaroller]

I've been stuck in a ASP.NET frame of mind since December