combining (using a union) on two data sets

1DMF · Mar 25, 2011

hi,

Is it possible to write a SQL query which creates a union between two data sets?

I have two SQL servers in two different locations, I currently use perl, and so linking tables with union queries across servers is not possible.

If I switch to writing my apps/reports in VB/.NET , could I create two data sets from separate querries, and then link those together with a 3rd SQL union query, so I could then bind to the final data set which would have all the data I need from both SQL servers?

Hope that makes sense and is possible.

Cheers,

1DMF.

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

Meleagant · Mar 25, 2011

Look into using the DataRelation Class. What I've done is create one dataset with multiple data tables which are the results of my queries.

Here is a small sample:

Code:

Dim dRel As New DataRelation("RelationName", ds.Tables("DS1").Columns("ParentColumn"), ds.Tables("DS2").Columns("ChildColumn"), False)
ds.Relations.Add(dRel)

Then to get the data, I would loop through the first data table and use GetChildRows to get the related rows from the second/third/etc datatable.

Code:

Dim dr() As DataRow
Dim myValue as String = ""
For Each dr As DataRow In ds.Tables("DS1").Rows

     dr = dr.GetChildRows(dRel)
     If dr.Length > 0 Then
          myValue = dr(0).Item(1).ToString.Trim
     Else
          myValue = ""
     End If

     ' ... Add'l Statements

Next

* Sine scientia ars nihil est
* Respondeat superior

1DMF · Mar 25, 2011

sorry you've lost me a bit.

These data sets ds1/ds2 you seem to to show them containing multiple tables.

how do you have a query that returns tables not records and columns?

I've only just got to databases in my VB OU course, and from what I can tell.

You create a binding source and set the datasource to the database, and then set a data member to the table is that right, I'm a little confused with all this, it seems far simpler in other languages.

Would you then link the binding sources somehow and then run a standard SQL quiry with unions against them.

Or are you saying you create a new dataRelation object which links the two binding sources together and then run a query against the dataRelation?

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

Meleagant · Mar 25, 2011

Noticed an error in my code. The second code block should be this:

Code:

Dim drChildren() As DataRow
Dim myValue as String = ""
For Each dr As DataRow In ds.Tables("DS1").Rows
     drChildren = dr.GetChildRows(dRel)
     If drChildren.Length > 0 Then
          myValue = drChildren(0).Item(1).ToString.Trim
     Else
          myValue = ""
     End If
     ' ... Add'l Statements
Next

* Sine scientia ars nihil est
* Respondeat superior

Meleagant · Mar 25, 2011

Records and columns are tables.

What I've done is create one dataSet object. Then I fill that dataSet object with the results of multiple queries. A dataSet can hold multiple data tables. Once that is done you can "query" the children dataTables to get the related rows. This approach works great for processing. But if you are binding to an HTML table/grid it may not work.

Here is an example of something I wrote. I had to de-identify alot of it so just bear that in mind.

I have 3 queries. One query that Selects from an Oracle table, and 2 queries from Sql server. These 3 queries are loaded into different DataTables in the same DataSet.

Now all of the results are in one object: the dataSet.

Looping through the Oracle data I can look up the related objects in the other two dataTables. (Think of doing this just like a Sql Join would work.)

If you want ALL of the records look into the Merge Method of the DataSet object.

