How to: Iterate Through the Members of a Collection in Visual Basic .NET
This section describes how to iterate through all the databases on an instance of SQL Server by using a collection in Visual Basic .NET.
The code example iterates through the Databases collection property and displays all database connections to the instance of SQL Server.
Iterating through a collection
Start Visual Studio 2005.
From the File menu, select New Project. The New Project dialog box appears.
In the Project Types pane, select Visual Basic. In the Templates pane, select Console Application.
(Optional) In the Name box, type the name of the new application.
Click OK to load the Visual Basic console application template.
On the Project menu, select Add Reference item. The Add Reference dialog box appears. Select Browse and locate the SMO assemblies in the C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies folder. Select the following files:
Microsoft.SqlServer.ConnectionInfo.dll
Microsoft.SqlServer.Smo.dll
Microsoft.SqlServer.SqlEnum.dll
Microsoft.SqlServer.SmoEnum.dll
On the View menu, click Code.-Or-Select the Module1.vb window to display the code window.
In the code, before any declarations, type the following Imports statements to qualify the types in the SMO namespace:
Imports Microsoft.SqlServer.Management.Smo Imports Microsoft.SqlServer.Management.CommonInsert the code that follows this procedure into the main program.
Run and build the application.
Example
'Connect to the local, default instance of SQL Server.
Dim srv As Server
srv = New Server
Dim count As Integer
Dim total As Integer
'Iterate through the databases and call the GetActiveDBConnectionCount method.
Dim db As Database
For Each db In srv.Databases
count = srv.GetActiveDBConnectionCount(db.Name)
total = total + count
'Display the number of connections for each database.
Console.WriteLine(count & " connections on " & db.Name)
Next
'Display the total number of connections on the instance of SQL Server.
Console.WriteLine("Total connections =" & total)