コード例 #1
0
ファイル: Global.asax.cs プロジェクト: nietzky/SignalR10
        public IEnumerable<JobInfo> GetData()
        {
            using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["KPIConnectionString"].ConnectionString))
            {
                connection.Open();
                using (SqlCommand command = new SqlCommand(@"SELECT [JobID],[Name],[LastExecutionDate],[Status] FROM [dbo].[JobInfo]", connection))
                {
                    // Make sure the command object does not already have
                    // a notification object associated with it.
                    command.Notification = null;

                    SqlDependency dependency = new SqlDependency(command);
                    dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);

                    if (connection.State == ConnectionState.Closed)
                        connection.Open();

                    using (var reader = command.ExecuteReader())
                        return reader.Cast<IDataRecord>()
                            .Select(x => new JobInfo()
                            {
                                JobID = x.GetInt32(0),
                                Name = x.GetString(1),
                                LastExecutionDate = x.GetDateTime(2),
                                Status = x.GetString(3)
                            }).ToList();

                }
            }
        }
コード例 #2
0
ファイル: SqlChangeMonitor.cs プロジェクト: stabbylambda/mono
		public SqlChangeMonitor (SqlDependency dependency)
		{
			if (dependency == null)
				throw new ArgumentNullException ("dependency");
			
			throw new NotImplementedException ();
		}
コード例 #3
0
        private static void UpdateGrid()
        {
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                //依赖是基于某一张表的,而且查询语句只能是简单查询语句,不能带top或*,同时必须指定所有者,即类似[dbo].[]
                using (SqlCommand command = new SqlCommand("select ID,UserID,[Message] From [dbo].[Messages] where id>0", connection))
                {
                    command.CommandType = CommandType.Text;
                    connection.Open();
                    SqlDependency dependency = new SqlDependency(command);
                    dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);

                    using (SqlDataReader sdr = command.ExecuteReader())
                    {
                        Console.WriteLine();
                        while (sdr.Read())
                        {
                            Console.WriteLine(
                                "Id:{0}\tUserId:{1}\tMessage:{2}",
                                sdr["ID"].ToString(), sdr["UserId"].ToString(),
                                sdr["Message"].ToString()
                                );
                        }
                    }

                }
            }
        }
コード例 #4
0
        /// <summary>
        /// On cache miss.
        /// </summary>
        internal protected override void OnCacheMiss(Container container, IQueryable query, string key)
        {
            // Patch model to include schema information in LinqToSql, this is a requirement for SqlDependency to work.
            LinqToSql.AddSchemaToModel(query);

            var dependency = new System.Data.SqlClient.SqlDependency();

            dependency.OnChange += (sender, args) =>
            {
                if (args.Info == SqlNotificationInfo.Invalid)
                {
                    var unsupportedQueryHandler = UnsupportedQuery;
                    if (unsupportedQueryHandler != null)
                    {
                        unsupportedQueryHandler(this, new SqlDependencyEventArgs(query));
                    }
                }
                else
                {
                    container.Delete(key);
                }

                OnChangeReceived.Set();
            };
            CallContext.SetData(CookieName, dependency.Id);
        }
コード例 #5
0
        public string[] RegisterForChanges()
        {
            string[] result = null;

            using (SqlConnection conn = new SqlConnection(mSubscriberConnectionString))
            {
                if (conn.State != ConnectionState.Open) conn.Open();

                using (SqlCommand cmd = new SqlCommand("SELECT [BilgilendirmeTipi],[Baslik],[Icerik] FROM [dbo].[Bilgilendirmeler]", conn))
                {
                    SqlDependency dependency = new SqlDependency(cmd);
                    dependency.OnChange += new OnChangeEventHandler(OnNotificationChange);

                    using (SqlDataAdapter adap = new SqlDataAdapter(cmd))
                    {
                        using (DataTable dt = new DataTable())
                        {
                            adap.Fill(dt);
                            if (dt.Rows.Count > 0)
                            {
                                result = new string[3];

                                result[0] = (string)dt.Rows[0][0];
                                result[1] = (string)dt.Rows[0][1];
                                result[2] = (string)dt.Rows[0][2];
                            }
                        }
                    }
                }
            }

            return result;
        }
コード例 #6
0
        public DataTable SomeMethod(Action<object, SqlNotificationEventArgs> handler)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                // notificationas bus iskvieciamas visuomet, kai pasikeis bent vienas duomuo, is sio SELECTO grazinamu duomenu set`o (bus atliktas UPDATE, DELETE, INSERT)
                using (SqlCommand command = new SqlCommand("SELECT StudentasID, Vardas, Pavarde, IBAN, AK FROM dbo.tblIseiviaiStudentai", connection))
                {

                    dependency = new SqlDependency();
                    command.Notification = null;

                    dependency.AddCommandDependency(command);
                    dependency.OnChange += new OnChangeEventHandler(handler);

                    if (connection.State != ConnectionState.Open)
                    {
                        connection.Open();
                    }

                    // kad veiktu notificationas butini sie conection`o nustatymai, kitaip eventas bus kvieciamas nuolatos
                    var ON_PARAMETERS = @"SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER, ARITHABORT ON";
                    var OFF_PARAMETERS = @"SET NUMERIC_ROUNDABORT OFF";
                    var cmd_ON_PARAMETERS = new SqlCommand(ON_PARAMETERS, connection);
                    var cmd_OFF_PARAMETERS = new SqlCommand(OFF_PARAMETERS, connection);
                    cmd_ON_PARAMETERS.ExecuteNonQuery();
                    cmd_OFF_PARAMETERS.ExecuteNonQuery();

                    dataTable.Clear();
                    dataTable.Load(command.ExecuteReader(CommandBehavior.CloseConnection));
                    dependency = null;
                    return dataTable;
                }
            }
        }
