Comparing two datasets

[Guest]

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 Ligthert [MVP]]

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/d...tml/frlrfsystemdatadataviewclassfindtopic.asp

I hope this helps,

Cor

 

[Guest]

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]]

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 Malik [MVP]]

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

 

[Guest]

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]]

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

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

Ali,


Can you show what does not work?

Cor

 

[Guest]

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

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

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

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

 

[Guest]

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

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

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

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 Ligthert [MVP]]

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