Dynamically Setting Properties of a Class from a Database

In this short example I will demonstrate a powerful feature of Reflection that will allow you to dynamically set the values of a class. By setting the properties of a class dynamically, you can create a simple method that allows your class to be constructed and pre-populated from a database.

We will use an Employee class, and a stored procedure named getAllEmployees.

The stored procedure looks like this:

CREATE PROCEDURE dbo.getAllEmployees
AS
select * from employees
RETURN

The procedure above will get all the employee records from the Employees table. Create this stored procedure in your Northwind database. (Installed by default with SQL server installation)

public class EmployeeCollection
{
#region Start - private variables
private int _employeeID;
private string _lastName;
private string _firstName;
#endregion
public EmployeeCollection(){}
public int EmployeeID
{
get { return _employeeID; }
set { _employeeID = value; }
}
public string LastName
{
get { return _lastName; }
set { _lastName = value; }
}
public string FirstName
{
get { return _firstName; }
set { _firstName = value; }
}
}

The Employee class contains a subset of properties that correspond to the column names of the Employees table. We will now create a new constructor for the Employee class. This constructor will allow us to perform some magic. The constructor will accept a DataRow as a parameter. When the constructor is called it will set the properties of the Employee class based on the data in the DataRow parameter. By doing this, you will be able to construct a collection of employees in a very simple manner.

public Employee (System.Data.DataRow dataRow)
{
/* Enumerate all the properties of this class
* if there is a corresponding column in the data row
* set this class' property to the value of the column in the
* datarow. */
foreach( System.Reflection.PropertyInfo pi in
this.GetType().GetProperties())
{
if (pi.CanWrite)
{
try
{
if (dataRow[ pi.Name ] != null && !dataRow.IsNull( pi.Name ) )
{
pi.SetValue(this, dataRow[ pi.Name ], null);
}
else
{
pi.SetValue(this, null, null);
}
}
catch // DB COLUMN does not exist for this property.
{
pi.SetValue(this, null, null);
}
}
}
}

In the EmployeeCollection we will now build a method named Select that will retrieve all of the rows from the Employees table into a collection of Employee classes.

public EmployeeCollection( SqlConnection cn )
{
if (cn.State != ConnectionState.Open)
cn.Open();
//Get a recordset
SqlCommand cmd = new SqlCommand("getAllEmployees", cn);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable table = new DataTable();
//Place the records into a DataTable object so
//we can enumerate the rows.
da.Fill(table);
foreach(DataRow dr in table.Rows)
{
this.InnerList.Add( new Employee( dr ) );
}
}

The EmployeeCollection constructor will take a SQL connection object as an argument. With this argument, the constructor will run the stored procedure that we created in the Northwind database. That stored procedure will return a record set of ALL the rows in the Employees table. This code will enumerate through all the rows returned and add an Employee class to the EmployeeCollection for each row in the record set. Additionally, since we are constructing the Employee class with the data row, the Employee class will be pre-populated with all of the applicable data from the data row.

private void btnGetRecords_Click(object sender, System.EventArgs e)
{
string _connectionString = "SERVER=_SERVERNAME_;DATABASE=NORTHWIND;UID=_USERNAME_;PWD=_PASSWORD_"; dataGrid1.DataSource = new EmployeeCollection(new SqlConnection(_connectionString));
}

This methodology becomes very useful in higher level coding, such as UI development. For example, take the button click event from the example form. In order to get the data from the database, and populate a datagrid with the results, you simply use ONE LINE OF CODE! How simple is that. Implementing strongly typed collections in this manner allows a less skillful developer, or someone who is less knowledgeable about your application write code that uses your API. In addition it keeps the business logic where it should be, in your applications foundation classes.

1 Comment

Join the conversation! Your thoughts help the community grow.

satyen mishra4y ago
Hello David, very nice article, Can I ask the following ?: I have a database configurable data stored in a table called dbo.CONFIG. This stores basically all the confKey and its confValues. It was just 2 columns in it. My question would How can I just call or hit once to the dbo.CONFIG table, bring all the confKeys and confValues records, store it in a class object session and then utilize the sessions attributes across all the pages as and when required. Is this approach looks correct or What would be your way. My purpose is to just call once to the CONFIG table i.e. hitting once to the DB and then perform in application session to improve performance.