Comparing two datasets

I need to compare the values of two datasets and retrieve the differences
into a third dataset.

I need to retrieve Employees names and numbers from Excel sheet into ds1
and retrieve Employees names and numbers from SQL Employees Table into ds2.
I did this part successfully.

Now i want to compare the values of both datasets so I can figure out new
added employees in the Excel sheet and add them into ds3. How can I do that?

I know i need to loop through one and look for each record in the other but
I can't get the idea ... can anyone give me a sample of how to do this?

Cor
Mon Nov 28 05:30:51 CST 2005

Ali,

You can use two kind of procedures.

The classic loop and access than both tables sequential.

Or just use the dataview.find to get the number in a for each loop from the
searching table in the table to search (what I prefer). If you don't find it
than you have to add the row of the searcher in the extra table.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatadataviewclassfindtopic.asp

I hope this helps,

Cor

Ali
Mon Nov 28 11:46:21 CST 2005

Dear Cor,
Thanx for your quick response.

I just need to know if all the values in ds1 ( retrieved from Excel Sheet)
exist in ds2 ( retrieved from SQL Employees table). if not, return the
non-existing employees and add them into ds3 and view it in datagridview.

your example doesn't look work for me.. If so, can u give me an example of
how to use it???

Thanx
"Cor Ligthert [MVP]" wrote:

> Ali,
>
> You can use two kind of procedures.
>
> The classic loop and access than both tables sequential.
>
> Or just use the dataview.find to get the number in a for each loop from the
> searching table in the table to search (what I prefer). If you don't find it
> than you have to add the row of the searcher in the extra table.
>
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatadataviewclassfindtopic.asp
>
> I hope this helps,
>
> Cor
>
>
>

Cor
Mon Nov 28 12:40:50 CST 2005

Ali,

> your example doesn't look work for me.. If so, can u give me an example of
> how to use it???

Can you show what does not work?

Cor

Sahil
Mon Nov 28 21:22:47 CST 2005

Ali,

Do something like this --

ds2.AcceptChanges
ds2.Merge(ds1,true)
ds2.GetChanges

You will however have to specify schemas on all these datasets, and if you
are workign with multiple tables - you will need to get rid of relatioships
or GetChanges may include unchanged rows as well (to maintain relational
sanctity)

HTH :)


--

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
----------------------------------------------------------------------------


"Ali" <Ali@discussions.microsoft.com> wrote in message
news:E57C4418-2B13-491F-A1DB-84E357F64DA6@microsoft.com...
> Dear Cor,
> Thanx for your quick response.
>
> I just need to know if all the values in ds1 ( retrieved from Excel Sheet)
> exist in ds2 ( retrieved from SQL Employees table). if not, return the
> non-existing employees and add them into ds3 and view it in datagridview.
>
> your example doesn't look work for me.. If so, can u give me an example of
> how to use it???
>
> Thanx
> "Cor Ligthert [MVP]" wrote:
>
>> Ali,
>>
>> You can use two kind of procedures.
>>
>> The classic loop and access than both tables sequential.
>>
>> Or just use the dataview.find to get the number in a for each loop from
>> the
>> searching table in the table to search (what I prefer). If you don't find
>> it
>> than you have to add the row of the searcher in the extra table.
>>
>> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatadataviewclassfindtopic.asp
>>
>> I hope this helps,
>>
>> Cor
>>
>>
>>

Ali
Wed Nov 30 00:36:03 CST 2005

I have done this so far. the problem is that I don't know how to loop
properly. I just need to ckeck if all the values in ds( retrieved from Excel
sheet) exist in ds1 ( retrieved from SQL Table). if not, retrun non-
existing employees in ds3.

All the values of ds have to be in ds1 ... but oppoiste might not be true. (
means ds1 always greater than ds ).



