locked
How do I create mutiple records in a dataset from mutiple databases ? RRS feed

  • Question

  • User-49466106 posted

    Hi;

     

    I am trying to design my logic to do this but I want to create a dataSet which will have records from several databases,

    The records all have the same layout.

    I am reagin the connection string paths from a table in a database.

    I am asking for your help in trying figure out my logic.

    Should I use the connectionString builder in conjunction with a loop to Connect, read a record into a dataset Until therer are no more records to be read from my databse table with the database name/paths tables ?

    Here is my beginning code which deals with one database:

      

    Imports System
    Imports System.Data
    Imports System.Data.SqlClient
    Imports System.Configuration
    
    Module Module1
    
        Sub Main()
    
            RetMgrData(35, 1, 1)
    
        End Sub
    
        Public Function RetMgrData(ByVal officer As String, ByVal sysId As Int32, ByVal type As Int32) As DataSet
            ' Use string builder here to read from a databse table which contaions the SPROC name and Server\Database paths
            Dim cn As New SqlConnection(ConfigurationManager.ConnectionStrings("FirstConnectionString").ConnectionString)
            Dim wsCommand As New SqlCommand
            wsCommand.Connection = cn
            wsCommand.CommandType = CommandType.StoredProcedure
            'Loop here to read the SPROC - paths table
            wsCommand.CommandText = "usp_FirstDBCounts"
    
            wsCommand.Parameters.AddWithValue("@officer", officer)
            wsCommand.Parameters.AddWithValue("@sysID", sysId)    'system id
            wsCommand.Parameters.AddWithValue("@type", type)      'report type 
    
            Dim da As SqlDataAdapter = New SqlDataAdapter(wsCommand.CommandText, cn)
    
            da.SelectCommand = wsCommand
    
            Dim ds As New DataSet
            Try
                'Fill the dataset
                da.Fill(ds, "usp_ARToolboxCounts")
            Catch ex As Exception
                'Write to log 
            End Try
            Return ds
        End Function
    
    
    End Module

    Thanks for your help !

     

    Monday, March 7, 2011 2:35 PM

Answers

  • User1096912014 posted

    If you want to create a query that will gather information from multiple databases, you can do so by using dynamic query. Assuming the account you're using for your web application has rights to query all databases, you can use ideas I show in this blog post How to get information about all databases without a loop

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, March 7, 2011 3:03 PM

All replies

  • User1096912014 posted

    If you want to create a query that will gather information from multiple databases, you can do so by using dynamic query. Assuming the account you're using for your web application has rights to query all databases, you can use ideas I show in this blog post How to get information about all databases without a loop

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, March 7, 2011 3:03 PM
  • User-49466106 posted

    Thanks Naom !

    Monday, March 7, 2011 9:40 PM