Even though the TableMappings collection provides a strong mechanism for mapping tables and columns in the database to table and columns in the DataSet, there may be times that you want to dynamically change this mapping at runtime. To support this behavior there is the SchemaMappingEvent. At the point where the DataSetCommand builds its internal structure defining what the result of the mappings are, we provide access to the those internal mapping structures.
As an example imagine a stored procedure that returns a column without a name by performing the following select:
SELECT @@IDENTITY
This would normally cause an error while trying to load the result into the DataSet, since the DataSet requires all columns to have a name. Since it doesn't have a name, how do I map it to a column within a DataTable?
The examples below works against the SQLSchemaMappingEvent to change the incoming column name from an empty value to "someuniquevalue".
In this case, the event is actually working against the SQLDataSetCommand. If this were being generated by the ADODataSetCommand, you would use the ADOSchemaMappingEvent declaration.
[VB]
Private Sub SchemaMappingEventHolder(ByVal sender As Object, ByVal e As _ SQLSchemaMappingEvent) Dim column As DataColumn = e.SchemaTable.Columns("DBCOLUMN_NAME") Dim row As DataRow For Each row in e.SchemaTable.Rows.All If row.RowState = DataRowState.Deleted Then row.RejectChanges() End If If row.IsNull(column) Or 0 = row(column).ToString Then column = e.SchemaTable.Columns("DataColumn") row(column) = New DataColumn("SomeUniqueName") End If Next End Sub
[C#]
private void SchemaMappingEventHandler(object sender, SQLSchemaMappingEvent e) { DataColumn column = e.SchemaTable.Columns["DBCOLUMN_NAME"]; foreach(DataRow row in e.SchemaTable.Rows.All) { if(DataRowState.Deleted == row.State) { row.RejectChanges(); } if (row.IsNull(column) || 0 == row[column].ToString()) { // unnamed column column = e.SchemaTable.Columns["DataColumn"]; row[column] = new DataColumn("SomeUniqueName"); } } }