Reflecting Data In .NET Classes - Part IV - From Database Table

Overview

 

This article completes the 4 parts series of Reflecting Data in .NET Classes. In the 3 preceding articles, we have looked at how to reflect data to .NET classes from Web/HTML Forms, WinForms, as well as XML Documents. In this article, we will be looking at how to reflect data from the most common data source Database tables.

 

Description

 

Again, just like in part 2 and part 3, this article assumes some basic understanding of Reflection. You can refer back to the first part for some information on it.

• Part I HTML/Web Forms
• Part II XML Documents
• Part III Windows Forms
• Part IV right here!

Although the data demonstrated in this article comes from a completely different data source, but the data mapping mechanism with Reflection pretty much remains the same, with just one little different. The mapping mechanism from all the preceding 3 parts were first to find out all the property a given class has, then for each property, we try to match a value from the data source (ex: web/win forms, xml doc etc). However, the mapping mechanism in this article is kind of the other way around. We first look at what kind of data the data source (database tables) has, and then try to match a property from the objects.

 

The idea is like this,

From a given SQL Select query, we get a DataReader object from the Command and Connection objects. Then we loop through the DataReader object, the way you and I have been doing for hundreds times already. For each row in the DataReader, we create an instance of the class of a given name. Then we loop through the columns of the DataReader object. For each column, we try to match a property from the object with the identical name, and assign the value from the DataReader to the property. After we loop through the columns on a row, we add the object to an ArrayList object. Then move on to the next row, where we will do the same thing again. If nothing goes wrong, we close the DataReader and the Connection object, then return the ArrayList object.

 

The example in this article consists of 5 files,

• Employee.sql the SQL script that creates the database table, and some data.
• DBAccess.cs the class that contains the mapping code.
• Employee.cs an entity class with a few properties and an overridden ToString() method.
• Test.cs - code for a test drive!
• Make.bat a batch file to compile the 3 C# classes.

The example has been developed and tested with Microsoft SQL Server 2000 Standard edition. After you run the Employee.sql, you will have a database table like this,

 

 

Below is the code in test.cs,

using System;
using System.Collections;
using DataReflector;
public class testDBReflector {
    public static void Main(string[] args) {
        string sqlQuery = "select * from employee order by hiredDate";
        string assemblyName = "employee";
        string className = "BusinessEntity.employee";
        ArrayList employees = Database.getArrayList(sqlQuery, assemblyName, className);
        for (int i = 0; i < employees.Count; i++) {
            Console.WriteLine(employees[i]);
            Console.WriteLine("----------------------");
        }
    }
}

Notice this line,

ArrayList employees = Database.getArrayList(sqlQuery, assemblyName, className);

 

Woohoo! All of the sudden, with just one method call; you get an ArrayList of specified objects whose properties are filled by the data from the select query! Which means no more boring line by line mappings and value assignment. Lets look at what you will see from running the test drive code,