public static string GetMaxLsn(SqlConnection connection)
{
using (var cmd = SqlCommandFactory.Create("SELECT CONVERT(varchar(22), sys.fn_cdc_get_max_lsn(), 1);", connection))
{
return((string)cmd.ExecuteScalar());
}
}
public static void DeleteSentNotifications(SqlConnection connection)
{
using (var cmd = SqlCommandFactory.Create(DeleteSentNotificationsQuery, connection))
{
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
}
}
public static string GetLastExecutedLsn(SqlConnection connection, string providerUrl)
{
using (var cmd = SqlCommandFactory.Create($"SELECT TOP(1) LastExecutedLsn FROM [dbo].[CdcLastExecutedLsn] WHERE ProviderUrl = {@ProviderUrl};", connection))
{
cmd.Parameters.AddWithValue(@ProviderUrl, providerUrl);
return((string)cmd.ExecuteScalar());
}
}
public static void InsertNotifications(SqlConnection connection, string notificationsXml)
{
using (var cmd = SqlCommandFactory.Create(InsertNotificationsQuery, connection))
{
cmd.CommandType = CommandType.Text;
var parameter = cmd.Parameters.AddWithValue("@notifications", notificationsXml);
parameter.SqlDbType = SqlDbType.Xml;
cmd.ExecuteNonQuery();
}
}
public void Create_NoArgument_CreatesDefaultWrapper()
{
// Arrange
var factory = new SqlCommandFactory();
// Act
var actualSqlComand = factory.Create();
// Assert
Assert.IsNotNull(actualSqlComand);
Assert.IsNotNull(actualSqlComand.SqlCommandInstance);
}
private static void ExecuteIdsQuery(SqlConnection connection, string query, IEnumerable <int> ids)
{
using (var cmd = SqlCommandFactory.Create(query, connection))
{
cmd.CommandType = CommandType.Text;
var idsTable = Common.IdsToDataTable(ids);
var parameter = cmd.Parameters.AddWithValue("@ids", idsTable);
parameter.SqlDbType = SqlDbType.Structured;
parameter.TypeName = "dbo.Ids";
cmd.ExecuteNonQuery();
}
}
public void Create_SqlTextArgument_CreatesSqlCommandWithGivenSqlText()
{
// Arrange
var expectedSqlCommand = "SELECT * FROM dbo.table1";
var factory = new SqlCommandFactory();
// Act
var actualSqlComand = factory.Create(expectedSqlCommand);
// Assert
Assert.IsNotNull(actualSqlComand);
Assert.AreEqual(expectedSqlCommand, actualSqlComand.SqlCommandInstance.CommandText);
}
public void Create_ExistingSqlCommandObject_WrapsTheObject()
{
// Arrange
var expectedSqlCommand = new SqlCommand();
var factory = new SqlCommandFactory();
// Act
var actualSqlComand = factory.Create(expectedSqlCommand);
// Assert
Assert.IsNotNull(actualSqlComand);
Assert.AreSame(expectedSqlCommand, actualSqlComand.SqlCommandInstance);
}
private static void ExecuteIdsQuery(SqlConnection connection, string query, IEnumerable <int> ids, string lastExceptionMessage = null)
{
using (var cmd = SqlCommandFactory.Create(query, connection))
{
cmd.CommandType = CommandType.Text;
var idsTable = Common.IdsToDataTable(ids);
var parameter = cmd.Parameters.AddWithValue("@ids", idsTable);
parameter.SqlDbType = SqlDbType.Structured;
parameter.TypeName = "dbo.Ids";
cmd.Parameters.Add(new SqlParameter("@lastExceptionMessage", SqlDbType.NVarChar, -1)
{
Value = (object)lastExceptionMessage ?? DBNull.Value
});
cmd.ExecuteNonQuery();
}
}
public static DataTable GetCdcTableData(SqlConnection 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 = SqlCommandFactory.Create(sb.ToString(), connection))
using (var da = new SqlDataAdapter(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 static int PostLastExecutedLsn(SqlConnection 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 = SqlCommandFactory.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());
}
}