Last post Mar 07, 2011 09:40 PM by GMann
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 !
Mar 07, 2011 03:03 PM|Naom|LINK
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
How to get information about all databases without a loop
Mar 07, 2011 09:40 PM|GMann|LINK
Thanks Naom !