Mar 07, 2011 02:35 PM|GMann|LINK
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:
RetMgrData(35, 1, 1)
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("@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
'Fill the dataset
Catch ex As Exception
'Write to log
Thanks for your help !