How to add missing primary key in datatable (oracle db)

See more:

C#

Oracle

database

We have two tables (formula and item) both table contains primary key (formula = Formula_Id and item = item_code).
joined the table to get the result from both the tables.
We are using disconnected approach to fetch the data from oracle to Dotnet datable.
Code is working fine with sqlserver database. it adds the formula_id column as primary key.
(which we want)


When we execute it against oracle. Datatable primary key length becomes zero, means there is no primary key columns in datatable.
Code logic is same and select statement is also same, there is no difference but for sql server it is adding primary key.


OracleDataAdapter adds the primary key columns if only one table is used in select statement.
If we join the table or add query within query, then it does not add any parimary key column in datatable.

What I have tried:

If I set the PRIMARY_IND column default value “0” then it adds FORMULA_ID column as primary key in the data table.

SELECT  FORMULA_ID,  FORMULA_CODE, VERSION,  DESCRIPTION,  UOM_CODE,  YIELD, YIELD_PCT, DOC_ID, TYPE_IND, PROCESS_YIELD,  OWNER_CODE,  OWNER_SECURITY,  GROUP_CODE,  GROUP_SECURITY ,  ROLE_SECURITY,  STATUS_IND, FORMULATOR_CODE, YIELDCALC_IND,  CLASS,  LOGICAL_DELETE, ADJUST_PARAM,ROLLUP_ID,CALC_MODE,  ITEM_CODE, 0 AS PRIMARY_IND  FROM FORMULA F WHERE FORMULA_CODE = [%1] AND VERSION=[%2]'

If I set expression as below, then it adds ITEM_CODE column as primary key in the data table.

SELECT  FORMULA_ID,  FORMULA_CODE, VERSION,  DESCRIPTION,  UOM_CODE,  YIELD, YIELD_PCT, DOC_ID, TYPE_IND, PROCESS_YIELD,  OWNER_CODE,  OWNER_SECURITY,  GROUP_CODE,  GROUP_SECURITY ,  ROLE_SECURITY,  STATUS_IND, FORMULATOR_CODE, YIELDCALC_IND,  CLASS,  LOGICAL_DELETE, ADJUST_PARAM,ROLLUP_ID,CALC_MODE,(SELECT COUNT(*) FROM ITEM I WHERE I.ITEM_CODE = F.ITEM_CODE AND I.FORMULA_ID = F.FORMULA_ID) AS PRIMARY_IND ITEM_CODE FROM FORMULA F WHERE FORMULA_CODE = [%1] AND VERSION=[%2]

both queries are working fine and adding formula_id columns as primary key in datatable if we execute against sql-server.

It is not working for oracle.