Further Development

The simple component that was used lacks some of the other features you may require. For example, components can use runtime connections or have properties. These would generally be represented through additional form controls, and their values would be interrogated and controls initialized in the form constructor. You will now look at these other methods in greater detail.

Runtime Connections

As previously discussed, components can use connections, and the System.IServiceProvider from IDtsComponentUI.Initialize and the Connections collection from IDtsComponentUI.Edit allow you to provide meaningful UI functions around them. Examples have been given of passing these as far as the form constructor, so now you will be shown what you then do with them. This example shows a modified constructor that accepts the additional connection-related parameters, performs some basic initialization, and stores them for later use. You would perform any column-or property-related work as shown in the previous examples, but for clarity none is included here. The final task is to initialize the connection-related control.

For this example, you will presume that the component accepts one connection, which would have been defined in the ProvidedComponentProperties method of the component. You will use a ComboBox control to offer the selection options, as well as the ability to create a new connection through the IDtsConnectionService. The component expects an ADO.Net SqlClient connection, so the list will be restricted to this, and the current connection, if any, will be preselected in the list. The preparatory work for this is all shown here:

 private IDTSComponentMetaData90 _dtsComponentMetaData; private CManagedComponentWrapper _designTimeComponent; private IDtsConnectionService _dtsConnectionService; private Microsoft.SqlServer.Dts.Runtime.Connections _connections; // Constant to define the type of connection we support and wish to work with. private const string Connection_Type = "ADO.NET:System.Data.SqlClient.SqlConnection, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"; public ConnectionDemoUIForm(IDTSComponentMetaData90 dtsComponentMetaData, IServiceProvider serviceProvider) {    InitializeComponent();    // Store constructor parameters for later.    _dtsComponentMetaData = dtsComponentMetaData;    _connections = connections;    // Get IDtsConnectionService and store.    IDtsConnectionService dtsConnectionService = serviceProvider.GetService(typeof(IDtsConnectionService)) as IDtsConnectionService;    _dtsConnectionService = dtsConnectionService;    // Get design-time interface for changes and validation.    _designTimeComponent = _dtsComponentMetaData.Instantiate();    // Perform any other actions, such as column population or    // component property work.    // Get Connections collection, and get name of currently selected connection.    string connectionName = "";    if (_dtsComponentMetaData.RuntimeConnectionCollection[0] != null)    {       IDTSRuntimeConnection90 runtimeConnection =             _dtsComponentMetaData.RuntimeConnectionCollection[0];       if (runtimeConnection != null          && runtimeConnection.ConnectionManagerID.Length > 0          && _connections.Contains(runtimeConnection.ConnectionManagerID))       {          connectionName = _connections[runtimeConnection.ConnectionManagerID].Name;       }    }    // Populate connections combo.    PopulateConnectionsCombo(this.cmbSqlConnections, Connection_Type,       connectionName); } 