コード例 #7
0
ファイル: DataBase.cs プロジェクト: YourTradingSystems/4XGain
 public DataBase()
 {
     ConnectToDB();
     sqlDependency = new SqlDependency();
     //sqlDependency.AddCommandDependency(new SqlCommand("INSERT", conn));
     sqlDependency.OnChange += new OnChangeEventHandler(onChange);
 }
コード例 #8
0
        public void RegisterDiscussMessages()
        {
            using (var connection = new SqlConnection(_connString))
            {
                connection.Open();
                using (var command = new SqlCommand(@"SELECT [Id]
                                                          ,[Username]
                                                          ,[Message]
                                                          ,[FilePath]
                                                          ,[Status]
                                                          ,[CreatedDate]
                                                      FROM [dbo].[Discuss] ORDER BY [dbo].[Discuss].CreatedDate desc", connection))
                {
                    command.Notification = null;

                    var dependency = new SqlDependency(command);
                    dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);

                    if (connection.State == ConnectionState.Closed)
                        connection.Open();

                    var reader = command.ExecuteReader();

                }

            }
           
        }
コード例 #9
0
        public IEnumerable<Messages> GetAllMessages()
        {
            var messages = new List<Messages>();
            using (var connection = new SqlConnection(_connString))
            {
                connection.Open();
                using (var command = new SqlCommand(@"SELECT [MessageID], [Message], [EmptyMessage], [Date] FROM [dbo].[Messages]", connection))
                {
                    command.Notification = null;

                    var dependency = new SqlDependency(command);
                    dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);

                    if (connection.State == ConnectionState.Closed)
                        connection.Open();

                    var reader = command.ExecuteReader();

                    while (reader.Read())
                    {
                        messages.Add(item: new Messages { MessageID = (int)reader["MessageID"], Message = (string)reader["Message"], EmptyMessage =  reader["EmptyMessage"] != DBNull.Value ? (string) reader["EmptyMessage"] : "", MessageDate = Convert.ToDateTime(reader["Date"]) });
                    }
                }

            }
            return messages;
        }
コード例 #10
0
        public IEnumerable<Info> GetData()
        {
            using (
                var connection =
                    new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString))
            {
                connection.Open();
                using (
                    var command = new SqlCommand(@"SELECT [Id],[Name],[Family],[Status] FROM [dbo].[Info]", connection))
                {
                    command.Notification = null;

                    var dependency = new SqlDependency(command);
                    dependency.OnChange += dependency_OnChange;

                    if (connection.State == ConnectionState.Closed)
                        connection.Open();

                    using (SqlDataReader reader = command.ExecuteReader())
                        return reader.Cast<IDataRecord>()
                            .Select(x => new Info
                            {
                                Id = x.GetInt32(0),
                                Name = x.GetString(1),
                                Family = x.GetString(2),
                                Status = x.GetString(3)
                            }).ToList();
                }
            }
        }
コード例 #11
0
        public static bool IsInMaintenanceMode()
        {
            bool inMaintenanceMode;
            string connStr = "Data Source=KIM-MSI\\KIMSSQLSERVER;Initial Catalog=MVWDataBase;User ID=sa;Password=mis123;MultipleActiveResultSets=True";
            if (MemoryCache.Default["MaintenanceMode"] == null)
            {
                Console.WriteLine("Hitting the database...");
                CacheItemPolicy policy = new CacheItemPolicy();
                SqlDependency.Start(connStr);
                using (SqlConnection conn = new SqlConnection(connStr))
                {
                    using (SqlCommand command = new SqlCommand("Select MaintenanceMode From dbo.MaintenanceMode", conn))
                    {
                        command.Notification = null;
                        SqlDependency dep = new SqlDependency();
                        dep.AddCommandDependency(command);
                        conn.Open();
                        inMaintenanceMode = (bool)command.ExecuteScalar();
                        SqlChangeMonitor monitor = new SqlChangeMonitor(dep);
                        policy.ChangeMonitors.Add(monitor);
                        dep.OnChange += Dep_OnChange;
                    }
                }

                MemoryCache.Default.Add("MaintenanceMode", inMaintenanceMode, policy);
            }
            else
            {
                inMaintenanceMode = (bool)MemoryCache.Default.Get("MaintenanceMode");
            }

            return inMaintenanceMode;
        }
