如何使用多个表过滤数据集

本文介绍了如何使用多个表过滤数据集的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

嗨朋友们， 我有一个包含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.