如何使用 SQL 依赖项知道数据库发生的更改类型

Question

我有以下代码，它使用 SignalR 显示实时数据。插入、更新或删除行后，使用 SignalR 立即更新表。但是，我特别想知道数据库发生的更改类型(无论是更新、删除还是插入(。我知道onchange()方法检测数据库上的更改，但是如何确定它是什么类型的更改？

using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionCustomer"].ConnectionString))
{
connection.Open();
using (SqlCommand command = new SqlCommand(@"SELECT [Id],[CustomerName] FROM [CustomerInfoes] ", 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();
SqlDataReader reader = command.ExecuteReader();
var listCus = reader.Cast<IDataRecord>()
.Select(x => new
{
Id = (int)x["Id"],
CustomerName = (string)x["CustomerName"],
}).ToList();
return Json(new { listCus = listCus }, JsonRequestBehavior.AllowGet);
}
}
}
private void dependency_OnChange(object sender, SqlNotificationEventArgs e)
{
CustomerHub.Show();
}

枢纽：

public static void Show()
{
IHubContext context = GlobalHost.ConnectionManager.GetHubContext<CustomerHub>();
context.Clients.All.displayCustomer();
}

视图

<script src="~/signalr/hubs" type="text/javascript"></script>
<script type="text/javascript">
$(function () {
// Proxy created on the fly
var cus = $.connection.customerHub;
// Declare a function on the job hub so the server can invoke it
cus.client.displayCustomer = function () {
getData();
};

// Start the connection
$.connection.hub.start();
getData();
});
function getData() {
var $tbl = $('#tblInfo');
$.ajax({
url: $("#Get").val(),
type: 'GET',
datatype: 'json',
success: function (data) {
$tbl.empty();
$.each(data.listCus, function (i, model) {
$tbl.append
(
'<tr>' +
'<td>' + model.Id + '</td>' +
'<td>' + model.CustomerName + '</td>' +
'<tr>'
);
});
}
});
}</script>

Answer 1

您可以通过查看 eventArgs.Info 属性轻松找到更改类型。