public void TestSubscriptionHash()
{
SqlCommand sqlCommand = new SqlCommand();
sqlCommand.Parameters.Add(AccessDB.CreateSqlParameter("param1", SqlDbType.Int, 1));
sqlCommand.Parameters.Add(AccessDB.CreateSqlParameter("param2", SqlDbType.Int, 1));
Subscription subscription1 = new Subscription("TestApp", "subscriberString", "dbo", "P_TestProcedure", sqlCommand.Parameters);
sqlCommand = new SqlCommand();
sqlCommand.Parameters.Add(AccessDB.CreateSqlParameter("param1", SqlDbType.Int, 1));
sqlCommand.Parameters.Add(AccessDB.CreateSqlParameter("param2", SqlDbType.Int, 1));
Subscription subscription2 = new Subscription("TestApp", "subscriberString2", "dbo", "P_TestProcedure", sqlCommand.Parameters, 15000);
if (subscription1.GetHashCode() != subscription2.GetHashCode())
{
Assert.Fail("Get hask code function returns diffrent values for similar object.");
}
sqlCommand = new SqlCommand();
sqlCommand.Parameters.Add(AccessDB.CreateSqlParameter("param1", SqlDbType.Int, 1));
sqlCommand.Parameters.Add(AccessDB.CreateSqlParameter("param2", SqlDbType.Int, 2));
Subscription subscription3 = new Subscription("TestApp", "subscriberString3", "dbo", "P_TestProcedure", sqlCommand.Parameters, 15000);
if (subscription3.GetHashCode() == subscription2.GetHashCode())
{
Assert.Fail("Get hask code function returns same values for diffrent object.");
}
}
public static SqlParameterCollection GetSqlParameterCollectionForTestProcedure(int?param1 = null, int?param2 = null, bool insert1 = false, bool insert2 = false, bool delete1 = false, bool delete2 = false)
{
SqlCommand sqlCommand = new SqlCommand();
if (param1.HasValue)
{
sqlCommand.Parameters.Add(AccessDB.CreateSqlParameter("@V_Param1", SqlDbType.Int, param1));
}
else
{
sqlCommand.Parameters.Add(AccessDB.CreateSqlParameter("@V_Param1", SqlDbType.Int, DBNull.Value));
}
if (param2.HasValue)
{
sqlCommand.Parameters.Add(AccessDB.CreateSqlParameter("@V_Param2", SqlDbType.Int, param2));
}
else
{
sqlCommand.Parameters.Add(AccessDB.CreateSqlParameter("@V_Param2", SqlDbType.Int, DBNull.Value));
}
sqlCommand.Parameters.Add(AccessDB.CreateSqlParameter("@V_Insert1", SqlDbType.Bit, insert1));
sqlCommand.Parameters.Add(AccessDB.CreateSqlParameter("@V_Insert2", SqlDbType.Bit, insert2));
sqlCommand.Parameters.Add(AccessDB.CreateSqlParameter("@V_Delete1", SqlDbType.Bit, delete1));
sqlCommand.Parameters.Add(AccessDB.CreateSqlParameter("@V_Delete2", SqlDbType.Bit, delete2));
return(sqlCommand.Parameters);
}
public void ReceiveTwoSubscriptions()
{
SqlParameterCollection sqlParameters = GetSqlParameterCollectionForTestProcedure(10);
SetDBState.SetTwoSubscriptionInstalledDB(
CommonTestsValues.DefaultTestDBName,
CommonTestsValues.MainServiceName,
CommonTestsValues.LoginPass,
CommonTestsValues.FirstSunscriberName,
"P_TestGetProcedure",
sqlParameters,
CommonTestsValues.SecondSunscriberName,
"P_TestGetProcedure",
sqlParameters);
SqlCommand dataChangeCommand = new SqlCommand("dbo.P_TestSetProcedure");
dataChangeCommand.Parameters.Add(AccessDB.CreateSqlParameter("@V_Param1", SqlDbType.Int, 10));
dataChangeCommand.Parameters.Add(AccessDB.CreateSqlParameter("@V_Param2", SqlDbType.Int, 10));
dataChangeCommand.Parameters.Add(AccessDB.CreateSqlParameter("@V_Insert1", SqlDbType.Bit, true));
dataChangeCommand.Parameters.Add(AccessDB.CreateSqlParameter("@V_Insert2", SqlDbType.Bit, true));
dataChangeCommand.Parameters.Add(AccessDB.CreateSqlParameter("@V_Delete1", SqlDbType.Bit, false));
dataChangeCommand.Parameters.Add(AccessDB.CreateSqlParameter("@V_Delete2", SqlDbType.Bit, false));
serviceAccessDBAdmin.SQLRunNonQueryProcedure(dataChangeCommand, 30);
for (int i = 0; i < 2; i++)
{
List <NotificationMessage> testResult = SqlProceduresInstance.ReceiveSubscription(CommonTestsValues.MainServiceName, 15);
if (testResult.Count < 1 || string.IsNullOrWhiteSpace(testResult[0].MessageString) || testResult[0].MessageType != NotificationMessageType.InsertedData || testResult[0].Inserted == null)
{
Assert.Fail(testResult[0].MessageString);
}
}
}
/// <summary>
/// Procedure used to receive messages from DB.
/// </summary>
/// <param name="appName"> Application name from which messages will be returned. </param>
/// <param name="receiveTimeout"> Timeout used during execution of queries in seconds. Default: 30s. (schould be less tchan typical app pool recucle time = 90s )</param>
/// <returns></returns>
public List <NotificationMessage> ReceiveSubscription(string appName, int receiveTimeout = 30)
{
string schemaName = "[" + appName + "]";
SqlCommand command = new SqlCommand(schemaName + "." + ProcedureNameReceiveNotification);
command.Parameters.Add(AccessDB.CreateSqlParameter("V_ReceiveTimeout", SqlDbType.Int, receiveTimeout * 1000));
List <NotificationMessage> result = AccessDBInstance.SQLRunQueryProcedure <NotificationMessage>(command, null, 0);
return(result);
}
/// <summary>
/// Procedure used to uninstall subsctiption from DB.
/// </summary>
/// <param name="subscription"> Subscription to be uninstalled. </param>
public void UninstallSubscription(Subscription subscription)
{
string schemaName = "[" + subscription.MainServiceName + "]";
SqlCommand command = new SqlCommand(schemaName + "." + ProcedureNameUninstall);
command.Parameters.Add(AccessDB.CreateSqlParameter("V_SubscriberString", SqlDbType.NVarChar, subscription.SubscriberString));
command.Parameters.Add(AccessDB.CreateSqlParameter("V_SubscriptionHash", SqlDbType.Int, subscription.CanBeInstalled() ? (object)subscription.GetHashCode() : null));
command.Parameters.Add(AccessDB.CreateSqlParameter("V_ProcedureSchemaName", SqlDbType.NVarChar, subscription.ProcedureSchemaName));
command.Parameters.Add(AccessDB.CreateSqlParameter("V_ProcedureName", SqlDbType.NVarChar, subscription.ProcedureName));
command.Parameters.Add(AccessDB.CreateSqlParameter("TBL_ProcedureParameters", SqlDbType.Structured, SqlParameterCollectionToDataTable(subscription.ProcedureParameters)));
command.Parameters.Add(AccessDB.CreateSqlParameter("V_NotificationValidFor", SqlDbType.Int, subscription.ValidFor));
AccessDBInstance.SQLRunNonQueryProcedure(command);
}
/// <summary>
/// Procedure used to install subsctiption in DB.
/// </summary>
/// <param name="subscription"> Subscription to be installed. </param>
public void InstallSubscription(Subscription subscription)
{
if (!subscription.CanBeInstalled())
{
throw new ArgumentException(subscription.GetHashText() + " subscription cannot be installed, as not enougth data provided. ");
}
string schemaName = "[" + subscription.MainServiceName + "]";
SqlCommand command = new SqlCommand(schemaName + "." + ProcedureNameInstall);
command.Parameters.Add(AccessDB.CreateSqlParameter("V_SubscriberString", SqlDbType.NVarChar, subscription.SubscriberString));
command.Parameters.Add(AccessDB.CreateSqlParameter("V_SubscriptionHash", SqlDbType.Int, subscription.GetHashCode()));
command.Parameters.Add(AccessDB.CreateSqlParameter("V_ProcedureSchemaName", SqlDbType.NVarChar, subscription.ProcedureSchemaName));
command.Parameters.Add(AccessDB.CreateSqlParameter("V_ProcedureName", SqlDbType.NVarChar, subscription.ProcedureName));
command.Parameters.Add(AccessDB.CreateSqlParameter("TBL_ProcedureParameters", SqlDbType.Structured, SqlParameterCollectionToDataTable(subscription.ProcedureParameters)));
command.Parameters.Add(AccessDB.CreateSqlParameter("V_NotificationValidFor", SqlDbType.Int, subscription.ValidFor));
AccessDBInstance.SQLRunNonQueryProcedure(command);
}
/// <summary>
/// Creates subsctription object witch provided data.
/// </summary>
/// <param name="mainServiceName"> Application name asociated with subsctription. </param>
/// <param name="subscriberString"> String which is used to identify subsctriber. Max 200 chars. </param>
/// <param name="procedureSchemaName"> Procedure schema name for which subscription will be made. </param>
/// <param name="procedureName"> Procedure name for which subscription will be made. </param>
/// <param name="procedureParameters"> Procedure parameters for which subscription will be made. </param>
/// <param name="validFor"> Amout of seconds till when subscription will be active. Default: two days. </param>
public Subscription(string mainServiceName = "", string subscriberString = "", string procedureSchemaName = "", string procedureName = "", SqlParameterCollection procedureParameters = null, int validFor = 172800)
{
if (subscriberString.Length > 200)
{
throw new ArgumentException("Subscriber String must be shorter than 200 chars. Provided subsctiber string: " + subscriberString);
}
MainServiceName = mainServiceName;
SubscriberString = subscriberString;
ProcedureSchemaName = procedureSchemaName;
ProcedureName = procedureName;
ProcedureParameters = procedureParameters;
ValidFor = validFor;
if (ProcedureParameters == null)
{
SqlCommand cmd = new SqlCommand();
cmd.Parameters.Add(AccessDB.CreateSqlParameter("@V_RemoveAllParameters", SqlDbType.Bit, true));
ProcedureParameters = cmd.Parameters;
}
}
public void PublicReceiveSingleSubscription()
{
SetDBState.SetAdminInstalledDB(
CommonTestsValues.DefaultTestDBName,
CommonTestsValues.MainServiceName,
CommonTestsValues.LoginPass);
DependencyDB.AddReceiver(
CommonTestsValues.MainServiceName,
CommonTestsValues.ServiceConnectionString
);
Receiver receiver = DependencyDB.GetReceiver(CommonTestsValues.MainServiceName);
receiver.MessageHandler += HandleMsg;
receiver.ErrorMessageHandler += HandleMsg;
receiver.UnsubscribedMessageHandler += HandleMsg;
Task receiverTask = new Task(receiver.Listen);
receiverTask.Start();
SqlParameterCollection sqlParameters = SqlProceduresTests.GetSqlParameterCollectionForTestProcedure(10);
DateTime validTill = (DateTime.Now).AddDays(5.0);
receiver.Subscribe(
CommonTestsValues.FirstSunscriberName,
CommonTestsValues.SubscribedProcedureSchema,
"P_TestGetProcedure",
sqlParameters,
validTill
);
AccessDB accessDB = new AccessDB(CommonTestsValues.AdminConnectionString);
SqlCommand dataChangeCommand = new SqlCommand("dbo.P_TestSetProcedure");
dataChangeCommand.Parameters.Add(AccessDB.CreateSqlParameter("@V_Param1", SqlDbType.Int, 10));
dataChangeCommand.Parameters.Add(AccessDB.CreateSqlParameter("@V_Param2", SqlDbType.Int, 10));
dataChangeCommand.Parameters.Add(AccessDB.CreateSqlParameter("@V_Insert1", SqlDbType.Bit, true));
dataChangeCommand.Parameters.Add(AccessDB.CreateSqlParameter("@V_Insert2", SqlDbType.Bit, true));
dataChangeCommand.Parameters.Add(AccessDB.CreateSqlParameter("@V_Delete1", SqlDbType.Bit, false));
dataChangeCommand.Parameters.Add(AccessDB.CreateSqlParameter("@V_Delete2", SqlDbType.Bit, false));
accessDB.SQLRunNonQueryProcedure(dataChangeCommand, 30);
Task waitForResults = new Task(() =>
{
while (Message == null)
{
Thread.Sleep(100);
}
});
waitForResults.Start();
waitForResults.Wait(10000);
receiverTask.Wait(1);
DependencyDB.StopReceiver(CommonTestsValues.MainServiceName);
if (Message == null)
{
Assert.Fail();
}
}
public void TestStopListening()
{
SqlParameterCollection sqlParameters = SqlProceduresTests.GetSqlParameterCollectionForTestProcedure(10);
SetDBState.SetSingleSubscriptionInstalledDB(
CommonTestsValues.DefaultTestDBName,
CommonTestsValues.MainServiceName,
CommonTestsValues.LoginPass,
CommonTestsValues.FirstSunscriberName,
"P_TestGetProcedure",
sqlParameters);
Receiver receiver = DependencyDB.AddReceiver(
CommonTestsValues.MainServiceName,
CommonTestsValues.ServiceConnectionString
);
receiver.MessageHandler += HandleMsg;
receiver.ErrorMessageHandler += HandleMsg;
receiver.UnsubscribedMessageHandler += HandleMsg;
Task receiverTask = new Task(receiver.Listen);
receiverTask.Start();
SqlCommand dataChangeCommand = new SqlCommand("dbo.P_TestSetProcedure");
dataChangeCommand.Parameters.Add(AccessDB.CreateSqlParameter("@V_Param1", SqlDbType.Int, 10));
dataChangeCommand.Parameters.Add(AccessDB.CreateSqlParameter("@V_Param2", SqlDbType.Int, 10));
dataChangeCommand.Parameters.Add(AccessDB.CreateSqlParameter("@V_Insert1", SqlDbType.Bit, true));
dataChangeCommand.Parameters.Add(AccessDB.CreateSqlParameter("@V_Insert2", SqlDbType.Bit, true));
dataChangeCommand.Parameters.Add(AccessDB.CreateSqlParameter("@V_Delete1", SqlDbType.Bit, false));
dataChangeCommand.Parameters.Add(AccessDB.CreateSqlParameter("@V_Delete2", SqlDbType.Bit, false));
serviceAccessDBAdmin.SQLRunNonQueryProcedure(dataChangeCommand, 30);
Task waitForResults = new Task(() =>
{
while (Message == null)
{
Thread.Sleep(100);
}
});
waitForResults.Start();
waitForResults.Wait(10000);
if (Message == null)
{
Assert.Fail("No mesage received after DependencyDB.StartListener().");
}
DependencyDB.StopReceiver(CommonTestsValues.MainServiceName);
Thread.Sleep(100000);
Message = null;
dataChangeCommand = new SqlCommand("dbo.P_TestSetProcedure");
dataChangeCommand.Parameters.Add(AccessDB.CreateSqlParameter("@V_Param1", SqlDbType.Int, 10));
dataChangeCommand.Parameters.Add(AccessDB.CreateSqlParameter("@V_Param2", SqlDbType.Int, 10));
dataChangeCommand.Parameters.Add(AccessDB.CreateSqlParameter("@V_Insert1", SqlDbType.Bit, true));
dataChangeCommand.Parameters.Add(AccessDB.CreateSqlParameter("@V_Insert2", SqlDbType.Bit, true));
dataChangeCommand.Parameters.Add(AccessDB.CreateSqlParameter("@V_Delete1", SqlDbType.Bit, false));
dataChangeCommand.Parameters.Add(AccessDB.CreateSqlParameter("@V_Delete2", SqlDbType.Bit, false));
serviceAccessDBAdmin.SQLRunNonQueryProcedure(dataChangeCommand, 30);
waitForResults = new Task(() =>
{
while (Message == null)
{
Thread.Sleep(100);
}
});
waitForResults.Start();
waitForResults.Wait(10000);
receiverTask.Wait(1);
if (Message != null)
{
Assert.Fail("Mesage received after DependencyDB.StopListener().");
}
}
public void SingleChangeWithMultipleSubscribers()
{
SingleChangeWithMultipleSubscribers_Subscribers = new List <string>();
SetDBState.SetAdminInstalledDB(
CommonTestsValues.DefaultTestDBName,
CommonTestsValues.MainServiceName,
CommonTestsValues.LoginPass);
DependencyDB.AddReceiver(
CommonTestsValues.MainServiceName,
CommonTestsValues.ServiceConnectionString
);
Receiver receiver = DependencyDB.GetReceiver(CommonTestsValues.MainServiceName);
receiver.MessageHandler += SingleChangeWithMultipleSubscribers_HandleMsg;
receiver.ErrorMessageHandler += SingleChangeWithMultipleSubscribers_HandleMsg;
receiver.UnsubscribedMessageHandler += SingleChangeWithMultipleSubscribers_HandleMsg;
Task receiverTask = new Task(receiver.Listen);
receiverTask.Start();
SqlParameterCollection sqlParameters = SqlProceduresTests.GetSqlParameterCollectionForTestProcedure(10);
DateTime validTill = (DateTime.Now).AddDays(5.0);
for (int i = 0; i < CountParallelInstances; i++)
{
string subscriberName = "subscriber" + i;
SingleChangeWithMultipleSubscribers_Subscribers.Add(subscriberName);
receiver.Subscribe(
subscriberName,
CommonTestsValues.SubscribedProcedureSchema,
"P_TestGetProcedure",
sqlParameters,
validTill
);
}
AccessDB accessDB = new AccessDB(CommonTestsValues.AdminConnectionString);
SqlCommand dataChangeCommand = new SqlCommand("dbo.P_TestSetProcedure");
dataChangeCommand.Parameters.Add(AccessDB.CreateSqlParameter("@V_Param1", SqlDbType.Int, 10));
dataChangeCommand.Parameters.Add(AccessDB.CreateSqlParameter("@V_Param2", SqlDbType.Int, 10));
dataChangeCommand.Parameters.Add(AccessDB.CreateSqlParameter("@V_Insert1", SqlDbType.Bit, true));
dataChangeCommand.Parameters.Add(AccessDB.CreateSqlParameter("@V_Insert2", SqlDbType.Bit, true));
dataChangeCommand.Parameters.Add(AccessDB.CreateSqlParameter("@V_Delete1", SqlDbType.Bit, false));
dataChangeCommand.Parameters.Add(AccessDB.CreateSqlParameter("@V_Delete2", SqlDbType.Bit, false));
accessDB.SQLRunNonQueryProcedure(dataChangeCommand, 30);
Task waitForResults = new Task(() =>
{
while (SingleChangeWithMultipleSubscribers_Subscribers.Count > 0)
{
Thread.Sleep(100);
}
});
waitForResults.Start();
waitForResults.Wait(20000);
receiverTask.Wait(1);
DependencyDB.StopReceiver(CommonTestsValues.MainServiceName);
if (SingleChangeWithMultipleSubscribers_Subscribers.Count > 0)
{
Assert.Fail(SingleChangeWithMultipleSubscribers_Subscribers.Count + " subscribers not recived notification.");
}
}
/// <summary>
/// Public constructor for creating instance from Message string. Message string should be Xml with proper structure.
/// </summary>
/// <param name="xmlMessage"> Message string containing all neccesary information. </param>
public NotificationMessage(string xmlMessage)
{
if (string.IsNullOrWhiteSpace(xmlMessage))
{
MessageString = "";
MessageType = NotificationMessageType.Empty;
return;
}
MessageString = xmlMessage;
Message = XDocument.Parse(xmlMessage);
base.MainServiceName = Message.Element("notification").Attribute("servicename").Value;
base.SubscriberString = Message.Element("notification").Attribute("subscriberstring").Value;
ValidTill = DateTime.Parse(Message.Element("notification").Attribute("validtill").Value);
base.ValidFor = Convert.ToInt32((ValidTill - DateTime.Now).TotalSeconds);
base.ProcedureSchemaName = Message.Element("notification").Element("schema").Attribute("name").Value;
base.ProcedureName = Message.Element("notification").Element("schema").Element("procedure").Attribute("name").Value;
if (Message.Element("notification").Element("error") != null)
{
Error = new NotificationMessageError(Message.Element("notification").Element("error"));
}
if (Message.Element("notification").Element("inserted") != null)
{
Inserted = new NotificationData(Message.Element("notification").Element("inserted"));
}
if (Message.Element("notification").Element("deleted") != null)
{
Deleted = new NotificationData(Message.Element("notification").Element("deleted"));
}
if (Inserted != null)
{
MessageType = NotificationMessageType.InsertedData;
}
if (Deleted != null)
{
MessageType = NotificationMessageType.DeletedData;
}
if (Inserted != null && Deleted != null)
{
MessageType = NotificationMessageType.InsertedAndDeletedData;
}
if (Message.Element("notification").Element("unsubscribed") != null)
{
MessageType = NotificationMessageType.Unsubscribed;
}
if (Error != null)
{
MessageType = NotificationMessageType.Error;
}
SqlCommand ProcedureCmd = new SqlCommand(base.ProcedureSchemaName + "." + base.ProcedureName);
foreach (XElement parameter in Message.Element("notification").Element("schema").Element("procedure").Elements("parameter"))
{
string parameterName = parameter.Element("name").Value;
SqlDbType parameterType = (SqlDbType)Enum.Parse(typeof(SqlDbType), parameter.Element("type").Value);
string parameterValue = parameter.Element("value").Value;
ProcedureCmd.Parameters.Add(AccessDB.CreateSqlParameter(parameterName, parameterType, parameterValue));
}
base.ProcedureParameters = ProcedureCmd.Parameters;
}