none
Querying multiple data sources into a single DataTable RRS feed

  • Question

  • I'm trying to devise an application that will query multiple sources and put these data into a single table.  The multiple sources are three remote SQL servers.  Each remote database table is set up exactly the same, and I'm only querying two bits of data from each table anyway.

     

    I would like to put the results of all three queries into a single TABLE.  Not a dataSET; I've done that.  But because of the needs of the application, I need to put all the results into a single table.  I'm well aware that a UNION statement will join the data together; but I don't know how to query three different remote sites with a single UNION statement.


    I have brought each query into the same dataset, but in different tables.  Now I need to figure out how to merge this data into one.

     

    The application is being written in VB.NET 1.1.  That's all I'm allowed to write it in.

     

    Thanks.

    Tuesday, October 23, 2007 6:30 PM

Answers

  •  

    The key point is that you are loading data into the same DataSet. If you still get three tables in the DataSet, you can specify the target table to load as an overload of Fill (e.g., specify the first table in the DataSet).

     

    This is true; however, this doesn't merge the data into a single table.  The problem is that I am not cross-referencing the data, I am editing the data on two passes, and I have to treat it like continuous data, not "related" data.

     

    However, I have successfully done this by iterating through each table and adding each row to another table.  Not the most elegant solution, but efficacious.

    Thursday, October 25, 2007 4:25 PM

All replies

  • Hello,

     

    You have a number of options in this situation. The first is a server side option, where you create linked servers in one SQL instance that points to the other servers, then issue the union query on that server. SQL Server will handle pulling all the results into a single table.

     

    Another option is to do it on the client. When you configure a DataAdapter, it has the SQL that is generated as well as the connection string. You can modify both of these at runtime, so you can use the same DataAdapter for all three servers by changing the connection string and SQL command.

     

    Let me know if this makes sense or you have any questions.

     

    Thanks,

    Erick

     

    Wednesday, October 24, 2007 12:24 AM
  • Thanks for the reply.  The first suggestion is not an option; I don't have access to link the servers like that.  Do you have a code example of how to do the second?  Presently, I have a function into which I pass the name of a server and bring back a dataset.  How would I change it to reflect your suggestion?

    Thanks.  Here's the function code:

     

     

    Function GetDataSet(ByVal sqlServerName As String) As dataSet

    Try

    Dim connString As String = "Data Source=" & sqlServerName & ";" & _

    "Initial Catalog=DBName;" & _

    "Integrated Security=SSPI;" & _

    "Trusted_Connection=yes;"

    Dim commandString As String = "SELECT * FROM TableName"

    Dim sqlConnection As New SqlConnection(connString)

    Dim sqlCommand As New SqlCommand(commandString, sqlConnection)

    dataAdapter.SelectCommand = sqlCommand

    sqlConnection.Open()

    Dim returnDataSet As New DataSet

    dataAdapter.Fill(returnDataSet, sqlServerName)

    sqlConnection.Close()

    Return returnDataSet

    Catch ex As Exception

    Console.WriteLine("Error: {0}", ex.ToString)

    Return New DataSet

    End Try

    End Function

    Wednesday, October 24, 2007 1:43 PM
  •  

    Instead of returning a DataSet, you should change your function to fill an existing DataSet. Then, you create a single DataSet and call the method three times, once for each server. For example, you could structure your method like the following.

     

    Sub LoadDataSet(ByRef ds As DataSet, ByVal sqlServerName As String)

    Try

    Dim connString As String = "Data Source=" & sqlServerName & ";" & _

    "Initial Catalog=DBName;" & _

    "Integrated Security=SSPI;" & _

    "Trusted_Connection=yes;"

    Dim commandString As String = "SELECT * FROM TableName"

    Dim sqlConnection As New SqlConnection(connString)

    Dim sqlCommand As New SqlCommand(commandString, sqlConnection)

    Dim dataAdapter As SqlDataAdapter

    dataAdapter.SelectCommand = sqlCommand

    sqlConnection.Open()

    dataAdapter.Fill(ds)

    sqlConnection.Close()

     

    Catch ex As Exception

    Console.WriteLine("Error: {0}", ex.ToString)

    End Try

     

    The key point is that you are loading data into the same DataSet. If you still get three tables in the DataSet, you can specify the target table to load as an overload of Fill (e.g., specify the first table in the DataSet).

     

    Thanks,

    Erick

    Wednesday, October 24, 2007 5:50 PM
  • Hi,

     

    Another option is to use the Merge method of the DataTable class.  You can keep the code you currently have and then use that function to merge everything together.

     

    http://msdn2.microsoft.com/en-us/library/fk68ew7b.aspx

     

    Regards,

    Charles

     

    Thursday, October 25, 2007 12:27 AM
  •  

    Another option is to use the Merge method of the DataTable class.  You can keep the code you currently have and then use that function to merge everything together.

     

    http://msdn2.microsoft.com/en-us/library/fk68ew7b.aspx

     

     

    That would be great, if I had access to .NET 3.0.  But I don't.

     

    Thanks anyway though.

    Thursday, October 25, 2007 4:21 PM
  •  

    The key point is that you are loading data into the same DataSet. If you still get three tables in the DataSet, you can specify the target table to load as an overload of Fill (e.g., specify the first table in the DataSet).

     

    This is true; however, this doesn't merge the data into a single table.  The problem is that I am not cross-referencing the data, I am editing the data on two passes, and I have to treat it like continuous data, not "related" data.

     

    However, I have successfully done this by iterating through each table and adding each row to another table.  Not the most elegant solution, but efficacious.

    Thursday, October 25, 2007 4:25 PM
  • The merge method has been part of the DataTable class since .net version 2.0.  Here's a link to the documentation of version 2.0:

     

    http://msdn2.microsoft.com/en-us/library/fk68ew7b(vs.80).aspx

     

    Charles

    Friday, October 26, 2007 12:04 AM
  • Yes, thanks.  And if you'd read my original post, you'd know that I'm limited to using VS2003 and .NET 1.1.

    Friday, October 26, 2007 3:20 PM