Skip to content

Latest commit

 

History

History
99 lines (86 loc) · 5.97 KB

using-collections.md

File metadata and controls

99 lines (86 loc) · 5.97 KB
title description author ms.author ms.date ms.service ms.topic ms.custom helpviewer_keywords monikerRange
Using Collections
Using Collections
markingmyname
maghan
08/06/2017
sql
reference
ignite-2024
SQL Server Management Objects, collections
SMO [SQL Server], collections
collections [SMO]
=azuresqldb-current || =azure-sqldw-latest || >=sql-server-2016 || >=sql-server-linux-2017 || =azuresqldb-mi-current || =fabric

Using Collections

[!INCLUDE SQL Server Azure SQL Database Azure SQL Managed Instance Synapse Analytics FabricSQLDB]

A collection is a list of objects that have been constructed from the same object class and that share the same parent object. The collection object always contains the name of the object type with the Collection suffix. For example, to access the columns in a specified table, use the xref:Microsoft.SqlServer.Management.Smo.ColumnCollection object type. It contains all the xref:Microsoft.SqlServer.Management.Smo.Column objects that belong to the same xref:Microsoft.SqlServer.Management.Smo.Table object.

The [!INCLUDEmsCoName] [!INCLUDEvisual-basic] For...Each statement or the [!INCLUDEmsCoName] [!INCLUDEc-sharp] foreach statement can be used to iterate through each member of the collection.

Examples

To use any code example that is provided, you will have to choose the programming environment, the programming template, and the programming language in which to create your application. For more information, see Create a Visual C# SMO Project in Visual Studio .NET.

Referencing an Object by Using a Collection in Visual Basic

This code example shows how to set a column property by using the xref:Microsoft.SqlServer.Management.Smo.TableViewTableTypeBase.Columns%2A, xref:Microsoft.SqlServer.Management.Smo.Database.Tables%2A, and xref:Microsoft.SqlServer.Management.Smo.Server.Databases%2A properties. These properties represent collections, which can be used to identify a particular object when they are used with a parameter that specifies the name of the object. The name and the schema are required for the xref:Microsoft.SqlServer.Management.Smo.Database.Tables%2A collection object property.

'Connect to the local, default instance of SQL Server.
Dim srv As Server
srv = New Server
'Modify a property using the Databases, Tables, and Columns collections to reference a column.
srv.Databases("AdventureWorks2022").Tables("Person", "Person").Columns("ModifiedDate").Nullable = True
'Call the Alter method to make the change on the instance of SQL Server.
srv.Databases("AdventureWorks2022").Tables("Person", "Person").Columns("ModifiedDate").Alter()

Referencing an Object by Using a Collection in Visual C#

This code example shows how to set a column property by using the xref:Microsoft.SqlServer.Management.Smo.TableViewTableTypeBase.Columns%2A, xref:Microsoft.SqlServer.Management.Smo.Database.Tables%2A, and xref:Microsoft.SqlServer.Management.Smo.Server.Databases%2A properties. These properties represent collections, which can be used to identify a particular object when they are used with a parameter that specifies the name of the object. The name and the schema are required for the xref:Microsoft.SqlServer.Management.Smo.Database.Tables%2A collection object property.

{   
//Connect to the local, default instance of SQL Server.   
Server srv;   
srv = new Server();   
//Modify a property using the Databases, Tables, and Columns collections to reference a column.   
srv.Databases["AdventureWorks2022"].Tables["Person", "Person"].Columns["LastName"].Nullable = true;   
//Call the Alter method to make the change on the instance of SQL Server.   
srv.Databases["AdventureWorks2022"].Tables["Person", "Person"].Columns["LastName"].Alter();   
}

Iterating Through the Members of a Collection in Visual Basic

This code example iterates through the xref:Microsoft.AnalysisServices.Server.Databases%2A collection property and displays all database connections to the instance of [!INCLUDEssNoVersion].

'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)

Iterating Through the Members of a Collection in Visual C#

This code example iterates through the xref:Microsoft.AnalysisServices.Server.Databases%2A collection property and displays all database connections to the instance of [!INCLUDEssNoVersion].

//Connect to the local, default instance of SQL Server.   
{   
Server srv = default(Server);   
srv = new Server();   
int count = 0;   
int total = 0;   
//Iterate through the databases and call the GetActiveDBConnectionCount method.   
Database db = default(Database);   
foreach ( 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);   
}   
//Display the total number of connections on the instance of SQL Server.   
Console.WriteLine("Total connections =" + total);   
}