コード例 #12
0
        public List<DiscussMessageModel> RegisterDiscussMessages()
        {
            var messageChat = new List<DiscussMessageModel>();
            using (var connection = new SqlConnection(_connString))
            {
                connection.Open();
                using (var command = new SqlCommand(@"SELECT *
                                                      FROM [dbo].[Comments]", connection))
                {
                    command.Notification = null;
                    var dependency = new SqlDependency(command);
                    dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
                    if (connection.State == ConnectionState.Closed)
                        connection.Open();

                    var reader = command.ExecuteReader();
                    while (reader.Read())
                    {
                        messageChat.Add(item: new DiscussMessageModel
                        {
                            Id = reader["Id"].ToString(),
                            Username = reader["Username"].ToString(),
                            Message = reader["Message"].ToString(),
                            FilePath = reader["FilePath"].ToString(),
                            Avatar = "dist/img/avatar.png",
                            Status = reader["Status"].ToString(),
                            CreatedDate = DateTime.Parse(reader["CreatedDate"].ToString())
                        });
                    }
                }

            }
            return messageChat;
        }
コード例 #13
0
ファイル: NotificatiobHub.cs プロジェクト: dotnext24/SignalIR
        public IEnumerable<MessageLog> GetAllMessagesLog()
        {
            string conStr = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
               SqlConnection connection = new SqlConnection(conStr);

               string query = "SELECT Message,EventID,LL.Name as LogLevelID,OC.Name as OperationCodeID,ML.ServerName,ML.ComponentName,ML.SubComponentName FROM [dbo].[MessageLog] ML inner join [dbo].[LogLevel] LL on ML.LogLevelID = LL.ID inner join [dbo].[OperationCode] OC on ML.OperationCodeID = OC.ID";
               SqlDependency.Start(conStr);
               SqlCommand command = new SqlCommand(query, connection);
               SqlDependency dependency = new SqlDependency(command);

               //If Something will change in database and it will call dependency_OnChange method.
               dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
               connection.Open();
               SqlDataAdapter da = new SqlDataAdapter(command);
               DataTable dt = new DataTable();
               da.Fill(dt);

               List<MessageLog> messageList = new List<MessageLog>();
               for (int i = 0; i < dt.Rows.Count; i++)
               {
               MessageLog ml = new MessageLog();
               ml.Name = dt.Rows[i]["Message"].ToString();
               ml.EventID = Convert.ToInt32(dt.Rows[i]["EventID"].ToString());
               ml.LogLevelName = dt.Rows[i]["LogLevelID"].ToString();
               ml.OperationCodeName = dt.Rows[i]["OperationCodeID"].ToString();
               ml.ServerName = dt.Rows[i]["ServerName"].ToString();
               ml.ComponentName = dt.Rows[i]["ComponentName"].ToString();
               ml.SubComponentName = dt.Rows[i]["SubComponentName"].ToString();
               messageList.Add(ml);
               }
               return messageList;
        }
コード例 #14
0
ファイル: Message.cs プロジェクト: vmeln/signalr-scaling
        public IEnumerable<Message> GetData()
        {
            using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString))
            {
                connection.Open();
                using (SqlCommand command = new SqlCommand("SELECT [ID], [Date], [Message] FROM [dbo].[Messages]", connection))
                {
                    command.Notification = null;

                    SqlDependency dependency = new SqlDependency(command);
                    dependency.OnChange += DependencyOnChange;

                    if (connection.State == ConnectionState.Closed)
                        connection.Open();

                    using (var reader = command.ExecuteReader())
                        return reader.Cast<IDataRecord>()
                            .Select(x => new Message()
                            {
                                ID = x.GetInt32(0),
                                Date = x.GetDateTime(1),
                                Text = x.GetString(2)
                            }).ToList();
                }
            }
        }
コード例 #15
0
ファイル: SqlDependency.cs プロジェクト: consumentor/Server
 private void OnDependencyChanged(object sender, SqlNotificationEventArgs e)
 {
     if (_eventHandler != null)
         _eventHandler.Invoke(this, e);
     _dependency = null;
     Start();
 }
コード例 #16
0
        public IEnumerable<JobModel> GetData(short minLevel)
        {

            using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["PubsEntitiesSimpleConnectionString"].ConnectionString))
            {
                connection.Open();
                using (var command = new SqlCommand(string.Format(@"SELECT [job_id],[job_desc]
                FROM [dbo].[jobs]"), connection))
                //WHERE [min_lvl] > {0}", minLevel), connection))
                {
                    // Make sure the command object does not already have
                    // a notification object associated with it.
                    command.Notification = null;

                    var dependency = new SqlDependency(command);
                    dependency.OnChange += dependency_OnChange;

                    if (connection.State == ConnectionState.Closed)
                    {
                        connection.Open();
                    }

                    using (var reader = command.ExecuteReader())
                    {
                        return reader.Cast<IDataRecord>()
                                     .Select(x => new JobModel
                                         {
                                             job_id = x.GetInt16(0),
                                             job_desc = x.GetString(1)
                                         }).ToList();
                    }
                }
            }
        }
		public ChangeMonitor Enlist()
		{
			using (var connection = new SqlConnection(connectionString))
			{
				using (var exeCommand = new System.Data.SqlClient.SqlCommand(command, connection))
				{
					//is the command a sproc
					if (isStoredProcedure)
					{
						exeCommand.CommandType = CommandType.StoredProcedure;
					}

                    if (commandTimeout.HasValue)
				        exeCommand.CommandTimeout = this.commandTimeout.Value;

					var dependency = new SqlDependency(exeCommand);
                    var monitor = new SqlChangeMonitor(dependency);

					connection.Open();
					exeCommand.ExecuteNonQuery();

					return monitor;

				}
			}
		}
コード例 #18
0
        /// <summary>
        /// On cache miss.
        /// </summary>
        protected internal override void OnCacheMiss(Container container, IQueryable query, string key)
        {
            // Patch model to include schema information in LinqToSql, this is a requirement for SqlDependency to work.
            LinqToSql.AddSchemaToModel(query);

            var dependency = new System.Data.SqlClient.SqlDependency();
            dependency.OnChange += (sender, args) =>
            {
                if (args.Info == SqlNotificationInfo.Invalid)
                {
                    var unsupportedQueryHandler = UnsupportedQuery;
                    if (unsupportedQueryHandler != null)
                    {
                        unsupportedQueryHandler(this, new SqlDependencyEventArgs(query));
                    }
                }
                else
                {
                    container.Delete(key);
                }

                OnChangeReceived.Set();
            };
            CallContext.SetData(CookieName, dependency.Id);
        }
コード例 #19
0
ファイル: Repository.cs プロジェクト: Rookian/SignalR
        public IEnumerable<Person> GetPersons()
        {
            using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString))
            {
                connection.Open();
                using (var command = new SqlCommand(@"SELECT [ID],[FirstName],[LastName] FROM [dbo].[Person]", connection))
                {
                    command.Notification = null;

                    var dependency = new SqlDependency(command);
                    dependency.OnChange += (x, y) => _hubContext.Clients.All.displayStatus(GetPersons());

                    using (var reader = command.ExecuteReader())
                    {
                        return reader.Cast<IDataRecord>()
                        .Select(x => new Person
                        {
                            ID = x.GetInt64(0),
                            FirstName = x.GetString(1),
                            LastName = x.GetString(2),
                        }).ToList();
                    }
                }
            }
        }
