Comparing 2 datatables

Question

Hi all...

I need help in comparing 2 datatables and then making column values null.

My Requirement -
1. I have 2 datatables -

datatable tableA = new datatable();
tableA = Method1(); //return type of Method1 is datatable.

datatable tableB = new datatable();
tableB = Method2(); //return type of Method2 is datatable.

2.
I have distinct values in tableA.

IN_NUMBER

122

12345

34567

123

456

I have multiple values in tableB.

IN_NUMBER	W_AMT	WH_AMT	Remarks	SrNo
122	10	100	Test	1
122	20	200	Test	2
12345	30	300	verified	3
12345	40	400	verified	4
34567	50	500	test	5
456	60	600	approved	6
123	70	700	approved	7

3. I need result as follows -

IN_NUMBER	W_AMT	WH_AMT	Remarks
122	10	100	Test
122
12345	30	300	verified
12345
34567	50	500	test
456	60	600	approved
123	70	700	approved

Logic -
I have to take IN_NUMBER Column values from tableA.
Say - 122
I need to check how many times it is appearing in tableB.
Here - 2 times.
Now, I need to keep the column values W_AMT, WH_AMT, Remarks in only one row for IN_NUMBER 122.
-------------------
For value 34567, I do not make any changes as it is appearing only once.
-------
Again for value 12345, I need to remove column values W_AMT, WH_AMT, Remarks from one row.
-------
In tableB, there is a SrNo (unique primary key).
----
How do I achieve this ?

Vikram Agrawal · Answer

