SQL Basics in ADO.NET
When you interact with a data source through ADO.NET, you use SQL to retrieve, modify, and update information. In some cases, ADO.NET will hide some of the details for you or even generate required SQL statements automatically. However, to design an efficient database application with a minimal amount of frustration, you need to understand the basic concepts of SQL.
SQL (Structured Query Language) is a standard data access language used to interact with relational databases. Different databases differ in their support of SQL or add other features, but the core commands used to select, add, and modify data are common. In a database product such as SQL Server, it’s possible to use SQL to create fairly sophisticated SQL scripts for stored procedures and triggers (although they have little of the power of a full object-oriented programming language). When working with ADO.NET, however, you’ll probably use only the following standard types of SQL statements:
• A Select statement retrieves records.
• An Update statement modifies existing records.
• An Insert statement adds a new record.
• A Delete statement deletes existing records.
If you already have a good understanding of SQL, you can skip the next few sections.
Otherwise, read on for a quick tour of SQL fundamentals. nTip To learn more about SQL, use one of the SQL tutorials available on the Internet, such as the one at http://www.w3schools.com/sql. If you’re working with SQL Server, you can use its thorough Books
Online help to become a database guru.
Browsing and Modifying Databases in Visual Studio
As an ASP.NET developer, you may have the responsibility of creating the database required for a web application. Alternatively, it may already exist, or it may be the responsibility of a dedicated database administrator. If you’re using a full version of SQL Server, you’ll probably use a graphical tool such as SQL Server Management Studio to create and manage your databases.
If you don’t have a suitable tool for managing your database, or you don’t want to leave the comfort of Visual Studio, you can perform many of the same tasks using Visual Studio’s
Server Explorer window.
Here’s how you can get started. First, choose View äServer Explorer from the Visual Studio menu to show the Server Explorer window. Then, using the Data Connections node in the
Server Explorer, you can connect to existing databases or create new ones. Assuming you’ve installed the pubs database (see the readme.txt file for instructions), you can create a connection to it by following these steps:
1. Right-click the Data Connections node, and choose Add Connection. If the Choose
Data Source window appears, select Microsoft SQL Server and then click Continue.
2. If you’re using a full version of SQL Server, enter localhost as your server name. This indicates the database server is the default instance on the local computer. (Replace this with the name of a remote computer if needed.) If you’re using SQL Server Express, you’ll need to use the server name localhost\SQLEXPRESS instead, as shown in
Figure 15-2. The SQLEXPRESS part indicates that you’re connecting to a named instance of SQL Server. By default, this is the way that SQL Server Express configures itself when you first install it.
3. Click Test Connection to verify that this is the location of your database. If you haven’t installed a database product yet, this step will fail. Otherwise, you’ll know that your database server is installed and running.
4. In the Select or Enter a Database Name list, choose the pubs database. (In order for this to work, the pubs database must already be installed. You can install it using the database script that’s included with the sample code, as explained in the following section.)
If you want to see more than one database in Visual Studio, you’ll need to add more than one data connection.
Database Access in the Web World
Accessing a database in a web application is a completely different scenario than accessing a database in a typical client-server desktop application. Most developers hone their database skills in the desktop world and run into serious problems when they try to apply what they have learned with stand-alone applications in the world of the Web. Quite simply, web applications raise two new considerations: problems of scale and problems of state.
Problems of scale are the problems that can result from the massively multiuser nature of the Web. A web application has the potential to be used by hundreds or thousands of simultaneous users. This means it can’t be casual about using server memory or limited resources such as database connections. If you design an application that acquires a database connection and holds it for even a few extra seconds, other users may notice a definite slowdown. And if you don’t carefully consider database concurrency issues (in other words, what happens when the changes from different users overlap), you can run into significant headaches, such as failed updates and inconsistent data.
Problems of scale can occur when developing traditional client-server desktop applications. The difference is that in most client-server applications they are far less likely to have any negative effect because the typical load (the number of simultaneous users) is dramatically lower. Database practices that might slightly hamper the performance of a client-server application can multiply rapidly and cause significant problems in a web application.
Problems of state are problems that can result from the disconnected nature of the Internet.
As you already know, HTTP is a stateless protocol. When a user requests a page in an
ASP.NET application, the web server processes the code, returns the rendered HTML, and closes the connection immediately. Although users may have the illusion that they are interacting with a continuously running application, they are really just receiving a string of static pages.
Because of the stateless nature of HTTP, web applications need to perform all their work in the space of a single request. The typical approach is to connect to a database, read information, display it, and then close the database connection. This approach runs into difficulties if you want the user to be able to modify the retrieved information. In this scenario, the application requires a certain amount of intelligence in order to be able to identify the original record, build a SQL statement to select it, and update it with the new values.
Fortunately, both ASP.NET and ADO.NET are designed with these challenges in mind. As you work your way through this chapter (and the following two chapters), you’ll learn how to deal with databases safely and efficiently.
Scenarios and Goals of the Security Application Block
- Obtaining a temporary token for an authenticated user
- Authenticating a user using a token
- Ending a user session (expire a token)
- Determining if a user is authorized to perform a task
- Authorization
- Security-related caching and session management
 Note: |
|---|
| If you use the Security Application Block to cache security-related information, the default caching store provider for the security cache is the Caching Application Block. Although the Caching Application Block can be configured to encrypt cache data in backing stores, the application block does not support encryption of cache data stored in memory. If an attacker compromises the computer and accesses the memory of your process, he or she can access information stored in the cache. If this threat is significant for your application, you should avoid storing sensitive information such as credit card numbers or passwords in the cache or use an alternate caching store provider that supports in-memory encryption. |