嗨朋友们, 我有一个包含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.
更多推荐
如何使用多个表过滤数据集
发布评论