Can We Integrate Visual Basic.NET, SQL Server 2000 and WMI? - How to retrieve “tables” information in SQL Server instance
(Page 4 of 4 )
Now we come to dealing with SQL Server table information. How do we retrieve the “tables” available in SQL Server 2000 database using WMI? Here, in this example, I am trying to filter based on a particular “database”. Let us go through the following code:
Private Sub btnListTables_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnListTables.Click
Dim PrivateLateBoundClass As ManagementClass
PrivateLateBoundClass = New ManagementClass(Nothing, New ManagementPath("\\server\root\MicrosoftSQLServer:
MSSQL_Table"), Nothing)
Dim tables As ManagementObjectCollection = PrivateLateBoundClass.GetInstances()
'loop the information to store in an array
Dim table As ManagementObject
Dim sTable As String
For Each table In tables
If table("DatabaseName") = "Northwind" Then
sTable &= table("SQLServerName").ToString & " - "
sTable &= table("DatabaseName").ToString & " - "
sTable &= table("Name").ToString() & ControlChars.NewLine
End If
Next
MessageBox.Show(sTable)
End Sub
Now, I am working on “ManagementClass” rather than “ManagementObject”. Even though, I didn’t specify that it needs to connect to “(local)”, it would automatically get connected to the local database!
“MSQL_Table” is the third WMI class I used now (in the above code), to retrieve all of the table names existing in the local SQL Server 2000 database. As there may exist more than one table in a single database (or more databases), 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 table names of a particular database “Northwind” automatically.
Dim tables As ManagementObjectCollection =
PrivateLateBoundClass.GetInstances()
'loop the information to store in an array
Dim table As ManagementObject
Dim sTable As String
For Each table In tables
If table("DatabaseName") = "Northwind" Then
sTable &= table("SQLServerName").ToString & " - "
sTable &= table("DatabaseName").ToString & " - "
sTable &= table("Name").ToString() & ControlChars.NewLine
End If
Next
“GetInstances” now would retrieve all “tables” available. The loop iterates through each table or WMI instance, and appends the value of “SQLServerName” , “DatabaseName” and “Name” (table name), which are WMI properties, to the string “sTable”. Finally, it displays a message box with the string (list of table names) we framed. You can also observe that I filtered the tables of a particular database using an “IF” condition.
Summary:
I hope now you can confirm that we can use WMI to interact with SQL Server 2000 together with the .NET framework. As this is an introductory article, I could not go up to the level of “managing SQL Server 2000”. But you can expect a few more articles on this subject soon. Check regularly on this site or sign up for the newsletter.
You can further enhance the coding I provided with list boxes, dropdown lists, and so forth. You can also shift this code to ASP.NET. And that gives us a way to manage SQL Server 2000 using a web application with WMI!
Any comments, suggestions, bugs, errors, feedback etc. are highly appreciated at jag_chat@yahoo.com.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