Code:

  Dim retVal As New StringBuilder

        '* ** Oracle Connection String
        Dim ConnStr As String = String.Format("Data Source={0};Persist Security Info=True;User ID=userId;Password=pwd;Unicode=True", DataSource)

        Dim SqlStr As String = ""
        Dim sb As New StringBuilder

        Dim rdr As OracleDataReader = Nothing
        Dim oConn As OracleConnection
        Dim oCmd As OracleCommand
        Dim oAdapter As New OracleDataAdapter

        Dim Con As SqlConnection
        Dim DA As SqlDataAdapter
        DA = New SqlDataAdapter
        Con = New SqlConnection(SqlConnectionString)

        oConn = New OracleConnection(ConnStr)
        oCmd = New OracleCommand
        oCmd.Connection = oConn
        oCmd.CommandType = CommandType.Text

        oConn.Open()

        Dim sbSql As New StringBuilder
        sbSql.Append("Select GroupId, ProcCode, ProcDesc, RevCode, Cst.Method From MyOracleTable Where ... ")

        '* ** Fill The Oracle DataTable in the Dataset
        oCmd.CommandText = sbSql.ToString
        oAdapter.SelectCommand = oCmd

        Dim ds As New DataSet
        oAdapter.Fill(ds, "Oracle")

        '* ** Fill the Sql DataTable in the DataSet
        SqlStr = String.Format("exec SqlServerStoredProc1 {0}, {1}", Value1, Value2)

        DA = New SqlDataAdapter(SqlStr, Con)
        DA.Fill(ds, "Sql")

        '* ** Create the Data Relation
        Dim dRel1 As New DataRelation("Relation1", ds.Tables("Oracle").Columns("Method"), ds.Tables("Sql").Columns("MethodId"), False)
        ds.Relations.Add(dRel1)

        '* ** Get Another Sql Server Data Set
        SqlStr = String.Format("exec SqlServerStoredProc2 {0}, {1}", Value3, Value4)

        DA = New SqlDataAdapter(SqlStr, Con)
        DA.Fill(ds, "ThirdDataTable")

        '* ** Create the Data Relation
        Dim dRel2 As New DataRelation("Relation2", ds.Tables("Oracle").Columns("GroupId"), ds.Tables("ThirdDataTable").Columns("GroupId"), False)
        ds.Relations.Add(dRel2)

        Dim groupDesc As String = ""
        Dim methodDesc As String = ""
        Dim drForRel1() As DataRow
        Dim drForRel2() As DataRow

        '* ** Loop to concat the output
        For Each dr As DataRow In ds.Tables("Oracle").Rows

            '* ** Get The Child Rows for each dataRelation
            drForRel1 = dr.GetChildRows(dRel1)
            drForRel2 = dr.GetChildRows(dRel2)

            If drForRel1.Length > 0 Then
                methodDesc = drForRel1(0).Item(1).ToString.Trim
            Else
                methodDesc = ""
            End If

            If drForRel2.Length > 0 Then
                groupDesc = String.Format("{0}: {1}", dr("GroupId").ToString.Trim, drForRel2(0).Item(1).ToString.Trim)
            Else
                groupDesc = ""
            End If

            '* ** Add'l Processing

        Next

        '* ** Add'l Processing

* Sine scientia ars nihil est
* Respondeat superior

1DMF · Mar 25, 2011

Many thanks, it will take me a while to digest your code.

But hopefully will get me on my way.

It looks like you create a connection to the DB and then 'fill' and empty data set with the returned records.

I've only ever used a db connection then retrieved records into a recordset, so this is a little confusing at first glance.

Especially being OO as well!

Code:

DA = New SqlDataAdapter(SqlStr, Con)        
DA.Fill(ds, "Sql")

I take this is where DA is a connection to the actual DB using the contection string and the SQL select as 'SqlStr' though you use a stored procedure.

So DA is now a recordset?

then ds is an empty data set object, you then execute the method FILL on the DA object which populates the new dataset object 'ds', giving it a table name of 'Sql'.

So ds is now a data set containing a table 'Sql' which contains the records from the execution of the stored procedure.

Have I got it?

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

jebenson · Mar 25, 2011

Another possibility is using LINQ to do the multi-database query. Here's a link that shows how to do that:

http://www.codeproject.com/KB/dotnet/linqToSql5.aspx

For the basics on LINQ, just search Google for "vb .net linq tutorial" (without the quotes).

* * *

Yet another possibility is linking the tables from one database into the other database, then you can just do normal queries on different tables.

Link SQL Server databases:

http://www.eggheadcafe.com/software/aspnet/30132211/linked-tables.aspx

Note: the above link describes how to link databases using a stored procedure for this purpose. Linking can also be done in the Enterprise Manager.

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

combining (using a union) on two data sets

1DMF

Programmer

Meleagant

Programmer

1DMF

Programmer

Meleagant

Programmer

Meleagant

Programmer

1DMF

Programmer

jebenson

Technical User

Similar threads

Part and Inventory Search

Sponsor