Core C# and .NET

This section offers an overview of using ADO.NET to access data stored in relational tables. Through simple examples, it presents the classes and concepts that distinguish the connected and disconnected access models.

All examples in this section as well as the entire chapter use data from the Films database defined in Figure 11-3. It consists of a movies table containing the top 100 movies as selected by the American Film Institute (AFI) in 1996, an actors table that lists the principal actors who performed in the movies, and an actor-movie helper table that links the two. The data is downloadable as a Microsoft Access (.mdb) file and an XML text (.xml) file.

Connected Model

In the ADO.NET connected mode, an active connection is maintained between an application's DataReader object and a data source. A row of data is returned from the data source each time the object's Read method is executed. The most important characteristic of the connected model is that it reads data from a resultset (records returned by a SQL command) one record at a time in a forward-only, read-only manner. It provides no direct way to update or add data. Figure 11-4 depicts the relation ship between the DataReader, Command, and Connection classes that comprise the connected model.

Working with the DataReader typically involves four steps:

The following code segment illustrates these steps with a SqlClient data provider. The code reads movie titles from the database and displays them in a ListBox control. Note that the DataReader, Command, and Connection objects are described in detail later in this chapter.

//System.Data.SqlClient namespace is required // (1) Create Connection SqlConnection conn = new SqlConnection(connstr); conn.Open(); // (2) Query string string sql = "SELECT movie_Title FROM movies ORDER BY movie_Year"; // (3) Create Command object SqlCommand cmd = new SqlCommand(sql, conn); DbDataReader rdr; // (4) Create DataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection); while (rdr.Read()) { listBox1.Items.Add(rdr["movie_Title"]); // Fill ListBox } rdr.Close(); // Always close datareader

The parameter to ExecuteReader specifies that the connection is closed when the data reader object is closed.

Disconnected Model

The concept behind the disconnected model is quite simple: Data is loaded using a SQL command from an external source into a memory cache on the client's machine; the resultset is manipulated on the local machine; and any updates are passed from data in-memory back to the data source.

The model is "disconnected" because the connection is only open long enough to read data from the source and make updates. By placing data on the client's machine, server resources data connections, memory, processing time are freed that would otherwise be required to manipulate the data. The drawback is the time required to load the resultset, and the memory used to store it.

As Figure 11-5 illustrates, the key components of the disconnected model are the DataApdapter and DataSet. The DataAdapter serves as a bridge between the data source and the DataSet, retrieving data into the tables that comprise the DataSet and pushing changes back to the data source. A DataSet object functions as an in-memory relational database that contains one or more DataTables, along with optional relationships that bind the tables. A DataTable contains rows and columns of data that usually derive from a table in the source database.

Among the numerous methods and properties exposed by the DataAdapter class, the Fill and Update methods are the two most important. Fill passes a query to a database and stores the returned set of data in a selected DataTable; Update performs a deletion, insertion, or update operation based on changes within the DataSet. The actual update commands are exposed as DataAdapter properties. The DataAdapter is presented in much more detail in Section 11.4, "DataSets, DataTables, and the Disconnected Model."

Core Note

As a simple introduction to how a DataAdapter and DataSet work together, Listing 11-2 shows how to create a DataTable, fill it with data from a database, and add it to a DataSet.

Listing 11-2. Using a DataAdapter to Load Data from a Database

string sql = "SELECT movie_Title, movie_Year FROM movies"; string connStr = " Data Source=MYSERVER;Initial Catalog=films; User Id=filmsadmin;Password=bogart;"; // (1) Create data adapter object SqlDataAdapter da = new SqlDataAdapter(sql,connStr); // (2) Create dataset DataSet ds = new DataSet(); // (3) Create table in dataset and fill with data da.Fill(ds, "movies"); // Fill table with query results DataTable dt = ds.Tables["movies"]; // (4) Add movie titles to list box for (int i=0; i< dt.Rows.Count;i++) { DataRow row = dt.Rows[i]; listBox1.Items.Add(row["movie_Title"]); }

The first step is to create an instance of a SqlDataAdapter by passing the select command and the connection string to its constructor. The data adapter takes care of creating the Connection object and opening and closing the connection as needed. After an empty DataSet is created, the DataAdapter's Fill method creates a table movies in the DataSet and fills it with rows of data returned by the SQL command. Each column of the table corresponds to a column in the source data table. Behind the scenes, the data transfer is performed by creating a SqlDataReader that is closed after the transfer is complete.

The data in the table is then used to populate a list box by looping through the rows of the table. As we see in the next chapter, we could achieve the same effect by binding the list control to the table a mechanism for automatically filling a control with data from a bound source.