Best way to retrieve more than one field in a single record

[zoro]

Hi, I am looking for the recommended way to retrieve several values
from a single record, i.e. a typical lookup scenario. An example would
be a query that needs to retrieve user_name, user_addres, user_email
for a given user.
I know that if I had to get a single values I should use the
Command.ExecuteScalar() and that if I need to get several records I
should use the SqlDataReader and loop using its Read() method.
But how should I retrieve multiple values from a single record?
Thank you for your help.
ilZoro.

 

[Marc Gravell]

One route is ExecuteNonQuery using variables / parameters for the items

Marc

 

[Miha Markic [MVP C#]]

Either do Read() once or follow Marc's suggestion.

 

[zoro]

Thanks Marc, Miha. So far i did both but felt there must be a 'proper'
way to do it, built in the ADO.NET architecture - just like
ExecuteScalar(). Perhaps ExecuteRecord() ?

 

[Guest]

Thanks Marc, Miha. So far i did both but felt there must be a 'proper'
way to do it, built in the ADO.NET architecture - just like
ExecuteScalar(). Perhaps ExecuteRecord() ?

I think there may be some confusion. SqlDataReader.Read() returns a record
for each row returned by your query. Just call SqlDataReader.GetString(x) (or
which ever method maps to the appropriate data type), replacing x with either
the field ordinal returned by SqlDataReader.GetOrdinal("fieldName"), or the
name of the field you want.

 

[zoro]

Thanks Dan. You are right of course to say that what I'm trying to
achieve can be achieved in several other ways. I suppose what I am
trying to say is that if ADO.NET provides a special way to retrieve a
single value with ExecuteScalar() (even though you could achieve this
task using SqlDataReader), in order to handle a popular type of query,
it stands to reason it would also provide a special way to do this
common task too.

 

[Michael Voss]

zoro wrote:

[...snip...]

if ADO.NET provides a special way to retrieve a
single value with ExecuteScalar() (even though you could achieve this
task using SqlDataReader), in order to handle a popular type of query,
it stands to reason it would also provide a special way to do this
common task too.

If you select a scalar, you'd not expect anything else except a single
value. If you do a "select * from table where condition = true", it's very
likely the result will have more than one row (even though you might be sute
it will contain one row only). Since you cannot know in advance the number
of rows your query will return, there is no sense in having some method
returning one row only...