Creating SQL Server Components Using SQL-DMO Collections
Using SQL-DMO to create a Microsoft® SQL Server™ component is always a three-step process. The application:
- Requests a new object from SQL-DMO.
- Configures the object to reflect the desired attributes of the SQL Server component.
- Adds the appropriately configured object to the containing collection.
When an application modifies SQL-DMO collection membership by adding objects, SQL-DMO attempts to convert the application action to an appropriate SQL Server component creation Transact-SQL script.
Adding a SQL-DMO object to its containing collection can cause an immediate update of the indicated server running SQL Server. In other instances, the same application action can cause a delayed update of the indicated server.
For example, adding a Column object to the Columns collection of a new Table object generates no Transact-SQL statement. Instead, the properties of Column objects in the collection define the attributes of columns in a CREATE TABLE statement submitted when the Table object is added to a Tables collection.
By default, SQL-DMO generates a Transact-SQL ALTER TABLE statement when a new, configured Column object is added to the Columns collection referencing the columns of an existing SQL Server table.
When the application uses the BeginAlter method of the Table object, adding a Column object to the Columns collection does not generate an ALTER TABLE statement. The referenced SQL Server table is modified by an ALTER TABLE statement created and submitted when the application invokes the DoAlter method of the Table object.
SQL-DMO performs some error checking for object consistency when a new object is added to a containing collection. For example, SQL-DMO checks to ensure that the Name and data type defining properties of a Column object are set and valid when the Column object is added to the Columns collection of a Table object.
Other errors can occur as the component-creating script is submitted to SQL Server. For example, when defining a new column in an existing table, the default error checking provided by SQL-DMO does not attempt to validate column null acceptance. As SQL Server is the ultimate arbiter of null acceptance, SQL-DMO relies on SQL Server for error determination in this case.
Important A SQL Server administrative action directed by collection membership modification can be time-consuming and can fail. Applications that allow collection membership change should notify the user through a message or busy pointer, and should provide appropriate error handling.