none
tableadapters and two different data sources RRS feed

  • Question

  • I am using visual studio and have a need to access two different data sources.  One is sql and the other is oracle.  I can use the data designer to create a dataset with tables from each datasource in it.  The question I have is "How can I create a query that gathers data from two tables with one in one datasource and the second table in the other datasource?" When you try to build your query you can only see tables from one data source at a time.  Is there a better solution?

    Thanks

    • Moved by Kathy LuMicrosoft employee Thursday, March 26, 2009 5:17 PM User is using DataSet (Moved from ADO.NET Data Providers to ADO.NET DataSet)
    Thursday, March 26, 2009 4:10 PM

Answers

  • What you can do is pull data into two DataTables from independent Data Providers (Oracle, SQL Server) that create a DataSet, push the DataTables into the DataSet, then if they are "joinable", create a DataRelation and make the join.

    Here is an example of having a DataRelation on a many to many relation which you could potentially tweak to incorporate your two DataTables:

    //Just an Example

    using System;  
    using System.Collections.Generic;  
    using System.Linq;  
    using System.Text;  
    using System.Data.SqlClient;  
    using System.Data;  
     
    namespace ConsoleApplication1  
    {  
        class Program  
        {  
            static void Main(string[] args)  
            {  
                ForumExample();  
            }  
     
            private static void ForumExample()  
            {  
                string connString, sql;  
                connString = @"Data Source=JOHN\JOHNGROVE;Initial Catalog=pubs;Integrated Security=SSPI";  
                SqlConnection cn = new SqlConnection(connString);  
                cn.Open();  
                SqlDataAdapter daAuthors, daTitles, daTitleAuthor;  
                sql = "SELECT au_id, au_lname, au_fname FROM authors";  
                daAuthors = new SqlDataAdapter(sql, cn);  
                sql = "SELECT title_id, title FROM titles";  
                daTitles = new SqlDataAdapter(sql, cn);  
                sql = "SELECT au_id, title_id FROM titleauthor";  
                daTitleAuthor = new SqlDataAdapter(sql, cn);  
                DataSet ds = new DataSet();  
                daAuthors.Fill(ds, "authors");  
                daTitles.Fill(ds, "titles");  
                daTitleAuthor.Fill(ds, "titleauthor");  
     
                //Add relations  
                ds.Relations.Add("authors_titleauthor",  
                    ds.Tables["authors"].Columns["au_id"],  
                    ds.Tables["titleauthor"].Columns["au_id"],  
                    false);  
                ds.Relations.Add("titles_titleauthor",  
                    ds.Tables["titles"].Columns["title_id"],  
                    ds.Tables["titleauthor"].Columns["title_id"],  
                    false);  
                foreach (DataRow row in ds.Tables["authors"].Rows)  
                {  
                    Console.WriteLine("{0}, {1}", row["au_lname"], row["au_fname"]);  
                    foreach (DataRow rowTitleAuthor in row.GetChildRows("authors_titleauthor"))  
                    {  
                        DataRow rowTitle = rowTitleAuthor.GetParentRow("titles_titleauthor");  
                        Console.WriteLine("\t" + rowTitle["title"]);  
                    }  
                }  
                Console.ReadLine();  
            }  
        }  
    }  
     

    John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com
    Friday, March 27, 2009 3:45 AM

All replies

  • I don't beleive the functionality you're talking about exists.  A way you can do this is by caching in each of the data in your app and then combining the data.  This seems like a DataSet question and so I will put this to the DataSet forum to see if there are others who would have better solutions.

    Thursday, March 26, 2009 5:16 PM
  • What you can do is pull data into two DataTables from independent Data Providers (Oracle, SQL Server) that create a DataSet, push the DataTables into the DataSet, then if they are "joinable", create a DataRelation and make the join.

    Here is an example of having a DataRelation on a many to many relation which you could potentially tweak to incorporate your two DataTables:

    //Just an Example

    using System;  
    using System.Collections.Generic;  
    using System.Linq;  
    using System.Text;  
    using System.Data.SqlClient;  
    using System.Data;  
     
    namespace ConsoleApplication1  
    {  
        class Program  
        {  
            static void Main(string[] args)  
            {  
                ForumExample();  
            }  
     
            private static void ForumExample()  
            {  
                string connString, sql;  
                connString = @"Data Source=JOHN\JOHNGROVE;Initial Catalog=pubs;Integrated Security=SSPI";  
                SqlConnection cn = new SqlConnection(connString);  
                cn.Open();  
                SqlDataAdapter daAuthors, daTitles, daTitleAuthor;  
                sql = "SELECT au_id, au_lname, au_fname FROM authors";  
                daAuthors = new SqlDataAdapter(sql, cn);  
                sql = "SELECT title_id, title FROM titles";  
                daTitles = new SqlDataAdapter(sql, cn);  
                sql = "SELECT au_id, title_id FROM titleauthor";  
                daTitleAuthor = new SqlDataAdapter(sql, cn);  
                DataSet ds = new DataSet();  
                daAuthors.Fill(ds, "authors");  
                daTitles.Fill(ds, "titles");  
                daTitleAuthor.Fill(ds, "titleauthor");  
     
                //Add relations  
                ds.Relations.Add("authors_titleauthor",  
                    ds.Tables["authors"].Columns["au_id"],  
                    ds.Tables["titleauthor"].Columns["au_id"],  
                    false);  
                ds.Relations.Add("titles_titleauthor",  
                    ds.Tables["titles"].Columns["title_id"],  
                    ds.Tables["titleauthor"].Columns["title_id"],  
                    false);  
                foreach (DataRow row in ds.Tables["authors"].Rows)  
                {  
                    Console.WriteLine("{0}, {1}", row["au_lname"], row["au_fname"]);  
                    foreach (DataRow rowTitleAuthor in row.GetChildRows("authors_titleauthor"))  
                    {  
                        DataRow rowTitle = rowTitleAuthor.GetParentRow("titles_titleauthor");  
                        Console.WriteLine("\t" + rowTitle["title"]);  
                    }  
                }  
                Console.ReadLine();  
            }  
        }  
    }  
     

    John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com
    Friday, March 27, 2009 3:45 AM
  • There is no automated (wizard) functionality for it in Visual Studio designer, but you can query data from SQL Server and Oracle using your own code (with SqlClient and OracleClient providers) and load data into two DataTables. Since DataTable are database-agnostic (they do not depend on any type of database), you could place them into single DataSet. If you need to merge data into single DataTable then you could use Merge method of DataTable or you could use LINQ for this purpose.
    Val Mazur (MVP) http://www.xporttools.net
    Wednesday, April 1, 2009 10:15 AM
    Moderator