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)