Combining results from three sources

  • Thread starter Thread starter Imran Aziz
  • Start date Start date
I

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.
 
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
 
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.
 
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.
 
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.
__________________________________
 
Do the tables have the same schema? If so, maybe you can just merge the
tables, which should remove duplicates.
 
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.
 
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.
 
Back
Top