Solved

myDataClass.AddRange crashes on integer Null fields in database

Posted on 2013-11-20
13
316 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
  • 7
  • 6
13 Comments
 
LVL 96

Expert Comment

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

What error are you getting?
0
 

Author Comment

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

Expert Comment

by:Bob Learned
Comment Utility
1) Does your database column allow nulls?

2) What are you using for a data context?
0
 

Author Comment

by:deleyd
Comment Utility
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
Comment Utility
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
Comment Utility
>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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 96

Expert Comment

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

Author Comment

by:deleyd
Comment Utility
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
Comment Utility
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
Comment Utility
    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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Code enhancement 5 12
Round a string to two digits 12 22
dynamic menu in asp.net c# 11 21
Get list of word ducuments in a folder 10 9
More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

728 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now