是否可以在数据库更改/更新上运行控制台应用程序？

I have a database A which is connected to my website(ASP.NET MVC). Whenever there is a change/update in database A through the website, I want to run a console app to grab updated data from database A and pull it down to database B.

我有一个连接到我的网站(ASP.NET MVC)的数据库A.每当通过网站在数据库A中进行更改/更新时,我想运行一个控制台应用程序来从数据库A中获取更新的数据并将其下载到数据库B.

Is this possible to implement this function using SqlDependency or Service Broker, or is there a better way of doing it?

是否可以使用SqlDependency或Service Broker实现此功能,或者有更好的方法吗?

3 个解决方案

#1

There is number of ways how you can do that. To name a few:

有多种方法可以做到这一点。仅举几例:

setup database mirroring

设置数据库镜像
backup/restore whole db (can easily be overkill)

备份/恢复整个数据库(很容易被矫枉过正)
use custom scripts to update one db to another

使用自定义脚本将一个db更新到另一个db
use sync framework from ado.net

使用ado.net的同步框架
use some custom code to update second db

使用一些自定义代码来更新第二个db

While you can setup first three to be completely on database level, 4,5 (and 3 as well) uses some application.

虽然您可以将前三个设置为完全在数据库级别,但是4,5(以及3个)也使用了一些应用程序。

In order to call your code on time you can use both push and pull approaches, so either setup a timer or use SqlDependency to have a callback when update happened.

为了按时调用代码,您可以使用推送和拉取方法,因此要么设置计时器,要么在更新发生时使用SqlDependency进行回调。

On database level you can setup trigger or have a recurring job setup.

在数据库级别,您可以设置触发器或重复设置作业。

#2

You may implement SQL SERVER CLR integration in following ways:

您可以通过以下方式实现SQL SERVER CLR集成:

Enable CLR with SQL server: https://msdn.microsoft.com/en-us/library/ms131048(SQL.100).aspx

使用SQL Server启用CLR:https://msdn.microsoft.com/en-us/library/ms131048(SQL.100).aspx
Write CLR trigger : https://msdn.microsoft.com/en-us/library/ms131093(v=sql.100).aspx

写CLR触发器:https://msdn.microsoft.com/en-us/library/ms131093(v = sql.100).aspx
For more info :https://msdn.microsoft.com/en-us/library/ms254498%28v=vs.110%29.aspx

欲了解更多信息:https://msdn.microsoft.com/en-us/library/ms254498%28v=vs.110%29.aspx

UPDATE:

You may create a sp like bellow and call this sp in a trigger for that table: CREDIT :

您可以创建一个类似于下面的sp并在该表的触发器中调用此sp:CREDIT:

CREATE PROCEDURE dbo.usp_ExecCmdShellProcess 
AS  
    BEGIN 
        DECLARE @job NVARCHAR(100) ; 
        SET @job = 'xp_cmdshell replacement - ' + CONVERT(NVARCHAR, GETDATE(), 121) ;  

        EXEC msdb..sp_add_job @job_name = @job, 
            @description = 'Automated job to execute command shell script', 
            @owner_login_name = 'sa', @delete_level = 1 ;  

        EXEC msdb..sp_add_jobstep @job_name = @job, @step_id = 1, 
            @step_name = 'Command Shell Execution', @subsystem = 'CMDEXEC', 
            @command = 'c:\Testconsole.exe', @on_success_action = 1 ;  

        EXEC msdb..sp_add_jobserver @job_name = @job ;  

        EXEC msdb..sp_start_job @job_name = @job ;  

    END ; 
GO

#3

and I can't say better ,but I think you can go for a trigger and call a clr function in the SQL server a https://msdn.microsoft.com/en-us/library/w2kae45k.aspx.

我不能说更好,但我认为你可以在SQL服务器中使用触发器并调用clr函数https://msdn.microsoft.com/en-us/library/w2kae45k.aspx。

#1