Framework 2.0 SqlDataReader equivalent to javas ResultSet rs.getString( "ColumnNameHere" ) ?

[Frank Milverckowitz]

Hi,

Newbie question about SqlDataReader column value access...

In java jdbc code to get a value from a table column we can pass the column
name (instead of an int index or offset):

String strLastName = rs.getString( "LastName" );


Now, let's say that LastName is the 14th column in the table...
Does this mean that in .NET SqlDataReader my only option is to define my own
equates to map column indexes to string values?

For example, Using the SqlDataReader it looks like my only option is to pass
an index for the column I want the value from:

string strLastName = sdr.GetString( 13 );

or do I need to define my own equates for every table in my database so that
my code is readable?
(e.g.)

class MyDatabaseTableClass
....
public int LAST_NAME = 13;
public int ADDRESS_LINE1 = 14;
public int ADDRESS_LINE2 = 15;

Or am I just not finding the method I am looking for here?

thanks for any tips,

Frank

 

[Mark Rae]

Or am I just not finding the method I am looking for here?

Yes - I think you're confusing a SqlDataReader with a DataSet. A
SqlDataReader is a forward-only recordset (for want of a better term) and,
more or less, the only thing you can do with it is read each record once
from beginning to end. Therefore, the properties and methods that you think
are missing simply aren't necessary...

string strLastName = String.Empty;
string strAddress_Line1 = String.Empty;
string strAddress_Line2 = String.Empty;

using (SqlDataReader objReader = <some DataReader object>)
{
while (objReader.Read())
{
strLastName = objReader["LAST_NAME"].ToString();
strAddress_Line1 = objReader["ADDRESS_LINE1"].ToString();
strAddress_Line2 = objReader["ADDRESS_LINE2"].ToString();
}
}

 

[bruce barker]

the datareader indexer allows the column name or index:

string v = reader["LastName"].ToString();

to use the helpers you can convert the name to index

int i = reader.GetOrdinal("LastName");
string v = reader.GetString(i);

-- bruce (sqlwork.com)

 

[sloan]

A couple of things.

Remembering that a SqlDataReader is ~an implementation of IDataReader, you
should look at the IDataReader interface.
You can decide with methods/properties/indexers are SqlServerReader specific
and are generic to IDataReader.

(thus you could program against the interface, and have future flexiblity).

......

Having said that, do a google search for "csla SafeDataReader".
this is a class a guy wrote to handle DBNULL, and has encapsulated the
method bruce describes:
int i = reader.GetOrdinal("LastName");
string v = reader.GetString(i);

.......

Having said that, I actually code now to the CONST method you describe.

except I do enums

public enum EmployeeSingleDefaultLayout
{
EmpID = 0 ,
LastName = 1 ,
FirstName = 2 ,
DateOfBirth = 3
}


I do this because I always code against an IDataReader. And when I
serialize my datareader to an object, (or object collection)
I use

IDataReader idr = something.ExecuteReader();
if (null!=idr)
{
while(idr.Read())
{

int empid = idr.GetInt32( EmployeeSingleDefaultLayout.EmpID ); //
(don't forget to check for DBNULL also, this is just a quick example)
string lname = idr.GetString( EmployeeSingleDefaultLayout.LastName);
//the rest I'll save for you

Employee e = new Employee (empid , lname);

}
}

something like that.

the enum is more maintainable and more organized
but I like the generic-ness of IDataReader, because I can get one against
any RDBMS, protecting me.

and you end up writing the same type code for

Access
Sql Server
Oracle
Excel

even a text file.


Anyway, that's my approach.