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

Introduction

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 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.

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

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

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

Happy Programming!