A Programmer with Microsoft tools

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)

.NET Aspire is a formidable tool to visualize your components and relation between them . Today I will show you how to add a custom visualizer for SqlServer database .

The code for adding a database is pretty simple

var paramPass = builder.AddParameter("password", "P@ssw0rd");
var sqlserver = builder.AddSqlServer("sqlserver", paramPass, 1433)
    .WithDbGate()
;
var db = sqlserver.AddDatabase("NewDB")
;

The community extension, https://github.com/CommunityToolkit/Aspire, has already an extension, WithDBGate, that adds a viewer for the whole SqlServer . But I want something faster, that adds just for the database . So SqlPad , https://getsqlpad.com/ , that has also a docker container , will be enough. So this is the code

public  static class SqlServerExtensions
{
    /// <summary>
    /// 
    /// </summary>
    /// <param name="db"></param>
    /// <param name="sqlserver"></param>
    /// <returns></returns>
    public static IResourceBuilder<SqlServerDatabaseResource> WithSqlPadViewerForDB(this IResourceBuilder<SqlServerDatabaseResource> db,IResourceBuilder<SqlServerServerResource> sqlserver) 
    {
    var builder = db.ApplicationBuilder;
    
    var sqlpad = builder
.AddContainer("sqlpad", "sqlpad/sqlpad:latest")
.WithEndpoint(5600, 3000, "http")
.WithEnvironment("SQLPAD_AUTH_DISABLED", "true")
.WithEnvironment("SQLPAD_AUTH_DISABLED_DEFAULT_ROLE","Admin")
.WithEnvironment("SQLPAD_ADMIN", "admin@sqlpad.com")

.WithEnvironment("SQLPAD_ADMIN_PASSWORD", "admin")
.WithEnvironment("SQLPAD_CONNECTIONS__sqlserverdemo__name", sqlserver.Resource.Name)
.WithEnvironment("SQLPAD_CONNECTIONS__sqlserverdemo__driver", "sqlserver")
.WithEnvironment("SQLPAD_CONNECTIONS__sqlserverdemo__host", sqlserver.Resource.Name)
.WithEnvironment("SQLPAD_CONNECTIONS__sqlserverdemo__database", db.Resource.Name)
.WithEnvironment("SQLPAD_CONNECTIONS__sqlserverdemo__username", "sa")
.WithEnvironment("SQLPAD_CONNECTIONS__sqlserverdemo__password", sqlserver.Resource.PasswordParameter.Value)


.WithEnvironment("SQLPAD_CONNECTIONS__sqlserverdemo1__name", "SqlMaster")
.WithEnvironment("SQLPAD_CONNECTIONS__sqlserverdemo1__driver", "sqlserver")
.WithEnvironment("SQLPAD_CONNECTIONS__sqlserverdemo1__host", sqlserver.Resource.Name)
.WithEnvironment("SQLPAD_CONNECTIONS__sqlserverdemo1__database", "master")
.WithEnvironment("SQLPAD_CONNECTIONS__sqlserverdemo1__username", "sa")
.WithEnvironment("SQLPAD_CONNECTIONS__sqlserverdemo1__password", sqlserver.Resource.PasswordParameter.Value)
.WithParentRelationship(db)
.WaitFor(db)
.WaitFor(sqlserver)
;
    return db;
}
}

And the code for adding is

  var db = sqlserver.AddDatabase("NewDB")
  .WithSqlPadViewerForDB(sqlserver);