如何使用多个表过滤数据集 [英] How to filter a dataset with multiple tables

问题描述

嗨朋友们，



我有一个包含7个表的DataSet。

Hi Friends,

I have a DataSet with 7 tables.

dss = FetchDataOdbc.ExecuteQuery(string.Format(str, commastring, "2015-04-15"));
             dss.Tables[0].TableName = "TCLT_FEESCH";
             dss.Tables[1].TableName = "TCLT_MNG_FEE";
             dss.Tables[2].TableName = "TCTL_CNTL";
             dss.Tables[3].TableName = "TCLT_PARAMETER";
             dss.Tables[4].TableName = "TCLT_BILL";
             dss.Tables[5].TableName = "TBILL_FAPAY";
             dss.Tables[6].TableName = "TBILL_DETAIL";

我需要查询此数据集，这意味着我需要根据某些条件过滤数据表我有7个sql查询。

I need to Query this Dataset which means i need to filter the datatables based on some criteria.I have 7 sql queries with me.

SELECT AMT_BRKPT,PCT_AE,PCT_RETENT,PCT_SVFE FROM DBQPUBS.TCLT_FEESCH WHERE KEY_TMS = '{0}' AND CAT_TRDCTL='{1}' AND CLA_ASSET_BILL = '{2}' AND DT2_EFF = (SELECT MAX(DT2_EFF) FROM DBQPUBS.TCLT_FEESCH WHERE KEY_TMS = '{0}' AND CAT_TRDCTL='{1}') ORDER BY AMT_BRKPT WITH UR; 

SELECT * FROM DBQPUBS.TCLT_MNG_FEE WHERE KEY_TMS = '{0}' AND CAT_TRDCTL = '{1}' AND STA_ERISA = '{3}' ORDER BY DT2_UPDATE DESC WITH UR; 

SELECT * FROM DBQPUBS.TCTL_CNTL WHERE CDE_TYPCTL='{4}' WITH UR;
SELECT A.PCT_PN,A.PCT_CRESFORT_ALLOC FROM DBQPUBS.TCLT_PARAMETER A, DBQPUBS.TBILL_FAPAY B WHERE A.KEY_TMS = B.KEY_TMS AND A.NUM_PNAE = B.NUM_AE AND A.NUM_PNAE >0 AND A.NUM_AE >0 AND B.AMT_PAY>0 AND B.DT2_BILL='{5}' AND A.KEY_TMS='{0}';

SELECT * FROM DBQPUBS.TCLT_BILL WHERE KEY_TMS = '{0}' AND CAT_TRDCTL = '{1}' AND DT2_BILL = '{5}'   AND CAT_BILL = '{6}'  WITH UR; 

SELECT SUM(AMT_PAY) AS AMT_PAY,SUM(AMT_SVFE) AS AMT_SVFE FROM DBQPUBS.TBILL_FAPAY WHERE KEY_TMS = '{0}' AND CAT_TRDCTL = '{1}' AND DT2_BILL = '{5}' AND CAT_BILL = '{6}'  WITH UR; 

SELECT * FROM DBQPUBS.TBILL_DETAIL WHERE KEY_TMS = '{0}' AND CAT_TRDCTL = '{1}' AND DT2_BILL = '{5}' AND CAT_BILL='{6}' WITH UR; 

SELECT * FROM DBQPUBS.TBILL_PAYEE WHERE KEY_TMS = '{0}' AND CAT_TRDCTL = '{1}' AND DT2_BILL = '{5}'   AND CAT_BILL = '{6}'  WITH UR;

我的要求是编写一个返回带有filterd数据表的数据集的方法。

请提供一些进一步的路线图

提前致谢

My Requirement is to write a method which return a dataset with filterd datatable.
Please provide me some roadmap to proceed further
Thanks in advance

推荐答案

您好jinesh，



您可以使用Select方法或usi过滤数据表ng LINQ。这里有些例子。我将以下查询作为示例。

Hi jinesh,

You can filter a datatable either by using Select method or by using LINQ. Here are some examples. I am taking the below query as example.

SELECT * FROM DBQPUBS.TBILL_DETAIL WHERE KEY_TMS = '{0}' AND CAT_TRDCTL = '{1}' AND DT2_BILL = '{5}' AND CAT_BILL='{6}' WITH UR;

使用Select方法

Select方法将返回DataRow数组。

Using Select method
Select method will return an array of DataRow.

DataRow[] expectedRows = dss.Tables["TBILL_DETAIL"].Select("KEY_TMS = '{0}' AND CAT_TRDCTL = '{1}' AND DT2_BILL = '{5}' AND CAT_BILL = '{6}'");

注意：替换{0}，{1}，{5}和{6}相应的输入



使用LINQ

Note: Replace {0},{1},{5} and {6} with corresponding input

Using LINQ

var expectedRows = from row in dss.Tables["TBILL_DETAIL"].AsEnumerable()
                            where row.Field<string>("KEY_TMS") == "{0}" &&
row.Field<string>("CAT_TRDCTL ") == "{1}" && row.Field<string>("DT2_BILL ") == "{5}" && row.Field<string>("CAT_BILL") == "{6}"
                            select row;</string></string></string></string>

注意：将{0}，{1}，{5}和{6}替换为相应的输入。我已经使用字符串转换进行所有输入。您必须将它们更改为正确的数据类型。



将DataRow []转换为DataTable

Note: Replace {0},{1},{5} and {6} with corresponding input. I have used string casting for all the input. You have to change them to the correct data type.

Convert DataRow[] to DataTable

dss.Tables["TBILL_DETAIL"]= expectedRows.CopyToDataTable();

现在 dss.Tables [TBILL_DETAIL] 将拥有过滤后的数据，就是这样。

Now dss.Tables["TBILL_DETAIL"] will have the filtered data and that's it.

这篇关于如何使用多个表过滤数据集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！