コード例 #20
0
 public SqlDependencyCacheWatcher(string tableName)
 {
     this.connectionString = connectionString;
     this.sqlQueue = sqlQueue;
     this.listenerQuery = listenerQuery;
     this.dependency = null;
 }
コード例 #21
0
ファイル: LineTestCache.cs プロジェクト: borealwinter/simpl
        private static void AddToCache(string key, object cacheItem, SqlDependency dependency)
        {
            var policy = new CacheItemPolicy();

            policy.ChangeMonitors.Add(new SqlChangeMonitor(dependency));

            Cache.Add(new CacheItem(key, cacheItem), policy);
        }
コード例 #22
0
 private void UnSubscribe()
 {
     if (dependency != null)
     {
         dependency.OnChange -= OnDependencyChange;
         dependency = null;
     }
 }
コード例 #23
0
ファイル: SqlDependency.cs プロジェクト: consumentor/Server
 private void OnDependencyChanged(object sender, SqlNotificationEventArgs e)
 {
     if (_eventHandler != null)
     {
         _eventHandler.Invoke(this, e);
     }
     _dependency = null;
     Start();
 }
コード例 #24
0
ファイル: SqlDependency.cs プロジェクト: consumentor/Server
 private void Start()
 {
     _command = new SqlCommand(_command.CommandText, new SqlConnection(_connectionString));
     OpenConnectionIfClosed(_command);
     AdoNetSqlDependency.Start(_connectionString);
     _dependency           = new AdoNetSqlDependency(_command);
     _depReader            = _command.ExecuteReader();
     _dependency.OnChange += OnDependencyChanged;
 }
コード例 #25
0
ファイル: SqlDependency.cs プロジェクト: consumentor/Server
 private void Start()
 {
     _command = new SqlCommand(_command.CommandText, new SqlConnection(_connectionString));
     OpenConnectionIfClosed(_command);
     AdoNetSqlDependency.Start(_connectionString);
     _dependency = new AdoNetSqlDependency(_command);
     _depReader = _command.ExecuteReader();
     _dependency.OnChange += OnDependencyChanged;
 }
コード例 #26
0
        public static void AddSqlDependency(this IDbCommand command, Action<SqlNotificationEventArgs> callback)
        {
            var sqlCommand = command as SqlCommand;
            if (sqlCommand == null)
            {
                throw new NotSupportedException();
            }

            var dependency = new SqlDependency(sqlCommand, null, (int)_dependencyTimeout.TotalSeconds);
            dependency.OnChange += (o, e) => callback(e);
        }
コード例 #27
0
 /// <summary>
 /// 监控数据库中表的数据变化
 /// </summary>
 /// <param name="monitorSql">监控表查询语句</param>
 /// <param name="method">添加代理事件的代理类并委托给一个方法,例:new OnChangeEventHandler(dependency_OnChange);
 /// private static void dependency_OnChange(object sender, SqlNotificationEventArgs e)</param>
 public static void UpdateGrid(String monitorSql,OnChangeEventHandler method)
 {
     //依赖是基于某一张表的,而且查询语句只能是简单查询语句,不能带top或*,同时必须指定所有者,即类似[dbo].[]
     using (SqlCommand command = new SqlCommand(monitorSql, Connection.Connection.getSqlConnection()))
     {
         command.CommandType = CommandType.Text;
         SqlDependency dependency = new SqlDependency(command);
         dependency.OnChange += method;
         SqlDataReader sdr = command.ExecuteReader();
         sdr.Close();
     }
 }
