Dynamically setting properties of a class from a Database

In this short example I will demonstrate apowerful feature of Reflection that will allow you to dynamically setthe values of a class. By setting the properties of a classdynamically, you can create a simple method that allows your class tobe 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 employeerecords from the Employees table. Create this stored procedure in yourNorthwind 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 ofproperties that correspond to the column names of the Employees table.We will now create a new constructor for the Employee class. Thisconstructor will allow us to perform some magic. The constructor willaccept a DataRow as a parameter. When the constructor is called it willset the properties of the Employee class based on the data in theDataRow parameter. By doing this, you will be able to construct acollection 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 amethod named Select that will retrieve all of the rows from theEmployees 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 aSQL connection object as an argument. With this argument, theconstructor will run the stored procedure that we created in theNorthwind database. That stored procedure will return a record set ofALL the rows in the Employees table. This code will enumerate throughall the rows returned and add an Employee class to theEmployeeCollection for each row in the record set. Additionally, sincewe are constructing the Employee class with the data row, the Employeeclass will be pre-populated with all of the applicable data from thedata 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 higherlevel coding, such as UI development. For example, take the buttonclick event from the example form. In order to get the data from thedatabase, and populate a datagrid with the results, you simply use ONELINE OF CODE! How simple is that. Implementing strongly typedcollections in this manner allows a less skillful developer, or someonewho is less knowledgeable about your application write code that usesyour API. In addition it keeps the business logic where it should be,in your application's foundation classes.