As with most projects, this one is the culmination of information learned while struggling to find an answer to the boss's wish to make a sub system easier, better, faster, and more flexible. This project will describe a little known .NET class named
PropertyDescriptor and how you can use it to your advantage.
During the course of this project, we will look at what it takes to build a lightweight flexible database that can be changed at runtime. This project will be limited to the basics. Some of the items on the picture above are not implemented but are shown as place holders for items that you may wish to implement yourself. A database application was chosen to demonstrate how to use the
PropertyDescriptor. The principles of the .NET class lend itself well to dynamically changing data structures at runtime.
One of the requirements I had was to make the system flexible. One of the most flexible data structures .NET has is the
DataSet. You can add tables, columns, rows, and constraints to emulate an in-memory database. All of the aforementioned items can be added at runtime to increase the meaning of the data provided. For instance, adding another column to the picture above to include a phone number could provide immense additional information.
Another requirement was to make the subsystem better. If I were to use a
DataSet as the underlying structure of my subsystem, how would I save the data? The first requirement was to be flexible. If I change the data structure of the table at runtime, and add data how would I save the data to a true database like SQL Server, Oracle, Access, etc.? I would have to have a generic table or store it as an XML data type, a string, etc. How would I search for data? Using a
DataSet violates the tenant of my second requirement and doesn't satisfy the requirement that it is easy to maintain or extend.
As the project is designed to emulate a database, I have chosen a data structure that looks like a database structure, one with tables, columns, rows, and cells. The following diagram shows the data structure and the more common methods and properties that will be used for the solution.
Working with a data structure such as this provides a problem for a
DataGrid to bind to for data entry. If we bind the
DataSource of the
DataGrid to the
RowCollection, we get all the rows and no cells. If we bind to the
CellCollection, we get all the cells and no rows. Ultimately, we will need all the cells from all the rows.
For the second attempt, we could create a
DataSet with all the columns and rows by traversing our
RowCollection. How do we marshal data back and forth between the data structure and the
DataSet? We ask our self once again, is this easy, fast, better and flexible. The answer is emphatically...No.
So what are we left with? The Rosetta Stone that glues this data structure together is the
PropertyDescriptor is described in the help file as "Provides an abstraction of a property on a class." Wow, that description leaves much to be desired. What about the example provided? This is what's provided in the help:
PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(button1);
System.ComponentModel.PropertyDescriptor myProperty = properties.Find("Text", false);
textBox1.Text = myProperty.DisplayName+ '\n' ;
textBox1.Text += myProperty.Description + '\n';
textBox1.Text += myProperty.Category + '\n';
In this form, the example doesn't help solve our problem. This is finding an existing
PropertyDescriptor and displays some information about it. However, looking at the definition gives us a clue. We are going to be dealing with a property on a class. The question becomes which class and which property.
Since we need all the rows, our
DataSource will be
Table.RowCollection. Now we need columns for our
DataGrid. The column names and individual data cells usually come from a property of the item in the collection. In our case, that means a
Row. Looking at the
Row class, what properties are available to give us the individual data points? Unfortunately, the data is stored in another collection,
CellCollection. How do we get the individual Cell out of the
CellCollection that is associated with the
Row and provide a meaningful
DataGrid column name?
We solve this in two parts: Create the columns and then fill with data.
DataGrid is comprised of columns which have meaningful names. We have a
ColumnCollection for each table in which each Column has a name of the column (
ColumnName), a name to display (
ColumnDisplayName) in the
DataGrid and a data type (
ColumnType) to be entered. We can do this by iterating through the
ColumnCollection dynamically and create each column.
foreach ( Column column in _table.Columns )
gridColumn = CreateColumn( column );
if ( gridColumn is DataGridViewComboBoxColumn )
BindComboBox( (DataGridViewComboBoxColumn)gridColumn, column );
_rowGrid.Columns.Add( gridColumn );
The second half of the solution is to provide a property inside each
Row that can be bound to the
DataGrid column. This is where the
PropertyDescriptor comes in. In order to provide the functionality that we will eventually need, we will create our own class (
ColumnPropertyDescriptor) derived from
ITypedList which has a method
GetItemProperties which returns each
PropertyDescriptor within a collection. This is the list of properties that the
DataGrid will bind to. We must manually create this list based on the columns in the
Column. To that end, each time we create
Column, we will create a new
PropertyDescriptor passing in the
Column as a parameter. Each time we delete the column, we will need to find the
PropertyDescriptor that the column was created for and remove it from the list.
Dynamically creating or removing
ColumnPropertyDescriptor within each
RowCollection allows us to create properties for the
DataGrid to bind to.
public new void Add( Column column )
if ( column != null )
this._table.Rows.AddPropertyDescriptor( column );
base.Add( column );
public new bool Remove( Column column )
if ( column != null )
base.Remove( column );
this._table.Rows.RemovePropertyDescriptor( column );
Each time the
DataGrid wishes to display a cell, it will call the
GetValue method of the
PropertyDescriptor passing in the current object, a
Row object. With the given row as input, we can iterate through the
CellCollection looking for a cell that has a
Column that matches the
Column within this
PropertyDescriptor. When we find that match, we have found the value to display. An additional method
SetValue takes two parameters, the
Row and the value to store.
With the creation of the
ColumnPropertyDescriptor class that has the
SetValue methods implemented, a method to add and remove a
ColumnPropertyDescriptor object from the
RowCollection and a method to create the
DataGrid columns, we draw to the end of this article.
The database project is meant to be portable to other projects. Other than the specific locations mentioned in this article above, the internal code in the other classes implement normal everyday code. The collection classes could be updated to include the capability to sort, search, filter, etc. as defined by the
Running the Demo
Start up the application and go to File -> Open and select both of the SQL files (Address.sql and States).
With both of the files loaded, click on the
Address table and then the Definition tab, change the state data type to "Foreign Key". This will then show two additional fields at the bottom of the screen. One that says "Table" the other says "Column". Set the table to "States" and the column to "Abbreviation" and click back to the Data tab and you will see that the state field is now a
Now let's add some new fields. Go back to the Data tab for
Address. Add in two new columns so that it looks like the following:
When you go back to the Data tab, you will see the two new columns that you can add data into.
Where To Go From Here
Setting break points at some of the code snippets listed in this article is a good place to start to really understand the flow of the application. Be aware, setting break points in the
ColumnPropertyDescriptor is good for understanding but bad for running the application. You will get into an endless loop of trying to display the data. You will have to remove/disable the breakpoint for the application to run.
For simplicity, The
SqlScriptReader in the
DataAccess class has some hard coded structures for reading in the SQL data files. This would have to be modified to read other data files.
While the application doesn't allow for saving of the data, the data could easily be stored in an XML file, back into a SQL text file or even written out to a real database. These I leave as an exercise as it is beyond the scope of this project.
Other suggestions are to implement some of the other fields on the Definition tab and add other data types. The database project presented here allows for you to implement your own
Column class adding any new properties for data masks or other validation routines and to wire them up via normal UI practices.
I have used this in a production environment with up to 10,000 rows of data with about 15 columns without experiencing any degradation of performance. As with any demonstration of technology, improvements to performance and functionality can always be made. These I leave to the reader as they are beyond the scope of this article.
- 19th March, 2007: Initial post