コード例 #28
0
ファイル: LineTestCache.cs プロジェクト: borealwinter/simpl
        public IEnumerable<test_results> GetLineTestResults(SqlDependency dependency)
        {
            if (Cache.Contains(LINETEST_CACHE_KEY))
            {
                return Cache[LINETEST_CACHE_KEY] as IEnumerable<test_results>;
            }

            using (var db = LineTestContextFactory.CreateContext())
            {
                AddToCache(LINETEST_CACHE_KEY, db.test_results.ToList(), dependency);
            }

            return Cache[LINETEST_CACHE_KEY] as IEnumerable<test_results>;
        }
コード例 #29
0
 private void RegisterForNotifications()
 {
     using (var connection = new SqlConnection(_connString))
     {
         using (SqlCommand command = new SqlCommand(_selectQuery, connection))
         {
             command.Notification = null;
             SqlDependency dependency = new SqlDependency(command);
             dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
             if (connection.State == ConnectionState.Closed)
                 connection.Open();
             var reader = command.ExecuteReader();
         }
     }
 }
コード例 #30
0
        static void ReadMessages()
        {
            DatabaseHelper databaseHelper = new DatabaseHelper();
            SqlCommand     cmd            = databaseHelper.CreateCommand(SQLQueries.GET_MESSAGES);

            System.Data.SqlClient.SqlDependency sqlDependency = databaseHelper.SqlDependency(cmd);
            databaseHelper.DependencyStart();
            sqlDependency.OnChange += SqlDep_OnChange;
            SqlDataReader dr = databaseHelper.ExecuteReader(cmd);

            Console.WriteLine("Time:" + DateTime.Now);
            while (dr.Read())
            {
                Console.WriteLine(dr["Message"]);
            }
        }
コード例 #31
0
        private void RegisterSqlDependency()
        {
            if (connection == null || command == null)
            {
                throw new ArgumentException("command and connection cannot be null");
            }

            // Make sure the command object does not already have
            // a notification object associated with it.
            command.Notification = null;
            // Create and bind the SqlDependency object
            // to the command object.
            dependency = new System.Data.SqlClient.SqlDependency(command);
            Console.WriteLine("Id of sqldependency:{0}", dependency.Id);
            RegisterSqlCommand();
        }
コード例 #32
0
ファイル: SyncObject.cs プロジェクト: patcher1983/vcsync
        public void ListenTable(string conStr, SyncMonitor monitor)
        {
            using (SqlConnection connection = new SqlConnection(conStr))
            {
                //依赖是基于某一张表的,而且查询语句只能是简单查询语句,不能带top或*,同时必须指定所有者,即类似[dbo].[]
                using (SqlCommand command = new SqlCommand(monitor.listenSql, connection))
                {
                    command.CommandType = CommandType.Text;
                    connection.Open();
                    SqlDependency dependency = new SqlDependency(command);
                    dependency.OnChange += monitor.callBack;

                    SqlDataReader sdr = command.ExecuteReader();
                }
            }
        }
コード例 #33
0
		static private void AutoRefresh(IEnumerable collection)
		{
			var oldCookie = CallContext.GetData(sqlDependencyCookie);
			try
			{
				var dependency = new SqlDependency();
				collections.Add(dependency.Id, collection);
				CallContext.SetData(sqlDependencyCookie, dependency.Id);
				dependency.OnChange += dependency_OnChange;
				ctx.Refresh(refreshMode, collection);
			}
			finally
			{
				CallContext.SetData(sqlDependencyCookie, oldCookie);
			}
		}
コード例 #34
0
ファイル: DbNotifier.cs プロジェクト: yerald231ger/EmmtyMr
 private void ResgisterNotification()
 {
     using (_connection = new SqlConnection(_connectionsString))
     {
         using (_command = new SqlCommand(_query, _connection))
         {
             _command.Notification = null;
             _dependencyObj = new SqlDependency(_command);
             _dependencyObj.OnChange += dependency_OnChange;
             if (_connection.State == ConnectionState.Closed)
                 _connection.Open();
             using (_command.ExecuteReader())
             {
             }
         }
     }
 }
コード例 #35
0
        private void ListenForChanges()
        {
            //Remove existing dependency, if necessary
            UnSubscribe();

            var connection = new SqlConnection(connectionString);
            connection.Open();

            var command = new SqlCommand(listenerQuery, connection);

            dependency = new SqlDependency(command);
            dependency.OnChange += new OnChangeEventHandler(OnDependencyChange);

            SqlDependency.Start(connectionString);
            command.ExecuteReader();
            connection.Close();
        }
コード例 #36
0
ファイル: SqlDependency.cs プロジェクト: consumentor/Server
 void ISqlDependency.Terminate(string connectionString)
 {
     try
     {
         _depReader.Close();
     }
     finally
     {
         try
         {
             AdoNetSqlDependency.Stop(connectionString);
         }
         finally
         {
             _dependency = null;
         }
     }
 }
コード例 #37
0
        private void RegisterSqlDependency()
        {
            if (command == null || connection == null)
            {
                throw new ArgumentException("command and connection cannot be null");
            }

            // Make sure the command object does not already have
            // a notification object associated with it.
            command.Notification = null;

            // Create and bind the SqlDependency object to the command object.
            dependency           = new System.Data.SqlClient.SqlDependency(command);
            dependency.OnChange += new OnChangeEventHandler(DependencyOnChange);

            // After register SqlDependency, the SqlCommand must be executed, or we can't
            // get the notification.
            RegisterSqlCommand();
        }
