To read the article online, visit http://www.4GuysFromRolla.com/articles/041107-1.aspx

Accessing and Updating Data in ASP.NET: Programmatically Accessing Data using the Data Source Controls

By Scott Mitchell


Introduction


Working with data in ASP.NET version 1.x required writing data access code. This involved establishing a connection to the database, specifying the SQL command, and then executing the command. ASP.NET 2.0's data source controls greatly simplifies this process by encapsulating the data access pattern within a Web control construct. As such, data can be retrieved or modified and bound to a Web control without writing a single line of code. We explored the essentials of declarative data binding earlier in this article series in the Data Source Control Basics and Accessing Database Data articles.

In addition to being used declaratively, the data source Web controls can also be accessed programmatically. It is possible to add a SqlDataSource or AccessDataSource control to a web page, specify SELECT, INSERT, UPDATE, or DELETE queries, and then to programmatically execute one of the control's SQL commands. Such an approach is useful when you need to work with data programmatically, but want to avoid having to write the boilerplate data access code. In short, using the data source controls in this manner allows for data to be accessed programmatically with a single line of code.

In this article we will examine how to use the SqlDataSource control to programmatically retrieve, insert, and delete data. Read on to learn more!

Programmatically Issuing SQL Commands with the SqlDataSource and AccessDataSource Controls


The SqlDataSource and AccessDataSource controls can be used to insert, update, delete, and retrieve data. The SQL commands used to perform the insert, update, delete, and data retrieval work are specified via the InsertCommand, UpdateCommand, DeleteCommand, and SelectCommand properties, respectively, and these command are executed automatically when the data source control is declaratively bound to a data Web control (such as a GridView, DetailsView, DropDownList, and so forth).

The SQL command specified in these properties may contain parameters. For example, the SelectCommand might contain a SELECT query that returns all products from a particular category by using a parameter named @CategoryID like so:

SELECT ProductID, ProductName, UnitPrice
FROM Products
WHERE Category = @CategoryID

The parameter's value can be specified via a Parameter control in the data source control's SelectParameters collection. We talked about using parameters in detail in the Filtering Database Data with Parameters portion of this article series. The data source control can use parameterized queries for all of its SQL commands (InsertCommand, UpdateCommand, DeleteCommand, and SelectCommand), each of which is specified by corresponding parameter collection properties (InsertParameters, UpdateParameters, DeleteParameters, and SelectParameters, respectively).

The SqlDataSource and AccessDataSource controls provide four methods that enable a particular SQL command to be executed. These four methods are:

  • Select(DataSourceSelectArguments) - executes the SelectCommand and returns the results either as a DataView object or as a DataReader, depending on the value of the data source control's DataSourceMode property. The DataSourceSelectArguments object expected by this method is used to provide additional information on how the data should be modified before it is returned. For example, when sorting or paging through data in the GridView, this input parameter is used to indicate that the results should be ordered by a particular column or that only a particular subset of the records should be returned. For the demos in this article we will always pass in a value of DataSourceSelectArguments.Empty, indicating that we do not want the data to be modified.
  • Insert() - executes the InsertCommand.
  • Update() - executes the UpdateCommand.
  • Delete() - executes the DeleteCommand.
In short, data can be retrieved or an INSERT, UPDATE, or DELETE statement executed with just a single line of code. The boilerplate data access code - creating a connection object, specifying the connection string, opening the connection, creating a command object, specifying the command text, and so on - is all handled by the data source control when its, Select(), Insert(), Update(), or Delete() methods are called.

The remainder of this article examines two demos that illustrate working with data programmatically using a SqlDataSource control to retrieve and insert data from the Northwind database. The complete source code for these demos - as well as a demo that looks at deleting data in much the same manner - is available as a download at the end of this article.

Programmatically Retrieving Results Using the SqlDataSource Control


In order to programmatically retrieve the results from a data source control we first need to add the data source control to the web page and specify its properties. To illustrate working with data programmatically, let's create a page that allows the user to choose a product category from the Categories table. Upon selecting a category, imagine that we want to display the average price for the products in that category as well as the three most expensive products (their name and price). While we could accomplish both of these tasks declaratively using data Web controls like the Repeater or FormView controls, let's instead use programmatic techniques to retrieve this information and display it in a Label Web control.

