Error TypedDataSet and OracleReader

Error TypedDataSet and OracleReader

Postby degas » Tue 03 May 2011 17:34

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)
degas
 
Posts: 77
Joined: Mon 16 Feb 2009 18:36
Location: Argentina

Postby Shalex » Wed 04 May 2011 15:28

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.
Shalex
Devart Team
 
Posts: 7608
Joined: Thu 14 Aug 2008 12:44

Postby degas » Wed 04 May 2011 15:47

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
degas
 
Posts: 77
Joined: Mon 16 Feb 2009 18:36
Location: Argentina

Postby Shalex » Fri 06 May 2011 13:02

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, ...").
Shalex
Devart Team
 
Posts: 7608
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for Oracle