Two databases, two tables, merge datasets into GridView RSS

• • Reply
  

  mattcase

  Member

  380 Points

  538 Posts

  Two databases, two tables, merge datasets into GridView

  Feb 15, 2012 04:05 PM|LINK

  Hi,

  I am trying to query DB1 T1 (column1, column2, column3, column4) and query DB2 T1 (column1, column2) and merge the two datasets together. I will then bind this dataset to a GridView.  Column1 of both tables are the same and represent the primary key for their respective table.  My goal is to join DB2 T1 (column2) to DB1 T1.

  Does that makes sense?  Does anyone know how to do this?

  Thanks.

  Matt
• • Reply
  

  stevenbey

  All-Star

  16526 Points

  3378 Posts

  Re: Two databases, two tables, merge datasets into GridView

  Feb 15, 2012 04:12 PM|LINK

  Do the databases reside in the same instance of SQL Server?  If so you can do something like this:

  SELECT t1.*, t2.column2
  FROM DB1.dbo.T1 t1
  INNER JOIN DB2.dbo.T1 t2 ON t2.ID = t1.ID

  Steven Bey
  http://stevenbey.com
  
  Recursion: see Recursion
  
• • Reply
  

  mattcase

  Member

  380 Points

  538 Posts

  Re: Two databases, two tables, merge datasets into GridView

  Feb 15, 2012 05:04 PM|LINK

  Thanks for your reply. Actually, they are in seperate instances located on seperate servers.

  Matt
• • Reply
  

  Csharp22

  Member

  296 Points

  410 Posts

  Re: Two databases, two tables, merge datasets into GridView

  Feb 15, 2012 05:11 PM|LINK

  you should have a linked server configured between these two instances on those tables.

  http://msdn.microsoft.com/en-us/library/ms188279.aspx

  after that you can access like

  SELECT t1.*, t2.column2
  FROM instance1.DB1.dbo.T1 t1
  INNER JOIN instance2.DB2.dbo.T1 t2 ON t2.ID = t1.ID

  • Edited by Csharp22 on Feb 15, 2012 05:15 PM
• • Reply
  

  mattcase

  Member

  380 Points

  538 Posts

  Re: Two databases, two tables, merge datasets into GridView

  Feb 15, 2012 05:13 PM|LINK

  Thanks for your reply but I am in a shared hosting environment and cannot link the servers.

  Matt
• • Reply
  

  stevenbey

  All-Star

  16526 Points

  3378 Posts

  Re: Two databases, two tables, merge datasets into GridView

  Feb 15, 2012 05:18 PM|LINK

  mattcase

  Thanks for your reply but I am in a shared hosting environment and cannot link the servers.

  You'll have to do separate queries and then join them using LINQ.

  http://msdn.microsoft.com/en-us/library/bb397941.aspx

  Steven Bey
  http://stevenbey.com
  
  Recursion: see Recursion
  
