C# IDatabase.Escape方法代码示例

        public override void Execute(IDatabase database)
        {
            if (!Table.Columns.Any())
            {
                throw new InvalidOperationException("Cannot create a table with no columns.");
            }

            var sql = new SqlBuilder();
            sql.Append("if not ({0}) begin create table {1} (",
                GetTableExistsSql(database, Table.Name),
                database.FormatTableNameAndEscape(Table.Name));

            var i = 0;
            foreach (var column in Table.Columns)
            {
                var sqlBuilder = new SqlBuilder()
                    .Append(i > 0, ",")
                    .Append(database.Escape(column.Name))
                    .Append(GetColumnSqlType(column, i.ToString()));

                sql.Append(sqlBuilder);
                i++;
            }

            sql.Append("); end;");

            database.RawExecute(sql.ToString());
        }

        public override void Execute(IDatabase database)
        {
            // Bug in SQL server 2012 prevents us to query SYS.DEFAULT_CONSTRAINTS for temp tables. Furthermore it is only possible to query
            // the table sys.objects$ and the value parent_object_id of sys.objects if using DAC, for which reason it is not possible to get
            // column constraints on a specific column.
            // See: https://connect.microsoft.com/SQLServer/feedback/details/765777/sys-default-constraints-empty-for-temporary-tables-in-tempdb
            // See: http://www.sqlservercentral.com/Forums/Topic1359991-3077-1.aspx
            if (database is SqlServerUsingTempTables)
                throw new InvalidOperationException("It is currently not possible to remove columns on temp tables.");

            var tableName = database.FormatTableNameAndEscape(Table.Name);
            var dropConstraints = "DECLARE @ConstraintName nvarchar(200) " +
                                  "SELECT @ConstraintName = Name FROM SYS.DEFAULT_CONSTRAINTS " +
                                  "WHERE PARENT_OBJECT_ID = OBJECT_ID('" + tableName + "') " +
                                  "AND PARENT_COLUMN_ID = (SELECT column_id FROM sys.columns WHERE NAME = N'" + Name + "' AND object_id = OBJECT_ID(N'" + tableName + "')) " +
                                  "IF @ConstraintName IS NOT NULL " +
                                  "EXEC('ALTER TABLE " + tableName + " DROP CONSTRAINT ' + @ConstraintName) ";

            //Solutions for dropping column constraints on temp tables, when either SQL server bug is fixed or DAC connection is used
            //var queryForTempTableAsItShouldBe = "DECLARE @ConstraintName nvarchar(200) " +
            //                   "SELECT @ConstraintName = Name FROM tempdb.SYS.DEFAULT_CONSTRAINTS " +
            //                   "WHERE PARENT_OBJECT_ID = OBJECT_ID(N'tempdb.." + tableName + "') " +
            //                   "AND PARENT_COLUMN_ID = (SELECT column_id FROM tempdb.sys.columns WHERE NAME = N'" + Name + "' AND object_id = OBJECT_ID(N'tempdb.." + tableName + "')) " +
            //                   "IF @ConstraintName IS NOT NULL " +
            //                   "EXEC('ALTER TABLE tempdb.." + tableName + " DROP CONSTRAINT ' + @ConstraintName) ";
            //var queryForTempTableAsAlternative = "DECLARE @ConstraintName nvarchar(200) " +
            //                   "SELECT @ConstraintName = Name FROM tempdb.sys.objects " +
            //                   "WHERE type = 'D ' AND parent_object_id <> 0 " +
            //                   "AND PARENT_COLUMN_ID = (SELECT column_id FROM tempdb.sys.columns WHERE NAME = N'" + Name + "' AND object_id = OBJECT_ID(N'tempdb.." + tableName + "')) " +
            //                   "IF @ConstraintName IS NOT NULL " +
            //                   "EXEC('ALTER TABLE tempdb.." + tableName + " DROP CONSTRAINT ' + @ConstraintName) ";

            database.RawExecute(dropConstraints);                
            database.RawExecute(string.Format("alter table {0} drop column {1};", database.FormatTableNameAndEscape(Table.Name), database.Escape(Name)));
        }

        public override void Execute(IDatabase database)
        {
            var sql = new SqlBuilder();
            sql.Append("alter table {0} add {1}", database.FormatTableNameAndEscape(Tablename), database.Escape(Column.Name));
            sql.Append(GetColumnSqlType(Column));

            database.RawExecute(sql.ToString());
        }

        public override void Execute(IDatabase database)
        {
            var builder = new SqlBuilder();
            var formattedAndEscapedTableName = database.FormatTableNameAndEscape(TableName);
            var escapedColumnName = database.Escape(Column.Name);

            //DROP CONSTRAINTS
            foreach (var constraintName in (database is SqlServerUsingTempTables ? tempTablesConstraintQueries : constraintQueries)
                .Select(x => database.RawQuery<string>(x, new { TableName = database.FormatTableName(TableName), ColumnName = Column.Name }))
                .Select(x => x.SingleOrDefault())
                .Where(x => x != null))
            {
                builder.Append("ALTER TABLE {0} DROP CONSTRAINT {1};", formattedAndEscapedTableName, database.Escape(constraintName));
            }

            builder
                //ALTER TABLE
                .Append(GetAlterTableStatement(formattedAndEscapedTableName, escapedColumnName))

                //DEFAULT CONSTRAINT
                .Append(Column.DefaultValue != null, "ALTER TABLE {0} ADD DEFAULT '{1}' FOR {2};", formattedAndEscapedTableName, Column.DefaultValue, escapedColumnName)

                //PRIMAY KEY
                .Append(Column.IsPrimaryKey, @"ALTER TABLE {0} ADD PRIMARY KEY ({1});", formattedAndEscapedTableName, escapedColumnName);

            database.RawExecute(builder.ToString());
        }