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

"Imran Aziz" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.
>

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.


"Adrian Moore" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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'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.



"Imran Aziz" wrote:

> 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.
>
>
> "Adrian Moore" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> >> 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.
> >>
> >
> >
>
>
>

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.
__________________________________

"Imran Aziz" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.
>

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

"Imran Aziz" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.
>

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.

"Adrian Moore" <(E-Mail Removed)> wrote in message
news:8F3FD516-5812-48A1-B54E-(E-Mail Removed)...
> 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.
>
>
>
> "Imran Aziz" wrote:
>
>> 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.
>>
>>
>> "Adrian Moore" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> > 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" <(E-Mail Removed)> wrote in message
>> > news:(E-Mail Removed)...
>> >> 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.
>> >>
>> >
>> >
>>
>>
>>

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.

"Marina" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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.
>
> "Adrian Moore" <(E-Mail Removed)> wrote in message
> news:8F3FD516-5812-48A1-B54E-(E-Mail Removed)...
>> 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.
>>
>>
>>
>> "Imran Aziz" wrote:
>>
>>> 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.
>>>
>>>
>>> "Adrian Moore" <(E-Mail Removed)> wrote in message
>>> news:(E-Mail Removed)...
>>> > 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" <(E-Mail Removed)> wrote in message
>>> > news:(E-Mail Removed)...
>>> >> 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.
>>> >>
>>> >
>>> >
>>>
>>>
>>>
>
>