Dim sConString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"data
source=c:\PayrollJVOct2005.xls;" & _
"Extended properties=""Excel 8.0;
HDR=Yes;"""


Dim jConString As String = "Data Source=JDE;Initial
Catalog=PS_HAIFA;User ID=ASIF;Pwd=78612312"
Dim jCon As New System.Data.SqlClient.SqlConnection(jConString)
Dim Jda As New System.Data.SqlClient.SqlDataAdapter
Dim Con As New System.Data.OleDb.OleDbConnection(sConString)
Con.Open()


Dim cmdSelect As New System.Data.OleDb.OleDbCommand("select * from
[sheet1$]", Con)
Dim da As New System.Data.OleDb.OleDbDataAdapter
da.SelectCommand = cmdSelect
Dim ds As New System.Data.DataSet
da.Fill(ds)
Con.Close()
Con.Dispose()
DR = ds.Tables(0).Rows(0)


Dim jConString2 As String = "Data Source=EN;Initial
Catalog=PS_HAIFA;User ID=ASIF;Pwd=78612312"
Dim jCon2 As New System.Data.SqlClient.SqlConnection(jConString2)
Dim Jda2 As New System.Data.SqlClient.SqlDataAdapter("Select aban8
from HAIFDTA.[F0101] where ABAT1='E'", jCon2)
jCon2.Open()
Jda2.Fill(ds1)
jCon2.Close()
jCon2.Dispose()

DrEmployee = ds1.Tables(0).Rows(0)

Dim m As Integer
For i = 1 To ds.Tables(0).Rows.Count

-- check code here <=== here where I am stuck
Next




"Cor Ligthert [MVP]" wrote:

> Ali,
>
> > your example doesn't look work for me.. If so, can u give me an example of
> > how to use it???
>
> Can you show what does not work?
>
> Cor
>
>
>

Cor
Wed Nov 30 03:43:23 CST 2005

Ali,

I made a sample for you (I use datatables, with a dataset is the datatable
just ds.tables(0)

By the way, you need only a close or a dispose. I use only the close.

\\\
'Creating a testset
Dim dt1 As New DataTable
Dim dt2 As New DataTable
dt1.Columns.Add("Customers")
dt2.Columns.Add("Customers")
For i As Integer = 0 To 20
dt1.LoadDataRow(New Object() {i.ToString}, True)
dt2.LoadDataRow(New Object() {i.ToString}, True)
Next
dt2.Rows(4).Delete()
dt2.Rows(9).Delete()

'Start of sample
Dim dt3 As DataTable = dt1.Clone
Dim dv As DataView(dt2)
dv.Sort = "Customers"
For Each dr As DataRow In dt1.Rows
If dv.Find(dr("Customers")) = -1 Then
dt3.ImportRow(dr)
End If
Next
///

I hope this helps,

Cor

"Ali" <Ali@discussions.microsoft.com> schreef in bericht
news:5D0E2E98-9706-4D8A-9E82-497A90668A8B@microsoft.com...
>I have done this so far. the problem is that I don't know how to loop
> properly. I just need to ckeck if all the values in ds( retrieved from
> Excel
> sheet) exist in ds1 ( retrieved from SQL Table). if not, retrun non-
> existing employees in ds3.
>
> All the values of ds have to be in ds1 ... but oppoiste might not be true.
> (
> means ds1 always greater than ds ).
>
>
>
> Dim sConString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
> "data
> source=c:\PayrollJVOct2005.xls;" & _
> "Extended properties=""Excel 8.0;
> HDR=Yes;"""
>
>
> Dim jConString As String = "Data Source=JDE;Initial
> Catalog=PS_HAIFA;User ID=ASIF;Pwd=78612312"
> Dim jCon As New System.Data.SqlClient.SqlConnection(jConString)
> Dim Jda As New System.Data.SqlClient.SqlDataAdapter
> Dim Con As New System.Data.OleDb.OleDbConnection(sConString)
> Con.Open()
>
>
> Dim cmdSelect As New System.Data.OleDb.OleDbCommand("select * from
> [sheet1$]", Con)
> Dim da As New System.Data.OleDb.OleDbDataAdapter
> da.SelectCommand = cmdSelect
> Dim ds As New System.Data.DataSet
> da.Fill(ds)
> Con.Close()
> Con.Dispose()
> DR = ds.Tables(0).Rows(0)
>
>
> Dim jConString2 As String = "Data Source=EN;Initial
> Catalog=PS_HAIFA;User ID=ASIF;Pwd=78612312"
> Dim jCon2 As New System.Data.SqlClient.SqlConnection(jConString2)
> Dim Jda2 As New System.Data.SqlClient.SqlDataAdapter("Select aban8
> from HAIFDTA.[F0101] where ABAT1='E'", jCon2)
> jCon2.Open()
> Jda2.Fill(ds1)
> jCon2.Close()
> jCon2.Dispose()
>
> DrEmployee = ds1.Tables(0).Rows(0)
>
> Dim m As Integer
> For i = 1 To ds.Tables(0).Rows.Count
>
> -- check code here <=== here where I am stuck
> Next
>
>
>
>
> "Cor Ligthert [MVP]" wrote:
>
>> Ali,
>>
>> > your example doesn't look work for me.. If so, can u give me an example
>> > of
>> > how to use it???
>>
>> Can you show what does not work?
>>
>> Cor
>>
>>
>>

Ali
Wed Nov 30 07:06:04 CST 2005


The sample returns the difference between the two .. in my case I have in
dt1 some values which I don't want to include in my comparison. Because for
me , dt2 is the payroll for all employees , and dt1 is for all employees even
those who left the company, inactive.

in my case:

all values of dt2 ( Employees Numbers) should be part of dt1 but the
opposite is not true. so the difference between both tables won't work. your
suggestion is highly appreciated. Thanx


"Cor Ligthert [MVP]" wrote:

> Ali,
>
> I made a sample for you (I use datatables, with a dataset is the datatable
> just ds.tables(0)
>
> By the way, you need only a close or a dispose. I use only the close.
>
> \\\
> 'Creating a testset
> Dim dt1 As New DataTable
> Dim dt2 As New DataTable
> dt1.Columns.Add("Customers")
> dt2.Columns.Add("Customers")
> For i As Integer = 0 To 20
> dt1.LoadDataRow(New Object() {i.ToString}, True)
> dt2.LoadDataRow(New Object() {i.ToString}, True)
> Next
> dt2.Rows(4).Delete()
> dt2.Rows(9).Delete()
>
> 'Start of sample
> Dim dt3 As DataTable = dt1.Clone
> Dim dv As DataView(dt2)
> dv.Sort = "Customers"
> For Each dr As DataRow In dt1.Rows
> If dv.Find(dr("Customers")) = -1 Then
> dt3.ImportRow(dr)
> End If
> Next
> ///
>
> I hope this helps,
>
> Cor
>
> "Ali" <Ali@discussions.microsoft.com> schreef in bericht
> news:5D0E2E98-9706-4D8A-9E82-497A90668A8B@microsoft.com...
> >I have done this so far. the problem is that I don't know how to loop
> > properly. I just need to ckeck if all the values in ds( retrieved from
> > Excel
> > sheet) exist in ds1 ( retrieved from SQL Table). if not, retrun non-
> > existing employees in ds3.
> >
> > All the values of ds have to be in ds1 ... but oppoiste might not be true.
> > (
> > means ds1 always greater than ds ).
> >
> >
> >
> > Dim sConString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
> > "data
> > source=c:\PayrollJVOct2005.xls;" & _
> > "Extended properties=""Excel 8.0;
> > HDR=Yes;"""
> >
> >
> > Dim jConString As String = "Data Source=JDE;Initial
> > Catalog=PS_HAIFA;User ID=ASIF;Pwd=78612312"
> > Dim jCon As New System.Data.SqlClient.SqlConnection(jConString)
> > Dim Jda As New System.Data.SqlClient.SqlDataAdapter
> > Dim Con As New System.Data.OleDb.OleDbConnection(sConString)
> > Con.Open()
> >
> >
> > Dim cmdSelect As New System.Data.OleDb.OleDbCommand("select * from
> > [sheet1$]", Con)
> > Dim da As New System.Data.OleDb.OleDbDataAdapter
> > da.SelectCommand = cmdSelect
> > Dim ds As New System.Data.DataSet
> > da.Fill(ds)
> > Con.Close()
> > Con.Dispose()
> > DR = ds.Tables(0).Rows(0)
> >
> >
> > Dim jConString2 As String = "Data Source=EN;Initial
> > Catalog=PS_HAIFA;User ID=ASIF;Pwd=78612312"
> > Dim jCon2 As New System.Data.SqlClient.SqlConnection(jConString2)
> > Dim Jda2 As New System.Data.SqlClient.SqlDataAdapter("Select aban8
> > from HAIFDTA.[F0101] where ABAT1='E'", jCon2)
> > jCon2.Open()
> > Jda2.Fill(ds1)
> > jCon2.Close()
> > jCon2.Dispose()
> >
> > DrEmployee = ds1.Tables(0).Rows(0)
> >
> > Dim m As Integer
> > For i = 1 To ds.Tables(0).Rows.Count
> >
> > -- check code here <=== here where I am stuck
> > Next
> >
> >
> >
> >
> > "Cor Ligthert [MVP]" wrote:
> >
> >> Ali,
> >>
> >> > your example doesn't look work for me.. If so, can u give me an example
> >> > of
> >> > how to use it???
> >>
> >> Can you show what does not work?
> >>
> >> Cor
> >>
> >>
> >>
>
>
>

Ali
Thu Dec 01 03:15:02 CST 2005

Hi Cor Ligthert,

I really need someone help and ur the only one who responded to my request.
I really appreciate this.


The sample which u post returns the difference between the two datasets ..
in my case I have in
dt1 some values which I don't want to include in my comparison. Because for
me , dt2 is the payroll for all employees , and dt1 is for all employees even
those who left the company, inactive employees. so they will not be
included in my comparison.



in my case:

all values of dt2 ( Employees Numbers) should be part of dt1 but the
opposite is not true. so the difference between both tables won't work. your
suggestion is highly appreciated. Thanx


I write something like this but it did not work:
dr= ds.tables(0).rows
dr1= ds1.tables(0).rows
For i = 1 to ds.tables(0).rows.count
For j = 1 tp ds1.tables(0).rows.count
if dr(6) <> dr1(0)
j = j + 1
else
i = i + 1
Next
Next


"Cor Ligthert [MVP]" wrote:

> Ali,
>
> I made a sample for you (I use datatables, with a dataset is the datatable
> just ds.tables(0)
>
> By the way, you need only a close or a dispose. I use only the close.
>
> \\\
> 'Creating a testset
> Dim dt1 As New DataTable
> Dim dt2 As New DataTable
> dt1.Columns.Add("Customers")
> dt2.Columns.Add("Customers")
> For i As Integer = 0 To 20
> dt1.LoadDataRow(New Object() {i.ToString}, True)
> dt2.LoadDataRow(New Object() {i.ToString}, True)
> Next
> dt2.Rows(4).Delete()
> dt2.Rows(9).Delete()
>
> 'Start of sample
> Dim dt3 As DataTable = dt1.Clone
> Dim dv As DataView(dt2)
> dv.Sort = "Customers"
> For Each dr As DataRow In dt1.Rows
> If dv.Find(dr("Customers")) = -1 Then
> dt3.ImportRow(dr)
> End If
> Next
> ///
>
> I hope this helps,
>
> Cor
>
> "Ali" <Ali@discussions.microsoft.com> schreef in bericht
> news:5D0E2E98-9706-4D8A-9E82-497A90668A8B@microsoft.com...
> >I have done this so far. the problem is that I don't know how to loop
> > properly. I just need to ckeck if all the values in ds( retrieved from
> > Excel
> > sheet) exist in ds1 ( retrieved from SQL Table). if not, retrun non-
> > existing employees in ds3.
> >
> > All the values of ds have to be in ds1 ... but oppoiste might not be true.
> > (
> > means ds1 always greater than ds ).
> >
> >
> >
> > Dim sConString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
> > "data
> > source=c:\PayrollJVOct2005.xls;" & _
> > "Extended properties=""Excel 8.0;
> > HDR=Yes;"""
> >
> >
> > Dim jConString As String = "Data Source=JDE;Initial
> > Catalog=PS_HAIFA;User ID=ASIF;Pwd=78612312"
> > Dim jCon As New System.Data.SqlClient.SqlConnection(jConString)
> > Dim Jda As New System.Data.SqlClient.SqlDataAdapter
> > Dim Con As New System.Data.OleDb.OleDbConnection(sConString)
> > Con.Open()
> >
> >
> > Dim cmdSelect As New System.Data.OleDb.OleDbCommand("select * from
> > [sheet1$]", Con)
> > Dim da As New System.Data.OleDb.OleDbDataAdapter
> > da.SelectCommand = cmdSelect
> > Dim ds As New System.Data.DataSet
> > da.Fill(ds)
> > Con.Close()
> > Con.Dispose()
> > DR = ds.Tables(0).Rows(0)
> >
> >
> > Dim jConString2 As String = "Data Source=EN;Initial
> > Catalog=PS_HAIFA;User ID=ASIF;Pwd=78612312"
> > Dim jCon2 As New System.Data.SqlClient.SqlConnection(jConString2)
> > Dim Jda2 As New System.Data.SqlClient.SqlDataAdapter("Select aban8
> > from HAIFDTA.[F0101] where ABAT1='E'", jCon2)
> > jCon2.Open()
> > Jda2.Fill(ds1)
> > jCon2.Close()
> > jCon2.Dispose()
> >
> > DrEmployee = ds1.Tables(0).Rows(0)
> >
> > Dim m As Integer
> > For i = 1 To ds.Tables(0).Rows.Count
> >
> > -- check code here <=== here where I am stuck
> > Next
> >
> >
> >
> >
> > "Cor Ligthert [MVP]" wrote:
> >
> >> Ali,
> >>
> >> > your example doesn't look work for me.. If so, can u give me an example
> >> > of
> >> > how to use it???
> >>
> >> Can you show what does not work?
> >>
> >> Cor
> >>
> >>
> >>
>
>
>

Cor
Thu Dec 01 03:36:15 CST 2005

Ali,

I am not sure if I understand you well. However if I do than it would be.

'Start of sample
Dim dt3 As DataTable = dt1.Clone
Dim dv As DataView(dt2)
dv.Sort = "Customers"
For Each dr As DataRow In dt1.Rows
if dr("StillInCompany) = true then
If dv.Find(dr("Customers")) = -1 Then
dt3.ImportRow(dr)
End If
end if
Next
///

However that is in my opinion something you should have to be able to do
yourself if you are making a program.

I hope this helps,

Cor