locked
ASP.Net MVC 5: Best place to create single instance of sql dependency class RRS feed

  • Question

  • User-454825017 posted

    I am using asp.net mvc 5. i have to start a background when web site will be started. basically i have to use sql dependency which monitor a sql server table. if any new records added there and any change made there then a notification will come to me then my another routine will accumulate changed data and index those data by Lucene.Net.

    so please guide me best way. i want to start a sql dependency when web site start. what would be best way to create a single instance of sql dependency which will monitor my table. 

    should i use quartz.net to start a function and from there create instance of sql dependency. i want to create only single instance of sql dependency not many.

    i want to design whole thing in such a way as a result my web site performance should be impacted. so please discuss best approach i should follow to accomplish this job. thanks

    Saturday, October 17, 2020 2:27 PM

All replies

  • User475983607 posted

    What is the actual use case?  Send a message when a table changes?  That's what triggers are for.  You can sent an email or call a HTTP end point.

    Saturday, October 17, 2020 3:02 PM
  • User-454825017 posted

    How sql server trigger can communicate with my asp.net mvc application. if i can use sql dependency in my mvc site then it has a event which fire at web application just to me know that some addition or updation occur at database end for a specific table. that is why i asked best function or event in asp.net mvc from where i can create sql dependency class single instance for entire web application. please advise best approach to accomplish this task.

    thanks

    Saturday, October 17, 2020 3:08 PM
  • User475983607 posted

    How sql server trigger can communicate with my asp.net mvc application.

    T-SQL can execute a bat, PowerShell, exe, etc.   You can do pretty much do whatever you like.  I think it is unusual to call an MVC project from the database.  The normal flow is the web application requests data from the DB.  I suppose if you are trying to invalidate cache.   your use case will be helpful...

    if i can use sql dependency in my mvc site then it has a event which fire at web application just to me know that some addition or updation occur at database end for a specific table. that is why i asked best function or event in asp.net mvc from where i can create sql dependency class single instance for entire web application.

    SQL Dependency only works if the application is running.  Web applications spin down after nonuse.    Besides the previous point, website are designed to respond to requests.  I  feel it is better to send a request rather than relying on a website to monitor another process.  But there are schedulers and background process in web apps.

    please advise best approach to accomplish this task.

    I did.  The problem is you've explained the solution but not the use case.

     

    Saturday, October 17, 2020 3:54 PM
  • User-474980206 posted

    Just use Application_Start() to start a background thread to handle the dependency callback. But as stated web sites are not reliable for this. When the app starts, it will need to do a query to find events it missed, and process them. Also extra code is required if the site needs to scale to more than one server.

    ,

    Saturday, October 17, 2020 4:45 PM
  • User-454825017 posted

    my web site will run on single server. so from Application_Start if i instantiate Quartz.net schedular and from that schedular if i instantiate sql dependency class to get information like any new data inserted in a specific table or not. if i do this any problem will occur because sql dependency event will fire again and again when new data will be inserted or existing data will be inserted. does it down my web application in terms of performance or any problem may created which i can not think of now?

    please advise. thanks

    Sunday, October 18, 2020 7:52 AM
  • User475983607 posted

    my web site will run on single server. so from Application_Start if i instantiate Quartz.net schedular and from that schedular if i instantiate sql dependency class to get information like any new data inserted in a specific table or not. if i do this any problem will occur because sql dependency event will fire again and again when new data will be inserted or existing data will be inserted. does it down my web application in terms of performance or any problem may created which i can not think of now?

    The community explained the problems with your design approach - twice - and provided alternatives.  Clearly you made up your mind before posting.

    Sunday, October 18, 2020 12:06 PM