The following demo includes a DropDownList named Categories that is declaratively bound to a SqlDataSource that returns all of the categories. The page also contains two other SqlDataSource controls that are not bound to any controls. The first one, AvgPriceDataSource, retrieves the average UnitPrice for the specified category. Its SelectCommand has been assigned to the following query:

SELECT AVG(UnitPrice) as AvgPrice
FROM Products
WHERE CategoryID = @CategoryID

The second data source control, MostExpensiveProductsDataSource, returns the ProductName and UnitPrice values for the three most expensive products for the specified category using the following SelectCommand:

SELECT TOP 3 ProductName, UnitPrice
FROM Products
WHERE CategoryID = @CategoryID
ORDER BY UnitPrice DESC

Both controls have a single ControlParameter in their SelectParameters collection that populates the @CategoryID parameter's value with the CategoryID value selected from the Categories DropDownList control.

When programmatically working with data returned from a SqlDataSource control is it important to take not of its DataSourceMode property. The DataSourceMode property indicates the type of data object returned by the SqlDataSource and it can be set to DataReader or DataSet (the default). If the DataSourceMode property is set to DataReader then the Select method returns an instance of an IDataReader object; if its set to DataSet then a DataView object is returned instead.

The difference is important because the code you use to programmatically work with the data differs for DataViews and Data Readers. Both objects can work with scalar data or sets of data. Data Readers are more efficient when retrieving data that you simply need to display; DataViews provide more features - the ability to sort and filter the retrieved data, to randomly access the records, and so forth. The AvgPriceDataSource SqlDataSource illustrates working with Data Readers, while MostExpensiveProductsDataSource looks at using DataViews.

The following method is called whenever the DropDownList's selected index changes and when the data is first bound to the DropDownList. As the code shows, it programmatically calls the Select() method of the AvgPriceDataSource and MostExpensiveProductsDataSource data source controls and assigns the resulting data to the Text properties of two different Label controls. Note how the syntax for accessing data differs depending on whether the SqlDataSource is returning a Data Reader or DataView.

Private Sub ProgrammaticallyBindToDataSourceControls()
   'Get the average price DataReader
   Dim myReader As IDataReader = CType(AvgPriceDataSource.Select(DataSourceSelectArguments.Empty), IDataReader)
   If myReader.Read Then
      If Convert.IsDBNull(myReader("AvgPrice")) Then
         AvgPrice.Text = "Unknown"
      Else
         AvgPrice.Text = Convert.ToDecimal(myReader("AvgPrice")).ToString("c")
      End If
   End If


   'Get the three most expensive items as a DataView
   MostExpensiveProducts.Text = String.Empty
   Dim vwExpensiveItems As DataView = CType(MostExpensiveProductsDataSource.Select(DataSourceSelectArguments.Empty), DataView)

   'Loop through each record
   For Each rowProduct As DataRowView In vwExpensiveItems
      'Output the name and price
      MostExpensiveProducts.Text &= String.Format("{0} ({1:c}); ", rowProduct("ProductName"), rowProduct("UnitPrice"))
   Next
End Sub

The code to retrive the data from the AvgPriceDataSource data source uses an IDataReader object. It then reads in the data using the Read method. Since UnitPrice can contain database NULL values, it is possible that all products in the selected category have a NULL UnitPrice value, in which case the average will be NULL as well. If that is the case, we display "Unknown", otherwise we display the result formatted as a currency. To retrieve the three most expensive products in the selected category we use a DataView object and iterate through the resulting records using a For Each statement. For each record, we display the product's name and price.

Inserting a New Record Programmatically with the SqlDataSource


The ASP.NET 2.0 data Web controls offer declarative inserting, updating, and deleting capabilites. Without writing a line of code it is possible to craft a GridView, DetailsView, or FormView that displays data as well as allows the visitor to edit, delete, and insert data. However, there may be times where this declarative, code-free approach is not what you want or need. Of course, it's always possible to write code to connect to database, craft the INSERT, UPDATE, or DELETE statement, and execute the command, but the data source controls make it even easier than that.

