The SQL persistence enables creation of scripts that can be run as a part of a deployment process instead of as part of endpoint startup as with standard installers. See Installer Workflow for more information.
Script execution runs by default at endpoint startup
To streamline development SQL persistence installers are, by default, executed at endpoint startup, in the same manner as all other installers.
endpointConfiguration.EnableInstallers();
var persistence = endpointConfiguration.UsePersistence<SqlPersistence>();
Optionally take control of script execution
However in higher level environment scenarios, where standard installers are being run, but the SQL persistence installation scripts have been executed as part of a deployment, it may be necessary to explicitly disable the SQL persistence installers executing while leaving standard installers enabled.
endpointConfiguration.EnableInstallers();
var persistence = endpointConfiguration.UsePersistence<SqlPersistence>();
persistence.DisableInstaller();
Table Prefix
Table prefix is the string that is prefixed to every table name, i.e. Saga, Outbox, Subscription and Timeout tables.
The default TablePrefix is Endpoint Name with all periods (.
) replaced with underscores (_
).
A Table Prefix is used at runtime and install time.
When using the default (execute at startup) approach to installation the value configured in code will be used.
var persistence = endpointConfiguration.UsePersistence<SqlPersistence>();
persistence.TablePrefix("ThePrefix");
Database Schema
When using a database that supports schemas, a schema value other than default can be defined in the configuration API. Consult the documentation of selected SQL dialect for details.
Manual installation
When performing a custom script execution the TablePrefix is required. See also Installer Workflow.
Note that scriptDirectory
can be either the root directory for all scripts for, alternatively, the specific locations for a given storage type i.e. Saga, Outbox, Subscription and Timeout scripts.
SQL Server
using (var connection = new SqlConnection("ConnectionString"))
{
connection.Open();
using (var transaction = connection.BeginTransaction())
{
foreach (var createScript in Directory.EnumerateFiles(
path: scriptDirectory,
searchPattern: "*_Create.sql",
searchOption: SearchOption.AllDirectories))
{
using (var command = connection.CreateCommand())
{
command.Transaction = transaction;
command.CommandText = File.ReadAllText(createScript);
var tablePrefixParameter = command.CreateParameter();
tablePrefixParameter.ParameterName = "tablePrefix";
tablePrefixParameter.Value = tablePrefix;
command.Parameters.Add(tablePrefixParameter);
var schemaParameter = command.CreateParameter();
schemaParameter.ParameterName = "schema";
schemaParameter.Value = "dbo";
command.Parameters.Add(schemaParameter);
command.ExecuteNonQuery();
}
}
transaction.Commit();
}
}
MySQL
using (var connection = new MySqlConnection("ConnectionString"))
{
connection.Open();
using (var transaction = connection.BeginTransaction())
{
foreach (var createScript in Directory.EnumerateFiles(
path: scriptDirectory,
searchPattern: "*_Create.sql",
searchOption: SearchOption.AllDirectories))
{
using (var command = connection.CreateCommand())
{
command.Transaction = transaction;
command.CommandText = File.ReadAllText(createScript);
var parameter = command.CreateParameter();
parameter.ParameterName = "tablePrefix";
parameter.Value = tablePrefix;
command.Parameters.Add(parameter);
command.ExecuteNonQuery();
}
}
transaction.Commit();
}
}
Oracle
using (var connection = new OracleConnection("ConnectionString"))
{
connection.Open();
using (var transaction = connection.BeginTransaction())
{
foreach (var createScript in Directory.EnumerateFiles(
path: scriptDirectory,
searchPattern: "*_Create.sql",
searchOption: SearchOption.AllDirectories))
{
using (var command = connection.CreateCommand())
{
command.Transaction = transaction;
command.CommandText = File.ReadAllText(createScript);
var parameter = command.CreateParameter();
parameter.ParameterName = "tablePrefix";
parameter.Value = tablePrefix;
command.Parameters.Add(parameter);
command.ExecuteNonQuery();
}
}
transaction.Commit();
}
}
PostgreSQL
using (var connection = new NpgsqlConnection("ConnectionString"))
{
connection.Open();
using (var transaction = connection.BeginTransaction())
{
foreach (var createScript in Directory.EnumerateFiles(
path: scriptDirectory,
searchPattern: "*_Create.sql",
searchOption: SearchOption.AllDirectories))
{
using (var command = connection.CreateCommand())
{
command.Transaction = transaction;
command.CommandText = File.ReadAllText(createScript);
var parameter = command.CreateParameter();
parameter.ParameterName = "tablePrefix";
parameter.Value = tablePrefix;
command.Parameters.Add(parameter);
command.ExecuteNonQuery();
}
}
transaction.Commit();
}
}