[Test] // ctor (String, String, Int32)
public void Constructor2()
{
SqlNotificationRequest nr;
nr = new SqlNotificationRequest("UD", "options", 5);
Assert.AreEqual("options", nr.Options, "#A1");
Assert.AreEqual(5, nr.Timeout, "#A2");
Assert.AreEqual("UD", nr.UserData, "#A3");
nr = new SqlNotificationRequest(string.Empty, " ", 0);
Assert.AreEqual(" ", nr.Options, "#B1");
Assert.AreEqual(0, nr.Timeout, "#B2");
Assert.AreEqual(string.Empty, nr.UserData, "#B3");
nr = new SqlNotificationRequest(" ", "O", int.MaxValue);
Assert.AreEqual("O", nr.Options, "#C1");
Assert.AreEqual(int.MaxValue, nr.Timeout, "#C2");
Assert.AreEqual(" ", nr.UserData, "#C3");
nr = new SqlNotificationRequest((string)null, "O", 7);
Assert.AreEqual("O", nr.Options, "#D1");
Assert.AreEqual(7, nr.Timeout, "#D2");
Assert.IsNull(nr.UserData, "#D3");
nr = new SqlNotificationRequest("UD", (string)null, 14);
Assert.IsNull(nr.Options, "#E1");
Assert.AreEqual(14, nr.Timeout, "#E2");
Assert.AreEqual("UD", nr.UserData, "#E3");
nr = new SqlNotificationRequest(new string ('A', 0xffff), new string ('X', 0xffff), 3);
Assert.AreEqual(new string ('X', 0xffff), nr.Options, "#F1");
Assert.AreEqual(3, nr.Timeout, "#F2");
Assert.AreEqual(new string ('A', 0xffff), nr.UserData, "#F3");
}
internal void StartTimer(SqlNotificationRequest notificationRequest)
{
IntPtr ptr;
Bid.NotificationsScopeEnter(out ptr, "<sc.SqlDependency.StartTimer|DEP> %d#", this.ObjectID);
try
{
if (this._expirationTime == DateTime.MaxValue)
{
Bid.NotificationsTrace("<sc.SqlDependency.StartTimer|DEP> We've timed out, executing logic.\n");
int timeout = 0x69780;
if (this._timeout != 0)
{
timeout = this._timeout;
}
if (((notificationRequest != null) && (notificationRequest.Timeout < timeout)) && (notificationRequest.Timeout != 0))
{
timeout = notificationRequest.Timeout;
}
this._expirationTime = DateTime.UtcNow.AddSeconds((double)timeout);
SqlDependencyPerAppDomainDispatcher.SingletonInstance.StartTimer(this);
}
}
finally
{
Bid.ScopeLeave(ref ptr);
}
}
[Test] // ctor ()
public void Constructor1()
{
SqlNotificationRequest nr = new SqlNotificationRequest();
Assert.IsNull(nr.Options, "#1");
Assert.AreEqual(0, nr.Timeout, "#2");
Assert.IsNull(nr.UserData, "#3");
}
public static async void ADO_CommandProperties(SqlCommand cmd)
{
Console.WriteLine($@"
Timeout: { cmd.CommandTimeout}
CmdType: { cmd.CommandType}
Conn: { cmd.Connection}
Container: { cmd.Container}
Notification: { cmd.Notification}
Parameters: { cmd.Parameters}
");
cmd.CommandType = CommandType.StoredProcedure;
// cmd.CommandType = CommandType.TableDirect;
cmd.CommandType = CommandType.Text;
//ComponentCollection comps = cmd.Container.Components;
SqlNotificationRequest notificationReq = cmd.Notification;
bool autoEnlist = cmd.NotificationAutoEnlist;
SqlParameterCollection parameters = cmd.Parameters;
//cmd.Site;
// cmd.Transaction;
// cmd.UpdatedRowSource
#if false //obsolete - use Async instead
IAsyncResult ar1 = cmd.BeginExecuteNonQuery();
IAsyncResult ar2 = cmd.BeginExecuteReader();
IAsyncResult ar3 = cmd.BeginExecuteXmlReader();
cmd.EndExecuteNonQuery(ar1);
cmd.EndExecuteReader(ar2);
cmd.EndExecuteXmlReader(ar3);
#endif
cmd.Cancel(); // tries to cancel the execution of command
SqlParameter parameter = cmd.CreateParameter();
parameters.Add(parameter);
int cnt = cmd.ExecuteNonQuery();
cnt = await cmd.ExecuteNonQueryAsync();
SqlDataReader rd1 = cmd.ExecuteReader();
SqlDataReader rd2 = await cmd.ExecuteReaderAsync();
cmd.ExecuteScalar();
await cmd.ExecuteScalarAsync();
cmd.ExecuteXmlReader();
await cmd.ExecuteXmlReaderAsync();
cmd.Prepare();
}
public void Timeout()
{
SqlNotificationRequest nr = new SqlNotificationRequest();
nr.Timeout = 5;
Assert.AreEqual(5, nr.Timeout, "#1");
nr.Timeout = 0;
Assert.AreEqual(0, nr.Timeout, "#2");
nr.Timeout = int.MaxValue;
Assert.AreEqual(int.MaxValue, nr.Timeout, "#3");
}
/// <summary>
/// Create a SqlNotificationRequest and invoke the event.
/// </summary>
private void RegisterSqlNotificationRequest()
{
request = new SqlNotificationRequest();
request.UserData = new Guid().ToString();
request.Options = String.Format("Service={0};", serviceName);
request.Timeout = notificationTimeout;
if (OnChanged != null)
{
OnChanged(this, null);
}
}
public void Clone()
{
#if NET_2_0
SqlNotificationRequest notificationReq = new SqlNotificationRequest();
#endif
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "sp_insert";
cmd.CommandTimeout = 100;
cmd.CommandType = CommandType.StoredProcedure;
cmd.DesignTimeVisible = false;
#if NET_2_0
cmd.Notification = notificationReq;
cmd.NotificationAutoEnlist = false;
#endif
cmd.Parameters.Add("@TestPar1", SqlDbType.Int);
cmd.Parameters ["@TestPar1"].Value = DBNull.Value;
#if NET_2_0
cmd.Parameters.AddWithValue("@BirthDate", DateTime.Now);
#else
cmd.Parameters.Add("@BirthDate", DateTime.Now);
#endif
cmd.UpdatedRowSource = UpdateRowSource.OutputParameters;
SqlCommand clone = (((ICloneable)(cmd)).Clone()) as SqlCommand;
Assert.AreEqual("sp_insert", clone.CommandText, "#1");
Assert.AreEqual(100, clone.CommandTimeout, "#2");
Assert.AreEqual(CommandType.StoredProcedure, clone.CommandType, "#3");
Assert.IsNull(cmd.Connection, "#4");
Assert.IsFalse(cmd.DesignTimeVisible, "#5");
#if NET_2_0
Assert.AreSame(notificationReq, cmd.Notification, "#6");
Assert.IsFalse(cmd.NotificationAutoEnlist, "#7");
#endif
Assert.AreEqual(2, clone.Parameters.Count, "#8");
Assert.AreEqual(100, clone.CommandTimeout, "#9");
#if NET_2_0
clone.Parameters.AddWithValue("@test", DateTime.Now);
#else
clone.Parameters.Add("@test", DateTime.Now);
#endif
clone.Parameters [0].ParameterName = "@ClonePar1";
Assert.AreEqual(3, clone.Parameters.Count, "#10");
Assert.AreEqual(2, cmd.Parameters.Count, "#11");
Assert.AreEqual("@ClonePar1", clone.Parameters [0].ParameterName, "#12");
Assert.AreEqual("@TestPar1", cmd.Parameters [0].ParameterName, "#13");
Assert.AreEqual("@BirthDate", clone.Parameters [1].ParameterName, "#14");
Assert.AreEqual("@BirthDate", cmd.Parameters [1].ParameterName, "#15");
Assert.IsNull(clone.Transaction, "#16");
}
public void Timeout_Value_Negative()
{
SqlNotificationRequest nr = new SqlNotificationRequest();
try {
nr.Timeout = -1;
Assert.Fail("#1");
} catch (ArgumentOutOfRangeException ex) {
Assert.AreEqual(typeof(ArgumentOutOfRangeException), ex.GetType(), "#2");
Assert.IsNull(ex.InnerException, "#3");
Assert.IsNotNull(ex.Message, "#4");
Assert.AreEqual("Timeout", ex.ParamName, "#5");
}
}
public void UserData_Value_ExceedMaxLength()
{
SqlNotificationRequest nr = new SqlNotificationRequest();
string userData = new string ('X', 0x10000);
try {
nr.UserData = userData;
Assert.Fail("#1");
} catch (ArgumentOutOfRangeException ex) {
Assert.AreEqual(typeof(ArgumentOutOfRangeException), ex.GetType(), "#2");
Assert.IsNull(ex.InnerException, "#3");
Assert.IsNotNull(ex.Message, "#4");
Assert.AreEqual("UserData", ex.ParamName, "#5");
}
}
public void UserData()
{
SqlNotificationRequest nr = new SqlNotificationRequest();
nr.UserData = "XYZ";
Assert.AreEqual("XYZ", nr.UserData, "#1");
nr.UserData = null;
Assert.IsNull(nr.UserData, "#2");
nr.UserData = " \r ";
Assert.AreEqual(" \r ", nr.UserData, "#3");
nr.UserData = string.Empty;
Assert.AreEqual(string.Empty, nr.UserData, "#4");
nr.UserData = new string ('X', 0xffff);
Assert.AreEqual(new string ('X', 0xffff), nr.UserData, "#5");
}
private void GetData()
{
// Make sure the command object does not already have
// a notification object associated with it.
command.Notification = null;
SqlNotificationRequest snr =
new SqlNotificationRequest();
snr.UserData = new Guid().ToString();
snr.Options = "Service=" + ServiceName;
// If a time-out occurs, a notification
// will indicate that is the
// reason for the notification.
snr.Timeout = NotificationTimeout;
command.Notification = snr;
// Start the background listener.
StartListener();
}
/* private static void GetLiteralInfo (DataRow dataTypeRow, out string literalPrefix, out string literalSuffix) {
*
* Object tempValue = dataTypeRow[DbMetaDataColumnNames.LiteralPrefix];
* if (tempValue == DBNull.Value) {
* literalPrefix = "";
* }
* else {
* literalPrefix = (string)dataTypeRow[DbMetaDataColumnNames.LiteralPrefix];
* }
* tempValue = dataTypeRow[DbMetaDataColumnNames.LiteralSuffix];
* if (tempValue == DBNull.Value) {
* literalSuffix = "";
* }
* else {
* literalSuffix = (string)dataTypeRow[DbMetaDataColumnNames.LiteralSuffix];
* }
* }
*/
protected override DataTable GetSchemaTable(DbCommand srcCommand)
{
SqlCommand sqlCommand = srcCommand as SqlCommand;
SqlNotificationRequest notificationRequest = sqlCommand.Notification;
bool notificationAutoEnlist = sqlCommand.NotificationAutoEnlist;
sqlCommand.Notification = null;
sqlCommand.NotificationAutoEnlist = false;
try {
using (SqlDataReader dataReader = sqlCommand.ExecuteReader(CommandBehavior.SchemaOnly | CommandBehavior.KeyInfo)){
return(dataReader.GetSchemaTable());
}
}
finally {
sqlCommand.Notification = notificationRequest;
sqlCommand.NotificationAutoEnlist = notificationAutoEnlist;
}
}
public void Clone()
{
SqlNotificationRequest notificationReq = new SqlNotificationRequest();
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "sp_insert";
cmd.CommandTimeout = 100;
cmd.CommandType = CommandType.StoredProcedure;
cmd.DesignTimeVisible = false;
cmd.Notification = notificationReq;
#if NET46
// see https://github.com/dotnet/SqlClient/issues/17
Assert.True(cmd.NotificationAutoEnlist);
#endif
cmd.Parameters.Add("@TestPar1", SqlDbType.Int);
cmd.Parameters["@TestPar1"].Value = DBNull.Value;
cmd.Parameters.AddWithValue("@BirthDate", DateTime.Now);
cmd.UpdatedRowSource = UpdateRowSource.OutputParameters;
SqlCommand clone = (((ICloneable)(cmd)).Clone()) as SqlCommand;
Assert.Equal("sp_insert", clone.CommandText);
Assert.Equal(100, clone.CommandTimeout);
Assert.Equal(CommandType.StoredProcedure, clone.CommandType);
Assert.Null(cmd.Connection);
Assert.False(cmd.DesignTimeVisible);
Assert.Same(notificationReq, cmd.Notification);
#if NET46
// see https://github.com/dotnet/SqlClient/issues/17
Assert.True(cmd.NotificationAutoEnlist);
#endif
Assert.Equal(2, clone.Parameters.Count);
Assert.Equal(100, clone.CommandTimeout);
clone.Parameters.AddWithValue("@test", DateTime.Now);
clone.Parameters[0].ParameterName = "@ClonePar1";
Assert.Equal(3, clone.Parameters.Count);
Assert.Equal(2, cmd.Parameters.Count);
Assert.Equal("@ClonePar1", clone.Parameters[0].ParameterName);
Assert.Equal("@TestPar1", cmd.Parameters[0].ParameterName);
Assert.Equal("@BirthDate", clone.Parameters[1].ParameterName);
Assert.Equal("@BirthDate", cmd.Parameters[1].ParameterName);
Assert.Null(clone.Transaction);
}
// This method is used by SqlCommand.
internal void StartTimer(SqlNotificationRequest notificationRequest)
{
if (_expirationTime == DateTime.MaxValue)
{
int seconds = SQL.SqlDependencyServerTimeout;
if (0 != _timeout)
{
seconds = _timeout;
}
if (notificationRequest != null && notificationRequest.Timeout < seconds && notificationRequest.Timeout != 0)
{
seconds = notificationRequest.Timeout;
}
// We use UTC to check if SqlDependency is expired, need to use it here as well.
_expirationTime = DateTime.UtcNow.AddSeconds(seconds);
SqlDependencyPerAppDomainDispatcher.SingletonInstance.StartTimer(this);
}
}
private void GetData()
{
_dataToWatch.Clear();
_cmd.Notification = null;
SqlNotificationRequest request = new SqlNotificationRequest();
request.UserData = Guid.NewGuid().ToString();
request.Options = "service=" + _serviceName + ";";
request.Timeout = _notificationTimeout;
_cmd.Notification = request;
using (SqlDataAdapter adapter = new SqlDataAdapter(_cmd))
{
adapter.Fill(_dataToWatch, "Products");
dataGridView1.DataSource = _dataToWatch;
dataGridView1.DataMember = "Products";
StartListener();
}
}
protected override DataTable GetSchemaTable(DbCommand srcCommand)
{
DataTable table;
SqlCommand command = srcCommand as SqlCommand;
SqlNotificationRequest notification = command.Notification;
bool notificationAutoEnlist = command.NotificationAutoEnlist;
command.Notification = null;
command.NotificationAutoEnlist = false;
try
{
using (SqlDataReader reader = command.ExecuteReader(CommandBehavior.KeyInfo | CommandBehavior.SchemaOnly))
{
return(reader.GetSchemaTable());
}
}
finally
{
command.Notification = notification;
command.NotificationAutoEnlist = notificationAutoEnlist;
}
return(table);
}
