Data reader and Null values

  • Thread starter Thread starter Vibhu Srinivasan
  • Start date Start date
V

Vibhu Srinivasan

I am using data reader for reading tables.
I have some issues with ADO readers, having come from a
java perspective

When reading a table say Account table ( which has two
fields Id(int), Name(varchar)) both can be null

In the C# code i end up doing

int id = (int)reader["Id"];
string name = (string)reader["Name"];

1) If the id or name Name is NULL this blows up with a ull
pointer . So i have to

int columnNum =
reader.GetOrdinalcolumnName);

if(reader.IsDBNull(columnNum)){
then read the column.
}

This int is a value type , how can i represent a NULL
object in the system. Is there a equivalent int object(
something like the Integer class in java to represent a
NULL value.

2) Is there a way to set up reader to retutn a DBNull
object if there are null values in the database.
What i ideally want to do is
reader.GetInteger("ColumnName"){

}
that returns a Integer and similarly reader.SetInteger
(Integer){

}
like i can easily do in the Java world.
Not quite sure why this was left out in .NET

Appreciate any feedback
Vibhu Srinivasan
 
null in a database means unknown value, while a null in c# or java means
unassigned value. there are subtle differences, this is why dbnull is not
mapped to null.

..net value types (unlike java) have no base class. to use them as an object
(called boxing) you just assign them to the base class object, which can
hold a reference to any value type.

int i = 0;
object o = i;

you cast to unbox

i = (int) o;

you can use object to get around the if on the datareader, but you will need
the if on every reference to the object, as a Db.Null or null will not cast
to an int. if you just want to cast Db.Null to 0 or "", then supplay a
static function.

static int ToInt(IDataReader dr, string colName)
{
int columnNum = dr.GetOrdinal(colName);
if (dr.IsDBNull(columnNum))
return 0;
else
return dr.GetInt32(columnNum);
}
 
I do agree that database null and DbNull are different.
But in a business world a NULL for an int field or an O in
the int field may mean different things.
I am aware of the boxing feature. It is not useful though
to just turn an int to an object. Then I have to know what
type it is to cast it back
So i am think of creating owr own types Integer, Float,
Double and then provide static methods
public static Integer GetInteger(IDataReader reader,
string columnName) {
int value;
int columnNum = reader.GetOrdinal
(columnName);
if(reader.IsDBNull(columnNum)){
return null;
}else{
value =(int)reader[columnName];
return new Integer(value);
}

}

public static string GetString(IDataReader reader,
string columnName){
int columnNum = reader.GetOrdinal
(columnName);
if(reader.IsDBNull(columnNum)){
return null;
}else{
return (string)reader[columnName];
}
}
-----Original Message-----
null in a database means unknown value, while a null in c# or java means
unassigned value. there are subtle differences, this is why dbnull is not
mapped to null.

..net value types (unlike java) have no base class. to use them as an object
(called boxing) you just assign them to the base class object, which can
hold a reference to any value type.

int i = 0;
object o = i;

you cast to unbox

i = (int) o;

you can use object to get around the if on the datareader, but you will need
the if on every reference to the object, as a Db.Null or null will not cast
to an int. if you just want to cast Db.Null to 0 or "", then supplay a
static function.

static int ToInt(IDataReader dr, string colName)
{
int columnNum = dr.GetOrdinal(colName);
if (dr.IsDBNull(columnNum))
return 0;
else
return dr.GetInt32(columnNum);
}




I am using data reader for reading tables.
I have some issues with ADO readers, having come from a
java perspective

When reading a table say Account table ( which has two
fields Id(int), Name(varchar)) both can be null

In the C# code i end up doing

int id = (int)reader["Id"];
string name = (string)reader["Name"];

1) If the id or name Name is NULL this blows up with a ull
pointer . So i have to

int columnNum =
reader.GetOrdinalcolumnName);

if(reader.IsDBNull(columnNum)){
then read the column.
}

This int is a value type , how can i represent a NULL
object in the system. Is there a equivalent int object(
something like the Integer class in java to represent a
NULL value.

2) Is there a way to set up reader to retutn a DBNull
object if there are null values in the database.
What i ideally want to do is
reader.GetInteger("ColumnName"){

}
that returns a Integer and similarly reader.SetInteger
(Integer){

}
like i can easily do in the Java world.
Not quite sure why this was left out in .NET

Appreciate any feedback
Vibhu Srinivasan
Click to expand...


.
Click to expand...
 
Hi Vibhu,

I think it would be a good idea if you tell us
what the business rule is. You said "But in a business
world a NULL for an int field or an O in the int field may
mean different things." Give us an example and you will
get more help.

Maybe what you should do is:
If Id is null return a value like -32768.
If Name is nulll return a values(string) as (UNKNOW).

Using this technique you now that the Id or Name fields
are null.

Thanks!!


