Como monitorar alterações de tabela do SQL Server usando c #?

Eu tenho mais de um aplicativo acessando o mesmo database e preciso ser notificado se um desses aplicativos alterar alguma coisa (atualização, inserção) em uma determinada tabela.

Banco de dados e aplicativos não estão no mesmo servidor.

Você pode usar a SqlDependency Class . Seu uso pretendido é principalmente para páginas ASP.NET (baixo número de notifications do cliente).

 ALTER DATABASE UrDb SET ENABLE_BROKER 

Implemente o evento OnChange para ser notificado:

 void OnChange(object sender, SqlNotificationEventArgs e) 

E no código:

 SqlCommand cmd = ... cmd.Notification = null; SqlDependency dependency = new SqlDependency(cmd); dependency.OnChange += OnChange; 

Ele usa o Service Broker (uma plataforma de comunicação baseada em mensagens) para receber mensagens do mecanismo de database.

Geralmente, você usaria o Service Broker

Isso é gatilho -> fila -> aplicativo (s)

Editar, depois de ver outras respostas:

FYI: “Notificações de consulta” é criado no Service Broker

Edit2:

Mais links

  • Notificação de events / corretor de serviços
  • Equipe do Service Broker

No interesse da integralidade, existem algumas outras soluções que, na minha opinião, são mais ortodoxas e bem estabelecidas do que as soluções que dependem das classs SqlDependency (e SqlTableDependency). O SqlDependency foi projetado para atualização de cache do servidor da Web e, portanto, não fornece o tipo de resiliência sob carga que você exigiria de um produtor de evento.

Existem quatro outras opções que não foram mencionadas aqui:

  • Acompanhamento de Mudanças
  • CDC
  • Triggers para filas
  • CLR

Acompanhamento de alterações

Fonte: https://docs.microsoft.com/en-us/sql/relational-databases/track-changes/about-change-tracking-sql-server

O rastreamento de alterações é um mecanismo de notificação leve no SQL Server. Basicamente, um número de versão em todo o database é incrementado a cada alteração em qualquer dado. O número da versão é então gravado nas tabelas de controle de alterações com uma máscara de bits, incluindo os nomes das colunas que foram alteradas. Observe que a alteração real não é persistida. A notificação contém apenas as informações que uma determinada entidade de dados mudou. Além disso, como a versão da tabela de mudanças é cumulativa, as alterações individuais não são preservadas e são substituídas por alterações mais recentes. Isso significa que, se uma entidade mudar duas vezes, o acompanhamento de alterações só saberá sobre a alteração mais recente.

Para capturar essas alterações em c #, a pesquisa deve ser usada. As tabelas de alteração podem ser consultadas e cada alteração inspecionada para ver se é de interesse. Se for de interesse, é necessário ir diretamente para os dados para recuperar o estado atual.

Change Data Capture

Fonte: https://technet.microsoft.com/pt-br/library/bb522489(v=sql.105).aspx

A captura de dados alterados (CDC) é mais poderosa, mas mais cara do que o rastreamento de alterações. A captura de dados alterados rastreará e notificará as alterações com base no monitoramento do log do database. Devido a isso, o CDC tem access aos dados reais que foram alterados e mantém um registro de todas as alterações individuais.

Da mesma forma para alterar o rastreamento, para capturar essas alterações em c #, a pesquisa deve ser usada. No entanto, no caso do CDC, as informações sondadas conterão os detalhes da alteração, portanto, não é estritamente necessário voltar aos próprios dados.

Triggers para filas

Fonte: https://code.msdn.microsoft.com/Service-Broker-Message-e81c4316

Essa técnica depende de gatilhos nas tabelas das quais as notifications são necessárias. Cada alteração triggersrá um gatilho e o gatilho gravará essas informações em uma fila do agente de serviços. A fila pode então ser conectada ao C # usando o Processador de Mensagens do Service Broker (amostra no link acima).

Ao contrário do rastreamento de alterações ou do CDC, os acionadores de filas não dependem de pesquisa e, portanto, fornecem events em tempo real.

CLR

Essa é uma técnica que já vi usada, mas não recomendaria. Qualquer solução que dependa do CLR para se comunicar externamente é um hack na melhor das hipóteses. O CLR foi projetado para facilitar a escrita de códigos de processamento de dados complexos, aproveitando o C #. Ele não foi projetado para conectar dependencies externas, como bibliotecas de mensagens. Além disso, as operações de limite CLR podem quebrar em ambientes clusterizados de maneiras imprevisíveis.

Dito isto, é bastante simples de configurar, como tudo que você precisa fazer é registrar o assembly de mensagens com o CLR e, em seguida, você pode chamar usando gatilhos ou trabalhos de SQL.

Em suma…

Sempre foi uma fonte de espanto para mim que a Microsoft tenha se recusado firmemente a abordar esse espaço problemático. O evento do database para o código deve ser um recurso interno do produto de database. Considerando que o Oracle Advanced Queuing, combinado com o evento ODP.net MessageAvailable , forneceu events confiáveis ​​de database para o C # há mais de 10 anos , isso é desastroso para o MS.

