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.

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.

Edhy Rijo

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. 

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.

Edhy Rijo