Display Live Data From Database in Blazor (SqlTableDependency & SignalR)

so in this video we're going to cover how we set up a Blazer server app that in real time will display what is in the database so just quickly what I mean by that is that you can see we have some information here we have John who is a manager we have Henrik who is a student and right now that is coming from our MS SQL database and normally when we create and we edit data we are going to do it over in the UI so that we manipulate the data that is in the database but in this case it's actually the other way around so when we edit something in the database it will instantaneously update over in our UI so just to show a quick example if we say John 2 and I hit enter you can see that it instantly updated on our website and also if we go and include a new person so let's say John again and this time we have a John who is maybe a driver and when I hit enter you can see that we get the new John in our list it is a very powerful feature when you have something to do with a dashboard for example but you could use it in so many cases but in this video we're going to create everything from scratch the database from scratch setup Entity framework so that we can create a table in our database then we're going to use a class called SQL table dependency which is actually the class that is listening to our database and is going to notify us when there is changes in the database and after that we're going to use signal R it might not be necessary to use but I think it's a smart way to broadcast the changes to every user that is logged into the website or that is just actually displaying the website on the computer so if you're not familiar with SQL table dependency and with signalr then you can learn the two Core Concepts in this video but I think the first thing we will do is to go and create a new blazer script app so just open Visual Studio and then we hit create a new project and from in here we want to choose the Blazer server app so we hit next and let's call this for a real time data and then we can just say next in our case we're just going to use the.net6 framework I know that there's a.net 7 right now but I want to use the long term support version you can choose a.net 7 then you just have to select some other nougat packages when we install them and I actually don't know if the syntax is a little bit different so I'll just stick to the.net6 version and say create and actually before we start coding I just want to say to you that I have all the code in my blog so you don't really have to sit and code along in this video you can do it but you can just go and copy and paste it from my blog I will also set a download link for this exact project that we're going to create so that you can just go and download it but the first thing we will do is to go and right click on our project because we want to install some nuget packages so click on the manage nougat packages we then want to go to browse and we actually want to download Entity framework and as you can see I already have them here so it's this Microsoft dot Entity framework call that is the first nougat package that we want to download and we want to choose a version that matched the.net framework that we're using so in this case I was not using the.net 7 but if you're using.net7 and want to try that then you have to choose the Thunder 7 version I'll just choose the newest.net6 and then I just want to install and say I accept and when that is installed I also want to install the Microsoft dot Entity framework code.tools nougat package and what this is doing is that it enables these commands so like we can add a migration and we can update the database and that's exactly what we want so I want to choose the sixth version also and say install and I accept I actually also think we have to install this Microsoft dot Entity framework code.sql server because we're going to use a Ms SQL Server so let's also choose the newest.6 version and except so when that is installed we can go and create our database context file so in the data folder I want to right click and say add a new item and I want to call it app DB context and make sure that it is a class that you're creating so we want to say add so the first thing we will do is to extend this class and we will do that from the DB context and as you can see it suggests that we use the DB context from the intensity framework package so let's just hit enter on that and on the first line I want to go and create a connection string to our database so in this case I just copy and pasted the old one I have but instead of calling this company database then let's call it company database 2 and next up we want to add the table to our database which is called employee and we're going to take it from our employee model that we're going to create in just a moment and then the last thing we want to do is to override our on configuring method because then we want to use the options build up to say that we want to connect to a SQL Server so now let's go to the employee hit Alt Enter and then we want to say we want to generate the type employee so we want to generate class employee in a new file and I can see it just added over in our data folder so I'll just open the file and let's just make some extra space and say prop and hit tab twice this is really just a shortcut to make a new property but we want to make it in int that's fine and then we want to call this an ID and let's just hit Ctrl D twice so we copy the line two times but we actually want to make a string this time so we can just copy this also to this one and then we want to create this as a name and we also want to say that this is a title so let's just save the file and also want to save the app DB context and the next thing we will do is to go to Microsoft SQL Server management Studio to create the new database that we want to create so let's expand this and let's go and say that we want to create a new database and this one should be called company database 2 and next up we want to go to the options Tab and this is actually a very important step because else it will not work we need to scroll a bit down and then we need to say that the broker enabled should be true like this so when you enable the broker you actually allow us to listen to the database as the changes so now we can go and say okay and I'll just close this one and minimize this and now we have our new database with no table inside it so let's go back to visual studio and now when our TB context and our model is set up we should be able to create the new table in our database so just go down to package manager console then we want to type add migration and let's give it a name I'll just call this init and hit enter so it looked like it created the script that will create the table so the next thing we will do is just to say update database and when it says that that is done let's go back to the database then and update open the tables and now we can see we have our employee table so let's just right click this say edit top 200 rows and you can see we don't have any rows yet so let's just add manually let's add John and say his manager and we want to add Android who is a student so now we have our employee table with some data inside so the next step would be to just fetch the data and show it on our website so let's go to visual studio again and just close this migration file we then want to go to pages and to our index Razer page and let's just go and remove the three lines here because we still want the page to be the front page and we also want a title but let's call this for live data instead and I think just to learn one thing at a time we will first go and display the data on our website and when we have done that we want to look at the SQL table dependency and see how that works and then finally when we know how that works we want to use the signal R to actually update the new data on our website so the first code I have put in here is a for each Loop that is going to for each our employees list and actually just Loop through them and display the name and the title I think I'll just zoom in a bit and next up we want to add the code section so that we can create some code on our racer page or in our DOT eraser file and the first thing I will do is to actually create the employees list so it's just the empty list that we're going to initialize and just so we can use the model that we created we're going to say we're going to use the real-time dataapp.data folder so that it knows where the employee is next we we want to overwrite the uninitialized async so that's basically just what's going to be executed when the page is loading for the first time and I think we will just cut right through it because we're going to create a employee service that will have a method called get all employees and the way we want to manage that in our Razer file is that we want to inject the employee service so we need to go and create this employee service now and what we're going to do is we're going to create a Services folder where we will include the employee service so over in our solution Explorer I want to right click and say add a new folder and we will call this services and inside our services we want to create a new file so we say new item and we want to call this employee service and first of all we want to include our appdb context so I'll just call it DB context and it automatically going to use the the data folder and next up we want to create the method that gets the all the employees it will be a async method that is returning a task where we're going to return the list of employees which makes sense because we want to get all employees and then we want to use the DB context to get all the employees from our employee table and an important step is also to include this as node tracking because else it somehow mess up the database call that we're going to send if you don't include this then if you make a breakpoint and when you try to get some new information from the database it will just fetch some old information instead so it is a very important thing to include and then finally we're going to make it to a list where we say that it should do it async so we also have to say that it should have waited so I actually think right now if we're going to save this and also save the index.racer that we can go and test this so let's try and run the application to see if it works so I actually do get an error and I think this is because we don't have a Constructor inside uh inside our employee service so let's just try and make that and see if that works so now inside our Employee Service file I have included this empty Constructor so let's try and see if it works now and we do actually get the same error but now I found out what the problem is it is that we we can just close this we have to go to our program.cs file and before we can inject it to our that razor file we need to add it as a Singleton so let's go and just hit Ctrl D on this line so that we copy the weather forecast service that was default in this project and then we want to include the employee service just like this and let's hit Alt Enter so that we can say that we want to use the service folder so let's try and test this again and it's actually okay inside our employee service that we created this Constructor because we're going to use it later on but let's go and test it and now we can see we get the data from the database so now when that is done let's go and find out what SQL table dependency is and how it works so let's just close the application again so first of all to use the SQL table dependency class we need to go and install the nougat package so again let's go to our project say manage nougat packages and let's type SQL table dependency and then it's actually this yellow icon that we want to choose and we just want to choose the latest version so install that and say I accept and now we have the latest version of the SQL table dependency so let's go back to our employee service so the idea is to say that when we create a new instance of our Employee Service which we are doing in the index.racer file then we want to make a connection or actually a listener to our database by using the SQL table dependency so first of all I want to include this property called private read only and it is the SQL table dependency class that we're going to use and we have to say that it should listen to our employee model and then I want to call this underscore dependency and what you might notice is that it also included the table dependency that Israel planned so it's just to make sure that if this give you an error then you might have to insert this line and I also want to include a connection string to our database because the SQL table dependency has to know the connection string also so now that we know what we're going to use let's go and initialize these things in the Constructor so that every time we make a new instance of this employee service it will run the Constructor and set up the SQL table dependency so just like this we want to say the underscore connection string is equal to our connective string you might want to get it from another place but just to keep this project simple I will just put it directly in the file and then we also want to initialize our underscore dependency where we say it should be a new instance of our SQL table dependency that is targeting the employee model we then want to give it our connection string and say that it should look for the employee table so now we can go and set up the listener so we say underscore dependency and say when there is changes so unchanged then we want to run this method and this is just the syntax to do that so this is actually the listener that is going to be set up so every time there is happening some new things in our employees table then the change method will execute and we're going to make the changed method in just a moment the last thing we will do is also to say that this underscore dependency should start because else it will not start listening before we say that it should actually start so now let's go and hit Alt Enter on this changed and say generate method changed so if you scroll a bit down we can see now that it have integrated the changed method and just to test if it works let's go and just say that it should make a it called value two zero and then let's set a break point here so there really should not be any more to it than this SQL table dependency that we're going to create it and we say unchanged and when it changed we will go inside the changed method and at last you want to say that it should start and all this code inside our Constructor is of course being run when we inside our Razer file injected so that we're going to create a new instance of this employee service so let's try to save all our files and say that we want to test the application so the application is still working and if we just make sure that we have our breakpoint then let's go to our database and actually I want to set it side by side here just like this and I want to make sure we're inside our company database 2 and inside our employee and we actually have it open already so if I now say John number two you can see that it actually hit the breakpoint in our changed method so now we can confirm that the SQL table dependency is working and it's listening to our database so every time we make some changes it will hit our method so let's just hit continue and I think we have to do it twice maybe sometimes you have to do it twice I really actually don't know why but as we can see now if we go back to the website it has not changed but it noticed that there is a difference in our table now and that is actually just the job of the SQL table dependency but to actually display the new information we have to use signalr oh I actually don't know if you have to use it I think you could do it in some other way also I have seen some headlines to some other videos where they say that they do it without signal but in this video we will walk through how we're going to do it with signalr so let's just close the application again and let's minimize our database so to use signal R we need to set up some things first first we will need to go and download a nougat package again and then we want to set it up inside our program.cs file because what we're going to do is that we want to create a new Hub which is basically the place that will broadcast the changes to the UI on every user that is currently on the website because what people do when they visit the website then they're going to connect to the hub so that our server know who is connected to the hub and so it knows who it should push the changes to but we will just take it one step at a time so first of all let's go and install the nougat package and this nougat package should be the microsoft.asp netco.signola.client so it's this one and again we only want to install the.net version that we have installed so in this case I will install the newest.net6 version and say install and I accept so next we will go to the program.cs file just to configure the signal up and the first thing we will include is this add response compression you don't really have to understand what this is doing it's just compressing the community Communication in a way so that signal are on the standard and so the communication is optimized and when you include this code it will automatically include the ASP call response compression so the next thing we will do is to scroll down just under our app where we built where we built the app and here we actually just want to say that it should use the response compression so we add the feature to our service and then when we build the app we will say that it should also use it and the last thing we will have to do in our program.cs file is to scroll down just under the map laser Hub here we're going to say what Hub we're going to use and we're of course going to create this employee Hub and when you do that we also want to specify a endpoint for the up so over in our solution Explorer I want to create a new folder as we say add new folder and we want to say hubs and again we want to create a new file in here so we say new item and then we want to specify the name which is employee Hub and it's a class so it's okay we say add so for this class to understand signal R and understand that it should act as a hub we have to extend it and we're going to extend it with the Hub class and you can see we again have the microsoft.asp.co.signal R so we're just going to select that and hit enter and inside this Hub we actually only want to have one method and that should be a method that refresh the employees on our website so I just created this method which is a public async method that is returning a task and it's called refresh employees and basically what this will do is that will take one parameter which is a list of employees and it will actually just put it down inside our synth async method and basically what this line of code is doing is that it's going to say that we want to Target the clients that is currently available on this Hub and we do want to Target them all and then we want to send this request out where if you ask for the refresh employee's name it's actually just it's not really a method this is called this is just a to reference what you're going to receive of data so every people that is logged in and want to have this refresh employees is going to receive this variable which is just a new list of the new employees that is going to be sent to the UI so the idea here is that this refresh employees a method is going to be called every time the database have some changes and I want to set it and look at this I actually don't think I actually use the Hub inside the project that I created before because what I'm doing is actually just calling this directly inside our employee service but this is also just to explain you what a hub is and how you can use it and actually just the idea behind it so the thing we actually want to do is to go to our employee service and then open our Constructor we can actually dependency inject our Hub directly to this employee service so the way we do that is by using the interface I Hub context and then also up here we want to make a private property so we can use the context of the Hub inside our employee service so just at the top of our Employee Service you can see it already knows what we want to do so just hit tab so we want to take this dependency injection which is called context and save it in sour our local variable so that we can go and use it inside our changed method so when there is a new change in the database we want to get the employees once again because we know that there have been a change and because we await this we also have to make this async and then the next thing we will do is to send these employees out to everyone that listens to this refresh employees just like you saw in the hub before so it will actually just send this out to everyone who is currently a client inside our employee Hub and as you saw in the program.cs file this employee Hub is mapped inside our program.cs as a hub so first of all we just want to hit Alt Enter on this because we also want to use the hubs folder so the last thing we have to do now is actually just to go to our racer page and then listen to this endpoint because then it will map it to our employee Hub and as you saw in our employee service if we scroll a bit down you can see we actually use the employee Hub here as our Hub context so when we send this async out as the name is refreshed employees then it will send it out to the employee Hub or to every user that is connected to the employee Hub and that's actually why we don't have to use the employee Hub itself but we can go and use it if we want to but I just think this is a most simple way to connect to the hub and to send the data out to the clients so let's make sure that this is saved and let's make sure this is also safe and let's go to the index.racer page and then we actually want to work inside our on initialized async method and the first line here is actually already set so that's okay because we just fetch the employees when the page is loaded and when we inject the employee service it also starts our listener so the first thing we will do inside our DOT Razer file is to add this Hub connection and if we hit Alt Enter we can see that we can use the Microsoft asp.net core signalr client so we just want to include that and next up we want to build the Hub connection so we use the Hub connection Builder and then we want to specify what URL we want to connect to and that should be the endpoint that we specified in our program.cs so we can just go and say that we want to navigate to it to Absolute URI and then we also want to include this navigation so the way we can include that is just by inject the navigation manager and say that we want to use it as navigation so now we can say navigation to Absolute URI and Target the endpoint to our Hub so that is the way that we connect a client to the hub so when we open the website and we come into this page it will automatically connect us to the employees Hub now which means that when we send some information out to a specific Hub we can now see that this user is inside the Hub so the next thing we will do is to set this Hub connection and say on because then we can go and specify that when we send some information to this Hub that has the name refresh employees then we should get a list of employees and then we can just go and say that this new list of employees should be equal to the employee list that actually display the content on our website and at last it will invoke async the state has changed and this is just so the UI know that something new has happened you have to update the UI else we will not be able to see the changes so basically what this do is just to Listen to If there is come any refresh employees message that is sent out to the hub and if a new refresh employees message has come then we will expect that there is some new employees that we have to take care of so the final thing we have to do is just to start this connection so we say start async and then I actually think we should be good to go so if we just hit Ctrl s to save and let's go and run the application we still get the data from the database so let's open the database and try to change this to just jump and now you can see that it updated and that is basically how you do it so let's go and say the manager 2 and you can see that's also updating and if we go and add a new line it was John again who was a driver and let's hit enter you can see it also refresh the list I think that the most important thing here is just the SQL table dependency and the signal R if you understand these two things then I think you can come a long way and this is of course just one out of many ways that you can use it you can actually also use it with JavaScript so you don't have to use Blazer to do it but if you enjoyed this video then please give it a like And subscribe to my channel and else I'll just see you in the next video bye

Channel: ZetBit

Views: 4,909

Keywords: .net signalr, database change notification using signalr, show live data on website, show live data, live data blazor, display live data, display live data on website, blazor, tutorial, sqltabledependency, signalr, sql dependency, blazor server signalr example, blazor server signalr client

Id: dzFk5C7CESo

Length: 27min 37sec (1657 seconds)

Published: Tue Feb 21 2023