Watch videos with subtitles in your language, upload your videos, create your own subtitles! Click here to learn more on "how to Dotsub"

[How Do I:] Make use of Caching?

0 (0 Likes / 0 Dislikes)
  • Embed Video

  • Embed normal player Copy to Clipboard
  • Embed a smaller player Copy to Clipboard
  • Advanced Embedding Options
  • Embed Video With Transcription

  • Embed with transcription beside video Copy to Clipboard
  • Embed with transcription below video Copy to Clipboard
  • Embed transcript

  • Embed transcript in:
    Copy to Clipboard
  • Invite a user to Dotsub
Welcome to this ASP.NET video on caching. This is part 1 of 2 parts. We're going to cover page-level caching and some of the new table-caching features that are supported in SQL Server 2000 and SQL Server 2005. To get started I'm going to build a simple New Web Site in Visual Basic. And the first thing I want to show you is caching at a single page level only. And first what I'm going to do is simply spit out the current date/time by calling Response.Write(System.DateTime,Now()) just to make sure it's working. [typing] Now page-level caching is going to allow us to tell ASP.NET not to retransmit or reprocess this file every time. It's going to be cached for us. Notice when I hit "Refresh" the time is ticking up. So let's make a couple changes. I'm going to remove this because I have a little bit nicer control ready to go that I wrote called TimeLabel, and it's going to show the same date and time, but it's going to be a little bit nicely— it'll have a nicer format to it. [typing] Okay. [typing] So that new control looks like this. So whenever I hit Refresh you can see the little green bar is ticking up to indicate how many seconds have elapsed. That will give us a nice visual clue as to when the page actually refreshes. So I'll keep this running and go back and add a feature that's been around since ASP.NET 1.0 days and that's page level output caching. So that's a special directive I add here to the page. Right there. And I need to set 2 things, the duration of the cache— I'll set it to 5 seconds, and we'll have it apply the caching to every page irrespective of the query string that's passed in. So now when I hit Refresh, I'll get one for 47, but you notice over the next 5 seconds it's staying as is and there it just ticked over to 52. So that's page level output caching. Now caching is great but where it really shows its benefits to a site is when you're doing some fairly heavy processing on the back end, like maybe making a call to a database. And that's what we're going to add in right now. So I'm going to change this cache to 20 seconds. And now let's go and connect our system to a database. So we're going to connect our website to a database that's running on SQL Server 2005. We're using 2005 instead of SQL Server Express because I can easily show the tracing that's going on behind the scenes or all the communication with SQL Server. And that is on a different computer, my main computer, deskzilla. And I've created an account for me. [typing] The database we'll use is SimpleNorthwinds. Good. Okay, I'm going to take that ConnectionString right now and copy that to the clipboard and put it inside my web config file. So under ConnectionStrings—normally this is added for me if I use one of the wizards. In this case I have to put it in by hand. So we're going to add a ConnectionString, which is what you just saw. I'm going to add the password directly. Now this is not best practices; however, you can encrypt this user name and password combination, and we'll show that in the Tips and Tricks video. But you could also use integrated security. In this case this virtual machine that I'm recording the demo on is not on the same box as the database. And finally let's give this ConnectionString a name. [typing] Okay? Good. So let's use that. If we go back to our page, I'm going to code this one up by hand. We need 2 things then, a SqlDataSource, and that represents our SQL command that talks to the database. And that's going to run on the server. And let's see, the ConnectionString—and put this on a new line— the ConnectionString comes from the— ConnectionStrings:NW the one I just added. And finally the SQL Select statement—Select command looks like—I'm going to get 3 columns from the customer's table— CustomerID, CompanyName, and a ContactName. Turns out you need that dbo to clarify the schema name from the database. That's crucial in one of the built in—the SQL Server 2005 database caching. So let's go ahead and see—whoops, I need one more thing. So we have our Connection, now we need a DataGrid. Give it an ID and also is a server control. And the DataSourceID is the one above it, that's a ds1. And that should be good. Let's take a look at it. [typing] Now remember, this time we set the cache duration to 20 seconds. So when I hit Refresh it's not making a query back to the database. In fact we can switch over to this Profiler now and enable that. Let me clear it out. Now when we go back to our page and hit Refresh, notice there's no database traffic going back to the system. So we have to wait for 20 seconds to elapse, and there's our query. Now this is a great, simple way of doing data caching; however there's a problem. If this data somehow changes on the back end, you would want this page—the cached version of this page to be thrown away and refreshed immediately. Now that's been something that's been difficult to do in the past and is now easily supported with ASP.NET 2.0. The technique I'm going to show you now works both on SQL Server 2000 and 2005. To do that we first need to enable the database to support this table-dependency caching. So in the .NET Framework 2.0 there is a command line tool, aspnet_regsql that I've already got the command line here where we're going to enable the table caching for the Customers table in the SimpleNorthwinds database. So I execute that. [typing] Now you can see it did some stuff on the back end. Let me show you what it did. If I go into the Northwinds table— if I hit Refresh—there's the Customers table— but now I have an extra table, the SqlCacheTable. And also on this Customer table I have a Trigger that whenever there's a change to this table it writes a row into this AspNet special table and that's going to be polled by ASP.NET to see if there are any changes to the system. It sounds complicated but actually is very simple in action. Let me show you. So here's the data that we have. We want this page to change whenever we change a row over here. So to enable this caching, I have to do 2 things— we've set it up for the database, now I need to set it up for my website. I have to make an entry in the web.config. I have to go to the caching and enable sqlCacheDependency and enable it first to "true" and set the polling time equal to 2000 milliseconds. And then I want to do that on the database specified—let's see I want to add for the connectionString "NW", which is the connectionString up here, and I need to give it a distinct name as well. Okay, that's good for the web.config. Now if we go back to our Default Page, we need to enable that caching here on this page. And that is simply by adding 1 line here in the OutputCache called SqlDependency. And that is equal to "NW:" and the name of the table, NW for Northwinds. NW:Customers. Now if we go back here and hit Refresh, we see we have the table at 41 seconds, and it's going to stay here in the cache. If we go back to our tracing, if we scroll down to the bottom, we see that it's hitting the database every couple of seconds. So let me clear that for you. See, there's a Cachepolling query, another one, another one. But it works pretty well, watch. If we go change a line of code— let me hit Refresh, I have to hit that window of 20 seconds— so if we change this to a different company like Frostwolf, and go here and hit Refresh, it shows up right away. So that works really well, in fact if we go back and look at the caching, you can see there was some notification back and forth that the table has changed but it worked right away. So if we change this to my name, switch back, and hit Refresh, we get the change right away. Now this is great because it works with SQL Server 2000 and 2005, but 2005 introduces this new concept called the Broker Service. And this is a means to allow ASP.NET to serve as a client to where it can be notified if there's a change, and it will help set up a dialog between ASP.NET 2.0 and SQL Server 2005. To enable that it's very easy. It's the reason why we're showing you here with SQL Server 2005. Again, keep in mind that this version we're showing now is going to be polling every couple of seconds. So when I hit Refresh it kicks off the query, it starts the poll. If I go back to the site and just simply change this to CommandNotification and go back here and hit Refresh, —oh yeah, I forgot I need to add 1 line of code. If you want any code executed when your system is running, you need to create something called the Global Application Class. When the application starts up, we need to enable the system to support this kind of new caching. One line of code, it's under System.Data.SqlClient.sqlDependency.Start and the name of the ConnectionString. And the ConnectionString comes from our ConfigurationManager, ConnectionStrings ("NW"), ConnectionString, okay? Now we can hit Refresh and that should work. [waiting] Now it looks the same here, but the traffic going back and forth is going to be much simpler. In fact there's nothing. There's no polling going on at this point. So once we hit our 20-second window, you see the Refresh, and again it's a very quiet communication. If we do go and change the data, [typing] and it Refresh, it shows it right away. So we get the benefit this page invalidation at a table level but with a lot less traffic going back and forth between the systems so we don't have the polling going on. So you've seen with this video the output caching for the whole page that's been around for a while and 2 techniques to enable table-level caching, a polling mechanism that's working with SQL Server 2000 and 2005, and the new feature for 2005 only which uses the Broker Service called CommandNotification. Try using this technique today to enable better performance for your next web application.

Video Details

Duration: 11 minutes and 34 seconds
Country: United States
Language: English
License: All rights reserved
Genre: None
Views: 5
Posted by: neudesicasp on Aug 22, 2013

Watch a demonstration of Page Output Caching followed by an overview of the new Database Caching support for SQL Server 2000 and SQL Server 2005.

Caption and Translate

    Sign In/Register for Dotsub to translate this video.