Dynamic Properties - A Database Created At Runtime

• Download demo - 33.74 KB
• Download source - 193.35 KB

Introduction

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.

Background

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.

Design Architecture

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 ColumnCollection and 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. A 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:

// Creates a new collection and assigns it the properties for button1.
PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(button1);

// Sets an PropertyDescriptor to the specific property.
System.ComponentModel.PropertyDescriptor myProperty = properties.Find("Text", false);

// Prints the property and the property description.
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.

The 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.

// Create Columns 
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 PropertyDescriptor.

The RowCollection implements 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.

ColumnCollection.cs

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 GetValue and 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 IBindingList interface.

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 ComboBox.

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.

Final Thoughts

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.

History

• 19^th March, 2007: Initial post