In my project, I use SignalR and SqlDependency. There is no problem when I use a normal SELECT query or a parameterized query. But when I create a query with JOIN, I get an error.
Repository,
public List<ResultNotificationDto> ListNotification(int id)
{
var notificationDtos = new List<ResultNotificationDto>();
using (var connection = new SqlConnection(connectionString))
{
connection.Open();
string query = "SELECT N.LogID, U.Name + ' ' + U.Surname AS SenderUserID, N.RecordID, N.Timestamp, N.Description, N.Title, N.NotificationIcon FROM Notification N INNER JOIN AspNetUsers U ON N.SenderUserID = U.Id WHERE N.Status = 0 AND N.ReceiverUserID = @receiverUserID";
var cmd = new SqlCommand(query, connection);
cmd.Parameters.AddWithValue("@receiverUserID", id);
var dependency = new SqlDependency(cmd);
dependency.OnChange += new OnChangeEventHandler(dbChangeNotification);
var reader = cmd.ExecuteReader();
while (reader.Read())
{
var notificationDto = new ResultNotificationDto
{
LogID = Convert.ToInt32(reader["LogID"]),
SenderUserID = reader["SenderUserID"].ToString(),
Action = reader["Action"].ToString(),
TableName = reader["TableName"].ToString(),
RecordID = Convert.ToInt32(reader["RecordID"]),
Timestamp = Convert.ToDateTime(reader["Timestamp"]),
Description = reader["Description"].ToString(),
Title = reader["Title"].ToString(),
NotificationIcon = reader["NotificationIcon"].ToString(),
};
notificationDtos.Add(notificationDto);
}
}
return notificationDtos;
}
private void dbChangeNotification(object sender, SqlNotificationEventArgs e)
{
_hubContext.Clients.All.SendAsync("ReceiveNotification");
}
Index.cshtml,
<script>
$(document).ready(() => {
let connection = new signalR.HubConnectionBuilder().withUrl("/appHub").build();
connection.start();
connection.on("ReceiveNotification", function () {
loadData()
});
loadData();
function loadData() {
$.ajax({
type: "Get",
url: "/Home/GetIndex",
success: function (value) {
$("#tableExpense tbody").empty();
var tablerow;
$.each(value, (index, item) => {
tablerow = $("<tr/>");
tablerow.append(`<td><a class="fw-semibold text-primary">#${item.logID}</a></td>`)
tablerow.append(`<td>${item.senderUserID}</td>`)
tablerow.append(`<td>${item.action}</td>`)
tablerow.append(`<td>${item.tableName}</td>`)
tablerow.append(`<td>${item.recordID}</td>`);
tablerow.append(`<td>${item.timeStamp}</td>`);
tablerow.append(`<td>${item.description}</td>`);
tablerow.append(`<td>${item.title}</td>`);
tablerow.append(`<td>${item.notificationIcon}</td>`);
$("#tableExpense").append(tablerow);
})
},
error: function (xhr, status, error) {
}
})
}
});
</script>
HomeController.cs,
[HttpGet]
public async Task<IActionResult> GetIndex()
{
var findUser = await _userManager.FindByNameAsync(User.Identity.Name);
int id = findUser.Id;
return Ok(_notificationDal.ListNotification(id));
}
GET https://localhost:7243/Home/GetIndex 500 (Internal Server Error)
I get this error in the console.