O resultado disso é que nenhuma das soluções listadas para essa pergunta é muito boa. Todos eles têm desvantagens técnicas e têm um custo significativo de configuração. Microsoft, se você estiver ouvindo, por favor, resolva este estado lastimável de coisas.

SqlDependency não assiste ao database que ele observa o SqlCommand que você especificou então se você está tentando digamos, inserir valores no database em 1 projeto e capturar esse evento em outro projeto, ele não funcionará porque o evento era do SqlCommand do 1º projeto não é o database porque quando você cria um SqlDependency você o vincula a um SqlCommand e somente quando o comando desse projeto é usado ele cria um evento Change.

Use SqlTableDependency. É um componente ac # que gera events quando um registro é alterado. Você pode encontrar outros detalhes em: https://github.com/christiandelbianco/monitor-table-change-with-sqltabledependency

É semelhante ao .NET SqlDependency, exceto que SqlTableDependency gera events contendo valores modificados / excluídos ou atualizados da tabela de database:

 string conString = "data source=.;initial catalog=myDB;integrated security=True"; using(var tableDependency = new SqlTableDependency(conString)) { tableDependency.OnChanged += TableDependency_Changed; tableDependency.Start(); Console.WriteLine("Waiting for receiving notifications..."); Console.WriteLine("Press a key to stop"); Console.ReadKey(); } ... ... void TableDependency_Changed(object sender, RecordChangedEventArgs e) { if (e.ChangeType != ChangeType.None) { var changedEntity = e.Entity; Console.WriteLine("DML operation: " + e.ChangeType); Console.WriteLine("ID: " + changedEntity.Id); Console.WriteLine("Name: " + changedEntity.Name); Console.WriteLine("Surname: " + changedEntity.Surname); } } 

Desde o SQL Server 2005, você tem a opção de usar Notificações de Consulta , que podem ser aproveitadas pelo ADO.NET, consulte http://msdn.microsoft.com/pt-br/library/t9x04ed2.aspx

Tenha cuidado ao usar a class SqlDependency – tem problemas com vazamentos de memory. No entanto, você pode usar sua própria realização com gatilhos DDL e API do SQL Service Broker ou usar um dos projetos de código aberto, por exemplo, SqlDependencyEx :

 int changesReceived = 0; using (SqlDependencyEx sqlDependency = new SqlDependencyEx( TEST_CONNECTION_STRING, TEST_DATABASE_NAME, TEST_TABLE_NAME)) { sqlDependency.TableChanged += (o, e) => changesReceived++; sqlDependency.Start(); // Make table changes. MakeTableInsertDeleteChanges(changesCount); // Wait a little bit to receive all changes. Thread.Sleep(1000); } Assert.AreEqual(changesCount, changesReceived); 

Espero que isto ajude.

parece uma arquitetura ruim por todo o caminho. você também não especificou o tipo de aplicativo que você precisa notificar (app / console / winforms / service etc etc)

No entanto, para responder à sua pergunta, existem várias maneiras de resolver isso. você poderia usar:

1) timestamps se você estava interessado em garantir que o próximo conjunto de atualizações do segundo app não esteja em conflito com as atualizações do primeiro app

2) object de dependência sql – consulte http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldependency.aspx para obter mais informações

3) um serviço de notificação por push personalizado no qual vários clientes (web / winform / service) podem se inscrever e receber notifications sobre alterações

Em suma, você precisa usar a solução mais simples e mais barata (em termos de esforços) com base na complexidade dos requisitos de notificação e com que finalidade você precisa usá-los. não tente criar um sistema de notificação excessivamente complexo se uma simples simultaneidade de dados for sua única exigência (nesse caso, optar por uma solução simples baseada em timestamp)

Outra forma muito simples de monitorar as tabelas é a versão das tabelas. O sistema está provado trabalhando em construções como a synchronization de DNS. Para fazê-lo funcionar você cria uma tabela contendo nomes de tabela e versões de tabela como decimal ou bigint. Em cada tabela que você precisa monitorar, crie um acionador na inserção, atualização e exclusão que irá incrementar a versão da tabela apropriada na tabela de version control quando executada. Se você espera que alguma das tabelas monitoradas seja alterada com frequência, é necessário provisionar a reutilização da versão. Finalmente, em seu aplicativo, toda vez que você consulta a tabela monitorada, você também consulta sua versão e a armazena. Quando você vai alterar a tabela monitorada do seu aplicativo, primeiro consulta sua versão atual e processa a alteração apenas se a versão não estiver alterada. Você pode ter o procedimento armazenado no servidor sql que funciona para você. Esta é uma solução sólida extremamente simples, mas comprovada. Ele tem um uso funcional específico (para garantir a consistência de dados) e é leve em resources (você não gera events de broker que você não iria observar), mas precisa de aplicativo para verificar ativamente as mudanças, em vez de esperar passivamente que o evento aconteça.