Combining results from three sources

[Imran Aziz]

Hello All,
I have to combine search results from three different data sources, two
are separate databases in an SQL server one is a mySQL server database. I
get the search results from each of them in my separate classes. Now I need
to combine them and remove duplicates how do I work with that ?

say
DataSet ds1 has now got three tables source1, source2 and source3 how do I
perform an in-memory query on the dataset tables to get a view that removes
duplicates ?

Imran.

 

[Adrian Moore]

Imran,

You might be interested in the assembly I've been working on at
http://www.queryadataset.com. It lets you perform complex SQL SELECT
statements including UNION, JOINS, GROUP BY, HAVING, ORDER BY, sub-queries,
functions, aggregates etc against the tables in a dataset.

In your case, you'll want to do a UNION on the three tables in the dataset:

DataSet ds = new DataSet;
//populate with data from two SQL server databases and MySQL.

string sql = "SELECT * FROM source1 UNION SELECT * FROM source2 UNION SELECT
* FROM source3";
DataView dv = QueryADataSet.DsCommand.Execute(sql, ds);

Its a lot easier than writing ADO.NET code by hand.

Hope this helps
Adrian Moore
http://www.queryadataset.com

 

[Imran Aziz]

Thanks a lot for the link , seems nice, but I dont want to use a third party
shareware application to do that, wanted to find a way that is provided by
..net framework itself.

Imran.

 

[Guest]

Imran,

You'll have to resort to writing a couple hundred lines of ADO.NET code in
your favorite .NET language, I'm afraid. You'll need to combine the rows of
each table into a new table and then perform a DISTINCT algorithm on the new
table.

Good Luck
Ad.

 

[William \(Bill\) Vaughn]

This is an oft-requested feature that is currently unsupported in ADO.NET. I
suggest you import the data to one of the SQL Servers (using bulk copy) and
do the JOIN/duplicate search there. Bulk copy can be executed from TSQL or
via a command-line utility. ADO.NET 2.0 supports it directly.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Marina]

Do the tables have the same schema? If so, maybe you can just merge the
tables, which should remove duplicates.

 

[Marina]

Ok, this would not be hundreds of lines of code. It might be slow to find
duplicates, but it is not going to be a lot of coding.

 

[Imran Aziz]

Hum thanks a lot for the info, decided to dump data from mySQL server to a
temp table (using a backend process) in the same MS SQL database and then do
a regular union on it. This might be more manageable and faster to work
with.

Thanks a lot!
Imran.