The ADO.NET Data Access Objects
|
All our data access tasks in ASP.NET depend on the objects (classes) that are exposed by the .NET Framework as part of the class library. Part of this class library consists of the objects that make up ADO.NET. Predominantly, these come from the three namespaces:
-
•System.Data
-
•System.Data.SqlClient
-
•System.Data.OleDb
A namespace in programming terms is simply the scope within which a name or variable is valid. In terms of the .NET Framework class library, a namespace is a "package" that contains a series of classes that implement the objects we use in our code. So, adding a reference to the System.Data namespace to our page, for example, makes all the classes within that namespace available without us having to include the System.Data prefix each time we want to use one of these classes. For more information on namespaces and classes, see Chapter 6.
When we create a page in Web Matrix (or in a text editor) that uses ADO.NET, we have to import the appropriate classes into the page so that the objects they expose are available to our code.
If we are using SQL Server or MSDE as the data source, we just need the System.Data and System.Data.SqlClient namespaces:
<%@Import Namespace="System.Data" %> <%@Import Namespace="System.Data.SqlClient" %>
We use the SqlClient namespace in all the data access examples in this book, because Web Matrix is designed to work with SQL Server or MSDE databases, as we've seen in our examples so far. However, if you are using some other database, for example, a Microsoft Access database, you will probably use a combination of the System.Data and System.Data.OleDb namespaces:
<%@Import Namespace="System.Data" %> <%@Import Namespace="System.Data.OleDb" %>
| Note | You can also obtain a separate set of classes that allow you to work with databases that have an ODBC driver from the Microsoft Universal Data site at http://msdn.microsoft.com/downloads/default.asp?URL=/downloads/sample.asp? |
Whichever set of namespaces you use, they will provide a series of classes that include five common objects. These are:
-
The Connection object, which provides the connection between the data source (in our examples, this is a database) and the ADO.NET objects we use. In our examples, because we are using MSDE as our database, we will use the SqlConnection object from the System.Data.SqlClient namespace. (The OleDbConnection object from the System.Data.OleDb namespace is the equivalent for non-SQL Server data sources that connect via OLEDB).
-
The Command object, which uses the Connection object to execute commands against the data source. These commands can be in the form of SQL statements or stored procedures. We'll be using the the SqlCommand object from the System.Data.SqlClient namespace.
-
The DataReader object, which is used to access the results of executing a command that returns data, such as a SELECT statement. We'll be using the SqlDataReader object from the System.Data.SqlClient namespace.
-
The DataAdapter object, which uses Connection and Command objects to extract data from a database and push it into a DataSet, or push changed rows held in a DataSet back into the database. We'll be using the SqlDataAdapter object from the System.Data.SqlClient namespace.
-
The DataSet object is a repository in which we can store data in the form of tables containing rows and columns. It can store more than one table, and even the relationships between the tables. There is only one version of the DataSet, as it comes from the System.Data namespace.
While Web Matrix hides from us some of the details of how these objects are used, and how they work, it is useful to be aware of what they actually do. In particular, you need to appreciate the difference between a DataReader and a DataSet.
The Command and DataReader Objects
When we execute a SQL SELECT statement or a stored procedure against a database that returns a set of rows, the database engine accesses the stored data, and builds up a rowset containing the rows that will be returned. To get at these rows from our ASP.NET page, we can use a DataReader. We call the ExecuteReader() method of the Command object to execute our SQL statement or stored procedure. When the command has completed, this method returns a DataReader object that is "pointing to" the results, and we can tell the DataReader to fetch the rows from the database and expose them to the code in our page.
The important point to take away from this is that a DataReader does not actually contain any data. It just provides a way for us to access the results set stored in our database. It means that we have to have a connection to the database all the time we are extracting the data.
When we want to update data in the database, and not return any rows, we just use the Command object directly – we don't need a DataReader. The Command object executes a SQL statement or stored procedure against the database and returns a value that indicates what happened. Usually, this is a count of the number of rows that were affected. The Command object's ExecuteNonQuery() method is used in this case.
A DataReader is suitable for use when we are displaying data using data binding, as we did in the examples in the previous chapter. We'll show you how in this chapter. Just bear in mind that we can also access the data exposed by a DataReader directly, without using data binding, if we wish.
The DataAdapter and DataSet Objects
The DataSet object is the direct opposite of a DataReader, and provides an opportunity to work with data in a completely different way. The DataSet can store data in the form of tables, just as they are stored in a database. We can extract data from a database, put it into a DataSet, and then disconnect the DataSet from the database. In some circumstances, such as where we want to access the same data repeatedly, the DataSet can improve performance of the page.
To connect a DataSet to a database, and extract data, we use a DataAdapter object. The DataAdapter uses a Connection object and a Command object to connect to the database, in a similar way to the DataReader we just looked at. When we call the Fill() method of the DataAdapter, the Command object executes a SQL SELECT statement or a stored procedure that fetches the data, and then the DataAdapter pushes the data into the DataSet to create a table.
However, a DataAdapter can also be used to push changes to the data stored in the DataSet back into the database. This involves using three more Command objects to execute the SQL statements or stored procedures that perform the updates. The three we need are an Update command, an Insert command, and a Delete command.
The schematic diagram shows the four Command objects (three for updating and the one that we use to extract data). If we are only extracting data, we don't need the UpdateCommand, InsertCommand, or DeleteCommand:
The DataAdapter and SqlDataSourceControl
Looking at the DataAdapter object in the schematic, and remembering what we discussed with regard to the SqlDataSourceControl object at the start of the previous chapter, you can probably now see how the latter depends on the former. The SqlDataSourceControl object provides us with a way to connect to a database and extract data without having to worry about the various objects that are used "under the hood":
The SqlDataSourceControl object can be used to provide data for the generic ASP.NET list controls, though in reality there is little real benefit in this. We prefer to use the combinations of a Command and DataReader, or a DataAdapter and DataSet to achieve the flexibility we want, and reduce the overhead associated with the SqlDataSourceControl. The good news, as we hinted earlier, is that there are tools built into Web Matrix that can help us to create the code we need to use these objects. By good fortune (or perhaps just excellent forward planning!), this is the topic of the next section of this chapter.
|
EAN: 2147483647
Pages: 141