How to process datareader nulls coming from datareader?

  • Thread starter Thread starter JB
  • Start date Start date
J

JB

Hello

I am using a datareader to reader a table. The table may have some null
values in it. The thing is that I need to process the row whether the field
has null values in it or not. The "Swim" field in this table will have null
values in it. The problem is that when I try to read the field with a null
value I get the error message stating:

"unable to cast object of type 'System.DBNull' to type 'System.String'.

How can I process a datareader record if a field in the record has null
values?

Below is a sample of the problem and the error:


using System;
using System.Collections.Generic;
using System.Text;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;

namespace WillUsedProj
{
class WillUsedList
{
private int PoolLoc;
private string Swims;

public WillUsedList(int PoolAddr, string PollHeat)
{
PoolAddr = PoolLoc;
PollHeat = Swims;
}

public int PoolAddr

{
get { return PoolLoc; }
set { PoolLoc = value; }
}

public string PollHeat
{
get { return Swims; }
set { Swims = value; }
}

public static List<object> WillUsedTable()
{
List<object> dtList = new List<object>();
dtList = WillUsedData.GetTblData();

return dtList;
}
}
}
*********************************************************************

using System;
using System.Collections.Generic;
using System.Text;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;

namespace WillUsedProj
{
class WillUsedData
{
public static List<object> GetTblData()
{
SqlConnection connection = GetConn.GetConnection();
List<object> nuList = new List<object>();
string selectStatement = @"select PoolLoc, Swims from PoolTbl
where Vicinity=10 and
(Swims is null) " + "group by PoolLoc, Swims order by
PoolLoc, Swims ";
SqlCommand selectCommand = new SqlCommand(selectStatement,
connection);
SqlDataReader reader;
connection.Open();

reader =
selectCommand.ExecuteReader(CommandBehavior.SingleResult);
while (reader.Read())
{
WillUsedList WillUsed = new WillUsedList((int)reader["PoolLoc
"],(string)reader["Swims"]);
WillUsed. PoolAddr = (int)reader["PoolLoc"];
WillUsed. PollHeat = (string)reader["Swims"];
nuList.Add(WillUsed.PoolAddr);
nuList.Add(WillUsed. PollHeat);
}
reader.Close();
connection.Close();
return nuList;
}
}
}
 
IsDBNull on the DataReader.

Note, the "ordinal" method will be slightly faster than the "by a columnname
string".

I use const(s) or (readonly) variables to improve readablility


public static readonly int EmployeeIDColumnOrdinal = 0;
public static readonly int LastNameColumnOrdinal = 1;

IDataReader idr;


if (!(idr.IsDBNull(0)))
{
myObject.MyGuid = idr.GetGuid(0);
}


Or with my style:

if (!(idr.IsDBNull(EmployeeIDColumnOrdinal )))
{
myObject.MyGuid =
idr.GetGuid(EmployeeIDColumnOrdinal );
}

I don't actually use the suffix "ColumnOrdinal" . That is there for clarity.




JB said:
Hello

I am using a datareader to reader a table. The table may have some
null
values in it. The thing is that I need to process the row whether the
field
has null values in it or not. The "Swim" field in this table will have
null
values in it. The problem is that when I try to read the field with a null
value I get the error message stating:

"unable to cast object of type 'System.DBNull' to type 'System.String'.

How can I process a datareader record if a field in the record has null
values?

Below is a sample of the problem and the error:


using System;
using System.Collections.Generic;
using System.Text;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;

namespace WillUsedProj
{
class WillUsedList
{
private int PoolLoc;
private string Swims;

public WillUsedList(int PoolAddr, string PollHeat)
{
PoolAddr = PoolLoc;
PollHeat = Swims;
}

public int PoolAddr

{
get { return PoolLoc; }
set { PoolLoc = value; }
}

public string PollHeat
{
get { return Swims; }
set { Swims = value; }
}

public static List<object> WillUsedTable()
{
List<object> dtList = new List<object>();
dtList = WillUsedData.GetTblData();

return dtList;
}
}
}
*********************************************************************

using System;
using System.Collections.Generic;
using System.Text;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;

namespace WillUsedProj
{
class WillUsedData
{
public static List<object> GetTblData()
{
SqlConnection connection = GetConn.GetConnection();
List<object> nuList = new List<object>();
string selectStatement = @"select PoolLoc, Swims from PoolTbl
where Vicinity=10 and
(Swims is null) " + "group by PoolLoc, Swims order by
PoolLoc, Swims ";
SqlCommand selectCommand = new SqlCommand(selectStatement,
connection);
SqlDataReader reader;
connection.Open();

reader =
selectCommand.ExecuteReader(CommandBehavior.SingleResult);
while (reader.Read())
{
WillUsedList WillUsed = new WillUsedList((int)reader["PoolLoc
"],(string)reader["Swims"]);
WillUsed. PoolAddr = (int)reader["PoolLoc"];
WillUsed. PollHeat = (string)reader["Swims"];
nuList.Add(WillUsed.PoolAddr);
nuList.Add(WillUsed. PollHeat);
}
reader.Close();
connection.Close();
return nuList;
}
}
}
Click to expand...
 
JB said:
I am using a datareader to reader a table. The table may have some null
values in it. The thing is that I need to process the row whether the field
has null values in it or not. The "Swim" field in this table will have null
values in it. The problem is that when I try to read the field with a null
value I get the error message stating:

"unable to cast object of type 'System.DBNull' to type 'System.String'.
Click to expand...
reader =
selectCommand.ExecuteReader(CommandBehavior.SingleResult);
while (reader.Read())
{
WillUsedList WillUsed = new WillUsedList((int)reader["PoolLoc
"],(string)reader["Swims"]);
Click to expand...

if(!reader.IsDBNull(swimcol))
{
// use (string)reader[swimcol]
}
else
{
// use some default value maybe null
}
WillUsed. PoolAddr = (int)reader["PoolLoc"];
WillUsed. PollHeat = (string)reader["Swims"];
nuList.Add(WillUsed.PoolAddr);
nuList.Add(WillUsed. PollHeat);
}
reader.Close();
Click to expand...

Arne
 
Hello Sloan

Actually the error points to the module where the property is set and
not when the column
is read from the datareader. But if you say it is a datareader problem, if
I am reading a table with lets say for example 25 columns does that mean
write 25 "if" statements (one for each column) or is it that the datareader
is reading the null column and when it hits the propery set module there is
some way to deal with it there?
--
JB


sloan said:
IsDBNull on the DataReader.

Note, the "ordinal" method will be slightly faster than the "by a columnname
string".

I use const(s) or (readonly) variables to improve readablility


public static readonly int EmployeeIDColumnOrdinal = 0;
public static readonly int LastNameColumnOrdinal = 1;

IDataReader idr;


if (!(idr.IsDBNull(0)))
{
myObject.MyGuid = idr.GetGuid(0);
}


Or with my style:

if (!(idr.IsDBNull(EmployeeIDColumnOrdinal )))
{
myObject.MyGuid =
idr.GetGuid(EmployeeIDColumnOrdinal );
}

I don't actually use the suffix "ColumnOrdinal" . That is there for clarity.




JB said:
Hello

I am using a datareader to reader a table. The table may have some
null
values in it. The thing is that I need to process the row whether the
field
has null values in it or not. The "Swim" field in this table will have
null
values in it. The problem is that when I try to read the field with a null
value I get the error message stating:

"unable to cast object of type 'System.DBNull' to type 'System.String'.

How can I process a datareader record if a field in the record has null
values?

Below is a sample of the problem and the error:


using System;
using System.Collections.Generic;
using System.Text;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;

namespace WillUsedProj
{
class WillUsedList
{
private int PoolLoc;
private string Swims;

public WillUsedList(int PoolAddr, string PollHeat)
{
PoolAddr = PoolLoc;
PollHeat = Swims;
}

public int PoolAddr

{
get { return PoolLoc; }
set { PoolLoc = value; }
}

public string PollHeat
{
get { return Swims; }
set { Swims = value; }
}

public static List<object> WillUsedTable()
{
List<object> dtList = new List<object>();
dtList = WillUsedData.GetTblData();

return dtList;
}
}
}
*********************************************************************

using System;
using System.Collections.Generic;
using System.Text;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;

namespace WillUsedProj
{
class WillUsedData
{
public static List<object> GetTblData()
{
SqlConnection connection = GetConn.GetConnection();
List<object> nuList = new List<object>();
string selectStatement = @"select PoolLoc, Swims from PoolTbl
where Vicinity=10 and
(Swims is null) " + "group by PoolLoc, Swims order by
PoolLoc, Swims ";
SqlCommand selectCommand = new SqlCommand(selectStatement,
connection);
SqlDataReader reader;
connection.Open();

reader =
selectCommand.ExecuteReader(CommandBehavior.SingleResult);
while (reader.Read())
{
WillUsedList WillUsed = new WillUsedList((int)reader["PoolLoc
"],(string)reader["Swims"]);
WillUsed. PoolAddr = (int)reader["PoolLoc"];
WillUsed. PollHeat = (string)reader["Swims"];
nuList.Add(WillUsed.PoolAddr);
nuList.Add(WillUsed. PollHeat);
}
reader.Close();
connection.Close();
return nuList;
}
}
}
Click to expand...
Click to expand...
 
Hello Arne

Actually the error points to the module where the property is set and
not when the column is read from the datareader. But if you say it is a
datareader problem, if I am reading a table with lets say for example 25
columns does that mean write 25 "if" statements (one for each column) or is
it that the datareader is reading the null column and when it hits the
propery set module there is some way to deal with it there?
--
JB


Arne Vajhøj said:
JB said:
I am using a datareader to reader a table. The table may have some null
values in it. The thing is that I need to process the row whether the field
has null values in it or not. The "Swim" field in this table will have null
values in it. The problem is that when I try to read the field with a null
value I get the error message stating:

"unable to cast object of type 'System.DBNull' to type 'System.String'.
Click to expand...
reader =
selectCommand.ExecuteReader(CommandBehavior.SingleResult);
while (reader.Read())
{
WillUsedList WillUsed = new WillUsedList((int)reader["PoolLoc
"],(string)reader["Swims"]);
Click to expand...

if(!reader.IsDBNull(swimcol))
{
// use (string)reader[swimcol]
}
else
{
// use some default value maybe null
}
WillUsed. PoolAddr = (int)reader["PoolLoc"];
WillUsed. PollHeat = (string)reader["Swims"];
nuList.Add(WillUsed.PoolAddr);
nuList.Add(WillUsed. PollHeat);
}
reader.Close();
Click to expand...

Arne
Click to expand...
 
Back
Top