Exemple #1
0
        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();

                }
            }
        }
		public SqlChangeMonitor (SqlDependency dependency)
		{
			if (dependency == null)
				throw new ArgumentNullException ("dependency");
			
			throw new NotImplementedException ();
		}
        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()
                                );
                        }
                    }

                }
            }
        }
        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;
        }
		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;

				}
			}
		}
 public DataBase()
 {
     ConnectToDB();
     sqlDependency = new SqlDependency();
     //sqlDependency.AddCommandDependency(new SqlCommand("INSERT", conn));
     sqlDependency.OnChange += new OnChangeEventHandler(onChange);
 }
Exemple #7
0
        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();
                    }
                }
            }
        }
        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;
        }
        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;
        }
        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;
        }
Exemple #11
0
        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();
                }
            }
        }
Exemple #12
0
 private void OnDependencyChanged(object sender, SqlNotificationEventArgs e)
 {
     if (_eventHandler != null)
         _eventHandler.Invoke(this, e);
     _dependency = null;
     Start();
 }
        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();

                }

            }
           
        }
        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;
                }
            }
        }
 public SqlDependencyCacheWatcher(string tableName)
 {
     this.connectionString = connectionString;
     this.sqlQueue = sqlQueue;
     this.listenerQuery = listenerQuery;
     this.dependency = null;
 }
        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;
        }
        /// <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);
        }
        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();
                }
            }
        }
        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();
                    }
                }
            }
        }
Exemple #20
0
        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);
        }
 private void UnSubscribe()
 {
     if (dependency != null)
     {
         dependency.OnChange -= OnDependencyChange;
         dependency = null;
     }
 }
Exemple #22
0
 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;
 }
        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);
        }
 /// <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();
     }
 }
Exemple #25
0
        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>;
        }
 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();
         }
     }
 }
Exemple #27
0
        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();
                }
            }
        }
		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);
			}
		}
        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();
        }
Exemple #30
0
 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())
             {
             }
         }
     }
 }