CODEWITHSUNDEEP
c#sqldatareader
RobinHu
RobinHu

Reputation: 384

Generic way of handling DBNull

I have a lot of this type of logic in my code when using the SqlDataReader Is there a cleaner more generic way to handle this?

 if (reader["VisitingAddressId"] != DBNull.Value)
 {
     visitingAddress = new Address()
     {
         AddressId = Convert.ToInt64(reader["VisitingAddressId"]),
         Address1 = reader["VisitingAddress"].ToString(),
         AddressType = AddressType.VisitingAddress,
         PostalCode = reader["VisitingPostal"].ToString(),
         PostalDistrict = reader["VisitingPostalDistrict"].ToString()
      };
  }

 if (reader["PostalAddressId"] != DBNull.Value)
 {
     postalAddress = new Address()
     {
         AddressId = Convert.ToInt64(reader["PostalAddressId"]),
         Address1 = reader["PostalAddress"].ToString(),
         AddressType = AddressType.PostalAddress,
         PostalCode = reader["PostalPostal"].ToString(),
         PostalDistrict = reader["PostalPostalDistrict"].ToString()
      };
  }

Upvotes: 2

Views: 1722

Answers (3)

Jonas Høgh
Jonas Høgh

Reputation: 10874

You could use a micro-ORM like Dapper: http://code.google.com/p/dapper-dot-net/

The multi mapping functionality would eliminate all that boiler plate code.

db.Query<Post,Address,Address,Post>("select * from Posts left join Address ... etc", 
 (post,vaddress,paddress) => 
  {
     post.VisitingAddress = vaddress; 
     post.PostalAddress = paddress; 
     return post; 
   });

Upvotes: 2

Rob Kent
Rob Kent

Reputation: 5193

I have these helper methods in a data service class (you could make them both static):

    public T CastDBValue<T>(object value)
    {
        return MapValue<T>(value);
    }   

    internal static T MapValue<T>(object value)
    {
        try
        {
            T result;
            result = value == DBNull.Value ? default(T) : (T)value;
            return result;
        }
        catch (InvalidCastException cex)
        {
            logger.ErrorFormat("Invalid cast while mapping db value '{0}' to type {1}. Error: {2}", value, typeof(T).Name, cex);
            throw new InvalidCastException(string.Format("Invalid cast while mapping db value '{0}' to type {1}. Error: {2}", value, typeof(T).Name, cex.Message));
        }
    }

Then in your mapping code, you just do:

AddressId = dataService.CastDBValue<int>(reader["AddressId"]));
if (AddressId > 0) { ... }

Upvotes: 3

Kieren Johnstone
Kieren Johnstone

Reputation: 42003

What you are referring to are ORMs, of which there are many. NHibernate, Entity Framework, even ADO.NET (which you are already using) has support for relational datasets, although in that case you usually have to use a DataSet or derived, strongly-typed classes.

Check out the .NET section here for a list:

http://en.wikipedia.org/wiki/List_of_object-relational_mapping_software

What are you requirements? Maybe we can narrow it down.

Upvotes: 0

Related Questions