Here, I am using SQLDependency which notifies us when any changes are done to the data in the database. If you don't know about SQLDependency, refer to the link shown below.
First, you have to add an empty MVC project and after that, add the SignalR Package which can be added by using NuGet Package Manager.
Add a Controller in your project.
- public class HomeController : Controller
- {
- // GET: Home
- public ActionResult Index()
- {
- return View();
- }
- public JsonResult GetMessages()
- {
- List<data> messages = new List<data>();
- Repository r = new Repository();
- messages = r.GetAllMessages();
- return Json(messages,JsonRequestBehavior.AllowGet);
- }
- }
Now, add a new class and name it as Repository.
- public class Repository
- {
- SqlConnection co = new SqlConnection(System.Web.Configuration.WebConfigurationManager.ConnectionStrings["con"].ConnectionString);
- public List<data> GetAllMessages()
- {
- var messages = new List<data>();
- using (var cmd = new SqlCommand(@"SELECT [id],
- [name],[adres] FROM [dbo].[userdata]", co))
- {
- SqlDataAdapter da = new SqlDataAdapter(cmd);
- var dependency = new SqlDependency(cmd);
- dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
- DataSet ds = new DataSet();
- da.Fill(ds);
- for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
- {
- messages.Add(item: new data
- {
- id = int.Parse(ds.Tables[0].Rows[i][0].ToString()),
- name = ds.Tables[0].Rows[i][1].ToString(),
- adres = ds.Tables[0].Rows[i][2].ToString()
- });
- }
- }
- return messages;
- }
- private void dependency_OnChange(object sender, SqlNotificationEventArgs e) //this will be called when any changes occur in db table.
- {
- if (e.Type == SqlNotificationType.Change)
- {
- MyHub.SendMessages();
- }
- }
- }
- public class data
- {
- public int id { get; set; }
- public string name { get; set; }
- public string adres { get; set; }
- }
- [HubName("MyHub")]
- public class MyHub : Hub
- {
- [HubMethodName("sendMessages")]
- public static void SendMessages()
- {
- IHubContext context = GlobalHost.ConnectionManager.GetHubContext<MyHub>();
- context.Clients.All.updateMessages();
- }
- }
- public class Startup
- {
- public void Configuration(IAppBuilder app)
- {
- // For more information on how to configure your application, visit http://go.microsoft.com/fwlink/?LinkID=316888
- app.MapSignalR();
- }
- }
Ahsan RazaPosted Jun 4, 2021, 5:35 AM
This Solution is not right. Your given code not works for onchange event.
Pergin SheniPosted May 8, 2019, 6:44 AM
Also dependency onchange not firing every time. it gets one time hit only
Pergin SheniPosted May 8, 2019, 6:17 AM
String cs = ConfigurationManager.ConnectionStrings["mycon"].ConnectionString; SqlDependency.Start(cs); these codes are missing before SqlDataAdapter da = new SqlDataAdapter(cmd); So this code says error when executing
Pergin SheniPosted May 8, 2019, 4:57 AM
In this, there is no insert or update happens. then how identifying any changes are done to the data in the database.
Dhandapani TRPosted Feb 10, 2018, 3:21 AM
Hi ashwani ji, i started mvc project with angularjs and for sms service but sms provider given api coding but i do not have idea to put code on where so please kindly provider solution or example mvc pro. thanks in advance.