Agfdhyk

I was wondering if there is a way to create the SQL distributed cache on startup of the ASP.NET Core 2.1 application.

I know I can run this command
dotnet sql-cache create <connection string> <schema> <table name> but this is a manual process. I want to automate this process on startup.

Is this possible?

Thanks

Here are two options:

Options1

Since we could run command to create the table, we could run the command from code by Process.

    public static IServiceCollection ConfigureSqlCacheFromCommand(this IServiceCollection services)

    {

        var options = services.BuildServiceProvider().GetRequiredService<IOptions<SqlServerCacheOptions>>();



        var process = new Process()

        {

            StartInfo = new ProcessStartInfo

            {

                FileName = "cmd.exe",

                Arguments = $"/c dotnet sql-cache create "{options.Value.ConnectionString}" { options.Value.SchemaName } { options.Value.TableName }",

                RedirectStandardOutput = true,

                UseShellExecute = false,

                CreateNoWindow = false,

                WindowStyle = ProcessWindowStyle.Normal,

                RedirectStandardInput = true,

                RedirectStandardError = true

            }

        };

        process.Start();

        string input = process.StandardError.ReadToEnd();

        string result = process.StandardOutput.ReadToEnd();

        process.WaitForExit();

        return services;

    }

Option2

For dotnet sql-cache command, it also calls dotnet-sql-cache, and you could implement the code to create the table programmly.

        private static int CreateTableAndIndexes(SqlServerCacheOptions options)

    {

        using (var connection = new SqlConnection(options.ConnectionString))

        {

            connection.Open();



            var sqlQueries = new SqlQueries(options.SchemaName, options.TableName);

            var command = new SqlCommand(sqlQueries.TableInfo, connection);



            using (var reader = command.ExecuteReader(CommandBehavior.SingleRow))

            {

                if (reader.Read())

                {

                    return 1;

                }

            }

            using (var transaction = connection.BeginTransaction())

            {

                try

                {

                    command = new SqlCommand(sqlQueries.CreateTable, connection, transaction);



                    command.ExecuteNonQuery();



                    command = new SqlCommand(

                        sqlQueries.CreateNonClusteredIndexOnExpirationTime,

                        connection,

                        transaction);



                    command.ExecuteNonQuery();

                    transaction.Commit();

                }

                catch (Exception ex)

                {

                    transaction.Rollback();

                    return 1;

                }

            }

        }

        return 0;

    }

For calling CreateTableAndIndexes, you could implement this extension method:

        public static IServiceCollection ConfigureSqlCache(this IServiceCollection services)

    {

        var options = services.BuildServiceProvider().GetRequiredService<IOptions<SqlServerCacheOptions>>();

        int result = CreateTableAndIndexes(options.Value);

        return services;

    }

For configuring in Startup.cs

        services.AddDistributedSqlServerCache(options => {

            options.ConnectionString = @"Server=localhostMSSQLSERVER01;Database=IISWindows;Trusted_Connection=True;";

            options.TableName = "CacheFromCommand";

            options.SchemaName = "dbo";

        });



        //services.ConfigureSqlCache();

        services.ConfigureSqlCacheFromCommand();

Note

• SqlQueries is from SqlQueries.




• install package Microsoft.Extensions.CommandLineUtils.



• Register ConfigureSqlCache after services.AddDistributedSqlServerCache
  

How do you want to automate this? I am sure but there are many ways to add a distributed cache.

You can see the samples the following link:

https://docs.microsoft.com/en-us/aspnet/core/performance/caching/distributed?view=aspnetcore-2.1