コード例 #38
0
        protected void Dispose(Boolean disposed)
        {
            if (disposed)
            {
                if (command != null)
                {
                    command.Dispose();
                    command = null;
                }

                if (connection != null)
                {
                    connection.Dispose();
                    command = null;
                }

                iquery     = null;
                dependency = null;
            }
        }
コード例 #39
0
        static void Main(string[] args)
        {
            string        _SqlConn = @"Data Source=rutwik-c\sqlexpress;Initial Catalog=Student.Data.StudentContext;Integrated Security=True";
            SqlConnection _Conn    = new SqlConnection(_SqlConn);
            SqlCommand    _Cmd     = new SqlCommand("SELECT RollNo, Name, Marks, Branch, Year FROM [dbo].[Studs]", _Conn);

            System.Data.SqlClient.SqlDependency listener = new System.Data.SqlClient.SqlDependency(_Cmd);

            listener.OnChange += Listener_OnChange;

            System.Data.SqlClient.SqlDependency.Start(_SqlConn);

            Console.WriteLine("Listening..");
            _Conn.Open();

            _Cmd.ExecuteReader();

            System.Data.SqlClient.SqlDependency.Stop(_SqlConn);
            Console.WriteLine("Stopped!");
            _Conn.Close();
        }
コード例 #40
0
        protected void Dispose(Boolean disposed)
        {
            if (disposed)
            {
                if (command != null)
                {
                    command.Dispose();
                    command = null;
                }

                if (connection != null)
                {
                    connection.Dispose();
                    connection = null;
                }

                OnChanged            = null;
                iquery               = null;
                dependency.OnChange -= DependencyOnChange;
                dependency           = null;
            }
        }
コード例 #41
0
        // Methods for Timer maintenance and firing.

        internal void StartTimer(SqlDependency dep)
        {
            // If this dependency expires sooner than the current next timeout, change
            // the timeout and enable timer callback as needed.  Note that we change _nextTimeout
            // only inside the hashtable syncroot.
            lock (_instanceLock)
            {
                // Enable the timer if needed (disable when empty, enable on the first addition).
                if (!_sqlDependencyTimeOutTimerStarted)
                {
                    _timeoutTimer.Change(15000 /* 15 secs */, 15000 /* 15 secs */);

                    // Save this as the earlier timeout to come.
                    _nextTimeout = dep.ExpirationTime;
                    _sqlDependencyTimeOutTimerStarted = true;
                }
                else if (_nextTimeout > dep.ExpirationTime)
                {
                    // Save this as the earlier timeout to come.
                    _nextTimeout = dep.ExpirationTime;
                }
            }
        }
コード例 #42
0
        // This method is called by SqlCommand to enable ASP.Net scenarios - map from ID to Dependency.
        internal SqlDependency LookupDependencyEntry(string id)
        {
            if (null == id)
            {
                throw ADP.ArgumentNull(nameof(id));
            }
            if (string.IsNullOrEmpty(id))
            {
                throw SQL.SqlDependencyIdMismatch();
            }

            SqlDependency entry = null;

            lock (_instanceLock)
            {
                if (_dependencyIdToDependencyHash.ContainsKey(id))
                {
                    entry = _dependencyIdToDependencyHash[id];
                }
            }

            return(entry);
        }
コード例 #43
0
ファイル: SqlDependencyUtils.cs プロジェクト: pgovind/runtime
        // Remove from commandToDependenciesHash all references to the passed dependency.
        private void RemoveDependencyFromCommandToDependenciesHash(SqlDependency dependency)
        {
            lock (_instanceLock)
            {
                List <string> notificationIdsToRemove = new List <string>();
                List <string> commandHashesToRemove   = new List <string>();

                foreach (KeyValuePair <string, DependencyList> entry in _notificationIdToDependenciesHash)
                {
                    DependencyList dependencies = entry.Value;
                    if (dependencies.Remove(dependency))
                    {
                        if (dependencies.Count == 0)
                        {
                            // this dependency was the last associated with this notification ID, remove the entry
                            // note: cannot do it inside foreach over dictionary
                            notificationIdsToRemove.Add(entry.Key);
                            commandHashesToRemove.Add(entry.Value.CommandHash);
                        }
                    }

                    // same SqlDependency can be associated with more than one command, so we have to continue till the end...
                }

                Debug.Assert(commandHashesToRemove.Count == notificationIdsToRemove.Count, "maps should be kept in sync");
                for (int i = 0; i < notificationIdsToRemove.Count; i++)
                {
                    // cleanup the entry outside of foreach
                    _notificationIdToDependenciesHash.Remove(notificationIdsToRemove[i]);
                    // Cleanup the map between the command hash and associated notification ID
                    _commandHashToNotificationId.Remove(commandHashesToRemove[i]);
                }

                Debug.Assert(_notificationIdToDependenciesHash.Count == _commandHashToNotificationId.Count, "always keep these maps in sync!");
            }
        }
