private void OnDataChange(object sender, SqlDependencyEx.TableChangedEventArgs e)
{
// TODO: do stuff here
// If you want to monitor changes of `ActivityDate` field only
// you have to use the `TableChangedEventArgs` object.
// It has the `NotificationType` field and the `Data` field.
// The `Data` field contains information about the changes being made,
// so you can filter it.
int c = 0;
}
private static void DetailsTest(int insertsCount)
{
int elementsInDetailsCount = 0;
int changesReceived = 0;
using (SqlDependencyEx sqlDependency = new SqlDependencyEx(
TEST_CONNECTION_STRING,
TEST_DATABASE_NAME,
TEST_TABLE_NAME, "temp"))
{
sqlDependency.TableChanged += (o, e) =>
{
changesReceived++;
if (e.Data == null) return;
var inserted = e.Data.Element("inserted");
var deleted = e.Data.Element("deleted");
elementsInDetailsCount += inserted != null
? inserted.Elements("row").Count()
: 0;
elementsInDetailsCount += deleted != null
? deleted.Elements("row").Count()
: 0;
};
sqlDependency.Start();
MakeChunkedInsertDeleteUpdate(insertsCount);
// Wait a little bit to receive all changes.
Thread.Sleep(1000);
}
Assert.AreEqual(insertsCount * 2, elementsInDetailsCount);
Assert.AreEqual(3, changesReceived);
}
private static SqlDependencyEx.NotificationTypes[] GetMembers(SqlDependencyEx.NotificationTypes value)
{
return
Enum.GetValues(typeof(SqlDependencyEx.NotificationTypes))
.Cast<int>()
.Where(enumValue => enumValue != 0 && (enumValue & (int)value) == enumValue)
.Cast<SqlDependencyEx.NotificationTypes>()
.ToArray();
}
private void NotificationTest(
int changesCount,
int changesDelayInSec = 0,
string connStr = TEST_CONNECTION_STRING)
{
int changesReceived = 0;
using (SqlDependencyEx sqlDependency = new SqlDependencyEx(
connStr,
TEST_DATABASE_NAME,
TEST_TABLE_NAME, "temp"))
{
sqlDependency.TableChanged += (o, e) => changesReceived++;
sqlDependency.Start();
Thread.Sleep(changesDelayInSec * 1000);
MakeTableInsertDeleteChanges(changesCount);
// Wait a little bit to receive all changes.
Thread.Sleep(1000);
}
Assert.AreEqual(changesCount, changesReceived);
}
private static void NotificationTypeTest(int insertsCount, SqlDependencyEx.NotificationTypes testType)
{
int elementsInDetailsCount = 0;
int changesReceived = 0;
int expectedElementsInDetails = 0;
var notificationTypes = GetMembers(testType);
foreach (var temp in notificationTypes)
switch (temp)
{
case SqlDependencyEx.NotificationTypes.Insert:
expectedElementsInDetails += insertsCount / 2;
break;
case SqlDependencyEx.NotificationTypes.Update:
expectedElementsInDetails += insertsCount;
break;
case SqlDependencyEx.NotificationTypes.Delete:
expectedElementsInDetails += insertsCount / 2;
break;
}
using (SqlDependencyEx sqlDependency = new SqlDependencyEx(
TEST_CONNECTION_STRING,
TEST_DATABASE_NAME,
TEST_TABLE_NAME, "temp", testType))
{
sqlDependency.TableChanged += (o, e) =>
{
changesReceived++;
if (e.Data == null) return;
var inserted = e.Data.Element("inserted");
var deleted = e.Data.Element("deleted");
elementsInDetailsCount += inserted != null
? inserted.Elements("row").Count()
: 0;
elementsInDetailsCount += deleted != null
? deleted.Elements("row").Count()
: 0;
};
sqlDependency.Start();
MakeChunkedInsertDeleteUpdate(insertsCount);
// Wait a little bit to receive all changes.
Thread.Sleep(1000);
}
Assert.AreEqual(expectedElementsInDetails, elementsInDetailsCount);
Assert.AreEqual(notificationTypes.Length, changesReceived);
}
public void ResourcesReleasabilityTest(int changesCount)
{
using (var sqlConnection = new SqlConnection(ADMIN_TEST_CONNECTION_STRING))
{
sqlConnection.Open();
int sqlConversationEndpointsCount = sqlConnection.GetUnclosedConversationEndpointsCount();
int sqlConversationGroupsCount = sqlConnection.GetConversationGroupsCount();
int sqlServiceQueuesCount = sqlConnection.GetServiceQueuesCount();
int sqlServicesCount = sqlConnection.GetServicesCount();
int sqlTriggersCount = sqlConnection.GetTriggersCount();
int sqlProceduresCount = sqlConnection.GetProceduresCount();
using (SqlDependencyEx sqlDependency = new SqlDependencyEx(
TEST_CONNECTION_STRING,
TEST_DATABASE_NAME,
TEST_TABLE_NAME, "temp"))
{
sqlDependency.Start();
// Make sure we've created one queue, sevice, trigger and two procedures.
Assert.AreEqual(sqlServicesCount + 1, sqlConnection.GetServicesCount());
Assert.AreEqual(
sqlServiceQueuesCount + 1,
sqlConnection.GetServiceQueuesCount());
Assert.AreEqual(sqlTriggersCount + 1, sqlConnection.GetTriggersCount());
Assert.AreEqual(sqlProceduresCount + 2, sqlConnection.GetProceduresCount());
MakeTableInsertDeleteChanges(changesCount);
// Wait a little bit to process all changes.
Thread.Sleep(1000);
}
// Make sure we've released all resources.
Assert.AreEqual(sqlServicesCount, sqlConnection.GetServicesCount());
Assert.AreEqual(
sqlConversationGroupsCount,
sqlConnection.GetConversationGroupsCount());
Assert.AreEqual(
sqlServiceQueuesCount,
sqlConnection.GetServiceQueuesCount());
Assert.AreEqual(
sqlConversationEndpointsCount,
sqlConnection.GetUnclosedConversationEndpointsCount());
Assert.AreEqual(sqlTriggersCount, sqlConnection.GetTriggersCount());
Assert.AreEqual(sqlProceduresCount, sqlConnection.GetProceduresCount());
}
}
public void ClearDatabaseTest()
{
Func<int> getDbDepCount =
() =>
SqlDependencyEx.GetDependencyDbIdentities(
TEST_CONNECTION_STRING,
TEST_DATABASE_NAME).Length;
var dep1 = new SqlDependencyEx(
TEST_CONNECTION_STRING,
TEST_DATABASE_NAME,
TEST_TABLE_NAME,
"temp",
identity: 4);
var dep2 = new SqlDependencyEx(
TEST_CONNECTION_STRING,
TEST_DATABASE_NAME,
TEST_TABLE_NAME,
"temp",
identity: 5);
dep1.Start();
// Make sure db has been got 1 dependency object.
Assert.AreEqual(1, getDbDepCount());
dep2.Start();
// Make sure db has been got 2 dependency object.
Assert.AreEqual(2, getDbDepCount());
// Forced db cleaning
SqlDependencyEx.CleanDatabase(TEST_CONNECTION_STRING, TEST_DATABASE_NAME);
// Make sure db has no any dependency objects.
Assert.AreEqual(0, getDbDepCount());
dep1.Dispose();
dep2.Dispose();
}
public void GetActiveDbListenersTest()
{
Func<int> getDbDepCount =
() =>
SqlDependencyEx.GetDependencyDbIdentities(
TEST_CONNECTION_STRING,
TEST_DATABASE_NAME).Length;
using (var dep1 = new SqlDependencyEx(TEST_CONNECTION_STRING, TEST_DATABASE_NAME, TEST_TABLE_NAME, "temp"
, identity: 4))
using(var dep2 = new SqlDependencyEx(TEST_CONNECTION_STRING, TEST_DATABASE_NAME, TEST_TABLE_NAME, "temp"
, identity: 5))
{
dep1.Start();
// Make sure db has been got 1 dependency object.
Assert.AreEqual(1, getDbDepCount());
dep2.Start();
// Make sure db has been got 2 dependency object.
Assert.AreEqual(2, getDbDepCount());
}
// Make sure db has no any dependency objects.
Assert.AreEqual(0, getDbDepCount());
}
public void AdminPermissionExceptionCheckTest()
{
const string ScriptDisableBroker = @"
ALTER DATABASE [TestDatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE [TestDatabase] SET DISABLE_BROKER;
ALTER DATABASE [TestDatabase] SET MULTI_USER WITH ROLLBACK IMMEDIATE";
// It is impossible to start notification without configured service broker.
ExecuteNonQuery(ScriptDisableBroker, MASTER_CONNECTION_STRING);
bool errorReceived = false;
try
{
using (SqlDependencyEx test = new SqlDependencyEx(
TEST_CONNECTION_STRING,
TEST_DATABASE_NAME,
TEST_TABLE_NAME,
"temp")) test.Start();
}
catch (SqlException) { errorReceived = true; }
Assert.AreEqual(true, errorReceived);
// Service broker supposed to be configured automatically with MASTER connection string.
NotificationTest(10, connStr: MASTER_CONNECTION_STRING);
}
public void MainPermissionExceptionCheckTest()
{
ExecuteNonQuery("USE [TestDatabase] DENY CREATE PROCEDURE TO [TempUser];", MASTER_CONNECTION_STRING);
bool errorReceived = false;
try
{
using (SqlDependencyEx test = new SqlDependencyEx(
TEST_CONNECTION_STRING,
TEST_DATABASE_NAME,
TEST_TABLE_NAME,
"temp")) test.Start();
}
catch (SqlException) { errorReceived = true; }
Assert.AreEqual(true, errorReceived);
// It is impossible to start notification without CREATE PROCEDURE permission.
ExecuteNonQuery("USE [TestDatabase] GRANT CREATE PROCEDURE TO [TempUser];", MASTER_CONNECTION_STRING);
errorReceived = false;
try
{
using (SqlDependencyEx test = new SqlDependencyEx(
TEST_CONNECTION_STRING,
TEST_DATABASE_NAME,
TEST_TABLE_NAME,
"temp")) test.Start();
}
catch (SqlException) { errorReceived = true; }
Assert.AreEqual(false, errorReceived);
// There is supposed to be no exceptions with admin rights.
using (SqlDependencyEx test = new SqlDependencyEx(
MASTER_CONNECTION_STRING,
TEST_DATABASE_NAME,
TEST_TABLE_NAME,
"temp")) test.Start();
}
