ADO.NET in VB.NET

[Sean]

Ok, ADO.NET is confusing the heck out of me. If I want to
just do a Transact-SQL SELECT Statement to a SQL Server
2000 Database in Visual Basic, should I even use ADO
Recordsets anymore. I was looking at the SQL Server
Specific stuff: (SQLClient.SQLConnection,
SQLClient.SQLCommand and SQLClient.SQLReader) but the SQL
Reader doesn't seem to have a nice way of navigation like
the ADORecordsets had. Or do I need to actually create a
SQLDataAdapter and fill a Dataset with the data. Basically
all I want to do is read one ID field:

"SELECT ID FROM TABLE WHERE Name = SomeName" and it seems
like I am creating alot of overhead to do it.

Thanks

Sean

 

[Marina]

You could do either. You didn't say what was wrong with the way
sqldatareader retrieved data. But it does quite nicely for forward only
read only access to data and is very easy to use.

If you need more functionality then this, then use the dataset.

If you want even more functionality, including functionality that feels like
the old ADO recordsets (type specific data retrieval, EOF, etc), then you
can build wrappers around the existing framework objects, that allow for
this.

Now, if you all you really need is to get one value from a row, then use the
ExecuteScalar method of the SqlCommand object. This will return the first
column of the first row returned by your query, and is a convenient method
when you are really just looking for one piece of information.

 

[One Handed Man [ OHM# ]]

Sean,
You would be better off putting this q in the adonet database. You
will get a fast an concise reponse there.

Regards - OHM

 

[Cor]

Hi Sean,

In addition to Marina, I think there is nothing that you cannot do with a
dataset, that you could do with a recordset.

And you can do more and even in a much more comfortable and consistent way.

The only big difference is that a dataset is disconnected data and therefore
you have to do your concurrency checkings (if that is needed) in another
way.

Cor

 

[William Ryan]

if you just want one value, use the ExecuteScalar method of command object.
DataReaders navigate forward very well, every bit as elegant as old school
recordsets (it's only in reverse where things get ugly). YOu can walk a
DataTable/DataSet every bit as easily, perhaps even easier

foreach(DataRow dr in myDataSet.Tables["Whatever"]){
//Do Something.

}

Or, just reference a row by index
myDataSet.Tables["Whatever"].Rows[SomeIndex].

For just retrieving one value, use ExecuteScalar. It does use a DataReader
behind the scenes, but it will only return one value so you don't have to
walk through the reader while(rdr.Read()){ //do something;}
cmd.CommandText = "SELECT ID FROM TABLE WHERE Name = SomeName";
int i = cmd.ExecuteScalar();

You'd probably want to use Parameters too...

"SELECT ID FROM TABLE WHERE Name = @SomeName"

Then,

cmd.Parameters.Add["@SomeSame", SqlDbType.Int].Value = SomeNamesValue;

HTH,

Bill

 

[Herfried K. Wagner [MVP]]

You would be better off putting this q in the adonet database. You

Let's put the question into the database.

;-)