コード例 #44
0
        internal SqlDependency LookupDependencyEntry(string id)
        {
            SqlDependency dependency2;
            IntPtr        ptr;

            Bid.NotificationsScopeEnter(out ptr, "<sc.SqlDependencyPerAppDomainDispatcher.LookupDependencyEntry|DEP> %d#, Key: '%ls'", this.ObjectID, id);
            try
            {
                if (id == null)
                {
                    throw ADP.ArgumentNull("id");
                }
                if (ADP.IsEmpty(id))
                {
                    throw SQL.SqlDependencyIdMismatch();
                }
                SqlDependency dependency = null;
                lock (this)
                {
                    if (this._dependencyIdToDependencyHash.ContainsKey(id))
                    {
                        dependency = this._dependencyIdToDependencyHash[id];
                    }
                    else
                    {
                        Bid.NotificationsTrace("<sc.SqlDependencyPerAppDomainDispatcher.LookupDependencyEntry|DEP|ERR> ERROR - dependency ID mismatch - not throwing.\n");
                    }
                }
                dependency2 = dependency;
            }
            finally
            {
                Bid.ScopeLeave(ref ptr);
            }
            return(dependency2);
        }
コード例 #45
0
 public System.Data.SqlClient.SqlDependency SqlDependency(SqlCommand command)
 {
     System.Data.SqlClient.SqlDependency sqlDependency = new System.Data.SqlClient.SqlDependency(command);
     return(sqlDependency);
 }
コード例 #46
0
ファイル: SqlDependencyUtils.cs プロジェクト: pgovind/runtime
        private static void TimeoutTimerCallback(object state)
        {
            SqlDependency[] dependencies;

            // Only take the lock for checking whether there is work to do
            // if we do have work, we'll copy the hashtable and scan it after releasing
            // the lock.
            lock (SingletonInstance._instanceLock)
            {
                if (0 == SingletonInstance._dependencyIdToDependencyHash.Count)
                {
                    // Nothing to check.
                    return;
                }
                if (SingletonInstance._nextTimeout > DateTime.UtcNow)
                {
                    // No dependency timed-out yet.
                    return;
                }

                // If at least one dependency timed-out do a scan of the table.
                // NOTE: we could keep a shadow table sorted by expiration time, but
                // given the number of typical simultaneously alive dependencies it's
                // probably not worth the optimization.
                dependencies = new SqlDependency[SingletonInstance._dependencyIdToDependencyHash.Count];
                SingletonInstance._dependencyIdToDependencyHash.Values.CopyTo(dependencies, 0);
            }

            // Scan the active dependencies if needed.
            DateTime now            = DateTime.UtcNow;
            DateTime newNextTimeout = DateTime.MaxValue;

            for (int i = 0; i < dependencies.Length; i++)
            {
                // If expired fire the change notification.
                if (dependencies[i].ExpirationTime <= now)
                {
                    try
                    {
                        // This invokes user-code which may throw exceptions.
                        // NOTE: this is intentionally outside of the lock, we don't want
                        // to invoke user-code while holding an internal lock.
                        dependencies[i].Invalidate(SqlNotificationType.Change, SqlNotificationInfo.Error, SqlNotificationSource.Timeout);
                    }
                    catch (Exception e)
                    {
                        if (!ADP.IsCatchableExceptionType(e))
                        {
                            throw;
                        }

                        // This is an exception in user code, and we're in a thread-pool thread
                        // without user's code up in the stack, no much we can do other than
                        // eating the exception.
                        ADP.TraceExceptionWithoutRethrow(e);
                    }
                }
                else
                {
                    if (dependencies[i].ExpirationTime < newNextTimeout)
                    {
                        newNextTimeout = dependencies[i].ExpirationTime; // Track the next earlier timeout.
                    }
                    dependencies[i] = null;                              // Null means "don't remove it from the hashtable" in the loop below.
                }
            }

            // Remove timed-out dependencies from the hashtable.
            lock (SingletonInstance._instanceLock)
            {
                for (int i = 0; i < dependencies.Length; i++)
                {
                    if (null != dependencies[i])
                    {
                        SingletonInstance._dependencyIdToDependencyHash.Remove(dependencies[i].Id);
                    }
                }
                if (newNextTimeout < SingletonInstance._nextTimeout)
                {
                    SingletonInstance._nextTimeout = newNextTimeout; // We're inside the lock so ok to update.
                }
            }
        }
