Query with tables from Multiple Connections

[cojiro]

Here's the situation:
I have two DB connections ConnA (DB2) and ConnB (SQL Server).

I need to bind a datagrid to the results of a query which joins a table from ConnA to a table from ConnB.

Can someone please tell me how I should be going about this?

I'm relatively new to asp.net so I'm only familiar with associating a Command object with a single DBconnection (then binding the datagrid with a datareader). It doesn't seem like the good old standby is going to work in this case...

 

[ca8msm]

You have the same options as were listed in this thread: thread855-1452503


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[cojiro]

Thanks!

 

[cojiro]

I looked through all of the examples on the web for relating tables to join them and they all were a little tough to understand. This may not be the best way to do this, but it's definitely very readable and it works. Maybe this will help someone in my situation...

    Sub JoinTables(ByRef GCC As DataTable, ByVal DEP As DataTable)
        'Function does a left join to populate GCC table with values from DEP support table
        Dim Rgcc As DataRow
        Dim Rdep As DataRow

        For Each Rgcc In GCC.Rows
            For Each Rdep In DEP.Rows
                'If statement below is the join criteria.  In this case I have to use a range.
                If Rgcc.Item("BATCH_CD") >= Rdep.Item("BatchCodeLeft") And Rgcc.Item("BATCH_CD") <= Rdep.Item("BatchCodeRight") Then
                    Rgcc.Item("GLAccount") = Rdep.Item("Account")
                    Rgcc.Item("Description") = Rdep.Item("Description")

                    Exit For 'no need to keep processing if row is already joined

                End If
            Next
        Next
    End Sub

 

[jmeckley]

definitely very readable

this is a relative to the person reading it. Personally I find this a bit wordy.

if you assign a primary key to the tables and create a realtionship between the 2 you essentially have an in memory database and can take full advantage of the dataset features.

it also appears that you are duplicating your efforts. why pull GLAccount and Description from Rgcc if they are present in Rdep? that's the point of the dataset. to relate the data to one another and remove duplication.

in reviewing the code above a cleaner option would be to use a dataview and filter the table. this removes the need of the inner loop

private DataView viewRelatedToSource;
public void JoinTable(DataTable destination, DataTable source)
{
   viewRelatedToSource = source.DefaultView;
   foreach(DataRow row in destination.Rows)
   {
      if (SourceContainsARowRelatingTo(row))
      {
         MapSourceValuesTo(row);
      }
   }
}

private bool SourceContainsARowRelatingTo(DataRow row)
{
   viewRelatedToSource.Filter = string.Format("'{0}' >= [BatchCodeLeft] AND '{0}' <= [BatchCodeRight]", row["Batch_CD"]);
   return viewRelatedToSource.Count == 1;
}

private void MapSourceValuesTo(DataRow row)
{
   row["GLAccount"] = viewRelatedToSource[0]["Account"];
   row["Description"] = viewRelatedToSource[0]["Description"];
}

Jason Meckley
Programmer
Specialty Bakers, Inc.