Use Vb.Net to compare recordsets

[Gaby Sandoval]

I want to write a exe that compares two recordsets i will create using
SQL statements.

Recordset "A" is from the source table our users are currently using.
Recordset "B" is from a view that shows up to the minute changes
(Pulls from muiltiple sources).

I will run the exe everynight and this is what I am trying to
accomplish.

If B has more records than A.... I want to insert the differeneces
into the source table from A. The changes would also be written to a
log file so the changes would be documented nightly.

If A has more records than B... I want to write to a log that notes
the missing records so that they can be reviewed later and the user
can determine if they need to be processed for deletion.

i know how to create the recordsets, but beyond that I am looking for
any help to see what type of function or code I would use.

thanks in advance for any suggestions.

 

[Scott M.]

Why would you be using Recordsets in the first place, since they are not a
part of the .NET architecture?

You'll have an easier time if you use the ADO .NET DataSet and populate it
with two tables that you can then compare against each other.

-Scott

 

[Joe Cool]

I want to write a exe that compares two recordsets i will create using
SQL statements.

Recordset "A" is from the source table our users are currently using.
Recordset "B" is from a view that shows up to the minute changes
(Pulls from muiltiple sources).

I will run the exe everynight and this is what I am trying to
accomplish.

If B has more records than A.... I want to insert the differeneces
into the source table from A.  The changes would also be written to a
log file so the changes would be documented nightly.

If A has more records than B... I want to write to a log that notes
the missing records so that they can be reviewed later and the user
can determine if they need to be processed for deletion.

i know how to create the recordsets, but beyond that I am looking for
any help to see what type of function or code  I would use.

thanks in advance for any suggestions.

Well, for one thing, you don't know the number of records in a
SqlDataReader until you have actually read through it, and you can
only read through a SqlDataReader once.

I would suggest loading both recordsets into DataTables and then use
them to determine which has more records (rows) and which ones are
either deleted or are new.

There is no built in way that I am aware of that can give you a
collection of which rows are in DataTable A that are not in B and vice
versa. You are going to manually loop through both tables and compare
enough of the values to determine if the two rows are identical or
not.

 

[Andrew Morton]

There is no built in way that I am aware of that can give you a
collection of which rows are in DataTable A that are not in B and vice
versa. You are going to manually loop through both tables and compare
enough of the values to determine if the two rows are identical or
not.

You can if you contrive to have the rows in something like two List(Of
String) and use listA.Except(listB).ToList

Andrew

 

[Gaby Sandoval]

Why would you be using Recordsets in the first place, since they are not a
part of the .NET architecture?

You'll have an easier time if you use the ADO .NET DataSet and populate it
with two tables that you can then compare against each other.

-Scott












- Show quoted text -

If i use DataSets, is there some built in way to do a Compare. Or
will I have to manually determine a way to loop through each row to
find which values differ from each table?

 

[Cor Ligthert[MVP]]

Gaby,

By using the name recordset you have probably confused all of us, like Scott
showed.

However, be aware that Ling to SQL has great methods to do what you want,
however, I would probably not even take the resultset (the true name) to the
client but do the job on the Server side, but that is no VB code but SQL
transact.

Cor

 

[Scott M.]

If i use DataSets, is there some built in way to do a Compare. Or
will I have to manually determine a way to loop through each row to
find which values differ from each table?


There's no "built-in" way, but there are certainly ways to code that
solution, especially if you loaded the data into a generic List(Of T) type
container.

Unless you have some specific reason for using Recordsets, you should say
goodbye to them and stick with .NET objects.

-Scott