コード例 #47
0
        // This method is called upon Execute of a command associated with a SqlDependency object.
        internal string AddCommandEntry(string commandHash, SqlDependency dep)
        {
            IntPtr hscp;
            string notificationId = string.Empty;

            Bid.NotificationsScopeEnter(out hscp, "<sc.SqlDependencyPerAppDomainDispatcher.AddCommandEntry|DEP> %d#, commandHash: '%ls', SqlDependency: %d#", ObjectID, commandHash, dep.ObjectID);
            try {
                lock (this) {
                    if (!_dependencyIdToDependencyHash.ContainsKey(dep.Id))   // Determine if depId->dep hashtable contains dependency.  If not, it's been invalidated.
                    {
                        Bid.NotificationsTrace("<sc.SqlDependencyPerAppDomainDispatcher.AddCommandEntry|DEP> Dependency not present in depId->dep hash, must have been invalidated.\n");
                    }
                    else
                    {
                        // check if we already have notification associated with given command hash
                        if (_commandHashToNotificationId.TryGetValue(commandHash, out notificationId))
                        {
                            // we have one or more SqlDependency instances with same command hash

                            DependencyList dependencyList = null;
                            if (!_notificationIdToDependenciesHash.TryGetValue(notificationId, out dependencyList))
                            {
                                // this should not happen since _commandHashToNotificationId and _notificationIdToDependenciesHash are always
                                // updated together
                                Debug.Assert(false, "_commandHashToNotificationId has entries that were removed from _notificationIdToDependenciesHash. Remember to keep them in [....]");
                                throw ADP.InternalError(ADP.InternalErrorCode.SqlDependencyCommandHashIsNotAssociatedWithNotification);
                            }

                            // join the new dependency to the list
                            if (!dependencyList.Contains(dep))
                            {
                                Bid.NotificationsTrace("<sc.SqlDependencyPerAppDomainDispatcher.AddCommandEntry|DEP> Dependency not present for commandHash, adding.\n");
                                dependencyList.Add(dep);
                            }
                            else
                            {
                                Bid.NotificationsTrace("<sc.SqlDependencyPerAppDomainDispatcher.AddCommandEntry|DEP> Dependency already present for commandHash.\n");
                            }
                        }
                        else
                        {
                            // we did not find notification ID with the same app domain and command hash, create a new one
                            // use unique guid to avoid duplicate IDs
                            // prepend app domain ID to the key - SqlConnectionContainer::ProcessNotificationResults (SqlDependencyListener.cs)
                            // uses this app domain ID to route the message back to the app domain in which this SqlDependency was created
                            notificationId = string.Format(System.Globalization.CultureInfo.InvariantCulture,
                                                           "{0};{1}",
                                                           SqlDependency.AppDomainKey, // must be first
                                                           Guid.NewGuid().ToString("D", System.Globalization.CultureInfo.InvariantCulture)
                                                           );

                            Bid.NotificationsTrace("<sc.SqlDependencyPerAppDomainDispatcher.AddCommandEntry|DEP> Creating new Dependencies list for commandHash.\n");
                            DependencyList dependencyList = new DependencyList(commandHash);
                            dependencyList.Add(dep);

                            // map command hash to notification we just created to reuse it for the next client
                            // do it inside finally block to avoid ThreadAbort exception interrupt this operation
                            try {}
                            finally {
                                _commandHashToNotificationId.Add(commandHash, notificationId);
                                _notificationIdToDependenciesHash.Add(notificationId, dependencyList);
                            }
                        }


                        Debug.Assert(_notificationIdToDependenciesHash.Count == _commandHashToNotificationId.Count, "always keep these maps in [....]!");
                    }
                }
            }
            finally {
                Bid.ScopeLeave(ref hscp);
            }

            return(notificationId);
        }
コード例 #48
0
 internal EventContextPair(OnChangeEventHandler eventHandler, SqlDependency dependency)
 {
     this._eventHandler = eventHandler;
     this._context      = ExecutionContext.Capture();
     this._dependency   = dependency;
 }
コード例 #49
0
        private static void TimeoutTimerCallback(object state)
        {
            IntPtr ptr;

            Bid.NotificationsScopeEnter(out ptr, "<sc.SqlDependencyPerAppDomainDispatcher.TimeoutTimerCallback|DEP> AppDomainKey: '%ls'", SqlDependency.AppDomainKey);
            try
            {
                SqlDependency[] dependencyArray;
                lock (SingletonInstance)
                {
                    if (SingletonInstance._dependencyIdToDependencyHash.Count == 0)
                    {
                        Bid.NotificationsTrace("<sc.SqlDependencyPerAppDomainDispatcher.TimeoutTimerCallback|DEP> No dependencies, exiting.\n");
                        return;
                    }
                    if (SingletonInstance._nextTimeout > DateTime.UtcNow)
                    {
                        Bid.NotificationsTrace("<sc.SqlDependencyPerAppDomainDispatcher.TimeoutTimerCallback|DEP> No timeouts expired, exiting.\n");
                        return;
                    }
                    dependencyArray = new SqlDependency[SingletonInstance._dependencyIdToDependencyHash.Count];
                    SingletonInstance._dependencyIdToDependencyHash.Values.CopyTo(dependencyArray, 0);
                }
                DateTime utcNow   = DateTime.UtcNow;
                DateTime maxValue = DateTime.MaxValue;
                for (int i = 0; i < dependencyArray.Length; i++)
                {
                    if (dependencyArray[i].ExpirationTime <= utcNow)
                    {
                        try
                        {
                            dependencyArray[i].Invalidate(SqlNotificationType.Change, SqlNotificationInfo.Error, SqlNotificationSource.Timeout);
                        }
                        catch (Exception exception)
                        {
                            if (!ADP.IsCatchableExceptionType(exception))
                            {
                                throw;
                            }
                            ADP.TraceExceptionWithoutRethrow(exception);
                        }
                    }
                    else
                    {
                        if (dependencyArray[i].ExpirationTime < maxValue)
                        {
                            maxValue = dependencyArray[i].ExpirationTime;
                        }
                        dependencyArray[i] = null;
                    }
                }
                lock (SingletonInstance)
                {
                    for (int j = 0; j < dependencyArray.Length; j++)
                    {
                        if (dependencyArray[j] != null)
                        {
                            SingletonInstance._dependencyIdToDependencyHash.Remove(dependencyArray[j].Id);
                        }
                    }
                    if (maxValue < SingletonInstance._nextTimeout)
                    {
                        SingletonInstance._nextTimeout = maxValue;
                    }
                }
            }
            finally
            {
                Bid.ScopeLeave(ref ptr);
            }
        }