Data access is the heart of any real-world application, and ASP.NET provides a rich set of controls that are well-integrated with the managed data access APIs provided in the common language runtime. This section walks through several iterations of a sample that uses the ASP.NET DataGrid control to bind to the results of SQL queries and XML data files. This section assumes some familiarity with database fundamentals and the SQL query language.

Server-side data access is unique in that Web pages are basically stateless, which presents some difficult challenges when trying to perform transactions such as inserting or updating records from a set of data retrieved from a database. As you'll see in this section, the DataGrid control can help manage these challenges, allowing you to concentrate more on your application logic and less on the details of state management and event handling.

The common language runtime provides a complete set of managed data access APIs for data-intensive application development. These APIs help to abstract the data and present it in a consistent way regardless of its actual source (SQL Server, OLEDB, XML, and so on). There are essentially three objects you will work with most often: connections, commands, and datasets.

• A connection represents a physical connection to some data store, such as SQL Server or an XML file.
• A command represents a directive to retrieve from (select) or manipulate (insert, update, delete) the data store.
• A dataset represents the actual data an application works with. Note that datasets are always disconnected from their source connection and data model and can be modified independently. However, changes to a dataset can be easily reconciled with the originating data model.

To perform a select query to a SQL database, you create a SqlConnection to the database passing the connection string, and then construct a SqlDataAdapter object that contains your query statement. To populate a DataSet object with the results from the query, you call the command's Fill method.

Dim myConnection As New SqlConnection("server=(local)\NetSDK;database=pubs;Trusted_Connection=yes")
Dim myCommand As New SqlDataAdapter("select * from Authors", myConnection)

Dim ds As New DataSet()
myCommand.Fill(ds, "Authors")

As mentioned earlier in this section, the benefit of using a dataset is that it gives you a disconnected view of the database. You can operate on a dataset in your application, and then reconcile your changes with the actual database later. For long-running applications this is often the best approach. For Web applications, you are usually performing short operations with each request (commonly to simply display the data). You often don't need to hold a DataSet object over a series of several requests. For situations like these, you can use a SqlDataReader.

A SqlDataReader provides a forward-only, read-only pointer over data retrieved from a SQL database. To use a SqlDataReader, you declare a SqlCommand instead of a SqlDataAdapter. The SqlCommand exposes an ExecuteReader method that returns a SqlDataReader. Note also that you must explicitly open and close the SqlConnection when you use a SqlCommand. After a call to ExecuteReader, the SqlDataReader can be bound to an ASP.NET server control, as you'll see in the next section.

Dim myConnection As SqlConnection = New SqlConnection("server=(local)\NetSDK;database=pubs;Trusted_Connection=yes")
Dim myCommand As SqlCommand = New SqlCommand("select * from Authors", myConnection)

myConnection.Open()


Dim dr As SqlDataReader = myCommand.ExecuteReader()

...

myConnection.Close()

When performing commands that do not require data to be returned, such as inserts, updates, and deletes, you also use a SqlCommand. The command is issued by calling an ExecuteNonQuery method, which returns the number of rows affected. Note that the connection must be explicitly opened when you use the SqlCommand; the SqlDataAdapter automatically handles opening the connection for you.

Dim myConnection As New SqlConnection("server=(local)\NetSDK;database=pubs;Trusted_Connection=yes")
Dim myCommand As New SqlCommand( _
                   "UPDATE Authors SET phone='(800) 555-5555' WHERE au_id = '123-45-6789'",  _
                    myConnection)

myCommand.Connection.Open()
myCommand.ExecuteNonQuery()
myCommand.Connection.Close()

An alternative syntax is to specify both a DataSource and a DataMember. In this case, ASP.NET automatically gets the DefaultView for you.

MyDataGrid.DataSource=ds
MyDataGrid.DataMember="Authors"
MyDataGrid.DataBind()

You can also bind directly to a SqlDataReader. In this case you are only displaying data, so the forward-only nature of the SqlDataReader is perfectly suited to this scenario, and you benefit from the performance boost that SqlDataReader provides.

VB DataGrid1.1.aspx [Run Sample] | [View Source]

Important: Note that the DataGrid's EnableViewState property has been set to false. If the data will be populated in each request, there is no benefit to having the DataGrid store state information to be sent through a round trip with form posts. Because the DataGrid stores all of its data when maintaining state, it is important to turn it off when appropriate to improve the performance of your pages.

