ASP.NET —

Accessing Databases

The previous example shows how to attach in-memory collections (such as ArrayLists) to a server-side control and have it render the correct tags to the client. While this is useful, the server-side controls are capable of working with other collections-including ones that come from databases. Before seeing how to render database queries using UI elements, let's take a quick look at the .NET database story.

The .NET Database Story

Just as .NET includes a library of classes for managing rich client UI (Windows Forms) and for handling HTTP requests (ASP.NET), .NET includes a library for connecting to a wide range of databases. That library is named ADO.NET.

ADO.NET is similar to Microsoft's previous database technology (named simply ADO). ADO stands for Active Data Objects. While Microsoft has dropped "Active" from its marketing lexicon, it kept the name ADO and appended ".NET" to name the managed database technology (surely for brand name recognition). ADO represents a set of managed providers that is very similar in function and form to classic ADO. ADO.NET centers around three main units of functionality: connecting to a database, commanding the database, and using the results.

Connections

When you want to talk to a specific database, you usually need to connect to it. At the very least, most of the time this involves specifying the location of the database. For many scenarios, connecting also requires managing security (via user names and passwords). More advanced scenarios may also require dealing with such issues as connection pooling and transactions. These are all handled as part of the process of connecting to the database. The connection information is usually passed in via a string, which sets various connection parameters.

ADO.NET has classes for making connections to a database. ADO.NET 1.x included only two: a connection for Microsoft SQL Server and another for connecting to OLEDB databases. ADO.NET 2.0 adds classes specialized for more database types and includes a new set of database services using the provider pattern.

Working with ADO.NET 1.x involved writing most of the data access code using the ADO interfaces (rather than directly instantiating the database classes). This allowed you to isolate the vendor-specific details in a single place in the code-in the spot where the connection is managed. After that, getting the other parts required for making queries (for example, getting the correct command object) was a matter of asking the connection for it. While you may still write code to connect to the database using ADO.NET 1.x-style code, there's now a better way-using the ADO.NET 2.0 database provider factories.

As mentioned previously, ADO.NET 2.0 offers the provider pattern, an improvement in connecting to and using databases. By using the provider pattern, you limit exposing the kind of database you're using to a single call to a provider factory. You choose the kind of database in one place and the provider takes care of making sure the correct connection and command objects are used. This was less important in ADO 1.x when ADO divided the database world into two kinds of databases: SQL Server and OLEDB databases. However, with its support of new database types, the provider pattern is a welcome addition.

If you look in Machine.Config, you'll see providers for the following database types:

  • Odbc Data Provider

  • OleDb Data Provider

  • OracleClient Data Provider

  • SqlClient Data Provider

  • SQL Server CE Data Provider

Listing 11-3 shows a snippet from Machine.Config illustrating how the provider keys are mapped to provider factories.

Listing 11-3

<system.d<configuration>
 <system.data>
    <DbProviderFactories>
      <add name="Odbc Data Provider"
           invariant="System.Data.Odbc"
           type="System.Data.Odbc.OdbcFactory…" />
      <add name="OleDb Data Provider"
           invariant="System.Data.OleDb"
           type="System.Data.OleDb.OleDbFactory…"/>
      <add name="OracleClient Data Provider"
           invariant="System.Data.OracleClient"
           type="System.Data.OracleClient.OracleClientFactory…"/>
      <add name="SqlClient Data Provider"
           invariant="System.Data.SqlClient"
           "System.Data.SqlClient.SqlClientFactory" />
      <add name="SQL Server CE Data Provider"
           invariant="Microsoft.SqlServerCe.Client"
           type="Microsoft.SqlServerCe.Client.SqlCeClientFactory…" />
    </DbProviderFactories>
  </system.data>
</configuration>>

To get a connection to a database, you ask the runtime for a reference to the right factory and then get a connection from the factory, as shown in Listing 11-4. You use the name of the database type (System.Data.SqlClient or Microsoft.SqlServerCe.Client, for example). After getting the right kind of factory, you ask it to create a connection for you.

Listing 11-4

DbConnection GetConnectionUsingFactory()
{
  DbProviderFactory dbProviderFactory =
        DbProviderFactories.GetFactory("System.Data.SqlClient")
  return dbProviderFactory.CreateConnection();
}

Once you have a connection, you may use it to connect to the database. Given a SQL Server database named AspDotNetStepByStep available on your machine, you'd insert a connection string as shown in Listing 11-5 in your Web.Config. You could type it in manually-or, as we saw in Tutorial 9, you may use the ASP.NET configuration utilities to add the connection string to the application's configuration. Listing 11-5 shows how this might appear in a Web.Config file.

Listing 11-5

<configuration>
 <connectionStrings>
<add name="AspDotNetStepByStep"
     connectionString=
          "server=.;integrated security=sspi;database= AspDotNetStepByStepDB "/>
</connectionStrings>
</configuration>

Once you have a reference to the database connection, you may open the connection and start commanding the database.

Commands

Once connected, the database is waiting for you to send database commands. These commands usually include querying the database, updating existing data, inserting new data, and deleting data. Most databases support Structured Query Language (SQL) to manage these commands. (Some databases may support specialized variations of SQL, so the actual command text may differ from one implementation to the other.) Commanding the database usually entails writing SQL statements such as

Select * from DotNetReferences where AuthorLastName = Petzold

