ColumnMappings don't seem to work!

[Guest]

Hi,

I am new to column mappings and can't seem to make them work. The following
is an example:

DataTable table = new DataTable( "SYSTEM.Usernames");
table.Columns.Add( "S1", typeof(decimal) );
OracleCommand cmd = new OracleCommand();
cmd.Connection = this.oracleConnection1;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT SuperUserFlag FROM SYSTEM.Usernames WHERE
Username = 'JuanDent'";
OracleDataAdapter da = new OracleDataAdapter( cmd );
DataSet ds = new DataSet( "Temp ds");
ds.Tables.Add( table );

DataTableMapping map = da.TableMappings.Add( "Table", table.TableName );
map.ColumnMappings.Add( "S1", "SuperUserFlag" );

ds.EnforceConstraints = false;
da.MissingSchemaAction = MissingSchemaAction.Error;
try
{
da.Fill( ds, 0, 200, table.TableName );
}
catch( Exception ex )
{
MessageBox.Show( ex.Message, ex.Source);
}

No matter how I play with it, I can't get the Fill statement to work, it
always asks for a missing column "SuperUserFlag" in the table - I thought
this is precisely what column mappings did for us...

 

[Kevin Yu [MSFT]]

Hi Juan,

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that you can't get TableMappings to work in
your app. If there is any misunderstanding, please feel free to let me know.

Thanks for posting your code here. Based on the code you have provided, I
can see that the source table name is incorrect which make the Fill throws
exception. When filling, we have to fill to the source table and then
TableMappings will map it to the destination table.

Also the source column name and destination column name has to be swapped.

Here I made some changes to your code, and it works fine on my machine.

DataTable table = new DataTable( "SYSTEM.Usernames");
table.Columns.Add( "S1", typeof(decimal) );
OracleCommand cmd = new OracleCommand();
cmd.Connection = this.oracleConnection1;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT SuperUserFlag FROM SYSTEM.Usernames WHERE
Username = 'JuanDent'";
OracleDataAdapter da = new OracleDataAdapter( cmd );
DataSet ds = new DataSet( "Temp ds");
ds.Tables.Add( table );

DataTableMapping map = da.TableMappings.Add( "Table", table.TableName );
map.ColumnMappings.Add( "SuperUserFlag", "S1" );

ds.EnforceConstraints = false;
da.MissingSchemaAction = MissingSchemaAction.Error;
try
{
da.Fill( ds, 0, 200, "Table");
}
catch( Exception ex )
{
MessageBox.Show( ex.Message, ex.Source);
}

HTH.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Guest]

Thanks. However, I have changed all you have suggested and I still get a
missing column with name "SuperUserFlag"...

I am puzzled...

What is the purpose of DataMapping then? is it incapable of mapping a column
with one name in the SELECT statement to a column with another name in the
DataTable given to the DataSet which is then given to the DataAdapter?

 

[Kevin Yu [MSFT]]

Hi Juan,

DataMapping is used to put the selected data to the desired column in your
DataSet. I tried this code snippet on my machine and it works fine.
map.ColumnMappings.Add( "SuperUserFlag", "S1" ); is used to map
SuperUserFlag column to S1. Could you please check it again?

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."