473,324 Members | 2,166 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Retrieving values from Data Reader by column name

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("email") ? null :
this.dataReader.GetString("email");

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?
Jul 4 '06 #1
5 21558
Something like below (in a static class, e.g. SqlHelper or something)?

public static string GetString(IDataReader reader, int columnIndex)
{ return GetString(reader, columnIndex, null); }
public static string GetString(IDataReader reader, string
columnName, string nullValue)
{ return GetString(reader, reader.GetOrdinal(columnName),
nullValue); }
public static string GetString(IDataReader reader, string
columnName)
{ return GetString(reader, reader.GetOrdinal(columnName), null); }
public static string GetString(IDataReader 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.IsDBNull(columnIndex)) result = nullValue;
else result = reader.GetString(columnIndex);
return result;
}

Marc
Jul 4 '06 #2
Dylan Parry <us****@dylanparry.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("email") ? null :
this.dataReader.GetString("email");

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/
Jul 4 '06 #3
"Dylan Parry" <us****@dylanparry.comwrote in message
news:1e***************@dylanparry.com...
string myString = this.dataReader.IsDBNull("email") ? null :
this.dataReader.GetString("email");

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
Jul 4 '06 #4
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 <us****@dylanparry.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("email") ? null :
this.dataReader.GetString("email");

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

Any ideas?
Jul 4 '06 #5
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?
Jul 4 '06 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: fotokard | last post by:
I am new to sql query and have a question. I would like to display row column values as column names. questions table consisted of question_id, group_id, question response table consisted...
1
by: Michael March | last post by:
I have seen a few recipes out there that allow for access to DB-API columns by their name instead of column number.. I was wondering if anyone knew of any code snippets that allow for the same...
5
by: Matt | last post by:
While i am looping the records how i do for each field name while(objRead.Read()) foreach( { match(x) Console.WriteLine(objRead.); }
1
by: atif | last post by:
hi i hav a prob with data reader. i hav a 2 column in a data table n i hav 2 drop down lists on my page. Now i want to connect my first drop down with the first column of the data table. Though i...
4
by: Ram | last post by:
I am using ADO.Net data reader to retrieve data from main frame. I am getting timestamp which is 26 (yyyy-mm-dd hh- mm-ss.123456) bytes as sqltimestamp data type. I am using data reader to...
3
by: SimonHeffer | last post by:
I've added a DataGridView and added some columns in the designer, now I want to add some rows with data applied via column name. Having done some web trawling I've ended up with this: private...
1
by: ananth | last post by:
Hi All, I am getting an SQL Error Inserted Value to LARGE FOR COLUMN .I know the cause for this error Is there any specific error handling method where in i can find for which column...
2
by: Big Daddy | last post by:
For example, if I have a DB table called DownloadPoints with a column named DownloadPointNo, then SqlMetal will create a file with a class called DownloadPoints with an accessor named...
1
by: sachinkale123 | last post by:
Hi, I am reading excel file and reading values from that I am using provider As : "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = " + Filename + ";Extended Properties=\"Excel 8.0;Hdr=No;IMEX=1\"";...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.