Duplicacy check between DATABASE and DATATABLE

Question

i have one column in my database by name PNUMSET (Primary Key) contains unique data.(approx 1L rows)

and in application i have one datatable with one column name NEWPNUM which contains data.

i want to check that no value is matching between existing database and current datatable values..

Note:- no of rows may or may not be same in database and datatable.

so far i tried.....

           String query = "Select PNUMSET FROM DUMMYTABLE";
           MySqlDataAdapter msda = new MySqlDataAdapter(query, connection);
           msda.Fill(dt);                

            for (int k = 0; k < Class1.global_dataset.Tables[0].Rows.Count; k++)
            {
                if (dt.Rows.Contains(Class1.global_dataset.Tables[0].Rows[k][4].ToString()))
                {
                    MessageBox.Show("Baj Gaya Ghanta!!!!");
                }
            }

You want to list all intersecting rows betweeen DataTable and database? Why do you fill the DataTable at all to check this, it would be much more efficient to check this in the database. — Tim Schmelter, Jan 03 '13 at 11:39
filling datatable is diffrent task, in the end i want to add it(datatable) in the database but before doing this want to check how many intersecting rows are there so that i can make a LOG file of it. — neerajMAX, Jan 03 '13 at 11:42

score 2 · Accepted Answer · answered Jan 03 '13 at 11:44

2

You can use Linq-To-DataTable to join both tables on this column, for example:

var commonRows = from r1 in dt.AsEnumerable()
                 join r2 in Class1.global_dataset.Tables[0].AsEnumerable()
                 on r1.Field<int>(4) equals r2.Field<int>(4)
                 select r1;
if(commonRows.Any())
{
    // do something with these rows
}

(assuming the 5th column and it's type int)

Note that although Enumerable.Join is quite efficient it might be better to compare this in the database instead of loading all into memory.

answered Jan 03 '13 at 11:44

Tim Schmelter

450,073
74
686
939

this is not what i am looking for anyways thankx for your help. waiting for more suggestions otherwise i will go with this but in diffrent way. . :) – neerajMAX Jan 03 '13 at 11:54
@neerajMAX: It could be helpful for us to hear what you expect instead. My answer will give you all intersecting rows. – Tim Schmelter Jan 03 '13 at 11:56
no i think this is what which can help me exactly :) so i am taking back my words :) hey one more ques what to do if Type is VARCHAR instead of int ???? – neerajMAX Jan 03 '13 at 12:11
@neerajMAX: Then it's `string` in the `DataTable`. Just change `Field...` to `Field`. – Tim Schmelter Jan 03 '13 at 12:31
doing the same thing.. thakx for your answer... let me try it then i ll let u knw... :) – neerajMAX Jan 03 '13 at 12:36

Duplicacy check between DATABASE and DATATABLE

1 Answers1

Linked