How to filter a dataset with multiple tables

Question

0.00/5 (No votes)

See more:

Hi Friends,

I have a DataSet with 7 tables.

C#

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";

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.

SQL

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;

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

Posted 14-May-15 9:26am

jinesh sam

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Mathi Mani · Answer 1 · 2015-05-14T13:23:00

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;

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

C#

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

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

Using LINQ

C#

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>

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();

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