我有一个场景，使用两个不同的SqlDependencies和两个不同数据库连接，但调用一个函数。
我想使用SqlDependency获得第一个DB的更新，然后在第一个DB更改时同步第二个DB，所以在第二个数据库更新时，我想通过Signalr在客户端Kendo Grid加载更改这个简单的过程是有效的，但当DB1第一次更改时，它会同步DB2，然后DB2在客户端通知以显示更改，但当在DB1中获得第二次更改时也是相同的过程，SqlDependency调用3次并通知客户端3次，对于DB1中的第三次更改，它的SqlDependence调用6次或更多次，意味着当3次更改后，它的SQL Dependency在现场调用
-
EmailHub（DB2 Hub）
public class EmailHub : Hub { private static string _connStringDB2 = ConfigurationManager.ConnectionStrings["MyDB2"].ToString(); [HubMethodName("updateRecords")] public static void UpdateRecords() { IHubContext context = GlobalHost.ConnectionManager.GetHubContext<EmailHub>(); context.Clients.All.getUpdates(); } } -
HMailHub（DB1集线器）
public class HMailHub : Hub { private static string _connStringDB1 = ConfigurationManager.ConnectionStrings["MyDB1"].ToString(); [HubMethodName("updateRecords")] public static void UpdateRecords() { IHubContext context = GlobalHost.ConnectionManager.GetHubContext<EmailHub>(); context.Clients.All.getUpdates(); } } -
GetEmailMessagesSQL（DB2函数）
public IEnumerable<EmailAflAwmMessageDM> GetEmailMessagesByAccountSQL(string emailid) { var messages = new List<EmailAflAwmMessageDM>(); // sync hmailDb to LocalDb by EmailAccountId HMailServerSync objEmailSync = new HMailServerSync(); objEmailSync.GetEmailMessagesByAccount(Guid.Parse(emailid)); // stop all Sql dependencies before start new one SqlDependency.Stop(_connStringDB1); SqlDependency.Stop(_connStringDB2); //hmailDB service(DB1 sql function call) hmailsyncService(emailid); using (var connection = new SqlConnection(_connString)) { connection.Open(); using (var command = new SqlCommand(SQL.emailmessagesbyaccount_sql(), connection)) { command.Parameters.Add(new SqlParameter("@emailaccountid", emailid)); command.Notification = null; var dependency = (dynamic)null; SqlDependency.Start(_connStringDB2); dependency = new SqlDependency(command); dependency.OnChange += new OnChangeEventHandler(dependencyemailmessage_OnChange); if (connection.State == ConnectionState.Closed) connection.Open(); using (var reader = command.ExecuteReader()) messages = reader.Cast<IDataRecord>() .Select(x => new EmailAflAwmMessageDM() { to_msg = x.GetString(0), from_msg = x.GetString(1), subject = x.GetString(2), msg_date = x.GetDateTime(3) }).ToList(); } connection.Close(); } return messages; } -
DB2 SqlDependency
private void dependencyemailmessage_OnChange(object sender, SqlNotificationEventArgs e) { if (e.Type == SqlNotificationType.Change) { EmailHub.UpdateRecords(); } } -
HMailDB（DB1 SQL函数）
public void GetHmailMessagesByAccountSQL(int hmailid) { using (var connection = new SqlConnection(_connStringDB1)) { connection.Open(); using (var command = new SqlCommand(SQL.hmailmessages_sql(), connection)) { command.Parameters.Add(new SqlParameter("@messageaccountid", hmailid)); command.Notification = null; var dependency = (dynamic)null; SqlDependency.Start(_connStringDB1); dependency = new SqlDependency(command); dependency.OnChange += new OnChangeEventHandler(dependencyhmailmessage_OnChange); if (connection.State == ConnectionState.Closed) connection.Open(); var reader = command.ExecuteReader(); } connection.Close(); } } -
DB1 SqlDependency
private void dependencyhmailmessage_OnChange(object sender, SqlNotificationEventArgs e) { if (e.Type == SqlNotificationType.Change) { EmailHub.UpdateRecords(); } } -
客户端代码（剑道网格）
<div id="grid"> </div> @Scripts.Render("~/bundles/signalr") <script src="~/signalr/hubs"></script> <script type="text/javascript"> var emailid = '@TempData["DefaultEmailId"]' $(function () { // Declare a proxy to reference the hub. var notifications = $.connection.emailHub; // Create a function that the hub can call to broadcast messages. notifications.client.getUpdates = function () { alert("notification called"); updateGridData(); }; // Start the connection. $.connection.hub.start().done(function () { alert("connection started") updateGridData(); }).fail(function (e) { alert(e); }); function updateGridData() { datasource = new kendo.data.DataSource({ transport: { read: { url: crudServiceBaseUrl + "EmailAflAwmMessage/getMessages/?emailid=" + emailid, dataType: "json", }, update: { url: crudServiceBaseUrl + "EmailAflAwmMessage/Put/", type: "PUT", parameterMap: function (options, operation) { if (operation !== "read" && options.models) { return { models: kendo.stringify(options.models) }; } } }, }, schema: { model: { id: "EmailMessageId", fields: { EmailMessageId: { editable: true, nullable: false, type: "guid" }, subject: { editable: true, nullable: true, type: "string" }, to_msg: { editable: true, nullable: false, type: "string" }, } } } }); $("#grid").kendoGrid({ dataSource: datasource, editable: "popup", toolbar: ["create"], columns: [ { field: "to_msg", title: "to_msg", }, { field: "from_msg", title: "from_msg", }, { field: "subject", title: "subject", }, { field: "msg_date", title: "msg_date", } ], height: "400px", pageable: { refresh: true, pageSizes: true, buttonCount: 5 }, }).data("kendoGrid"); } }); -
API方法在剑道网格中的应用
public IEnumerable<EmailAflAwmMessageDM> GetMessages(string emailid) { return objEmailSQLFunction.GetEmailMessagesByAccountSQL(emailid); }
我已经详细解释了我上面的问题。请指导我解决或建议我任何替代优化解决方案，我感谢您宝贵的时间和精力。感谢
我在使用SQL依赖关系时也遇到过类似的问题。
所以我创建了一个类来有效地使用它。
注意：您应该在Application_Start呼叫SqlDependency.Start(_connStringDB1);（仅一次）
public class LiveData
{
public string SprocOrQuery { get; set; }
private Dictionary<string, object> par = new Dictionary<string, object>();
public Dictionary<string, object> Parameters { get { return par; } set { par = value; } }
public string SqlConn { get; set; }
public Action<DataTable> ActionOnData { get; private set; }
public bool EffectedOnly { get; set; }
public DateTime EffectDate = DateTime.Now;
public int EffectedCyles { get; private set; }
public DataTable Data { get; private set; }
public List<SqlNotificationInfo> Events { get; set; }
public SqlNotificationInfo CurrentEvent { get; private set; }
public LiveData() { }
public LiveData(string sprocOrQuery, Dictionary<string, object> parameters = null, string connection = null)
{
SprocOrQuery = sprocOrQuery;
Parameters = parameters;
SqlConn = connection;
}
public Task Start(Action<DataTable> actionOnData = null)
{
return Task.Factory.StartNew(() =>
{
try
{
if (ActionOnData == null) ActionOnData = actionOnData;
SqlConnection sqlConn = new SqlConnection(SqlConn);
using (SqlCommand cmd = new SqlCommand(SprocOrQuery, sqlConn) { CommandType = SprocOrQuery.Contains(" ") ? CommandType.Text : CommandType.StoredProcedure, CommandTimeout = 60 })
{
if (Parameters != null && Parameters.Count > 0)
foreach (var key in Parameters.Keys) cmd.Parameters.Add(new SqlParameter(key, Parameters[key]));
if (EffectedOnly) /* Sproc or Query must accept @UpdateDate parameter as DateTime */
{
if (cmd.Parameters.Contains("EffectDate")) cmd.Parameters["EffectDate"].Value = EffectDate;
else cmd.Parameters.Add(new SqlParameter("EffectDate", EffectDate));
}
cmd.Notification = null;
Data = new DataTable();
new SqlDependency(cmd).OnChange += OnChange;
if (sqlConn.State == ConnectionState.Closed) sqlConn.Open();
Data.Load(cmd.ExecuteReader(CommandBehavior.CloseConnection));
}
if ((Events == null || Events.Contains(CurrentEvent)))
{
if (EffectedCyles > 0) EffectDate = DateTime.Now;
EffectedCyles++;
if (ActionOnData != null) ActionOnData.Invoke(Data);
}
}
catch (Exception ex)
{
Logger.LogException(ex);
}
});
}
private void OnChange(object sender, SqlNotificationEventArgs e)
{
CurrentEvent = e.Info;
SqlDependency dependency = sender as SqlDependency;
dependency.OnChange -= OnChange;
Start();
}
}
使用
new LiveData()
{
SprocOrQuery = @"SELECT
t.[ID],
t.[CreateDate],
t.[UpdateDate]
FROM
dbo.Table t
INNER JOIN dbo.Group g
ON g.[ID] = t.[GroupID]
WHERE
t.[UpdateDate] >= @EffectDate",
SqlConn = "SqlConnectionString",
EffectedOnly = true,
Events = new List<SqlNotificationInfo>() { SqlNotificationInfo.Update }
}.Start(dt =>
{
/* dt is the dataTable you get for every update */
// you can run your dependencyemailmessage_OnChange logic here
});