如何在datatable中添加缺少的主键（oracle db） [英] How to add missing primary key in datatable (oracle db)

问题描述

我们有两个表（公式和项目），两个表都包含主键（formula = Formula_Id和item = item_code）。

加入表以从两个表中获取结果。

我们使用断开连接的方法从oracle获取数据到Dotnet数据。

代码与sqlserver数据库一起工作正常。它将formula_id列添加为主键。

（我们想要的）





当我们执行它时反对oracle。数据表主键长度变为零，表示数据表中没有主键列。

代码逻辑相同且select语句也相同，没有区别，但对于sql server，它是添加主键。





如果select语句中只使用了一个表，OracleDataAdapter会添加主键列。

如果我们加入表或在查询中添加查询，然后它不会在数据表中添加任何parimary键列。



我尝试过：



如果我将PRIMARY_IND列的默认值设置为0，那么它会将FORMULA_ID列添加为数据表中的主键。

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]'

如果我将表达式设置如下，然后将ITEM_CODE列添加为数据表中的主键。

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]

两个查询都正常工作，并且如果我们针对sql-server执行，则将formula_id列添加为datatable中的主键。



它不适用于oracle。

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.

推荐答案

尝试在cols上使用物化视图。

或在公式表中使用item_code作为fk。

Try using Materialized views on your cols.
or use item_code as fk in formula table.

这篇关于如何在datatable中添加缺少的主键（oracle db）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！