-----Original Message-----
I do agree that database null and DbNull are different.
But in a business world a NULL for an int field or an O in
the int field may mean different things.
I am aware of the boxing feature. It is not useful though
to just turn an int to an object. Then I have to know what
type it is to cast it back
So i am think of creating owr own types Integer, Float,
Double and then provide static methods
public static Integer GetInteger(IDataReader reader,
string columnName) {
int value;
int columnNum = reader.GetOrdinal
(columnName);
if(reader.IsDBNull(columnNum)){
return null;
}else{
value =(int)reader[columnName];
return new Integer(value);
}

}

public static string GetString(IDataReader reader,
string columnName){
int columnNum = reader.GetOrdinal
(columnName);
if(reader.IsDBNull(columnNum)){
return null;
}else{
return (string)reader[columnName];
}
}
-----Original Message-----
null in a database means unknown value, while a null in c# or java means
unassigned value. there are subtle differences, this is why dbnull is not
mapped to null.

..net value types (unlike java) have no base class. to use them as an object
(called boxing) you just assign them to the base class object, which can
hold a reference to any value type.

int i = 0;
object o = i;

you cast to unbox

i = (int) o;

you can use object to get around the if on the datareader, but you will need
the if on every reference to the object, as a Db.Null or null will not cast
to an int. if you just want to cast Db.Null to 0 or "", then supplay a
static function.

static int ToInt(IDataReader dr, string colName)
{
int columnNum = dr.GetOrdinal(colName);
if (dr.IsDBNull(columnNum))
return 0;
else
return dr.GetInt32(columnNum);
}




I am using data reader for reading tables.
I have some issues with ADO readers, having come from a
java perspective

When reading a table say Account table ( which has two
fields Id(int), Name(varchar)) both can be null

In the C# code i end up doing

int id = (int)reader["Id"];
string name = (string)reader["Name"];

1) If the id or name Name is NULL this blows up with a ull
pointer . So i have to

int columnNum =
reader.GetOrdinalcolumnName);

if(reader.IsDBNull(columnNum)){
then read the column.
}

This int is a value type , how can i represent a NULL
object in the system. Is there a equivalent int object(
something like the Integer class in java to represent a
NULL value.

2) Is there a way to set up reader to retutn a DBNull
object if there are null values in the database.
What i ideally want to do is
reader.GetInteger("ColumnName"){

}
that returns a Integer and similarly reader.SetInteger
(Integer){

}
like i can easily do in the Java world.
Not quite sure why this was left out in .NET

Appreciate any feedback
Vibhu Srinivasan
Click to expand...


.
Click to expand...
.
Click to expand...
 
Vibhu,

These types have already been created. They are the System.Data.SQLTypes.
THe SQLDataReader provides exactly teh methods you are talking about
writing.

Kathleen

Vibhu said:
I do agree that database null and DbNull are different.
But in a business world a NULL for an int field or an O in
the int field may mean different things.
I am aware of the boxing feature. It is not useful though
to just turn an int to an object. Then I have to know what
type it is to cast it back
So i am think of creating owr own types Integer, Float,
Double and then provide static methods
public static Integer GetInteger(IDataReader reader,
string columnName) {
int value;
int columnNum = reader.GetOrdinal
(columnName);
if(reader.IsDBNull(columnNum)){
return null;
}else{
value =(int)reader[columnName];
return new Integer(value);
}

}

public static string GetString(IDataReader reader,
string columnName){
int columnNum = reader.GetOrdinal
(columnName);
if(reader.IsDBNull(columnNum)){
return null;
}else{
return (string)reader[columnName];
}
}
-----Original Message-----
null in a database means unknown value, while a null in c# or java means
unassigned value. there are subtle differences, this is why dbnull is not
mapped to null.

..net value types (unlike java) have no base class. to use them as an object
(called boxing) you just assign them to the base class object, which can
hold a reference to any value type.

int i = 0;
object o = i;

you cast to unbox

i = (int) o;

you can use object to get around the if on the datareader, but you will need
the if on every reference to the object, as a Db.Null or null will not cast
to an int. if you just want to cast Db.Null to 0 or "", then supplay a
static function.

static int ToInt(IDataReader dr, string colName)
{
int columnNum = dr.GetOrdinal(colName);
if (dr.IsDBNull(columnNum))
return 0;
else
return dr.GetInt32(columnNum);
}




I am using data reader for reading tables.
I have some issues with ADO readers, having come from a
java perspective

When reading a table say Account table ( which has two
fields Id(int), Name(varchar)) both can be null

In the C# code i end up doing

int id = (int)reader["Id"];
string name = (string)reader["Name"];

1) If the id or name Name is NULL this blows up with a ull
pointer . So i have to

int columnNum =
reader.GetOrdinalcolumnName);

if(reader.IsDBNull(columnNum)){
then read the column.
}

This int is a value type , how can i represent a NULL
object in the system. Is there a equivalent int object(
something like the Integer class in java to represent a
NULL value.

2) Is there a way to set up reader to retutn a DBNull
object if there are null values in the database.
What i ideally want to do is
reader.GetInteger("ColumnName"){

}
that returns a Integer and similarly reader.SetInteger
(Integer){

}
like i can easily do in the Java world.
Not quite sure why this was left out in .NET

Appreciate any feedback
Vibhu Srinivasan
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top