Basically:
A datareader is for getting one or more records with minimal RAM and server
resources consumed.
http://www.learnasp.com/freebook/learn/datareader.aspx
If 50,000 people hit your site and they all are activating datareaders your
site will scale well.Even though the datareader is efficient (if you read
10,000 records x 4 columns from SQLserver it ain't 10,000 database hits) in
code you can only access 1 row at a time and only go forward. The
"connected" buffers are hidden from you. Nothing can make it go backwards
or hop around your results. WARNING If your query returns one value (not 1
row with n columns, exactly 1 value use ExecuteScalar
http://www.learnasp.com/freebook/learn/executescalar.aspx which is lightest
way to grab 1 value with one of the "gunk" that one needs to have a rich
communication with a database that adds to overhead.
A dataTable is for loading several records into memory (it is mostly like a
recordset)
http://www.learnasp.com/freebook/learn/datatable.aspx
They act like rows, columns and databases while in memory (they can be
queried, sorted, added to. deleted from) but they are all RAM. When it was
filled up the database is disconnected immediately. Until you ask it to
commit the changes and then it connects makes all the changes at once to
the database. If you need to move around rows, add calculated fields and
play with data this is the one for you.
A dataset is a whole new beast. It can contain 1 to N datatables. Most
people use it when they should use a
dataTable. http://www.learnasp.com/learn/datasets.aspx gives you a high
level overviewb but I suspect runnig the example at:
http://www.learnasp.com/freebook/learn/yahoostore.aspx
which loads XML into a dataset you can see may clarify what it is.
In past terms a Dataset simplifies any code where we used to make multiple
recordsets. One dataset can have 1 to n datatables within yet from a code
is simple because it only has one dataset object instead of many recordset
objects to juggle in code.
Common Sin:
Using Datasets to fill because the Docs do, i.e.
Dim DS As DataSet
Dim MyConnection As OLEDBConnection
Dim MyCommand As OLEDBDataAdapter
MyConnection = New OLEDBConnection("....")
MyCommand = New OLEDBDataAdapter("select * from publishers where
state='NY'", MyConnection)
DS = new DataSet()
MyCommand.Fill(ds, "NYStateOfMind")
MyDataGrid.DataSource=ds.Tables("Authors").DefaultView
MyDataGrid.DataBind()
MYConnection.Close()
when a Executereader does the job much much much faster iwthout all the
overhead of dataset object and with less code:
Dim MyConnection As new OLEDBConnection("....")
Dim MyCommand As OLEDBCommand("select * from publishers where state='NY'")
DIM myreader as new
OLEBDBReader()=MyCommand.ExecuteReader(system.data.commandbehavior.closeconnecti\
on)
MyDataGrid.DataSource=myreader
MyDataGrid.DataBind()
myReader.Close()