public static string GetMaxLsn(DbConnection connection)
{
using (var cmd = DbCommandFactory.Create("SELECT CONVERT(varchar(22), sys.fn_cdc_get_max_lsn(), 1);", connection))
{
return((string)cmd.ExecuteScalar());
}
}
public async Task <DbData> ExtractAsync(string viewName, ViewParameters parameters)
{
using (DbConnection connection = DbConnectionFactory.Create(_dbEngine, _connectionString))
{
try
{
_logger.LogDebug("Extract db data async via \"View\" started");
DbData result = null;
await connection.OpenAsync().ConfigureAwait(false);
string cmdText = SqlStatmentsGenerator.CreateSelectStatement(SqlStatmentsGenerator.SelectAllColumns, viewName, parameters);
using (IDbCommand command = DbCommandFactory.Create(_dbEngine, connection, cmdText))
{
command.CommandType = CommandType.Text;
result = await ReadDataImplAsync((DbCommand)command);
}
connection.Close();
_logger.LogDebug("Extract db data async via \"View\" completed");
return(result);
}
catch (Exception e)
{
_logger.LogError($"An error occured during async data extraction via \"View\", exception: {e}");
return(null);
}
}
}
public Tuple <IDataReader, IDbConnection> ExecuteDbReader(string connectionString, string cmdText)
{
IDbConnection connection = DbConnectionFactory.Create(_dbEngine, connectionString);
IDbCommand command = DbCommandFactory.Create(_dbEngine, connection, cmdText);
connection.Open();
return(new Tuple <IDataReader, IDbConnection>(ExecuteDbReader(command as DbCommand), connection));
}
public static string GetLastExecutedLsn(DbConnection connection, string providerUrl)
{
using (var cmd = DbCommandFactory.Create($"SELECT TOP(1) LastExecutedLsn FROM [dbo].[CdcLastExecutedLsn] WHERE ProviderUrl = {@ProviderUrl};", connection))
{
cmd.Parameters.AddWithValue(@ProviderUrl, providerUrl);
return((string)cmd.ExecuteScalar());
}
}
public async Task <Tuple <DbDataReader, DbConnection> > ExecuteDbReaderAsync(string connectionString, string cmdText)
{
DbConnection connection = DbConnectionFactory.Create(_dbEngine, connectionString);
await connection.OpenAsync();
IDbCommand command = DbCommandFactory.Create(_dbEngine, connection, cmdText);
return(new Tuple <DbDataReader, DbConnection>(await ExecuteDbReaderAsync(command as DbCommand), connection));
}
private static void ExecuteIdsQuery(DbConnection connection, string query, IEnumerable <int> ids)
{
using (var cmd = DbCommandFactory.Create(query, connection))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add(SqlQuerySyntaxHelper.GetIdsDatatableParam("@ids", ids, DatabaseTypeHelper.ResolveDatabaseType(connection)));
cmd.ExecuteNonQuery();
}
}
public static void DeleteSentNotifications(DbConnection connection)
{
var dbType = DatabaseTypeHelper.ResolveDatabaseType(connection);
var query = $"DELETE FROM SYSTEM_NOTIFICATION_QUEUE WHERE SENT = {SqlQuerySyntaxHelper.ToBoolSql(dbType, true)}";
using (var cmd = DbCommandFactory.Create(query, connection))
{
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
}
}
/// <summary>
/// Full sql command execution in one call without manual connection open and close, constructing
/// proper instance of DbCommand
/// </summary>
/// <param name="connectionString"> Database connection string </param>
/// <param name="cmdText"> Script that should be executed </param>
/// <returns>
/// True if execution was successful, otherwise - false
/// </returns>
public bool ExecuteNonQuery(string connectionString, string cmdText)
{
using (DbConnection connection = DbConnectionFactory.Create(_dbEngine, connectionString))
{
IDbCommand command = DbCommandFactory.Create(_dbEngine, connection, cmdText);
connection.Open();
bool result = ExecuteNonQuery(command as DbCommand);
connection.Close();
return(result);
}
}
public static void InsertNotifications(DbConnection connection, string notificationsXml)
{
var dbType = DatabaseTypeHelper.ResolveDatabaseType(connection);
var query = dbType == DatabaseType.Postgres ? PgInsertNotificationsQuery : SqlInsertNotificationsQuery;
using (var cmd = DbCommandFactory.Create(query, connection))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add(SqlQuerySyntaxHelper.GetXmlParameter("@notifications", notificationsXml, dbType));
cmd.ExecuteNonQuery();
}
}
private static void ExecuteIdsQuery(DbConnection connection, string query, IEnumerable <int> ids, string lastExceptionMessage = null)
{
using (var cmd = DbCommandFactory.Create(query, connection))
{
var dbType = DatabaseTypeHelper.ResolveDatabaseType(connection);
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add(SqlQuerySyntaxHelper.GetIdsDatatableParam("@ids", ids, dbType));
cmd.Parameters.AddWithValue("@lastExceptionMessage", lastExceptionMessage);
cmd.ExecuteNonQuery();
}
}
private bool ExecuteStatement(string connectionString, string statement)
{
bool result = true;
using (IDbConnection connection = DbConnectionFactory.Create(_dbEngine, connectionString))
{
connection.Open();
IDbCommand command = DbCommandFactory.Create(_dbEngine, connection, statement);
result = ExecuteNonQuery(command);
connection.Close();
return(result);
}
}
public async Task <bool> ExecuteNonQueryAsync(string connectionString, string cmdText)
{
using (DbConnection connection = DbConnectionFactory.Create(_dbEngine, connectionString))
{
IDbCommand command = DbCommandFactory.Create(_dbEngine, connection, cmdText);
await connection.OpenAsync().ConfigureAwait(false);
bool result = await ExecuteNonQueryAsync(command as DbCommand);
connection.Close();
return(result);
}
}
public static DataTable GetCdcTableData(DbConnection connection, string captureInstance, string fromLsn = null, string toLsn = null)
{
var sb = new StringBuilder();
sb.AppendLine(BuildQueryHeader(fromLsn, toLsn));
sb.AppendLine(BuildQueryBody(captureInstance));
using (var cmd = DbCommandFactory.Create(sb.ToString(), connection))
using (var da = DataAdapterFactory.Create(cmd))
{
cmd.Parameters.AddWithValue(@CaptureInstance, captureInstance);
cmd.Parameters.AddWithValue(@RawLsnFrom, (object)fromLsn ?? DBNull.Value);
cmd.Parameters.AddWithValue(@RawLsnTo, (object)toLsn ?? DBNull.Value);
var dt = new DataTable();
da.Fill(dt);
return(dt);
}
}
public async Task <DbData> ExtractAsync(string storedProcedureName, IList <StoredProcedureParameter> parameters)
{
using (DbConnection connection = DbConnectionFactory.Create(_dbEngine, _connectionString))
{
try
{
_logger.LogDebug("Extract db data async via \"Stored procedure\" started");
DbData result = null;
await connection.OpenAsync().ConfigureAwait(false);
using (IDbCommand command = DbCommandFactory.Create(_dbEngine, connection, storedProcedureName))
{
command.CommandType = CommandType.StoredProcedure;
// command.CommandText = "call " + command.CommandText;
// add parameters
if (parameters != null && parameters.Count > 0)
{
foreach (StoredProcedureParameter parameter in parameters)
{
if (string.IsNullOrEmpty(parameter.ParameterName))
{
throw new InvalidDataException("parameter name can't be null or empty");
}
DbParameter procedureParameter = DbParameterFactory.Create(_dbEngine, parameter.ParameterName, parameter.ParameterType,
parameter.ParameterValue);
command.Parameters.Add(procedureParameter);
}
}
result = await ReadDataImplAsync((DbCommand)command);
}
connection.Close();
_logger.LogDebug("Extract db data async via \"Stored procedure\" completed");
return(result);
}
catch (Exception e)
{
_logger.LogError($"An error occured during async data extraction via \"Stored procedure\", exception: {e}");
return(null);
}
}
}
public static int PostLastExecutedLsn(DbConnection connection, string providerName, string providerUrl, string lastPushedLsn, string lastExecutedLsn)
{
var query = $@"
IF EXISTS (SELECT * FROM [dbo].[CdcLastExecutedLsn] WHERE providerUrl = {@ProviderUrl})
UPDATE [dbo].[CdcLastExecutedLsn]
SET
ProviderName = {@ProviderName},
TransactionLsn = COALESCE({@LastPushedLsn}, TransactionLsn),
TransactionDate = sys.fn_cdc_map_lsn_to_time(CONVERT(binary, COALESCE({@LastPushedLsn}, TransactionLsn), 1)),
LastExecutedLsn = {@LastExecutedLsn}
OUTPUT inserted.id
WHERE providerUrl = {@ProviderUrl}
ELSE
INSERT INTO [dbo].[CdcLastExecutedLsn] (
[ProviderName],
[ProviderUrl],
[TransactionLsn],
[TransactionDate],
[LastExecutedLsn]
)
OUTPUT inserted.id
VALUES (
{@ProviderName},
{@ProviderUrl},
{@LastPushedLsn},
sys.fn_cdc_map_lsn_to_time(CONVERT(binary, {@LastPushedLsn}, 1)),
{@LastExecutedLsn}
)
";
using (var cmd = DbCommandFactory.Create(query, connection))
{
cmd.Parameters.AddWithValue(@ProviderName, providerName);
cmd.Parameters.AddWithValue(@ProviderUrl, providerUrl);
cmd.Parameters.AddWithValue(@LastPushedLsn, (object)lastPushedLsn ?? DBNull.Value);
cmd.Parameters.AddWithValue(@LastExecutedLsn, lastExecutedLsn);
return((int)cmd.ExecuteScalar());
}
}