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();
}
}
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')
Can you please help me on this?
Kind regards,
Pedro