Hi Riddhi try this I think it will work fine for (int index = 0; index { flag= false; var newrow = dtNew.NewRow(); string In_Number = table.Rows[index][IN_Number].ToString(); newrow[SrNo] = index + 1 ; newrow[IN_Number] = In_Number ; for( int i = 0 ; i { if (dtNew.Rows[i][IN_Number].ToString() == IN_Number ) { newrow[W_Amt] = String.Empty; newrow[WH_Amt] = String.Empty; newrow[Remarks] = String.Empty; flag = True break; } } if (!flag) Then { newrow[W_Amt] = table.Rows[index][W_Amt]; newrow[WH_Amt] = table.Rows[index][WH_Amt]; newrow[Remarks] = table.Rows[index][Remarks]; } dtNew.Rows.Add(newrow); } still if it is not giving proper result then please debug this code and see why it is giving blank rows from 5 to 8 Thanks

Riddhi Valecha · Answer

HI.. I have already accepted Vikram's answer.....

mukesh kumar · Answer

????????????????????////

Gowtham Rajamanickam · Answer

aceept the answer//..

Vikram Agrawal · Answer

You are most welcome @Riddhi

Riddhi Valecha · Answer

THANKS A TON VIKRAM... THIS CODE IS RUNNING VERY WELL.... THANKS THANKS THANKS THANKS THANKS A TON....YOU SAVED ME.....

Riddhi Valecha · Answer

table - method() (data from back-end) --- IN_NUMBER WORKORDERS DEDUCTION_AMT WITH_HOLD_AMT HOLD_AMT_REMARK 115274839 WORKORDERS -100 0 0 11577782 WORKORDERS - 200 0 0 115274837 WORKORDERS - 300 11577782 WORKORDERS - 400 0 0 315274860 WORKORDERS -500 115274838 WORKORDERS - 600 1111 1111 test 115274836 WORKORDERS - 700 115274840 WORKORDERS - 800 1500 0 test 115274837 WORKORDERS - 900 115274837 WORKORDERS - 1000 dtNew - (data after executing your code ) IN_NUMBER WORKORDERS DEDUCTION_AMT WITH_HOLD_AMT HOLD_AMT_REMARK 115274839 WORKORDERS -100 0 0 11577782 WORKORDERS - 200 0 0 115274837 WORKORDERS - 300 11577782 WORKORDERS - 400 115274837 WORKORDERS - 900 115274837 WORKORDERS - 1000 This is inserting blank rows... Please let me know if you need any other information.

Vikram Agrawal · Answer

hi riddhi please provide the data of DataTable newDT. I want to see what is the structure and what are the values its displaying

Riddhi Valecha · Answer

True... But THis is inserting blank records in the datatable. I don't want any blank record. IN_NUMBER will never be null.

Vikram Agrawal · Answer

Earlier you said that you want your result like this only IN_NUMBER 122 12345 34567 123 456 I have multiple values in tableB. IN_NUMBER W_AMT WH_AMT Remarks SrNo 122 10 100 Test 1 122 20 200 Test 2 12345 30 300 verified 3 12345 40 400 verified 4 34567 50 500 test 5 456 60 600 approved 6 123 70 700 approved 7 3. I need result as follows - IN_NUMBER W_AMT WH_AMT Remarks 122 10 100 Test 122 12345 30 300 verified 12345 34567 50 500 test 456 60 600 approved 123 70 700 approved pls give the o/p of newDT how it contains all the rows

Riddhi Valecha · Answer

Hi.. It is giving IN_NUMBER. It is making an issue with the following if-condition - Whenever the IF-Condition results in false it increases blank rows. if (dtNew.Rows[i][INWARD_NUMBER].ToString() == table.Rows[index][INWARD_NUMBER].ToString()) ------------ Also There is one IN_NUMBER - 11357 in which the column values - W_AMT WH_AMT Remarks - are blank in database. -----------

Vikram Agrawal · Answer

is it giving any value for IN_Number or it is also blank?

Riddhi Valecha · Answer

Hi... From this logic.. I got the desired result from Srno - 1234910. For SrNo - 5678 - Rows are blank. What to do to fill those records?

Riddhi Valecha · Answer

Hi... From this logic.. I got the desired result from Srno - 1234910. For SrNo - 5678 - Rows are blank. What to do to fill those records?

Vikram Agrawal · Answer

Use this code private void button1_Click(object sender Eventargs e) { table = new DataTable(); table =Method1; // Here return whole table instead of one column from method 1 #region AddSRNOColumnAtPositionZeroAndFillITWithSerialNumbers DataTable dtNew =new DataTable(); dtNew.Columns.Add(SrNo); dtNew.Columns.Add(IN_Number); dtNew.Columns.Add(W_Amt); dtNew.Columns.Add(WH_Amt); dtNew.Columns.Add(Remarks); for (int index = 0; index { var newrow = dtNew.NewRow(); newrow[SrNo] = index + 1 ; for( int i = 0 ; i { if (dtNew.Rows[i][IN_Number].ToString() == table.Rows[index][IN_Number].ToString() ) { newrow[IN_Number] = table.Rows[index][IN_Number]; newrow[W_Amt] = String.Empty; newrow[WH_Amt] = String.Empty; newrow[Remarks] = String.Empty; flag = True break; } } if (!flag) Then { newrow[IN_Number] = table.Rows[index][IN_Number]; newrow[W_Amt] = table.Rows[index][W_Amt]; newrow[WH_Amt] = table.Rows[index][WH_Amt]; newrow[Remarks] = table.Rows[index][Remarks]; } dtNew.Rows.Add(newrow); } #endregion //Here no need to Use DataView you can use newDt for displaying this data. }

Riddhi Valecha · Answer

Sure-- private void button1_Click(object sender Eventargs e) { table = new DataTable(); table =Method1; #region AddSRNOColumnAtPositionZeroAndFillITWithSerialNumbers DataColumn Col = table.Columns.Add(SrNo typeof(System.Int32)); Col.SetOrdinal(0); for (int index = 1; index { if (table.Rows[index - 1][SrNo].ToString().Trim().Length == 0) { table.Rows[index - 1][SrNo] = index; } } #endregion #region GetColumnValues DataView dv = new DataView(table); DataTable dt = dv.ToTable(true IN_NUMBER); //distinct IN_NUMBER #endregion } --------- From here I need to 1. access each record from datatable-dt 2. check how many times each IN_NUMBER is present in datatable-table. 3. If more than once then select all SrNo from column. 4. Keep W-AMT WH_AMT and Remarks column in SrNo which is minimum(say for IN-NUMBER - 122 I need to keep columns values W-AMT WH_AMT and Remarks for SrNo - 1). 5.Remove the column values W-AMT WH_AMT and Remarks for SrNo - 2. 6. If IN_NUMBER is present say 3 times then I need to remove the column values W-AMT WH_AMT and Remarks for SrNo - 2 and SrNo -3. --- ---------------------

Vikram Agrawal · Answer

Hi Riddhi actually this code is creating one more DataTable i.e. dtNew which is created based on old DataTable and for all duplicate IN_Numbers we are providing empty values. your TableB contains all the columns which is returning from the method2() so use TableB inspite of TableA. can you please share your button_Click code and Method1() code here? so we will be able to help more. Thanks

Riddhi Valecha · Answer

Hi Vikram... My 2 datatables are as follows - tableA = new Datatable(); tableA = Method1(); //return type of method is datatable. Now In front-end on button click event I have written - DataView dv = new DataView(table); DataTable dt = dv.ToTable(true IN_NUMBER); in datatable dt there is just one column - INWARD_NUMBER. Other columns - W_AMT WH_AMT and Remarks in other table. The code here is giving an exception - table does not contain column W_AMT. Please explain your logic... so that I can proceed further...

Vikram Agrawal · Answer

Well try with this code it will give you desired result DataTable dtOld = new DataTable(); DataTable dtNew As New DataTable (); bool flag=false; dtNew.Columns.Add(IN_Number); dtNew.Columns.Add(W_Amt); dtNew.Columns.Add(WH_Amt); dtNew.Columns.Add(Remarks); foreach(DataRow row In dtOld.Rows) { var newrow = dtNew.NewRow(); for( int i = 0 ; i { if (dtNew.Rows[i][IN_Number].ToString() == row[IN_Number].ToString() ) { newrow[IN_Number] = row[IN_Number]; newrow[W_Amt] = String.Empty; newrow[WH_Amt] = String.Empty; newrow[Remarks] = String.Empty; flag = True break; } } if (!flag) Then { newrow[IN_Number] = row[IN_Number]; newrow[W_Amt] = row[W_Amt]; newrow[WH_Amt] = row[WH_Amt]; newrow[Remarks] = row[Remarks]; } dtNew.Rows.Add(newrow); } //Now your dtNew is ready with your desired result.

Riddhi Valecha · Answer

No.. tableA = new Datatable(); tableA = Method1(); //return type of method is datatable. Now In front-end on button click event I have written - DataView dv = new DataView(table); DataTable dt = dv.ToTable(true IN_NUMBER); ------- So in datatable dt I have all unique IN_NUMBER. -- Total rows in datatable dt is less and total rows in datatable table is more... There are 3 IN_NUMBERS that are present 3 times in all the rows column values W_AMT WH_AMT and Remarks are there. ------ I am working on it. I am not getting how to get total records from datatable table where IN_NUMBER (say 122) is present. -------- Then I can make values null using SrNo column - since it is unique.

Jignesh Trivedi · Answer

Hi Same thing is happening in my previous c# code right?

Riddhi Valecha · Answer

I have 2 datatables- tableA = new Datatable(); tableA = Method1(); //return type of method is datatable. Now In front-end on button click event I have written - DataView dv = new DataView(table); DataTable dt = dv.ToTable(true IN_NUMBER); ------- So in datatable dt I have all unique IN_NUMBER. -- Requirement - I have to access all IN_NUMBERS from datatable dt (say I start with 122). I have to find out how many times IN_NUMBER - 122 is present in tableA. (Here it is two times). If IN_NUMBER is present just once then I don't have to do anything. If IN_NUMBER is present more than one time (say 2 times 3 times) then I have to show the W_AMT WH_AMT and Remarks columns in only 1 row. Eg- IN_NUMBER 122 is present 2 times in tableA (SrNo - 1 and 2). So Columns W_AMT WH_AMT and Remarks should be displayed against only one row (say SrNo -1) and should be null for other row (say SrNo - 2). ------------ Same for IN_NUMBER 12345. So Columns W_AMT WH_AMT and Remarks should be displayed against only one row (say SrNo -3) and should be null for other row (say SrNo - 4).

Jignesh Trivedi · Answer

Hi you want to same result in SQL in single query ... try... Create table #Temp ( id int IN_NUMBER varchar(50) W_AMT varchar(20) WH_AMT varchar(30) Remarks varchar(30) ) insert into #Temp values (1'122' '10' '100' 'Test') insert into #Temp values (2'122' '20' '200' 'Test') insert into #Temp values (3'12345' '30' '300' 'verified') insert into #Temp values (4'12345' '40' '400' 'verified') insert into #Temp values (5'34567' '50' '500' 'test') insert into #Temp values (6'456' '60' '600' 'approved') insert into #Temp values (7'123' '70' '700' 'approved') select a.id IN_NUMBER case when p.id is null then '' else a.W_AMT end as W_AMT case when p.id is null then '' else a.WH_AMT end as WH_AMT case when p.id is null then '' else a.Remarks end as Remarks from #temp a Left outer join ( select min(id) as id from #temp group by IN_NUMBER) p on a.id = p.id hope this will help you.

Jignesh Trivedi · Answer

Hi I am not getting your point can please elaborate?

Riddhi Valecha · Answer

Hi.. Yes... I need the solution in front-end.... @Jignesh I tried your solution I am almost near to the result. The issue is - I am not getting the number of times one IN_NUMBER is present. Eg-IN_NUMBER - 122 is present two times. So I need to keep the columns (W_AMT WH_AMT Remarks) values in just one row. and remove from the second row. I am stuck up there !! PLease guide... THanks a ton...

Vikram Agrawal · Answer

Hi Riddhi Valecha if you want in SQL then try this script it will give you result as per your requirement. Made some changes as you needed. DECLARE @TOTROWS INT@IDX INT=1 DECLARE @IN_NUMBER INT@W_AMT VARCHAR(100) CREATE TABLE #TEMP (ID INT IDENTITYIN_NUMBER INTW_AMT VARCHAR(100)) CREATE TABLE #TMP (IN_NUMBER INTW_AMT VARCHAR(100)) INSERT INTO #TEMP (IN_NUMBERW_AMT) SELECT IN_NUMBERW_AMT FROM TABLE_B SET @TOTROWS=@@ROWCOUNT WHILE(@IDX =@TOTROWS ) BEGIN SELECT @IN_NUMBER = IN_NUMBER@W_AMT = W_AMT FROM #TEMP WHERE ID=@IDX IF NOT EXISTS(SELECT IN_NUMBER FROM #TMP WHERE IN_NUMBER=@IN_NUMBER ) INSERT INTO #TMP (IN_NUMBERW_AMT) VALUES (@IN_NUMBER@W_AMT) ELSE INSERT INTO #TMP (IN_NUMBERW_AMT) VALUES (@IN_NUMBERNULL) SET @IDX+=1 END SELECT * FROM #TMP -- THIS TABLE CONTAINS YOUR DESIRED RESULT DROP TABLE #TEMP DROP TABLE #TMP Thanks

Jignesh Trivedi · Answer

Hi Try following code DataTable dt = new DataTable(); dt.Columns.Add(IN_NUMBER); dt.Columns.Add(W_AMT); dt.Columns.Add(WH_AMT); dt.Columns.Add(Remarks); dt.Columns.Add(SrNo ); dt.Rows.Add(new object[] { 122 10 100 Test 1 }); dt.Rows.Add(new object[] { 122 20 200 Test 2 }); dt.Rows.Add(new object[] { 12345 30 300 verified 3 }); dt.Rows.Add(new object[] { 12345 40 400 verified 4 }); dt.Rows.Add(new object[] { 34567 50 500 test 5 }); dt.Rows.Add(new object[] { 456 60 600 approved 6 }); dt.Rows.Add(new object[] { 123 70 700 approved 7 }); string IN_NUMBER = Convert.ToString(dt.Rows[0][IN_NUMBER]); for (var i = 1; i { if (IN_NUMBER == Convert.ToString(dt.Rows[i][IN_NUMBER])) { dt.Rows[i][W_AMT] = ; dt.Rows[i][WH_AMT] = ; dt.Rows[i][Remarks] = ; } IN_NUMBER = Convert.ToString(dt.Rows[i][IN_NUMBER]); } hope this will help you.

Jignesh Trivedi · Answer

you required solution in SQL or C# code?