Imagine that we wanted to create an interface that allowed visitors to add a new product to the Products table. In particular, we wanted a highly customizable interface that, for now, displays textboxes for the product's name and price, a DropDownList for the category, and a CheckBox for the product's discontinued state. This interface would also include an "Add Product" button that, when clicked, would add a new record to the database using the values entered by the user.

To create such a page without using a FormView or one of the other ASP.NET 2.0 data Web controls you would start by adding the TextBox, DropDownList, CheckBox, and Button Web controls. Next, add a SqlDataSource control that specifies the following parameterized INSERT statement via its InsertCommand property:

INSERT INTO Products(ProductName, CategoryID, UnitPrice, Discontinued)
VALUES (@ProductName, @CategoryID, @UnitPrice, @Discontinued)

Map these parameters' values in the InsertParameters collection using ControlParameters that point to the appropriate Web controls on that page. There are a variety of ways to accomplish this. From the Designer, click on the SqlDataSource and go to the Properties window. There you will see an InsertQuery option that, if clicked, displays the Command and Parameter Editor shown below. Here you can specify the InsertCommand, the parameters, and their source. Note that each of the four parameters uses a Control as its Parameter source, with the ControlID drop-down list set to the appropriate Web control on the page.

Alternatively, these parameters can be specified via the SqlDataSource control's declarative syntax:

<asp:SqlDataSource ID="AddProductDataSource" runat="server" ConnectionString="..."
   InsertCommand="INSERT INTO Products(ProductName, CategoryID, UnitPrice, Discontinued) VALUES (@ProductName, @CategoryID, @UnitPrice, @Discontinued)"
   ProviderName="...">
   <InsertParameters>
      <asp:ControlParameter ControlID="ProductName" Name="ProductName" PropertyName="Text" />
      <asp:ControlParameter ControlID="Categories" Name="CategoryID" PropertyName="SelectedValue" />
      <asp:ControlParameter ControlID="UnitPrice" Name="UnitPrice" PropertyName="Text" />
      <asp:ControlParameter ControlID="Discontinued" Name="Discontinued" PropertyName="Checked" />
   </InsertParameters>
</asp:SqlDataSource>

In any event, once the Web controls have been added to the page and the SqlDataSource's InsertCommand and InsertParameters properties have been correctly configured, inserting a new record is as simple as calling the data source control's Insert() method. That is, the only code you need to write is the following line of code (which would be placed in the "Add Product" Button's Click event handler):

Protected Sub btnAddProduct_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnAddProduct.Click
   'Add the new product!
   AddProductDataSource.Insert()
End Sub

Conclusion


ASP.NET 2.0's data source controls are typically used declaratively, providing a code-free way to work with data. In scenarios where the data needs to be retrieved, inserted, updated, or deleted programmatically, however, you can save a few lines of boilerplate data access code by using the data source controls as we saw in this tutorial. Simply add a SqlDataSource or AccessDataSource control to the page, configure its properties, and then execute the specified SQL command by invoking the control's Select(), Insert(), Update(), or Delete() method. This tutorial showcased two demos: one that retrieved data and one that inserted data. The download available at the end of this article provides the complete source code to these two tutorials, plus an additional demo on using the SqlDataSource control to programmatically delete database data.

Happy Programming!

  • By Scott Mitchell


    Attachments:


  • Download the code used in this article

  • Article Information
    Article Title: ASP.NET.Accessing and Updating Data in ASP.NET: Programmatically Accessing Data using the Data Source Controls
    Article Author: Scott Mitchell
    Published Date: April 11, 2007
    Article URL: http://www.4GuysFromRolla.com/articles/041107-1.aspx


    Copyright 2017 QuinStreet Inc. All Rights Reserved.
    Legal Notices, Licensing, Permissions, Privacy Policy.
    Advertise | Newsletters | E-mail Offers