OleDb.OleDbDataReader

[Miro]

Im using an OleDb.OleDbDataReader to get some data from an ms access
database.

I am trying to do some things by code to learn to do things without the
wizards and im wondering if there is an easier way to do this.

I created a dataset by code ' super
then i create a table with fields within the dataset 'no prob

then i create a connection and a oledb.oledbcommand and then run this:

Dim rdr As OleDb.OleDbDataReader = cmd.ExecuteReader()
'Debug.WriteLine(curTourneyId)

Dim foundTableColumn As Boolean = False
Do While rdr.Read()

Console.WriteLine("WinID: {0}, PlayerID: {1}, Standing: {2},
GameDate: {3:d}, TourneyID: {4}", _
rdr.GetInt32(0), rdr.GetInt32(1),
rdr.GetInt32(2), rdr.GetDateTime(3), rdr.GetInt32(4))

here is where my question is.

Basically the only way I have found to get my 'data' is by using the
rdr.getIntBLA( position in sql statement ) and assign it to a table column
value.

Example:
Dim GameDate As String = rdr.GetDateTime(3).ToString("d")

is there a different command I shoudl be using instead of the
OLEDBDataReader to do this. It seems to me its kinda 'flimsy' as the
(position in sql statement ) has to be setup and is easily prone to errors.
Im looking for a way to reference the fields directly ....
so basically I call my sql statement...get a list of data - and throw that
data into my datatable in my dataset.

I have it working ( with the above code ) ...but it just doesnt feel like
its the proper way to go about it.

Any suggestions welcome.

Thanks,

Miro

 

[Armin Zingler]

Example:
Dim GameDate As String = rdr.GetDateTime(3).ToString("d")

is there a different command I shoudl be using instead of the
OLEDBDataReader to do this. It seems to me its kinda 'flimsy' as the
(position in sql statement ) has to be setup and is easily prone to
errors. Im looking for a way to reference the fields directly ....

"directly"=?

First step can be:

Dim GameDate = directcast(rdr("GameDate"), date).ToString("d")

Well, it doesn't look a lot better due to additional DirectCast, though you
can specify the field name.

so basically I call my sql statement...get a list of data - and throw
that data into my datatable in my dataset.

I have it working ( with the above code ) ...but it just doesnt feel
like its the proper way to go about it.

Any suggestions welcome.

It's the right way (if you want to learn the basics without using a wizard).
The next step is using a DataAdapter that reads the data for you into a
datatable (and can write changes back to the data source). You can still use
it without a wizard. A typed dataset is one more step ahead, and I recommend
it because it saves you from doing type casting (on your own) all the time.
Then comes the TableAdapter that - I've heard of it - is even more advanced.


Armin

 

[Cor Ligthert[MVP]]

Miro,

The basic adonet method to fill a dataset is to use the Fill metod from a
DBDataAdapter (by instance the OleDBDataAdapter in the case of MS Access)\

dim da as new OleDB.OleDBDataAdapter("Select * from myTable",
connectionObject)
dim ds as new dataset
da.fill(ds)

Should be enough,

The dataadapter is using the datareader so don't have the idea that as you
does the coding for the datareader yourself that you will win any
picasecond.

Cor

http://msdn.microsoft.com/en-us/library/by7t044k.aspx

 

[Miro]

Thank you Armin,

Yes i was looking for 'directly' to reference the fields 'directly' instaed
of using the position of the field in the sql statement.

I did create a typed dataset (by code) - so i might be on my way already to
the next step without knowing it.

Your directcast( rdr("gameDate") was what I was looking for.

I will have a look at the dataadapter.

Thanks for your time.

Miro

 

[Miro]

Datareader was how I got it working - i will try the data- adapter next.

I just didnt know where / what to change to go to the next step.

dataAdapter here i come.

Thanks for your input.

Miro

 

[Michel Posseth [MCP]]

Verry good of you that you take the effort to find out what is going on
behind the scenes , in my opinion this makes you a "true coder" instead of a
"code monkey"

Don`t get fooled by all those wizzards, behind the scenes it is the same
code as you write as Cor pointed out , i had once a IT manager on a project
who said "i do not want to see aany SQL code so use table adapters" , i just
selected the show all files option in the solution explorer , double clicked
on the code behind files and showed him the SQL statements that the TA
generates remember that these people had no idea what was going on behind
the scenes and though that the TA did this in a miracoluous way .


However keep in mind that in 90 % of the situations the code that the
wizzards generate behind the scenes will suit your needs and give you the
advantage of RAD
however there are situations where a datareader ( wich is the .Net
equivalant of a firehose cursor ) can give your app a hughe performance
boost for the obvious reassons ( if you understand the concepts of a dataset
aproach VS a datareader aproach )


regards

Michel Posseth

 

[Cor Ligthert[MVP]]

Miro,

To give a sample where the datareader is more simple and better to use than
a dataset..

Assume you do a job for an electricity company and you have to proces all
the payments which are done from your customers daily in one file which you
get from your bank.

Then it is probably in most cases more simple to proces every payment one by
one using a datareader then first load all in memory in a dataset before you
process then..

Cor

 

[Miro]

Makes sense.

I have gone back in my book to re-read datareader.

Thanks for your help.

Miro

 

[Miro]

and by datareader i mean the data adapter. ;-)

M.