Execute SqlServer scripts at startup – Aspire 9.1 vs Aspire 9.2
One of the usual things in Software development is creating the database with all the tables,views, stored procedures and anything related . For Aspire 9.1 with Sql Ser, this involved a coordination between some shell scripts – see the code :
var sqlserver = builder.AddSqlServer("sqlserver", paramPass, 1433)
.WithDbGate()
// Mount the init scripts directory into the container.
.WithBindMount("./sqlserverconfig", "/usr/config")
// Mount the SQL scripts directory into the container so that the init scripts run.
.WithBindMount("../../Scripts/data/sqlserver", "/docker-entrypoint-initdb.d")
// Run the custom entrypoint script on startup.
.WithEntrypoint("/usr/config/entrypoint.sh")
The scripts where in the mount binding. Also, some .gitattributes files was required with
* text=auto *.sh text eol=lf *.mod text eol=lf *.sum text eol=lf
to can execute the .sh entry point. An painfull orchestration just for create tables
For Aspire 9.2 this has been simplified a bit , because of Creation Script for database
var db = sqlserver.AddDatabase("MyDB")
.WithCreationScript("....")
However , this is has some flaws :
1 .Executing in the timeout of an sql
2, Executing just the last one
You can find the code at SqlServerBuilderExtensions.cs :
private static async Task CreateDatabaseAsync(SqlConnection sqlConnection, SqlServerDatabaseResource sqlDatabase, IServiceProvider serviceProvider, CancellationToken ct)
{
try
{
var scriptAnnotation = sqlDatabase.Annotations.OfType<SqlServerCreateDatabaseScriptAnnotation>().LastOrDefault();
if (scriptAnnotation?.Script == null)
{
var quotedDatabaseIdentifier = new SqlCommandBuilder().QuoteIdentifier(sqlDatabase.DatabaseName);
using var command = sqlConnection.CreateCommand();
command.CommandText = $"IF ( NOT EXISTS ( SELECT 1 FROM sys.databases WHERE name = @DatabaseName ) ) CREATE DATABASE {quotedDatabaseIdentifier};";
command.Parameters.Add(new SqlParameter("@DatabaseName", sqlDatabase.DatabaseName));
await command.ExecuteNonQueryAsync(ct).ConfigureAwait(false);
}
else
{
using var reader = new StringReader(scriptAnnotation.Script);
var batchBuilder = new StringBuilder();
while (reader.ReadLine() is { } line)
{
var matchGo = GoStatements().Match(line);
if (matchGo.Success)
{
// Execute the current batch
var count = matchGo.Groups["repeat"].Success ? int.Parse(matchGo.Groups["repeat"].Value, CultureInfo.InvariantCulture) : 1;
var batch = batchBuilder.ToString();
for (var i = 0; i < count; i++)
{
using var command = sqlConnection.CreateCommand();
command.CommandText = batch;
await command.ExecuteNonQueryAsync(ct).ConfigureAwait(false);
So I made my own extension,
[GeneratedRegex(@"^\s*GO(?<repeat>\s+\d{1,6})?(\s*\-{2,}.*)?\s*$", RegexOptions.CultureInvariant | RegexOptions.IgnoreCase)]
internal static partial Regex GoStatements();
public static IResourceBuilder<SqlServerDatabaseResource> ExecuteSqlServerScripts(this IResourceBuilder<SqlServerDatabaseResource> db, params IEnumerable<string> sqlScripts)
{
var builder = db.ApplicationBuilder;
builder.Eventing.Subscribe<ResourceReadyEvent>(async (ev, ct) =>
{
if (ev.Resource is not SqlServerDatabaseResource dbRes) return;
if (db.Resource != dbRes) return;
var cn = await dbRes.ConnectionStringExpression.GetValueAsync(ct);
if (cn == null) return;
using var sqlConnection = new SqlConnection();
sqlConnection.ConnectionString = cn;
await sqlConnection.OpenAsync(ct);
foreach (var item in sqlScripts)
{
using var reader = new StringReader(item);
var batchBuilder = new StringBuilder();
while (reader.ReadLine() is { } line)
{
var matchGo = GoStatements().Match(line);
if (matchGo.Success)
{
// Execute the current batch
var count = matchGo.Groups["repeat"].Success ? int.Parse(matchGo.Groups["repeat"].Value, CultureInfo.InvariantCulture) : 1;
var batch = batchBuilder.ToString();
for (var i = 0; i < count; i++)
{
using var command = sqlConnection.CreateCommand();
command.CommandText = batch;
//TODO: modify the timeout
command.CommandTimeout = 120;
try
{
await command.ExecuteNonQueryAsync(ct).ConfigureAwait(false);
}
catch (Exception ex)
{
//TODO: log
Console.WriteLine($"!!!!Error in executing {batch} : {ex.Message}");
}
}
batchBuilder.Clear();
}
else
{
// Prevent batches with only whitespace
if (!string.IsNullOrWhiteSpace(line))
{
batchBuilder.AppendLine(line);
}
}
}
// Process the remaining batch lines
if (batchBuilder.Length > 0)
{
using var command = sqlConnection.CreateCommand();
var batch = batchBuilder.ToString();
command.CommandText = batch;
//TODO: modify the timeout
try
{
await command.ExecuteNonQueryAsync(ct).ConfigureAwait(false);
}
catch (Exception ex)
{
//TODO: log
Console.WriteLine($"!!!!Error in executing {batch} : {ex.Message}");
}
}
}
});
return db;
}
And can be executed like this:
var db = sqlserver.AddDatabase("MyDB")
.WithSqlPadViewerForDB(sqlserver)
.ExecuteSqlServerScripts(DBFiles.FilesToCreate)