using-collections.md

title	Using Collections | Microsoft Docs
ms.custom
ms.date	06/13/2017
ms.prod	sql-server-2014
ms.reviewer
ms.technology
ms.topic	reference
topic_type	apiref
helpviewer_keywords	SQL Server Management Objects, collections SMO [SQL Server], collections collections [SMO]
ms.assetid	209eb175-2514-4de1-bc32-b2e6a469d945
author	stevestein
ms.author	sstein
manager	craigg

Using Collections

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] [!INCLUDEvbprvb] For...Each statement or the [!INCLUDEmsCoName] [!INCLUDEcsprcs] foreach statement can be used to iterate through each member of the collection.

Examples

[!INCLUDEssChooseProgEnv]

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.

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("AdventureWorks2012").Tables("Person", "Person").Columns("LastName").Nullable = true;   
//Call the Alter method to make the change on the instance of SQL Server.   
srv.Databases("AdventureWorks2012").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].

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);   
}