DataGrid2.aspx statically populates the values of the select box, but this will not work well if those values ever change in the database. Because the select HtmlControl also supports an IEnumerable DataSource property, you can use a select query to dynamically populate the select box instead, which guarantees that the database and user interface are always in sync. The following sample demonstrates this process.

Public Sub MyDataGrid_Edit(sender As Object, E As DataGridCommandEventArgs)
    MyDataGrid.EditItemIndex = E.Item.ItemIndex
    BindGrid()
End Sub

When a row of the DataGrid is being edited, the EditCommandColumn renders the Update and Cancel links. If the client selects Cancel, you simply set the EditItemIndex back to -1. If the client selects Update, however, you need to execute your update command to the database. Performing an update query requires that you know the primary key in the database for the row you wish to update. To support this, the DataGrid exposes a DataKeyField property that you can set to the field name for the primary key. In the event handler wired to the UpdateCommand, you can retrieve the key name from the DataGrid's DataKeys collection. You index into this collection using the ItemIndex of the event, as shown in the following example.

myCommand.Parameters("@Id").Value = MyDataGrid.DataKeys(CType(E.Item.ItemIndex, Integer))

At the end of the Update event handler, you set the EditItemIndex back to -1. The following sample demonstrates this code in action.

VB DataGrid6.aspx [Run Sample] | [View Source]

One problem with the preceding example is that the primary key field (au_id) also renders as a text input box when a row is editable. You don't want the client to change this value, because you need it to determine which row to update in the database. Fortunately, you can disable this column from rendering as a text box by specifying exactly what each column looks like for the editable row. You do this by defining each row in the DataGrid's Columns collection, using the BoundColumn control to assign data fields with each column. Using this technique gives you complete control over the order of the columns, as well as their ReadOnly properties. For the au_id column you set the ReadOnly property to true. When a row is in edit mode, this column will continue to render as a Label. The following sample demonstrates this technique.

VB DataGrid7.aspx [Run Sample] | [View Source]

BoundColumn controls aren't the only controls you can set in the DataGrid's Columns collection. You can also specify a TemplateColumn, which gives you complete control over the contents of the column. The template is just arbitrary content; you can render anything you like, including server controls, inside the DataGrid's columns. The following sample demonstrates using the TemplateColumn control to render the "State" column as a drop-down list and the "Contract" column as a check box HtmlControl. The ASP.NET data binding syntax is used to output the data field value within the template. Note that there is a bit of tricky logic to make the drop-down list and check box reflect the state of the data inside the row.

VB DataGrid8.aspx [Run Sample] | [View Source]

Just as you can place a drop-down list or check box HtmlControl in a TemplateColumn, you can also place other controls there. The following sample adds Validator controls to the columns to check the client input before attempting to perform the update.

The following sample shows this code in action.

VB DataGrid11.aspx [Run Sample] | [View Source]

When using BoundColumn controls, you can explicitly set the SortExpression property for each column, as demonstrated in the following sample.

On the details page, you retrieve the querystring argument and perform a join select to obtain details from the database. The following sample demonstrates this scenario.

You can create stored procedures that accept parameters as well. For example:

CREATE Procedure LoadPersonalizationSettings (@UserId varchar(50)) AS
    SELECT * FROM Personalization WHERE UserID=@UserId
    return
GO

Using a stored procedure from an ASP.NET page is just an extension of what you've learned so far about the SqlCommand object. The CommandText is just the name of the stored procedure instead of the ad hoc query text. You indicate to the SqlCommand that the CommandText is a stored procedure by setting the CommandType property.

myCommand.SelectCommand.CommandType = CommandType.StoredProcedure

The following sample demonstrates a call to a stored procedure to fill the DataSet.

VB DataGrid14.aspx [Run Sample] | [View Source]

Parameters to stored procedures are passed just as they are for ad hoc queries, as shown in the following samples.

VB DataGrid15.aspx [Run Sample] | [View Source]

Each TableName section corresponds to a single row in the table. The following sample shows reading schema and data from an XML file using the ReadXml method of the DataSet. Note that after the data is read into the DataSet it is indistinguishable from SQL data -- the DataGrid binds to it just the same, as shown in the following sample.

VB DataGrid17.aspx [Run Sample] | [View Source]

You can also read the data and schema separately, using the ReadXmlData and ReadXmlSchema methods of the DataSet, as shown in the following sample.

VB DataGrid18.aspx [Run Sample] | [View Source]

Just as the DataSet supports reader methods for XML data, it also supports writing the data. The following sample implements a tool for selecting data from SQL and writing the result as XML data or schema text.