Solved

myDataClass.AddRange crashes on integer Null fields in database

Posted on 2013-11-20
13
325 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 500 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

Industry Leaders: 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!

Question has a verified solution.

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

In my previous two articles we discussed Binary Serialization (http://www.experts-exchange.com/A_4362.html) and XML Serialization (http://www.experts-exchange.com/A_4425.html). In this article we will try to know more about SOAP (Simple Object Acces…
Introduction Hi all and welcome to my first article on Experts Exchange. A while ago, someone asked me if i could do some tutorials on object oriented programming. I decided to do them on C#. Now you may ask me, why's that? Well, one of the re…
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…

729 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