Advice for assigning data from a DataReader to object properties

Discussion in 'ASP .Net' started by Froefel, Jul 13, 2007.

  1. Froefel

    Froefel Guest

    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
    Froefel, Jul 13, 2007
    #1
    1. Advertising

  2. Froefel

    bhar Guest

    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



    --
    bhar
    ------------------------------------------------------------------------
    Posted via http://www.codecomments.com
    ------------------------------------------------------------------------
    bhar, Jul 13, 2007
    #2
    1. Advertising

  3. Froefel

    bruce barker Guest

    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 wrote:
    > 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
    >
    bruce barker, Jul 13, 2007
    #3
  4. Froefel

    Froefel Guest

    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



    On Jul 13, 6:00 pm, bruce barker <> wrote:
    > 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 wrote:
    > > 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
    Froefel, Jul 17, 2007
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. , India
    Replies:
    1
    Views:
    670
    Pascal J. Bourguignon
    Apr 22, 2008
  2. Nathan Sokalski
    Replies:
    5
    Views:
    699
    FlashMerlot
    Mar 11, 2009
  3. Replies:
    2
    Views:
    379
  4. Richard Lionheart
    Replies:
    27
    Views:
    392
    Jean-Hugues ROBERT
    May 4, 2004
  5. weston
    Replies:
    1
    Views:
    246
    Richard Cornford
    Sep 22, 2006
Loading...

Share This Page