Error TypedDataSet and OracleReader

[degas]

I have the following code that does not work:

Code: Select all

            OracleCommand cmd = new OracleCommand("SELECT TO_MAIL, TO_DISPLAY_NAME , ID_MAIL, ID_MAIL_TO, ENVIADO, REINTENTAR, ID_CONTACTO FROM SISTEMA.MAIL_TO");
            cmd.Connection = conn.GetOracleConnection();
            cmd.Connection.Open();
            var reader = cmd.ExecuteReader();          
            this.sistema.MailTo.Load(reader);
            cmd.Connection.Close();
            return sistema.MailTo;

and this one that does work

Code: Select all

            OracleCommand cmd = new OracleCommand("SELECT TO_MAIL TOMAIL, TO_DISPLAY_NAME TODISPLAYNAME , ID_MAIL IDMAIL, ID_MAIL_TO IDMAILTO, ENVIADO, REINTENTAR, ID_CONTACTO IDCONTACTO FROM SISTEMA.MAIL_TO");
            cmd.Connection = conn.GetOracleConnection();
            cmd.Connection.Open();
            var reader = cmd.ExecuteReader();          
            this.sistema.MailTo.Load(reader);
            cmd.Connection.Close();
            return sistema.MailTo;

the object sistema is a TypedDataSet and MalTo is a TypedDataTable. The problem is that the OracleReader is not using the fieldName in the DataTable and is instead using the FieldNames (wich do not mach the datatable)

[Shalex]

Does behaviour of ODP.NET and System.Data.OracleClient defer in this scenario?

Any DataReader (and OracleDataReader) doesn't rename columns which are returned from database. If your query is "SELECT 1 AS N, 2 AS N, 3 AS N, 4 AS N FROM DUAL", you will get four columns with the "N" name each. If you execute "SELECT 1 AS N1, 2 AS N2, 3 AS N3, 4 AS N4 FROM DUAL", every column will have a unique name (N1,N2,...).

But columns added to DataTable are renamed to keep them unique. DataTableMapping's DataColumnMapping contains a real mapping between database columns and DataTable columns.

If this information does not help, please send us a small test project with the corresponding DDL/DML script to reproduce the issue in our environment.

[degas]

The problem occurs when creating a TypedDataSet with a DataTable. If the i rename the dataTable columns, then if i want to use a dataReader i have to match the column's name in the SqlCommand to the name of the columns in the DataTable.

If i do not rename the columns in the datatable (so the columns name mach) i can use the data reader without renaming the datacolumns.
In other words i can use

Code: Select all

SELECT * FROM TABLE WHERW X=:Y

instead

Code: Select all

SELECT COLUMN_1 as COLUMN1, COLUMN_2 as COLUMN2 FROM TABLE WHERW X=:Y

where COLUMN_1 is the name in the Oracle Table and COLUMN1 is the name in the OracleDataTable in the OracleDataSet

[Shalex]

This is a designed behaviour. OracleDataReader retrieves columns names as is in database. If columns in OracleDataTable are renamed, you will get the columns names mismatch as expected. As a solution, please use your approach ("SELECT COLUMN_1 as COLUMN1, ...").