/// <summary>
/// Run a test and clean up the databases when complete.
/// </summary>
/// <param name="connectionString">The connection string for the database.</param>
/// <param name="action">The test to run.</param>
internal static void TestWithRollback(string connectionString, Action <RecordingDbConnection> action)
{
// make sure the database exists
if (!SchemaInstaller.DatabaseExists(connectionString))
{
SchemaInstaller.CreateDatabase(connectionString);
}
// do all of the work in a transaction so we can clean up our changes
using (TransactionScope transaction = new TransactionScope())
using (SqlConnection connection = new SqlConnection(connectionString))
using (RecordingDbConnection recordingConnection = new RecordingDbConnection(connection))
{
recordingConnection.Open();
try
{
action(recordingConnection);
}
finally
{
Console.WriteLine("== BEGIN SCRIPT ============================");
Console.WriteLine(recordingConnection.ScriptLog.ToString());
Console.WriteLine("== END SCRIPT ============================");
}
}
}
static void Main(string[] args)
{
SqlConnectionStringBuilder connectionString = new SqlConnectionStringBuilder("Database=.;Initial Catalog=InsightTest;Integrated Security=true");
SchemaInstaller.CreateDatabase(connectionString.ConnectionString);
using (SqlConnection connection = new SqlConnection(connectionString.ConnectionString))
{
connection.Open();
// make sure our database exists
SchemaInstaller installer = new SchemaInstaller(connection);
new SchemaEventConsoleLogger().Attach(installer);
// load the schema from the embedded resources in this project
SchemaObjectCollection schema = new SchemaObjectCollection();
schema.Load(Assembly.GetExecutingAssembly());
// install the schema
Console.WriteLine("Installing");
installer.Install("BeerGarten", schema);
// uninstall the schema
if (args.Length > 0 && args[0].ToUpperInvariant() == "UNINSTALL")
{
Console.WriteLine("Uninstalling");
installer.Uninstall("BeerGarten");
}
}
}
private static void CreateOrModifyDbSchema()
{
var connStr = ConfigurationManager.ConnectionStrings["ad.util.GeneralPurposeTree"].ConnectionString;
if (!SchemaInstaller.DatabaseExists(connStr))
{
_logger.Info("ad.util.GeneralPurposeTree is about to create a database.");
SchemaInstaller.CreateDatabase(connStr);
_logger.Info("ad.util.GeneralPurposeTree has just created a database.");
}
using (var conn = new SqlConnection(connStr)) {
conn.Open();
var installer = new SchemaInstaller(conn);
installer.CreatingObject += SchemaInstaller_CreatingObject;
installer.CreatedObject += SchemaInstaller_CreatedObject;
installer.DroppingObject += SchemaInstaller_DroppingObject;
installer.DropFailed += SchemaInstaller_DropFailed;
_logger.Info("Database modification starting for database '{0}' and schema '{1}'.", conn.Database, DbSchemaName);
var saveCultureInfo = Thread.CurrentThread.CurrentCulture;
Thread.CurrentThread.CurrentCulture = CultureInfo.InvariantCulture;
try {
var schema = new SchemaObjectCollection();
schema.Load(System.Reflection.Assembly.GetExecutingAssembly());
installer.Install(DbSchemaName, schema);
}
finally {
Thread.CurrentThread.CurrentCulture = saveCultureInfo;
}
_logger.Info("Database modification ended for database '{0}' and schema '{1}'.", conn.Database, DbSchemaName);
}
}
/// <summary>
/// Make sure the database exists.
/// </summary>
/// <returns></returns>
static bool CreateDatabase()
{
// make sure the database does not exist
if (SchemaInstaller.DatabaseExists(ConnectionString.ConnectionString))
{
Console.WriteLine("The database {0} already exists. Verify needs an empty database.", Database);
return(false);
}
// create the database
Console.WriteLine("Creating database");
SchemaInstaller.CreateDatabase(ConnectionString.ConnectionString);
return(true);
}
/// <summary>
/// Install the schema.
/// </summary>
static void Install()
{
SchemaInstaller.CreateDatabase(ConnectionString);
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
connection.Open();
Console.WriteLine("Beginning install...");
SchemaInstaller installer = new SchemaInstaller(connection);
installer.AllowRepair = AllowRepair;
new SchemaEventConsoleLogger().Attach(installer);
installer.Install(SchemaGroup, Schema);
Console.WriteLine("Done.");
}
}
public void UpsertShouldReturnRowsInOrder()
{
var connectionString = base.ConnectionStrings.First();
TestWithDrop(connectionString, () =>
{
using (var c = new SqlConnection(connectionString))
{
if (!SchemaInstaller.DatabaseExists(connectionString))
{
SchemaInstaller.CreateDatabase(connectionString);
}
c.Open();
SchemaInstaller installer = new SchemaInstaller(c);
SchemaObjectCollection schema = new SchemaObjectCollection();
schema.Add("CREATE TABLE Beer ([id] [int] NOT NULL IDENTITY, [name] varchar(100))");
schema.Add("ALTER TABLE Beer ADD CONSTRAINT PK_Beer PRIMARY KEY ([ID])");
schema.Add("-- AUTOPROC All Beer");
installer.Install("test", schema);
using (var reader = c.GetReaderSql(@"
truncate table Beer
declare @b BeerTable
insert into @b (id, name) values (null, 'one')
insert into @b (id, name) values (null, 'two')
exec upsertBeers @b
delete from @b
insert into @b (id, name) values (1, 'one')
insert into @b (id, name) values (2, 'two')
insert into @b (id, name) values (null, 'three')
exec upsertBeers @b
" ))
{
reader.NextResult();
reader.Read(); Assert.AreEqual(1, reader.GetInt32(0));
reader.Read(); Assert.AreEqual(2, reader.GetInt32(0));
reader.Read(); Assert.AreEqual(3, reader.GetInt32(0));
}
}
});
}
private void Test(Action <SqlConnection> action)
{
var connectionString = base.ConnectionStrings.First();
TestWithDrop(connectionString, () =>
{
if (!SchemaInstaller.DatabaseExists(connectionString))
{
SchemaInstaller.CreateDatabase(connectionString);
}
using (var c = new SqlConnection(connectionString))
{
c.Open();
SetUpDatabase(c);
action(c);
}
});
}
public static void SetUpFixture()
{
#if !NET35
// insight.schema requires 4.0, so let's assume that in 35, the setup is already done
// let's do all of our work in the test database
if (!SchemaInstaller.DatabaseExists(BaseTest.ConnectionString))
{
SchemaInstaller.CreateDatabase(BaseTest.ConnectionString);
}
var schema = new SchemaObjectCollection(Assembly.GetExecutingAssembly());
using (var connection = new SqlConnection(BaseTest.ConnectionString))
{
connection.Open();
var installer = new SchemaInstaller(connection);
installer.Install("Test", schema);
}
#endif
}
public void TestDropDatabase([ValueSource("ConnectionStrings")] string connectionString)
{
TestWithDrop(connectionString, () =>
{
// create the database if it doesn't exist
if (!SchemaInstaller.DatabaseExists(connectionString))
{
SchemaInstaller.CreateDatabase(connectionString);
}
// drop the database
Assert.True(SchemaInstaller.DropDatabase(connectionString));
// make sure the database doesn't exist
Assert.False(SchemaInstaller.DatabaseExists(connectionString));
// drop the database again, it should return false
Assert.False(SchemaInstaller.DropDatabase(connectionString));
});
}
public void TestCreateDatabaseWithPath([ValueSource("ConnectionStrings")] string connectionString)
{
TestWithDrop(connectionString, () =>
{
// drop the database if it already exists
if (SchemaInstaller.DatabaseExists(connectionString))
{
SchemaInstaller.DropDatabase(connectionString);
}
// create the database
Assert.True(SchemaInstaller.CreateDatabase(connectionString, ConfigurationManager.AppSettings["filepath"] ?? Environment.GetEnvironmentVariable("TEMP")));
// make sure the database exises
Assert.True(SchemaInstaller.DatabaseExists(connectionString));
// create the database again, it should return false
Assert.False(SchemaInstaller.CreateDatabase(connectionString));
});
}
static void Main()
{
var schema = new SchemaObjectCollection();
schema.Load(Assembly.GetExecutingAssembly());
// automatically create the database
var connectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
var databaseName = "Thomas-test-db";
SchemaInstaller.CreateDatabase(connectionString);
// automatically install it, or upgrade it
using (var connection = new SqlConnection(connectionString))
{
connection.Open();
var installer = new SchemaInstaller(connection);
new SchemaEventConsoleLogger().Attach(installer);
installer.Install(databaseName, schema);
}
}