private static void AddTodoContext(IServiceProvider serviceProvider, DbContextOptionsBuilder options)
{
// Check that SQL Server LocalDB is installed
ISqlLocalDbApi localDB = serviceProvider.GetRequiredService <ISqlLocalDbApi>();
if (!localDB.IsLocalDBInstalled())
{
throw new NotSupportedException("SQL LocalDB is not installed.");
}
// Get the configured SQL LocalDB instance to store the TODO items in, creating it if it does not exist
IConfiguration config = serviceProvider.GetRequiredService <IConfiguration>();
ISqlLocalDbInstanceInfo instance = localDB.GetOrCreateInstance(config["SqlLocalDbInstance"]);
// Ensure that the SQL LocalDB instance is running and start it if not already running
if (!instance.IsRunning)
{
instance.Manage().Start();
}
// Get the SQL connection string to use to connect to the LocalDB instance
string connectionString = instance.GetConnectionString();
options.UseSqlServer(connectionString);
}
public static string GetRunningConnectionString(ISqlLocalDbInstanceInfo db)
{
if (!db.IsRunning)
{
db.Manage().Start();
}
return(db.GetConnectionString());
}
private static string InitSqlLocalDB()
{
ISqlLocalDbApi localDB = new SqlLocalDbApi();
ISqlLocalDbInstanceInfo instance = localDB.GetOrCreateInstance(@"localtestdb");
ISqlLocalDbInstanceManager manager = instance.Manage();
if (!instance.IsRunning)
{
manager.Start();
}
return(instance.GetConnectionString());
}
public static void GetConnectionString_Returns_Sql_Connection_String()
{
// Arrange
var mock = new Mock <ISqlLocalDbInstanceInfo>();
mock.Setup((p) => p.IsRunning).Returns(true);
mock.Setup((p) => p.NamedPipe).Returns("MyNamedPipe");
ISqlLocalDbInstanceInfo instance = mock.Object;
// Act
string actual = instance.GetConnectionString();
// Assert
actual.ShouldBe("Data Source=MyNamedPipe");
}
public static void RegisterAndPopulateLocalDatabase(this IServiceCollection services)
{
var options = new SqlLocalDbOptions()
{
AutomaticallyDeleteInstanceFiles = true,
StopOptions = StopInstanceOptions.NoWait,
StopTimeout = TimeSpan.FromDays(1),
};
services.AddSingleton(sp => new SqlLocalDbApi(options, sp.GetRequiredService <ILoggerFactory>()));
services.AddSingleton(sp =>
{
ISqlLocalDbInstanceInfo instance = sp.GetRequiredService <SqlLocalDbApi>().GetOrCreateInstance("ProductSuggestions");
if (!instance.IsRunning)
{
ISqlLocalDbInstanceManager manager = instance.Manage();
manager.Start();
}
sp.GetRequiredService <ILogger <ISqlLocalDbInstanceInfo> >().LogInformation($"\nConnectionString:\n{instance.GetConnectionString()}");
var upgrader = DeployChanges.To
.SqlDatabase(instance.GetConnectionString())
.WithScriptsEmbeddedInAssembly(Assembly.GetExecutingAssembly())
.LogToConsole()
.Build();
var result = upgrader.PerformUpgrade();
if (!result.Successful)
{
throw result.Error;
}
return(instance);
});
}
public async Task RestoreBackup()
{
using (SqlConnection connection = new SqlConnection(instance.GetConnectionString()))
{
try
{
await connection.OpenAsync();
var command = connection.CreateCommand();
var DBPath = Path.GetDirectoryName(Process.GetCurrentProcess().MainModule.FileName) + Path.DirectorySeparatorChar;
var UserPath = Environment.GetFolderPath(System.Environment.SpecialFolder.UserProfile) + Path.DirectorySeparatorChar;
var DatabaseBackupZip = DBPath + "Resources" + Path.DirectorySeparatorChar + DatabaseFixture.DATABASE_NAME + ".zip";
var DatabaseBackup = DBPath + "Resources" + Path.DirectorySeparatorChar + DatabaseFixture.DATABASE_NAME + ".bak";
if (File.Exists(DatabaseBackup))
{
File.Delete(DatabaseBackup);
}
if (!File.Exists(DatabaseBackupZip))
{
throw new Exception("File " + DatabaseBackupZip + " does not exist.");
}
System.IO.Compression.ZipFile.ExtractToDirectory(DatabaseBackupZip, DBPath + "Resources" + Path.DirectorySeparatorChar);
if (!File.Exists(DatabaseBackup))
{
throw new Exception("File " + DatabaseBackup + " does not exist.");
}
if (File.Exists(string.Format("{0}{1}.mdf", UserPath, DatabaseFixture.DATABASE_NAME)))
{
File.Delete(string.Format("{0}{1}.mdf", UserPath, DatabaseFixture.DATABASE_NAME));
}
if (File.Exists(string.Format("{0}{1}.ldf", UserPath, DatabaseFixture.DATABASE_NAME)))
{
File.Delete(string.Format("{0}{1}.ldf", UserPath, DatabaseFixture.DATABASE_NAME));
}
command.CommandText = string.Format(@"
USE [master]
DECLARE @NOTE VARCHAR(8000) = '';
SET @NOTE = 'Restoring Local DB'; RAISERROR (@NOTE, 10, 1) WITH NOWAIT;
SET NOCOUNT ON;
DECLARE @PMSG VARCHAR(8000) = ''; DECLARE @PROC VARCHAR(512) = 'LocalDB Restore: ';
BEGIN TRY
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'{2}') BEGIN
ALTER DATABASE [{2}] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'{2}';
SET @NOTE = 'Deleting old database'; RAISERROR (@NOTE, 10, 1) WITH NOWAIT;
DROP DATABASE [{2}]
END
END TRY
BEGIN CATCH
END CATCH
DECLARE @MDF VARCHAR(512) = CONVERT(NVARCHAR, serverproperty('InstanceDefaultDataPath')) + '{2}.mdf';
DECLARE @LDF VARCHAR(512) = CONVERT(NVARCHAR, serverproperty('InstanceDefaultLogPath')) + '{2}.ldf';
BEGIN TRY
DROP DATABASE [{2}]
END TRY
BEGIN CATCH
END CATCH
SET @NOTE = 'Restoring....'; RAISERROR (@NOTE, 10, 1) WITH NOWAIT;
RESTORE DATABASE [{2}]
FROM DISK = '{0}'
WITH REPLACE,RECOVERY,
MOVE '{2}' TO @MDF,
MOVE '{2}_log' TO @LDF;
", DatabaseBackup, UserPath, DatabaseFixture.DATABASE_NAME).Replace(@"\", @"\\");
command.CommandTimeout = 300;
await command.ExecuteNonQueryAsync();
}
catch (Exception ex)
{
throw;
}
}
}