• • Reply
  

  mattcase

  Member

  380 Points

  538 Posts

  Re: Two databases, two tables, merge datasets into GridView

  Feb 15, 2012 05:36 PM|LINK

  Wow, that is way over my head.  Thanks for taking the time to post your reply.  However, could you maybe give me some guidance how to apply this to my situation?

  After the query I am trying to bind all data to my GridView.

  Here are the queries I am trying to run:

  '''Query from DB1 TB1
  
  Dim connstr As String = ConfigurationManager.ConnectionStrings("String1").ToString()
              Dim sqlStr As String = "SELECT T1.Col1, T1.Col2, T2.Col3, T2.Col4, T1.Col5 AS Name, CONVERT(CHAR(12), T1.DateTimeCreated, 107) AS Date, T1.DateTimeReceived AS Date2, T1.Col6 FROM Table T1 RIGHT OUTER JOIN Table T2 ON T1.Col6 = T2.Col2 WHERE T1.Col6 = '" & HttpContext.Current.User.Identity.Name & "' ORDER BY T1.DateTimeCreated DESC"
              Dim dt As New DataTable
              Dim dataAdapter As New SqlClient.SqlDataAdapter(sqlStr, connstr)
              dataAdapter.Fill(dt)
              dataAdapter.Dispose()
              MyGrid.DataSource = dt
              MyGrid.Columns(3).Visible = True
              MyGrid.Columns(4).Visible = True
              MyGrid.Columns(9).Visible = True
              MyGrid.Columns(10).Visible = True
              MyGrid.DataBind()
              MyGrid.Columns(3).Visible = False
              MyGrid.Columns(4).Visible = False
              MyGrid.Columns(9).Visible = False
              MyGrid.Columns(10).Visible = False
  
  '''Query from DB2 TB2
  
  Dim connstr As String = ConfigurationManager.ConnectionStrings("String2").ToString()
              Dim sqlStr As String = "SELECT Col1, Col2 FROM Table WHERE Col3 = '" & HttpContext.Current.User.Identity.Name & "'"
              Dim dt As New DataTable
              Dim dataAdapter As New SqlClient.SqlDataAdapter(sqlStr, connstr)
              dataAdapter.Fill(dt)
              dataAdapter.Dispose()
              MyGrid.DataSource = dt
              MyGrid.DataBind()
  
  
  '''Column1 from both queries are the keys for each.  They are identical columns.

  Matt
• • Reply
  

  mattcase

  Member

  380 Points

  538 Posts

  Re: Two databases, two tables, merge datasets into GridView

  Feb 21, 2012 03:17 PM|LINK

  UPDATE

  I have made a little progress, but I am receiving the following error:

  Unable to cast object of type '<JoinIterator>d__61`4[System.Data.DataRow,System.Data.DataRow,System.Object,VB$AnonymousType_0`2[System.Data.DataRow,System.Data.DataRow]]' to type 'System.Collections.Generic.IEnumerable`1[System.Data.DataRow]'.

  Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
  
  Exception Details: System.InvalidCastException: Unable to cast object of type '<JoinIterator>d__61`4[System.Data.DataRow,System.Data.DataRow,System.Object,VB$AnonymousType_0`2[System.Data.DataRow,System.Data.DataRow]]' to type 'System.Collections.Generic.IEnumerable`1[System.Data.DataRow]'.
  
  Source Error:
  
  

  Line 114: 'Dim finalTable As New DataTable Line 115: Line 116: Dim joinedData As Generic.IEnumerable(Of DataRow) = (From m In dt.AsEnumerable() _

  Does anyone know how I could fix this?  My code is pulled from this link:  http://stackoverflow.com/questions/5587780/linq-to-dataset-joins-and-gridview-issues

  Here is my code:

  Dim connstr As String = ConfigurationManager.ConnectionStrings("String1").ToString()
              Dim sqlStr As String = "SELECT T1.Col1, T1.Col2, T2.Col3, T2.Col4, T1.Col5 AS ColumnName, CONVERT(CHAR(12), T1.Col6, 107) AS Date, T1.Col7 AS Date2, T1.Col8 FROM T1 RIGHT OUTER JOIN T2 ON T1.Col8 = T2.Col1 WHERE T1.Col8 = '" & HttpContext.Current.User.Identity.Name & "' ORDER BY T1.Col6 DESC"
              Dim dt As New DataTable
              Dim dataAdapter As New SqlClient.SqlDataAdapter(sqlStr, connstr)
              dataAdapter.Fill(dt)
              dataAdapter.Dispose()
              
  
              Dim Keyconnstr As String = ConfigurationManager.ConnectionStrings("String2").ToString()
              Dim sqlStrKey As String = "SELECT Col1, Col2 FROM T1 WHERE VariableName = '" & HttpContext.Current.User.Identity.Name & "'"
              Dim dtKey As New DataTable
              Dim dataAdapterKey As New SqlClient.SqlDataAdapter(sqlStrKey, Keyconnstr)
              dataAdapterKey.Fill(dtKey)
              dataAdapterKey.Dispose()
              
  
              Dim joinedData As Generic.IEnumerable(Of DataRow) = (From m In dt.AsEnumerable() _
                                                       Join a In dtKey.AsEnumerable() _
                                                       On a.Item("Col1") Equals m.Item("Col1"))
  
              MyGrid.DataSource = joinedData.CopyToDataTable()
              MyGrid.Columns(3).Visible = True
              MyGrid.Columns(4).Visible = True
              MyGrid.Columns(9).Visible = True
              MyGrid.Columns(10).Visible = True
              MyGrid.DataBind()
              MyGrid.Columns(3).Visible = False
              MyGrid.Columns(4).Visible = False
              MyGrid.Columns(9).Visible = False
              MyGrid.Columns(10).Visible = False

  Matt
• • Reply
  

  mattcase

  Member

  380 Points

  538 Posts

  Re: Two databases, two tables, merge datasets into GridView

  Feb 21, 2012 08:28 PM|LINK

  I have resolved this issue. The following helped me to achieve the desired result:

  Dim query = From t1 In dt.AsEnumerable() Join t2 In dtKey.AsEnumerable() On t1.Field(Of String)("Col1") Equals _
                  t2.Field(Of String)("Col1") Select New With { _
                          .Name1 = t1.Field(Of String)("Col1"), _
                          .Name2 = t1.Field(Of String)("Col2"), _
                          .Name3 = t1.Field(Of String)("Col3")}

  Matt
  • Marked as answer by mattcase on Feb 21, 2012 08:28 PM
• • Reply
  

  cadfish1

  Member

  2 Points

  1 Post

  Re: Two databases, two tables, merge datasets into GridView

  Apr 03, 2012 05:18 PM|LINK

  What datatype is 'query' please?  In my app 'Join' is defined as Microsoft.VisualBasic.Strings.Join.  Is your datatype the same or something else (what exactly?)?  If so, does your above code mearge the tables somehow even thought Join returns a string created by joining a number of substrings contained in an array?  In other words, you're not using the returned data from Join but instead you're using tit to merge the tables, true?

  I'm a rookie so would you mind showing the final code (all of the data queries and merging code)?