Database Change Notifications in ASP.NET (MVC) Using SignalR & SQL Dependency
HTML-код
- Опубликовано: 11 сен 2018
- Special Thanks Goes To Mr. Suranga Upul Priyadarshana.
Source Code:
github.com/mewanindula/Signal...
This video shows how to real time update data grid, when make changes on table of database
watching in 2020. as a beginner, I understand it easily! thank you, sir!
Thank You!!!
Thank you very much for this wonderful video. With your video example, I am able to work with one table in real-time. It would be great if you share or suggest any examples for multiple tables in real-time on one page.
Hi,
continue with this !!! Thank you
Superb video...very helpful...👍
Thank you so much
Sir is it possible that I want to notify through a notification that database change is done? and can I connect azure database with this project ??
Excelent
Thank you
Hello, if we want to make a condition in the query so that only the record of the given condition is shown, what should we do? That is, if the condition is dynamic, we should send a parameter.
Thankyou .
Does this realtime signal R works fine in Mac machine ? I am facing some issue its updating the UI fine on windows machine but when i run in Mac the page where signal R is implemented it stucks in loading state never loads, until i comment script signalr/hub
I run this project successfully but noticed something wrong when I inspect element on Network Tab. When every page refresh (f5) trigger multiple(Ex: 2 refresh x2 get req, 5 refresh x5 get req) GET request from server. How can I resolve this?
i got a problem " Specified cast is not valid."
my list here :
var list = reader.Cast().Select(
x => new {
id=(long?)x["id"],
EName=(string)x["EName"],
DeviceName = (string)x["DeviceName"],
Status = (bool)x["Status"],
DateDetectedE=(DateTime)x["DateDetectedE"],
DateFixedE = (DateTime)x["DateFixedE"],
TimeToStart = (DateTime)x["TimeToStart"],
TimeRun=(string)x["TimeRun"],
ClientName=(string)x["ClientName"],
}).ToList();
please help
thank in advance
After SqlDataReader reader = command.ExecuteReader(); this line,
if(reader.FieldCount > 0)
{
var displayData = db.view_name.ToList();
}
Then return your displayData. Plese ignore, Cast and other things
Hi ,
I performed all steps u explained, but there is not change after data base changes
Can we do this with oracle database
Hi ,
I just wanted to check if ow can i use SignalR with Azure SQL database.Is it possible to get input from multiple azure sql databases? i wan to show data on UI dashboard which will have data received form multiple azure sql server databases.Thanks in advance.
Sorry friend, Azure SQL databases doesn't allow enable brockers upto now.
Thank you a lot , saved my life :)
You are welcome friend!!!
@@mewanindula5877 Not workig...
Where the error is occures friend?
Mewan Indula download your code and run after run sql query , data dose not display real time
Can you goto inspect of web page? and can you tell me there has any errors of console tab?
hello,how can i convert this sample using the api sending query to database by storedprocedure
SQL Dependency doesn't support for Stored procedures and Views
Thanks simple and elegant example!!! One suggestion speak a bit louder...!
Thank you. And I'll try😊
Hello @Mewan Indula
If my application is render at first time then data will be loaded successfully............,
but If I'm refresh my page then why Get() method is goes to in looping condition ??
Dear friend, Can you send me the images of your steps what you know please. (mewanindula@gmail.com)
I've tried to download and try in visual studio 2019 but it's not real time
please help me
If you have any problem regarding this video, please send email to me. mewanindula@gmail.com
Hello sir I am already using get Data() function but still not able to see real time functionality,Can you please help me
Could you please re code step by step as I explained in video, without copy pasting?
Is this the MVC application in which you are doing the changes ?
Yes. This is MVC Application
Hi thank you for amazing work, it worked on my local machine but when I connected to remotely DB not working at all could you please help me
Did you enable the brokers for your remote db?
Data is not automatially loading for me also
Only when refreshed its changing
Need help on this?
Please check the installed signalR version with,
this line inside the _Layout.cshtml.
Both versions need to be same.
Thank You!!!
Hello, I am using the mysql database, how do I change it for this?
I think SQL Dependency doesn't work for MySQL. But you can use SignalR for MySQL
not changing value if change the database ..
Thanks for the effort on this code. I have a problem however. The first time my customer view loads, all is OK with the list of items on the page. But, when I change the database, the controller bypasses the json get() code and goes right to cusHub.Show(). This results in an inaccurate list that is not updated. If I change the database again, no code is fired. Any ideas. Your help is greatly appreciated.
Did you rewrite the all codes? or copy paste my project?
@@mewanindula5877 Copied and pasted the code but changed the database and field names to match my database. Using SignalR 2.4.1
Also, getting following alert: SQL71502: Procedure: [dbo].[SqlQueryNotificationStoredProcedure-11c29b8b-c375-4ae3-9735-470c0297fb85] has an unresolved reference to object [sys].[services].[name]. Currently, I am working on trying to resolve this. Thanks in advance.
@@Video-zi3yu , Did you enable brockers?
Could you please re try the all steps without copy pasting all project?
@@mewanindula5877 Yep. Enabled broker. Returned a "1". I'll start over and see if I can get it to work. Stay tuned.
Hello sir,Everything is working fine,but in order to see updates ,I have to hit refresh button,Can you please help me
Please call getData() function
@@mewanindula5877 Already using get data() function,but records are not updating real time,Please help!!!
Mewan. It is not working for me. I don't see any errors in console. dependency_OnChange doesn't get fired at all. I have followed all the steps
what is your signalR version?
@@mewanindula5877 ME ALSO SAME PROBLEM THE SIGNALR VERSION IS /Scripts/jquery.signalR-2.3.0.min.js
@@giridharan805, Is same version that you are calling from cshtml
Hello, nice work, Thank you,
i get this error message while i am trying to run the application:
0x800a139e - JavaScript runtime error: SignalR: SignalR is not loaded. Please ensure jquery.signalR-x.js is referenced before ~/signalr/js.
could you please help?
Can you tell me, what is your Installed SignalR version please?
Hi thank you for amazing work, it worked. But When I use Procedure realtime not working?
SQL Dependency doesn't support for Stored procedures. Could you please read sql dependency limitations???
@@mewanindula5877 Thank you so much
Hello friend .I want to set notification sound whenever data has been changed.Is there anyway fri ? 😊
Yes. You can do it friend
@@mewanindula5877 Notification with sound work in desktop but I also want to work in andriod webview is that possible fir ?
@@mgpyaephyoswe5464, I think you can do it by playing some audio from mobile. But I am 100% not sure friend
Good job, is it possibe to set SQL depenancy in table level? If not, I think we can create a StatusChange database, once table changes, trigger a insert record into that database.
Please can you explain about "SQL depenancy in table level" ?
@@mewanindula5877
Since there is a sentence ”Alter Database customer set enable broker ....”, it seems that if there are many tables perform update action, then it may trigger refresh many times
@@rayleung5848, I think no. It trigger only table that we mention in sql statement
@@mewanindula5877 Thanks, I am trying to create and run, but I found (video at 8:23) is missing. Where can I fund this folder?
@@rayleung5848 it automatically read our hubs that we extended from hub
can we do this with web api??Please help
Sorry, I didn't try with API
i have aproblem with this line ( ) i cannot find this>>>thanks :)
Please rename, jquery.signalR-2.3.0 into your new updated signalR version
@@mewanindula5877 what about that (Unable to get property 'client' of undefined or null reference
)
My project not change :(
Not change means?
got a solution for asp core ?
Please, Can you explain which problem?
Yes, Can I get the solution in dotnet core?
11:00
Specified cast is not valid . How can i fix this
After SqlDataReader reader = command.ExecuteReader(); this line,
if(reader.FieldCount > 0)
{
var displayData = db.view_name.ToList();
}
Then return your displayData. Plese ignore, Cast and other things
data is not automatically load
i need to refresh the page
It is impossible. There are any errors in console??/
@@mewanindula5877 yes need to refresh the page again. no console error
DO it work in core 2.1 ??? Please
SignalR works with ASP.NET Core Web API. But I didn't try it with Core MVC Project
@@mewanindula5877 Thanks so much
I don't have Startup.cs, I just added Owin but...
Are you using library class or something else???
@@mewanindula5877 Never mind, I was not referencing the good SignalR, i took EntitySignal, but EntitySignal does not work without SignalR
@@mewanindula5877 My $.connection.cusHub return null
in written form:
techbrij.com/database-change-notifications-asp-net-signalr-sqldependency
does it work also with views?
Doesn't work with views in SQL dependency. But you can work with views using another method
@@mewanindula5877 what can i do your tut looks great and i wanted to do the same but you said not work with views So only with tables..
@@_Szakal , After SqlDataReader reader = command.ExecuteReader(); this line,
if(reader.FieldCount > 0)
{
var displayData = db.view_name.ToList();
}
Then return your displayData. Plese ignore, Cast and other things
@@mewanindula5877 So this solution work for views?
@@_Szakal , Yes
I did it and the code work succesfully. But I have a question , if we have a column in sql such as datetime,smalldatetime etc , what must we do in part of columnname = (??)x["example-date-column-name"] ? Do you have an answer for solution ? (11:02)
I think you have to convert to exact data type. e.g Convert.ToDateTime(). But I am not sure of this solution friend. If you found any solution, please comment in here. Because it will help others. Thank you!!!
@@mewanindula5877
1-) According to my smalldatetime sql column, sql command should be like :
SqlCommand command = new SqlCommand(@"SELECT [textId],[textOwner],[textContent], convert(varchar(25), [textDate], 120) as textDate from [dbo].[TextMessage]", connection)
2-) and we need to convert result to string
var listMsg = reader.Cast()
.Select(x => new
{
textId = (int)x["textId"],
textOwner = (string)x["textOwner"],
textContent = (string)x["textContent"],
textDate = x["textDate"].ToString() /* here*/
}).ToList();
Thank you friend!!!