Retrieving values from Data Reader by column name

[Dylan Parry]

Hi,

At the moment I use code like the following:

string myString = this.dataReader .IsDBNull(2) ? null :
this.dataReader .GetString(2);

With a record from the DB that looks like:

..---------------------------.
| id | name | email | phone |
'---------------------------'

In this example I am getting the value of the "email" column, but this
can get quite complicated, especially if I want to retrieve more columns
from the database, so that for example the record now looks like:

..-----------------------------------.
| id | name | title | email | phone |
'-----------------------------------'

Now the "email" column is at index 3 rather than 2, so my code is now
broken.

So what I want to be able to do is using code like:

string myString = this.dataReader .IsDBNull("emai l") ? null :
this.dataReader .GetString("ema il");

Obviously this is just pseudo-code and it doesn't work that way, but
something similar would be great.

Any ideas?

--
Dylan Parry - http://webpageworkshop.co.uk

A Flower?

[Marc Gravell]

Re: Retrieving values from Data Reader by column name

Something like below (in a static class, e.g. SqlHelper or something)?

public static string GetString(IData Reader reader, int columnIndex)
{ return GetString(reade r, columnIndex, null); }
public static string GetString(IData Reader reader, string
columnName, string nullValue)
{ return GetString(reade r, reader.GetOrdin al(columnName),
nullValue); }
public static string GetString(IData Reader reader, string
columnName)
{ return GetString(reade r, reader.GetOrdin al(columnName), null); }
public static string GetString(IData Reader reader, int columnIndex,
string nullValue)
{
//note: structure is different to e.g. GetInt32 (value-type), to
accomodate for auto-return of nulls (ref-type)
string result;
if (reader.IsDBNul l(columnIndex)) result = nullValue;
else result = reader.GetStrin g(columnIndex);
return result;
}

Marc

[Barry Kelly]

Re: Retrieving values from Data Reader by column name

Dylan Parry <usenet@dylanpa rry.comwrote:

(Retrieving values from Data Reader by column name)
>
So what I want to be able to do is using code like:
>
string myString = this.dataReader .IsDBNull("emai l") ? null :
this.dataReader .GetString("ema il");
>
Obviously this is just pseudo-code and it doesn't work that way, but
something similar would be great.

There are two simple solutions:

1) Load the data into a DataTable and work with it there
(DataTable.Load () in .NET 2).

2) Cache the name-index map after you've looked them up using FieldCount
and GetName(int).

-- Barry

--

http://barrkel.blogspot.com/

[Michael C]

Re: Retrieving values from Data Reader by column name

"Dylan Parry" <usenet@dylanpa rry.comwrote in message
news:1es9ntcxm3 rki.dlg@dylanpa rry.com...

string myString = this.dataReader .IsDBNull("emai l") ? null :
this.dataReader .GetString("ema il");
>
Obviously this is just pseudo-code and it doesn't work that way, but
something similar would be great.

Why not just use this?

dataReader["email"].ToString();

and work with empty string instead of null. This seems to be a bit of a
standard, eg MyTextBox.Text never returns null.

Michael

[Jeff Shepler]

Re: Retrieving values from Data Reader by column name

Two things:
1. You can index the SqlDataReader object by column name. It returns
an object.

2. DBNull is a type. You can use the "is" operator.


string myString = this.dataReader["email"] is DBNull ? null :
this.dataReader["email"].ToString();



On Tue, 4 Jul 2006 12:29:51 +0100, Dylan Parry <usenet@dylanpa rry.com>
wrote:

>Hi,
>
>At the moment I use code like the following:
>
string myString = this.dataReader .IsDBNull(2) ? null :
this.dataReader .GetString(2);
>
>With a record from the DB that looks like:
>
>.---------------------------.
>| id | name | email | phone |
>'---------------------------'
>
>In this example I am getting the value of the "email" column, but this
>can get quite complicated, especially if I want to retrieve more columns
>from the database, so that for example the record now looks like:
>
>.-----------------------------------.
>| id | name | title | email | phone |
>'-----------------------------------'
>
>Now the "email" column is at index 3 rather than 2, so my code is now
>broken.
>
>So what I want to be able to do is using code like:
>
string myString = this.dataReader .IsDBNull("emai l") ? null :
this.dataReader .GetString("ema il");
>
>Obviously this is just pseudo-code and it doesn't work that way, but
>something similar would be great.
>
>Any ideas?

[Dylan Parry]

Re: Retrieving values from Data Reader by column name

Dylan Parry wrote:

Any ideas?

Thanks to all that replied. I've been given plenty of solutions to work
with, so I'll try them all out and see which suits me best. Cheers,

--
Dylan Parry - http://webpageworkshop.co.uk

A Flower?