DogSpots - .Net Programming

Programmatically check if SQL Replication Components are Installed

Bill — Fri, 02 Oct 2009 23:16:00 -0500

I spent some time this week tightening up the code in a project that will actually be released into the wild. I wanted to make sure that I catch as many errors as possible and put up user friendly messages.

The program uses SMO and RMO to synchronize a local SQL Express database with a master database over the internet. The program would get some pretty cryptic error messages if SQL Express wasn’t installed, if the expected instance weren’t available or if the replication components weren’t installed.

I was easily able to check if SQL Express was installed and to get a list of the installed instances, but I couldn’t find a way to check if the replication components were installed. This was annoying because the replication components aren’t installed by default when SQL Express is installed and I was assuming that this situation would come up often.

After some refactoring, I was able to get it to throw an exception with a pretty straight forward error message: “Replication components are not installed on this server. Run SQL Server Setup again and select the option to install replication.” In fact, I could catch the exception and put up whatever error message I wanted. Unfortunately, this exception would not occur until the program spent quite a while preparing to do the synchronization. I really wanted to check for the replication components before I attempted any synchronization.

I spent quite a while googling trying to find something that would work, but couldn’t find anything. Then I tried checking for the existence of several stored procedures, tables and entries in the SysObjects tables, but nothing I found on a server with the replication components was missing on one without them.

Then I came across the sp_MS_replication_installed system stored procedure. It’s on all installations and tells exactly what I needed. I was surprised that with all the searching I did, there was no mention of this handy stored procedure, so I figured I would do my part and document what I found.

The stored procedure is simple enough in that it returns a 0 if the replication components are installed and a 1 if they aren’t. However, under usual circumstances, it goes a step further and raises an exception if the components aren’t installed. This is because the procedure reads an entry in the registry to see if the replication components are installed and if they aren’t, it is likely that the entry was never written in the first place. When it can’t find the registry entry, an exception is thrown. The only time the stored procedure would return a 1 without throwing an exception is if the replication components were installed at some point and then later uninstalled.

USE [master]

GO

/******Object:  StoredProcedure [sys].[sp_MS_replication_installed]******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

  

--

-- Name: sp_MS_replication_installed

--

-- Descriptions: 

--

-- Parameters: as defined in create statement

--

-- Returns: 0 - success

--          1 - Otherwise

--

-- Security: 

-- Requires Certificate signature for catalog access

--

ALTER procedure [sys].[sp_MS_replication_installed]

as

    set nocount on

    declare @isinstalled int

    select @isinstalled = 0

    declare @retcode int

    

    EXECUTE @retcode = master.dbo.xp_instance_regread 'HKEY_LOCAL_MACHINE', 

        'SOFTWARE\Microsoft\MSSQLServer\Replication',

        'IsInstalled',

        @param = @isinstalled OUTPUT

  

    IF ( @retcode <> 0 ) or ( @@ERROR <> 0 ) 

    begin

        raiserror (21028, 16, -1)

        return (0)

    end

    

    if (@isinstalled is null or @isinstalled = 0)

    begin

        raiserror (21028, 16, -1)

        return (0)

    end

  

    return (1)

View Plain

I could use the sp_MS_replication_installed stored procedure as is and catch the exception when it got thrown, but I really don’t like code that relies on exceptions to operate correctly. Exceptions should be just that – the exception.

private static bool replicationInstalled(string connString) {

    bool result = false;

    using (SqlConnection conn = new SqlConnection(connString)) {

  

        conn.Open();

        using (SqlCommand cmd = new SqlCommand()) {

            cmd.Connection = conn;

            cmd.CommandType = CommandType.StoredProcedure;

            cmd.CommandText = "sp_MS_replication_installed";

            cmd.Parameters.Add("Result", SqlDbType.Int).Direction 

                = ParameterDirection.ReturnValue;

            cmd.ExecuteNonQuery();

            result = ((int)cmd.Parameters["Result"].Value) == 0;

        }

        conn.Close();

    }

    return result;

}

View Plain

I took a look at the sp_MS_replication_installed stored procedure and found that all it is doing is calling xp_instance_regread and doing error handling. Since I didn’t like the error handling that sp_MS_replication_installed was doing, I figured I would call xp_instance_regread myself and do my own error handling.

private static bool replicationInstalled(SqlConnection conn) {
    bool result = false;
    using (SqlCommand cmd = new SqlCommand()) {
        cmd.Connection = conn;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "xp_instance_regread";
        cmd.Parameters.AddWithValue("param1", "HKEY_LOCAL_MACHINE");
        cmd.Parameters.AddWithValue("param2", "SOFTWARE\\Microsoft\\MSSQLServer\\Replication");
        cmd.Parameters.AddWithValue("param3", "IsInstalled");
        cmd.Parameters.AddWithValue("@IsInstalled", 0);
        cmd.Parameters["@IsInstalled"].Direction = ParameterDirection.Output;
        cmd.ExecuteNonQuery();
        object returnValue = cmd.Parameters["@IsInstalled"].Value;
        if (returnValue != null && returnValue is int) {
            result = ((int)returnValue) == 1;
        }
    }
    return result;
}

View Plain

I changed the replicationInstalled function to call xp_instance_regread directly. If the registry key exists, the @IsInstalled parameter returns the value from the registry. That value tells whether the replication components are installed. If there is no value in the registry, the @IsInstalled parameter returns a null. I take the value of @IsInstalled and check to see if it is null. If it is null, I return false. If it is not null and it is indeed an integer, I return that integer.

Using the new replicationInstalled function my program is able to quickly check if the replication components are installed before attempting to do any synchronization and without throwing any exceptions. Mission accomplished!

Presenting Detail Values as Part of the Master – part 3

Bill — Fri, 04 Sep 2009 15:39:22 -0500

This is part three of a three part series on working with master-detail data. In part one I showed how to present detail values on the master using a utility I wrote called the SubPropertyAccessor. In part two I showed how the SubPropertyAccessor works. In this part, I introduce a descendant of DataGridViewColumn that will allow us to show the indexed detail data in line with the master data as additional columns. I also introduce a descendent of TypeDescriptionProvider that allows the DataGridView to see the SubProperty as a proper property so that the rest of the grid functionality will still work.

Note: The SubPropertyAccessor was called SubAttributeAccessor in parts one and two.

SubPropertyColumn

The whole point of having detail values appear as part of a master record is to show the data in a grid where the detail values would appear in columns next to the other properties in the record. In order to do this I created a descendant of DataGridViewColumn called SubPropertyColumn. Actually, SubPropertyColumn is a descendant of DataGridViewTextBoxColumn. The DataGridViewTextBoxColumn has most of the functionality I want. I only need to have the data marshaled in and out of the data bound item in a specific way.

The first thing we need is to know where the SubPropertyAccessor is on the data bound item. This will be a property on the column that can be set at design time in the DataGridView designer.

       72:         /// <summary>

        /// The name of the property that is the indexed property.

        /// Note that for sorting to work this

        /// should be set to 'Properties'

        /// </summary>

        [Category("Data"),

        DefaultValue("Properties"),

        Description("The name of the property that is the indexed property.")]

        public string IndexedPropertyName {

            get { return fIndexedPropertyName; }

            set {

                fIndexedPropertyName = value;

            }

        }

View Plain

Now that we know where to find the SubPropertyAccessor, we need to know which property we are looking for. This is the index that we will pass into the SubPropertyAccessor. We need another string property.

 114:         /// <summary>

 115:         /// The index to pass to the indexed property to get the value for this column.

 116:         /// </summary>

 117:         [Category("Data"),

 118:         DefaultValue(""),

 119:         Description("The index to pass to the indexed property to get the value for this column.")]

 120:         public string PropertyIndex {

 121:             get {

 122:                 return base.DataPropertyName;

 123:             }

 124:             set {

 125:                 base.DataPropertyName = value;

 126:             }

 127:         }

View Plain

This property doesn’t have any backing data of its own. It is using the DataPropertyName of the base DataGridViewTextBoxColumn. We do this because the base DataGridColumn bases a lot of functionality on the DataPropertyName property. For example, if this property is blank the base DataGridColumn doesn’t consider the column to be a data bound column. If we are going to make use of the functionality provided by the base classes, we going to have to make them happy. This may seem like a kludge, but it’s better than having to reinvent the DataGridColumn.

Since we’re re-purposing the DataPropertyName property, let’s go ahead and hide it from the designer so it’s not confusing.

  57:         /// <summary>

  58:         /// We're calling this property PropertyIndex instead, so we'll hide this from the Property Viewer.

  59:         /// </summary>

  60:         [EditorBrowsable(EditorBrowsableState.Never),

  61:         Browsable(false),

  62:         DesignerSerializationVisibility(DesignerSerializationVisibility.Hidden), Obsolete("Use PropertyIndex instead")]

  63:         new public string DataPropertyName {

  64:             get {

  65:                 return base.DataPropertyName;

  66:             }

  67:             set {

  68:                 base.DataPropertyName = value;

  69:             }

  70:         }

View Plain

There’s one more piece if information that will make things much easier. That is the type of the data that will be shown in the column. Since the property from where we are getting the doesn’t really exist, we’ll make a property here and set it at design time.

 143:         /// <summary>

 144:         /// Specifies the type of the value that is contained in the data.

 145:         /// In order to allow the ValueType property to be set in the Visual Studio designer

 146:         /// This property is hidden and presented as a string.

 147:         /// </summary>

 148:         [Category("Data"),

 149:         DefaultValue("System.String"),

 150:         Description("Specifies the type of the value that is contained in the data.")]

 151:         new public string ValueType {

 152:             get { return fValueTypeString; }

 153:             set {

 154:                 if (fValueTypeString != value) {

 155:                     fValueTypeString = value;

 156:                     fValueType = null;

 157:                 }

 158:             }

 159:         }

View Plain

I named the property ValueType even though there already is a ValueType property on the base class. I hide that property and replace it with this string property. I made this a string property to avoid having to make a property designer that offered a list of types. This does put a burden on the user to type in the name of an actual type. The property designer would be an obvious improvement.

I also added an internal property called InternalValueType that converts the string representation of the type to an actual Type.

  87:         /// <summary>

  88:         /// In order to avoid having to make a property designer for the Type type

  89:         /// The ValueType property is presented as a string which is easily manipulated

  90:         /// in the Visual Studio Properties Window.

  91:         /// </summary>

  92:         internal Type InternalValueType {

  93:             get {

94:

  95:                 if (fValueType == null) {

  96:                     fValueType = GetType("", fValueTypeString);

  97:                 }

98:

  99:                 return fValueType;

 100:             }

 101:         }

View Plain

The actual work of converting the string into a Type is done in a method called GetType which can be seen in the attached source code.

SubPropertyCell

The rest of the work of marshaling the data in and out of the SubPropertyAccessor is done at the cell level in a class called SubPropertyCell. SubPropertyCell is derived from DataGridViewTextBoxCell and gets most of its functionality from there. There are four methods that do all of the work, GetValue, SetValue, GetFormatedValue and ParseFormattedValue. They are all overrides of methods on the base DataGridViewCell. An extra method, getAccessor contains some code that is shared by GetValue and SetValue.

GetValue

The GetValue method is called by the DataGridView when it needs to get a value from the underlying bound data. The GetValue method uses the information set in the Column to get a value out of the SubPropertyAccessor.

 131:         /// <summary>

 132:         /// Gets the value of the cell.

 133:         /// </summary>

 134:         /// <param name="rowIndex">The index of the cell's parent row.</param>

 135:         /// <returns>The value contained in the cell.</returns>

 136:         /// <remarks>It is here that the SubPropertyAccessor is used to get the correct value stored in the sub property.</remarks>

 137:         protected override object GetValue(int rowIndex) {

 138:             object result = null;

 139:             SubPropertyColumn column = (DataGridView.Columns[ColumnIndex] as SubPropertyColumn);

 140:

 141:             //Get the SubPropertyAccessor and the PropertyInfo for the property

 142:             //on it that has the data needed

 143:             object accessorObject;

 144:             PropertyInfo indexedPropertyInfo;

 145:             getAccessor(rowIndex, out accessorObject, out indexedPropertyInfo);

 146:

 147:             //use some reflection to get the value out of the

 148:             if (indexedPropertyInfo != null) {

 149:                 result = indexedPropertyInfo.GetValue(accessorObject,

 150:                     new object[] { column.PropertyIndex });

 151:             }

 152:

 153:            return result ?? (column != null ? column.NullValue : "");

 154:         }

View Plain

The GetValue method first finds the accessor object and gets a PropertyInfo for the property that contains the sub-property data using the getAccessor method. It then uses reflection to get the value out of the accessor object.

The getAccessor method doesn’t look specifically for a SubPropertyAccessor. As long IndexedPropertyName on the subPropertyColumn points to a property that is an object that has a default indexed property that takes a single string parameter as an index it will work. In fact, if the IndexedPropertyName is blank and the data bound object itself has a default indexed property that takes a single string parameter, it will still work.

  73:         /// <summary>

  74:         /// Get the object that contains the sub-property data as well as a

  75:         /// Property info for the indexed property on that object.

  76:         /// </summary>

  77:         /// <param name="rowIndex">The index of the row in the grid from which to get the DataBoundObject. </param>

  78:         /// <param name="accessorObject">The object that contains the sub-property data is returned here.</param>

  79:         /// <param name="indexedPropertyInfo">The PropertyInfo for the property on the accessor object that is the indexed property that contains the sub-property data</param>

  80:         private void getAccessor(int rowIndex, out object accessorObject, out PropertyInfo indexedPropertyInfo) {

  81:             //Set up some local variables to access the row, column and data bound item.

  82:             DataGridViewRow row = DataGridView.Rows[rowIndex];

  83:             Object rowItem = row.DataBoundItem;

  84:             SubPropertyColumn subPropertyColumn = DataGridView.Columns[ColumnIndex] as SubPropertyColumn;

  85:             accessorObject = null;

  86:             indexedPropertyInfo = null;

  87:             if (rowItem != null) {

  88:                 if (subPropertyColumn != null) {

  89:                     //It is possible that the DataBoundItem is actually the SubPropertyAccessor

  90:                     //so we assume that is the case

  91:                     accessorObject = rowItem;

  92:                     Type accessorType = accessorObject.GetType();

93:

  94:                     //It is more likely that the SubPropertyAccessor is contained by the DataBoundItem

  95:                     if (!string.IsNullOrEmpty(subPropertyColumn.IndexedPropertyName)) {

  96:                         //get a PropertyInfo for the IndexedProperty

  97:                         indexedPropertyInfo = accessorType.GetProperty(subPropertyColumn.IndexedPropertyName);

98:

  99:                         accessorObject = indexedPropertyInfo.GetValue(rowItem, null);

 100:                         accessorType = accessorObject.GetType();

 101:                     }

 102:

 103:                     //Find the indexed property that is a default property and takes a single string as the index

 104:                     MemberInfo[] members = accessorType.GetDefaultMembers();

 105:                     if (members.Length > 0) {

 106:                         int memberNdx = 0;

 107:                         indexedPropertyInfo = null;

 108:                         //check each of the members.

 109:                         while (memberNdx < members.Length && indexedPropertyInfo == null) {

 110:                             if (members[memberNdx].MemberType == MemberTypes.Property) {

 111:                                 PropertyInfo propertyinfo = accessorType.GetProperty(members[memberNdx].Name);

 112:                                 ParameterInfo[] indexedParameters = propertyinfo.GetIndexParameters();

 113:                                 if (indexedParameters.Length == 1) {

 114:                                     //The member is a property, it has indexed parameters,

 115:                                     //it has only one indexed parameter and that

 116:                                     //one indexed parameter is of type string

 117:                                     if (indexedParameters[0].ParameterType == typeof(string)) {

 118:                                         indexedPropertyInfo = accessorType.GetProperty(members[memberNdx].Name);

 119:                                     }

 120:                                 }

 121:                             }

 122:                             memberNdx++;

 123:                         }

 124:

 125:                     }

 126:

 127:                 }

 128:             }

 129:         }

View Plain

SetValue

The DataGridView uses the SetValue method when it needs to put a value into the underlying bound data. The SetValue method uses essentially the same code as GetValue to put the data into the sub-property.

 156:         /// <summary>

 157:         /// Changes the value of a cell

 158:         /// </summary>

 159:         /// <param name="rowIndex">The index of the cell's parent row.</param>

 160:         /// <param name="value">The new value of the cell</param>

 161:         /// <returns>A boolean that tells if the value was successfully changed in the underlying data.</returns>

 162:         /// <remarks>It is here that the SubPropertyAccessor is used to put the correct value into the sub property.</remarks>

 163:         protected override bool SetValue(int rowIndex, object value) {

 164:             if (DataGridView == null || DataGridView.RowCount <= 0) {

 165:                 return base.SetValue(rowIndex, value);

 166:             }

 167:

 168:             //Get the SubPropertyAccessor and the PropertyInfo for the property

 169:             //on it that has the data needed

 170:             object accessorObject;

 171:             PropertyInfo indexedPropertyInfo;

 172:             getAccessor(rowIndex, out accessorObject, out indexedPropertyInfo);

 173:

 174:             //Use reflection to get the data out of the SubPropertyAccessor

 175:             if (indexedPropertyInfo != null) {

 176:                 indexedPropertyInfo.SetValue(accessorObject, value,

 177:                     new object[] {(DataGridView.Columns[ColumnIndex] as SubPropertyColumn).PropertyIndex });

 178:                 return true;

 179:             }

 180:

 181:             return base.SetValue(rowIndex, value);

 182:         }

View Plain

GetFormattedValue

The GetFormattedValue method is used by the DataGridView to convert data from the underlying data source into a format that can be presented in the grid. Since the SubPropertyCell is derived from a DataGridTextBoxCell, I took a shortcut and converted everything to a string to be presented in the TextBox that the base DataGridTextBoxCell handles.

  46:         /// <summary>

  47:         /// Gets the value of the cell as formatted for display.

  48:         /// </summary>

  49:         /// <param name="value">The value to be formatted.</param>

  50:         /// <param name="rowIndex">The index of the cell's parent row.</param>

  51:         /// <param name="cellStyle">The  DataGridViewCellStyle in effect for the cell.</param>

  52:         /// <param name="valueTypeConverter">A TypeConverter associated with the value type that provides custom conversion to the formatted value type.</param>

  53:         /// <param name="formattedValueTypeConverter">A TypeConverter associated with the formatted value type that provides custom conversion from the value type.</param>

  54:         /// <param name="context">A bitwise combination of  DataGridViewDataErrorContexts values describing the context in which the formatted value is needed.</param>

  55:         /// <returns>The formatted value of the cell</returns>

  56:         /// <remarks>Because this cell is based on a text box cell, it is assumed that the result should be a string.</remarks>

  57:         protected override object GetFormattedValue(object value, int rowIndex, ref DataGridViewCellStyle cellStyle, TypeConverter valueTypeConverter, TypeConverter formattedValueTypeConverter, DataGridViewDataErrorContexts context) {

  58:             SubPropertyColumn column = DataGridView.Columns[ColumnIndex] as SubPropertyColumn;

  59:             if (column != null) {

  60:                 if (cellStyle.Format != string.Empty) {

  61:                     string format = String.Format("{{0:{0}}}", cellStyle.Format);

  62:                     return string.Format(format, value);

  63:                 }

  64:                 else {

  65:                     return value.ToString();

  66:                 }

  67:             }

  68:             else {

  69:                 return base.GetFormattedValue(value, rowIndex, ref cellStyle, valueTypeConverter, formattedValueTypeConverter, context);

  70:             }

  71:         }

View Plain

ParseFormattedValue

The ParseFormattedValue method is called by the DataGridView to convert data that was displayed in the grid into a format that can be stored in the underlying bound data source. Again, I took a shortcut and assumed that the grid would always be displaying strings. The ParseFormattedValue uses the static Convert.ChangeType to convert strings into the required data type.

  23:         /// <summary>

  24:         /// Takes the value that was in the cell and transforms it into the type as it is stored in the underlying data

  25:         /// </summary>

  26:         /// <param name="formattedValue">The value that was in the cell</param>

  27:         /// <param name="cellStyle">The cell style from the cell</param>

  28:         /// <param name="formattedValueTypeConverter">The type converter for the display value type</param>

  29:         /// <param name="valueTypeConverter">The type converter for the value type></param>

  30:         /// <returns>The value of the cell in the type of as it is stored in the underlying data.</returns>

  31:         /// <remarks>Since this cell is based on a text box cell, it is assumed that the type of the display data is string.</remarks>

  32:         public override object ParseFormattedValue(object formattedValue, DataGridViewCellStyle cellStyle, TypeConverter formattedValueTypeConverter, TypeConverter valueTypeConverter) {

  33:             if (formattedValue is string) {

  34:                 object result = Convert.ChangeType(formattedValue, ValueType);

  35:                 return result;

  36:             }

  37:             else {

  38:                 return base.ParseFormattedValue(formattedValue, cellStyle, formattedValueTypeConverter, valueTypeConverter);

  39:             }

  40:         }

View Plain

There is no error handling here and it is very easy for an end user to cause an exception simply by entering the wrong data type into the grid.

Demo

With what we already have, we can show sub-property values in a grid. Using the same Linq-To-SQL classes from Part 1, we can point a BindingSource at the Song class and point a DataGridView at the DataSource.

In the screen shot of the Edit Columns dialog, we can see that there are five columns defined. The Artist and Title columns are regular TextBoxColumns that get their data directly from the Song table in the database. The Category, Score and Date columns, on the other hand, are SubPropertyColumns. In the picture, the focus is on the Score column and we can see that IndexedPropertyName is set to “Properties” and PropertyIndex is set to “Rating”. This is the equivalent of showing Song.Properties[“Rating”] in a column.

When the program is run, the DataGridView works exactly as expected. Rows and be added, deleted and edited. Any changes to the Artist and Title columns are saved to Song table, but changes to the other columns are saved to the Attributes table. As values are put in the cells in the Category, Score and Date columns, records are added to the Attributes table.

Sorting

What we have so far is great, but if we tried to sort any of the SubProperty columns, they would not sort. Sorting doesn’t work because the default sorting routine built into the DataGridView doesn’t know how to sort the data that we’ve put into the cells.

Since we are dealing with bound data we cannot use programmatic sorting. This would have been the easiest route to take we could call the sort method on the grid and pass in a specialized IComparer class that knows how to access the SubProperty data. Unfortunately, the DataGridView doesn’t allow this when using bound data. If we try it, we’ll get a runtime exception like the one pictured on the left.

Another option would be to put the sorting logic into a specialized collection class. This gets complicated quickly and while it is possible, it limits how we can use our objects by limiting the collections that can can work with. I really want the sorting to work no matter what kind of collection my objects are in. I want this so I can continue to use something like Linq where I can easily call up a collection of my objects.

In parts one and two we’ve been working with a small sample database and some Linq-To-SQL classes. We were able to add the sub property functionality to one of the Linq-To-SQL classes by simply editing the partial class part of the generated class and adding a SubPropertyAccessor to the class as a field. All of the functionality is contained in the SubPropertyAccessor and is added to the business class with a few lines of code. I want this same ease of use for the sorting functionality. The best way to do this is to use a TypeDDescriptionProvider.

SubPropertyTypeDescriptionProvider

A type description provider is what provides the information about a type when reflection is used against that type. If we create our own TypeDescriptionProvider, we can make is seem as though all of our sub-properties are actual properties of our type. If our sub-properties appear as real properties of our type, the DataGridView should have no trouble sorting the column itself.

       1: using System;

       2: using System.ComponentModel;

       3:  

       4: namespace SubProperties {

       5:     /// <summary>

       6:     /// This class will provide a TypeDescriptor for a class that contains a SubPropertyAccessor.

       7:     /// The TypeDescriptor will report a property for each sub property that is available

       8:     /// via the SubPropertyAccessor.

       9:     /// </summary>

    /// <typeparam name="T">The type of the class that owns the SubPropertyAccessor</typeparam>

    /// <typeparam name="A">The type of the class that contains the sub property data and that the accessor uses to get to that data.</typeparam>

    class SubPropertyTypeDescriptionProvider<T, A> : TypeDescriptionProvider where T : ISubProperty<A> {

  

        private static TypeDescriptionProvider defaultTypeProvider = TypeDescriptor.GetProvider(typeof(T));

  

        public SubPropertyTypeDescriptionProvider() : base(defaultTypeProvider) {

        }

  

        /// <summary>

        /// Returns the SubPropertyTypeDescriptor for the given object type.

        /// </summary>

        /// <param name="objectType"></param>

        /// <param name="instance"></param>

        /// <returns></returns>

        public override ICustomTypeDescriptor GetTypeDescriptor(Type objectType, object instance) {

            if (!typeof(T).IsAssignableFrom(objectType)) {

                throw new ArgumentException(string.Format("The objectType argument must be of type {0}", typeof(T).Name), "objectType");

            }

  

            ICustomTypeDescriptor defaultDescriptor = base.GetTypeDescriptor(objectType, instance);

  

            return new SubPropertyTypeDescriptor<T, A>(defaultDescriptor, instance);

        }

  

    }

  

}

View Plain

Our SubPropertyTypeDescriptionProvider doesn’t do anything more than override GetTypeDescriptor to return a SubPropertyTypeDescriptor. It is in SubPropertyTypeDescriptor where the work will be done.

SubPropertyTypeDescriptor

Our SubPropertyTypeDescriptor will need to return the list of actual properties as well as a list of our custom properties.

       1: using System;

       2: using System.Collections.Generic;

       3: using System.Linq;

       4: using System.ComponentModel;

       5: using System.Reflection;

       6:  

       7: namespace SubProperties {

       8:     /// <summary>

       9:     /// a TypeDescriptor for a class that contains a SubPropertyAccessor.

    /// It will report a property for each sub property that is available

    /// via the SubPropertyAccessor.

    /// </summary>

    /// <typeparam name="T">The type of the class that owns the SubPropertyAccessor</typeparam>

    /// <typeparam name="A">The type of the class that contains the sub property data and that the accessor uses to get to that data.</typeparam>

    class SubPropertyTypeDescriptor<T, A> : CustomTypeDescriptor where T : ISubProperty<A> {

  

        private T fInstance;

  

        /// <summary>

        /// A List of PropertyDescriptors for all of the custom properties.

        /// </summary>

        public List<PropertyDescriptor> CustomProperties {

            get {

                List<PropertyDescriptor> result = new List<PropertyDescriptor>();

                result.AddRange(getCustomProrerties(fInstance).Select(f => new CustomPropertyDescriptor<T, A>(f)).Cast<PropertyDescriptor>());

                return result;

            }

        }

  

  

        public SubPropertyTypeDescriptor(ICustomTypeDescriptor parent, object instance) : base(parent) {

            fInstance = (T)instance;

        }

  

        /// <summary>

        /// Returns all of the properties for class T including the custom properties that are actually data stored in the an object of class A

        /// </summary>

        /// <returns>The properties for class T including the custom properties that are actually data stored in the an object of class A</returns>

        public override PropertyDescriptorCollection GetProperties() {

            return new PropertyDescriptorCollection(base.GetProperties().Cast<PropertyDescriptor>().Union(CustomProperties).ToArray());

        }

  

        /// <summary>

        /// Returns all of the properties for class T including the custom properties that are actually data stored in the an object of class A

        /// only the properties with the given attributes are returned.

        /// </summary>

        /// <param name="attributes">Used to filter the results</param>

        /// <returns>The properties for class T including the custom properties that are actually data stored in the an object of class A</returns>

        public override PropertyDescriptorCollection GetProperties(System.Attribute[] attributes) {

            return new PropertyDescriptorCollection(base.GetProperties(attributes).Cast<PropertyDescriptor>().Union(CustomProperties).ToArray());

        }

  

        //This generator will only work on classes that implement the ISubProperty

        //interface which requires all objects of the class to have

        //a property called Properties of type SubPropertyAccessor<A>.

  

        private static IEnumerable<CustomProperty> getCustomProperties(T tee) {

            List<CustomProperty> customProperties = new List<CustomProperty>();

  

            //We may have an actual instance to work with

            //If so, check the Properties property of the object

            if (tee != null) {

                foreach (var propertyName in tee.Properties.PropertyNames) {

                    customProperties.Add(new CustomProperty(propertyName, tee.Properties.PropertyType(propertyName)));

                }

            }

  

            //If we don't have an instance object, use the static CustomProperties

            //property of the SubPropertyAccessor

            else {

                Type aType = typeof(SubPropertyAccessor<A>);

                PropertyInfo propertyInfo = aType.GetProperty("CustomProperties");

                object info = propertyInfo.GetValue(null, null);

                if (info != null && info is Dictionary<string, Type>) {

                    Dictionary<string, Type> properties = info as Dictionary<string, Type>;

                    foreach (var item in properties) {

                        customProperties.Add(new CustomProperty(item.Key, item.Value));

                    }

                }

            }

  

            return customProperties;

        }

  

    }

}

View Plain

The SubPropertyTypeDescriptor overrides the GetProperties method to return a list of properties that includes the properties that normally would be returned as well as a list of custom properties. In order for it to work, it requires that the class we are working with implement the ISubProperty interface.

       1: using System;

       2:  

       3: namespace SubProperties {

       4:     /// <summary>

       5:     /// This interface must be implemented on any class 

       6:     /// that uses the CustomPropertyDescriptor

       7:     /// </summary>

       8:     /// <typeparam name="T"></typeparam>

       9:     interface ISubProperty<T> {

      10:         SubPropertyAccessor<T> Properties { get; }

      11:     }

      12: }

View Plain

The ISubProperty interface requires that the class we are working with have a property that is called “Properties” of the type SubPropertiesAccessor<T>.

When we were working in the Cell and Column overrides, we could allow the SubPropertyAccessor be named anything and it could be specified at design time as a property on the column. The TypeDescriptionProvider will be applied to the business class as an attribute on the class. There is no way to tell the TypeDescriptionProvider what the SubPropertyAccessor will be called.

We can however make our TypeDescriptionProvider a generic class. By doing that, we can specify what classes and interfaces it will work with by putting a constraint on the Type the generic class can work with. Our constraint, which passes down from The TypeDescriptionProvider to the TypeDescriptor requires the ISubProperty interface and that interface tells us that the SubPropertyAccessor is always going to be named “Properties”.

Having this constraint does, as its name implies, put a constraint on our classes, but I couldn’t figure out how to do sorting without it. Also, I think the benefits that the custom TypeDescriptor provide out weigh the cost of the constraint.

SubPropertyAccessor

When the type descriptor has an object instance, it’s easy to see that it accesses the SubPropertyAccessor to figure out what the custom properties are, but how does it do it when there is no instance object?

The answer is in the SubPropertyAccessor itself. It now contains a static Dictionary of all of the custom properties that are created for any particular type. Since the SubPropertyAccessor is a generic class each type that is used with it actually creates a unique type. This means that there will be a single static Dictionary for each unique type.

 174:         /// <summary>

 175:         /// A list of custom properties that are available for type T.  This is a static list that is common to all instances of type T.

 176:         /// </summary>

 177:         public static Dictionary<string, Type> CustomProperties {

 178:             get {

 179:                 return fCustomProperties;

 180:             }

 181:         }

View Plain

The dictionary of custom properties get populated whenever a new instance of a SubPropertyAccessor is created.

  79:         /// <summary>

  80:         /// Constructor for the SubPropertyAccessor

  81:         /// </summary>

  82:         /// <param name="parent">The main object that will be accessed using this accessor.</param>

  83:         /// <param name="propertyName">The property on the parent object that holds a list of child objects.</param>

  84:         /// <param name="propertyName">The property on the child object that will be the key of the sub attributes.</param>

  85:         /// <param name="valueName">The property on the child object that will be the value of the sub attributes.</param>

  86:         public SubPropertyAccessor(object parent,

  87:             string propertyName,

  88:             string propertyName,

  89:             string valueName) {

90:

  91:             fParent = parent;

  92:             fPropertyName = propertyName;

  93:             fKeyPropertyName = propertyName;

  94:             fValuePropertyName = valueName;

95:

...

 142:             foreach (var property in PropertyNames) {

 143:                 if (!CustomProperties.ContainsKey(property)) {

 144:                     CustomProperties.Add(property, PropertyType(property));

 145:                 }

 146:             }

 147:

 148:         }

View Plain

It also gets updated whenever a new custom property is added.

 192:         /// <summary>

 193:         /// This is the indexed property that provides access to

 194:         /// the list of child attributes.

 195:         /// </summary>

 196:         /// <param name="index">The value of the key property on the object to be looked up</param>

 197:         /// <returns>The value of the value property on the object that has the matching key property</returns>

 198:         public object this[string index] {

 199:             get {

...

 256:                 return result;

 257:             }

 258:

 259:             set {

...

 300:                 //Keep the list of custom properties up to date.

 301:                 Type valueType = value.GetType();

 302:                 if (CustomProperties.ContainsKey(index)) {

 303:                     CustomProperties[index] = valueType;

 304:                 }

 305:                 else {

 306:                     CustomProperties.Add(index, valueType);

 307:                 }

 308:

 309:             }

 310:         }

View Plain

Song

Let’s take a look at what our business class looks like now.

       1: namespace SubProperties {

       2:     using System.ComponentModel;

       3:     

       4:     [TypeDescriptionProvider(typeof(SubPropertyTypeDescriptionProvider<Song, Attribute>))]

       5:     partial class Song : ISubProperty<Attribute> {

       6:         //The TypeDescriptionProvider makes the sub properties actually appear as properties of the object.

       7:  

       8:         //This is the SubPropertyAccessor that will provide the sub property functionality 

       9:         private SubPropertyAccessor<Attribute> fProperties = null;

  

        partial void OnLoaded() {

            //In order for the TypeDescriptionProvider to "see" all of the sub properties, the SubPropertyAccessor

            //needs to be created as soon as the data is loaded.

            fProperties = new SubPropertyAccessor<Attribute>(this, "Attributes", "PropertyName", "Value");

        }

  

        /// <summary>

        /// Used to access the SubPropertiesAccessor class which

        /// has an indexed property to access the additional properties.

        /// This property needs to be here to satisfy the ISubProperty interface.

        /// </summary>

        public SubPropertyAccessor<Attribute> Properties {

            get {

                return fProperties;

            }

        }

  

        /// <summary>

        /// This is the default indexed property

        /// It is just a wrapper around the Properties property

        /// that allows us to access the functionality without having

        /// to use the Properties name.

        /// </summary>

        /// <param name="index"></param>

        /// <returns></returns>

        public object this[string index] {

            get {

                return Properties[index];

            }

            set {

                Properties[index] = value;

            }

        }

  

    }

}

View Plain

We’ve had to make a few changes to the class, but they are negligible compared to the functionality we will get.

With the exception of the Song class itself all of the code we have written is generic and can be used over and over by making the changes that we’ve made in the Song class. The changes to the Song class amount to 46 lines and that includes verbose commenting.

One change is the addition of the TypeDescriptionProvider attribute on line 4. This tells the framework to use our SubPropertyTypeDescriptionProvider when using reflection on our Song class.

The Song class now implements the ISubProperty<T> interface as indicated on line 5. Our SubPropertyAccessor was already named “Properties”, so we didn’t have to make any changes there.

Finally, at line 14, we construct the SubPropertyAccessor as soon as the data is loaded. This will populate the static CustomProperties dictionary on the SubPropertyAccessor as soon as possible.

Now when we try to sort our columns, everything should just work.

Demo

Here we can see that the dates are sorted and sorted correctly by date rather than alphabetically. All of this sorting was handled by the grid itself. All we did was provide a way for the grid to learn about our virtual properties.

Bonus – Dynamic Runtime Properties

I did cheat a little. In the constructor for the example form, I pre-loaded the static Dictionary of custom properties on the SubPropertyAccessor with two properties that I knew about beforehand.

   7:         public Form1() {

   8:             SubPropertyAccessor<Attribute>.CustomProperties.Add("Rating", typeof(int));

   9:             SubPropertyAccessor<Attribute>.CustomProperties.Add("Date", typeof(DateTime));

  10:             InitializeComponent();

View Plain

But what happens if we want to add a property at run-time? The same syntax can be used.

I added a few controls to the form that allow me to add a property at run time. A TextBox allows me to specify the name of the property, a dropdown lets me choose the type and a button creates the property.

        private void loadGrid() {

            fContext.SubmitChanges();

            var result = from aSong in fContext.Songs select aSong;

            songBindingSource.DataSource = result;

        }

  

        private void button1_Click(object sender, EventArgs e) {

            Type aType = typeof(string);

  

            if (comboBox1.SelectedItem.ToString() == "Integer") {

                aType = typeof(int);

            }

            else if (comboBox1.SelectedItem.ToString() == "Double") {

                aType = typeof(double);

            }

            else if (comboBox1.SelectedItem.ToString() == "Date") {

                aType = typeof(DateTime);

            }

            

            SubPropertyAccessor<Attribute>.CustomProperties.Add(textBox1.Text, aType);

            dataGridView1.Columns.Add(new SubPropertyColumn { IndexedPropertyName = "Properties", PropertyIndex = textBox1.Text, HeaderText = textBox1.Text, ValueType = aType.FullName });

            loadGrid();

        }

View Plain

Most of the click handler for the button is concerned with getting a Type from the ComboBox. The important stuff happens in lines 44 through 46. On line 44 we add the CustomProperty to the SubPropertyAccessor. Line 45 adds a column to the DataGridView to show the new CustomProperty. Finally, on line 46, the DataGridView is reloaded. This forces the grid to re-run the reflection on the bound data so that it will discover the new “Property” that was added.

Conclusion

There’s a lot of code that goes into presenting child data as part of a master object. Fortunately, most of the code is done in generic classes. This allows us to take advantage of this functionality without having to go through this exercise every time.

The file attached to this article contains all of the source code for all of the classes I presented and the demo program. The code changes significantly from part 1 and part 2. The file attached to this part (part 3) contains all of the functionality discussed in all three parts.

Source Code: SubPropertyColumns.zip (220 KB)