The final command in the constructor is to call your helper function, PopulateConnectionsCombo, to populate the combo box. The parameters for this are quite simple: the combo box to populate, the type of connection you wish to list, and the name of the currently selected connection. Using these three items, you can successfully populate the combo as shown here:

 private void PopulateConnectionsCombo(ComboBox comboBox,    string connectionType, string selectedItem) {    // Prepare combo box by clearing, and adding the new connection item.    comboBox.Items.Clear();    comboBox.Items.Add("<New connection...>");    // Enumerate connections, but for type supported.    foreach (ConnectionManager connectionManager in       _dtsConnectionService.GetConnectionsOfType(connectionType))    {       comboBox.Items.Add(connectionManager.Name);    }    // Set currently selected connection    comboBox.SelectedItem = selectedItem; } 

The ADO.Net connection is slightly different from most connections in that it has what can be thought of as subtypes. Because you need a specific subtype, the System.Data.SqlClient.SqlConnection, you need to use the full name of the connection, as opposed to the shorter creation name moniker, ADO.NET, which you may see elsewhere and which is the pattern used for other simpler types of Connection Managers.

Now that you have populated the combo box, you need to handle the selection of an existing connection or the creation of a new connection. When you author a Connection Manager yourself, you can provide a user interface by implementing the IDtsConnectionManagerUI, which is analogous to the way you have implemented IDtsComponentUI to provide a user interface for your component. The connection service will then display this user interface when you call the CreateConnection method.

The following example is the event handler for the connections combo box, which supports new connections and existing connections and ensures that the selection is passed down to the component:

 private void cmbSqlConnections_SelectedValueChanged(object sender, EventArgs e) {    ComboBox comboxBox = (ComboBox)sender;    // Check for index 0 and <New Item...>    if (comboxBox.SelectedIndex == 0)    {       // Use connection service to create a new connection.       ArrayList newConns = _dtsConnectionService.CreateConnection(Connection_Type);       if (newConns.Count > 0)       {          // A new connection has been created, so populate and select          ConnectionManager newConn = (ConnectionManager)newConns[0];          PopulateConnectionsCombo(comboxBox, Connection_Type, newConn.Name);       }       else       {          // Create connection has been cancelled          comboxBox.SelectedIndex = -1;       }    }    // An connection has been selected. Verify it exists and update component.    if (_connections.Contains(comboxBox.Text))    {       // Get the selected connection       ConnectionManager connectionManager = _connections[comboxBox.Text];       // Save selected connection       _dtsComponentMetaData.RuntimeConnectionCollection[0].ConnectionManagerID =          _connections[comboxBox.Text].ID;       _dtsComponentMetaData.RuntimeConnectionCollection[0].ConnectionManager =          DtsConvert.ToConnectionManager90(_connections[comboxBox.Text]);    } } 

By following the examples shown here, you can manage connections from within your user interface, allowing the user to create a new connection or select an existing one, and ensure that the selection is persisted through to the component's RuntimeConnectionCollection, thereby setting the connection.

You can also use variables within your UI. Normally the selected variable is stored in a component property, so by combining the property access code from the Component Properties section and following the pattern for Runtime Connections, substituting the IDtsVariableService instead, you can see how this can be done.

Component Properties

As an example of displaying and setting component-level properties, you may have a string property that is displayed in a simple text box control and an enumeration value that is used to set the selected index for a combo box control. The following example assumes that the two component properties, StringProp and EnumProp, have been defined in the overridden ProvideComponentProperties method of your component class. You would then extend the form constructor to include some code to retrieve the property values and display them in the form controls. This assumes that you have added two new form controls, a TextBox control called MyStringTextBox, and a ComboBox called MyEnumValComboBox. An example of the additional form constructor code is shown here:

 MyStringTextBox.Text = _dtsComponentMetaData.CustomPropertyCollection["StringProp"].Value.ToString(); MyEnumValComboBox.SelectedIndex = Convert.ToInt32(_dtsComponentMetaData.CustomPropertyCollection["EnumProp"].Value); 

The appropriate events for each control would then be used to set the property value of the component, ensuring that this is done through the design-time interface. A variety of events could be used to capture the value change within the Windows Form control, and this may depend on the level of validation you wish to apply within the form, or if you wish to rely solely on validation routines within an overridden SetComponentProperty method in your component class. Capturing these within the control's validating event would then allow you to cancel the change in the form, as well as displaying information to the user. A simple example is shown here for the two properties:

 private void MyStringTextBox_Validating(object sender, CancelEventArgs e) {     // Set the property, and capture any validation errors     // thrown in SetComponentProperty     try     {       _designTimeComponent.SetComponentProperty("StringProp", MyStringTextBox.Text);    }    catch(Exception ex)    {       // Display exception message       MessageBox.Show(ex.Message);       // Cancel event due to error       e.Cancel = true; } private void MyEnumValComboBox_SelectedIndexChanged(object sender, EventArgs e) {    try    {       _designTimeComponent.SetComponentProperty("EnumProp ",          ((ComboBox)sender).SelectedIndex);    }    catch(Exception ex)    {       // Display exception message       MessageBox.Show(ex.Message);       // Cancel event due to error       e.Cancel = true;    } } 

Providing an overridden SetComponentProperty is a common requirement. The most obvious reason is that component properties are stored through the object type, but you may require a specific type, such as integer, so the type validation code would be included in SetComponentProperty. A simple example of this is shown here, where the property named IntProp is validated to ensure that it is an integer:

 public override IDTSCustomProperty90 SetComponentProperty(string propertyName, object propertyValue) {    int result;    if (propertyName == "IntProp" &&       int.TryParse(propertyValue.ToString(), out result) == false)    {       bool cancel;       ComponentMetaData.FireError(0, ComponentMetaData.Name, "The IntProp property           is required to be a valid integer.", "", 0, out cancel);       throw new ArgumentException("The value you have specified for IntProp is not           a numeric value");    }     return base.SetComponentProperty(propertyName, propertyValue); } 

You will build on this example and learn how to handle the exceptions and events in the following section, "Handling Errors and Warnings."

Handling Errors and Warnings

The previous example and the column selection method in the main example both demonstrated how you can catch exceptions thrown from the base component when you apply settings. Although it is recommended that you use managed exceptions for this type of validation and feedback, you may also wish to use the component events such as FireError or FireWarning. Usually, these would be called immediately prior to the exception and used to provide additional information in support of the exception. Alternatively you could use them to provide the detail and only throw the exception as a means of indicating that an event has been raised. To capture the event information, you can use the IErrorCollectionService. This service can be obtained through System.IServiceProvider, and the preparatory handling is identical to that of IDtsConnectionService as illustrated in the previous example. For the following examples, you will assume that a class-level variable containing the IErrorCollectionService has been declared, _errorCollectionService, and populated through in the form constructor.

The following example demonstrates how we can use the GetErrorMessage method of the IErrorCollectionsrevice to retrieve details of an event. This will also include details of any exception thrown as well. The validating method of a text box control is illustrated, and SetComponentProperty is based on the overridden example shown previously, to validate the property value is an integer:

 private void txtIntPropMessage_Validating(object sender, CancelEventArgs e) {    // Clear any existing errors in preparation for setting property    _errorCollectionService.ClearErrors();    try    {       // Set property through CManagedComponentWrapper       _designTimeComponent.SetComponentProperty("IntProp",          this.txtIntPropMessage.Text);    }    catch    {       // Display message       MessageBox.Show(_errorCollectionService.GetErrorMessage());    // Cancel event due to error    e.Cancel = true;    } } 

If a non-integer value is entered, the following message is displayed:

 Error at Data Flow Task [ReverseString]: The IntProp property is required to be a valid integer. Error at Data Flow Task [ReverseString [84]]: System.ArgumentException: The value you have specified for IntProp is not a numeric value    at Konesans.Dts.Pipeline.ReverseString.ReverseString.SetComponentProperty(String propertyName, Object propertyValue)    at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostSetComponentProperty(IDTS ManagedComponentWrapper90 wrapper, String propertyName, Object propertyValue) 

This second example demonstrates the GetErrors method and how to enumerate through the errors captured by the service individually:

 private void txtIntPropErrors_Validating(object sender, CancelEventArgs e) {    // Clear any existing errors in preparation for setting property    _errorCollectionService.ClearErrors();    try    {       // Set property through CManagedComponentWrapper       _designTimeComponent.SetComponentProperty("IntProp",          this.txtIntPropErrors.Text);    }    catch    {       // Get ICollection of IComponentErrorInfo and cast into       // IList for accessibility       IList<IComponentErrorInfo> errors =          _errorCollectionService.GetErrors() as IList<IComponentErrorInfo>;       // Loop through errors and process into message       string message = "";       for (int i = 0; i < errors.Count; i++)       {          IComponentErrorInfo errorInfo = errors[i] as IComponentErrorInfo;          message += "Level: " + errorInfo.Level.ToString() + Environment.NewLine +             "Description : " + Environment.NewLine + errorInfo.Description             + Environment.NewLine + Environment.NewLine;       }       // Display message       MessageBox.Show(message);       // Cancel event due to error       e.Cancel = true;    } } 

If a non-integer value is entered, the following message is displayed:

 Level: Error Description : The IntProp property is required to be a valid integer. Level: Error Description : System.ArgumentException: The value you have specified for IntProp is not a numeric value    at Konesans.Dts.Pipeline.ReverseString.ReverseString.SetComponentProperty(String propertyName, Object propertyValue)    at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostSetComponentProperty(IDTS ManagedComponentWrapper90 wrapper, String propertyName, Object propertyValue) 

As you can see, both the event and exception information are available through the IErrorCollectionService. You can also see the use of the Level property in this example, which may be useful for differentiating between errors and warnings. For a complete list of IComponentErrorInfo properties, please refer to the SQL Server documentation.

Column Properties

When you require column-level information, beyond the selection state of a column, it is best practice to store this as a custom property on the column. This applies to all column types. An example of this can be seen with the stock Character Map transform. If you select a column and perform an in-place operation, such as the Lowercase operation, this is stored as a custom property on that input column. To confirm this, select a column as described and view the component through the Advanced Editor. If you then navigate to the Input and expand to select the column, you will see a custom property called MapFlags. This stores the operation enumeration, as shown in Figure 15-7.

Figure 15-7

If your component uses custom column properties in this way, these are perhaps the best candidates for a custom user interface. Using the Advanced Editor to navigate columns and set properties correctly carries a much higher risk of error and is more time-consuming for the user than a well-designed user interface. Unfortunately this does raise the complexity of the user interface somewhat, particularly from the Windows Forms programming perspective, as the effective use of form controls is what will determine the success of such a UI. However, if you are still reading this chapter, you will probably be comfortable with such challenges.

To persist these column level properties, simply call the appropriate SetColumnTypeProperty method on the design-time interface, CManagedComponentWrapper. For example, in the following code, a property is being set on an input column:

 _designTimeComponent.SetInputColumnProperty(_input.ID, inputColumn.ID,    "PropertyName", propertyValue);