Getting Acquainted with ASP.NET Data Access Objects
|
Enough introductory talk. Let s have a lesson in how data access happens in your ASP.NET Web pages. This section will describe database access in some detail. It might initially sound as if you ll need to work with a lot of objects and do a lot of programming to access data. True, you must use a number of objects to perform data access. However, Web Matrix will write a lot of the data access code for you, so you won t have to write much code at all. You need to read this section primarily so that you ll understand the terminology I ll use later in the book, and so that you ll have a general idea of what the code does that Web Matrix will generate for you when you begin working with databases.
When you want your Web page to talk to a database, you use a set of data objects (components) that each performs a discrete function: the connection object, the command object, and the data reader object. As a matter of interest, these objects are not directly part of Web Matrix or even of ASP.NET. Instead, they re part of Microsoft ADO.NET, which is another element of the .NET Framework that has libraries (namespaces) for all sorts of data access. When you work in Web Matrix, however, this distinction doesn t particularly matter. In practice, you ll use the data access objects without having to worry about what namespace they live in.
Understanding the Connection Object
You use the connection object to establish a link to your database server. The connection object incorporates the following information, which is necessary to create that link:
-
The type of database you want to communicate with, which is referred to as the provider. You ll work primarily with MSDE (or SQL Server, if you have it installed), of course, but in theory you can use different connections to communicate with Access, Oracle, or many other types of databases. (For more information about working with databases other than SQL Server, see the sidebar I Want My Access! )
-
The specific database you want to open and work with. A single instance of SQL Server might have dozens of discrete databases in it, so you need to indicate which specific database to open. If you re familiar with Access, the equivalent is to specify the .mdb file you want to work with. (Specifying database names is not the same as specifying the name of an individual table; you specify a table name in a different object.)
-
Login credentials. In SQL Server, you have to log in to the database server to get access to any database. When you establish a connection, therefore, the connection needs to have the right credentials. In SQL Server, there are two ways to provide credentials. You can tell SQL Server to log you in using your Windows user account information, which is referred to as using integrated security. Alternatively, you can pass a specific login name and password as part of the connection object, which is referred to as mixed mode in MSDE and SQL Server. For your work with MSDE, integrated security works best, so you ll use that.
| Note | You ll use integrated security while you re developing your data- driven Web pages. If you deploy your pages to a hosting site, however, you might have to modify the pages to include the explicit login name and password that you ve been given by your ISP. I ll discuss the deployment issue in more detail in Appendix B. |
To recap, the connection object just establishes a link to a database. Any time you want to work with a database perform a query, add data, whatever you must first establish a connection to the database using a connection object, as shown in Figure 9-1. The connection stays open only long enough to let you execute a SQL statement and get the results, and then it s closed again.
Figure 9-1: A connection object provides a link to a database.
Understanding the Command Object
As you probably know, you communicate your desires to a database by sending it SQL statements. For example, when you want to query a database to get all the rows in a table, you issue a SQL Select statement. If you want to add rows to a database table, you issue a SQL Insert statement, and so on.
In your Web pages, you use the command object to perform operations against the database. The command object has a handful of properties, including the following:
-
Connection The command doesn t communicate directly with the database; instead, the command object is associated with a connection object that handles the communication chores.
-
CommandText Naturally, a command object has to include a SQL statement as the text of a command that you want to issue against the database.
-
Parameters You ll frequently want to execute a SQL statement that includes information that you don t know in advance; instead, you get the information when the page runs. For example, you might create a Web page that selects certain records out of the database, based on a value that the user selects in the page. For these types of situations, you can create parameters for the command object and then fill them in before you execute the SQL statement.
After the command object is configured, you call a method to actually execute the SQL statement. Some SQL statements such as Select queries return one or more rows of data, or a result set, as shown in Figure 9-2. To make things slightly complicated, you call different methods depending on whether you want to return a result set. You call one method when you re expecting a result set. If you want to execute a SQL statement that doesn t return a result set, you need to call a different method. For example, if you execute a SQL statement to delete a row, you don t get back any data, just a value indicating that the operation was successful.
Figure 9-2: A command object contains a SQL statement that you execute to communicate with the database. Some SQL statements return data in a result set.
Understanding the Data Reader Object
The last of the three basic data objects is the data reader object. If your command object brings back a result set, your application accesses that data using the data reader object. As its name implies, the data reader object reads the data that the command object makes available after executing a query.
The data reader fetches one data row at a time out of the command object s result set. When it s time to get the next row, the data reader is called again, which gets the next row in the result set, and so on. In this way, the data reader works its way through the result set, consuming the rows as it goes. When it gets to the end of the result set, that s it the data is used up. Figure 9-3 shows this data reading process. If the data is needed again, the entire process is repeated the command object s execute method is called again and the results read again with the data reader. (Unless, of course, the results were cached somewhere.)
Figure 9-3: If a command object returns a result set, you can read the rows using a data reader object.
