The combination of ASP.NET and ADO.NET provides great flexibility in terms of data sources. Unlike classic ADO, in which support for XML was bolted on after the basic interfaces were written, ADO.NET was written from the ground up to deal with XML and does so quite handily. For example, the DataSet class provides built-in support for reading from and writing to XML files and streams, and also provides support for reading, writing, and inferring (from the structure of a table retrieved from a DBMS) XSD schemas. This makes working with XML data quite easy, as demonstrated in the custom control examples in Chapter 6.
ADO.NET also provides excellent support for reading data from a DBMS, including a set of classes for fast, efficient access to data in SQL Server databases and another set of classes to support OLE DB data sources. Most importantly, in the DataSet class, ADO.NET provides an abstract, in-memory representation of data. By design, once the DataSet class is populated, it knows nothing about the backend source from which its data was retrieved; it only knows about the structure of the tables and data it contains and the relationships between them. This allows a great deal of flexibility when manipulating data, passing data between application tiers, or translating data between different DBMS systems.
The .NET Framework Version 1.1 ships with four .NET Data Providers. These data providers are represented by the System.Data.SqlClient namespace, which contains classes for accessing SQL Server data, the System.Data.OleDb namespace, which contains classes for accessing data sources using an OLE DB provider, the System.Data.Odbc namespace, which contains classes for accessing ODBC data sources, and the System.Data.OracleClient namespace, which contains classes for accessing Oracle data. Each provider has a class that derives from the DbDataAdapter base class, which acts as a translator between a data source and the DataSet class, as explained in the next section. For the SqlClient namespace, this class is SqlDataAdapter. For OleDB, it is OleDbDataAdapter, and so on.
Authentication and Security
When accessing data from a backend database, one of the decisions you'll need to make is how to authenticate the user or application against the database's login credentials. It is fairly common for applications to pass a user ID and password as part of the connection string when opening a connection to the database. However, this is generally not the most secure method, since it requires storing this information in a place where the application can retrieve it when needed.
An even more serious mistake is to have an application log into the database using a privileged account, such as the SQL Server sa account. This mode of access allows all queries to run with sa privileges. If a malicious user were able to insert a query, they could delete data, modify security settings, or worse, possibly run the xp_cmdshell stored procedure, which would allow them to do just about anything on the database server.
Application code should never be run with a system administrator-level account. In fact, if you're going to run application code using a specific user ID and password to log into the database, you should create separate accounts for each application, including distinct accounts for reading and updating. If a particular part of an application requires only read access to the data, then it should use account credentials that are restricted to read-only access. This can help prevent the database or data from being compromised.
When using SQL Server, however, database access should be performed using a trusted connection wherever possible. All examples in this chapter use trusted connections because they do not require storing sensitive information (user IDs and passwords) where someone might be able to get at them.
Because of the security context in which the ASP.NET worker process is run, using trusted connections requires you to take one of two actions: