Can We Integrate Visual Basic.NET, SQL Server 2000 and WMI? - How to retrieve “Databases” information in SQL Server instance
(Page 3 of 4 )
Now we come to dealing with SQL Server information. How do we retrieve the “databases” available in SQL Server 2000 using WMI? Actually it is simpler than you might have expected. Let us go through the following code:
Private Sub btnListDatabases_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnListDatabases.Click
Dim PrivateLateBoundClass As ManagementClass
PrivateLateBoundClass = New ManagementClass(Nothing, New ManagementPath("\\server\root\MicrosoftSQLServer:
MSSQL_Database"), Nothing)
Dim dbs As ManagementObjectCollection = PrivateLateBoundClass.GetInstances()
Dim db As ManagementObject
Dim sdb As String
For Each db In dbs
sdb &= db("SQLServerName").ToString & " - " & db("Name").ToString() & ControlChars.NewLine
Next
MessageBox.Show(sdb)
End Sub
Now, I am working on the “ManagementClass” rather than the “ManagementObject”. Even though I didn’t specify that it needs to connect to “(local)”, it would automatically get connected to the local database!
“MSQL_Database” is the second WMI class I used now (in the above code), to retrieve all of the database names existing in the local SQL Server 2000 instance. As there may exist more than one database, I need to work with the “collection” (which is why I used “ManagementObjectCollection”). The following is the loop, which helps us to retrieve all the database names automatically.
Dim dbs As ManagementObjectCollection = PrivateLateBoundClass.GetInstances()
Dim db As ManagementObject
Dim sdb As String
For Each db In dbs
sdb &= db("SQLServerName").ToString & " - " & db("Name").ToString() & ControlChars.NewLine
Next
“GetInstances” now would retrieve all “database” instances. The loop iterates through each database instance, and appends the value of “SQLServerName” and “Name” (database name), which are WMI properties, to the string “sdb”. Finally, it displays a message box with the string (list of database names) we framed.
Next: How to retrieve “tables” information in SQL Server instance >>
More Visual Basic.NET Articles
More By Jagadish Chaterjee
