Lesson 1: Accessing Data with ADO.NET

The purpose of this lesson is to provide an overview of ADO.NET and suggest design patterns featuring ADO.NET in ASP.NET. The center of the ADO.NET 1.x universe is the DataSet and the strongly typed dataset, extending the DataSet class, which is generated automatically in Visual Studio .NET.

• "Visual Studio .NET provides access to databases through the set of tools and namespaces collectively referred to as Microsoft ADO.NET."

There are four data access layers in ADO.NET (in order of execution): the physical data store, the data provider (the connection and adapter), the data set and the data view. ADO.NET 1.x shipped with three data providers: OLEDB, SQL and Oracle.

• "In addition to these database connections, you can access XML files directly from data sets using the DataSet object's ReadXML and WriteXML methods."

Unfortunately this feature is not explored in this lesson but will resurface in Chapter 7.

• "ADO.NET provides its objects, properties, and methods through the three namespaces..."

These "three" namespaces are: System.Data, System.Data.SqlClient, System.Data.OracleClient and System.Data.OleDb. These three namespaces lead to the three steps of database access: define a connection to the physical data store, define a set of commands to adapt the physical data store into a DataSet object, execute the commands.

• "Invoking commands without first invoking the [Connection.]Open method implicitly opens and closes the connection with each request."

Namely, this applies to the data adapters Fill() and Update() methods.

• "Add code to the Web form's Page_Load event procedure to fill the data set from the data adapter and to bind the data from the DataSet object to the control."

This quote suggests that it is best (or 'good') practice to call any DataBind() methods of Web form controls during the Page_Load event. This might be misleading when designing responses to postbacks (see below).

• "Turning off state information improves performance because the data in the DataGrid control does not have to be saved to the page's ViewState between requests."

The code sample in DesignModeSql.aspx worked as expected when EnableViewState was set to false on the DataGrid.

• "However, you'll often want to sort and filter data within the DataGrid control. To do that, you'll need to bind the DataGrid to the DataSet object's DataView property."

The code samples, by the way, remind us that that DataTable objects have a DefaultView property which may come in handy for runtime enjoyment of filtering and sorting. But for design time declarations, a DataView component is needed.

It may help to mention that the printed code sample that follows this quote is in error. The software code sample (in DesignModeSql.aspx.cs) clearly shows that a PreRender event procedure is required for designing a Sort command. This is because, during a sort postback, the Load event will fire first, then the Sort event procedure and finally the PreRender event procedure. By calling DataBind() during PreRender, the DataGrid object can display the sort applied to its DataView object.

• "The DataSet is the central object in ADO.NET... When you create connection, adapter, and data set objects in Design mode, you enable data typing for those objects... Type information for data objects comes from the XML Schema that Visual Studio generates when you create a data set in Design mode."

• "Use the Rows collection to add, change, or delete rows in the DataSet object's Table object."

• "To change a row in a data set, get a Row object from the table using the FindBy method and then make changes to the fields in the row..."

There is no "FindBy" method in the DataSet object. The printed code sample clearly shows that methods with this prefix are generated automatically for the strongly typed dataset, extending the DataSet object. This is an impressive feature!

• "...the Update method is usually called from the Page_PreRender event procedure..."

This quote is in line with the commentary mentioned earlier about the error found in the printed code sample. It follows that DataBind() and Update() methods for controls and datasets respectively is "usually" handled in the PreRender event procedure.

• "...generate InsertCommand, DeleteCommand, and UpdateCommand properties at run time..."

These adapter object properties can be generated at run time with a command builder object: for the SQL Server provider, it's the SqlCommandBuilder object. All the command builder object needs is a SelectCommand object defined in the adapter, using a SQL SELECT statement. Very impressive!

Note that the generated "properties" are not stored in the adapter object. Instead, the command builder object will listen for the adapter's RowUpdating event.

• "...you'll usually want to save data sets as Application, Session, or Cache variables so that you don't have to re-create them each time the form is displayed... The Cache object is uniquely useful when you're working with data sets because it allows you to specify and expiration for the data it contains."

More on the Cache object in Chapter 12.