Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

myDataClass.AddRange crashes on integer Null fields in database

Posted on 2013-11-20
13
Medium Priority
?
329 Views
Last Modified: 2013-11-28
I have this line of code which loads data from a database table into a list:
Dim facilities As New List(Of FacilityData)()
...
facilities.AddRange(ds.Tables(0).Rows.OfType(Of DataRow)().Select(Function(r) FacilityData.FromRow(r)))

Open in new window

Problem is some of the data columns are integers, and sometimes that integer field is Null in the database, which really makes the program unhappy (crash) when it hits the facilities.AddRange line.

Is there an easy way to fix this so default values are used if certain fields are null?

I do have a FacilityData class constructor that was supposed to take care of these problems:
    public class FacilityData
    {
        public FacilityData()
        {
            Active = true;
        }
        public FacilityData(DataRow DRow)
        {
            SiteID     = DRow.IsNull("SiteID")     ? 0    : (int)   DRow["SiteID"];
...

Open in new window

but it's using the public FacilityData() constructor instead of the public FacilityData(DataRow DRow) constructor that has all the fix it code for Null fields.
0
Comment
Question by:deleyd
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
13 Comments
 
LVL 96

Expert Comment

by:Bob Learned
ID: 39665692
"which really makes the program unhappy (crash) when it hits the facilities.AddRange line."

What error are you getting?
0
 

Author Comment

by:deleyd
ID: 39667179
It's trying to convert a null to an integer.
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 39667321
1) Does your database column allow nulls?

2) What are you using for a data context?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:deleyd
ID: 39667369
One of the fields is a data type = int, Allow Null = true

Not sure what you mean by "data context"?
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 39667737
I usually work with Entity Framework questions, so that's where my mind went...

I would like to understand this code better:

ds.Tables(0)
   .Rows
   .OfType(Of DataRow)()
   .Select(Function(r) FacilityData.FromRow(r)

Open in new window


What does FromRow do?

Do you know about LINQ-to-DataSets, and DataTableExtensions?

DataTableExtensions.AsEnumerable Method
http://msdn.microsoft.com/en-us/library/system.data.datatableextensions.asenumerable(v=vs.110).aspx

var productNames = 
   from products in table.AsEnumerable() 
   select products.Field<string>("ProductName");

Open in new window

0
 

Author Comment

by:deleyd
ID: 39667765
>What does FromRow do?
I'm not sure. That's the way the code was when I inherited it.

Probably I should look for a new completely different way to fill this facilities list with all the facilities.
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 39667846
Can you show me the FromRow method from the FacilityData class?
0
 

Author Comment

by:deleyd
ID: 39679847
Found it:
        public static FacilityData FromRow(DataRow dr)
        {
            return DB.LoadObj<FacilityData>(dr);
        }

Open in new window

0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 39680169
What is "DB" declared as?  Looks a little like a data context from something like Entity Framework...

Also, what class did you find this method in?
0
 

Author Comment

by:deleyd
ID: 39682365
    public class DB
    {
...
        public static T LoadObj<T>(DataRow dr, IEnumerable<T> existingObjs=null)
        {

            var type = typeof(T);
            var props = GetProperties(typeof(T),null,BindingFlags.Default);

            var keyProps=props.Where(p=>p.PKAttr!=null).ToArray();
            var keyVals=keyProps.Select(p=>NullIfDBNull(dr[p.Name])).ToArray();
            object obj = existingObjs == null ? Activator.CreateInstance<T>() : GetOrCreate<T>(keyVals, existingObjs, keyProps);

            foreach (var prop in props)
            {
                if (dr.Table.Columns.Contains(prop.Name))
                {
                    object val = dr[prop.Name];
                    if (DBNull.Value.Equals(val))
                        val = null;
                    prop.Property.SetValue(obj, ChangeType(val, prop.Property.PropertyType), null);
                }
            }
            return (T)obj;
        }

Open in new window

Maybe this is where I need to make the change. I see there's a test
if (DBNull.Value.Equals(val))

(What the heck is this code doing anyway? Why so complicated? Aren't there easier ways of getting data from the database?)
0
 
LVL 96

Accepted Solution

by:
Bob Learned earned 2000 total points
ID: 39683574
That code uses reflection to get the values from DataTable, by matching the column names to the property name for the business object class.

There are definitely easier approaches to database access, rather than using complicated (and slower) reflection code.

The classification is OR/M or (Object Relational Mapping).  These systems attempt to make it easier to define a data layer for a n-tier application, but usually require a considerable amount of time to learn.

Entity Framework Tutorial
http://www.entityframeworktutorial.net/

NHibernate Made Simple
http://www.codeproject.com/Articles/21122/NHibernate-Made-Simple
0
 

Author Comment

by:deleyd
ID: 39683994
Let's see, the original goal was to create a list of facility class instances.
Dim facilities As New List(Of FacilityData)()

Open in new window

This creates a list, and then I just need to fill in that list from the database.

I have variable ds, a System.Data.DataSet with all the data:
        Dim ds As New DataSet
        Using conn = CCSqlCon()
            Using cmd = DB.CreateCommand("SELECT * FROM CFacility WHERE Active=1 ORDER BY SiteID", conn)
                Using ad = DB.CreateAdapter(cmd)
                    ad.Fill(ds)
                End Using
            End Using
        End Using

Open in new window

ds.Tables is a System.Data.DataTableCollection. There's only 1 table, so ds.Tables(0).Rows gives me a System.Data.DataRowCollection. I can then do:
    foreach(DataRow row in table.Rows)
    {
        create instance of facility class, hand row to the constructor
        insert instance into facilities list
    }

Open in new window

Gee that sounds like it actually ought to work. I'll give it a try.
0
 

Author Closing Comment

by:deleyd
ID: 39684033
Thank you for all the wonderful help. It got me to where I needed to make the change. My idea actually worked!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question