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)