C# Table.Alter方法代码示例

        public static List<DroppedDependencyDbo> DeleteTheIndex(Database db, Table aTable, string indexName, bool disableDependencies)
        {
            Index ind = aTable.Indexes[indexName];
            if (ind == null)
                throw new Exception(String.Format("There is no index {0} in the {1} table.", indexName, aTable.Name));

            var droppedForeignKeys = new List<DroppedDependencyDbo>();
            if (disableDependencies)
                DropDependentForeignKeys(indexName, db, droppedForeignKeys);
            ind.Drop();
            aTable.Alter();

            return droppedForeignKeys;
        }

        public static void DeleteTheForeignKey(Database db, Table aTable, string fKeyName)
        {
            ForeignKey fKey = aTable.ForeignKeys[fKeyName];
            if (fKey == null)
                throw new Exception(String.Format("There is no foreign key {0} in the {1} table.", fKeyName, aTable.Name));

            fKey.Drop();
            aTable.Alter();
        }

        public void GrantAccessToSchemaObjects()
        {
            var helper = new TestHelper();
            try
            {
                var schema = helper.GetSchema();
                //schema.Owner = helper.GetUser().Name;
                //schema.Alter();

                var table = new Table(helper.GetDatabase(), "Table1", schema.Name);
                table.Columns.Add(new Column(table, "Col1", DataType.Int));
                table.Columns.Add(new Column(table, "Col2", DataType.NVarCharMax));
                table.Create();
                helper.AddCleanup(table);

                var view = new View(helper.GetDatabase(), "View1", schema.Name)
                    {
                        TextMode = false,
                        TextBody = String.Format("SELECT Col1, Col2 FROM [{0}].[{1}]", table.Schema, table.Name)
                    };
                //view.TextHeader = String.Format("CREATE VIEW [{0}].[{1}] AS", view.Schema, view.Name);
                view.Create();
                helper.AddCleanup(view);

                var scalarTsqlFn = new UserDefinedFunction(helper.GetDatabase(), "ScalarTsqlFunction", schema.Name)
                    {
                        TextMode = false,
                        DataType = DataType.DateTime,
                        ExecutionContext = ExecutionContext.Caller,
                        FunctionType = UserDefinedFunctionType.Scalar,
                        ImplementationType = ImplementationType.TransactSql,
                        TextBody = "BEGIN RETURN GETDATE() END"
                    };
                scalarTsqlFn.Create();
                helper.AddCleanup(scalarTsqlFn);

                var inlineTsqlFn = new UserDefinedFunction(helper.GetDatabase(), "InlineTsqlFunction", schema.Name)
                    {
                        TextMode = false,
                        ExecutionContext = ExecutionContext.Caller,
                        FunctionType = UserDefinedFunctionType.Inline,
                        ImplementationType = ImplementationType.TransactSql,
                        TextBody = String.Format("RETURN SELECT * FROM [{0}].[{1}]", view.Schema, view.Name)
                    };
                inlineTsqlFn.Create();
                helper.AddCleanup(inlineTsqlFn);

                // TODO: Create table valued function

                // TODO: Create Clr scalar func

                // TODO: Create Clr inline func (Exists?)

                // TODO: Create Clr table valued func

                // TODO: Create Clr Aggregate

                var proc = new StoredProcedure(helper.GetDatabase(), "sproc1", schema.Name)
                    {
                        TextMode = false,
                        AnsiNullsStatus = false,
                        QuotedIdentifierStatus = false,
                        TextBody = String.Format("SELECT * FROM [{0}].[{1}]()", inlineTsqlFn.Schema, inlineTsqlFn.Name)
                    };
                proc.Create();
                helper.AddCleanup(proc);

                // TODO: Create Clr Sproc

                // TODO: Create Constraint
                // TODO: Create Queue
                // TODO: Create Statistic
                // TODO: Create Synonym

                var user = helper.GetUser();

                var permissable = new IObjectPermission[]
                    {
                        table,
                        view,
                        scalarTsqlFn,
                        inlineTsqlFn,
                        proc,
                    };

                permissable.Do(tg => tg.GrantAll(user.Name));
                permissable.Do(tg => tg.DenyAll(user.Name));
                permissable.Do(tg => tg.RevokeAll(user.Name));

                // change all owners
                table.Owner = user.Name;
                table.Alter();

                view.Owner = user.Name;
                view.Alter();

                scalarTsqlFn.Owner = user.Name;
                scalarTsqlFn.Alter();

                inlineTsqlFn.Owner = user.Name;
//.........这里部分代码省略.........

        /// Establish a description for table provided
        private void SetTableDescription(Table updateTable, string description)
        {
            BeginAction(Resources.TableDescriptionBeginAction, 100);

            if (!updateTable.ExtendedProperties.Contains(SmoUtil.DESCRIPTION_PROPERTY))
            {
                updateTable.ExtendedProperties.Add(new ExtendedProperty(updateTable, SmoUtil.DESCRIPTION_PROPERTY, description));
            }
            else
            {
                updateTable.ExtendedProperties[SmoUtil.DESCRIPTION_PROPERTY].Value = description;
                updateTable.ExtendedProperties[SmoUtil.DESCRIPTION_PROPERTY].Alter();
            }

            updateTable.Alter();

            EndAction(Resources.TableDescriptionEndAction);
        }

        private static void RecreateColumn(Table aTable, DataColumnDbo column)
        {
            // Create new column
            bool oldIsNullable = column.IsNullable;
            string oldName = column.Name;
            column.IsNullable = true;
            column.Name = String.Format("{0}_{1}", oldName, CreateUniqueAppendix());
            Column newColumn = CreateColumn(aTable, column);
            aTable.Alter();

            // Copy data into new column
            string queryString = String.Format("UPDATE [{0}] SET [{1}] = [{2}]", aTable.Name, column.Name, oldName);
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                var command = new SqlCommand(queryString, connection);
                command.Connection.Open();
                command.ExecuteNonQuery();
                command.Connection.Close();
            }

            // Drop old column and rename new one.
            aTable.Columns[oldName].Drop();
            aTable.Alter();
            Column clmn = aTable.Columns[column.Name];
            if (!oldIsNullable)
                clmn.Nullable = false;
            column.IsNullable = oldIsNullable;

            clmn.Rename(oldName);
            clmn.Alter();
            aTable.Alter();
            column.Name = oldName;
        }