Advice for assigning data from a DataReader to object properties

[Froefel]

Hi group

I am creating a web application that uses a simple DAL as an
ObjectDataSource.
To retrieve data from the database, I use a DataReader object from
which I then assign the various fields to properties in an object,
like so:

in the DB, the fields are defined as follows:
ProjectID int NOT NULL
Description varchar(50) NULL
Status int NULL
Active bit

My object looks like this:
public class Project
{
uint ProjectID;
string Description;
int Status;
bool Active;
}

Project p = new Project();
p.ProjectID = (uint) dr["ProjectID"];
p.Description = dr["Description"].ToString();
p.Status = (int) dr["Status"];
p.Active = (bool) dr["Active"];

I am experiencing a significant amount of problems when dealing with
nullable fields.
Because Description and Status are nullable, I have to check for that
prior to assigning it to the properties, otherwise an exception will
occur.

if (dr["Description"].ToString().Length > 0) p.Description =
dr["Description"].ToString();
if (dr["Status"].ToString().Length > 0) p.Status = (int) dr["Status"];


It seems to me that this is overly complex and very inefficient... Is
there a best practice that I'm missing to address these kind of
conversions and checks?

-- Hans

 

[bhar]

Hi,

I hope you get the idea.

Private Sub Form1_Load(ByVal sender As Object, ByVal e As
System.EventArgs) Handles Me.Load

Dim SelectStatement As String ' 2.
The SelectStatement Used for SQL
Dim NorthwindDataReader As SqlDataReader ' 3.
The Datareader that will return from the DataAccess Object
Dim Con As String =
System.Configuration.ConfigurationManager.AppSettings("Northwind")
SelectStatement = "Select * From AccountsTable"

'Instantiate the Data Access Oject
Dim localSQLServer As New DataServer(Con)
'Call the runSQLDataSet Function that takes the SQL String, an
optional Table name and returns the DataSet
NorthwindDataReader =
localSQLServer.runSQLDataReader(SelectStatement)
'We can now populate the Grid with the returning DataSet's
DataTable
'DataGridView1.DataSource = NorthwindDataReader
'DataGridView1.DataBindings()

If NorthwindDataReader.Read = True Then

TextBox1.Text = NorthwindDataReader(0)
TextBox2.Text = NorthwindDataReader(1)
End If

Dim intCol As Integer
With NorthwindDataReader
If .HasRows Then
DataGridView1.Rows.Clear()
'Add column definition: FieldName, and ColumnName
For intCol = 0 To .FieldCount - 1
DataGridView1.Columns.Add(.GetName(intCol),
GetName(intCol))
Next
'Base column width on header text width
DataGridView1.AutoSizeColumnsMode = _
DataGridViewAutoSizeColumnsMode.ColumnHeader
While .Read
'Get row data as an Object array
Dim objCells(intCol) As Object
GetValues(objCells)
'Add an entire row at a time
DataGridView1.Rows.Add(objCells)
End While
End If
End With
End Sub

 

[bruce barker]

the main problem is value types cannot be null. use the new nullable
type. then check for DBNull being returned.

public class Project
{
uint? ProjectID;
string Description;
int? Status;
bool? Active;
}

p.ProjectID = dr.IsDBull(dr.GetOrdinal("ProjectID") : null ? (uint)
dr["ProjectID"];


i'd write a helper routine

-- bruce (sqlwork.com)

 

[Froefel]

Hi Bruce,

I was hoping there was another way, but if the nullable data types are
what you're proposing, then that's what I'll go for.

I do have some more questions about it though... I think the answers
will very depending on whom I'm talking to, but that's just what I'm
after, so I can get an initial repository of paths to follow.

1. When using nullable types, would you recommend a best practice to
use nullable types only for the private fields inside the object
(those that get their data from the DB), and work with standard types
for the public properties?
For example:
private int? _status
public int Status
{
get
{
if (_status != DBNull)
return _status;
else
// return some default value
}
set
{
_status = value;
}
}

public void GetData()
{
//get DataReader object -- code omitted --
_status = (int?) dr["Status"];
}

2. what's your take on allowing NULL in the database? Thus far I've
always allowed it for fields that could be empty. But for fields that,
if empty, should take a default value, would it be better to set that
default value in the DB and set the field as NOT NULL. Or is it better
to provide the default value in the property set of the object class
(like in the example above)? I know there's no definitive answer to
this, but I'm looking for reasonings for and against nullable DB
fields.

-- Hans

the main problem is value types cannot be null. use the new nullable
type. then check for DBNull being returned.

public class Project
{
uint? ProjectID;
string Description;
int? Status;
bool? Active;
}

p.ProjectID = dr.IsDBull(dr.GetOrdinal("ProjectID") : null ? (uint)
dr["ProjectID"];

i'd write a helper routine

-- bruce (sqlwork.com)

Hi group

I am creating a web application that uses a simple DAL as an
ObjectDataSource.
To retrieve data from the database, I use a DataReader object from
which I then assign the various fields to properties in an object,
like so:

in the DB, the fields are defined as follows:
ProjectID int NOT NULL
Description varchar(50) NULL
Status int NULL
Active bit

My object looks like this:
public class Project
{
uint ProjectID;
string Description;
int Status;
bool Active;
}

Project p = new Project();
p.ProjectID = (uint) dr["ProjectID"];
p.Description = dr["Description"].ToString();
p.Status = (int) dr["Status"];
p.Active = (bool) dr["Active"];

I am experiencing a significant amount of problems when dealing with
nullable fields.
Because Description and Status are nullable, I have to check for that
prior to assigning it to the properties, otherwise an exception will
occur.

if (dr["Description"].ToString().Length > 0) p.Description =
dr["Description"].ToString();
if (dr["Status"].ToString().Length > 0) p.Status = (int) dr["Status"];

It seems to me that this is overly complex and very inefficient... Is
there a best practice that I'm missing to address these kind of
conversions and checks?

-- Hans