Parse DataSet via Reflection

• Download source - 28.91 KB

Introduction

A lot of times, I get a task to bind a relational model with an object designed model. Sometimes, I create an application engine with NHibernate, sometimes I use ADO.NET Entity Framework, but most of all I had to use DataTable and create a wrapper around DataTable objects with large "switch"s in it.

NHibernate is a good choice when you develop "Desktop" or "Web" applications, but it doesn't work in Compact Framework on Windows mobile, because CF doesn't support dynamic compilation. ADO.NET Entity Framework is a good choice for "new" applications designed in .NET 3.5 style. In this article, I want to show how to parse DataSet and create objects from it. You can download the source code from the link at the top of this article and use this little library.

Background

To test the application, you should install SQL Compact 3.5 and .NET 3.5. You can also use this library in .NET 2.0, but you should replace LINQ usages.

Using the Code

To show how it works, I create a simple database that includes 3 tables. The core of this system is generic class BusinessObject<T>. This class contains the necessary static methods that allow to get derived objects.

public static List<T> Get(DataSet dataSet) 

public static List<T> Get(DataTable dataTable) 

public static T GetOne(DataSet dataSet)	

public static T GetOne(DataTable dataTable) 

public static T GetOne(DataRow row) 

Steps to bind object:

1. Create a new class derived from BusinessObject<T>.
2. Create properties that correspond to fields in database and mark them with "BoFieldAttribute".
3. If a table has relations with other tables, create new properties and mark them with BoChildAttribute.

BoFieldAttribute - has constructor with argument "name". You should put a string which corresponds to ColumnName in DataTable.

Here is an example:

class Customer : BusinessObject<Customer>
    {
        [BoField(CustomerNames.Id)]
        public int Id { get; set; }
        [BoField(CustomerNames.Name)]
        public string Name { get; set; }
    }

class Product : BusinessObject<Product>
    {
        [BoField(ProductNames.Id)]
        public int Id { get; set; }
        [BoField(ProductNames.Title)]
        public string Title { get; set; }
    } 

    class OrderLine : BusinessObject<OrderLine>
    {
        [BoField(OrderLineNames.Id)]
        public int Id { get; set; }
        [BoField(OrderLineNames.ProductId)]
        public int ProductId { get; set; }
        [BoField(OrderLineNames.CustomerId)]
        public int CustomerId { get; set; }

        [BoChild]
        public Customer Customer { get; set; }
        [BoChild]
        public Product Product { get; set; }
    }  

Then it's very simple to use. Just invoke one of the "Get" methods.

  const string sql =
                @"SELECT * FROM OrderLine INNER JOIN Customer 
				ON Customer.C_Id = OrderLine.O_CustomerId
						INNER JOIN Product 
				ON Product.P_Id = OrderLine.O_ProductId";
  DataTable dataTable = DatabaseLayer.Instance.ExecuteTable(sql);
  lwOrderLines.ItemsSource = OrderLine.Get(dataTable);

How It Works

When you invoke one of the "Get" methods:

1. BusinessObject class creates new object of generic type.
2. Search for marked attributes and assign new values to properties from DataTable.

One more interesting thing that I want to describe is the GetConvertedObject method:

protected virtual object GetConvertedObject(Type type, object o)
        {
            if (type.IsEnum) return Enum.Parse(type, o.ToString(),true);
            if (type == typeof(byte[])) return o.Equals(DBNull.Value) ? null : o;
            if (type == typeof(DateTime)) return ToDateTime(o);
            if (type == typeof(string)) return ToString(o);
            if (type == typeof(int)) return ToInt32(o);
            if (type == typeof(long)) return ToInt64(o);
            if (type == typeof(DateTime)) return ToDateTime(o);
            if (type == typeof(Double)) return ToDouble(o);
            if (type == typeof(Boolean))
                return o.ToString().Trim() == "1" 
                           ? true : o.ToString().Trim() == "0" 
                                        ? false : type.ParseValue(o); 
            return type.ParseValue(o);
        }   

This method is virtual and can be overriden in the derived class. It is very useful if we want to have the possibility to convert data in parse action. For example, we can store data in SQL Compact as image type and convert it to System.Drawing.Image or to ImageSource (WPF version).

History

• 1^st September, 2009: Initial post