本文整理汇总了C#中IDatabase.Execute方法的典型用法代码示例。如果您正苦于以下问题：C# IDatabase.Execute方法的具体用法？C# IDatabase.Execute怎么用？C# IDatabase.Execute使用的例子？那么恭喜您, 这里精选的方法代码示例或许可以为您提供帮助。您也可以进一步了解该方法所在类IDatabase的用法示例。
在下文中一共展示了IDatabase.Execute方法的15个代码示例，这些例子默认根据受欢迎程度排序。您可以为喜欢或者感觉有用的代码点赞，您的评价将有助于系统推荐出更棒的C#代码示例。
示例1: Up
public void Up(IDatabase db)
{
db.CreateTable(Tables[0].Name)
.WithPrimaryKeyColumn(Tables[0].Columns[0], DbType.Int32).AsIdentity()
.WithNotNullableColumn(Tables[0].Columns[1], DbType.Decimal).OfSize(3)
.WithNotNullableColumn(Tables[0].Columns[2], DbType.Decimal).OfSize(5, 2);
if (db.Context.ProviderMetadata.Name != ProviderNames.SQLite) // SQLite uses adaptiv algorithms for their data types: http://www.sqlite.org/datatype3.html
{
db.Execute(GetInsertStatement((decimal)Tables[0].Value(0, 2) + 0.003m)); // the extra precision should be cut off silently
db.Execute(context =>
{
IDbCommand command = context.Connection.CreateCommand();
command.Transaction = context.Transaction;
command.CommandText = GetInsertStatement(1000m);
Log.Verbose(LogCategory.Sql, command.CommandText);
try
{
command.ExecuteNonQuery();
Assert.Fail("The previous query should have failed.");
}
catch (DbException)
{
// this is expected
}
});
}
else
{
db.Execute(GetInsertStatement((decimal)Tables[0].Value(0, 2)));
}
}示例2: Up
public void Up(IDatabase db)
{
if (!this.IsFeatureSupported(db))
{
return;
}
const string schemaName = "Schema 29";
db.CreateSchema(schemaName);
db.Schemata[schemaName].CreateTable(Tables[0].Name)
.WithPrimaryKeyColumn("Id", DbType.Int32)
.WithNotNullableColumn("Column With Default", DbType.DateTime2).HavingCurrentDateTimeAsDefault();
db.Execute(string.Format(CultureInfo.InvariantCulture, @"INSERT INTO ""{0}"".""{1}"" (""{2}"") VALUES ('{3}')", schemaName, Tables[0].Name, Tables[0].Columns[0], 1));
// the following ALTER statement has to DROP the old default constraint and create the new one
db.Schemata[schemaName].Tables[Tables[0].Name].Columns["Column With Default"].AlterToNotNullable(DbType.DateTimeOffset).HavingCurrentDateTimeOffsetAsDefault();
// renaming the column also needs to rename the default constraint using the schema prefix
db.Schemata[schemaName].Tables[Tables[0].Name].Columns["Column With Default"].Rename("Column With Default II");
// check if the default constraint still works
db.Execute(string.Format(CultureInfo.InvariantCulture, @"INSERT INTO ""{0}"".""{1}"" (""{2}"") VALUES ('{3}')", schemaName, Tables[0].Name, Tables[0].Columns[0], 2));
}开发者ID:dradovic，项目名称:MigSharp，代码行数:25，代码来源:Migration29_Altering_DefaultConstraint_in_Custom_Schema.cs
示例3: Up
public void Up(IDatabase db)
{
if (!this.IsFeatureSupported(db))
{
return;
}
db.CreateTable(Tables[0].Name, "Mig22PrimaryKey") // parent table
.WithPrimaryKeyColumn(Tables[0].Columns[0], DbType.Int32).AsIdentity()
.WithNotNullableColumn(Tables[0].Columns[1], DbType.String).OfSize(255);
db.CreateTable(Tables[1].Name)
.WithPrimaryKeyColumn(Tables[1].Columns[0], DbType.Int32).AsIdentity()
.WithNotNullableColumn(Tables[1].Columns[1], DbType.Int32);
db.Tables[Tables[1].Name].AddForeignKeyTo(Tables[0].Name, "Mig22ChildForeignKey")
.Through(Tables[1].Columns[1], Tables[0].Columns[0])
.CascadeOnDelete();
db.Execute(string.Format(CultureInfo.InvariantCulture, @"INSERT INTO ""{0}"" (""{1}"") VALUES ('{2}')", Tables[0].Name, Tables[0].Columns[1], "Parent Row 1"));
db.Execute(string.Format(CultureInfo.InvariantCulture, @"INSERT INTO ""{0}"" (""{1}"") VALUES ('{2}')", Tables[0].Name, Tables[0].Columns[1], "Parent Row 2"));
db.Execute(string.Format(CultureInfo.InvariantCulture, @"INSERT INTO ""{0}"" (""{1}"") VALUES ('{2}')", Tables[1].Name, Tables[1].Columns[1], 1));
db.Execute(string.Format(CultureInfo.InvariantCulture, @"INSERT INTO ""{0}"" (""{1}"") VALUES ('{2}')", Tables[1].Name, Tables[1].Columns[1], 2));
db.Execute(string.Format(CultureInfo.InvariantCulture, @"DELETE FROM ""{0}"" WHERE ""{1}"" = 1", Tables[0].Name, Tables[0].Columns[0])); // removing the parent row should delete its child rows
}示例4: Up
public void Up(IDatabase db)
{
if (!this.IsFeatureSupported(db))
{
return;
}
MySqlHelper.ActivateStrictMode(db);
db.CreateTable(Tables[0].Name)
.WithPrimaryKeyColumn(Tables[0].Columns[0], DbType.Int32).AsIdentity()
.WithNotNullableColumn(Tables[0].Columns[1], DbType.Decimal).OfSize(3)
.WithNotNullableColumn(Tables[0].Columns[2], DbType.Decimal).OfSize(5, 2);
db.Execute(GetInsertStatement((decimal)Tables[0].Value(0, 2) + 0.003m)); // the extra precision should be cut off silently
db.Execute(context =>
{
IDbCommand command = context.CreateCommand();
command.CommandText = GetInsertStatement(1000m);
Log.Verbose(LogCategory.Sql, command.CommandText);
try
{
command.ExecuteNonQuery();
Assert.Fail("The previous query should have failed.");
}
catch (Exception x)
{
if (!x.IsDbException())
{
throw;
}
}
});
}示例5: Up
public void Up(IDatabase db)
{
db.CreateTable(Tables[0].Name)
.WithPrimaryKeyColumn(Tables[0].Columns[0], DbType.Int32).AsIdentity()
.WithNotNullableColumn(Tables[0].Columns[1], DbType.String);
db.Execute(GetInsertStatement((string)Tables[0].Value(0, 1)));
// Note: the following statement does *not* the identity constraint. Doing so is very difficult. For example, see: http://stackoverflow.com/questions/702745/sql-server-how-to-drop-identity-from-a-column
//db.Tables[TableName].Columns[ColumnNames[0]].AlterToNotNullable(DbType.Int32);
db.Tables[Tables[0].Name].Drop(); // make sure, TRIGGERS and SEQUENCES are dropped as well
db.CreateTable(Tables[0].Name)
.WithPrimaryKeyColumn(Tables[0].Columns[0], DbType.Int32)
.WithNotNullableColumn(Tables[0].Columns[1], DbType.String);
// inserting another row without specifying the Id value should fail as the Identity constraint is removed
if (db.Context.ProviderMetadata.Name != ProviderNames.SQLite) // SQLite automatically generates identity columns for PKs
{
db.Execute(context =>
{
// MySQL will not throw an error on insert unless strict mode is enabled
if (db.Context.ProviderMetadata.Name == ProviderNames.MySqlExperimental) {
IDbCommand command2 = context.Connection.CreateCommand();
command2.Transaction = context.Transaction;
command2.CommandText = "SET SQL_MODE = 'ANSI_QUOTES,STRICT_ALL_TABLES'";
command2.ExecuteNonQuery();
}
IDbCommand command = context.Connection.CreateCommand();
command.Transaction = context.Transaction;
command.CommandText = GetInsertStatement((string)Tables[0].Value(0, 1));
Log.Verbose(LogCategory.Sql, command.CommandText);
try
{
command.ExecuteNonQuery();
Assert.Fail("The previous query should have failed.");
}
catch (Exception x)
{
if (!x.IsDbException())
{
throw;
}
}
});
}
db.Execute(GetInsertStatement((int)Tables[0].Value(0, 0), (string)Tables[0].Value(0, 1)));
db.Tables[Tables[0].Name].Drop(); // make sure, TRIGGERS and SEQUENCES are dropped as well
// recreating the table with the identity constraint again might reveal undropped TRIGGERS or SEQUENCES
db.CreateTable(Tables[0].Name)
.WithPrimaryKeyColumn(Tables[0].Columns[0], DbType.Int32).AsIdentity()
.WithNotNullableColumn(Tables[0].Columns[1], DbType.String);
db.Execute(GetInsertStatement((string)Tables[0].Value(0, 1)));
}示例6: Up
public void Up(IDatabase db)
{
db.CreateTable(Tables[0].Name)
.WithPrimaryKeyColumn(Tables[0].Columns[0], DbType.Int32).AsIdentity()
.WithNotNullableColumn(Tables[0].Columns[1], DbType.String).OfSize(255);
db.Execute(string.Format(CultureInfo.InvariantCulture, "INSERT INTO \"{0}\" (\"{1}\") VALUES ('{2}')", Tables[0].Name, Tables[0].Columns[1], Tables[1].Value(0, 1)));
db.Execute(string.Format(CultureInfo.InvariantCulture, "INSERT INTO \"{0}\" (\"{1}\") VALUES ('{2}')", Tables[0].Name, Tables[0].Columns[1], Tables[1].Value(1, 1)));
if (db.Context.ProviderMetadata.Name != ProviderNames.SqlServerCe4 &&
db.Context.ProviderMetadata.Name != ProviderNames.SqlServerCe35 &&
db.Context.ProviderMetadata.Name != ProviderNames.SQLite &&
!db.Context.ProviderMetadata.Name.Contains("Teradata"))
{
db.Tables[Tables[0].Name].PrimaryKey().Rename("PK_" + NewTableName);
}
else if (db.Context.ProviderMetadata.Name == ProviderNames.SqlServerCe4 || db.Context.ProviderMetadata.Name == ProviderNames.SqlServerCe35)
{
// this code is actually not required for the test but we still execute it because it is what the recommendation
// of the SqlServerCe4 and SqlServerCe35 is in the NotSupportedException for the primary key renaming
db.Tables[Tables[0].Name].PrimaryKey().Drop();
db.Tables[Tables[0].Name].AddPrimaryKey("PK_" + NewTableName)
.OnColumn(Tables[0].Columns[0]);
}
db.Tables[Tables[0].Name].Rename(NewTableName);
// insert another row into the renamed table to verify that the Identity is still working
db.Execute(string.Format(CultureInfo.InvariantCulture, "INSERT INTO \"{0}\" (\"{1}\") VALUES ('{2}')", NewTableName, Tables[1].Columns[1], Tables[1].Value(2, 1)));
// create a new table that has the same name as the previously renamed table and also has an identity column to check
// if associated db objects that are managed by the provider itself were renamed along with the table
db.CreateTable(Tables[0].Name)
.WithPrimaryKeyColumn(Tables[0].Columns[0], DbType.Int32).AsIdentity()
.WithNotNullableColumn(Tables[0].Columns[1], DbType.String).OfSize(255);
db.Execute(string.Format(CultureInfo.InvariantCulture, "INSERT INTO \"{0}\" (\"{1}\") VALUES ('{2}')", Tables[0].Name, Tables[0].Columns[1], Tables[0].Value(0, 1)));
db.Execute(string.Format(CultureInfo.InvariantCulture, "INSERT INTO \"{0}\" (\"{1}\") VALUES ('{2}')", Tables[0].Name, Tables[0].Columns[1], Tables[0].Value(1, 1)));
}示例7: Up
public void Up(IDatabase db)
{
if (!this.IsFeatureSupported(db))
{
return;
}
db.CreateTable("Mig23a")
.WithPrimaryKeyColumn("Id", DbType.Int32).AsIdentity()
.WithNotNullableColumn("Data", DbType.String).OfSize(255);
db.CreateTable("Mig23b")
.WithPrimaryKeyColumn("Id", DbType.Int32).AsIdentity()
.WithNotNullableColumn("Data", DbType.String).OfSize(255);
// INSERT using literal SQL (something which could be considered bad practice is most cases)
string unicodeLiteralPrefix = db.Context.ProviderMetadata.PrefixUnicodeLiterals ? "N" : string.Empty; // see: http://stackoverflow.com/questions/31270356/is-nsome-string-here-ansi-sql
db.Execute(string.Format(CultureInfo.InvariantCulture, @"INSERT INTO ""Mig23a"" (""Data"") VALUES ({0}'Irgendöppis')", unicodeLiteralPrefix));
db.Execute(string.Format(CultureInfo.InvariantCulture, @"INSERT INTO ""Mig23a"" (""Data"") VALUES ({0}'Unicodović')", unicodeLiteralPrefix));
// INSERT using parameter injection
db.Execute(ctx =>
{
InsertUsingParameters(ctx, "Irgendöppis");
InsertUsingParameters(ctx, "Unicodović");
});
}示例8: Up
public void Up(IDatabase db)
{
if (!this.IsFeatureSupported(db))
{
return;
}
db.CreateTable(Tables[0].Name)
.WithPrimaryKeyColumn(Tables[0].Columns[0], DbType.Int32).AsIdentity()
.WithNotNullableColumn(Tables[0].Columns[1], DbType.String).OfSize(255);
db.Execute(string.Format(CultureInfo.InvariantCulture, "INSERT INTO \"{0}\" (\"{1}\") VALUES ('{2}')", Tables[0].Name, Tables[0].Columns[1], Tables[1].Value(0, 1)));
db.Execute(string.Format(CultureInfo.InvariantCulture, "INSERT INTO \"{0}\" (\"{1}\") VALUES ('{2}')", Tables[0].Name, Tables[0].Columns[1], Tables[1].Value(1, 1)));
db.Tables[Tables[0].Name].PrimaryKey().Rename("PK_" + NewTableName);
db.Tables[Tables[0].Name].Rename(NewTableName);
// insert another row into the renamed table to verify that the Identity is still working
db.Execute(string.Format(CultureInfo.InvariantCulture, "INSERT INTO \"{0}\" (\"{1}\") VALUES ('{2}')", NewTableName, Tables[1].Columns[1], Tables[1].Value(2, 1)));
// create a new table that has the same name as the previously renamed table and also has an identity column to check
// if associated db objects that are managed by the provider itself were renamed along with the table
db.CreateTable(Tables[0].Name)
.WithPrimaryKeyColumn(Tables[0].Columns[0], DbType.Int32).AsIdentity()
.WithNotNullableColumn(Tables[0].Columns[1], DbType.String).OfSize(255);
db.Execute(string.Format(CultureInfo.InvariantCulture, "INSERT INTO \"{0}\" (\"{1}\") VALUES ('{2}')", Tables[0].Name, Tables[0].Columns[1], Tables[0].Value(0, 1)));
db.Execute(string.Format(CultureInfo.InvariantCulture, "INSERT INTO \"{0}\" (\"{1}\") VALUES ('{2}')", Tables[0].Name, Tables[0].Columns[1], Tables[0].Value(1, 1)));
}示例9: Up
public void Up(IDatabase db)
{
// dropping columns is not supported by SQLite
bool dropColumnIsSupported = db.Context.ProviderMetadata.Name != ProviderNames.SQLite;
var table = db.CreateTable(Tables[0].Name)
.WithPrimaryKeyColumn(Tables[0].Columns[0], DbType.Int32);
if (dropColumnIsSupported)
{
table.WithNotNullableColumn(Tables[0].Columns[1], DbType.String).OfSize(10).HavingDefault(FirstDefaultValue);
}
else
{
table.WithNotNullableColumn(Tables[0].Columns[1], DbType.Int32).HavingDefault(SecondDefaultValue);
}
db.Execute(string.Format(CultureInfo.InvariantCulture, "INSERT INTO \"{0}\" (\"{1}\") VALUES ('{2}')", Tables[0].Name, Tables[0].Columns[0], Tables[0].Value(0, 0)));
if (dropColumnIsSupported)
{
db.Tables[Tables[0].Name].Columns[Tables[0].Columns[1]].Drop();
// add a new column with the same name like the previously dropped one to make sure that any associated db object were dropped, too
db.Tables[Tables[0].Name].AddNotNullableColumn(Tables[0].Columns[1], DbType.Int32).HavingDefault(SecondDefaultValue);
}
db.Execute(string.Format(CultureInfo.InvariantCulture, "INSERT INTO \"{0}\" (\"{1}\") VALUES ('{2}')", Tables[0].Name, Tables[0].Columns[0], Tables[0].Value(1, 0)));
}示例10: Up
public void Up(IDatabase db)
{
if (!this.IsFeatureSupported(db))
{
return;
}
const string schemaName = "Schema27";
db.CreateSchema(schemaName);
db.CreateTable("Mig27") // should be created in Schema27
.WithPrimaryKeyColumn("Id", DbType.Int32).AsIdentity()
.WithNotNullableColumn("Content", DbType.String);
db.Execute(@"INSERT INTO ""Schema27"".""Mig27"" ( ""Content"" ) VALUES ( 'Success' )");
db.Execute(context =>
{
Assert.AreEqual("Schema27", context.StepMetadata.ModuleName);
Assert.AreEqual(27L, context.StepMetadata.Migrations.Single().Timestamp);
Assert.AreEqual("Test UseModuleNameAsDefaultSchema", context.StepMetadata.Migrations.Single().Tag);
Assert.AreEqual(MigrationDirection.Up, context.StepMetadata.Direction);
Assert.IsTrue(context.StepMetadata.UseModuleNameAsDefaultSchema, "Passing UseModuleNameAsDefaultSchema to migration metadata failed.");
});
}示例11: Up
public void Up(IDatabase db)
{
db.CreateTable(Tables[0].Name, "first")
.WithPrimaryKeyColumn(Tables[0].Columns[0], DbType.Int32).AsIdentity()
.WithNotNullableColumn(Tables[0].Columns[1], DbType.Int32); // FK to 'other'
const string otherId = "Id";
const string otherName = "Name";
db.CreateTable(Other)
.WithPrimaryKeyColumn(otherId, DbType.Int32).AsIdentity()
.WithNotNullableColumn(otherName, DbType.String).OfSize(255);
db.Execute(string.Format(CultureInfo.InvariantCulture, @"INSERT INTO ""{0}"" (""{1}"") VALUES ('{2}')", Other, otherName, "Not Referenced"));
db.Execute(GetDeleteStatementForOther()); // removing the row from Other should not be a problem since it is not referenced
db.Execute(string.Format(CultureInfo.InvariantCulture, @"INSERT INTO ""{0}"" (""{1}"") VALUES ('{2}')", Other, otherName, "Referenced"));
// testing to see that if we drop an index rename the table create a copy of the table with the same old fk it should work (TD issue in security)
db.Tables[Tables[0].Name].AddForeignKeyTo(Other, "test")
.Through(Tables[0].Columns[1], otherId);
db.Tables[Tables[0].Name].ForeignKeys["test"].Drop();
db.Tables[Tables[0].Name].Rename(TmpPrefix + Tables[0].Name);
db.CreateTable(Tables[0].Name)
.WithPrimaryKeyColumn(Tables[0].Columns[0], DbType.Int32).AsIdentity()
.WithNotNullableColumn(Tables[0].Columns[1], DbType.Int32); // FK to 'other
db.Tables[Tables[0].Name].AddForeignKeyTo(Other, "test")
.Through(Tables[0].Columns[1], otherId);
// insert a row that references a row from Other
db.Execute(string.Format(CultureInfo.InvariantCulture, @"INSERT INTO ""{0}"" (""{1}"") VALUES ('{2}')", Tables[0].Name, Tables[0].Columns[1], Tables[0].Value(0, 1)));
// removing the row from Other should fail as it is referenced by the foreign key
if (db.Context.ProviderMetadata.Name != ProviderNames.SQLite) // Mig# does not support SQLite foreign keys (see comments in SQLiteProvider.AddForeignKey)
{
db.Execute(context =>
{
IDbCommand command = context.Connection.CreateCommand();
command.Transaction = context.Transaction;
command.CommandText = GetDeleteStatementForOther();
Log.Verbose(LogCategory.Sql, command.CommandText);
try
{
command.ExecuteNonQuery();
Assert.Fail("The previous query should have failed.");
}
catch (Exception x)
{
// a DbException is expected (for the case of a SqlServer35 the SqlCeException is not derived from DbException)
if (!(x is DbException) && x.GetType().Name != "SqlCeException")
{
throw;
}
}
});
}
}示例12: Up
public void Up(IDatabase db)
{
db.CreateTable(Tables[0].Name)
.WithPrimaryKeyColumn(Tables[0].Columns[0], DbType.Int32).AsIdentity()
.WithNotNullableColumn(Tables[0].Columns[1], DbType.String).OfSize(100);
db.Execute(GetInsertStatement(0));
db.Execute(GetInsertStatement(1));
if (db.Context.ProviderMetadata.Name != ProviderNames.SQLite)
{
// add two new columns one of int and one of string
db.Tables[Tables[0].Name].AddNotNullableColumn(Tables[0].Columns[2], DbType.String).OfSize(100).HavingTemporaryDefault(DefaultString);
db.Tables[Tables[0].Name].AddNotNullableColumn(Tables[0].Columns[3], DbType.Int32).HavingTemporaryDefault(DefaultInt);
db.Tables[Tables[0].Name].AddNotNullableColumn(Tables[0].Columns[4], DbType.DateTime).HavingTemporaryDefault(DefaultDate);
// ensure that the default values have been droped
db.Execute(context =>
{
// MySQL will not throw an error on insert unless strict mode is enabled
if (db.Context.ProviderMetadata.Name == ProviderNames.MySqlExperimental) {
IDbCommand command2 = context.Connection.CreateCommand();
command2.Transaction = context.Transaction;
command2.CommandText = "SET SQL_MODE = 'ANSI_QUOTES,STRICT_ALL_TABLES'";
command2.ExecuteNonQuery();
}
IDbCommand command = context.Connection.CreateCommand();
command.Transaction = context.Transaction;
command.CommandText = GetInsertStatement(1);
Log.Verbose(LogCategory.Sql, command.CommandText);
try
{
command.ExecuteNonQuery();
Assert.Fail("The previous query should have failed.");
}
catch (Exception x)
{
if (!x.IsDbException())
{
throw;
}
// a DbException is expected (for the case of a SqlServer35 the SqlCeException is not derived from DbException)
if (!(x is DbException) && x.GetType().Name != "SqlCeException")
{
throw;
}
}
});
}
else
{
// SQLite does not support dropping of default values
db.Tables[Tables[0].Name].AddNotNullableColumn(Tables[0].Columns[2], DbType.String).OfSize(100).HavingDefault(DefaultString);
db.Tables[Tables[0].Name].AddNotNullableColumn(Tables[0].Columns[3], DbType.Int32).HavingDefault(DefaultInt);
db.Tables[Tables[0].Name].AddNotNullableColumn(Tables[0].Columns[4], DbType.DateTime).HavingDefault(DefaultDate);
}
}示例13: Up
public void Up(IDatabase db)
{
if (db.Context.ProviderMetadata.Name == "a provider that does not exist")
{
db.Execute("bogus query which would fail");
}
db.CreateTable(Tables[0].Name)
.WithPrimaryKeyColumn(Tables[0].Columns[0], DbType.Int32);
db.Execute(string.Format(CultureInfo.InvariantCulture, "INSERT INTO \"{0}\" VALUES ({1})", Tables[0].Name, Tables[0].Value(0, 0)));
}示例14: Up
public void Up(IDatabase db)
{
string tableName = Tables[0].Name;
db.CreateTable(tableName)
.WithPrimaryKeyColumn("Id", DbType.Int32)
.WithNotNullableColumn("Flag", DbType.Boolean).HavingDefault(DefaultValue);
db.Execute(string.Format(CultureInfo.InvariantCulture, @"INSERT INTO ""{0}"" (""{1}"") VALUES (1)", tableName, Tables[0].Columns[0])); // without 'Flag'
db.Execute(string.Format(CultureInfo.InvariantCulture, @"INSERT INTO ""{0}"" (""{1}"", ""{2}"") VALUES (2, 0)", tableName, Tables[0].Columns[0], Tables[0].Columns[1]));
db.Execute(string.Format(CultureInfo.InvariantCulture, @"INSERT INTO ""{0}"" (""{1}"", ""{2}"") VALUES (3, 1)", tableName, Tables[0].Columns[0], Tables[0].Columns[1]));
}示例15: Up
public void Up(IDatabase db)
{
db.CreateTable(Tables[0].Name)
.WithPrimaryKeyColumn(Tables[0].Columns[0], DbType.Int32).AsIdentity()
.WithNotNullableColumn(Tables[0].Columns[1], DbType.String);
db.Execute(GetInsertStatement((string)Tables[0].Value(0, 1)));
// Note: the following statement does *not* the identity constraint. Doing so is very difficult. For example, see: http://stackoverflow.com/questions/702745/sql-server-how-to-drop-identity-from-a-column
//db.Tables[TableName].Columns[ColumnNames[0]].AlterToNotNullable(DbType.Int32);
db.Tables[Tables[0].Name].Drop(); // make sure, TRIGGERS and SEQUENCES are dropped as well
db.CreateTable(Tables[0].Name)
.WithPrimaryKeyColumn(Tables[0].Columns[0], DbType.Int32)
.WithNotNullableColumn(Tables[0].Columns[1], DbType.String);
// inserting another row without specifying the Id value should fail as the Identity constraint is removed
if (db.Context.ProviderMetadata.Name != ProviderNames.SQLite) // SQLite automatically generates identity columns for PKs
{
db.Execute(context =>
{
IDbCommand command = context.Connection.CreateCommand();
command.Transaction = context.Transaction;
command.CommandText = GetInsertStatement((string)Tables[0].Value(0, 1));
Log.Verbose(LogCategory.Sql, command.CommandText);
try
{
command.ExecuteNonQuery();
Assert.Fail("The previous query should have failed.");
}
catch (Exception ex)
{
// this is expected
if (db.Context.ProviderMetadata.Name != ProviderNames.SqlServerCe35 && !(ex is DbException))
throw;
// for the case of a SqlServer35 the SqlCeException is not derived from DbException
}
});
}
db.Execute(GetInsertStatement((int)Tables[0].Value(0, 0), (string)Tables[0].Value(0, 1)));
db.Tables[Tables[0].Name].Drop(); // make sure, TRIGGERS and SEQUENCES are dropped as well
// recreating the table with the identity constraint again might reveal undropped TRIGGERS or SEQUENCES
db.CreateTable(Tables[0].Name)
.WithPrimaryKeyColumn(Tables[0].Columns[0], DbType.Int32).AsIdentity()
.WithNotNullableColumn(Tables[0].Columns[1], DbType.String);
db.Execute(GetInsertStatement((string)Tables[0].Value(0, 1)));
}