Returning TableSchema(System.Data.CommandBehavior.KeyInfo)

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
pedro.castelo
Posts: 4
Joined: Tue 24 Jun 2008 12:31

Returning TableSchema(System.Data.CommandBehavior.KeyInfo)

Post by pedro.castelo » Thu 11 Mar 2010 10:44

Hi,
I'm using dotOracle version 5.60.102.0.
I have to create a datatable which reflects a database table and I'm using the following code:

Code: Select all

using (System.Data.Common.DbConnection connection = Manager.DBFactory.CreateConnection())
{
    connection.ConnectionString = Manager.ConnectionString;
    using (System.Data.Common.DbCommand selectCommand = Manager.DBFactory.CreateCommand())
    {
        selectCommand.Connection = connection;
        connection.Open();
        selectCommand.CommandText = string.Format("SELECT * FROM {0} WHERE 1=0", table);
        System.Data.Common.DbDataReader reader = selectCommand.ExecuteReader(System.Data.CommandBehavior.KeyInfo);
        //Then we get the reader tableSchema to update some infos not automatically retrieved
        System.Data.DataTable schemaTable = reader.GetSchemaTable();
        //Loading the datatable, to fetch the structure
        dataTable.Load(reader);   
        //Storing some numeric relevat data to the extendend properties for later usage
        foreach (System.Data.DataRow column in schemaTable.Rows)
        {   //For more infos about the SchemaTable
            //http://msdn.microsoft.com/en-us/library/system.data.datatablereader.getschematable.aspx
            //NumericPrecision: How many significant digits(left side of decimal deparator) EX: Number(18,2) should be 18
            //NumericScale: How many decimal digits EX: Number(18,2) should be 2
            //ColumnSize: How big is the column
            if(dataTable.Columns.Contains(column["ColumnName"].ToString()))
            {
                System.Data.DataColumn dataColumn = dataTable.Columns[column["ColumnName"].ToString()];
                dataColumn.ExtendedProperties.Add("NumericPrecision", column["NumericPrecision"]);
                dataColumn.ExtendedProperties.Add("NumericScale", column["NumericScale"]);
                dataColumn.ExtendedProperties.Add("ColumnSize", column["ColumnSize"]);
            }                            
        }
        reader.Close();
        connection.Close();
    }
}
The problem is that, it is creating a constraint for each column of a a compound unique key(Not primary key!). For example if I have a compound Unique key with fields A and B, instead of create a datatable constraint with A and B, it's creating two contraints: One for A and One for B.

I further investigated and I noticed that the "IsUnique" column on the table returned by GetSchemaTable() is true for both fields involved. The problem is that there's no way to know by the information that is coming if it's a compound key or not.

On the other hand I used dbMonitor to monitor the comands being executed on the database. Here it is:

Code: Select all

select 0 as typ,0 as table_id,column_name,column_id,' ' as constraint_type, owner
from all_tab_columns
where owner = SYS_CONTEXT ('USERENV', 'CURRENT_SCHEMA') and table_name = 'DICFIELD'
union all
select 0 as typ,0 as table_id,cc.column_name,0 as column_id,cs.constraint_type || ':' || cs.constraint_name, cc.owner as owner
from all_constraints cs,all_cons_columns cc
where cc.owner = SYS_CONTEXT ('USERENV', 'CURRENT_SCHEMA') and cc.table_name = 'DICFIELD' and
cs.owner = cc.owner and cs.table_name = cc.table_name and cs.constraint_name = cc.constraint_name and cs.constraint_type in ('P','U')
According to the results of this statement I can see that the constraintname is being taken(cs.constraint_type || ':' || cs.constraint_name), which would allow dotOracle to know if the constraint has multiple columns or not, and the create only one contsraint instead of multiple.

Can you please help me on this?

Kind regards,

Pedro

StanislavK
Devart Team
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Post by StanislavK » Thu 11 Mar 2010 17:06

Using the reader.GetSchemaTable method you can obtain metadata for single columns only. You may retrieve the information about the compound unique key from OracleDataTable generated in the following way:

Code: Select all

OracleDataTable dt = new OracleDataTable(selectCommand, connection);
In this case the dt.Constraints collection will contain the needed unique constraint.

Feel free to contact us if something is unclear.

bernhard.klefer
Posts: 5
Joined: Wed 24 Nov 2010 14:52

Post by bernhard.klefer » Wed 24 Nov 2010 17:07

Hi,

I've just tried the Express edition of dotConnect for Oracle 5.70.190.0. migrating over from Oracle's 11gR2 ODP.net for .NET4.
I'm making heavy use of .NET's abstract DbConnection, DbCommand,... and DataTable classes so i would prefer to stick with them.

I think the OracleDataReader.GetSchemaTable() behaves incorrect when returning IsUnique-Flags flags for Columns in multi-column-unique-constraint.

Microsoft's System.Data.OracleClient (.NET4) and Oracle's ODP.NET (11gR2 .NET4) both don't return IsUnique-Flags for multi-column-unique-constraints.

Example:

Code: Select all

 CONSTRAINT "UN_IMAGE_NAMESIZE" UNIQUE ("IMAGENAME", "IMAGESIZE")
meaning "not two images with same name and size".
To interprete the constraint per column would result in "only one size per image and each image has to have a distinct size"..


Returning IsUnique for these columns breaks the abstract System.Data.Common layer which interpretes the flags as individual unique-constraints on each columns.
For example the DbDataAdapter.Fill()-method creates these wrong constraints in the datatables it fills which will most likely result in an ConstraintException. See also http://www.devart.com/forums/viewtopic.php?t=11995.


using OracleDataTable would be some kind of workaround (if it was included in the express edition) but not a fix for the basic issue.

I hope you can understand my problem and provide a fix.

Thanks for your help.
With kind regards,
Bernhard

StanislavK
Devart Team
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Post by StanislavK » Thu 25 Nov 2010 12:54

We will analyze the possibility of supporting such behaviour, and inform you about the results here.

As a workaround, you can use the OracleDataTable component with the same select command and connection.

StanislavK
Devart Team
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Post by StanislavK » Thu 10 Feb 2011 17:39

We have changed this behaviour, now the IsUnique property for columns that form composite unique keys is set to false in the schema table. This change is available in the new 6.10.103 build of dotConnect for Oracle. This build can be downloaded from
http://www.devart.com/dotconnect/oracle/download.html
(the trial version) or from Registered Users' Area (for users with active subscription only):
http://secure.devart.com/

For the detailed information about the fixes and improvements available in dotConnect for Oracle 6.10.103, please refer to
http://www.devart.com/forums/viewtopic.php?t=20224

Post Reply