StrataFrame Forum

UNION Select across multiple data connections?

http://forum.strataframe.net/Topic29780.aspx

By William Fields - 3/25/2011

Hello,

I have a BO (my first, yea!) that grabs the data I'm looking for and all is well in the universe.

Now, I have a requirement to combine data from multiple data sources (same table structure, but the data is living on different SQL Servers) and I'm figuring a UNION would give me what I need.  This is a read-only scenario, so I don't need to worry about updates being sent back to the different servers (yet).

I have not gone through the process of adding additional data sources to my project yet, but I will review the documentation on how to set that up. After that, I guess the trick is how to select data from different servers into the BO dataset...

Any suggestions?

Thanks.
By Edhy Rijo - 3/25/2011

Hi William,

You will need to create a new connection for each source using a unique DataSourceKey, then in your BO via code change the DataSourceKey before filling the BO so it will connect to the correct data source.
By William Fields - 3/27/2011

OK, I've configured the two connections and can retrieve data from either, but my goal is to retrieve data from both and end up with a single DataTable in my BO.

So, in my BO ParentFormLoading event, I call:

Me.MyBO.GetData("Parm1","Parm2")

In the GetData method, I go through the process of building a SqlCommand object:

        '-- Establish locals
        Dim loCommand As New SqlCommand()
        '-- Build the query
        loCommand.CommandText = "SELECT * FROM OPENQUERY(Blah Blah Blah..."
        '-- Execute the command to fill the business object
        Me.DataSourceKey = "DataSource1"
        Me.FillDataTable(loCommand)

But of course, this just gives me data from one DataSource.

How would I go about retrieving data from both DataSources and end up with data in the BO as if it had come from just one? The best I can describe what I'm looking for would be a UNION select statement that grabs data across the two connections.

Any suggestions?

Thanks. 
By Edhy Rijo - 3/27/2011

Hi William,

I don't know about the UNION, but you can create several instance of your BO by code, fill each with data from different sources, then use the BO.CopyDataFrom method to combine all BOs in a single one, some like this:


Dim Mainbo1 as new MyBusinessObject
Dim TempBO2 as new MyBusinessObject
Dim TempBO3 as new MyBusinessObject

'-- Now fill TempBO2 and TempBO3 from different datasources
TempBO2.DataSourceKey = "VFP"
TempBO2.CustomFillMethod()

TempBO3.DataSourceKey = "SQL"
TempBO3.CustomFillMethod()

'-- Now combine bo2 and bo3 into bo1
MainBO1.CopyDataFrom(TempBO2, BusinessCloneDataType.AppendDataToTableFromDefaultView)
MainBO1.CopyDataFrom(TempBO3, BusinessCloneDataType.AppendDataToTableFromDefaultView)


The above is just a pseudo code so you can see the logic.  If you want to go this route, make sure you read about the CopyDataFrom method in the help file.  Also you may need to add validations to Clear and dispose the temporary BOs.
By Peter Jones - 3/27/2011

Hi William,

If your SQL servers are defined as Linked Servers in SQL than I presume you could also create your data using a sigle view and fully qualified table names for each Select in the UNION query.

Cheers, Peter
By William Fields - 3/28/2011

Yes, that was my original design intent, but I could not get it to work... Maybe I'm not formatting the query properly.

The data in each server is itself coming from a Linked Server (a VFP database). The intent is to use the SQL server on either side of a WAN connection to query the VFP data locally before sending the results over the WAN.

In this example, I'll reference SQL Server 1 as the "local" server and SQL Server 2 as the "remote" server.

I found that when I added the remote server as a linked server in the local server, I could not query the remote servers linked servers.

Meaning, this query executed on the local server in SSMS returns an error:

SELECT *

FROM [NameOfLinkedServerInLocalServer].[NameOfLinkedServerInRemoteServer]..[NameOfVFPTableInRemoteLinkedServer]

I've tried formatting the FROM in different ways, but everything comes back with an error. For example:

Msg 7314, Level 16, State 1, Line 1

The OLE DB provider "SQLNCLI10" for linked server "NameOfLinkedServerInLocalServer" does not contain the table ""NameOfLinkedServerInRemoteServer"."NameOfVFPTableInRemoteLinkedServer"". The table either does not exist or the current user does not have permissions on that table.

Is this type of query possible? If so, how would I format the FROM clause?

 

Thanks.
By Peter Jones - 3/28/2011

Hi William,

Hmmm, chained linked servers, that's beyond my level of knowledge. But no matter which way you go (BO / View) then you still need to solve the chained linked servers problem.

Cheers, Peter
By William Fields - 3/29/2011

Yes, the chained linked servers has me stumped too.

The CopyDataFrom BO approach works, so I'll move in that direction for now.

Thank you.
By Edhy Rijo - 3/29/2011

William Fields (3/29/2011)
Yes, the chained linked servers has me stumped too.
The CopyDataFrom BO approach works, so I'll move in that direction for now.


The logic behind the SF Business Object is pretty cool.  I have been able to do some complex routine with data manipulation using all available methods of the SF Business Object including the AcceptChanges, Sort, Filter and of course the bo.GetEnumerable().

In your case you can probably build a Stored Procedure to get the data from different sources and use the BusinessLayer.FillMultipleDataTable() to get all the data at once.