For example, to connect to an SQL database named AspDotNetStepByStepDB and query the DotNetReferences table for all the references by someone with the last name "Petzold," you'd use code as shown in Listing 11-6.

Listing 11-6

class UseDBApp {
 static void Main(string[] args) {
  DbProviderFactory dbProviderFactory =
        DbProviderFactories.GetFactory("System.Data.SqlClient")
  DbConnection conn = dbProviderFactory.CreateConnection()
  using(conn) {
   ConfigurationSettings s =
    ConfigurationSettings.ConnectionStrings["AspDotNetStepByStep"];
   conn.ConnectionString = s.ConnectionString;
   conn.Open();

   DbCommand cmd = conn.CreateCommand();
   cmd.CommandText =
     "SELECT * FROM DotNetReferences WHERE AuthorLastName=Petzold";
   DbDataReader reader = cmd.ExecuteReader();
   // do something with the reader
  }
}

Executing the command using ExecuteReader sends a query to the database. The results come back via an instance of the IDataReader interface. The code listed above stops short of using the results. Let's take a look at how that works.

Managing Results

Once you've connected to the database and issued a query, you probably need to sift through the data to use it. ADO.NET supports two broad approaches to managing result sets: the IDataReader interface and the DataSet class.

DataReader

The example above retrieves an IDataReader from the query operation. The IDataReader interface is useful for iterating through the results of the query. Listing 11-7 shows part of the IDataReader interface.

Listing 11-7

public interface IDataReader
{
   bool IsClosed {get;}
   int    RecordsAffected {get;}
   void Close();
   bool NextResult();
   bool Read();
//..…
}

When iterating through the results of a query, Read fetches the next row. NextResult will fetch the next result set.

Accessing data through IDataReader is often termed "fire hose mode" because you have to eat your way through the data one row at a time going forward only. There's no way to revert back to a previous row except by resetting the reader and starting again. An alternative to accessing data through the IDataReader interface is to use a DataSet.

DataSet

In addition to the IDataReader, ADO.NET supports the notion of a disconnected record set-the DataSet class in ADO.NET. The ADO.NET is primarily designed to help you write large, highly scalable applications. One of the biggest hindrances to scalability is the limits of database connectivity. Databases usually have a limit on the number of active connections available at one time, and if all the connections are in use at any particular time, any piece of code wanting a database connection will need to wait. If the number of users of a system is about the same as the number of connections available, then perhaps that's not a problem. However, if the number of users of a system is greater than the number of database connections, the system performance will likely be impacted greatly.

To encourage scalability, ADO.NET includes a class named DataSet that's designed to give you an easily navigable snapshot of your application's database. The idea behind a database is to get in and get out quickly with a copy of the data.

Objects in the DataSet class are usually built using a DataAdapter. A DataSet includes a Data[[<img src="images/shy.gif"/>]]Table array-one for each selection statement in the query. Once the DataAdapter comes back from fetching the DataSet, you have the latest snapshot of the database in memory. The DataSet contains a DataTable collection and creates a DataTable element for each SELECT statement in the query. You may access the Tables collection using either ordinal or String-type indices. Once you get to a table, iterating through the rows and columns is a matter of indexing into the table using ordinal indices for the rows and ordinal or String-type indices for the columns. Listing 11-8 shows an example of using the SqlDataAdapter to get a DataSet.

Listing 11-8

public static void UseDataSet()
{
   DataSet ds = new DataSet();
   try
   {
      SqlDataAdapter da = new SqlDataAdapter(
         "select * from customer; select * from country",
         "server=.;uid=sa;pwd=;database=ASPNetStepByStepDB");
      da.Fill(ds, "DotNetReferences");
   }
   catch(SqlException e)
   {
      System.Console.WriteLine(e);
   }

 foreach (DataTable t in ds.Tables)
   {
      Console.WriteLine("Table " + t.TableName + " is in dataset"");
      Console.WriteLine("Row 0, column 1: " + t.Rows[0][1]);
      Console.WriteLine("Row 1, column 1: " + t.Rows[1][1]);
      Console.WriteLine("Row 2, column 1: " + t.Rows[2][1]);
   }
   ds.WriteXml("c:\\dataset.xml");
   ds.WriteXmlSchema("c:\\dataset.xsd");
// also- may bind to the tables here:
this.ListBox1.DataSource = ds.Tables[0];
this.ListBox1.TextDataField = "AuthorLastName"
this.ListBox1.DataBind();
}

The code in Listing 11-8 illustrates using a DataAdapter and a DataSet. The code prints out the first two columns of the first three rows of each table in the DataSet. The example in Listing 11-8 shows that a DataTable is valid as a DataSource for databound controls. The example also shows that the DataSet objects also serialize as XML. Both the table schema and the contents may be serialized this way-making it especially useful for transferring data between systems.

Here's one final note about items in the DataSet class. They're disconnected and are not restricted to the "fire hose mode" of data access. You have complete random access to any table, any column, and/or any row in the DataSet. In fact, objects in the DataSet class are also smart enough to keep track of any data you change inside of them. You may flush the data back to the physical database by using the CommandBuilder to prepare the DataSet for an Update through the DataAdapter.

Given either an IDataReader or a DataSet, the databound controls will automatically render themselves appropriately to show the control on the browser. While you may always connect to the database and fetch the data manually through the standard connection/command architecture, ASP.NET 2.0 and Visual Studio 2005 support an even easier-to-use way to render data-via declarative databinding.