Home   Preview the Book   Chapter 15, TOC   Part 26

  Previous: Storing Visitor Information in Persistent Cookies
  Next: Creating a Database Access Class

Reading Database Information Efficiently

Although they are very useful for updating databases from Web applications, the disconnected datasets of ADO aren’t needed by applications like VB Snippets that only display database information. (The VB Snippets database is maintained in a separate application.) To make such an application scale as well as possible, use stored procedures to define the rows needed from the database and use data readers to retrieve those rows for display.

You don’t have to be a database expert to create stored procedures: Visual Studio .NET can do the work for you. Figure 15-16 shows the creation of the BrowseAll stored procedure using Visual Studio .NET’s Query Builder. If you create and configure a data adapter to use a newly created stored procedure, the Query Builder does all the work. The Advanced settings allow you to stop the creation of stored procedures other than the Select procedure that you really need. You can discard the data adapter, and the stored procedures remain in the database for your use.

Figure 15-16. The Visual Studio .NET Query Builder creates a stored procedure with an inner join of four tables.
Chapter 15, Figure 16.

When you use a stored procedure, the database does not need to parse the SQL in a command to determine what rows to return. This naturally increases efficiency and improves the scalability of your Web application. When you execute a stored procedure, you need to know the parameters it requires, and your code must set the values correctly. The BrowseAll stored procedure created in Figure 15-16, for example, needs a parameter containing a valid culture.

To use a data reader, your code needs a data connection and a data command. The ExecuteReader method of the data command creates a data reader that retrieves database rows with great efficiency. One caveat: a data reader supports forward-only reading of the rows selected. Once you move past a row, you cannot get back to it without reading the database again. Make sure that your code takes this fact into account.

The CVbCode class uses stored procedures and data readers to access the information presented by VB Snippets. Let’s take a look at that class.

  Previous: Storing Visitor Information in Persistent Cookies
  Next: Creating a Database Access Class

Home   Preview the Book   Chapter 15, TOC   Part 26

Copyright © 1996 - 2017 SoftMedia Artisans, Inc. All Rights Reserved.